We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copying a Worksheet Unhides Filtered Rows

Hello,

We are seeing an issue in Aspose Cells version 17.4.0 where a worksheet that has filters will unhide the hidden (filtered) rows when the sheet is copied. The primary issue is that when we convert the copied worksheet to HTML, the unhidden columns are included in the output.

In the attached workbook “FilteredRows.xlsx”:

  • A filter is applied in cell A1.
  • There’s a named range “TestData” which is the target for the HTML output.
  • The rows that is meant to be hidden are highlighted in red.
We can reproduce this issue under the latest Cells version 17.4.0, using this code:

public static void main(String[] args) {
try {
setLicense(); //set the license

// attached workbook
String path = “C:\Temp\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);

// BUG NOTE:
// 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
wb.getWorksheets().setActiveSheetIndex(newSheetIdx);
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.setEncoding(Encoding.getUTF8());
options.setHtmlCrossStringType(HtmlCrossType.CROSS);
options.setPresentationPreference(true);
options.setExportHiddenWorksheet(false);
options.setExportActiveWorksheetOnly(true);
options.setExportImagesAsBase64(true);
options.setCreateDirectory(false);
options.setExpImageToTempDir(false);
options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);

wb.save(path.replace(".xlsx", “.html”), options);

System.out.println(“Successfully created HTML file:\n " +
path.replace(”.xlsx", “.html”));

} catch (Exception ex) {
System.out.println(“Unexpected EXCEPTION: \n\t” +
ex.getMessage());
}
}

Thanks.

Hi,


Thanks for the sample code, template file and details.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found that by copying a worksheet unhides filtered rows in the output HTML file, this works fine if we re-save to XLSX file format though.

I have logged a an id “CELLSJAVA-42282” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

Hi Amjad,

Thank you for your prompt response and investigation.

I also want to bring to your attention that we found a related issue that results in a NullPointerException. When the filter exists outside of the named range (as opposed to within the named range as in the original example above) - a NullPointerException is incurred.

You can reproduce this related issue by using the new attached workbook “FilteredRow-NPE.xlsx” and the same test code above (just adjust the line that loads the workbook).

To reiterate, the difference between the first workbook and the new one is that the filter exists on Column A only. In the original example, the filters exist on the range A1:H1.

Thanks.

Hi,


Thanks for your posting and using Aspose.Cells.

We were able to observe this issue as per your description and logged it in our database for investigation and for a fix. Once, there is some news for you, we will update you asap.

This issue has been logged as

  • CELLSJAVA-42283 - NullPointerException occurs when the filter exists outside of the named range

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issues (i.e. CELLSJAVA-42282, CELLSJAVA-42283) now. We will soon provide the fix after performing QA and including other enhancements and fixes.

Hi,


Please try our latest fix/version: Aspose.Cells for Java v17.4.7 (attached) as both your issues should be fixed in it.

Let us know your feedback.

Thank you.

Hi Amjad,

Once again thank you for the prompt response and fix to this issue. We were able to verify that the 17.4.7 Cells version you provided does indeed fix the two issues we presented in this thread.

Unfortunately, we’re now seeing a new issue under 17.4.7 that we did not encounter under 17.4.0. If a Pivot Table exists in the worksheet that is being converted to HTML and it is outside of the named range, we are getting the following exception:
java.lang.IllegalArgumentException: Row index cannot be negative

You can reproduce this behavior by using the attached “FilteredRows-PivotTable.xlsx” file and the sample code provided above (just update the “path” variable).

To reiterate, we were not getting this exception under 17.4.0.
Thanks again.

Hi,


Thanks for providing us template file and details.

After an initial test, I am able to observe the issue as you mentioned by using your original sample code (in the first post) with your newly attached template file. I found that if a Pivot Table exists in the worksheet that is being converted to HTML and it is outside of the named range, we are getting the following exception:
“java.lang.IllegalArgumentException: Row index cannot be negative”.

I have logged a ticket with an id “CELLSJAVA-42285” for your issue. We will look into the issue soon.

Once we have an update on it, we will let you know here.

Thank you.

The issues you have found earlier (filed as CELLSJAVA-42283;CELLSJAVA-42282) have been fixed in Aspose.Cells for Java 17.5.


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

Thanks for using Aspose.Cells.

Please download and try the following latest fix: Aspose.Cells for Java v17.5.1 and let us know your feedback.

Hi Shakeel, We have tested : Aspose.Cells for Java v17.5.1, and find it didn’t solve problem. Here I attach the test file, in the excel file, we added another pivot table.



1. The html file will give us the wrong pivot table.

2. If we change the named-range to table1, which is the newly added pivot table, “java.lang.IllegalArgumentException” will come back.

3. The html file will:

a. missing last 2 rows;

b. table head will be printed twice;

c. date 2/1/2017 will be changed to a numeric number.

Hi,

Thanks for the template file.

oraspose:
2. If we change the named-range to table1, which is the newly added pivot table, "java.lang.IllegalArgumentException" will come back.

I observed the issue as you mentioned by using the following sample code with your newly attached template file. I found that if we change the named-range to "table1" in code an exception: "java.lang.IllegalArgumentException: The row index should not be inside the pivottable report" occurred when rendering to HTML file format.
e.g
Sample code:

//Apply license for Aspose.Cells for Java.

License license = new License();

license.setLicense("Aspose.Cells.lic");

// attached workbook

String path = "FilteredRows.xlsx";

String namedRng = "table1";

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);

// BUG NOTE:

// 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

wb.getWorksheets().setActiveSheetIndex(newSheetIdx);

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.setEncoding(Encoding.getUTF8());

options.setHtmlCrossStringType(HtmlCrossType.CROSS);

options.setPresentationPreference(true);

options.setExportHiddenWorksheet(false);

//options.setExportActiveWorksheetOnly(true);

options.setExportImagesAsBase64(true);

options.setCreateDirectory(false);

options.setExpImageToTempDir(false);

options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);

options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);

wb.save(“f:\files\out1.html”, options);//Exception



I have logged a ticket with an id “CELLSJAVA-42307” for your issue. We will look into it soon.

oraspose:
3. The html file will:
a. missing last 2 rows;
b. table head will be printed twice;
c. date 2/1/2017 will be changed to a numeric number.

I observed the issue as you mentioned by using the following sample code with your newly attached template file. 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.
e.g
Sample code:

//Apply license
for Aspose.Cells for Java. <o:p></o:p>

License license = new License();

license.setLicense("Aspose.Cells.lic");

// 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);

// BUG NOTE:

// 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

wb.getWorksheets().setActiveSheetIndex(newSheetIdx);

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.setEncoding(Encoding.getUTF8());

options.setHtmlCrossStringType(HtmlCrossType.CROSS);

options.setPresentationPreference(true);

options.setExportHiddenWorksheet(false);

//options.setExportActiveWorksheetOnly(true);

options.setExportImagesAsBase64(true);

options.setCreateDirectory(false);

options.setExpImageToTempDir(false);

options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);

options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);

wb.save(“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.


oraspose:
1. The html file will give us the wrong pivot table.

Could you elaborate what issue you are talking about, you may attach details and screenshots and sample code to replicate the issue, we will check it soon.

Thank you.

regarding 1. The html file will give us the wrong pivot table.
In the attached sample file FilteredRows.xlsx, there are 2 pivot tables, the first one is TestData, and the second one is table1. When we set namedRange to TestData, we expect the html will give us the TestData pivot table, but actually it give us the table1 pivot table.

Hi,


Regarding 1)
Could you give us your expected file with correct PivotTable. Also, give us some screenshot(s) to highlight the problematic area with comments (comparing incorrect PivotTable with correct PivotTable). This will help us to trace the issue precisely, so we could log a separate ticket (if appropriate) for it into our database.

Moreover, we are pleased to inform you that your issues (previously logged as “CELLSJAVA-42307” and “CELLSJAVA-42308”) have been fixed now. We will soon provide you the fix after performing QA and incorporating other enhancements and fixes.

Thank you.
Hi,

Amjad Sahi:
Moreover, we are pleased to inform you that your issues (previously logged as "CELLSJAVA-42307" and "CELLSJAVA-42308") have been fixed now. We will soon provide you the fix after performing QA and incorporating other enhancements and fixes.

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

Both issues should be fixed in it.

Let us know your feedback.

Thank you.

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


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