Solved

how to export data to excel from gridview

Posted on 2006-11-13
7
425 Views
Last Modified: 2012-05-05
how to export data to excel from gridview
0
Comment
Question by:itssoo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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