Free Support Forum - aspose.com

Ho to skip blank rows while reading data from the excel sheet

Hi,

We want to skip the blank rows while reading data from the excel sheet. Is there any method that we can use to ignore blank rows?

For example

Table-1(We want to skip row#2 while reading from excel sheet.

Type<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Value1

Value2

Value3

Value4

Value5

1

a

b

c

d

e

3

s

r

ty

u

m

Server: Windows 2008

Application developed in: Visual Studio 2010

Hi there,

Thank you for contacting support.

You may use Row.isBlank property to check if a given row is blank, and skip it during the process as per your requirement. Please have a look at below provided code snippet.

C#


foreach (Row row in worksheet.Cells.Rows)
{
if (row.IsBlank)
{
//do process
}
}

Please feel free to write back in case you face any difficulty.

Hi,

Thank you for your reply with the solution.

As per the above code snipper, this will check all the rows in the worksheet i.e. 65k +

We are using

worksheet.Cells.ExportDataTableAsString

method to get the data from excel sheet. Is there any option that we can provide to skip the blank rows while using this method.

Many Thanks.

Hi,


I think you may try to remove the blank rows before exporting data to fill a DataTable. You may use the line of code if it suits your needs:
e.g
Sample code:
//…
worksheet.Cells.DeleteBlankRows();

Thank you.

I want to skip first row and col while generating images using aspose.cells how can I do this?

@tanishaa32,

Well, you may either hide first row and first column or specify printable area range (excluding first row/col) using worksheet page setup option before rendering imagine file(s) of the sheet.

Hope, this helps.

How??? Can you give me an example?

1	


Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.HideRow(0);
worksheet.Cells.HideColumn(0);
2

Workbook workbook = new Workbook(“e:\test\Book1.xlsx”);
//Get the first worksheet.
Worksheet mysheet = workbook.Worksheets[0];
//Specify your range excluding first row and first column.
mysheet.PageSetup.PrintArea = “B2:K7”;
//Or dynamically specify the range starting from B2:
// Get the Maximum row which contains data in the sheet
//int maxdataRow = mysheet.Cells.MaxDataRow;
//int maxdatacol = mysheet.Cells.MaxDataColumn;
//mysheet.PageSetup.PrintArea = “B2:” + CellsHelper.CellIndexToName(maxdataRow + 1, maxdatacol).ToString();
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
printoption.ImageFormat = ImageFormat.Jpeg;

printoption.OnePagePerSheet = true;
SheetRender sr = new SheetRender(mysheet, printoption);

sr.ToImage(0, "e:\\test\\" + "out1" + ".jpeg");

Hope, this helps a bit.