CreateExcel

/// <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);
}

byte[] excel_sales = dataTable.CreateExcel("Sales");
byte[] excel_customer = dataTable.CreateExcel("Customer");