LightCellsDataHandler reads empty cells

Aspose.Cells Product Family
1

As far as I know, LightCellsDataHandler does not read the empty cells i.e. processCell() does not get called for empty cells. But, for the excel: empty multiple columns and rows.xls, it gives empty cells.

For another sheet: 97-2003.xls, it does not read empty cells.

Kindly let us know the exact behaviour for which cells are read by
LightCellsDataHandler.

2

Hi,


Yes, LightCells APIs generally skips null/empty cells in the worksheet. But if the cells are initialized, it would include those cells even if they are empty.

Could you give us sample code (runnable) to evaluate your issue. Also give us details which cells are called in the worksheet, so we could evaluate those cells if they are initialized (yet empty) or some formatting is already applied to those cells.

Thank you.
3

Attached the code in Handler.zip which reads excel file using LightCellsDataHandler.

While reading mini.xls, it reads empty cells specifically column H, column K, and rows 3,4,5,16.
The program prints string “EMPTY THIS IS” when the empty cell is encountered.

In another workbook 97-2003.xls, there is empty column B, and empty row 4, but it is not read by the LightCellsDataHandler.

Let us know how it’s working differently in both the cases.

4

Hi,


Thanks for providing us sample code and template files.

After an initial test, I observed the issue as you mentioned by using your sample code with your template files. I found that for certain cases LightCellsDataHandler reads empty cells. While reading mini.xls, it reads empty cells specifically column H, column K, and rows 3,4,5,16. In your workbook 97-2003.xls, there is empty column B, and empty row 4, but it is not read rightly by the LightCellsDataHandler. May be those empty cells in mini.xls file are initialized, anyways, we have to find out and investigate your issue in details.

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

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

Thank you.
5
Hi,

Thanks for using Aspose.Cells.

Just as Amjad has mentioned: "LightCells APIs generally skip null/empty cells in the worksheet. But if the cells are initialized, it would include those cells even if they are empty."

For your template file, in mini.xls those empty cells has been initialized so they will be sent to your handler. In 97-2003.xls, those empty cells does not exist in the data block, so no empty cells will be sent to your handler.

If you do want to skip all empty cells, you may try the option:

Java
LoadOptions loadOptions = new LoadOptions(); loadOptions.setLoadFilter(new LoadFilter(LoadDataFilterOptions.ALL&~LoadDataFilterOptions.CELL_BLANK)); //skip empty cells
loadOptions.setLightCellsDataHandler((LightCellsDataHandler) handler);
6

Thanks, it works.

But, empty cells starting with single quote(’) are still appearing. Is there any way to get rid of such cells?

7

Kindly check the file attached.
With the code provided earlier, it loads the file (with empty cells) properly with LightCellsDataHandler.
If the loadDataOptions is set as follows, the file does NOT open. No stacktrace in console. Just says, “program terminated” as shown in the screenshot.

loadOptions.setLoadDataFilterOptions(LoadDataFilterOptions.ALL & ~LoadDataFilterOptions.CELL_BLANK);

8

Any udpates?

9
Hi,

Thanks for using Aspose.Cells.

For the issue of cells starting with single quote('), the value of them are saved as empty string value "", not really blank, we are afraid you have to check the cell value by yourself in the handler to ignore them.

10

Thanks.

Request you to update the documentation page Using LightCells API|Documentation with the information/outcome of the following two threads:

  1. Detect empty header with LightCellsDataHandler

Sample statements for updating help:

  • LightCells API does NOT read empty cells, but some empty cells like you mentioned are read.
  • We can avoid loading of blank cell values as you mentioned earlier.

Also why LoadDataFilterOptions.CELL_BLANK is failing with the file SuperEatsBasics.xlsx?

11

I have aspose cells for java 9.0.0.

There is no class named LoadFilter. So the code provided by you is:

LoadFilter lf = new LoadFilter();
lf.setLoadDataFilterOptions(LoadDataFilterOptions.ALL & ~LoadDataFilterOptions.CELL_BLANK);

Code for the version 9.0.0 would be:

LoadOptions loadOptions = new LoadOptions();
loadOptions.setLoadDataFilterOptions(LoadDataFilterOptions.ALL & ~LoadDataFilterOptions.CELL_BLANK);
NonEmptyCellsHandler nech = new NonEmptyCellsHandler();
loadOptions.setLightCellsDataHandler((LightCellsDataHandler) nech);
new Workbook(“Basics.xlsx”, loadOptions);

It does not work with the excel provided earlier. Using the program mentioend above, it does not read any data. Kindly check with version 9.0.0 and let us know the work-around if any.

12

Hi,


Thanks for your posting and using Aspose.Cells.

Please try the latest version: Aspose.Cells for Java 17.1.0 and see if it works for you. We cannot fix issues in older versions. We only fix issues in the most recent version.

13

Yep, the version 17.1.0 is working fine as expected.