c读取excel文件的几种方法.docx

1、c读取excel文件的几种方法C# 读取EXCEL文件的几种经典方法例子引入（读取时excel要打开）namespace读取excel测试publicpartialclassForm1 : Form public Form1() InitializeComponent(); /读取privatevoid simpleButton1_Click(object sender, EventArgs e) bindingSource1.DataSource = null;OpenFileDialogfd = newOpenFileDialog();fd.Filter = 电子表格|*.xlsx|电子表

2、格|*.xls|所有文件|*.*;/打开对话框if (fd.ShowDialog() = DialogResult.OK) /得到文件路径名称this.txtPath.Text = fd.FileName;/将excel的对象放到bindingSource1中预览DataTabledt = returndb(this.txtPath.Text).Tables0; bindingSource1.DataSource = dt; gridView1.BestFitColumns(); /读取方法publicDataSetreturndb(string path) stringstrConn = P

3、rovider=Microsoft.Jet.OLEDB.4.0; + Data Source= + path + ; + Extended Properties=Excel 8.0;OleDbConnection conn = newOleDbConnection(strConn);conn.Open();stringstrExcel = ;OleDbDataAdaptermyCommand = null;DataSet ds = null;strExcel = select * from sheet1$;myCommand = newOleDbDataAdapter(strExcel, st

4、rConn); ds = newDataSet();myCommand.Fill(ds, table1);return ds; /另一种写法/FileInfofileInfo = new FileInfo(path);/if (!fileInfo.Exists) return null;/string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + path + ;Extended Properties=Excel 8.0;HDR=NO;IMEX=1;/OleDbConnectionobjConn = new OleDbCon

5、nection(strConn);/DataSetdsExcel = new DataSet();/try/ objConn.Open();/ DataTable table = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);/ string tableName = table.Rows0Table_Name.ToString();/ string strSql = select * from + tableName + ;/ OleDbDataAdapterodbcExcelDataAd

6、apter = new OleDbDataAdapter(strSql, objConn);/ odbcExcelDataAdapter.Fill(dsExcel);/ return dsExcel;/catch (Exception ex)/ throw ex;/ 1.方法一：采用OleDB读取EXCEL文件：把EXCEL文件当做一个数据源来进行数据的读取操作，实例如下：public DataSetExcelToDS(string Path) string strConn = Provider=Microsoft.Jet.OLEDB.4.0; +Data Source=+ Path +;+E

7、xtended Properties=Excel 8.0; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ; OleDbDataAdaptermyCommand = null; DataSet ds = null; strExcel=select * from sheet1$; myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds,table1);

8、 return ds; 对于EXCEL中的表即sheet(sheet1$)如果不是固定的可以使用下面的方法得到 string strConn = Provider=Microsoft.Jet.OLEDB.4.0; +Data Source=+ Path +;+Extended Properties=Excel 8.0; OleDbConnection conn = new OleDbConnection(strConn); DataTableschemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.T

9、ables,null); string tableName=schemaTable.Rows02.ToString().Trim(); 另外：也可进行写入EXCEL文件，实例如下：public void DSToExcel(string Path,DataSetoldds) /先得到汇总EXCEL的DataSet主要目的是获得EXCEL在DataSet中的结构 string strCon = Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =+path1+;Extended Properties=Excel 8.0 ; OleDbConnect

10、ionmyConn = new OleDbConnection(strCon) ; string strCom=select * from Sheet1$; myConn.Open( ) ; OleDbDataAdaptermyCommand = new OleDbDataAdapter( strCom, myConn ) ; ystem.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand); /QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使

11、用。 builder.QuotePrefix=; /获取insert语句中保留字符（起始位置） builder.QuoteSuffix=; /获取insert语句中保留字符（结束位置） DataSetnewds=new DataSet(); myCommand.Fill(newds ,Table1) ; for(inti=0;ioldds.Tables0.Rows.Count;i+) /在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后newds内有值，但不能更新到

12、Excel中因为所有导入行的DataRowState!=Added DataRownrow=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文件首先是Exce

13、l.dll的获取,将Office安装目录下的Excel.exe文件Copy到DotNet的bin目录下,cmd到该目录下,运行TlbImp EXCEL.EXE Excel.dll 得到Dll文件。再在项目中添加引用该dll文件./读取EXCEL的方法 (用范围区域读取数据)private void OpenExcel(string strFileName) object missing = System.Reflection.Missing.Value; Application excel = new Application();/lauch excel application if (exc

14、el = null) Response.Write(alert(Cant access excel); else excel.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, missi

15、ng); /取得第一个工作薄 Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1); /取得总记录行数 (包括标题列)introwsint = ws.UsedRange.Cells.Rows.Count; /得到行数 /intcolumnsint = mySheet.UsedRange.Cells.Columns.Count;/得到列数 /取得数据范围区域 (不包括标题列) Range rng1 = ws.Cells.get_Range(B2, B + rowsint); /item Range rng2 = ws.Cells.get_Rang

16、e(K2, K + rowsint); /Customerobject, arryItem= (object,)rng1.Value2; /get ranges valueobject, arryCus = (object,)rng2.Value2; /将新值赋给一个数组string, arry = new stringrowsint-1, 2; for (inti = 1; i= rowsint-1; i+) /Item_Code列arryi - 1, 0 =arryItemi, 1.ToString(); /Customer_Name列arryi - 1, 1 = arryCusi, 1.

17、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文本

18、文件)。先引用命名空间:using System.Text;和using System.IO; FileStream fs = new FileStream(d:Customer.csv, FileMode.Open, FileAccess.Read, FileShare.None); StreamReadersr = 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();另外也可以将数据库数据导入到一个txt文件，实例如下：/txt文件名 string fn = DateTime.Now.ToString(yyyyMMddHHmmss) + - + PO014 + .txt;OleDbConnection con = new OleDbConnection(conStr); co

20、n.Open(); string sql = select ITEM,REQD_DATE,QTY,PUR_FLG,PO_NUM from TSD_PO014; /OleDbCommandmycom = new OleDbCommand(select * from TSD_PO014, mycon);/OleDbDataReader myreader = mycom.ExecuteReader(); /也可以用Reader读取数据DataSet ds = new DataSet();OleDbDataAdapteroda = new OleDbDataAdapter(sql, con);oda.

21、Fill(ds, PO014);DataTabledt = ds.Tables0;FileStream fs = new FileStream(Server.MapPath(download/ + fn), FileMode.Create, FileAccess.ReadWrite);StreamWriterstrmWriter = new StreamWriter(fs); /存入到文本文件中/把标题写入.txt文件中/for (inti = 0; idt.Columns.Count;i+) / / strmWriter.Write(dt.Columnsi.ColumnName + ); /

22、foreach (DataRowdr 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();strmWriter.Write(str0);strmWriter.Write(str);strmWriter.Write(str1);strmWriter.Write(str);strmWriter.Write(str2);strmWriter.Write(str);strmWriter.Write(str3); strmWriter.WriteLine(); /换行 strmWriter.Flush(); strmWriter.Close(); if (con.State = ConnectionState.Open) con.Close();