Problems when exporting excel to pdf

Aspose.Cells Product Family
1

The sample excel file and expected output is attached.



1. Export the area with data or style, ignore the cells without style or global style

2. Ensure the entire picture is exported in the pdf



Is there any sample code to achieve this?



delete blank rows and columns will not work because:

1. the ones with boders but empty data will also be deleted

2. the empty columns in the left of last column will also be deleted, and the same as the rows above the last row.



Set setPrintArea also cannot work because I don’t know where is the pictures bottom and right.



Thanks a lot if you can provide any solution.

2

Hi Huangfu,

Thanks for your posting and using Aspose.Cells.

Please see the following sample code for your reference. It achieves your desired results. I have attached the output PDF generated by it for your reference.

The code is fully commented so you will not find it difficult to understand. It sets the print area by finding the maximum display range and set all margins to 0 and set one page per sheet true so that each worksheet will be displayed in a single pdf page.

Java


String filePath = “F:\Shak-Data-RW\Downloads\test+(2).xls”;


//Create workbook from source file

Workbook workbook = new Workbook(filePath);


for(int i = 0; i< workbook.getWorksheets().getCount(); i++)

{

//Access the worksheet

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


//Access the maximum display range

Range range = worksheet.getCells().getMaxDisplayRange();


//Set the print area of worksheet

worksheet.getPageSetup().setPrintArea(“A1:” + CellsHelper.cellIndexToName(range.getRowCount()-1, range.getColumnCount()-1));


//Set all margins 0

worksheet.getPageSetup().setTopMargin(0);

worksheet.getPageSetup().setBottomMargin(0);

worksheet.getPageSetup().setLeftMargin(0);

worksheet.getPageSetup().setRightMargin(0);


}


//Set one page per sheet

PdfSaveOptions options = new PdfSaveOptions();

options.setOnePagePerSheet(true);


//Save the workbook into pdf

workbook.save(“output.pdf”, options);

3

Thanks a lot. But it failed to work for the attached file.

Can you help on this?

4

And another problem, you can see in sheet3, the word is truncated since it is longer than the cell width

5
huangfujunyan:
Thanks a lot. But it failed to work for the attached file. Can you help on this?


Hi,

Thanks for your posting and using Aspose.Cells.

The following code generates correct Pdf. Now it uses worksheet.getCells().getMaxDataRow() and worksheet.getCells().getMaxDataColumn() properties to calculate the Print Area.

I have attached the output pdf generated by the code for your reference.

Java
String filePath = "F:\\Shak-Data-RW\\Downloads\\预算.xlsx";

//Create workbook from source file
Workbook workbook = new Workbook(filePath);

for(int i = 0; i< workbook.getWorksheets().getCount(); i++)
{
//Access the worksheet
Worksheet worksheet = workbook.getWorksheets().get(i);

//Set the print area of worksheet
worksheet.getPageSetup().setPrintArea("A1:" + CellsHelper.cellIndexToName(worksheet.getCells().getMaxDataRow(), worksheet.getCells().getMaxDataColumn()));

//Set all margins 0
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setRightMargin(0);

}

//Set one page per sheet
PdfSaveOptions options = new PdfSaveOptions();
options.setOnePagePerSheet(true);

//Save the workbook into pdf
workbook.save("output.pdf", options);

6
huangfujunyan:
And another problem, you can see in sheet3, the word is truncated since it is longer than the cell width

Hi,

As a workaround, you can autofit the last column so that your truncated word could also be displayed. Please use worksheet.autoFitColumn(columnIndex) method for this purpose.
7

Thanks a lot. Auto fit works

8

auto fit works for text in column.
But cannot work for pictures.

How to deal with picture in this case?

9

Hi Huangfu,

Thanks for your posting and using Aspose.Cells.

You can use the shape.getLowerRightRow() and shape.getLowerRightColumn() to know the lower right cell.

Please see the following sample code, it successfully works with your both Excel files and generate correct pdf. I have attached the output PDFs generated by the code for your reference.

Java


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


Workbook workbook = new Workbook(filePath);


for (int i = 0; i < workbook.getWorksheets().getCount(); i++)

{

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


//Calculate the Last Cell


//Iterate all shapes and find the last cell.

int maxRow=0, maxCol=0;


for (int j = 0; j < worksheet.getShapes().getCount(); j++)

{

Shape shape = worksheet.getShapes().get(j);


if (maxRow < shape.getLowerRightRow())

maxRow = shape.getLowerRightRow();


if (maxCol < shape.getLowerRightColumn())

maxCol = shape.getLowerRightColumn();

}


//Compare the maxRow and maxColumn with maxDataRow and maxDataColumn

if (maxRow < worksheet.getCells().getMaxDataRow())

maxRow = worksheet.getCells().getMaxDataRow();


if (maxCol < worksheet.getCells().getMaxDataColumn())

maxCol = worksheet.getCells().getMaxDataColumn();



String lastCell = CellsHelper.cellIndexToName(maxRow, maxCol);


worksheet.getPageSetup().setPrintArea(“A1:” + lastCell);


//Set all margins 0

worksheet.getPageSetup().setTopMargin(0);

worksheet.getPageSetup().setBottomMargin(0);

worksheet.getPageSetup().setLeftMargin(0);

worksheet.getPageSetup().setRightMargin(0);


//See if you need to autofit last column

//If autofit makes your column smaller than it is, then reset it

double colWidth = worksheet.getCells().getColumnWidth(maxCol);

worksheet.autoFitColumn(maxCol);


if (colWidth > worksheet.getCells().getColumnWidth(maxCol))

worksheet.getCells().setColumnWidth(maxCol,colWidth);


}


//Set one page per sheet

PdfSaveOptions options = new PdfSaveOptions();

options.setOnePagePerSheet(true);


//Save the workbook into pdf

workbook.save(“output.pdf”, options);

1 Like
10

Thanks a lot.
That help me a lot