Hey Everybody,
I am here today to bring a problem that I have been trying to solve for a while. I can’t find a solution.
Here is the situation. I have an option to import xls files in my application. The values in my first columns of the xls file are “5.7” and “5.8”. The problem happens when my code tries to read the data from the xls file, the previous values are showed as “5,7” and “5,8”.
What I could understand is that aspose sees the values from the first column as numeric values. I does not happen to the other columns though.
I attached in this post my xls file and a print screen that shows the numeric value that my code is getting.
Has anybody seen this problem???
Sincerely,
Arthur Medeiros.
Hi Arthur,
Thank you for contacting Aspose support.
I have tried your presented scenario while using the latest version of Aspose.Cells for .NET 8.8.1. I am afraid, I was not able to observe the said problem. Please check the attached snapshot and give the latest version a try on your side as well. In case the problem persists, please provide details of your environment (locale/regional settings, .NET Framework) along with a sample application to replicate the issue on our end.
Hello Barbar,
Well, it turns out that this problem happens to every situation that the user can try to import from xls.
Here
is what I figured, whenever a value of a field in my xls file is
something like “6.5”,“2.4” aspose will see it as a number. Now, if I use
instead “6.5.4”, aspose will treat it as a string. I even tried stuff
like “6.a” and that worked.
Now, is there any way I could force aspose to see all of my rows and columns as string values?
I
already call the StringValue property (as you can see in the attached
file). However, that property already has the value altered.
I appreciate you attention,
Sincerely,
Arthur Medeiros.
Hi Arthur,
Thank you for writing back.
Please note, the column A in your provided spreadsheet is set to format as General, that means, the numeric values will be treated as numbers and non-numeric values as strings/text. In case of numeric values such as 6.5 or 2.4, the dot between the two values is the decimal separator, which is dependent on the system's locale (see attached snapshot). When you open your spreadsheet in Excel application on a system whose regional settings correspond to a location where decimal separator is comma (,) the Excel application automatically converts the decimal separator to match the system's locale, that is; dot is replaced with comma. I believe your presented problem is related to the locale of your machine. As you haven't shared any details about the environment so I am assuming your system's locale is Brazil (according to your profile country). You can override the locale settings with following code snippet to US where decimal separator is dot.
Note: I have tested the scenario by changing the locale of my machine to Brazil. Without setting the CurrentCulture property, the decimal separator is comma whereas with following snippet, the decimal separator is dot.
C#
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
var workbook = new Aspose.Cells.Workbook(dir + "questao_2.xlsx");
var cells = workbook.Worksheets[0].Cells;
Console.WriteLine(cells["A2"].Value + " " + cells["A2"].StringValue); Console.WriteLine(cells["A3"].Value + " " + cells["A3"].StringValue); Console.WriteLine(cells["A4"].Value + " " + cells["A4"].StringValue); Console.WriteLine(cells["A5"].Value + " " + cells["A5"].StringValue); Console.WriteLine(cells["A6"].Value + " " + cells["A6"].StringValue); Console.WriteLine(cells["A7"].Value + " " + cells["A7"].StringValue); Console.WriteLine(cells["A8"].Value + " " + cells["A8"].StringValue); Console.WriteLine(cells["A9"].Value + " " + cells["A9"].StringValue);
Console.WriteLine(cells["A2"].Value + " " + cells["A2"].StringValue); Console.WriteLine(cells["A3"].Value + " " + cells["A3"].StringValue); Console.WriteLine(cells["A4"].Value + " " + cells["A4"].StringValue); Console.WriteLine(cells["A5"].Value + " " + cells["A5"].StringValue); Console.WriteLine(cells["A6"].Value + " " + cells["A6"].StringValue); Console.WriteLine(cells["A7"].Value + " " + cells["A7"].StringValue); Console.WriteLine(cells["A8"].Value + " " + cells["A8"].StringValue); Console.WriteLine(cells["A9"].Value + " " + cells["A9"].StringValue);
Hi again,
Adding more to my previous response, the values in cells from A2 to A9 are stored as numbers in the spreadsheet, therefore you can simply use the Cell.StringValueWithoutFormat to get the original value. Please check attached snapshot taken when system’s locale is set to Brazil. Moreover, you should format the column A as text so that Excel does not auto-convert the values to numbers.
C#
var workbook = new Aspose.Cells.Workbook(dir + "questao_2.xlsx");
var cells = workbook.Worksheets[0].Cells;
Console.WriteLine(cells["A2"].Value + " " + cells["A2"].StringValue + " " + cells["A2"].StringValueWithoutFormat);
Console.WriteLine(cells["A3"].Value + " " + cells["A3"].StringValue + " " + cells["A3"].StringValueWithoutFormat);
Console.WriteLine(cells["A4"].Value + " " + cells["A4"].StringValue + " " + cells["A4"].StringValueWithoutFormat);
Console.WriteLine(cells["A5"].Value + " " + cells["A5"].StringValue + " " + cells["A5"].StringValueWithoutFormat);
Console.WriteLine(cells["A6"].Value + " " + cells["A6"].StringValue + " " + cells["A6"].StringValueWithoutFormat);
Console.WriteLine(cells["A7"].Value + " " + cells["A7"].StringValue + " " + cells["A7"].StringValueWithoutFormat);
Console.WriteLine(cells["A8"].Value + " " + cells["A8"].StringValue + " " + cells["A8"].StringValueWithoutFormat);
Console.WriteLine(cells["A9"].Value + " " + cells["A9"].StringValue + " " + cells["A9"].StringValueWithoutFormat);
var cells = workbook.Worksheets[0].Cells;
Console.WriteLine(cells["A2"].Value + " " + cells["A2"].StringValue + " " + cells["A2"].StringValueWithoutFormat);
Console.WriteLine(cells["A3"].Value + " " + cells["A3"].StringValue + " " + cells["A3"].StringValueWithoutFormat);
Console.WriteLine(cells["A4"].Value + " " + cells["A4"].StringValue + " " + cells["A4"].StringValueWithoutFormat);
Console.WriteLine(cells["A5"].Value + " " + cells["A5"].StringValue + " " + cells["A5"].StringValueWithoutFormat);
Console.WriteLine(cells["A6"].Value + " " + cells["A6"].StringValue + " " + cells["A6"].StringValueWithoutFormat);
Console.WriteLine(cells["A7"].Value + " " + cells["A7"].StringValue + " " + cells["A7"].StringValueWithoutFormat);
Console.WriteLine(cells["A8"].Value + " " + cells["A8"].StringValue + " " + cells["A8"].StringValueWithoutFormat);
Console.WriteLine(cells["A9"].Value + " " + cells["A9"].StringValue + " " + cells["A9"].StringValueWithoutFormat);
Hey Babar,
First of all, sorry for not giving you enough information about my enviroment, but you are right about where I am located. Also, I know that in the USA the dot is the decimal separator, which is different from what we use in Brazil, but I could not to relate that to the problem. I thought that if I had everything in my computer set to be in English, I mean keyboard and language, I would not have that problem.
Anyway, I also did not know that when the cell is set to format as General and has a numeric value, it will be treated as a number.
Thank you very much for your help, I really appreciate it, and I think the problem is solved!
Sincerely,
Arthur Medeiros.
Hi Arthur Medeiros,
Good to know that your issue is sorted out. 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.