Saving in PDF using aspose cells

image.png (16.3 KB)
How can I configure PDF file to have following:
1- Adjust font size.
2- Table titles should not be truncated.
3- Avoid overlapping of column values and wrap them if possible.
4- Fit all columns in one page only.

Please review if this all is possible using aspose else I will need to use ITEXT.

@amitkumartrips

Thanks for considering Aspose APIs.

Please provide us your sample Excel file and expected Pdf file so that we could test this issue at our end and provide you a sample code to achieve the expected Pdf file using Aspose.Cells.

The following code saves the Excel file to Pdf and renders the entire worksheet on single Pdf page.

Please also see the sample Excel file and output Pdf generated by the code.

Sample Excel File and Output Pdf.zip (18.8 KB)

C#

//Load source Excel file
Workbook wb = new Workbook("sample.xlsx");

//Specify pdf save options - we want entire sheet on single pdf page
PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;

//Save the workbook with pdf save options
wb.Save("output.pdf", opts);

Hi Shakeel,

Thanks for your response, In my case PDF is generated however headers and values are truncated, I guess this is because the generated excel file does not auto fit column width based on content, do you know how to fix excel file to allow auto fit column widths and also text wrapping does not seem to work:
Field field = reportDto.getClass().getDeclaredField(tableHeader.getMappedProperty());
field.setAccessible(true);
cell.setValue(field.get(reportDto));
cell.getStyle().setTextWrapped(true);
cell.getStyle().getFont().setBold(false);

Sorry I can’t provide you with actual data since that is not allowed, however I would attach a screen shot of pdf here:
image.png (17.7 KB)

The problem here are as follows:
1- Column headers are truncated for example 1st Sub becomes 1st only.
2- The values are overlapping and are not wrapping up.

I even tried doing following to get columns and rows auto fit:
worksheet.autoFitColumns();
worksheet.autoFitRows();

This does not work though.
Please review.

@amitkumartrips

Please see the following code in Java (as well as in C#). The code explains how to auto-fit rows and columns, how to set the style of the cell. Please see the output Excel and output Pdf files generated by the code as well as screenshot for a reference.

Please study the comments inside the code and modify it as per your needs.

Output Excel and Pdf Files.zip (26.9 KB)

Java

//Create workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Access cell A1 and write some text
Cell a1 = ws.getCells().get("A1");
a1.putValue("This is long text. We will autofit it.");

//Access cell C4 and write some text
//Note this is multi-line text. It contains '\n'
Cell c4 = ws.getCells().get("C4");
c4.putValue("This is multiline.\nThis is second line\nThis is third line.");

//Wrap the text in cell C4 and also make it bold and red color
//You must also set the style to work it correctly
Style st = c4.getStyle();
st.setTextWrapped(true);
st.getFont().setBold(true);
st.getFont().setColor(Color.getRed());
c4.setStyle(st);

//Set the width of third column
ws.getCells().setColumnWidth(2, 30);

//Autofit all columns or just first column
ws.autoFitColumn(0);

//Autofit rows - we want to autofit only auto rows
AutoFitterOptions opts = new AutoFitterOptions();
opts.setOnlyAuto(true);
ws.autoFitRows(opts);

//Save to xlsx format
wb.save(dirPath + "output.xlsx");         

//Save to pdf format
wb.save(dirPath + "output.pdf");

C#

//Create workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Access cell A1 and write some text
Cell a1 = ws.Cells["A1"];
a1.PutValue("This is long text. We will autofit it.");

//Access cell C4 and write some text
//Note this is multi-line text. It contains '\n'
Cell c4 = ws.Cells["C4"];
c4.PutValue("This is multiline.\nThis is second line\nThis is third line.");

//Wrap the text in cell C4 and also make it bold and red color
//You must also set the style to work it correctly
Style st = c4.GetStyle();
st.IsTextWrapped = true;
st.Font.IsBold = true;
st.Font.Color = Color.Red;
c4.SetStyle(st);

//Set the width of third column
ws.Cells.SetColumnWidth(2, 30);

//Autofit all columns or just first column
ws.AutoFitColumn(0);

//Autofit rows - we want to autofit only auto rows
AutoFitterOptions opts = new AutoFitterOptions();
opts.OnlyAuto = true;
ws.AutoFitRows(opts);

//Save to xlsx format
wb.Save("output.xlsx");         

//Save to pdf format
wb.Save("output.pdf");

Screenshot: