How do i iterate through rows in an excel table using epplus ?
How do i iterate through rows in an excel table using epplus ?
This solution is more excellent than using Interop objects.
var package = new ExcelPackage(new FileInfo("sample.xlsx")); ExcelWorksheet workSheet = package.Workbook.Worksheets[0]; var start = workSheet.Dimension.Start; var end = workSheet.Dimension.End; for (int row = start.Row; row <= end.Row; row++) { // Row by row... for (int col = start.Column; col <= end.Column; col++) { // ... Cell by cell... object cellValue = workSheet.Cells[row, col].Text; // This got me the actual value I needed. } }
The following command is to get the whole row while ExcelRange after it can be either iterated or for LINQ:
for (var rowNum = 1; rowNum <= sheet.Dimension.End.Row; rowNum++) { var row = sheet.Cells[string.Format("{0}:{0}", rowNum)]; // just an example, you want to know if all cells of this row are empty bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text)); if (allEmpty) continue; // skip this row // ... }
By using this approach .Worksheet property of a table and basis its cells. and here the production procedure for this intention, and it will creates a order of dictionaries measuring column name to cell value:
Try this following code :
public static IEnumerable<IDictionary<string, object>> GetRows(this ExcelTable table) { var addr = table.Address; var cells = table.WorkSheet.Cells; var firstCol = addr.Start.Column; var firstRow = addr.Start.Row; if (table.ShowHeader) firstRow++; var lastRow = addr.End.Row; for (int r = firstRow; r <= lastRow; r++) { yield return Enumerable.Range(0, table.Columns.Count) .ToDictionary(x => table.Columns[x].Name, x => cells[r, firstCol + x].Value); } }