protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dataToExport = GetData();
ExportToExcelFile(dataToExport);
}
private DataTable GetData()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("FirstName", typeof(string));
dt.Columns.Add("LastName", typeof(string));
//Add Row1
DataRow dr = dt.NewRow();
dr["ID"] = 100;
dr["FirstName"] = "Phaneendra";
dr["LastName"] = "Bitra";
dt.Rows.Add(dr);
//Add Row2
dr = dt.NewRow();
dr["ID"] = 101;
dr["FirstName"] = "Kranthi";
dr["LastName"] = "Gullapalli";
dt.Rows.Add(dr);
//Add Row3
dr = dt.NewRow();
dr["ID"] = 102;
dr["FirstName"] = "Kiran";
dr["LastName"] = "Gunda";
dt.Rows.Add(dr);
//Add Row4
dr = dt.NewRow();
dr["ID"] = 103;
dr["FirstName"] = "Siva";
dr["LastName"] = "Kuchi";
dt.Rows.Add(dr);
return dt;
}
private void ExportToExcelFile(System.Data.DataTable dt)
{
Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;
string strhdr;
int row;
string strFile = "file1.xls";
string filename = Server.MapPath(strFile);
if (dt.Rows.Count > 0)
{
//Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);
//Get the first worksheet
xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
//Activate current worksheet
xlsWorksheet.Activate();
//Set header row to row 1
row = 1;
//Add table headers to worksheet
xlsWorksheet.Cells[row, 1] = "ID";
xlsWorksheet.Cells[row, 2] = "FirstName";
xlsWorksheet.Cells[row, 3] = "LastName";
//Format header row (bold, extra row height, autofit width)
xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();
//Freeze the columm headers
xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
xlsApp.ActiveWindow.FreezePanes = true;
//Write data to Excel worksheet
foreach (DataRow dr in dt.Rows)
{
row += 1;
if (dr["ID"] != null)
xlsWorksheet.Cells[row, 1] = dr["ID"];
if (dr["FirstName"] != null)
xlsWorksheet.Cells[row, 2] = dr["FirstName"];
if (dr["LastName"] != null)
xlsWorksheet.Cells[row, 3] = dr["LastName"];
}
//Format data rows (align to center and left, autofit width and height)
xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();
xlsWorksheet.get_Range("A:A", Missing.Value).EntireColumn.Hidden = true;
//Make excel workbook visible to user after all data has been added to worksheet.
xlsApp.DisplayAlerts = false;
xlsWorkbook.Close(true, filename, null);
//Export data to client machine
strhdr = "attachment;filename=" + strFile;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.AppendHeader("Content-Disposition", strhdr);
Response.WriteFile(filename);
Response.Flush();
Response.Clear();
Response.Close();
}
xlsApp.Quit();
}