How to get Page count of a work sheet programmatically

tauqeer.phoplunkar · October 6, 2015, 3:33am

Hi All,

I am looking for API to get default page count of a worksheet using aspose cell .

If I add Horizontal page break in a worksheet then I get total PageCount.

Below is code snippet.

worksheet.getHorizontalPageBreak().add(20);

worksheet.getHorizontalPageBreak().getCount() gives valid page count.

But i dont add page break then worksheet.getHorizontalPageBreak().getCount() gives 0. even though print preview it show more then 1 page of a worksheet.

amjad.sahi · October 6, 2015, 3:49am

Hi,

Thanks for your query.

Well, I think you may try to use SheetRender APIs, see the sample code below on how to get total number of pages for a worksheet:

e.g

Sample code:

Workbook workbook = new Workbook(“e:\test\book1.xls”);

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

ImageOrPrintOptions printoption = new ImageOrPrintOptions();

printoption.setPrintingPage(PrintingPageType.DEFAULT);

SheetRender sr = new SheetRender(worksheet, printoption);

int pageCount = sr.getPageCount();

Let us know if I can be of any further help.

Thank you.

tauqeer.phoplunkar · October 6, 2015, 4:27am

Thanks a lot. It’s working now.

amjad.sahi · October 6, 2015, 4:41am

Hi,

Good to know that your issue is sorted out now. Feel free to contact us any time if you have further queries or issue, we will be happy to assist you soon.

Thank you.

tauqeer.phoplunkar · October 7, 2015, 2:35am

Workbook workbook = new Workbook("e:\\test\\book1.xls");

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

ImageOrPrintOptions printoption = new ImageOrPrintOptions();

printoption.setPrintingPage(PrintingPageType.DEFAULT);

SheetRender sr = new SheetRender(worksheet, printoption);

int pageCount = sr.getPageCount();

Above code working fine and gives correct page count when I add page break programmatically using below line.

outPutWorkSheet.getHorizontalPageBreaks().add(pageBreakIndex);

But if I don't set page break programmatically. then page count by sheetrender and printpreview are different .and this different comes when data in worksheet is big around 1000 rows. for 200-300 rows there is no difference.

Kindly sugguest.

amjad.sahi · October 7, 2015, 2:53am

Hi,

Thanks for providing us further details.

I think you may try to use Worksheet.getPrintingPageBreaks() get the automatic page breaks in the worksheet.

e.g

Sample code:

…

CellArea [] area = worksheet.getPrintingPageBreaks(printoption);

System.out.println(area.length);

If you still have any issue, kindly attach your template Excel file here, we will check it soon.

Thank you.

tauqeer.phoplunkar · October 9, 2015, 6:01am

Hi

issue is not resolved after using

CellArea [] area = worksheet.getPrintingPageBreaks(printoption);

System.out.println(area.length);

I have attached file. which is generated by Aspose cell.

if first sheet (CoverLetter) is showing page count 39 of second sheet. but in print preview to page is 42.

Please note the page count is counted before workbook save on machine.

amjad.sahi · October 9, 2015, 6:59am

Hi,

Thanks for the template file.

Please try our latest version/fix: Aspose.Cells for Java (Latest Version)

I have tested using your template file with the following sample code, it shows correct page count:

e.g

Sample code:

Workbook workbook = new Workbook(“PAGECount.xlsx”);

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

ImageOrPrintOptions printoption = new ImageOrPrintOptions();

printoption.setPrintingPage(PrintingPageType.DEFAULT);

SheetRender sr = new SheetRender(worksheet, printoption);

int pageCount = sr.getPageCount();

System.out.println(pageCount);

CellArea [] area = worksheet.getPrintingPageBreaks(printoption); 

System.out.println(area.length);

Let us know if you still find the issue with latest version/fix v8.6.0.3.

Thank you.

tauqeer.phoplunkar · October 12, 2015, 4:50am

Hi Amjad,

What we are doing

we are creating Template first

then calculate the page count.

then save the file

Below is the sample code.

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

Cells cells = worksheet.getCells();

cells.insertRows(,)

ImageOrPrintOptions printoption =

new ImageOrPrintOptions();

printoption.setPrintingPage(PrintingPageType.

DEFAULT);

SheetRender sheetRender =

new SheetRender(worksheet, printoption);

pageCount+= sheetRender.getPageCount();

we save workbook in last

workbook.save()

Does above process have different result?

we are facing this issue when there are more than 800 rows. for 100-200 rows there is no issue.

Kindly help

amjad.sahi · October 12, 2015, 6:58am

Hi,

Thanks for providing further details.

I am afraid, to figure out your issue, we need to reproduce the issue first. Kindly create a sample JAVA program (runnable) and provide complete code ( or JAVA file) here, so we could compile it and run it at our end with your template file (please also attach it here if you have any) to reproduce the issue, we will check it soon.

Thank you.

tauqeer.phoplunkar · October 23, 2015, 12:52am

Hi,

Below is code snippet the similar thing I am doing in my application.

result of Page count via code and in print preview, is different. Please find the input and output file in attachment

import com.aspose.cells.Cells;
import com.aspose.cells.ImageOrPrintOptions;
import com.aspose.cells.PrintingPageType;
import com.aspose.cells.SheetRender;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class TestPrintOption {

public static void main(String[] args) throws Exception {

Workbook workbook=new Workbook("C:\\test\\TestPrintOption.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
Workbook outputWorkbook = new Workbook();
Worksheet outputWorkSheet = outputWorkbook.getWorksheets().get(0);
outputWorkSheet.copy(worksheet);
Cells cells = outputWorkSheet.getCells();
cells.insertRows(1, 10000);

for(int i=1;i<=10000;i++) {
cells.copyRow(cells, 0, i);
}
cells.deleteRows(1, 20);
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
printoption.setPrintingPage(PrintingPageType.DEFAULT);
SheetRender sheetRender = new SheetRender(outputWorkSheet, printoption);
System.out.println(sheetRender.getPageCount());
outputWorkbook.save("C:\\test\\TestPrintOption_output.xlsx");

}
}

babar.raza · October 23, 2015, 1:47am

Hi Tauqeer,

Thank you for sharing the samples.

We have evaluated the presented scenario while using the latest version of Aspose.Cells for Java 8.6.0.7, and we are able to notice the issue, that is; the page count returned for the final spreadsheet does not match with the page count shown in the Excel Print Preview. Please note, Aspose.Cells for Java API returns the page count as 222 whereas it should be 233 as per Excel Print Preview. We have logged this incident in our bug tracking system as CELLSJAVA-41540. Our product team will further look into the details of this problem and we will keep you updated on the status of correction. We apologize for your inconvenience.

babar.raza · October 23, 2015, 3:35am

Hi again,

This is to update you that we have looked further into the matter. Please note, the said issue if caused because the row heights are automatic, that mean; when spreadsheet is loaded in MS Excel, it automatically recalculated the row heights therefore it will be good to add autoFitRows before initializing the SheetRender object.

Java

Workbook workbook=new Workbook(“D:/TestPrintOption.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);
Workbook outputWorkbook = new Workbook();
Worksheet outputWorkSheet = outputWorkbook.getWorksheets().get(0);
outputWorkSheet.copy(worksheet);
Cells cells = outputWorkSheet.getCells();
cells.insertRows(1, 10000);

for(int i=1;i<=10000;i++) {
cells.copyRow(cells, 0, i);
}
cells.deleteRows(1, 20);
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
printoption.setPrintingPage(PrintingPageType.DEFAULT);

//autofit rows(only row height is auto)
System.out.println(outputWorkSheet.getCells().getRowHeight(0));
outputWorkSheet.autoFitRows(true);
System.out.println(outputWorkSheet.getCells().getRowHeight(0));

SheetRender sheetRender = new SheetRender(outputWorkSheet, printoption);
System.out.println(sheetRender.getPageCount());
outputWorkbook.save(“D:/TestPrintOption_output.xlsx”);

tauqeer.phoplunkar · October 26, 2015, 4:58am

Hi Team,

There is chance that my original sheet’s rows height are fixed.

How can I get row’s hight is auto or fixed. so that I can set same flag before SheetRender object creation.

babar.raza · October 26, 2015, 9:25am

Hi Tauqeer,

Thank you for writing back.

It seems that Aspose.Cells APIs are missing this feature (to detect if a row height is fixed or auto) as I was not able to find a suitable property/method therefore I have logged it as a feature request in our database as CELLSJAVA-41545. Please allow us some time to analyze the requirement and to provide a solution/workaround. We will get back to you as soon as we have some updates in this regard.

babar.raza · October 26, 2015, 11:36pm

Hi again,

We have looked into your requirements further. Please note, when you call the method autoFitRows(boolean onlyAuto) while passing the true as parameter, it only effects the rows whose height is auto therefore you can use it without detecting if row heights are fixed or auto.

babar.raza · October 27, 2015, 1:23am

Hi Tauqeer,

Adding more to my previous response, if you wish to detect if a given row has the height fixed or auto then you can use the Row.isHeightMatched method. The aforementioned method should return true for the row whose height it auto.

tauqeer.phoplunkar · February 5, 2016, 4:34am

Hi again,

I am facing the same problem again , not getting proper page count. Below is my code snippet.

Result page count conflicit

1.Java api show page count output 1278 pages.

2.whereas Print preview is showing 1135 pages

Workbook workbook=new Workbook(“C:\test\Test.xlsx”);

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

Workbook outputWorkbook = new Workbook(); 

Worksheet outputWorkSheet = outputWorkbook.getWorksheets().get(0); 

outputWorkSheet.copy(worksheet); 

Cells cells = outputWorkSheet.getCells(); 

//cells.insertRows(1, 10000); 

for(int i=1;i<=10000;i++) { 

cells.copyRow(cells, 0, i); 

} 

cells.deleteRows(1, 20); 

ImageOrPrintOptions printoption = new ImageOrPrintOptions(); 

printoption.setPrintingPage(PrintingPageType.DEFAULT); 

//printoption.setAllColumnsInOnePagePerSheet(true);

//autofit rows(only row height is auto) 

System.out.println(outputWorkSheet.getCells().getRowHeight(0));

outputWorkSheet.autoFitRows(true); 

System.out.println(outputWorkSheet.getCells().getRowHeight(0)); 

SheetRender sheetRender = new SheetRender(outputWorkSheet, printoption); 

System.out.println(sheetRender.getPageCount()); 

outputWorkbook.save(“C:\test\Test_output.xlsx”);

Please let me know if I am making any mistake. I run this program with aspose-cells-8.3.2.jar and aspose-cells-8.7.0.jar both. Please find output file in attachment

amjad.sahi · February 5, 2016, 7:22am

Hi,

Thanks for providing us sample code and output Excel file.

Please also provide us the input Excel file “Test.xlsx”, so we could evaluate your issue on our end.

By the way, I simply tested using your output file with the following sample code and it returns valid PageCount:

e.g

Sample code:

Workbook workbook=new Workbook(“Test_output.xlsx”);

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

ImageOrPrintOptions printoption = new ImageOrPrintOptions();

printoption.setPrintingPage(PrintingPageType.DEFAULT);

SheetRender sheetRender = new SheetRender(worksheet, printoption);

System.out.println(sheetRender.getPageCount()); //1135 - Ok.

Thank you.

tauqeer.phoplunkar · February 7, 2016, 11:40pm

hi

Please find source file in attachment.

below line in code i pasted in last post is showing 1278 pages but out file is having 1135 pages

System.out.println(sheetRender.getPageCount());

hope it will help to identify the issue.