CreateExcel
Transforms a DataTable in Excel (xls). Requires Excel Library (https://code.google.com/p/excellibrary/)
Source
/// <summary>
/// Transforms a dataTable into Excel (xls). Requires Excel Library
/// </summary>
/// <param name="workSheetName">Set a name for the WorkSheet. If not given, the same name of the DataTable is set</param>
/// <returns></returns>
public static byte[] CreateExcel(this System.Data.DataTable dt, string workSheetName)
{
byte[] excel = null;
if (dt != null && dt.Rows.Count > 0)
{
// If a name has not been set for the WorkSheet, the name of the DataTable is set
if (string.IsNullOrEmpty(workSheetName)) workSheetName = dt.TableName;
// Create a new Workbook
ExcelLibrary.SpreadSheet.Workbook workbook = new ExcelLibrary.SpreadSheet.Workbook();
// Creates a new Worksheet
ExcelLibrary.SpreadSheet.Worksheet worksheet = new ExcelLibrary.SpreadSheet.Worksheet(workSheetName);
// Generates the columns excel according to the columns of the DataTable
for (int i = 0; i < dt.Columns.Count; i++)
{
// The first line will contain excel column names of DataTable
worksheet.Cells[0, i] = new ExcelLibrary.SpreadSheet.Cell(dt.Columns[i].ColumnName);
// Sets the column width
worksheet.Cells.ColumnWidth[0, (ushort)i] = 3000;
}
// Generates rows with records from DataTable
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
// Inserts the data in the DataTable rows and columns in Excel
worksheet.Cells[i + 1, j] = new ExcelLibrary.SpreadSheet.Cell(dt.Rows[i][j].ToString());
}
}
// Insert blank lines to avoid error in excel file when the number of records is very small
if (dt.Rows.Count < 100)
{
for (int i = dt.Rows.Count + 1; i < (100 + dt.Rows.Count); i++)
{
worksheet.Cells[i, 0] = new ExcelLibrary.SpreadSheet.Cell("");
}
}
// Adds the Worksheet to Workbook
workbook.Worksheets.Add(worksheet);
// Generates the file in memory
System.IO.MemoryStream stream = new System.IO.MemoryStream();
workbook.Save(stream);
// Returns a byte array of Excel
excel = stream.ToArray();
}
return excel;
}
/// <summary>
/// Transforms a dataTable into Excel (xls). Requires Excel Library
/// </summary>
/// <returns></returns>
public static byte[] CreateExcel(this System.Data.DataTable dt)
{
return CreateExcel(dt, string.Empty);
}
Example
byte[] excel_sales = dataTable.CreateExcel("Sales");
byte[] excel_customer = dataTable.CreateExcel("Customer");
Author: Raphael Augusto Ferroni Cardoso
Submitted on: 5 jun. 2014
Language: C#
Type: System.Data.DataTable
Views: 8223