Posted by: R Manimaran | September 26, 2008

DataTable to Excel File

• Interop.Excel
• Interop.Office
• Microsoft.Vbe.Interop

private static Excel.Application app = null;
private static Excel.Workbook workbook = null;
private static Excel.Worksheet worksheet = null;
private static Excel.Range worksheet_range = null;

public static void CreateExcelDoc()
app = new Excel.Application();
app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Excel.Worksheet)workbook.Sheets[1];
catch (Exception ex)
throw new ApplicationException(“Error while creating the Excel
file.Message:” + ex.Message);
public static void CloseDoc(string Path)
workbook.Close(true, Path, false);
if (worksheet != null)
if (workbook != null)
if (app != null)

private static void InsertSummaryDataToExcel(DataTable dtResult, ref int row)
int col=0;
string WorksheetRangeSt = string.Empty;
string WorksheetRangeEnd = string.Empty;
foreach (DataRow dtRow in dtResult.Rows)
WorksheetRangeSt = “A” + row.ToString();
foreach (DataColumn dtCol in dtResult.Columns)
worksheet.Cells[row, col] = dtRow[dtCol.ColumnName].ToString();
col = col + 1;
row = row + 1;
WorksheetRangeEnd = “E” + row.ToString();
worksheet_range = worksheet.get_Range(WorksheetRangeSt,
worksheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
worksheet_range.Interior.Color = System.Drawing.Color.Ivory.ToArgb();
row = row + 1;



  1. My opinion is that you should avoid COM automation (, especially on server.
    Alternatively one could use some 3rd party library. For example,
    GemBox.Spreadsheet Free read/write Excel component can be used for XLS / CSV / XLSX for small files.

  2. I suggest to use a excel component,I feel it other easy way to read and write excel. maybe best excel component to .net. too powerful !!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: