ASP.NET - export dynamically created table to excel

Asked By anbu n on 31-Oct-11 06:05 AM



the above is dynamically created table , this has to be exported to excel
smr replied to anbu n on 31-Oct-11 06:09 AM
hi

(1)You should context.Response.Write(output ) to send the stream to user.

(2)You should use Response.End() method to finish the request at the end of the code.

Please check the following link:

http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

http://www.codeproject.com/KB/office/ExcelDataTable.aspx

http://madskristensen.net/post/Export-a-DataTable-to-Excel-in-ASPNET.aspx

Reena Jain replied to anbu n on 31-Oct-11 06:14 AM
hi,

Example imports/exports DataTable to Excel file (in XLS format) by directly working with DataTable using InsertDataTable and ExtractToDataTable methods:

ExcelFile ef = new ExcelFile();
DataTable dataTable = new DataTable();
  
// Depending on the format of the input file, you need to change this:
dataTable.Columns.Add("FirstName", typeof(string));
dataTable.Columns.Add("LastName", typeof(string));
  
  
// Load Excel file.
ef.LoadXls("FileName.xls");
  
// Select the first worksheet from the file.
ExcelWorksheet ws = ef.Worksheets[0];
  
// Extract the data from the worksheet to the DataTable.
// Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
  
// Change the value of the first cell in the DataTable.
dataTable.Rows[0][0] = "Hello world!";
  
// Insert the data from DataTable to the worksheet starting at cell "A1".
ws.InsertDataTable(dataTable, "A1", true);
  
// Save the file to XLS format.
ef.SaveXls("DataTable.xls");

try this and let me know
dipa ahuja replied to anbu n on 31-Oct-11 06:26 AM
Untitled document
public static void ExportDataSetToExcel(DataSet ds, string filename)
{
 
  DataTable dt = new DataTable();
 
  //Adding columns
 
  dt.Columns.Add("Names"typeof(string));
 
  //Adding Row
  DataRow dr = dt.NewRow();
  dr["Names"] = "dipa";
  dt.Rows.Add(dr);
 
  dr = dt.NewRow();
  dr["Names"] = "priya";
  dt.Rows.Add(dr);
 
  HttpResponse response = HttpContext.Current.Response;
 
  // first let's clean up the response.object
  response.Clear();
  response.Charset = "";
 
  // set the response mime type for excel
  response.ContentType = "application/vnd.ms-excel";
  response.AddHeader("Content-Disposition""attachment;filename=\"" + filename + "\"");
 
  // create a string writer
  using (StringWriter sw = new StringWriter())
  {
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
      // instantiate a datagrid
      DataGrid dg = new DataGrid();
      dg.DataSource = dt;
      dg.DataBind();
      dg.RenderControl(htw);
      response.Write(sw.ToString());
      response.End();
    }
  }
}