Hi,
Add reference below in your project
Microsoft office 12.0 controls library
After that use below code
add namespace
using Microsoft.Office.Interop.Excel;
ApplicationClass excel = new ApplicationClass();
Workbook wBook;
Worksheet wSheet;
wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
wSheet = (Worksheet)wBook.ActiveSheet;
System.Data.DataTable dt = dset.Tables[0];
System.Data.DataColumn dc = new DataColumn();
int colIndex = 0;
int rowIndex = 4;
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
excel.Cells[5, colIndex] = dcol.ColumnName;
}
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
}
}
wSheet.Columns.AutoFit();
String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");
Boolean blnFileOpen = false;
try
{
System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
fileTemp.Close();
}
catch
{
blnFileOpen = false;
}
if (System.IO.File.Exists(strFileName))
{
System.IO.File.Delete(strFileName);
}
Range oRng;
wSheet.Cells[1, 2] = lblOffice1.Text;
wSheet.Cells[3, 2] = lblCostCenter1.Text;
wSheet.Cells[4, 1] = lblOfficeName1.Text;
wSheet.get_Range("B1", "B1").Font.Bold = true;
wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
wSheet.get_Range("B1", "E1").Merge(Type.Missing);
wSheet.get_Range("B3", "E3").Merge(Type.Missing);
wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
wSheet.get_Range("B3", "B3").Font.Bold = true;
wSheet.get_Range("A4", "A4").Font.Bold = true;
wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
wSheet.get_Range("A5", "P5").Font.Bold = true;
wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
wSheet.Name = "Stock Statement";
oRng = wSheet.get_Range("A1", "P1");
oRng.EntireColumn.AutoFit();
wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);