Free Support Forum -

Copying a Worksheet Unhides Filtered Rows - Problem 3

problem 3: the html result pivot table will missing last 2 rows; and the date 2/1/2017 will be changed to a numeric number 42736; and the title of the pivot table will be printed twice.


We were able to observe the issue as you mentioned by using the following sample code with your newly attached template file from your original thread. I found that PivotTable is wrong (missing rows, pivot field headers printed twice, Date converted to numeric values, etc.) in Excel to HTML rendering:
3. The HTML file contains the following issues:
a. missing last 2 rows;
b. table head will be printed twice;
c. date 2/1/2017 will be changed to a numeric number.
Sample code:

//Apply license for Aspose.Cells for Java.

License license = new License();


// attached workbook

String path = "FilteredRows.xlsx";

String namedRng = "TestData";

Workbook wb = new Workbook(path);

Name name = wb.getWorksheets().getNames().get(namedRng);

Range rng = name.getRange();

Worksheet sheet = rng.getWorksheet();

Cells cells = sheet.getCells();

int sheetIdx = sheet.getIndex();

int firstCol = rng.getFirstColumn();

int firstRow = rng.getFirstRow();

int colCnt = rng.getColumnCount();

int rowCnt = rng.getRowCount();

int maxCol = cells.getMaxDisplayRange().getColumnCount();

int maxRow = cells.getMaxDisplayRange().getRowCount();

// Create a copy of the worksheet - such that only the copy is modified

int newSheetIdx = wb.getWorksheets().addCopy(sheetIdx);


// The copied worksheet does NOT have the filter applied and therefore

// the filtered (hidden) rows from the original sheet are now visible

// redefine the ranges using the copied worksheet


sheet = wb.getWorksheets().get(newSheetIdx);

cells = sheet.getCells();

rng = cells.createRange(firstRow, firstCol, rowCnt, colCnt);

// Manually hide all the columns not part of the named range

if (firstCol > 0)


cells.hideColumns(0, firstCol);


if (firstCol + colCnt < maxCol)


cells.hideColumns(firstCol + colCnt, maxCol + 1);


// Manually hide all the rows not part of the range

if (firstRow > 0)


cells.hideRows(0, firstRow);


if (firstRow + rowCnt < maxRow)


cells.hideRows(firstRow + rowCnt, maxRow);


// Now export the copied worksheet

HtmlSaveOptions options = new HtmlSaveOptions(SaveFormat.HTML);










options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);"f:\\files\\out1.html", options);

I have logged a separate ticket with an id "CELLSJAVA-42308" for your issue. We will look into it soon.

Thank you.

Please try our latest version/fix: Aspose.Cells for Java v17.5.5

Your issue (logged earlier as "CELLSJAVA-42308") should be fixed in it.

Let us know your feedback.

Thank you.

The issues you have found earlier (filed as CELLSJAVA-42308) have been fixed in Aspose.Cells for Java 17.6.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.