Hello, I just started evaluating Aspose.Excel and found the following problem(s) with the Replace-method. I am using Aspose.Excel in my ASP.NET application (VS 2003, C#). Maybe I am just doing something wrong...so I write the code as well.
Case 1: If I use the Replace method on a newly created Excel-object (without importing an existing workbook) it doesn't work: Excel excel = new Excel(); Cell cell1 = excel.Worksheets[0].Cells[1,1]; cell1.PutValue("&YY"); excel.Replace("&YY","test"); excel.Save(pathToGenerated,FileFormatType.Default); Result: "&YY" is NOT replaced through "test" in the generated workbook.
Case 2: If I use the Replace method on a newly created Excel-object (WITH importing an existing workbook) it DOES work: Excel excel = new Excel(); excel.Open(pathToTemplate); Cell cell1 = excel.Worksheets[0].Cells[1,1]; cell1.PutValue("&YY"); excel.Replace("&YY","test"); excel.Save(pathToGenerated,FileFormatType.Default); Result: "&YY" IS replaced in the generated workbook
Case 3: If I want to replace a placeholder in an imported file through multiple values (arrray of strings), an error occurs: string[] summary = new string[5]; for (int i = 0; i<5; i++) { summary = "Test" + i.ToString(); } Excel excel = new Excel(); excel.Open(pathToTemplate); Cell cell1 = excel.Worksheets[0].Cells[1,1]; cell1.PutValue("&YY"); excel.Replace("&YY",summary,true); excel.Save(pathToGenerated,FileFormatType.Default); Result: An error occurs at the replace method, saying "Object reference not set to an instance of an object".
Case 4: If I want to replace a placeholder in a newly created file (without import) the replace doesn't work. (The same code as in case 3, just without importing). Result: The original string is NOT replaced.
What do you think? w.
p.s. the code tags in the forum don't work...text between code tags was always displayed without line breaks and in a strange font...
Replace method is designed to replace the placeholders in designer spreadsheet. So it works in case2 but doesn't work in case 1, 4. I will change the routine to make it also work on a newly created excel file. It will be available in one or two weeks.
Case 3 is caused by a small bug in Aspose.Excel. I fixed it and will release a hotfix within this week.
I downloaded the hotfix and tried my sample. Unfortunately the Replace method still doesn’t work as implied in the chapter “Designer-driven approach”. There, you describe how Aspose.Excel can be used to populate a template with data. When I try a simple example, where I want to replace a placeholder with actual data (data in an array), the values below the placeholder in the template are overwritten by the array-data and NOT shifted down. Probably my explanation is not very clear, so I give an example:
C#-Code: string[] summary = new string[5]; for (int i = 0; i<5; i++) { summary = “Test” + i.ToString(); } excel.Replace("&Replace",summary,true);
Excel Template: The first column of the template that should be filled looks like the following (-- indicate an empty row in excel):
MY HEADING &Replace – These value should be shifted down 111 222 333 444 555
Result: The generated workbook looks like that: MY HEADING Test0 … Test4 333 444
So the tempalte cells are obviously just overwritten and not shifted down. I think this is a strange behaviour, as a template would not be very useful, if one had to know the amount of data to be inserted into the template at design-time…
Is this an intended behaviour of the Replace method or is there a bug?
The Replace method doesn’t shift down cells. If you want to do that, you can use Cells.InsertRow method to insert rows before you call the Replace method.
To use the designer driven approach, I suggest you to use Smart Marker. That’s the advance upgrade for Replace method. The demo source code is included in the setup msi. Online demo is at http://www.aspose.com/Products/Aspose.Excel/Demos/SmartMarker.aspx.