Using Excel = microsoft.office.interop.excel;//Add Microsoft.Office.Interop.Excel.dll reference
Class Excelmanager
{
public delegate void Progressbareventhandler (int max, int value);//Export progress bar required maximum and progress value
public event Progressbareventhandler Progressbarevent;
public delegate void Progressbarokeventhandler (int sum);//export Record line number
public event Progressbarokeventhandler Progressbarokevent;
public void exportToExcel (System.Data.DataTable dt,string fileName)
{
if (dt = = NULL | | dt. Rows.Count = = 0) return;
Excel.Application xlapp = new Excel.Application ();
if (xlapp = = null)
{
Return
}
Excel.Workbook book = XlApp.Workbooks.Add (True);
Excel.Worksheet sheet = (excel.worksheet) book. WORKSHEETS[1];
Excel.Range range1 = null;
Excel.Range range2 = null;
Excel.Range range3 = null;
int rowscount = dt. Rows.Count;
int COLUMNSCOUNT=DT. Columns.count;
Object[] Arrheader=new Object[columnscount];
for (int i = 0; i < Columnscount; i++)
{
Arrheader[i] = dt. Columns[i]. ColumnName;
}
Range1 = sheet. Range[sheet. Cells[1, 1], sheet. Cells[1, Columnscount]];
Range1. Value2 = Arrheader;
Range1. HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Range1. VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Range1. Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
Range1. Interior.ColorIndex = 15;
Range1. Font.Bold = true;
object[,] arrbody = new Object[rowscount, Columnscount];
for (int i = 0; i < Rowscount; i++)
{
for (int j = 0; J < Columnscount; J + +)
{
Arrbody[i, j] = dt. ROWS[I][J]. ToString ();
}
if (progressbarevent! = null) progressbarevent (rowscount,i+1);
}
Range2 = sheet. Range[sheet. Cells[2, 1], sheet. Cells[rowscount+1, Columnscount]];
Range2. Value2 = Arrbody;
Range2. HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Range2. VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Range2. Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
Range3 = sheet. Range[sheet. Cells[1, 3], sheet. Cells[rowscount + 1, 3]];
Range3. NumberFormat = @ "Yyyy/mm/dd HH:mm:ss";
Book. SaveAs (FileName);
Xlapp.visible = true;
Book. Close ();
xlApp.Quit ();
Gc. Collect ();
if (progressbarokevent! = null) progressbarokevent (Rowscount);
}
}
C # Exports data from a DataTable to Excel