Introduction
This post shows that how you can export data from
gridview
to Excel and formatting Excel file in C#.
Follow the below process:
- Add reference below in your project:
Microsoft Office 12.0 Control Library
- Now, add NameSpace in your Page at which you want to use this functionality:
using Microsoft.Office.Interop.Excel;
- At click event of button (Export to Excel), call below function:
private void ExportToExcel()
{
DataSet dset = new DataSet();
dset.Tables.Add();
for (int i = 0; i < gridView.Columns.Count; i++)
{
dset.Tables[0].Columns.Add(gridView.Columns[i].HeaderText);
}
System.Data.DataRow dr1;
for (int i = 0; i < gridView.Rows.Count; i++)
{
dr1 = dset.Tables[0].NewRow();
System.Web.UI.WebControls.Label lblCCName = (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblCCName");
System.Web.UI.WebControls.Label lblItemName = (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblItemName");
System.Web.UI.WebControls.Label lblItemCode = (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblItemCode");
dr1[0] = lblCCName.Text.ToString();
dr1[1] = lblItemName.Text.ToString();
dr1[2] = lblItemCode.Text.ToString();
dset.Tables[0].Rows.Add(dr1);
}
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);
}
}
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);
- Before saving, you can format the Excel data using the below code:
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();
You can use the above code for Export data to Excel using gridview
in C#. It saves Excel file to server. You can use download code for save Excel file from server to client system.
Point of Interest
Using this code, you will get proper Excel file and formatted. You can use formula on columns.
Using there should be full permissions to IIS user and Network Service User.
When you use the above code, then there can be some issue, then add below code in
web.config file
<identity impersonate="true"/>
If anybody has any issues with this topic, send me comments. I will provide your solution.