Identify hidden rows and columns of an excel file using aspose.cells

Aspose.Cells Product Family
1

Hi,


I am using “Worksheet.Cells.ExportDataTableAsString()” function to get all the data from the excel file into a datatable. “ExportDataTableAsString” is getting all the rows and columns that are in the excel file including the hidden ones. Is there anyway that I could specify to export, only visible columns and rows?
Or even after exporting can I in anyway say that this column is hidden and this is not?

Thanks for your help.
Indu.
2

Hi Indu,

Thanks for your posting and using Aspose.Cells.

Currently, you cannot export data from cells without hidden columns and rows. However, you can find if the row or column is hidden using the Row.IsHidden and Column.IsHidden properties.

However, I have logged a New Feature request in our database for this issue. We will look into it and implement it if possible. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42399.

3

Hi,


I think you may try to set the ExportTableOption options e.g PlotVisibleCells and ExportAsString to set them as true while using ExportDataTable() method accordingly. Now only the visible cells in the rows/columns are exported, the cells data in the hidden rows won’t be exported. I have written an example code for your reference, please refer to it:
e.g
Sample code:

Workbook workbook = new Workbook(@“e:\test2\test_ExportDataTableAsString1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Range displayRange = worksheet.Cells.MaxDisplayRange;

ExportTableOptions options = new ExportTableOptions();
options.ExportAsString = true;
options.ExportColumnName = false;
options.PlotVisibleCells = true;
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, displayRange.RowCount, displayRange.ColumnCount, options);
dataTable.TableName = “Data2”;
workbook.Worksheets[1].Cells.ImportDataTable(dataTable, true, “A1”);

workbook.Save(“e:\test2\outtestExportDataTableAsString2.xlsx”);

Hope, this helps a bit.

Thank you.
4

Hi Amjad,


Thank you!!
It did help me. But I don’t think it can be used as an alternative. It has its drawbacks, for me the hidden columns are imported as it is. The hidden rows are imported too but the row is blank so I skipped them while processing (this was helpful).

In this case I only care about rows, I don’t care much about columns so it worked out pretty well.

Thanks once again.
Indu.
5

Hi Shakeel,


Thanks for getting back to me. I appreciate adding it to new feature requests.

Indu.
6

Hi Indu,


Yes, it does include blank records for the hidden rows in the table. Anyways, you may try to use it if it suits your needs well . Also, as Shakeel Faiz has logged a ticket “CELLSNET-42399” into our database, so we might provide that feature as well for it.

Thank you.
7

Hi Indu,

Thanks for using Aspose.Cells.

Please download and use the latest version: Aspose.Cells
for .NET v7.7.2.2.

We have added ExportTableOptions.PlotVisibleRows property for your needs.

8
This is great. Thank you!!

Indu.
9

Hi Indu,

Thanks for your posting and using Aspose.Cells.

Let us know if you face any other issue. We will be glad to look into it and help you further.

10

The issues you have found earlier (filed as CELLSNET-42399) have been fixed in this update.

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