Don't need hiding rows

Aspose.Cells Product Family
1

Hi,

I have a excel file it contains some of hiding rows which i don’t need them to imported into database, How can i get rid of these hiding rows by using function of ExportDataTableAsString?

Please advise,

2

Hi,

Well, yes, when you use ExportDataTableAsString() method, the hidden rows would be also exported to the data table. I think for your need, you need to delete those hidden rows or move them to other place/worksheet before exporting to fill the data table. See the sample code below for your reference:

Sample code:

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(@“e:\test2\import1.xlsx”);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
for (int i = 0; i < worksheet.Cells.MaxRow; i++)
{
if (worksheet.Cells.GetRow(i).IsHidden)
{
worksheet.Cells.DeleteRow(i);

}

}

DataTable dt = worksheet.Cells.ExportDataTableAsString(1, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, false);


Hopefully, it will work for your need accordingly.

Thank you.

3

Hi,


I am afraid there is no direct method available to exclude the hidden rows while exporting to DataTable. You need to implement your custom logic to achieve that.
I may suggest one way out of this. You can iterate over the rows to check if any row is hidden. If row is not Hidden then you may copy that row to new worksheet and latter export the cells of this newly inserted worksheet to DataTable.

My Sample C# code is as follows,


Workbook book = new Workbook(“C:\temp\hiddenrows.xlsx”);

int sheetindex = book.Worksheets.Add();

Worksheet sheet = book.Worksheets[sheetindex];

Cells cells = book.Worksheets[0].Cells;

int j = 0;

for (int i = 0; i < cells.Rows.Count; i++)

{

if (!cells.Rows[i].IsHidden)

{

sheet.Cells.CopyRow(cells, i, j++);

}

}

DataTable table = sheet.Cells.ExportDataTableAsString(0, 0, sheet.Cells.MaxRow, sheet.Cells.MaxColumn);

book.Save(“C:\temp\out.xlsx”);

4

i am sorry to tell you that i am using the old version of 4.7 now, it doesn’t have IsHidden property. Would you please help me on this again?

thanks,

5

Hi,

Well, I think may try to find the method like e.g use Cells.GetRowHeight() method to check if it gives you “0”, then the row must be hidden. The method should be there in your older version that you are using.

E.g

if (worksheet.Cells.GetRowHeight(i) == 0)
{
//…
}

Thank you.