I am using excel which contains more than 10 sheets. in every sheet, the first 3 or 5 rows have sample data. PFA screen shot. after file upload, I m using aspose to manipulate the data entered in excel. Since the sample data is populated in all the sheets, even if data is not entered, worksheet.Cells.MaxDataRow gives values as 3 or 5. how can I validate an empty template using c#.samplesheets.docx (185.9 KB)
Thanks for the screenshot.
If first 3 or 5 rows are sample (header) data then how could you say that a worksheet is empty? An empty worksheet is such a worksheet which has no data at all (even there should be no header rows). I guess in your case MaxDataRow is giving you the right values.
Thanks for the resposne.
in this case how can I validate if data is present from row 3 or 6? for every sheet, the starting row will differ.
You need to browse/loop through each cell and check if the cells are empty/null or have value.
will it not be time consuming? say example if there 15 sheets, I have loop through all 15 sheets and check if data is there or not. Is there any other way to handle it?
Since your worksheets are not empty all all (all sheets have data in the first 3-5 rows), so how could say those are actually empty sheets. You just want to check/validate data in specific rows (e.g., >=5) only, so you have to browse into those rows from 5th index. One thing you could do is you may use Row.CheckRow method to further evaluate if a row (in your specified range) exists and then check if it is null/empty.
e.g.
Sample code:
Workbook workbook = new Workbook("Book1.xlsx");
//Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
//create a range after your header rows
//you may change data range as per your maximum data
Range range = sheet.Cells.CreateRange("A6:Y100");
Cells cells = range.Worksheet.Cells;
int endRow = range.FirstRow + range.RowCount;
bool isEmpty = true;
for (int i = range.FirstRow; i < endRow; i++)
{
Row row = cells.CheckRow(i);
if (row == null || row.IsBlank)
{
}
else {
isEmpty = false;
break;
}
}
if (isEmpty == true)
{
Console.WriteLine("Worksheet is empty");
}
else {
Console.WriteLine("Worksheet is not empty");
}
The above code segment is a bit efficient. You may refer to the above code segment and write/update your own code accordingly.
Hope, this helps a bit.