Introduction

In our normal programs, the requirement of exporting data to excel is very usual. I’ve summarized some basic skills in an article in Codeproject. And right here, I want to specialize on some solutions and offer you a solution for large amount data exporting to excel.

Solution 1 - OLEDB

Use OLEDB can export data to excel conveniently. When you deal with this, just make the Excel an Access. Create tables with SQL, insert data information. Check 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);
    }
}

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

Solution 2 - Excel Com

Excel provides com component which also can generate Excel. It can generate any cell with content or format, which OLEDB is not available. But this solution requires user to install Excel related component and once file generated, some Excel process 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 COM component, user need add “Microsoft Office.Interop.Excel” reference. ExcelApp is given for some namespace alias. using ExcelApp = Microsoft.Office.Interop.Excel;

Solution 3 - Open xml

Use Open XML SDK which provided by Microsoft also can generate Excel. Rename Office 2007(Word, Excel, PPT) as .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 Data Export Component can export data to Excel and you don’t even install Microsoft Office.

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

• Export Database to Excel, PDF, HTML, RTF, XML, etc. for ASP.NET without Automation
• Export Data to Excel, Word, PDF without Automation from DataBase

Huge Amount Data Export to Excel Solution 

The solutions above are normal methods. When it happens on large amount data information, the first 2 solutions may not be useful, especially COM component, which needs generate one by one. But through QueryTable in COM, the effective can be improved 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 it’s much faster, it’s not stable. Sometimes it’s fast and sometimes it’s slow. And some excel process exist without solutions to switch off.

In SQL Server, which with OPENDATASOURCE（or OPENROWSET）can directly read data information in 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]

File should exist and there should some information in the first line.

Before use 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 this will cause the file can not be directly generated in client.

Use BCP to Export data can be very fast. But the output Excel file is not standard.

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;]'

Execute the code above in Access can consult data information in SQL Server. “in” also support other Database besides SQL Server.

At the beginning we mentioned that we can deal with Excel as Access. Just export OLEDB to Excel table, insert recorded SQL command text to replace the queries. (Need change the code to select into) Check 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 passed 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

Note:

• Only 65536 rows a sheet in Excel 2003
• 1048576 rows a sheet in Excel 2007