Click here to Skip to main content
15,845,436 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am using the below code to export ASP.NET grid to Excel (.xlsx format only, I don't want .xls format) entire table as well as template fields which contain link buttons and hyperlinks. I have found some code in CodeProject, but it is not exporting the link buttons, instead it shows as text format, in the code if I don't convert into TEXT, the data itself is not showing:
C#
grd_financereport.AllowSorting = false;
       grd_financereport.AllowPaging = false;
       this.ShowData();
       EnableViewState = false;
       Response.Clear();
       DataTable dt2 = new DataTable("FinanceReports");
       //Adding Headers of Columns
       foreach (TableCell cell in grd_financereport.HeaderRow.Cells)
       {
           dt2.Columns.Add(cell.Text.Replace(" ", ""));
       }
       //Getting Each Row Data from Grid
       foreach (GridViewRow row in grd_financereport.Rows)
       {
           dt2.Rows.Add();
           for (int i = 0; i < row.Cells.Count; i++)
           {
               if (!string.IsNullOrEmpty(row.Cells[i].Text))
               {
                   dt2.Rows[dt2.Rows.Count - 1][i] =
                       row.Cells[i].Text.Replace(" ", "");
               }
               else
               {
                   Literal l = new Literal();
                   List<Control> controls = new List<Control>();
                   foreach (Control control in row.Cells[i].Controls)
                   {
                       controls.Add(control);
                   }
                   //Adding Special Controls to excel
                   foreach (Control control in controls)
                   {
                       switch (control.GetType().Name)
                       {
                           case "Label":
                               string label = (control as Label).Text;
                               dt2.Rows[dt2.Rows.Count - 1][i] = label;
                               break;
                           case "TextBox":
                               string textbox = (control as TextBox).Text;
                               dt2.Rows[dt2.Rows.Count - 1][i] = textbox;
                               break;
                           case "HyperLink":
                               string hyperLink =
                               (control as HyperLink).Text;
                               dt2.Rows[dt2.Rows.Count - 1][i] = hyperLink;
                               break;
                           case "LinkButton":

                               string linkButton =
                                      (control as LinkButton).Text;
                               dt2.Rows[dt2.Rows.Count - 1][i] = linkButton;
                               break;
                           case "CheckBox":
                               string checkBox = (control as CheckBox).Text;
                               dt2.Rows[dt2.Rows.Count - 1][i] = checkBox;
                               break;
                           case "RadioButton":
                               string radioButton =
                                      (control as RadioButton).Text;
                               dt2.Rows[dt2.Rows.Count - 1][i] = radioButton;
                               break;
                       }
                       row.Cells[i].Controls.Add(control);
                   }
               }
           }
       }

       //By Using ClosedXML.Excel Dll, Exported Grid to Excel(XLSX) Format
       using (XLWorkbook wb = new XLWorkbook())
       {
           wb.Worksheets.Add(dt2);
           Response.Clear();
           Response.Buffer = true;
           Response.Charset = "";
           Response.ContentType =
           "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
           Response.AddHeader
           ("content-disposition", "attachment;filename=ContractsReport.xlsx");
           using (MemoryStream MyMemoryStream = new MemoryStream())
           {
               wb.SaveAs(MyMemoryStream);
               MyMemoryStream.WriteTo(Response.OutputStream);
               Response.Flush();
               Response.End();
           }
       }


What I have tried:

I tried to add directly without converting into Text, but if I don't... The data itself is not showing in Excel.
Posted
Updated 27-Oct-23 5:32am
v2

1 solution

I tried to add without converting to Text too.
 
Share this answer
 
Comments
Richard Deeming 27-Oct-23 9:52am    
How precisely is this meant to be a solution to the question?!

And why have you written this non-solution as if you are the person who posted the question?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900