Free Support Forum - aspose.com

Strange behaviour of Replace method

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++)
{
summaryIdea = "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.

Laurence,
that’s great …I’m looking forward to the hotfix!

Regards,
wolfgang

Hi wolfgang,

Please try the latest hotfix. The bug is fixed.

Hello Laurence!

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++)
{
summaryIdea = “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?

Best regards,
w.

Hi,

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.