Solved

how to export data to excel from gridview

Posted on 2006-11-13
7
416 Views
Last Modified: 2012-05-05
how to export data to excel from gridview
0
Comment
Question by:itssoo
  • 2
7 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17936189
If u r using Dev express

then u have

grdview.ExpotToExcel(filename);
0
 
LVL 16

Accepted Solution

by:
Swapnil Piparia earned 63 total points
ID: 17936279
Hi itssoo,
on export button click write following

        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"

        Response.AddHeader("content-disposition", "attachment; filename=filename.xls") ' Set a file name

Regards,
NetSwap
0
 
LVL 8

Assisted Solution

by:redpipe
redpipe earned 62 total points
ID: 17936722
try this method:

public void DoExportAdo(DataGridView dgv, string FilePath, ProgressBar prg, string xlSheet)
    {
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=" + FilePath + ";" +
                          "Extended Properties=\"Excel 8.0;HDR=YES\""; //HDR => column headers
      OleDbCommand cmd = new OleDbCommand();
      StringBuilder sbCreate = new StringBuilder();
      StringBuilder sbCols = new StringBuilder();
      StringBuilder sbValues = new StringBuilder();
      string insertString;

      using (OleDbConnection conn = new OleDbConnection(connString)) {
        cmd.Connection = conn;
        foreach (DataGridViewColumn col in dgv.Columns) {
          sbCols.Append(col.HeaderText + ",");
          if (col.DefaultCellStyle.Format.StartsWith("N")) {
            sbCreate.Append(col.HeaderText + " INT, ");
          } else {
            sbCreate.Append(col.HeaderText + " CHAR(255), ");
          }
        }
        cmd.CommandText = "CREATE TABLE " + xlSheet + " (" +
                          StringUtils.removeFromLastChar(sbCreate, ',') + ")";
        if (conn.State != ConnectionState.Open) { conn.Open(); }
        cmd.ExecuteNonQuery();

        insertString = "INSERT INTO " + xlSheet + " (" +
                       StringUtils.ReplaceLast(sbCols.ToString(), ",", ") VALUES (");
        string value;
        foreach (DataGridViewRow row in dgv.Rows) {
          sbValues = new StringBuilder();
          if (row.IsNewRow) { continue; }
          foreach (DataGridViewColumn col in dgv.Columns) {
            if (StringUtils.isEmpty(row.Cells[col.Index].Value)) {
              sbValues.Append("'', ");
            } else {
              value = row.Cells[col.Index].Value.ToString();
              if(StringUtils.isNumeric(value)){
                sbValues.Append(StringUtils.toDbDecimal(value) + ", ");
              } else {
                sbValues.Append("'" + value + "', ");
              }
            }
          }
          cmd.CommandText = insertString +
                            StringUtils.ReplaceLast(sbValues.ToString(), ",", ")");
          if (conn.State != ConnectionState.Open) { conn.Open(); }
          cmd.ExecuteNonQuery();
          if (prg != null) {
            prg.PerformStep();
          }
          Application.DoEvents();
        }
        CloseResources(conn, cmd, null, null);
      }
    }
0
 
LVL 8

Expert Comment

by:redpipe
ID: 18128713
My suggestion is copied from one of my applications that has been running in production for months. I have never had any problems with it.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Best resource to learn entity framework 2 32
Cant save 3D 4 19
Problem!!! 8 33
.NET 2008 VB and C# 6 27
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question