Free Support Forum - aspose.com

Expand cells in xls before converting to pdf

I am a first time user, apologize if this question is already answered before. I am using the Aspose.Cells for Java.

MS Excel will display large numbers in cells using the scientific notation format. If I use aspose to convert to pdf, the pdf also shows scientific notation format. Understand, it is converting and displaying how the xls is.

But is there any option to tell aspose to expand to full value and then convert to pdf?

My current java code,

//Instantiate a new workbook with excel file path
Workbook workbook = new Workbook(“C:\test\test.xls”);

//Save the document in Pdf format
workbook.save(“C:\test\test.pdf”, FileFormatType.PDF);

Hi,

Thanks for your question and using Aspose.Cells for Java.

Aspose.Cells generate PDF according to Ms-Excel preview. If you want to display differently as per your requirements you will have to do changes in your workbook.

For example, you will have to convert your scientific numbers into normal numbers programmatically using Aspose.Cells API(s) before saving your workbook into PDF format.

Suppose your cell A1 has a number 2.33E+04, then you will have to retrieve its Style object and set the number format to general and then save your workbook into PDF.

Please see the sample code below for your reference. I have attached the source file, output pdf file and screenshot for your reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“A1”);


//First check the string value of this cell

System.out.println(cell.getStringValue());


//Now set its style to general value

Style st = cell.getStyle();

st.setNumber(0);

cell.setStyle(st);


worksheet.autoFitColumns();


workbook.save(“output.pdf”, SaveFormat.PDF);

Output: (string value of cell A1)
2.33E+04

Screenshot:

Thanks for quick reply!

I intend to use aspose to handle a bunch of xls in a batch mode and I have no prior knowledge of what the content might be and which cells will actually have the numbers or text.

Wondering if there is way to expand ALL cells in xls? if we could do that, then probably pdf wont have the scientific numbers.

This will help us in txt cells too. If a column is shrunk in width, the text in the cell is also partly hidden in the converted pdf.

Hi,

There is no direct way. One way is to save your xls file into csv file and the load your csv file and convert it to pdf. But it will lose all formatting too.

Another way is to find all the cells with scientific numbers and convert them into simple numbers as I have shown above. This way, you will not lose any formatting at all.

To do so, you will have to iterate all cells using row and column indices and then access the cell, then retrieve the Style object and then checking Style.getNumber() returned value. If the value is 11, it means, it is a scientific number.

For more help, please see the following article.


You can get rid of shrunk columns by calling worksheet.autofitColumns() method.