Pagebreak in Excel

Aspose.Cells Product Family
1

Hi,

I have a scenario, where I am exporting the data to excel in the form of a tables.

The tables are almost of 10-12 rows in length based on some selection criteria while generating the report.

When I export the data to excel and see the print preview, I find that table is being splitted across the pages and I would like to bring them to next page.

Please help me on resolution of above issue.

Can I insert page break between these tables? if YES please guide me.

Thank You.
Kailas

2

Hi Kailas,

Thank you for considering Aspose.

Yes, you can add Page breaks to the worksheets both vertically and horizontally by using the Aspose.Cells APIs, Please see the following sample code in this regard,

Workbook workbook = **new** Workbook();
workbook.open("C:\\input.xls");
Worksheets worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.getSheet(0);
//Add page break
worksheet.addPageBreaks("B2");
//OR
//Horizontal Page Break
worksheet.getHPageBreaks().add("B12");
// To Add Vertical Page Break
// worksheet.getVPageBreaks().add("B12");
workbook.save("C:\\output.xls");

Also, please see our online demo for your reference,
https://github.com/aspose-cells/Aspose.Cells-for-Java

Thank You & Best Regards,

3

Hi,

Initially FitToPagesWide & FitToPagesTall is Automatic.

I want the FitToPagesWide to be Automatic & FitToPagesTall to be 1.

When I change FitToPagesWide to 1 and leave FitToPagesTall as is, it is setting both FitToPagesWide & FitToPagesTall to 1. Due to which I am not able see the pagebreaks.

Please find the attached simulation program.

Thank You.
Kailas

4

Hi Kailas,

Thank you for considering Aspose.

Yes, we have found your mentioned issue after an initial test. We will fix it soon.

Thank You & Best Regards,

5

Hi Kailas,

Thank you for considering Aspose.

Please try the attached latest fix of Aspose.Cells. We have resolved your reported issue regarding FitToPagesWide & FitToPagesTall.

Thank You & Best regards,

6

Hi Nausherwan,

The issue does not seem to be fixed.

Please try with the attached excel file. This excel file is generated from my program.

Now when I try to set the fit to page wide to 1, it sets bithe fit to page wide and fit to page tall to 1.

Use the main program attached in the previous post.

Thank You.
Kailas

7

Hi Kailas,

Thank you for considering Aspose.

Yes, we have found the issue after an initial test. The issue is caused due to the default value of FitToPageTall and FitToPageWide as 1 by MS Excel. If you remove these values from template file, the functionality works fine. We will fix it and get back to you soon.

Thank You & Best Regards,

8

Hi Kailas,

Thank you for considering Aspose.

After further investigating your issue we came to know that it is not a bug of Aspose.Cells. Aspose.Cells just does same as MS Excel.

For your first template file(excelOutput.xls in PageBreak.zip), the default values for FitToPagesWide and FitToPagesTall are 0(automatic). When you only set the FitToPagesWide to 1, FitToPagesTall will be kept its original value (0) both by MSEXCEL and Aspose.Cells.

For your second template file (excelOutput.xls), the default values of FitToPagesWide and FitToPagesTall are 1. If you do not want the FitToPagesTall to be 1, you have to modify the FitToPagesTall value manually both by MSEXCEL and Aspose.Cells. Please try the attached fix, the new version allow to setFitToPagesWide/Tall as 0(automatic) and you can add following code to get your desired result.

ps.setFitToPagesTall(0);

Thank You & Best Regards,

9

Hi Nausherwan,

Thank You for your quick responses.

With the jar you have provided we are facing following issue.

Error Message: The subscription included in this license allows free upgrades until 2009-04-07, but this version of the product was released on 2009-04-24. Please renew the subscription or use a previous version of the product.

Please let me know what can be done in this case.

Thank You.
Kailas

10

Hi Kailas,

Thank you for considering Aspose.

This error message means that your license subscription has expired and you need to renew your license subscription to use our latest fixes. Please contact Aspose.Purchase to renew your license subscription. Following is the link of Aspose.Purchase forum.

Thank You & Best Regards,

11

Hi Nausherwan,

In the fix you provided, we didn’t ask for any new features. We have just got the issue fixed which we were facing in earlier versions.

Please let me know if you can provide the same fix with the previous version Aspose.Cells.V2.0.1.5 we were using.

Thank You.
Kailas Kore

12

Hi Kailas,

Thank you for considering Aspose.

Well, we don’t have such policy to update some older version to incorporate new functionality/ fix to re-build it and give it to the user. If you want to correct your issue, you should use our latest patch only, so, consequently, you need to upgrade your subscription. We cannot rollback current version to older one and make a fix on it.

Thank you for understanding,

13

Hi Nausherwan,

Excel Page break is working fine with the Aspose jar which you have given fix with the following piece of code in windows environment.

PageSetup summaryPageSetup = Worksheet.getPageSetup();

summaryPageSetup.setPaperSize(PaperSizeType.PAGE_LETTER);

summaryPageSetup.setOrientation(PageOrientationType.LANDSCAPE);

summaryPageSetup.setFitToPagesTall(0);

summaryPageSetup.setFitToPagesWide(1);

Worksheet.getHPageBreaks().add("B60");

Now the same piece of code Excel page break is not working in Sun Solaris environment for some scenario.But the same scenario is working in Windows.

Please help me to resolve this issue.

Regards,

Karuppiah M

14

Hi Karuppiah,

Thank you for your feedback.

We will get back to you regarding your issue.

Thank You & Best Regards,

15
Hi Nausherwan,
Please let us know any update on this issue?
Thanks & Regards
Karuppiah M
16
Hi Karuppiah,
Thank you for considering Aspose.
We have tested both the fix(2.0.1.8) attached in previous post and our latest released version(2.1.0) on LINUX OS (as currently we don’t have Solaris available for testing) and the jar works fine for your code.
Please make sure you are using the right jar on your system. If you still face the issue, please give us more details such as the Exception message and stack trace so we can get a hint to figure out the issue. Also, if you can successfully generate a file in Solaris, please share the file and we will check if we can figure out the issue by looking into your output file.
Thank You & Best Regards,
17

Hi,

Please find the attached zip file contains the sample output Excel file which was generated after applying the page break.

If you see this excel file Page break up is not proper when we compare to windows environment.

Problem only in Sun Solaris but its working with windows environment.

Please provide us the fixes. Thanks in advance.

Thanks & Regards,

Karuppiah M

18

Hi Karuppiah,

Thank you for considering Aspose.

We will look into your shared file and get back to you soon.

Thank You & Best Regards,

19

Hi Karuppiah,

Thank you for considering Aspose.

It seems that the file you attached is not generated by Aspose.Cells for Java because there are some records that Aspose.Cells for Java would never write. Or may be you have opened the generated file by MS Excel and resaved it?

We are not very clear about what’s the difference between files generated by Windows and Sun Solaris. Would you please point it out for us and give us more detail info such as the template file, the code to generate file, and the generated files by Windows and Sun Solaris? This will help us figure out the issue soon.

Thank you for you support in this regards,

20

Hi Nausherwan,

Thanks for the respose. Yes you are correct we have manually edited some of the fields in the excel file.

We will get back to you with the right excel file which has been generated by the code in windows and Sun solaris.

Thanks & Regards,

Karuppiah M