how to Design the exported excel in asp.net?

Question

I am exporting gridview data to excel but that excel file add the some lable text and textbox values and format also taken , pls give how to design the excel code in asp.net. I am writing like this

• All gridview data taken dataset ds -pls give me textbox values and some label or normal text designed code in excel

  GridView GridView1 = new GridView(); GridView1.AllowPaging = false; GridView1.DataSource = ds; GridView1.DataBind();

      Response.Clear();
      Response.Buffer = true;
      Response.AddHeader("content-disposition",
      "attachment;filename=DataTable.xls");
      Response.Charset = string.Empty;
      Response.ContentType = "application/vnd.ms-excel";
      StringWriter sw = new StringWriter();
      HtmlTextWriter hw = new HtmlTextWriter(sw);
  
      for (int i = 0; i < GridView1.Rows.Count; i++)
      {
      ////Apply text style to each Row
              GridView1.Rows[i].Cells[4].Attributes.Add("class", "textmode");
      }
      GridView1.RenderControl(hw);
  
      //style to format numbers to string
      string style = @"<style> .textmode { mso-number-format:'\#,\#\#0\.00';}
      .textmode1(mso-number-format:'\@';}
      .SSNmode{mso-number-format:'000-00-000';} </style>";
  
  
      Response.Write(style);
      Response.Output.Write(sw.ToString());
      Response.Flush();
      Response.End(); 
  

Answer 1

Take a look at this awesome tool: http://www.carlosag.net/Tools/ExcelXmlWriter/

Futhermore, the author also created a code generator, which generates .net code from a provided excel sheet(formatting, layout and so on)
http://www.carlosag.net/Tools/ExcelXmlWriter/Generator.aspx

Here's some code that generates an excel file from a datatable (converted vb code, as pr. request by poster)

        CarlosAg.ExcelXmlWriter.Workbook book = new CarlosAg.ExcelXmlWriter.Workbook();
        book.ExcelWorkbook.ProtectWindows = false;

        book.ExcelWorkbook.ProtectStructure = false;

        var styles = book.Styles;
        WorksheetStyle defaultStyle = styles.Add("Default");
        var defStyles = defaultStyle;
        defStyles.Name = "Normal";
        defStyles.Font.FontName = "Calibri";
        defStyles.Font.Size = 11;
        defStyles.Font.Color = "#000000";
        defStyles.Alignment.Vertical = StyleVerticalAlignment.Bottom;

        Worksheet sheet = book.Worksheets.Add("Sheet1");
        sheet.Table.DefaultRowHeight = 15f;
        sheet.Table.FullColumns = 1;
        sheet.Table.FullRows = 1;
        DataTable dt = new DataTable();  //=  your datatable

        //Header
        WorksheetRow HeaderRow = sheet.Table.Rows.Add();
        foreach (DataColumn col in dt.Columns)
        {
            HeaderRow.Cells.Add(col.ColumnName.ToString());

        }
        //Body
        foreach (DataRow dr in dt.Rows)
        {
            WorksheetRow row = sheet.Table.Rows.Add();
            foreach (DataColumn cols in dt.Columns)
            {
                  row.Cells.Add(dr[cols.ColumnName.ToString()].ToString());
            }
        }

        sheet.Options.Selected = true;
        sheet.Options.ProtectObjects = false;
        sheet.Options.ProtectScenarios = false;

        book.Save("path to file");