Efficiently determining sheet names in workbook only

TarasTielkes · June 8, 2016, 6:21am

Hi,

In a number of use cases, we are only interested in known all the sheet names in a given workbook.

What is the most efficient way to collect the sheet names using Aspose.Cells? Note that we don’t want the actual sheet content to be parsed.

Would the “LightCellsDataHandler” API be the way to go (returning “false” from startSheet()), or are there other relevant load options for this? For example, when using the “light” mode, would calling loadOptions.setLoadDataFilterOptions(LoadDataFilterOptions.NONE) make any difference?

Thanks,

Taras

shakeel.faiz · June 8, 2016, 11:25am

Hi,

Thanks for your posting and using Aspose.Cells.

Yes, loadOptions.setLoadDataFilterOptions(LoadDataFilterOptions.NONE) makes difference. It will only load your workbook sheet names and will not load anything else.

Please see the following sample code and its console output for your reference. I have also attached the source excel file used in this code for your investigation as well as the output pdf.

Java
//Specify load options, we don’t want to load anything
LoadOptions opts = new LoadOptions();
opts.setLoadDataFilterOptions(LoadDataFilterOptions.NONE);

//Load your workbook with no data at all
Workbook wb = new Workbook(“s.xlsx”, opts);

//Print the sheet names in your workbook
for (int i = 0; i < wb.getWorksheets().getCount(); i++ )
{
System.out.println("Name: " + wb.getWorksheets().get(i).getName());
}

//save your workbook in pdf format
wb.save(“output.pdf”);

Console Output
Name: Test1
Name: Sheet2
Name: Test3
Name: Sheet4

TarasTielkes · June 8, 2016, 11:46am

Hi Shakeel,

Thank you for the explanation.

Using LoadDataFilterOptions.NONE is very fast when we only need to retrieve the sheet names.

Using the LightCellsDataHandler API together with LoadDataFilterOptions.NONE does not seem to provide any additional performance improvement - in fact, it seems to be a slower approach to collect the sheetnames compared to simply iterating workbook.getWorksheets().

Just to confirm my understand of LoadDataFilterOptions: setting option NONE will act as if all the other information in the workbook simply does not exist, is that correct? In other words, the other information will also not be loaded “lazily” - is that the correct understanding?

Thanks,

Taras

amjad.sahi · June 8, 2016, 12:38pm

Hi,

Well, if you need to extract only Worksheet names, you should not use LightCells APIs and use normal mode (as per sample code shared by Shakeel Faiz). If you need to read and write big Excel files with long list of data or contents in the worksheet, the LightCells APIs would be useful in that case. The LightCells APIs are mainly used for reading/creating huge Excel spreadsheets, with it, you need relatively less memory and you will get better performance and efficiency.

And, yes, your understanding is correct, setting to LoadDataFilterOptions.NONE would not load anything (data, contents, etc.) from the spreadsheet.

Thank you.