Extracting special characters from *.xlsm file

Aspose.Cells Product Family
1

Hi There,

I have a problem extracting values (special characters) from an *.xlsm file.
We’re using Aspose.Cells for .NET version 7.2.2.4. When opening and extracting values from the column B from the attached file (created with office 2010) we get following values:

· Char(10) = B2 = “Row1\r\nRow2”

· Char(11) = B3= “Row1_x000B_Row2”

· Char(12) = B4= “Row1_x000C_Row2”

· Char(13) = B5= “Row1_x000D_Row2”

· Char(14) = B6= “Row1_x000E_Row2”

· Char(15) = B7= “Row1_x000F_Row2”

Why do the special characters are interpreted in this way? We expect to get a vertical tab Char(11) as Row1\vRow2

With the most recent version of cells, the problem is similar.

Thanks a lot for your help

And sorry if there is any information missing (greenhorn :wink:)

Martin

2

Hi,


Thanks for the template file.

I used the code segment with your file with v8.2.0.x, I think it gives expected values from the cells i.e. B2:B8:
e.g
Sample code:

string filePath = @“e:\test2\SpecialCharacter.xlsm”;


Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

workbook.CalculateFormula();


Cell cell = worksheet.Cells[“B2”];
Debug.WriteLine(cell.StringValue);
cell = worksheet.Cells[“B3”];
Debug.WriteLine(cell.StringValue);

cell = worksheet.Cells[“B4”];
Debug.WriteLine(cell.StringValue);

cell = worksheet.Cells[“B5”];
Debug.WriteLine(cell.StringValue);

cell = worksheet.Cells[“B6”];
Debug.WriteLine(cell.StringValue);
cell = worksheet.Cells[“B7”];
Debug.WriteLine(cell.StringValue);

cell = worksheet.Cells[“B8”];
Debug.WriteLine(cell.StringValue);


I have attached the output window’s screen shot for debug prints in VS.NET for your reference.

Thank you.

3

Hi there,


Thanks a lot for your quick reply and help. We discoverd that the problem was caused by the fact that we did not use CalculateFormula().

Greets Martin
4

Hi,


Good to know that your issue is resolved now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
5
Hi there,

Sorry to get back to you.
Can you indicate why the CalculateFormula() has to be used and whether there is another option/possebility without recalculating?

We discovered that the use of the "CalculateFormula()" has some sideeffects so that we cannot apply this.

Thanks a lot for your help

Martin
6

Hi,


Well, I am afraid, when you have formulas in the cells, you need to call the method to calculate the values in the cells. Do you find any performance issue or other issue while using the Workbook.CalculateFormula() method. As a workaround, you may try to use Worksheet.CalculateFormula() method instead or even you may try to calculate the values for the underlying cells only via using Cell.Calculate() method, see the sample line of code below:
e.g
Sample code:

Worksheet worksheet = workbook.Worksheets[0];
worksheet.CalculateFormula(true, false, null);

Thank you.



7

Hi Martin,

Thanks for your posting and using Aspose.Cells.

Actually, your cell contains formulas (e.g =CHAR(10) , =CONCATENATE(“Row1”,A2,“Row2”) etc), these are not string values. If these were string values, then you would not need to call calculate formula. But for formulas, you must call calculate formula to retrieve the correct values.

As a workaround, you can create two workbook objects from the same file. For your first workbook, you just work without calling calculate formula and for second workbook, you call calculate formula to retrieve the correct values.

Please see the following code for your reference. Here mainWorkbook is a your main workbook where you did not call the calculate formula, while the other workbook is just instantiated to retrieve correct values after calling the calculate formula.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\SpecialCharacter.xlsm”;


//This is main workbook without calculate formula

Workbook mainWorkbook = new Workbook(filePath);



//This is the second workbook where formula is calculated

//to retrieve correct values

Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();


Worksheet worksheet = workbook.Worksheets[0];


for (int i = 1; i < 8; i++)

{

Cell cell = worksheet.Cells[i, 0];

}