Intro
Excerpt |
---|
...
Blurb shown on index page/master list of ASP.NET samples.
Text from the 'Technical Overview' section
Extract Excel data into a DataTable |
The source Workbook contains a listing of Employees for the fictional company AdventureWorks. The actual data is marked with two Named Ranges, "EmployeesHeadings" and "EmployeesData", which will make it easier to import using ExcelWriter. This data is then used to populate a DataGrid
Code
Code Block |
---|
public Insert code from sample stored in SVN here |
Downloads
...
class ExcelToDataTable
{
// The indexes of the columns which contain dates
protected int[] dateColumnIdxs;
/// <summary>
/// Open an existing Excel workbook, navigate to the "Employees"
/// named range, and extract the named range data into a DataTable.
/// </summary>
public void PopulateDataTable()
{
// Create an instance of ExcelApplication
ExcelApplication xlw = new ExcelApplication();
// Open the Workbook containing the data
string workbookPath = @"..\..\ExcelData\ExcelToDataTable.xlsx";
Workbook wb = xlw.Open(workbookPath);
//Get the first Area of the Named Range "employees", which contains the
//column headings and data
Range empRange = wb.GetNamedRange("Employees");
Area empArea = empRange.Areas[0];
// Populate the array of columns which are dates, in this case the 5th column
this.dateColumnIdxs = new int[] {4};
// Call conversion method below to convert Area to DataTable
DataTable empTable =
AreaToDataTable(empArea, true, this.dateColumnIdxs, "Employees");
}
/// <summary>
/// ExcelApplication has the ability to read data from Excel workbooks.
/// This method Extracts data from an ExcelApplication Area object into
/// a System.Data.DataTable object.
/// </summary>
/// <param name="a">Area from which to extract data</param>
/// <param name="hasHeaderRow">True if first row of area contains column headers</param>
/// <param name="dateColumnIndexes">Columns that should be handled as Date values</param>
/// <param name="tableName">Name for the new DataTable</param>
/// <returns>DataTable containing data from the specified Area</returns>
private DataTable AreaToDataTable(Area a, bool hasHeaderRow, int[] dateColumnIndexes, string tableName)
{
// Set count and index variables
int dataRowCount;
int firstDataRowIdx;
int columnCount = a.ColumnCount;
if (hasHeaderRow == true)
{
firstDataRowIdx = 1;
dataRowCount = a.RowCount - 1;
}
else
{
firstDataRowIdx = 0;
dataRowCount = a.RowCount;
}
// Create DataTable
DataTable table = new DataTable(tableName);
// Add columns to DataTable
for (int colIdx = 0; colIdx < columnCount; colIdx++)
{
string columnName;
if (hasHeaderRow == true)
{
// Get column name from cell value
columnName = (string)a[0, colIdx].Value;
}
else
{
// Use a generic column name
columnName = "Column" + colIdx;
}
// Insert column into DataTable
table.Columns.Add(new DataColumn(columnName));
}
//Populate rows of DataTable
// For each row in Area
for (int rowIdx = 0; rowIdx < dataRowCount; rowIdx++)
{
// Add row to DataTable
DataRow row = table.NewRow();
table.Rows.Add(row);
// For each column
for (int colIdx = 0; colIdx < columnCount; colIdx++)
{
// Get value from cell
object val = a[firstDataRowIdx + rowIdx, colIdx].Value;
// If current column is in the array of date columns, convert to a .NET date.
//(Excel dates are stored in a serial format which we must convert.)
if (Array.IndexOf(dateColumnIndexes, colIdx) != -1)
{
// Convert to a .NET date using conversion method below
DateTime dt = ExcelSerialDateToDateTime(Convert.ToInt32(val));
// Add date to DataTable
row[colIdx] = dt;
}
else
{
// Otherwise, just add cell value to DataTable
row[colIdx] = val;
}
}
}
// Return DataTable
return table;
}
/// <summary>
/// Internally, Microsoft Excel stores dates as the number of days
/// that have passed since the epoch. For example, November 1, 2005
/// is stored as 38657. Use this method to convert
/// an Excel serial date to a .NET DateTime object.
/// </summary>
/// <param name="serialDate">The Excel serial date</param>
/// <returns>DateTime object for the specified serial date</returns>
private DateTime ExcelSerialDateToDateTime(int serialDate)
{
if(serialDate == 60)
{
return new DateTime(1900, 2, 29);
}
else if(serialDate < 60)
{
serialDate++;
}
return new DateTime(1900, 1, 1).AddDays(serialDate - 2);
}
}
|
Downloads
- Template file: ExcelToDataTable.xlsx