C# 读取EXCEL文件的三种经典方法.doc

C# 读取EXCEL文件的三种经典方法 1.方法一：采用Old读取EXCEL文件：把EXCEL文件当做一个数据源来进行数据的读取操作，实例如下：?1234567891011121314public DataSet ExcelToDS(string Path) string strConn = Provider=Microsoft.Jet.OLEDB.4.0; +Data Source=+ Path +;+Extended Properties=Excel 8.0; OleDbConnection 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; 对于EXCEL中的表即sheet(sheet1$)如果不是固定的可以使用下面的方法得到?1234string 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=schemaTable.Rows02.ToString().Trim(); 另外：也可进行写入EXCEL文件，实例如下：?12345678910111213141516171819202122232425262728public void DSToExcel(string Path,DataSet oldds) /先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构 string strCon = Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =+path1+;Extended Properties=Excel 8.0 ; OleDbConnection myConn = new OleDbConnection(strCon) ; string strCom=select * from Sheet1$; myConn.Open ( ) ; OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ; ystem.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand); /QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。 builder.QuotePrefix=; /获取insert语句中保留字符（起始位置） builder.QuoteSuffix=; /获取insert语句中保留字符（结束位置） DataSet newds=new DataSet(); myCommand.Fill(newds ,Table1) ; for(int i=0;ioldds.Tables0.Rows.Count;i+) /在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added DataRow 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安装目录下的Excel.exe文件Copy到DotNet的bin目录下,cmd到该目录下,运行 TlbImp EXCEL.EXE Excel.dll 得到Dll文件。 再在项目中添加引用该dll文件.?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152/读取EXCEL的方法 (用范围区域读取数据)private void OpenExcel(string strFileName)object missing = System.Reflection.Missing.Value;Application excel = new Application();/lauch excel applicationif (excel = 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);/取得第一个工作薄Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);/取得总记录行数 (包括标题列)int rowsint = ws.UsedRange.Cells.Rows.Count; /得到行数/int columnsint = mySheet.UsedRange.Cells.Columns.Count;/得到列数/取得数据范围区域 (不包括标题列) Range rng1 = ws.Cells.get_Range(B2, B + rowsint); /itemRange rng2 = ws.Cells.get_Range(K2, K + rowsint); /Customerobject, arryItem= (object,)rng1.Value2; /get ranges valueobject, arryCus = (object,)rng2.Value2; /将新值赋给一个数组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();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();/没有更好的方法,只有杀掉进程GC.Collect();3.方法三：将EXCEL文件转化成CSV（逗号分隔）的文件，用文件流读取(等价就是读取一个txt文本文件)。?12345678910111213141516先引用命名空间: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.ReadLine();string xu = new String2;xu = str.Split(,);string ser = xu0; string dse = xu1; if (ser = s) Console.WriteLine(dse);break; sr.Close();另外也可以将数据库数据导入到一个txt文件，实例如下：?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849/txt文件名string fn = DateTime.Now.ToString(yyyyMMddHHmmss) + - + PO014 + .txt;OleDbConnection con = new OleDbConnection(conStr); con.Open();string sql = select ITEM,REQD_DATE,QTY,PUR_FLG,PO_NUM from TSD_PO014; /OleDbCommand mycom = new OleDbCommand(select * from TSD_PO014, mycon);/OleDbDataReader myreader = mycom.ExecuteReader(); /也可以用Reader读取数据DataSet ds = new DataSet();OleDbDataAdapter oda = new OleDbDataAdapter(sql, con);oda.Fill(ds, PO014);DataTable dt = ds.Tables0;FileStream fs = new FileStream(Server.MapPath(download/ + fn), FileMode.Create, FileAccess.ReadWrite);StreamWriter strmWriter = new StreamWriter(fs); /存入到文本文件中 /把标题写入.txt文件中 /for (int i = 0; i dt.Columns.Count;i+)/ strmWriter.Write(dt.Columnsi.ColumnName + );/foreach (DataRow dr in dt.Rows)string str0, str1, str2, str3;string str = |; /数据用|分隔开str0 = dr0.ToString();str1 = dr1.ToString();str2 = dr2.ToString();str3 = dr3.ToString();str4 = dr4.ToString().Trim();strmWri