大資料匯入Excel

來源:互聯網
上載者:User

標籤:

在平時的項目中,將資料匯出到Excel的需求是很常見的,在此對一些常見的方法做以總結,並提供一種大資料量匯出的實現。

  • OLEDB

    使用OLEDB可以很方便匯出Excel,思路很簡單,處理時將Excel當做Access處理,利用SQL建表、插入資料。不多說了,直接看代碼   

使用OLEDB匯出Excel

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);

}

}

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);
    }
}

   產生的Excel是2003還是2007,就是通過連接字串來控制的(代碼中的connString ),同時傳遞的檔案名稱也要一致(xls or xlsx),不然會出現運行不成功或者產生的檔案打不開的情況。

  • Excel Com

  Excel本身提供com組件來實現對Excel的操作,它的優點是顯而易見的,可以具體控制到操作excel中的任意一個儲存格(內容+格式),利用oledb是做不到這一點的。當項目中需要使用現有模板產生Excel的時候使用該方法是很方便的。但該方法效能上慢,而且需要安裝Excel相關組件,組建檔案後記憶體中有時仍舊有Excel進程。如果是web的話不建議使用該方法,否則管理員和伺服器都會瘋掉的。 

com產生Excel

public static void Export(DataTable dt, string filepath)

{

ExcelApp.Application myExcel = new ExcelApp.Application();

//建立檔案

ExcelApp._Workbook mybook = myExcel.Workbooks.Add();

//開啟現有檔案

//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);

//開啟檔案在.net4.0中的寫法,使用“具名引數”和“選擇性參數”

//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();

}

}

public static void Export(DataTable dt, string filepath)
{
    ExcelApp.Application myExcel = new ExcelApp.Application();
    //建立檔案
    ExcelApp._Workbook mybook = myExcel.Workbooks.Add();
    //開啟現有檔案
    //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);
    //開啟檔案在.net4.0中的寫法,使用“具名引數”和“選擇性參數”
    //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();
    }
}

  在使用com組件時,需要先在項目中添加“Microsoft.Office.Interop.Excel”引用。(代碼中的ExcelApp是我給相關命名空間提供的別名,using ExcelApp = Microsoft.Office.Interop.Excel;)

  • Open xml

  使用微軟提供的Open XML SDK也可以很方便的產生excel。(將office2007(Word、Excel、PPT) 的檔案尾碼名修改為”.zip”,將得到的壓縮包解壓,發現裡面就是xml檔案。),具體代碼我就不貼了,在使用中發現初始載入的時候也是比較慢的。

  需要注意的是,該方法只能操作office 2007以上的版本,不支援0ffice 2003.http://www.libxl.com/

  • NPOI

  這個是朋友介紹的,一個開源的組件,使用時不需要安裝office(極大的優勢),也能具體到對一個儲存格的控制。NPOI是POI的.NET版本,POI是一套用Java寫成的庫,能夠協助開發人員在沒有安裝微軟Office的情況下讀寫Office 97-2003的檔案,支援的檔案格式包括xls, doc, ppt等。官網(已遷移到googlecode)上提供了許多實際例子,而且atao也給了介紹和入門教程,代碼我就不複製了。

  需要注意的是,目前該方法只能操作office2003。

  • 大批量資料

  上面幾種方法是比較常見的,但是當遇到大批量資料時,前兩種就太不給力了,特別是使用com組件那種,它們都是一條一條的產生。不過,com中可以使用QueryTable來提高效率。

QueryTable大量匯出資料

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();

}

}

  該方法比一條一條的插入不知道要快上多少倍,但是在我這測試時發現不穩定,同樣的代碼第一天還很快,到第二天就很慢了(相差特別大),同時,也存在excel進程關不掉的問題。

既然這樣,換個思路換個法子,既然一條一條的插入比較慢,那麼批量插入呢,SQL語句中就有“select into”,能不能利用這個實現呢?尋找資料後得知是可以的,在SQL Server中有OPENDATASOURCE(還有OPENROWSET)的 ,可以直接讀取excel中 的資料。

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

  也可以寫入,

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]

寫入的話得要求該檔案存在,並且第一行有資料(表頭行)。

  在使用OPENDATASOURCE前,需要先執行

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

  不然的話會報錯:“SQL Server 阻止了對組件 ‘Ad Hoc Distributed Queries‘ 的 STATEMENT ‘OpenRowset/OpenDatasource‘ 的訪問”

  但是,使用OPENDATASOURCE是在SQL Server進程中執行的,這也就導致產生的檔案時在SQL Server伺服器端的,無法在用戶端直接產生。

  也可以利用 BCP 來匯出,速度上非常快,但是匯出的Excel並不是標準格式的,如果客戶不需要標準的格式,可以用這個來實現。

  既然SQL Server中可以這樣做,在Access中是不是也可以呢?按照這個思路尋找協助,發現也是可以的,就是用in

select  * from productin "" [ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;]

  在Access中運行上面的代碼後,就可以查詢中SQL Server資料庫中的資料,不過不支援串連查詢(用視圖唄),而且要注意保留字的處理(欄位添加“[]”)。 當然,使用in不僅僅支援SQL Server,對其他資料庫也是支援的,可以看MSDN ,或者ACMAIN_CHM的這篇文章。

  剛開始的時候已經說過,在用Oledb處理Excel時可以把excel當做access,那麼只需要將OLEDB匯出Excel中的建立表、插入記錄的SQL語句替換上面的查詢語句就可以了(得將代碼改為select into 才行,不然是生不成excel檔案的),看看代碼

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);    }}

 

使用上面的方法時,傳遞的SQL

select  * into 商品
from product
in "" [ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;]

如果需要添加where、orderby的話就放在上面SQL的後面(into後的表名在Excel中就是sheet的名稱)

select  [fullname] as 名稱,[alias] as 簡稱,[price] as 單價
into 商品
from product
in "" [ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample;]
where id_product  >1
order by fullname

 

    大批量資料匯出的時候,需要注意這樣的一個問題,Excel2003格式一個sheet只支援65536行,excel 2007 就比較多,是1048576。 

大資料匯入Excel

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.