...
Code Block |
---|
// 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 = @"..\..\Data\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);
}
}
}
|