This project has moved and is read-only. For the latest updates, please go here.

Opposite of LoadFromDataTable()?

Nov 7, 2011 at 10:23 PM
Edited Nov 7, 2011 at 10:26 PM

I'd like to be able to read a table in from Excel and insert the results directly into an ADO.NET DataTable. Is there a direct method for this, or do I need to use Linq?

Nov 17, 2011 at 6:30 AM

No, there is no build in method for that. Use Linq or add cell by cell.

Jan 16, 2012 at 10:21 AM

I'm also looking for a similar function. if you can post the Linq code , it would be helpful

Jan 16, 2012 at 11:47 AM
Edited Jan 16, 2012 at 6:45 PM

Here is a simple example(VB.NET):       

Dim pck = New OfficeOpenXml.ExcelPackage() 
pck.Load(New IO.FileInfo(path).OpenRead)
Dim ws = pck.Workbook.Worksheets("Worksheet1")
Dim tbl As New DataTable Dim hasHeader = True For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column) tbl.Columns.Add( If(hasHeader, firstRowCell.Text, String.Format("Column {0}", firstRowCell.Start.Column))) Next Dim startRow = If(hasHeader, 2, 1) For rowNum = startRow To ws.Dimension.End.Row Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column) Dim row = tbl.NewRow For Each cell In wsRow row(cell.Start.Column - 1) = cell.Text Next tbl.Rows.Add(row) Next
 

C# (translated):
var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(new System.IO.FileInfo(path).OpenRead());
var ws = pck.Workbook.Worksheets["Worksheet1"];
DataTable tbl = new DataTable();
var hasHeader = true;
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]){
      tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
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);
}
Jan 16, 2012 at 5:49 PM
Edited Jan 16, 2012 at 5:51 PM

@viki,

Here's my "FillTable" method:

        private void FillTable(DataTable table, ExcelWorksheet worksheet)
        {
            var firstCell = worksheet.Cells[1, 1];
            var endCell = GetLastContiguousCell(firstCell);
            var beginCell = worksheet.Cells[2, 1];
            if (beginCell.Value == null) return;
            var range = worksheet.Cells[beginCell.Start.Row, beginCell.Start.Column, endCell.Start.Row, endCell.Start.Column];
            var data = range
                .OrderBy(cell => cell.Start.Row)
                .GroupBy(cell => cell.Start.Row);
            foreach (var rowData in data)
            {
                var values = table.Columns.Cast<DataColumn>()
                    .Select((column, index) => rowData.SingleOrDefault(cell => cell.Start.Column == index + 1))
                    .Select(cell => cell == null ? null : cell.Value)
                    .Select(value => _valueAdjuster == null ? value : _valueAdjuster(value))
                    .ToArray();
                table.Rows.Add(values);
            }
        }

        private ExcelRange GetLastContiguousCell(ExcelRange beginCell)
        {
            var worksheet = beginCell.Worksheet;
            var beginCellAddress = new ExcelCellAddress(beginCell.Start.Row, beginCell.Start.Column);
            var lastCellAddress = worksheet.Dimension.End;
            var bottomCell = worksheet.Cells[beginCellAddress.Row, beginCellAddress.Column, lastCellAddress.Row, beginCellAddress.Column]
                .First(cell => cell.Offset(1, 0).Value == null);
            var rightCell = worksheet.Cells[beginCellAddress.Row, beginCellAddress.Column, beginCellAddress.Row, lastCellAddress.Column]
                .First(cell => cell.Offset(0, 1).Value == null);
            return worksheet.Cells[bottomCell.Start.Row, rightCell.Start.Column];
        }

This assumes a couple things:

  1. You already have a DataTable with the schema filled.
  2. Your Excel data begins at the top/left cell (A1), and there are no gaps in the data (i.e., no empty columns or rows in the middle of the data).

I have another method that checks if the heading row is a perfect match for the column names in the DataTable:

        private void ValidateWorksheetHeadings(DataTable table, ExcelWorksheet worksheet)
        {
            var firstCell = worksheet.Cells[1, 1];
            var endCell = GetLastContiguousCell(firstCell);
            var beginCell = worksheet.Cells[1, 1];
            var range = worksheet.Cells[beginCell.Start.Row, beginCell.Start.Column, beginCell.Start.Row, endCell.Start.Column];
            var data = range.OrderBy(cell => cell.Start.Column);
            var tableHeadings = table.Columns.Cast<DataColumn>().Select((column, index) => index + "." + column.ColumnName);
            var worksheetHeadings = data.Select((cell, index) => index + "." + cell.Value.ToString());
            var missingHeadings = tableHeadings.Except(worksheetHeadings);
            var extraHeadings = worksheetHeadings.Except(tableHeadings);
            if (missingHeadings.IsEmpty() && extraHeadings.IsEmpty()) return;
            throw new Exception(string.Format(
                "The worksheet, {1}, has the following errors:{0}" +
                (missingHeadings.IsEmpty() ? string.Empty : "- Missing Headings: {2}{0}") +
                (extraHeadings.IsEmpty() ? string.Empty : "- Extra Headings: {3}"),
                Environment.NewLine, table.TableName, string.Join(", ", missingHeadings), string.Join(", ", extraHeadings)));
        }

Note: if you use the validation method, you may want to put the calculation of EndCell into a method (to keep your code DRY).

Jan 2, 2014 at 2:32 PM
@danthman what is _valueAdjuster ?
I have a error: "The name '_valueAdjuster' does not exist in the current context "

Thanks
Jan 6, 2014 at 7:03 PM
@msbr,

You can safely remove the line that contains _valueAdjuster. (All that does is enable the client to provide a Func delegate to perform a custom conversion of a value.)