/// <summary>
/// Copy data from dataset into workbook
/// </summary>
/// <param name="dataSet"></param>
/// <param name="excelWorkbook"></param>
private void Export(DataSet dataSet, ref Workbook excelWorkbook) {
int sheetIndex = 0;
// build rawData 2 dimensional array with data for each datatable
foreach (System.Data.DataTable dt in dataSet.Tables) {
// Copy the DataTable to an object array
object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++) {
data[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++) {
for (int row = 0; row < dt.Rows.Count; row++) {
data[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen) {
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName; // name new sheet name of table
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = data;
// Mark the first row as BOLD
((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
}
}