Exporting Data to Excel

Introduction

Exporting data to Excel is a very common requirement in programs. In this article, I want to explain some solutions and offer you a solution for large amount data exporting to Excel which may be useful for you.

Solution 1 - OLEDB

Using OLEDB, we can export data to Excel conveniently. When you deal with this, just make the Excel an Access. Create tables with SQL and insert data information. See the code below:

public static void Export(DataTable dt, string filepath, string tablename)
{
    //excel 2003
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
           filepath + ";Extended Properties=Excel 8.0;";
    //Excel 2007
    //string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
    //       filepath + ";Extended Properties=Excel 12.0 Xml;";
    try
    {
        using (OleDbConnection con = new OleDbConnection(connString))
        {
            con.Open();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]");
            strSQL.Append("(");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
            }
            strSQL = strSQL.Remove(strSQL.Length - 1, 1);
            strSQL.Append(")");

            OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
            cmd.ExecuteNonQuery();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strSQL.Clear();
                StringBuilder strfield = new StringBuilder();
                StringBuilder strvalue = new StringBuilder();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    strfield.Append("[" + dt.Columns[j].ColumnName + "]");
                    strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
                    if (j != dt.Columns.Count - 1)
                    {
                        strfield.Append(",");
                        strvalue.Append(",");
                    }
                    else
                    {
                    }
                }
                cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ")
                    .Append(strfield.ToString())
                    .Append(") values (").Append(strvalue).Append(")").ToString();
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
        Console.WriteLine("OK");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Use connString to control and generate Excel 2003 or Excel 2007. At the same time, the file name should match (xls or xlsx).

Solution 2 - Excel COM

Excel provides a COM component which can generate an Excel file. It can generate cells with content and format, which OLEDB does not support. But this solution requires the user to install Excel related components, and once the file is generated, the Excel process will still exist.

public static void Export(DataTable dt, string filepath)
{
    ExcelApp.Application myExcel = new ExcelApp.Application();
    //Create a New file
    ExcelApp._Workbook mybook = myExcel.Workbooks.Add();
    //Open the exist file
    //ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
    //          Type.Missing, Type.Missing, Type.Missing,
    //    Type.Missing,Type.Missing, Type.Missing, Type.Missing,
    //    Type.Missing, Type.Missing, Type.Missing,
    //    Type.Missing, Type.Missing,Type.Missing, Type.Missing);
    //ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
    myExcel.Visible = true;
    try
    {
        mybook.Activate();
        ExcelApp._Worksheet mysheet = mybook.Worksheets.Add();      
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ExcelApp.Range cell = 
                  mysheet.get_Range(((char)(65 + j)).ToString() + (i + 1).ToString());
                cell.Select();
                cell.Cells.FormulaR1C1 = dt.Rows[i][j] ?? "";
            }
        }
        //mybook.SaveAs(Filename: filepath);
        mybook.Save();
    }
    catch (Exception ex)
    {
    }
    finally
    {
        mybook.Close();
        myExcel.Quit();
        GC.Collect();
    }
}

While using the COM component, the user needs to add the "Microsoft Office.Interop.Excel" reference. ExcelApp is given as a namespace alias.

using ExcelApp = Microsoft.Office.Interop.Excel;

Solution 3 - Open XML

The Open XML SDK provided by Microsoft can be used to generate Excel. Rename Office 2007 (Word, Excel, PPT) as a .zip and release the zip file; you will find the released files are XML files.

Note: Microsoft Office version should be 2007 or above.

Solution 4 - Data Export Component

Use the Data Export Component to export data to Excel and you don't even need to install Microsoft Office.

Recommend a Free Data Export Component which also recommended in these articles:

Huge Amount Data Export to Excel Solution

The solutions above are normal methods. For large amounts of data, the first two solutions may not be useful, especially the COM component, which needs to generate rows one by one. But using QueryTable in COM can improve the solution a lot.

public static void Export( string filepath,string strSQL)
{
    ExcelApp.Application myExcel = new ExcelApp.Application();
    ExcelApp._Workbook mybook = myExcel.Workbooks.Add();            
    try
    {
        mybook.Activate();
        ExcelApp._Worksheet mysheet = mybook.Worksheets.Add();
        string conn = 
          "ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;";
        ExcelApp.QueryTable querytable = 
          mysheet.QueryTables.Add(conn, mysheet.get_Range("A1"), strSQL);
        querytable.Refresh(false);
        mybook.SaveAs(Filename : filepath,AccessMode:ExcelApp.XlSaveAsAccessMode.xlNoChange);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        mybook.Close();
        myExcel.Quit();
    }
}

Although this is much faster, it's not stable. Sometimes it's fast and sometimes it's slow. And an Excel process exists after we close the app, and there is no easy way to switch it off.

In SQL Server, OPENDATASOURCE or OPENROWSET can directly read data from Excel.

SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\book.xls;Extended Properties=EXCEL 5.0')...[sheet1$] ;

-- or

insert into OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\book.xls;Extended Properties=EXCEL 5.0')...[sheet1$]
select Customer .name ,Product .fullname
from [v_Order]

A file is created and there should be some information in the first line.

Before using OPENDATASOURCE, execute this:

exec sp_configure 'Ad Hoc Distributed Queries','1'
RECONFIGURE

Otherwise there will be an error: "SQL Server stopped the visit of STATEMENT 'OpenRowset/OpenDatasource' in 'Ad Hoc Distributed Queries'.

But because of this, the file can not be directly generated in the client. Use BCP to export data can be very fast. But the output Excel file is not standard format.

SQL Server can do this Job. How about Access? It also works if we use "in":

select * from product
in '[ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;]'

Executing the code above in Access can get the data from SQL Server. "in" also supports other databases besides SQL Server.

At the beginning I mentioned that we can deal with Excel as Access. Just export OLEDB to an Excel table and insert the recorded SQL command text to replace the queries (need to change the code to select into). Here is the code:

public static void Export(string strSQL, string filepath)
{
    //string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
    //       filepath + ";Extended Properties=Excel 8.0;";
    string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
           filepath + ";Extended Properties=Excel 12.0 Xml;";
    try
    {
        using (OleDbConnection conn = new OleDbConnection(connString))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(strSQL, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        Console.WriteLine("OK");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Use the SQL:

select  * into product
from product
in "" [ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;]
If you need add where, orderby, place them behind SQL 
select  [fullname] as Name,[alias] as Abbreviation,[price] as Price
into Product
from product
in "" [ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;]
where id_product  >1
order by fullname