《C操作Excel类以及其使用举例说明.docx》由会员分享，可在线阅读，更多相关《C操作Excel类以及其使用举例说明.docx（23页珍藏版）》请在课桌文档上搜索。
1、C#操作Excel的类以及其运用举例说明1、曲育这两天Ih于某种须要，探讨了吓.NET中对EXCel的互操作.之前主要是卜脆通过第三方的组件等方式来操作的Excel,这次细致的探讨了一下，对常用的EXCel操作需求做了一个简洁的总结，比如创建EXceI,设置单元格样式，合并单元格.导入内存中的DataTable,插入图片、图表等，在此茶拙上借助于MicrosoftQfficeJnterop.ExceI对这些操作进行了M装最终形成了ExceIHandlert,本文主要对自己封装的这个类进行简洁的举例说明,关于此类的完整代码参见第三部分.国总：对于命名空间MiCrOSoft.OfficeJnter
2、opExceI.运用之前须要引用CoM：MicrosoftOffice11.0Object1.ibraryoffice2003)或者MiCrOSOftOffice12.0Object1.ibrary(office2007,假如引川列表中没有，须要自行添加XrProgramOffiCe/OFFICE1112/EXCE1.EXE的引用.C如已羟安装VSTO(VISUalStudioToolsForOffice).亦可干腌添加对X:ProgramFllesXMicrosoftVisualStudio9.08.0VisualStudloToolsforOffcePIAOfficell12Microso
3、ft.Office.Interop.Ecel.dll的引用.2、ExceIHandIer为了展示该类的运用方法，新建了个WinForm的测试项目进行测试,项目名称可自定，添加对ExcelHandler的dll的引用.全部测试代码均放在港体的个按钮单击事务的处理方法中.2.1 创建EXCel文件H/测试ExceIHandler类/privatevoidbuttonTest_Click(objectsender,EventArgse)(stringexcel=string.Format(0Excel-l.xls,AppDomain1CurrentDomain.BaseDirectory,DateT
4、ime.Now.ToStringCyyyyMMddHHmmss);using(ExceIHandIerhandler=newEcelHandler(ecel,true)(handler.Ope11OrCreate();MeSSageBOX.Show(创建EXCeI胜利！”)；handler.Save();MessageBox.Show(string.Format(lffExcel胜利!Excel路径:0,excel);)点击按钮执行后，可以行到在DebUg书目下多了个Excel文件.iyProjctsT皿UinUD*taViaaliztioaTnginf;.DataViualizatioabi
5、ftDbu文件99收麻工具帮助QP0后退1，上搜索文件荚国地址(B)|JDb1文件和文件夷任务J创陡一个新文件夹将这个文件夹发布到9ffb白共享此文件夹JJWicrosoft.OfficeInterop.Exceldlli*1TngngDataVi三ualraton.ext;TanncDataVisudiration.pdb*1TailingDataVisualiration.v三ho三t.CjCeTn*色蜓的Ex”】文传此文件的完整路径为:F:MyProjectsXTanging.DataVisuaizationTanging.DataVisualizatlonbinDebugExcel-2
6、02.xls.r.:之后的例子的Excel的文件的路径须要为你自己创建的Excel的相应路径.下面将举例说明对此Excel文件进行操作.2.2 创建自己的工作表WorksheetprivatevoidbuttonTeSjeliCk(ObjeCtsender,EventArgse)(stringexcel=string.FormatC0Excel-202.ls,AppDomaIn.CurrentDomain.BaseDirectory);using(ExceIHandIerhandler=newEXCeIHandler(excel,false)设置其次个参数为false衣示干脆打开现力的Exce
7、l文档handler.OpenOrCreate();创建一个WorksheetWorksheetsheet=handler.AddWorksheetCTestSheet);删除除TeStSheet之外的具余WOrkSheethandler.DeleteWorksheetcept(sheet);handler.Save();再次打开创建的EXCel,可以乔到新建的Worksheet2.3 单元格、Range等的掾作下而设EAl到E5的单元格样式，并且设置单元格值等privatevoidbuttonTest_Cllck(objectsender,EventArgse)(stringexcel=st
8、ring.Format(0Excel-202.ls,AppDomain1CurrentDomain.BaseDirectory);using(ExceIHandIerhandler=newEXCeIHandler(excel,false)设置其次个参数为false表示干脆打开现有的Excel文档handler.Ope11OrCreate();快符WOrkSheet对象Worksheetsheet=handler.GetWorksheet(TestSheef);/AI-ESRangerange=handler.GetRange(sheet,1,1,5,5);handler.SetRangeFor
9、mat(range);handler.SetCeliValue(sheetr1,1,测试);handler.SetCellValue(sheetr2,1,测试2);range.Font.Bold=true;加租handler.Save();)效果如下：2.4 导入DataTable代码如下；privatevoidbuttonTest_Click(objectsender,EventArgse)stringexcel=string.Format(0Ecel-202.xls,AppDomain1CurrentDomain.BaseDirectory);using(ExceIHandIerhandl
10、er=newExceHandler(ecel,false)设置其次个秒数为false表示干脆打开现有的Excel文档handler.OpenOrCreate();获得VVOrkSheet对象Worksheetsheet=handler.GetWorksheet(TestSheet);/A1-E5Rangerange=handler.GetRange(sheetf1,1,5,5);handler.SetRangeFormat(range);range.Font.Bold=true;System1Data-DataTabIetable=newSystem.Data.DataTable();tabl
11、e.Columns.AddRange(newDataCoIumnfnewDataCOIUmn(测试列l)fnewDataCOI1.Imn(测试列2),newDataCOIUmn(“测试列3);Randomrandom=newRandom(20);for(inti=O;i10;i+)效果如下图;5fd,!5EXCeI-20100314181502JdS0HMBO-Mic.BtAIB/口-IlAaII二laA字依陶玷J市算变图梏式ls融条件格式A，M%I庭哥国表悟格式IAFr1司3单元格律式格式S2字_2_|珏式单元格Ai三三三jjerr2,!入页面布层公式58据二W三1A测试BCD2测试2345
12、6测试导入表格7涕试列1测试列2测试列38174768567477982702357368108671475845181164872658161238168746981513520726953953142185586474315754737201951666003899129317797710188318192021史阴G图片1G,TitleTe!3、ExceIHandIer类完整源码usingSystem;usingSystem.IO;usingSyStern.Reflection;usingSyStem.Text;usingXls=Microsoft1Office.Interop.Exce
13、l;usingSystem.Data;usingSystem.Collections.Generic;usingSystem.Runtime-InteropServices;namespaceMyPrograme/Excel处理类III/rttJ11lExcel.掾作工作表，设巴单元格样式对齐方式等，导入内存、数据库的数据虫,插入图片到Excel等/remarkspublicsealedclassExceIHandIer:!Disposable#region构造函数/ExceIHandIer的构造函数，/publicExcelHandler(strlng):this(,false)/EXCel
14、HarKner的构造画数H!H!Excel文件名.书定路径vparampublicEcelHandler(strlng):this(,false)IIIIII创建ExceIHandIerim.指定文件名以及是否创建新的Excel文件HIHIExcel文件名，肯定路径/是再创if新的EXCel文件vparampublicExcelHandler(string,boolCreateNew)(this.=;this.ifCreateNew=CreateNew;*endreglon#region字段和典性privatestaticreadonlyobject-missing=Missing.Value
15、;privatestringH!/Excel文件名IIIpublicstringset_=value;)/是否新建Excel文件H!privateboolIfCreateNew;privateXls-Application_app;/3前EXCel应用程.序/publicXls-ApplicationApp(getreturn_app;set_app=value;)privatels.Workbooks_aIIWorkbooks;H!/iilEXCel应用程序所打开的全部Excel工作簿IIIpublicXlsAVorkbooksAlIWorkbooksgetreturn-allWorktxo
16、ks;set_allWorkbooks=value;)privateXls-Workbook_CUrrentWOrkbook;IllIII当前EXCel工作符/publicXlsAVorkbookCurrentWorkbookgetreturn_CUrrentWOrkbOok;set-currentWorkbook=value;)privateXIs1Worksheets-allWorksheets;/当前Excel工作袍内的全部SheetU!publicXIs1WorksheetsAIIWorksheetsgetreturn-allWorksheets;set_allWorksheets=v
17、alue;)privateXls-Worksheet_CUrTentWOrkSheet;/当前Excel中激活的SheetH!publicXIsAVorksheetCurrentWorksheetgetreturn_currentWorksheet;set-Currentworksheet=value;)*endregion#region初始化操作，打开或者创建文件H!/初始化,假如不创建新文件干脆打开，否则创建新文件/publicvoidOpenOrCreateO(this.App=newXls.ApplicationCIassO;this.AIIWorkbooks=this.App.Wor
18、kbooks;if(IthisJfCreateNew)/)H.if(!(this.)thrownew(找不到指定的Excel文件.阴;7杳路铃是行询！”，this.);this.CurrentWorkbook=this.AIIWorkbooks.Open(this.,Type.Missing,Type1Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Xls.IPIatform.xlWinclows,Type-Missing,Type-Missing,Type.Missing,Type.Missing,ye.Missing,
19、Type.Missing,ye.Missing);else创建新文件(If(this.)(this.);)this.CurrentWorkbook=this.AllWorkbooks.Add(Type-Missing);this.AIIWorksheets=this.CurrentWorkbook.WorksheetsasXls.Worksheets;this.Currentworksheet=this.CurrentWorkbook.ActiveSheetasXls.Worksheet;this.App.DispIayAIerts=false;this.App.Visible=false;)
20、*endregion#regionExcelSheet相关操作等/III依据工作衣名获得Excel工作表对象的引用/H!IIIpublicXIs1WorksheetGetWorksheet(stringSheetNarne)returnIhls-CurrentWorkbook.SheetssheetNameasXlsAVorksheet;)/III依樵工作表。弓愀知EXCel工作表对象的引用HIU!IIIpublicXls.WorksheetGetWorksheet(intindex)returnthis.CurrentWorkbook.Sheets.get-Item(lnde)asls.Wo
21、rksheet;III/给当前工作簿添加工作表并返回的方法重载,添加工作表后不使K激活H/IIIIIIpublicXIsAVorksheetAddWorksheet(stringSheetName)(returnthis.AddWorksheet(sheetNameffalse);)/给当前1：作簿添加I：作表并返回III/工作表名HI创建后是否使其激活/publicXIsAVorksheetAddWorksheet(stringSheetName,boolactivated)(Xls.Worksheetsheet=this.CurrentWorkbook.Worksheets.Add(Typ
22、e.Missing,Type1Missing,1,Type.Missing)asXls.Worksheet;sheet.Name=SheetName;If(activated)(sheet.Activate();returnsheet;/重命名工作费III/工作表对象vparam/paramname=newName工作表新名称/publicXIsAVorksheetRenameWorkSheet(XIS.Worksheelsheet,stringnewName)sheet.Name=newName;returnsheet;/Jfi命名工作表/Ifi41Hf新名称vparam/publicXIs
23、AVorksheetRenameWorkSheet(StnngoldName,stringnewName)(XIs1Worksheetsheet=this.GetWorksheet(OldName);returnthis.RenameWorksheet(sheet,newName);)/州除工作表/工作衣名vparampublicvoidDeIeteWorkSheet(StnngSheetName)if(this.CurrentWorkbook.Worksheets-Count=1)(thrownewInValidOPeratiOnEXCePtion(I：作簿至少须吆一个可视化的工作表！thi
24、s.GetWorksheet(SheetName)1DeIeteO;/州除除公敌sheet指定外的K余工作表III/publicvoidDeleteWorksheetExcept(ls.Worksheetsheet)foreach(Xis.WorksheetWSinthis.CurrentWorkbook.Worksheets)(if(sheetI=WS)ws.Delete();)#endregion*region单元格，Range相关操作III/设置单元格的值/III工作表vparamHI单元格行号III单元格列号vparam/单元格vParampublicvoidSetCellValue(
25、ls.Worksheetsheet,introwNumber,intCoIumnNumbei;objectvalue)sheet.Cells(rowNumberrCOlumnNumber=value;)/合并於元格/Tf/第一个单元格行号vparam/第一个单元格列号vparamIII结束单元格行号vparamH!结束单元格列号vparampublicvoidMergeCells(ls.Worksheefsheet,ntrowNumberlfintCoIumnNumberl,introwNumber2,intcolumNumber2)Xls.Rangerange=this.GetRange(s
26、heet,rowNumberl,CoIumnNumberl,rowNumber2,coumnNumber2);range.Merge(Type1Missing);)III/获得RangexJ/III工作表/第一个单元格行号vparam/fi个雅元格列号Hf结束单元恪行号vparamIII结束他元格列号vparamIIIpublicXls.RangeGetRange(Xls.Worksheetsheet,introwNumberl,intCoIumnNumberl,introwNumber2,intcolumnNumber2)returnsheet.get_Range(sheet.Cellsro
27、wNumberl,COlumnNumberl,sheet.CellsrowNumber2,CoIUmnNUmber2);)*endregion#region设置单元格，Range的样式、对齐方式自动携行等IIIIll自动调整,设过门动换行以及自动调整列宽/HIpublicvoidAutoAdjustment(ls.Rangerange)(range.WrapText=true;range.AutoFit();)/III设置Range的单元格样式H!/Vremarks将各项值设置为跣认值/remarks/publicvoidSetRangeFOrmat(XlS.Rangerange)this.S
28、etRangeFormat(range,11,Xs.Constants.xlAutomatic,ls.Constants.xlColor1,Xs.Constants.I1.eft);)/设置Range的单元格样式/remarks将各项值设置为取UJfi/remarksIIIH!/VParamname=rowNumber2/publicvoidSetRangeFormat(Xls.Worksheetsheet,introwNumberl,intCoIumnNumberl,introwNumber2,intcolumNumber2)thls.SetRangeFormat(sheet,rowNumb
29、erl,COlumnNumberl,rowNumber2,columNumber2,11,ls.Constants.XlAutomatic);)III/设置Range的单元格样式/第,个维元格行号/第个总元格列号vparamIII结束单元恪行号vparam/结束单元格列号vparamIIIH!publicvoidSetRangeFormat(s.Worksheetsheet,introwNumberl,intCoIumnNumberl,introwNumber2,intcolumNumber2,objectfontSize,objectfontName)this.SetRa11geFormat
30、(this.GetRa11ge(sheet,rowNumberl,CoIumnNumberl,rowNumber2,columNumber2),fontSize,fontName,Xs.Constants.xlColorl,Xs.Constants.xl1.eft);/设置Range的单元恪样式IIIIIIRa11ge)III字体大小/字体名称/4T*frpublicvoidSetRangeFOrmat(XIS.Rangerange,objectfontsize,objectfontName,ls.Constantscolor;Xls,ConstantshorizontalAlignment)
31、range.Font.Color=color;range.Font.Size=fontsize;range.Font.Name=fontName;range.HorizontaIAIignment=horizontalAlignment;)*endregion#region导入内存中的DataTabIe/导入内存中的数据表到EXCeI中/HIVremarks干腌9入到工作表的最起始部分/remarksIIIH!/publicvoidImportDataTable(Xls.Worksheetsheet,stringheaderTitle,boolShowTitIe,objectheaders,D
32、ataTabIetable)this.ImportDatalabe(sheet,headerTitle,ShowTitIe,headers,1,1,table);)/导入内存中的数据表到EXCeI中/remarks干脆导入到工作衣的最起始部分，且不显示标时行(/remarksIIIIfl/publicvoidImPortDataTable(XIS.Worksheetsheet,objectheaders,DataTabIetable)this.ImportDataTable(sheetfnull,false,headers,table);/导入内存中的数据表到EXCel中/IIIVremarks标遨行每一列及DataTabIe标网一样/remarksH/HfpublicvoidImportDataTable(Xls.Worksheetsheet,DataTabIetable)1.isfheaders=new1.st();foreach(DataCoIumncolumnintable.Columns)(headers.Add(column.Caption);this.ImportDatalabe(sheetrheaders.ToArray(),table);)/导入内存中的数抠表到EXCeI中III/fH1表格标lvparam/
