国产精品三级,国产a在线不卡,国产福利在线视频

欧美日韩亚-欧美日韩亚州在线-欧美日韩亚洲-欧美日韩亚洲第一区-欧美日韩亚洲二区在线-欧美日韩亚洲高清精品

大批量數(shù)據(jù)導(dǎo)出到Excel的實(shí)現(xiàn)

轉(zhuǎn)帖|其它|編輯：郝浩|2011-05-30 14:27:03.000|閱讀 1822 次

概述：在平時的項(xiàng)目中，將數(shù)據(jù)導(dǎo)出到Excel的需求是很常見的，在此對一些常見的方法做以總結(jié)，并提供一種大數(shù)據(jù)量導(dǎo)出的實(shí)現(xiàn)。

# 界面/圖表報表/文檔/IDE等千款熱門軟控件火熱銷售中 >>

　　在平時的項(xiàng)目中，將數(shù)據(jù)導(dǎo)出到Excel的需求是很常見的，在此對一些常見的方法做以總結(jié)，并提供一種大數(shù)據(jù)量導(dǎo)出的實(shí)現(xiàn)。

OLEDB

　　使用OLEDB可以很方便導(dǎo)出Excel，思路很簡單，處理時將Excel當(dāng)做Access處理，利用SQL建表、插入數(shù)據(jù)。不多說了，直接看代碼　　

使用OLEDB導(dǎo)出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);
     }
}

　　生成的Excel是2003還是2007，就是通過連接字符串來控制的（代碼中的connString ），同時傳遞的文件名也要一致（xls or xlsx），不然會出現(xiàn)運(yùn)行不成功或者生成的文件打不開的情況。

Excel Com

　　Excel本身提供com組件來實(shí)現(xiàn)對Excel的操作，它的優(yōu)點(diǎn)是顯而易見的，可以具體控制到操作excel中的任意一個單元格（內(nèi)容+格式），利用oledb是做不到這一點(diǎn)的。當(dāng)項(xiàng)目中需要使用現(xiàn)有模板生成Excel的時候使用該方法是很方便的。但該方法性能上慢，而且需要安裝Excel相關(guān)組件，生成文件后內(nèi)存中有時仍舊有Excel進(jìn)程。如果是web的話不建議使用該方法，否則管理員和服務(wù)器都會瘋掉的。

com生成Excel

public static void Export(DataTable dt, string filepath)
{
     ExcelApp.Application myExcel = new ExcelApp.Application();
     //新建文件
     ExcelApp._Workbook mybook = myExcel.Workbooks.Add();
     //打開現(xiàn)有文件
     //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中的寫法，使用“命名參數(shù)”和“可選參數(shù)”
     //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組件時，需要先在項(xiàng)目中添加“Microsoft.Office.Interop.Excel”引用。（代碼中的ExcelApp是我給相關(guān)命名空間提供的別名，using ExcelApp = Microsoft.Office.Interop.Excel;）

Open xml

　　使用微軟提供的Open XML SDK也可以很方便的生成excel。（將office2007（Word、Excel、PPT）的文件后綴名修改為”.zip”,將得到的壓縮包解壓，發(fā)現(xiàn)里面就是xml文件。）,具體代碼我就不貼了，在使用中發(fā)現(xiàn)初始加載的時候也是比較慢的。

　　需要注意的是，該方法只能操作office 2007以上的版本，不支持0ffice 2003.

NPOI

　　這個是朋友介紹的，一個開源的組件，使用時不需要安裝office（極大的優(yōu)勢），也能具體到對一個單元格的控制。NPOI是POI的.NET版本，POI是一套用Java寫成的庫，能夠幫助開發(fā)者在沒有安裝微軟Office的情況下讀寫Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。官網(wǎng)（已遷移到googlecode）上提供了許多實(shí)際例子，而且atao也給了介紹和入門教程，代碼我就不復(fù)制了。

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

大批量數(shù)據(jù)

　　上面幾種方法是比較常見的，但是當(dāng)遇到大批量數(shù)據(jù)時，前兩種就太不給力了，特別是使用com組件那種，它們都是一條一條的生成。不過，com中可以使用QueryTable來提高效率。

QueryTable批量導(dǎo)出數(shù)據(jù)

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

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

　　既然這樣，換個思路換個法子，既然一條一條的插入比較慢，那么批量插入呢，SQL語句中就有“select into”，能不能利用這個實(shí)現(xiàn)呢？查找資料后得知是可以的，在SQL Server中有OPENDATASOURCE（還有OPENROWSET）的，可以直接讀取excel中的數(shù)據(jù)。

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]

　　寫入的話得要求該文件存在，并且第一行有數(shù)據(jù)（表頭行）。

　　在使用OPENDATASOURCE前，需要先執(zhí)行

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

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

　　但是，使用OPENDATASOURCE是在SQL Server進(jìn)程中執(zhí)行的，這也就導(dǎo)致生成的文件時在SQL Server服務(wù)器端的，無法在客戶端直接生成。

　　也可以利用 BCP 來導(dǎo)出，速度上非常快，但是導(dǎo)出的Excel并不是標(biāo)準(zhǔn)格式的，如果客戶不需要標(biāo)準(zhǔn)的格式，可以用這個來實(shí)現(xiàn)。

　　既然SQL Server中可以這樣做，在Access中是不是也可以呢？按照這個思路查找?guī)椭l(fā)現(xiàn)也是可以的，就是用in

select * from product
in "" [ODBC;Driver=SQL Server;Server=.;

uid=sa;pwd=sa;database=sample;]

　　在Access中運(yùn)行上面的代碼后，就可以查詢中SQL Server數(shù)據(jù)庫中的數(shù)據(jù)，不過不支持連接查詢（用視圖唄），而且要注意保留字的處理（字段添加“[]”）。當(dāng)然，使用in不僅僅支持SQL Server，對其他數(shù)據(jù)庫也是支持的，可以看MSDN ，或者ACMAIN_CHM的這篇文章。

　　剛開始的時候已經(jīng)說過，在用Oledb處理Excel時可以把excel當(dāng)做access，那么只需要將OLEDB導(dǎo)出Excel中的創(chuàng)建表、插入記錄的SQL語句替換上面的查詢語句就可以了（得將代碼改為select into 才行，不然是生不成excel文件的），看看代碼

oledb執(zhí)行SQL

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

　　大批量數(shù)據(jù)導(dǎo)出的時候，需要注意這樣的一個問題，Excel2003格式一個sheet只支持65536行，excel 2007 就比較多，是1048576

標(biāo)簽：

本站文章除注明轉(zhuǎn)載外，均為本站原創(chuàng)或翻譯。歡迎任何形式的轉(zhuǎn)載，但請務(wù)必注明出處、不得修改原文相關(guān)鏈接，如果存在內(nèi)容上的異議請郵件反饋至chenjj@fc6vip.cn

文章轉(zhuǎn)載自：博客園

上一篇：幾行代碼搞定Flash應(yīng)用的多語言實(shí)時切換問題下一篇：如何動態(tài)加載 JavaScript 與 CSS