Aspose Cells for Python - Converting excel sheet to PDF results in Number formatting getting changed

Aspose.Cells Product Family
1

I am using Aspose Cells for Python (evaluation version) to convert a simple excel sheet to pdf in Python. PDF gets created and everything is great. However i am facing a small issue. I live in India and i have my formats set accordingly in my windows and linux machines. Input Excel has the correct number format (as per India Locale) but when it converts to PDF, the number format changes to en_US. I have used another python library to double check the conversion and using the other library, conversion happens with the correct number format (although I face a different issue in using this alternative, so have to use aspose.cells for python only for my purpose). Can you please guide me at least with a dirty fix to set the specific format / locale / region within aspose cells so that the conversion happens with the correct number format. Most of the examples given online are for .NET or Java and when I try that it doesnt work in aspose cells python. So please help. My current code snippet is given below:

EDIT: I have also confirmed that if I simply read the excel in the below code and write to the xlsx format to a different excel filename, resultant excel file retains the number formats. I think the messing up happens only during the conversion to PDF, I guess.

import jpype

import asposecells

import datetime

jpype.startJVM()

from asposecells.api import Workbook, FileFormatType, PdfSaveOptions, HtmlSaveOptions, SheetSet

datetoday = datetime.datetime.now().strftime(‘%d%m%Y’)

filename = ‘portfolio_tracker_’ + datetoday + ‘.xlsx’

workbook = Workbook(filename)

saveOptions = PdfSaveOptions()

saveOptions.setOnePagePerSheet(True)

saveOptions.setCalculateFormula(True)

saveOptions.setSheetSet(SheetSet([0]))

workbook.save(“Portfolio_Tracker.pdf”, saveOptions)

jpype.shutdownJVM()

2

@balajith,

Please specify your desired Region or CultureInfo in workbook settings after you open the Excel file using workbook object for your needs:

workbook.getSettings().setRegion(......);

Let us know if you still find any issue

3

Thank you for your suggestion. Have tried it now and can confirm it does not work.

As i mentioned in the edit to my original post, issue seems to be only during the pdf conversion. when I just read the source excel and save it to another excel file (with a different name) using aspose cells for python, the resultant file has the correct number format (same as the source excel). So it seems to me to be some issue during the pdf conversion.

4

@balajith,

Good to know that your issue is resolved by specifying the region/locale for your needs.

Please note, Aspose.Cells uses the underlying locale/regional settings of the environment/OS where the code is processed when rendering to PDF, so either you need to set/update regional/locale settings of the environment (manually) accordingly or set your desired region in code (as suggested).

5

Hi Amjad,

Sorry if i didn’t make it clear. I mentioned in my reply that using your suggestion DOES NOT work. So the issue remains. Thanks for your time.

6

Also the locale is correctly set in my machine environment. Reason I can confidently says this is because:

  1. Using aspire.cells python itself when I read the source excel (that has correct format) and write it to a different named excel sheet, resultant excel sheet also has correct format

  2. When I use a different python library to convert the very same source excel to pdf, output pdf displays correct number formatting.

So I strongly suspect that something is going on in pdf conversion. Logically I tried to look at pdfSaveOptions but I couldn’t find one that could help me.

7

@balajith,

Kindly provide the following details to investigate your issue thoroughly:

  1. The source XLSX file
  2. The output PDF file generated by the latest version of Aspose.Cells for Python via Java
  3. Your sample code that you are currently using
  4. The expected PDF file
  5. Your environment details, including Python version, OS, JDK version, etc.

Once we receive the above files and artifacts, we will commence the investigation.

Note: Please ensure to zip the resource files before attaching them here.

8

Hi Amjad,

Thanks so much for reverting. Here are the details you asked for. Have attached a zip file that contains the following:

  1. portfolio_tracker_17072024.xlsx - Input excel file that needs to be converted to pdf.
  2. PT.py - code snippet i am using to get the above done
  3. Portfolio_Tracker.pdf - Output of the above code, which is the converted pdf version of the above excel file

as you can see everything is perfect, except for the number formatting. If you look at values in columns titled ‘Cost Value’, ‘Present Value’, ‘Total Gains’ etc in the pdf and in the excel, you will see the same value, but formatted differently. For example in xlsx, it is formatted in the Indian way (crores, lakhs, thousands etc) whereas in the pdf it is formatted the US way (milions, thousands etc).

This is happening in both windows 11 and linux (ubuntu Server 24.04), python version is 3.12.4, java version “1.8.0_411”.

Hope this helps. Let me know. Thanks for your time and effort.

from the output of java -XshowSettings -version in my windows 11 terminal, below is locale setting values:

Locale settings:
default locale = English
default display locale = English (India)
default format locale = English (India)
data.zip (75.2 KB)

data.zip (75.2 KB)

9

@balajith,

Thanks for the sample files.

I tested your scenario/case using the following sample code with your template file:
e.g.,
Sample code:

# Convert the Input xlsx to PDF
import jpype
import asposecells
import locale
import datetime

jpype.startJVM()
from asposecells.api import Workbook, FileFormatType, PdfSaveOptions, HtmlSaveOptions, SheetSet, CountryCode

# Set the locale to US English
locale.setlocale(locale.LC_ALL, 'en_IN')

# Print the current locale settings
print(locale.getlocale())

datetoday = datetime.datetime.now().strftime('%d%m%Y')
filename = 'portfolio_tracker_' + datetoday + '.xlsx'

workbook = Workbook(filename)

workbook.getSettings().setRegion(CountryCode.INDIA)
# Print the current locale settings
print(locale.getlocale())

saveOptions = PdfSaveOptions()
saveOptions.setOnePagePerSheet(True)
saveOptions.setCalculateFormula(True)
saveOptions.setSheetSet(SheetSet([0]))
workbook.save("Portfolio_Tracker.pdf", saveOptions)

jpype.shutdownJVM()

I got the same results in the output PDF file as per your output PDF file.

I then opened your Excel file in MS Excel manually and checked its display, which was the same as the output PDF generated by Aspose.Cells for Python via Java. I changed/updated the locale/regional settings to English (India). Then I opened your template Excel file in MS Excel manually again. I found no change in the display, which is the same as the default regional settings. In short, there may not be any issue with the formatting display in the PDF generated by Aspose.Cells.

Anyways, we would love to evaluate your issue in detail. Could you please share a screenshot to show what is displayed in MS Excel on your end? Moreover, provide your expected PDF file. Also, please share a screenshot of the regional/locale settings of the OS on your machine.

10

@balajith

We tested your template file with locale “hi_IN”. In ms excel the formatted result looks like: “1,23,45,67,89,012”. Is it the expected result for you? If true, we will create one ticket for it and try to implement it later. Currently we are afraid we do not support such kind of special formatting.

11

Hello Amjad,

Have attached the details you have asked for. Hope that suffice for your analysis.

Regards
Balaji
Aspose Cells Python Conversion Issue.docx (1.3 MB)

12

No, my locale is not hi_IN. my locale is en_IN. I have sent an attachment detailing the issue to Amjad in the same thread. You can have a look

13

@balajith
Thank you for your feedback. By testing with sample code and files on the latest version v24.7, we can reproduce the issue. Found that converting excel to PDF results in Number formatting getting changed using Aspose Cells for Python via JAVA.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSPYTHONJAVA-95

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

14

The issues you have found earlier (filed as CELLSPYTHONJAVA-95) have been fixed in this update.

15

Thanks, team. I tested using aspose-cells 24.8.1 for python and found that the issue is fixed.

16

@balajith
Thank you for your feedback. You are welcome. If you have any questions, please feel free to contact us at any time.