c#讀取excel文件的幾種方法

1、C# 讀取 EXCEL文件的幾種經(jīng)典方法例子引入（讀取時(shí)excel 要打開）namespace讀取 excel測(cè)試publicpartialclassForm1 :FormpublicForm1()InitializeComponent();/ 讀取privatevoid simpleButton1_Click(objectsender,EventArgse)bindingSource1.DataSource =null ;OpenFileDialogfd =newOpenFileDialog();fd.Filter = 電子表 格|*.xlsx|電子表格|*.xls|所有文件 |*.*;/

2、打開對(duì)話框if(fd.ShowDialog()= DialogResult.OK)/ 得到文件路徑名稱this.txtPath.Text = fd.FileName;/將excel的對(duì)象放到 bindingSource1中預(yù)覽DataTabledt =returndb(this.txtPath.Text).Tables0;bindingSource1.DataSource = dt;gridView1.BestFitColumns();/讀取方法publicDataSetreturndb(stringpath).stringstrConn =Provider=Microsoft.Jet.OLE

3、DB.4.0;+DataSource= + path + ; + Extended Properties=Excel 8.0; ;OleDbConnectionconn =newOleDbConnection(strConn);conn.Open();stringstrExcel = ;OleDbDataAdaptermyCommand =null;DataSetds =null;strExcel =select * from sheet1$;myCommand =newOleDbDataAdapter(strExcel, strConn);ds =newDataSet();myCommand

4、.Fill(ds,table1);returnds;/ 另一種寫法/FileInfo fileInfo = newFileInfo(path);/if (!fileInfo.Exists) return null;/string strConn =Provider=Microsoft.Jet.OLEDB.4.0;Data Source=+ path + ;Extended Properties=Excel8.0;HDR=NO;IMEX=1;/OleDbConnection objConn = new OleDbConnection(strConn);/DataSet dsExcel = new

5、 DataSet(); /try/ objConn.Open();/ DataTable table = objConn.GetOleDbSchemaTable(System.Data.OleDb.O leDbSchemaGuid.Tables, null);/ string tableName =.table.Rows0Table_Name.ToString();/ string strSql = select * from + tableName + ;/ OleDbDataAdapterodbcExcelDataAdapter = new OleDbDataAdapter(strSql,

6、 objConn);/odbcExcelDataAdapter.Fill(dsExcel);/ return dsExcel;/catch (Exception ex)/ throw ex;/1. 方法一：采用 OleDB讀取 EXCEL文件：把 EXCEL文件當(dāng)做一個(gè)數(shù)據(jù)源來進(jìn)行數(shù)據(jù)的讀取操作，實(shí)例如下： public DataSet ExcelToDS(string Path)string strConn = Provider=Microsoft.Jet.OLEDB.4.0; +Data Source=+ Path +;+Extended Properties=Excel 8.0;OleD

7、bConnection conn = new OleDbConnection(strConn); conn.Open();string strExcel = ;OleDbDataAdapter myCommand = null; DataSet ds = null;strExcel=select * from sheet1$;myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet();myCommand.Fill(ds,table1); return ds;對(duì)于 EXCEL中的表即 sheet(sheet1$)

8、如果不是固定的可以使用下面的.方法得到string strConn = Provider=Microsoft.Jet.OLEDB.4.0; +Data Source=+ Path +;+Extended Properties=Excel 8.0;OleDbConnection conn = new OleDbConnection(strConn); DataTable schemaTable =objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);string tableName=schemaTab

9、le.Rows02.ToString().Trim();另外：也可進(jìn)行寫入 EXCEL文件，實(shí)例如下：public void DSToExcel(string Path,DataSet oldds)/ 先得到匯總 EXCEL的DataSet 主要目的是獲得 EXCEL在DataSet 中的結(jié)構(gòu)string strCon = Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =+path1+;Extended Properties=Excel 8.0 ;OleDbConnection myConn = new OleDbConnection(str

10、Con) ; string strCom=select * from Sheet1$;myConn.Open ( ) ;OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom,myConn ); ystem.Data.OleDb.OleDbCommandBuilder builder=newOleDbCommandBuilder(myCommand);/QuotePrefix 和QuoteSuffix 主要是對(duì) builder 生成 InsertComment 命令時(shí)使用。builder.QuotePrefix=;/獲取 inser

11、t語(yǔ)句中保留字符（起始位置）builder.QuoteSuffix=;/ 獲取 insert語(yǔ)句中保留字符（結(jié)束位置）DataSet newds=new DataSet();myCommand.Fill(newds ,Table1) ;for(int i=0;ioldds.Tables0.Rows.Count;i+)/ 在這里不能使用 ImportRow方法將一行導(dǎo)入到 news中，因?yàn)镮mportRow將保留原來 DataRow的所有設(shè)置 (DataRowState 狀態(tài)不變 ) 。在使用 ImportRow后newds內(nèi)有值，但不能更新到 Excel 中因?yàn)樗袑?dǎo).入行的 DataRowS

12、tate!=AddedDataRow nrow=aDataSet.TablesTable1.NewRow(); for(int j=0;jnewds.Tables0.Columns.Count;j+)nrowj=oldds.Tables0.Rowsij;newds.TablesTable1.Rows.Add(nrow);myCommand.Update(newds,Table1);myConn.Close();2. 方法二：引用的 com組件： Microsoft.Office.Interop.Excel.dll 讀取 EXCEL文件首先是 Excel.dll 的獲取 , 將Office 安裝

13、目錄下的 Excel.exe 文件 Copy到DotNet 的bin 目錄下 ,cmd到該目錄下 , 運(yùn)行TlbImp EXCEL.EXE Excel.dll 得到 Dll 文件。 再在項(xiàng)目中添加引用該 dll 文件 ./ 讀取 EXCEL的方法 ( 用范圍區(qū)域讀取數(shù)據(jù) ) private void OpenExcel(string strFileName)object missing = System.Reflection.Missing.Value; Application excel = new Application();/lauch excelapplicationif (excel

14、 = null)Response.Write(alert(Cant access excel);elseexcel.Visible = false; excel.UserControl = true;/以只讀的形式打開 EXCEL文件Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,missing, missing, missing, true, missing, missing,.missing, missing, missing);/ 取得

15、第一個(gè)工作薄Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);/ 取得總記錄行數(shù) ( 包括標(biāo)題列 )int rowsint = ws.UsedRange.Cells.Rows.Count; /得到行數(shù)/int columnsint =mySheet.UsedRange.Cells.Columns.Count;/得到列數(shù)/ 取得數(shù)據(jù)范圍區(qū)域 ( 不包括標(biāo)題列 )Range rng1 = ws.Cells.get_Range(B2, B + rowsint); /itemRange rng2 = ws.Cells.get_Range(K2, K

16、 + rowsint); /Customerobject, arryItem= (object,)rng1.Value2; /get ranges valueobject, arryCus = (object,)rng2.Value2;/ 將新值賦給一個(gè)數(shù)組string, arry = new stringrowsint-1, 2;for (int i = 1; i = rowsint-1; i+)/Item_Code列arryi - 1, 0 =arryItemi, 1.ToString();/Customer_Name列arryi - 1, 1 = arryCusi, 1.ToString

17、();Response.Write(arry0, 0 + / + arry0, 1 + # + arryrowsint - 2, 0 + / + arryrowsint - 2, 1);excel.Quit(); excel = null;Process procs = Process.GetProcessesByName(excel); foreach (Process pro in procs)pro.Kill();/沒有更好的方法 , 只有殺掉進(jìn)程GC.Collect();.3. 方法三：將 EXCEL文件轉(zhuǎn)化成 CSV（逗號(hào)分隔）的文件，用文件流讀取 ( 等價(jià)就是讀取一個(gè) txt 文本

18、文件 ) 。先引用命名空間 :using System.Text; 和using System.IO; FileStream fs = new FileStream(d:Customer.csv,FileMode.Open, FileAccess.Read, FileShare.None); StreamReader sr = new StreamReader(fs,System.Text.Encoding.GetEncoding(936);string str = ;string s = Console.ReadLine();while (str != null) str = sr.Read

19、Line();string xu = new String2;xu = str.Split(,);string ser = xu0;string dse = xu1;if (ser = s)Console.WriteLine(dse);break;sr.Close();另外也可以將數(shù)據(jù)庫(kù)數(shù)據(jù)導(dǎo)入到一個(gè)txt 文件，實(shí)例如下：/txt文件名string fn = DateTime.Now.ToString(yyyyMMddHHmmss) + - + PO014 + .txt;OleDbConnection con = new OleDbConnection(conStr); con.Open()

20、;string sql = select ITEM,REQD_DATE,QTY,PUR_FLG,PO_NUM fromTSD_PO014;/OleDbCommand mycom = new OleDbCommand(select * fromTSD_PO014, mycon);/OleDbDataReader myreader = mycom.ExecuteReader(); /也可以用Reader讀取數(shù)據(jù).DataSet ds = new DataSet();OleDbDataAdapter oda = new OleDbDataAdapter(sql, con); oda.Fill(ds, PO014);DataTable dt = ds.Tables0;FileStreamfs = new FileStream(Server.MapPath(download/+