none
easiest way to export a datatable to an excel file RRS feed

  • Question

  • Hi,

    What is the easiest and quickest way to export a datatable to an excel file, pls?

    Thanks
    Monday, December 15, 2008 2:22 PM

All replies

  • What language are you using, and how are you accessing the excel file?

    the easiest way to get data from a database into a spreadsheet is to use

    Excel.Range arng;

    set arng to some starting cell

    then using ADODB rather than ado.net

    adodb.recordset rs = someCommand.Execute();
    arng.CopyFromRecordSet(rs);

    Monday, December 15, 2008 3:23 PM
  • either c# or vb.net. All I want to do is: from an existing datatable, generate an excel file and save it on the disk, and display it after to the user. But the code I am interested in is: transform the datatable data to a new excel file.

    Thanks
    Monday, December 15, 2008 3:26 PM
  • Have you looked into using Office OpenXML @ OpenXMLDeveloper.org 

    OpenXML SDK forum
    http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/threads/

    Regards
    Tuesday, December 16, 2008 7:15 AM
  • Hi Below is function for

    Common.Data.Export2Excel(dataTableWithData, "exported.xls");

    ExportData Table to Excel using C#

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
     
    namespace Common.Data
    {
        public class Export2Excel
        {
            public static bool Export(DataTable tbl, string fileName, bool createTable, bool overwriteFile)
            {
          string tableName = tbl.TableName.Replace(" ", "_");
                tableName = String.IsNullOrEmpty(tableName) ? "Sheet1" : tableName;
                try
                {
                    System.Globalization.CultureInfo _infoEn = System.Globalization.CultureInfo.GetCultureInfo("en-GB");
                    string sql = "";
                    if (overwriteFile)
                        if (File.Exists(fileName))
                            File.Delete(fileName);
     
                    using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes'"))
                    {
                        con.Open();
                        OleDbCommand cmdInsert;
                        if (createTable || !File.Exists(fileName))
                        {
                            sql = "CREATE TABLE " + tableName + " (";
                            for (int i = 0; i < tbl.Columns.Count; i++)
                            {
                                sql += tbl.Columns[i].ColumnName;
                                if (i + 1 == tbl.Columns.Count) //Here we decide should we close insert command or appebd another create column command
                                    sql += " " + GetColumnType(tbl.Columns[i]) + ")"; //Close insert
                                else
                                    sql += " " + GetColumnType(tbl.Columns[i]) + ","; //there is more columns to add
                            }
                        }
                        if (!String.IsNullOrEmpty(sql))
                        {
                            cmdInsert = new OleDbCommand(sql, con);
                            cmdInsert.ExecuteNonQuery();
                        }
                        foreach (DataRow row in tbl.Rows)
                        {
                            //Dodati parametre na comandu
                            string values = "(";
                            for (int i = 0; i < tbl.Columns.Count; i++)
                            {
                                if (i + 1 == tbl.Columns.Count)
                                {
                                    if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
                                         tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
                                         tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
                                        values += String.IsNullOrEmpty(row[i].ToString()) ? "0)" : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ")";
                                    else
                                        values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "')";
                                }
                                else
                                {
                                    if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
                                         tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
                                         tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
                                        values += String.IsNullOrEmpty(row[i].ToString()) ? "0," : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ",";
                                    else
                                        values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "',";
                                }
                            }
                            string sqlInsert = String.Format("Insert into [{0}$] VALUES {1}", tableName, values);
                            cmdInsert = new OleDbCommand(sqlInsert, con);
     
                            cmdInsert.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception)
                {
                    return false;
                }
                    return true;
            }
     
            private static string GetColumnType(DataColumn dataColumn)
            {
                string t;
                if (dataColumn.DataType == System.Type.GetType("System.Decimal"))
                    t = "decimal";
                else if (dataColumn.DataType == System.Type.GetType("System.Int64"))
                    t = "INT";
                else if (dataColumn.DataType == System.Type.GetType("System.Double"))
                    t = "double";
                else
                    t = "VARCHAR(255)";
                return t;
            }
            public static bool Export(DataTable tbl, string fileName)
            {
                return Export(tbl, fileName, true, true);
            }
        }
    }

    Monday, May 2, 2011 5:54 AM
  • I was also trying to find the solution to export stored procedure output to excel in visual C#. Finally, I got it... This solution is applicable for larger datatables also.

    • without using datagridview,
    • without looping cell by cell but using array instead
    public void ExportToExcel_StoredProc(string MyConnString, string MyStoredProcedure)
    
    {
    
                //Step 1: Run stored procedure and generate output in a DataTable
                SqlConnection con = new SqlConnection();
                con.ConnectionString = MyConnString;
                con.Open();
    
                SqlDataAdapter ada = new SqlDataAdapter(MyStoredProcedure, con);
                ada.SelectCommand = new SqlCommand();
                ada.SelectCommand.Connection = con;
                ada.SelectCommand.CommandType = CommandType.StoredProcedure;
                ada.SelectCommand.CommandText = MyStoredProcedure;
    
                DataTable tbl = new DataTable();
                ada.Fill(tbl);
    
                //Step 2: copy datatable to array
                int rowcount = tbl.Rows.Count;
                int columncount = tbl.Columns.Count;
    
                var data = new object[rowcount, columncount];
                for (var row = 1; row <= rowcount; row++)
                {
                    for (var column = 1; column <= columncount; column++)
                    {
                        data[row - 1, column - 1] = tbl.Rows[row-1].ItemArray[column-1];          
                    }
                }
    
    
                //Step 3: Create xls variables
                Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
    
                object missing = System.Reflection.Missing.Value;
    
                //Create Header in xls
                int iCol = 0;
                foreach (DataColumn c in tbl.Columns)
                {
                    excel.Cells[1, iCol+1] = c.ColumnName;
                    iCol++;
                }
    
                //Copy array named 'data' to xls
                var startCell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1];
                var endCell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowcount, columncount];
                var writeRange = worksheet.get_Range(startCell, endCell);
                writeRange.Value2 = data;
    
                //Activate Excel
                ((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();
                excel.Visible = true;
    
                //Cleanup
                tbl.Dispose();
                ada.Dispose();
                con.Close();
    
            }
    

    • Proposed as answer by dj_rakasz Monday, June 18, 2012 3:13 PM
    Monday, June 18, 2012 7:20 AM
  • You can google" Spire.DataExport for .NET" with the following code .It is easy and fast,i tried it already .

    C#


    private void btnLoad_Click(object sender, EventArgs e)

    {

        using(OleDbConnection oleDbConnection = new OleDbConnection())

        {
            oleDbConnection.ConnectionString = this.textBox1.Text;

            OleDbCommand oleDbCommand = new OleDbCommand();

            oleDbCommand.CommandText = this.textBox2.Text;

            oleDbCommand.Connection = oleDbConnection;

            using(OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))

            {
                DataTable dt = new DataTable();

                da.Fill(dt);

                dataGridView1.DataSource = dt;
            }
        }
    }

    C#

       private void btnRun_Click(object sender, EventArgs e)

    {

        Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();

        Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();

        worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;

        worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;

        worksheet1.StartDataCol = ((System.Byte)(0));

        cellExport.Sheets.Add(worksheet1);

        cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;

        cellExport.SaveToFile("20110223.xls");
    }

     
    Monday, August 12, 2013 9:05 AM