Solved

how to export data to excel from gridview

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now