You can take a look at the attached excel file.
There are two problems when converted to pdf.
1. The picture in sheet1 will cover the text
2. When calling sheet.getCells().deleteBlankRows();
the row inside the table will also be deleted.
Is it possible to only delete the blank rows outside the table?
The code to convert is as following:
Workbook workbook = new Workbook(sourcePath);
WorksheetCollection collection = workbook.getWorksheets();
for (int i = 0; i
Worksheet sheet = collection.get(i);
sheet.getCells().deleteBlankColumns();
sheet.getCells().deleteBlankRows();
sheet.getPageSetup().setHeader(1, “&K9B9B9B&A”);
sheet.getPageSetup().setPrintArea("");
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
workbook.save(targetPath, pdfSaveOptions);
Hi,
I have tested your scneario/ case a bit using your code segment, it works as expected.
For your issues/ concerns:
1) Well, for your information, setting setAllColumnsInOnePagePerSheet() option to true will ensure that all the columns would be rendered on the single page, this option is somewhat equal to Fit to Pages Wide = 1 option in Ms Excel. So, the text would be moved to the first page under the picture. You may just use the following sample code and check the output file (attached) here. You will see the similar output as per your attached PDF file format:
e.g
Sample code:
Workbook workbook = new Workbook(“test(1).xls”);
WorksheetCollection collection = workbook.getWorksheets();
for (int i = 0; i < collection.getCount(); i++)
{
Worksheet sheet = collection.get(i);
sheet.getCells().deleteBlankColumns();
sheet.getCells().deleteBlankRows();
sheet.getPageSetup().setHeader(1, “&K9B9B9B&A”);
sheet.getPageSetup().setPrintArea("");
sheet.getPageSetup().setFitToPagesWide(1);
}
workbook.save(“outtest1.xls”);
Aspose.Cells works the same way as per Ms Excel, so this is not the issue in Aspose.Cells by any means.
2) Yes, this is expected. For your information, Cells.deleteBlankRows() would delete all the rows having no data even if the cells(in the rows) are formatted, so those two rows in the table are blank but formatted (borders are set etc.) and would be removed.
To delete the specific rows out of the table, I am afraid, you have to remove them manually using the Aspose.Cells APIs for your requirements.
Thanks for your understanding!
Then how can I achieve the following purpose using Aspose Cell?
1. Fit to page width but keep the pictures not moved.
1.1 More over, it’s better to keep all the sheet the same wide, but fit to the widest sheet, leave blank on the right if the sheet is narrower than the widest sheet. (If I can detect the width of the sheet, I can achieve this)
2. How to avoid blank (unformatted) rows and columns printed if exported to pdf.
Can aspose achieve the above requirements and are there any sample code?
Hi,
1) How could you achieve this in MS Excel manually, please use your input template file and accomplish your task in Ms Excel manually, save the file as per your expectations and provide us here. Also give the steps involved on how you get your desired output using MS Excel features, we will check it soon.
2) As I told you earlier, Cells.deleteBlankRows()/ Cells.deleteBlankColumns would remove the rows/columns which are blank irrespective if those rows/ columns are formatted or not formatted.
Thank you.
I don’t know how to achieve it in ms excel.
But I want to achieve it via aspose excel.
See the detail here: Problems when exporting excel to pdf