title: Export gridview data to xlsx format?
body: <p>I have a big gridview as part of a web application which has a combination of bound fields and template fields and has DataFormatStrings defined that define how values are represented on the cells. I am having a hard time converting all this to a datatable so that I can write them to an excel sheet. </p>
  
  <p>Converting the gridview directly to excel forces me to use String Write and Html Write which produce only .xls format. We are looking at standardizing all excel download options to the .xlsx format. Here is what I tried so far</p>
  
  <pre><code>dt = gvInvoicesEX.DataSource as DataTable;
  </code></pre>
  
  <p>The DataSource property returns null for some reason. I have also tried the normal loop within loop technique to go over each cell in the gridview and copy it to the datatable but I just get a datatable full of blank cells.</p>
  
  <pre><code>for (int i = 0; i &lt; gvInvoicesEX.Rows.Count; i++)
  {
       DataRow dr = dt.NewRow();
       for (int j = 1; j &lt; gvInvoicesEX.Columns.Count; j++)
       {
            gvInvoicesEX.Rows[i].Cells[j].Attributes.Add("class", "text");
            dr[j - 1] = gvInvoicesEX.Rows[i].Cells[j].Text;   
       }
       dt.Rows.Add(dr);
  }
  </code></pre>
  
  <p>The following is my excel conversion code:</p>
  
  <pre><code>using (var wb = new XLWorkbook())
  {
      DataTable dt = GetDataTableFromGridView();
      string fileName = "BillingDetail" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
      wb.Worksheets.Add(dt);
      Response.Clear();
      Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
      Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
      using (var ms = new System.IO.MemoryStream())
      {
          wb.SaveAs(ms);
          ms.WriteTo(Response.OutputStream);
          ms.Close();
      }
      Response.End();
  }
  </code></pre>
  
  <p>Is there anything I can change in any of the above to make this all work smoothly?</p>