C#操作EXCEL.pdf

《C#操作EXCEL.pdf》由会员分享，可在线阅读，更多相关《C#操作EXCEL.pdf（100页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、C#操作 EXCEL下面代码是几年前初做项目写的代码，没加整理，仅供基础参考呵呵using System;using System.10;using System.Reflection;using System.Collections;using Excel;using System.Data;using System.Data.OleDb;using System.Runtime.InteropServices;namespace Business.Common(操作Excel的接口public interface Excellnterface(把Excel中的数据导入具体的物理表中(单表)

2、bool InDataFromExcelColumns(OleDbConnection conn,string tableName,string|tableColsName,string excelFilePath,string excelColsName);bool DataInTableFromExcel(System.Data.DataTable objDataTable,string excelFilePath);bool DataInTableColsFromExcelCols(System.Data.DataTable objDataTable,stringtableColsNam

3、e,string excelFilePath,string excelColsName);bool DataUpdateTableColsFromExcelCols(OleDbConnection conn,string tableName,stringPID,string|tableColsName,System.Data.DataTable srcDataTable,string srcColsName);bool OutDataToExcel(System.Data.DataTable srcDataTable,string excelFilePath);System.Data.Data

4、Table GetDataFromExcel(string excelFilePath);)具体的一个实现类public class ExcelOperate:Excellnterface(导入数据2003.11.28(增添了可设置是否表的第一列是自增长的id 列)public bool InDataFromExcelColumns(OleDbConnection conn,string tableName,string tableColsName,string excelFilePath,string excelColsName,bool hasSysID)(if(hasSysID)(ret

5、urnthis.InDataFromExcelColumns(conn,tableName TableColsName,excelFilePath,excelColsName)；else把表的结构全部提取出来OleDbDataAdapter oleDA=new OleDbDataAdapter(select*from n+tableName,conn);DataSet ds=new DataSet();OleDbCommandBuilder oleCmdBlt=new OleDbCommandBuiIder(oleDA);if(conn.State=ConnectionState.Closed

6、)conn.Open();oleDA.Fill(ds);conn.Close();System.Data.DataTable objDataTable=ds.Tables01;if(!this.DataInTableColsFromExcelCols(objDataTable,tableColsName excelFilePath,excelColsName,false)return false;conn.Open();int addNum=oleDA.Update(ds);conn.Close();if(addNum!=0)return true;return false;)把Excel I

7、 的数据导入具体的物理表中(单表)public bool InDataFromExcelColumns(OleDbConnection conn,string tableName,string tableColsName,string excelFilePath,string excelColsName)(/try/把表的结构全部提取出来OleDbDataAdapter oleDA=new OleDbDataAdapter(select*from n+tableName,conn);DataSet ds=new DataSet();OleDbCommandBuilder oleCmdBlt=n

8、ew OleDbCommandBui!der(oleDA);if(conn.State=ConnectionState.Closed)conn.Open();oIeDA.Fill(ds);conn.Close();System.Data.DataTable objDataTable=ds.TablesO;if(!this.DataInTableColsFromExcelCols(objDataTable,tableColsName,excelFilePath,excelColsName)return false;conn.Open();int addNum=oleDA.Update(ds);c

9、onn.Close();if(addNum!=0)return true;return false;/)/catch/return false;/)由Excel中的数据完全导入到表中(前提是excel的表结构与要导入的表的结构是完全一样的)public bool DataInTableFromExcel(System.Data.DataTable objDataTable,string excelFilePath)(先得到excel文件对应的表System.Data.DataTable excelDataTable=this.GetDataFromExcel(excelFilePath);if

10、(excelDataTable=null II excelDataTable.Rows.Count=0II(excelDataTable.Rows.Count=l)&(excelDataTable.Columns.Count=l)&(excelDataTable.Rows00.ToString().Equals()return false;if(objDataTable=null)return false;if(excelDataTable.Columns.Count!=objDataTable.Columns.Count)return false;检测数据类型是否匹配fbr(int i=0;

11、i objDataTable.Columns.Count;i+)(if(!this.IsMatchDataType(objDataTable.ColumnsiJ.DataType.ToString(),excelDataTable.Columnsi.DataType.ToString()return false;)完全添加进去System.Data.DataRow myDataRow;for(int i=0;i excelDataTable.Rows.Count;i+)(myDataRow=objDataTable.NewRow();for(int j=0;j objDataTable.Col

12、umns.Count;j+)(myDataRowj=this.GetMatchData(objDataTable.Columns|j.DataType.ToString(),excelDataTable.Rowsij.ToString().Trim();objDataTable.Rows.Add(myDataRow);return true;)III/给指定的表的某列更新成Excel中某列的数据III III 对此表有效的连接v/paramIII 物理表的表名 /vparam name=PID此物理表的主键列名称(这里认为主键列只有,个)/此物理表中要更新的列集合/更新的数据源/对应 tabl

13、eColsName 中数据源中的列名集合/public bool DataUpdateTableColsFromExcelCols(OleDbConnection conn,stringtableName,string ID,string tableColsName,System.Data.DataTable srcDataTable,string srcColsName)(此时认为objDataTable srcDataTable的第一列的id 列是连接关系OleDbDataAdapter oleDA=new OleDbDataAdapter(select*from n+tableName,

14、conn);DataSet ds=new DataSet();OleDbCommandBuilder oleCmdBlt=new OIeDbCommandBuilder(oleDA);if(conn.State=ConnectionState.Closed)conn.Open();oleDA.Fill(ds);conn.Close();System.Data.DataTable objDataTable=ds.TablesO;设主键DataColumn keyCols=new DataColumnl;keyCols0=objDataTable.ColumnsID;objDataTable.Pr

15、imaryKey=keyCols;循环检测物理表的每一条记录是不是要更新for(int i=0;i objDataTable.Rows.Count;i+)(string idValue=objDataTable.RowsiID.ToString().Trim();for(int j=0;j srcDataTable.Rows.Count;j+)fstring tempID=srcDataTable.Rowsj0.ToString().Trim();if(tempID.Equals(id Value)objDataTable.Rowsi.BeginEdit();=for(int k=0;k ob

16、jDataTable.Columns.Count;k+)(int index=this.MatchStringIndex(objDataTable.Columnsk.ColumnName,tableColsName);if(index!=-1)(objDataTable.Rowsi tableColsNameindex=this.GetMatchData(objDataTable.Columnslk.DataType.ToString().Trim(),srcDataTable.Rows|jsrcColsNameindex.ToString().Trim();)/=一 =:=:=-:=一 二o

17、bjDataTable.Rowsi.EndEdit();break;)/forconn.Open();int affect=oleDA.Update(ds);conn.Close();if(affect=0)return false;return true;)/2003.1L28TO(增添了可设置是否表的第一列是自增长的id 歹 U)public bool DataInTableColsFromExcelCols(System.Data.DataTable objDataTable,stringtableColsName,string excelFilePath,string excelCol

18、sName,bool hasID)(此时认为objDataTable是都有自动增长的id 列if(tableColsName=null I I excelColsName=null II tableColsName.Length!=excelColsName.Length)return false;先得到excel文件对应的表System.Data.DataTable excelDataTable=this.GetDataFromExcel(excelFilePath);if(excelDataTable=null II excelDataTable.Rows.Count=0II(excelD

19、ataTable.Rows.Count=l)&(excelDataTable.Co!umns.Count=l)&(excelDataTable.Rows00.ToString().Equals(,H)return false;if(objDataTable=null)return false;把相应的数据添加到目标表中首先验证要添加的数据与源数据的类型是否匹配/for(int i=0;i excelColsName.Length;i+)/string srcDataType=excelDataTable.Columns excelColsNamei|.DataType.ToString().T

20、rim();/string objDataType=objDataTable.Columns tableColsNamei.DataType.ToString().Trim();/if(!this.IsMatchDataType(objDataType,srcDataType)/return false;/)/System.Data.DataRow myDataRow;添加(一次添加一行)/myDataRow=objDataTable.NewRow();int colNum=objDataTable.Columns.Count;/object col Values=new Object col

21、 Num ;int dataRow=excelDataTable.Rows.Count;for(int k=0;k dataRow;k+)(/int colNum=objDataTable.Columns.Count;object colValues=new Object!colNum;认为第一列是自动增长的id 列，不添加任何值，由系统自动生成/colValues0=Guid.NewGuid();int addindex=0;int j=0;if(hasID)j=1 ;for(;j colNum;j+)(if(this.NumberInSource(objDataTable.Columnsj

22、.ColumnName,tableColsName)!=1 )(colValueslj=this.GetMatchData(objDataTable.Columnsj.DataType.ToString(),；/myDataRowj J =this.GetMatchData(objDataTable.Columns|j.DataType.ToString(),；)else/是要添加数据的列时(找到与此列对应的tableColsName中的项索弓Iint index=MatchStringIndex(objDataTable.Columnsj.ColumnName,tableColsName);

23、colValues|j=this.GetMatchData(objDataTable.ColumnsjJ.DataType.ToString(),excelDataTable.RowskexcelColsNameindex.ToString().Trim();/myDataRowj=this.GetMatchData(objDataTable.Columnsj.DataType.ToString(),exceIDataTable.RowskexcelColsNameaddIndex+.ToString().Trim();objDataTable.Rows.Add(col Values);)re

24、turn true;)给指定的表的某列导入Excel中某列的数据public bool DataInTableColsFromExcelCols(System.Data.DataTable objDataTable,stringtableColsName,string excelFilePath,string excelColsName)(此时认为objDataTable是都有自动增长的id 列if(tableColsName=null I I excelColsName=null II tableColsName.Length!=excelColsName.Length)return fal

25、se;先得到excel文件对应的表System.Data.DataTable excelDataTable=this.GetDataFromExcel(excelFilePath);if(excelDataTable=null II excelDataTable.Rows.Count=0II(excelDataTable.Rows.Count=!)&(excelDataTable.CoIumns.Count=l)&(excelDataTable.RowsfO0.ToString().Equals(n)return false;if(objDataTable=null)return false;

26、把相应的数据添加到目标表中首先验证要添加的数据与源数据的类型是否匹配/for(int i=0;i excelColsName.Length;i+)/string srcDataType=excelDataTable.Columns excelColsNamei.DataType.ToString().Trim();/string objDataType=objDataTable.Columns!tableColsNamefi.DataType.ToString().Trim();/if(!this.IsMatchDataType(objDataType,srcDataType)/return

27、false;/)/System.Data.DataRow myDataRow;/添 加(一次添加一行)/myDataRow=objDataTable.NewRow();int colNum=objDataTable.Columns.Count;/object|colValues=new Object colNum;int dataRow=excelDataTable.Rows.Count;for(int k=0;k dataRow;k+)(/int colNum=objDataTable.Columns.Count;object colValues=new Object colNum|;认为第

28、一列是自动增长的id 歹 ij,不添加任何值，由系统自动生成/colValues0=Guid.NewGuid();int addindex=0;for(int j=1 ;j colNum;j+)(if(this.NumberInSource(objDataTable.Columnsj.ColumnName,tableColsName)!=1 )(colValuesj=this.GetMatchData(objDataTable.Columnsj.DataType.ToString(),*);/myDataRowfj|=this.GetMatchData(objDataTable.Columns

29、|j|.DataType.ToString(),;)else 是要添加数据的列时(找到与此列对应的tableColsName中的项索引int index=MatchStringIndex(objDataTable.Columns|j.CoIumnName,tableColsName);colValueslj=this.GetMatchData(objDataTable.ColumnsjJ.DataType.ToString(),excelDataTable.RowskexcelColsNameindex.ToString().Trim();/myDataRowfj=this.GetMatchD

30、ata(objDataTable.Columns|j.DataType.ToString(),excelDataTable.RowskexcelColsNameaddIndex+.ToString().Trim();)objDataTable.Rows.Add(colValues);)return true;把数据表的内容导出到Excel文件中:methodipublic bool OutDataToExcel(System.Data.DataTable srcDataTable,string excelFilePath)(if(srcDataTable=null)return false;E

31、xcel.Application myExcel=new Excel.Application();try(object oMissing=System.Reflection.Missing.Value;myExcel.Application.Workbooks.Open(excelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);Excel._Workbook xBk;Excel.-Worksheet xSt;

32、xBk=myExcel.Workbooks.Add(true);xSt=(ExcelWorksheet)xB k.Acti veSheet;int rowindex=1;int collndex=1 ;写标题fdreach(DataColumn col in srcDataTable.Columns)(xSt.Cells rowindex,collndex =col.ColumnName.Trim();+collndex;)rowindex=2;foreach(DataRow row in srcDataTable.Rows)(collndex=1 ;fdreach(DataColumn co

33、l in srcDataTable.Columns)xSt.Cellsf rowindex,collndex=row col.ColumnName .ToString().Trim();+collndex;+rowlndex;)MarshaLReleaseComObject(myExcel);return true;catch Marshal.ReleaseComObject(myExcel);return false;)从Excel中的workBookl中的全部的sheet选取全部的数据public System.Data.DataTable GetDataFromExcel(string

34、excelFilePath)(Excel.Application myExcel=new Excel.Application();try(/取得Excel文件中共有的sheet的数目object oMissing=System.Reflection.Missing.Value;myExcel.Application.Workbooks.Open(excelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);Exc

35、el.Workbook myBook=myExcel.Workbooksl;int sheetNum=myBook.Worksheets.Count;/=2003.11.28=string sheetName=new String sheetNum;int sheetindex=0;fbreach(Excel.Worksheet xlsheet in myBook.Worksheets)(sheetNamesheet!ndex+=xlsheet.Name;)/=#2(X)3.11.28=myExcel.Appiication.Workbooks.Close();string strConn=S

36、tring.Format(n Provider=Microsoft.Jet.OLEDB.4.0;Data Source=0;Extended Properties=Excel 8.0,excelFilePath);OleDbConnection conn=new OleDbConnection(strConn);string selSqlStr=,H;循环取完所有的sheet中的数据/System.Data.DataTable totalDataTable=new System.Data.DataTable();fbr(int i=1 ;i=sheetNum;i+)selSqlStr=sele

37、ct*from*+sheetNamei-l+;/selSqlStr=select*from Sheet3$n;/=rig h tOleDbDataAdapter oleDa=new OleDbDataAdapter(selSqlStr,conn);DataSet ds=new DataSet();conn.Open();oleDa.Fill(ds/dataTable*);conn.Close();if(ds.Tables.Count!=0)(totalDataTable=this.AddDataTable(totalDataTable,ds.TablesdataTable*);)Marshal

38、.ReleaseComObject(myExcel);return totalDataTable;)catch(Marshal.ReleaseComObject(myExcel);return null;)/III/sheet 的索引号，从 1 开始III public string GetColumnsNameList(string excelFilePath,int index)(/string retumList=new string dataTable.Columns.Count;Excel.Application myExcel=new Excel.Application();try

39、(取得Excel文件中共有的sheet的数目object oMissing=System.Reflection.Missing.Value;myExcel.Application.Workbooks.Open(excelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);Excel.Workbook myBook=myExcel.Workbooks 1 ;Excel.Sheets sheets=myBook.Wo

40、rksheets;Excel.Worksheet worksheet=(Excel.Worksheet)sheets.get_Item(index);Excel.Range range=worksheet.get_Range(HA 1UCZ1n);System.Array my values=(System.Array)range.Cells.Value;S yste m.Collecti ons.ArrayLi st array=this.ConvertToStringArray(my values);/int col Name=myBook.Worksheets.Count;/=2003.

41、11.28=/string sheetName=new String sheetNum;/int sheetindex=0;/foreach(Excel.Worksheet xlsheet in myBook.Worksheets)/sheetNamesheetlndex+1=xlsheet.Name;/)string colName=new string array.Count;/myExcel.get_Range(for(int i=0;i array.Count;i+)(colNamei=arrayi.ToString().Trim();)myExcel.Application.Work

42、books.Close();Marshal.ReleaseComObject(myExcel);return colName;)catch(Marshal.ReleaseComObject(myExcel);return null;)III/获取有指定的colList的页的名称/III/public string GetSheetNameList(string excelFilePath,string colList)(Excel.Application myExcel=new Excel.Application();try/取得Excel文件中共有的sheet的数目object oMissi

43、ng=System.Reflection.Missing.Value;myExcel.Application.Workbooks.Open(excelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);Excel.Workbook myBook=myExcel.Workbooksl;int sheetNum=myBook.Worksheets.Count;string col=null;ArrayList tot

44、alName=new ArrayList();fbr(int i=1 ;i=sheetNum;i+)col=this.GetColumnsNameList(excelFilePath,i);if(col=null II col.Length=0 II col.Length!=colList.Length)continue;fbr(int j=0;j col.Length;j+)(if(col|j.Trim()!=colListj.Trim()continue;)Excel.Worksheet xlsheet=(ExceLWorksheet)myBook.WorksheetsiJ;totalNa

45、me.Add(xlsheet.Name);)/=2003.11.28=string|sheetName=new String totalName.Count;for(int i=0;i totalName.Count;i+)(sheetNamei=totalNamefi.ToString().Trim();)myExcel.Application.Workbooks.Close();Marshal.ReleaseComObject(myExcel);return sheetN ame;)catch Marshal.ReleaseComObject(myExcel);return null;)/

46、III public bool DelSheetColumnData(string excel FilePath,System.Collections.ArrayList col Name)(Excel.Application myExcel=new Excel.Application();try(if(col Name=null&colName.Count=0)return true;取得Excel文件中共有的sheet的数目object oMissing=System.Reflection.Missing.Value;myExcel.Application.Workbooks.Open(e

47、xcelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);Excel.Workbook myBook=myExcel.Workbooks 1 ;Excel.Sheets sheets=myBook.Worksheets;Excel.Worksheet worksheet=(Excel.Worksheet)sheets.get_Item(1);Excel.Range range=worksheet.get_Ran

48、ge(nA r nZ ln);System.Array my values=(System.Array)range.Cells.Value;Excel.Range del Range;for(int i=1;i=myvalues.Length;i+)(if(ISDelCol(my values.Ge t Vai ue(l,i).ToString().Trim(),colName)!=-1)(delRange=worksheet.get_Range(i,i);delRange.Delete(i);)/if(values.Get Vai ue(1,i)!=null&values.GetValue(

49、l,i).ToString().Trim()!=H)/the Array.Add(values.Get Value(1,i).ToString().Trim();)myExcel.Application.Workbooks.Close();Marshal.ReleaseComObject(myExcel);return true;)catch(Marshal.ReleaseComObject(myExcel);return false;)/III III/public void SetColumnName(string excelFilePath,ArrayList oldColName,Ar

50、rayListnewColName)Excel.Application myExcel=new Excel.Application();try(if(oldColName=null II oldColName.Count=0)return;取得Excel文件中共有的sheet的数目object oMissing=System.Reflection.Missing.Value;myExcel.Application.Workbooks.Open(excelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing