Importing the data from database to the excel sheet.

Question

Hi,

 

  I want to import the data from database to the excel sheet. I am able to import the data to a csv file. The below is the method i have used to import to the csv file. Can i be able to import the data directly to the .xls (Excel) File.

If so , please post your suggestions.

 

The below code was placed in a class file  (clsHelper),

    public static void ExportToExcel(System.Data.DataTable table, string name)

    {

        HttpContext context = HttpContext.Current;

        context.Response.Clear();

        foreach (DataColumn column in table.Columns)

        {

            context.Response.Write(column.ColumnName + ",");

        }

        context.Response.Write(Environment.NewLine);

        foreach (DataRow row in table.Rows)

        {

            for (int i = 0; i < table.Columns.Count; i++)

            {

                context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");

            }

            context.Response.Write(Environment.NewLine);

        }

        context.Response.ContentType = "text/csv";

        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");

        context.Response.End();

    } 

I am passing the data in the below format.

clsHelper.ExportToExcel(dt,"excelname");

 

With Regards,

Bharat

 

Answer 1

Following post gives excellent direction about how to export gridview data to excel sheet. Hope this resolves your problem.

http://blogs.msdn.com/b/erikaehrli/archive/2009/01/30/how-to-export-data-to-excel-from-an-asp-net-application-avoid-the-file-format-differ-prompt.aspx

---

Thanks

Answer 2

Hi vishvvas,

 

      I have tried this earlier with the gridview control , the problem here is i am showing the records in the gridview control based on the filter search criteria , so i was able to show only the limited records . Is there any other solution for getting the complete set of records to a dataset or datatable and export it to the excel sheet.

 

Thanks,

Bharat

Answer 3

If you are using SQL Server, you might consider DTS package. Create a package you can easily query the data from database and convert to excel. You can execute the package dierectly or even call from .net.

Answer 4

An alternative could be use the dataset to populate intermediate grid and export the data as demonstrated below.

    protected void ExportToExcel(DataSet dataSetToBeExported, Path fileName )
    { 
      HttpContext currentContext= HttpContext.Current;
      HttpResponse currentResponse= currentContext.Response;
      
     // content type is now excel rather than text/csv
      currentResponse .ContentType = "application/vnd.ms-excel";
      currentResponse.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\""); 
 
      try

     {
      StringWriter stringWriter = new StringWriter();
      
        HtmlTextWriter htmltextWriter = new HtmlTextWriter(stringWriter);
        
          // use an intermediate datagrid so to employ its renderControl abilities
          DataGrid dataGrid = new DataGrid();
        //assuming the data to be exported is in first datatable
          dataGrid.DataSource = dataSetToBeExported.Tables[0]; 
          dataGrid.DataBind(); 
          dataGrid.RenderControl(htmltextWriter); 
          currentResponse.Write(stringWriter.ToString()); 
          currentResponse.End(); 
      
      }
      catch(Exception ex)
      {}
      finally
      {}
    }

 

 

Answer 5

Hi Vishvvas,

                     Sorry for the late reply, I have tested the above code,  Here I have exported the code to the excel sheet from the data grid. On opening the excel file i was getting the error as the file you are trying to open is in a different format and to is asking to verify the file is not corrupted and is from a trusted source before opening the file.  

 

As I have to again upload the downloaded file by making minor changes in it. Please suggest on this.

 

Answer 6

 

Hi,

 

From link in Vishvvas's first post, a KB article has been provided to resolve this problem.

You can follow the steps in the KB.

 

I hope this can help you.

Sincerely,

---

Paul Zhou [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.