Issue in reading xlsx files in European format

Hi,


We are reading a xlsx file which is in spanish settings through Aspose workbook object. But the cultureinfo of the workbook is getting set as ‘EN-US’ instead of ‘Es-Es’. But in case of xls file in spanish settings, the cultureinfo of workbook is getting set correctly as ‘Es-Es’.
Below is my code for reading file through workbook.

TxtLoadOptions options = new TxtLoadOptions(LoadFormat.Auto)
{
ConvertDateTimeData = false,
ConvertNumericData = false,
};
//We are reading Aspose license here
SetAsposeCellLicense();
Workbook workBook = new Workbook(stream, options);
workBook.Settings.CheckExcelRestriction = false;

Please let us know how to read the file settings in the file format.

Thanks,
Prasanna.


Hi Prasanna,


Thank you for contacting Aspose support.

Please use the WorkbookSettings.CultureInfo property to set the correct/desired locale for the spreadsheet. In case you face any difficulty, please provide us the problematic spreadsheet for further review.

Hi Raza,


In case of xls file, Aspose is able to read the client file settings without explicitly setting the culture but in case of xlsx it is not able to read the client file settings. Is there any reason for this behaviour?
Let me know if you need more information.

Hi,

Thanks for your posting and using Aspose.Cells.

Do you have a Spanish Microsoft Excel or do you have English Microsoft Excel which is used after changing the Date & Regional Settings to Spanish.

Please also provide us your xls and xlsx files so that we could investigate this issue by loading your files in Aspose.Cells APIs. Also it will be helpful if you could provide us some screenshots highlighting your issue in detail. Thanks for your cooperation.

Hi,


We have a client machine with regional settings as Spanish(Spain) and when we are reading a xlsx file uploaded from such a machine using Aspose .Net library, the aspose workbook culture info is getting set as En-Us because of which decimal separator is read incorrectly. But with the same code, when a xls file is uploaded from a spanish machine, the aspose workbook culture info is getting set as Es-Es which will read the decimal separator for the numbers correctly.

I have attached the screenshots of aspose settings in case of xls and xlsx.


Hi,

Thanks for your posting and screenshots and using Aspose.Cells.

Does it means your client machine has created your source excel file and you load that source file in Aspose.Cells API and find the problem. This happens with xlsx only and not with xls. Kindly also attach your both excel xls and xlsx files so that we could look into it and find the possible solution.

Hi Shakeel,


That is right. My client machine has created the xls and xlsx files and when we load in Aspose.cells API, we are finding problem with xlsx whereas xls is working fine. Please find the attached xls and xlsx files which are created in a client machine which has regional settings as Spanish(Spain)


Hi Nakul,


Thank you for sharing the sample spreadsheets.

Please note that XLS is Binary File Format where the country information is stored in the file it self therefore you do not need to specify locale while processing XLS file format. However, in case of spreadsheet format such as XLSX, the locale information is picked from the locale of the machine, that is the reason when you load the file created in Spain on a machine with locale other than Spain the formatting will change accordingly. You can confirm this by loading the spreadsheet in Excel with locale set to US.

The only solution to avoid such problem is to set the locale/region for the WorkbookSettings class. Please execute the following piece of code on your side to see the difference in formatting before and after setting the region.

C#

var workbook = new Workbook(“D:/sample.xlsx”);
var cells = workbook.Worksheets[0].Cells;
var cell = cells[“F2”];
Console.WriteLine(cell.DisplayStringValue);
workbook.Settings.Region = CountryCode.Spain;
Console.WriteLine(cell.DisplayStringValue);