Hi,
I need to find next cell in row or column which has no data and no formatting.
Basically i've excel report which has multiple sub reports on each spreadsheet of workbook. so i need to find a max row / max column for each report section. Each spreadseet could have multiple reports.
Hi,
Thanks for your query.
Well, you will use Cells.MaxColumn and Cells.MaxRow attributes if you need to get the farthest row/column index (zero based) of the cell with data or formatting/style applied.
Workbook workbook = new Workbook();
workbook.Open(@“e:\test\Book1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//Get the last row index which contains data or style.
int mrow = cells.MaxRow;
//Get the farthest column index which contains data or formatting
//Suppose the last column which contains data is U but there is some background color applied in //Z10 column (and there is no data in it). It is to be noted here the MaxColumn would give you 25 value here.
int mcol = cells.MaxColumn;
//To find the next row ( of the max row) which has no data or formatting in it.
nrow = mrow+1;
//To find the next column (of the max column) which has no data or
formatting in it.
ncol = mcol+1;
Hope, this helps you.
Thank you.
Hi,
Thanks for your reply. Actually i need to find cell within report which has no data and no formatting.
For example, i've 20 rows in my excel spreadsheet. First 10 rows belong to first report, so cells have custom formtting and may have data.
11th row is empty without formtting and without any data.
12-20 th row make up for 2nd report.
So i cannot use maxrow as it would give me 20th row, whereas i need to find 11th row which is an intermediate row.
Can you please advise, if it possible to find out?
Hi,
Well, I think you can loop through the cells to check each cell (in the row) whether it is null/empty by using your own code accordingly.
e.g
Workbook excel = new Workbook();
excel.Open(@“e:\test\Book1.xlsx”);
string cname;
for (int i = 0; i <= excel.Worksheets[0].Cells.MaxRow;i++)
{
for (int j = 0; j <= excel.Worksheets[0].Cells.MaxColumn; j++)
{
if (excel.Worksheets[0].Cells[i,j].Type == CellValueType.IsNull)
{
cname = excel.Worksheets[0].Cells[i,j].Name;
//Your code goes here.
}
}
}
Thank you.