Hello,
I am trying to convert xml files to xlsx using apose.cells within a Python script, by loading the xml file and then saving it as xlsx. I have noticed that in some files data gets wrongly converted into a date format.
For example 45.7 gets converted to 01.07.1945. I have tried to convert this cell using cell.set_style, however then the value is changed to something entirely different.
Is it possible to disable the conversion and simply use the data as it is in the xml file?
Could you please zip and attach your source XML file and output XLSX file. Moreover, share your sample code that you are using with Aspose.Cells for Python. We will check your issue soon.
Sure, here’s the essential part of my code and both files in the zip file.
import aspose.cells as ac
workbook = ac.Workbook("example.xml")
workbook.save("Output_apose.xlsx")
example.7z (7,2 KB)
@Mika13
By testing on the latest version v24.8 using sample files and the following sample code, we can obtain the correct results. Please refer to the attachment. example_out.zip (7.0 KB)
import aspose.cells as ac
workbook = ac.Workbook("example.xml")
workbook.save("example_out.xlsx")
We recommend you to kindly try using our latest version/fix,
Aspose.Cells for Python via .NET 24.8.
I am using version 24.8.0. Looking at the xlsx you provided I did notice that it s using a comma as a separator rather than a period as in my file. Is that an issue with my computer then?
It might be due to your locale/regional settings of OS (machine). When I opened the output Excel file into MS Excel it is fine tuned, see the screenshot for your reference.
sc_shot1.png (59.8 KB)
What is your locale/regional settings on your end?
It is set to Germany and unfortunately I am not able to change it, since I do know this tends to be a problem.
@Mika13
Please refer to the following example code to set the language code.
import aspose.cells as ac
workbook = ac.Workbook("example.xml")
workbook.settings.language_code = ac.CountryCode.USA
workbook.save("example_out.xlsx")
Hope helps a bit.
This does not seem to do anything really. It looks still the same in excel and when I check the type with the following code:
import aspose.cells as ac
workbook = ac.Workbook("example.xml")
workbook.settings.language_code = ac.CountryCode.USA
worksheet = workbook.worksheets[0]
cell = worksheet.cells.get_cell(3,3)
print(cell)
workbook.save("Output_aspose.xlsx")
the output is
Aspose.Cells.Cell [ D4; ValueType : IsDateTime; Value : 45.7 ]
same as before.
This is the expected behavior. Please note that when you render to the Excel (e.g., XLSX) file format and open the output Excel file in MS Excel manually, it will override/use the region/locale of the system (OS) where you are opening the file, and you cannot change this behavior. For example, on a US locale/region, “.” would be used as decimal point in Excel. For other systems with different regions/locales, it will be shown differently according to their locale settings. By the way, if you render to PDF, it will use language settings/region set in code in the output PDF.
But then how can I convert this value into a numeric value while keeping its real value? I have tried
workbook = ac.Workbook("example.xml")
workbook.settings.language_code = ac.CountryCode.USA
worksheet = workbook.worksheets[0]
cell = worksheet.cells.get_cell(3,3)
style = cell.get_style()
style.number = 1
cell.set_style(style)
print(cell)
which then gave me
Aspose.Cells.Cell [ D4; ValueType : IsNumeric; Value : 16619 ]
Either you need to set value as “text/string” or set custom formatting (e.g., “[$-en-US]#,##0.00_);([$-en-US]#,##0.00)”) to the cell(s) in code.
How can I do this? I have tried
style.number = 49
which should be setting it as text, however the output is the same as above.
You may try to re-insert the value of the relevant cells as string. See the sample code for your reference.
e.g.,
Sample code:
workbook = ac.Workbook("e:\\test2\\example.xml")
workbook.settings.language_code = ac.CountryCode.USA
worksheet = workbook.worksheets[0]
cell = worksheet.cells.get_cell(3,3)
cell.value = cell.string_value
print(cell)
workbook.save("e:\\test2\\Output_aspose.xlsx")
Hope, this helps a bit.
This does work for this specific value, however I have other values in my files that it does then wrongly convert.
Here is my code where I loop over all the cells in the file and convert them to strings:
import aspose.cells as ac
workbook = ac.Workbook("example.xml")
workbook.settings.language_code = ac.CountryCode.USA
worksheet = workbook.worksheets[0]
cols = worksheet.cells.max_data_column
rows = worksheet.cells.max_data_row
for col in range(1,cols+1):
for row in range(1,rows+1):
cell = worksheet.cells.get_cell(row,col)
val = worksheet.cells.check_cell(row,col)
if (str(val) != "None"):
cell.value = cell.string_value
workbook.save("Output_aspose.xlsx")
The input and output files are attached. The value under LastEdit is the one that is now wrong.
example2.7z (7,3 KB)
@Mika13
Please refer to the following example code to specify not to convert dates and numbers when loading XML files. Please refer to the attachment. example_out.zip (7.1 KB)
import aspose.cells as ac
options = ac.XmlLoadOptions()
options.convert_numeric_or_date = False
options.region = ac.CountryCode.USA
workbook = ac.Workbook("example.xml", options)
workbook.save("example_out.xlsx")
Thanks a lot, that did the trick!
Good to know that by specifying not to convert dates and numbers option while loading the XML file sorts out your issue. Please feel free to write us back if you have further queries or comments.
I tested it and I think that
options.region = ac.CountryCode.USA
actually is enough to fix the issue. With the before provided
workbook.settings.language_code = ac.CountryCode.USA
the region only got set to USA once the workbook was already wrongly loaded.
Yes, you are spot on. Setting region is enough while loading the XML file, so it is not necessarily needed second time.