Nov 7 2011 at 9:23 PM
Edited Nov 7 2011 at 9: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?
|
|
Coordinator
Nov 17 2011 at 5:30 AM
|
No, there is no build in method for that. Use Linq or add cell by cell.
|
|
|
|
I'm also looking for a similar function. if you can post the Linq code , it would be helpful
|
|
Jan 16 at 10:47 AM
Edited Jan 16 at 5: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 at 4:49 PM
Edited Jan 16 at 4: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:
- You already have a DataTable with the schema filled.
- 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).
|
|