Posted by: R Manimaran | September 26, 2008

DataTable to Excel File

ADD REFERENCE
• 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()
{
try
{
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);
app.Quit();
if (worksheet != null)
Marshal.ReleaseComObject(worksheet);
if (workbook != null)
Marshal.ReleaseComObject(workbook);
if (app != null)
Marshal.ReleaseComObject(app);
}

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)
{
col=1;
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,
WorksheetRangeEnd);
worksheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
worksheet_range.Interior.Color = System.Drawing.Color.Ivory.ToArgb();
row = row + 1;
}

Advertisements

Responses

  1. My opinion is that you should avoid COM automation (http://www.gemboxsoftware.com/GBSpreadsheet.htm#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 !!
    http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Categories

%d bloggers like this: