Clever Geek Handbook

Reading data from .xls and .ods files.

Is there a way to read data from Excel spreadsheets and open offices and store it in temporary storage like a DataSet or an array? I came across ExcelDataReader (http://exceldatareader.codeplex.com/) but it only works if Excel is installed on the computer. Some of our users only have Open Office (others don't).

+3


source to share


1 answer


Here is a simple approach using EPPlus (which I can recommend) that converts it manually to DataTable

:

DataTable tbl = new DataTable();
var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(new System.IO.FileInfo(@"C:\Temp\ExcelFile.xlsx").OpenRead());
if (pck.Workbook.Worksheets.Count != 0)
{
    var sheet = pck.Workbook.Worksheets.First();
    var hasHeader = false; // adjust accordingly '
    foreach (var firstRowCell in sheet.Cells[1, 1, 1, sheet.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 <= sheet.Dimension.End.Row; rowNum++)
    {
        var wsRow = sheet.Cells[rowNum, 1, rowNum, sheet.Dimension.End.Column];
        tbl.Rows.Add(wsRow.Select(cell => cell.Text).ToArray());
    }
}


Originally posted by me here (VB.NET).

+1


source






More articles:


All Articles