...
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); } |
Downloads
- Template file: ExcelToDataTable.xlsx