Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Javascript .NET4
Hi
 
I am developing a web application in C#.I want to export data from my application to Excel.I need it badly,plz help me out in this regard
 
Thanking you
Posted 16-May-12 0:00am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
v2
Comments
VJ Reddy at 16-May-12 19:41pm
   
Good references. 5!
Prasad_Kulkarni at 16-May-12 23:43pm
   
Thank you VJ!
Member 10850583 at 2-Sep-14 15:17pm
   
I am having an issue where the EXCEL file needs to be saved before I can open it. If I open it before saving I get an error: http://www.codeproject.com/Questions/814410/Why-is-exporting-GridView-data-to-Excel-exports-th?arn=0
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

  Permalink  
Comments
VJ Reddy at 16-May-12 19:42pm
   
Useful reference. 5!
Mika Wendelius at 17-May-12 4:20am
   
Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Comments
jagadeeshmn at 16-May-12 6:41am
   
Thanks a lot for ur help and it worked well for me.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

This is the best working example
 
The DataGridViewPrinter Class[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

#region Export Grid to Excel
public static void Export(string File, GridView GridView1, string CompanyName, string ReportName, string Criteria1, string Criteria2,int FirstColSpan,int SecColSpan,int ThirdColSpan,int ForthColSpan)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + File);
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
Table table = new Table();
 
// Report Header Start
TableRow Tr = new TableRow();
TableCell TC = new TableCell();
TC.Text = CompanyName;
TC.ColumnSpan = FirstColSpan;
TC.Font.Size = 22;
TC.Font.Bold = true;

 
TableCell TC1 = new TableCell();
TC1.Text = ReportName;
TC1.ColumnSpan = SecColSpan;
TC1.Font.Size = 22;
TC1.Font.Bold = true;
TC1.HorizontalAlign = HorizontalAlign.Right;
Tr.Cells.Add(TC);
Tr.Cells.Add(TC1);
 
TableRow Tr1 = new TableRow();
TableCell TC2 = new TableCell();
TC2.Text = Criteria1;
TC2.ColumnSpan = ThirdColSpan;
TC2.HorizontalAlign = HorizontalAlign.Left;
TC2.Font.Size = 12;
TC2.Font.Bold = true;
 
TableCell TC3 = new TableCell();
TC3.Text = Criteria2;
TC3.ColumnSpan = ForthColSpan;
TC3.Font.Size = 12;
TC3.Font.Bold = true;
TC3.HorizontalAlign = HorizontalAlign.Right;
Tr1.Cells.Add(TC2);
Tr1.Cells.Add(TC3);
 
table.Rows.Add(Tr);
table.Rows.Add(Tr1);
// Report Header End
 
// include the gridline settings
table.GridLines = GridView1.GridLines;
if (GridView1.HeaderRow != null)
{
//GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
PrepareControlForExport(GridView1.HeaderRow);
table.Rows.Add(GridView1.HeaderRow);
}
 
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
//Change Color back to white
row.BackColor = System.Drawing.Color.White;
//Apply text style to each Row
row.Attributes.Add("class", "textmode");
//Apply style to Individual Cells of Alternating Row
 
//if (i % 2 != 0)
//{
//for (int y = 0; y <= row.Cells.Count - 1; y++)
//{
// row.Cells[y].BackColor = System.Drawing.Color.FromName(GetCellcolor(row.Cells[y].Text));
 
//}
// }
 
}
 
foreach (GridViewRow row in GridView1.Rows)
{
//GridViewExportUtil.PrepareControlForExport(row);
PrepareControlForExport(row);
table.Rows.Add(row);
GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
//TableCell oTableCell = new TableCell();
//oTableCell.Text = "";
//oGridViewRow.Cells.Add(oTableCell);
//PrepareControlForExport(oGridViewRow);
// table.Rows.Add(oGridViewRow);
}
 
// add the footer row to the table
if (GridView1.FooterRow != null)
{
//GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
PrepareControlForExport(GridView1.FooterRow);
table.Rows.Add(GridView1.FooterRow);
}
 
table.RenderControl(hw);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
 

 
}
 
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
 
if (current.HasControls())
{
Util.PrepareControlForExport(current);
//GridViewExportUtil.PrepareControlForExport(current);
PrepareControlForExport(current);
}
}
}
#endregion
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

In this article, i have explained how to export data to excel with formatting.
 
Export DataTable to Excel with Formatting in C#[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 10

clsGridViewExportUtil.ExportDataTableToExcel("Sheet1.xlsx", DT, "Vechile Master");
 

 
public static void ExportDataTableToExcel(string fileName, DataTable oDataTable, string Header2)
{
ExportToSpreadsheet(fileName, oDataTable,null, Header2);
}
public static bool ExportToSpreadsheet(string fileName, DataTable oDataTable, DataControlFieldCollection Columns, string Header2)
{
clsValuePair[] Headers=null;
bool FormatSupported = true;
if (Columns != null)
{
if (Columns.Count > 0)
{
Headers = new clsValuePair[Columns.Count];
for (int i = 0; i <= Columns.Count - 1; i++)
{
Headers[i] = new clsValuePair();
if ((Columns[i].SortExpression == null || Columns[i].SortExpression=="") && Columns[1].Visible)
{
FormatSupported = false;
break;
}
Headers[i].Value = Columns[i].HeaderText;
Headers[i].Name = Columns[i].SortExpression;
}
}
}
if(FormatSupported)
ExportToSpreadsheet(fileName, Header2, Headers, oDataTable);
return FormatSupported;
}
 

public static void ExportToSpreadsheet(string fileName, string Header2, clsValuePair[] Headers, DataTable oDataTable)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
 

ExcelWriter writer = new ExcelWriter(HttpContext.Current.Response.OutputStream);
writer.BeginWrite();
writer.WriteFormat(@"dd\-MM\-yyyy"); //index 0
writer.WriteFormat(@"dd/MM/yyyy");
writer.WriteFormat(@"0,00,000");
 

System.Text.RegularExpressions.Regex oRegEx = new System.Text.RegularExpressions.Regex("<[^>]+>");
Header2=oRegEx.Replace(Header2," ");
 
writer.WriteCell(0, 0, Header2);
// add each of the data rows to the table
int Len;

if (Headers==null)
{
Len = oDataTable.Columns.Count - 1;
Headers = new clsValuePair[Len+1];
for (int j = 0; j <= Len; j++)
{
Headers[j] = new clsValuePair();
Headers[j].Name =CNFUtil.ConvertString(oDataTable.Columns[j].ColumnName);
Headers[j].Value = Headers[j].Name;
}
}

Len = Headers.Length - 1;
for (int j = 0; j <= Len; j++)
{
writer.WriteCell(1, j, Headers[j].Value);
}
for (int i = 0; i <= oDataTable.Rows.Count - 1; i++)
{
 
for (int j = 0; j <= Len; j++)
{
writer.WriteCell(i + 2, j, oDataTable.Rows[i][Headers[j].Name]);
}
}
writer.EndWrite();
HttpContext.Current.Response.End();
}
  Permalink  
v2
Comments
CHill60 at 23-Jul-14 10:45am
   
2 years late and this unformatted code dump doesn't add anything to the other solutions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 200
1 Kornfeld Eliyahu Peter 175
2 BillWoodruff 160
3 George Jonsson 140
4 OriginalGriff 136


Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 23 Jul 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100