1、publicDataSetreturndb(string path)stringstrConn = Provider=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
2、$myCommand = newOleDbDataAdapter(strExcel, strConn); ds = newDataSet();myCommand.Fill(ds, table1);return ds; /另一种写法/FileInfofileInfo = new FileInfo(path);/if (!fileInfo.Exists) return null;/string strConn = Extended Properties=Excel 8.0;HDR=NO;IMEX=1/OleDbConnectionobjConn = new OleDbConnection(strC
3、onn);/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 + / OleDbDataAdapterodbcExcelDataAdapter = new O
4、leDbDataAdapter(strSql, objConn);/ odbcExcelDataAdapter.Fill(dsExcel);/ return dsExcel;/catch (Exception ex)/ throw ex; 1.方法一:采用OleDB读取EXCEL文件:把EXCEL文件当做一个数据源来进行数据的读取操作,实例如下:public DataSetExcelToDS(string Path) string strConn = + Path + OleDbConnection conn = new OleDbConnection(strConn); conn.Open(
5、); string strExcel = OleDbDataAdaptermyCommand = null; DataSet ds = null; strExcel= myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, return ds;对于EXCEL中的表即sheet(sheet1$)如果不是固定的可以使用下面的方法得到 DataTableschemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.O
6、leDbSchemaGuid.Tables,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
7、; OleDbConnectionmyConn = 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生成Inse
8、rtComment命令时使用。 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后newd
9、s内有值,但不能更新到Excel中因为所有导入行的DataRowState!=Added DataRownrow=aDataSet.Tables.NewRow(); for(int j=0;jnewds.Tables0.Columns.Count;j+) nrowj=oldds.Tables0.Rowsij; newds.Tables.Rows.Add(nrow); myCommand.Update(newds, myConn.Close();2.方法二:引用的com组件:Microsoft.Office.Interop.Excel.dll 读取EXCEL文件首先是Excel.dll的获取,将
10、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 (excel = null)
11、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); /取得第一个工作
12、薄 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_Range(K2K /Custom
13、erobject, 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.ToString();Response.Write(a
14、rry0, 0 + / + arry0, 1 + # + arryrowsint - 2, 0 + + arryrowsint - 2, 1);excel.Quit(); excel = null;Process procs = Process.GetProcessesByName(excelforeach (Process pro in procs) pro.Kill();/没有更好的方法,只有杀掉进程 GC.Collect();3.方法三:将EXCEL文件转化成CSV(逗号分隔)的文件,用文件流读取(等价就是读取一个txt文本文件)。先引用命名空间:using System.Text;和u
15、sing 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.ReadLine(); string xu = new String2; x
16、u = 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.txtOleDbConnection con = new OleDbConnection(conStr);con.Open(); string sql = select ITEM,REQD_DATE
17、,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.Fill(ds, DataTabledt = ds.Tables0;FileStream f
18、s = new FileStream(Server.MapPath(download/ + fn), FileMode.Create, FileAccess.ReadWrite);StreamWriterstrmWriter = new StreamWriter(fs); /存入到文本文件中/把标题写入.txt文件中/for (inti = 0;dt.Columns.Count;i+) / / strmWriter.Write(dt.Columnsi.ColumnName + /foreach (DataRowdr in dt.Rows) string str0, str1, str2, st
19、r3;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(str2);strmWriter.Write(str3); strmWriter.WriteLine(); /换行 strmWriter.Flush(); strmWriter.Close(); if (con.State = ConnectionState.Open) con.Close();
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1