Solved

how to export data to excel from gridview

Posted on 2006-11-13
7
420 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project

856 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