Here goes the code to export a DataTable to excel sheet

class ExportToExcel

{

private Application application;

private Workbook workbook;

private Worksheet previousWorksheet;

/// <summary>

/// Creates a blank excel sheet and adds a worksheet to it

/// </summary>

public ExportToExcel()

{

this.application = null;

this.workbook = null;

this.previousWorksheet = null;

application = new Excel.Application();

application.Visible = false;

workbook = application.Workbooks.Add(1);

}

/// <summary>

/// Exports the datatable to a excel file

/// </summary>

/// <param name="dtExcelData"></param>

/// <param name="sheetName"></param>

/// <param name="strFilePath"></param>

public void ExportTable(System.Data.DataTable dtExcelData, string sheetName, string strFilePath)

{

try

{

Worksheet worksheet = (Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, XlSheetType.xlWorksheet);

worksheet.Name = sheetName;

previousWorksheet = worksheet;

int columnCount = dtExcelData.Columns.Count;

foreach (DataColumn column in dtExcelData.Columns)

{

CreateExcelHeaders(worksheet, 1, dtExcelData.Columns.IndexOf(column) + 1, column.ColumnName);

}

foreach (DataRow row in dtExcelData.Rows)

{

foreach (DataColumn dtColumn in dtExcelData.Columns)

{

AddData(worksheet, dtExcelData.Rows.IndexOf(row) + 2, dtExcelData.Columns.IndexOf(dtColumn) + 1, row[dtColumn.ColumnName].ToString());

}

}

SaveWorkbook(strFilePath);

ShutDownExcel();

}

catch (Exception ex)

{

Logger.Write(ex);

}

}

/// <summary>

/// Write the headers to the excel sheet

/// </summary>

/// <param name="worksheet"></param>

/// <param name="row"></param>

/// <param name="col"></param>

/// <param name="htext"></param>

public void CreateExcelHeaders(Worksheet worksheet, int intRowIndex, int intColumnIndex, string strHeaderData)

{

try

{

worksheet.Cells[intRowIndex, intColumnIndex] = strHeaderData;

}

catch (Exception ex)

{

Logger.Write(ex);

}

}

/// <summary>

///

/// </summary>

/// <param name="worksheet"></param>

/// <param name="row"></param>

/// <param name="col"></param>

/// <param name="data"></param>

public void AddData(Worksheet worksheet, int intRowIndex, int intColumnIndex, string strData)

{

try

{

worksheet.Cells[intRowIndex, intColumnIndex] = strData;

}

catch (Exception ex)

{

Logger.Write(ex);

}

}

/// <summary>

/// Saves the workbook to the disk

/// </summary>

/// <param name="strFilePath"></param>

public void SaveWorkbook(string strFilePath)

{

try

{

workbook.SaveAs(strFilePath, XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

}

catch (Exception ex)

{

Logger.Write(ex);

}

}

/// <summary>

/// Stop the excel application process

/// </summary>

public void ShutDownExcel()

{

try

{

workbook = null;

application.Quit();

}

catch (Exception ex)

{

Logger.Write(ex);

}

}

}

The example usage is as follows:

ExportToExcel export = new ExportToExcel();

export.ExportTable(dataTable,"Master Sheet", strFilePath);

Where ‘master sheet’ is the name of the workbook to be added..

Advertisements