Retrieving Excel data using EPPlus with and without headers

If you ever had to read from a sheet inside C# using EPPlus, you can use the following snippet to read the data.

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

 

using OfficeOpenXml;
....
string sourceFilePath = hidFilename.Value;
            using (ExcelPackage package = new ExcelPackage(new FileInfo(sourceFilePath)))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[ddlSheets.SelectedItem.Text];
                DataTable tbl = new DataTable();
                var hasHeader = chkHasHeaders.Checked;
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    string strColumnName = hasHeader
                        ? firstRowCell.Text
                        : string.Format("Column {0}", firstRowCell.Start.Column);
                    int counts = 0;
                    string newColumn = strColumnName;
                    while (tbl.Columns.Contains(newColumn))
                    {
                        newColumn = strColumnName + counts.ToString();
                        counts++;
                    }
                    tbl.Columns.Add(newColumn);
                }
                var startRow = hasHeader ? 2 : 1;
                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }
                grdTable.DataSource = tbl;
                grdTable.DataBind();
            }
Advertisements