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 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 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.
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  

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

  Print Answers RSS
0 OriginalGriff 328
1 Sergey Alexandrovich Kryukov 295
2 Gihan Liyanage 292
3 ClimerChinna 232
4 Abhinav S 192
0 Sergey Alexandrovich Kryukov 8,213
1 OriginalGriff 7,089
2 CPallini 2,598
3 Richard MacCutchan 1,925
4 Abhinav S 1,778


Advertise | Privacy | Mobile
Web03 | 2.8.140826.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