C操作Excel类以及其使用举例说明.docx

C#操作Excel的类以及其运用举例说明1、曲育这两天Ih于某种须要，探讨了吓.NET中对EXCel的互操作.之前主要是卜脆通过第三方的组件等方式来操作的Excel,这次细致的探讨了一下，对常用的EXCel操作需求做了一个简洁的总结，比如创建EXceI,设置单元格样式，合并单元格.导入内存中的DataTable,插入图片、图表等，在此茶拙上借助于MicrosoftQfficeJnterop.ExceI对这些操作进行了M装最终形成了ExceIHandlert,本文主要对自己封装的这个类进行简洁的举例说明,关于此类的完整代码参见第三部分.国总：对于命名空间MiCrOSoft.OfficeJnteropExceI.运用之前须要引用CoM：MicrosoftOffice11.0Object1.ibraryoffice2003)或者MiCrOSOftOffice12.0Object1.ibrary(office2007>,假如引川列表中没有，须要自行添加XrProgramOffiCe/OFFICE1112/EXCE1.EXE的引用.C如已羟安装VSTO(VISUalStudioToolsForOffice).亦可干腌添加对X:ProgramFllesXMicrosoftVisualStudio9.08.0VisualStudloToolsforOffcePIAOfficell12Microsoft.Office.Interop.E×cel.dll的引用.2、ExceIHandIer为了展示该类的运用方法，新建了个WinForm的测试项目进行测试,项目名称可自定，添加对ExcelHandler的dll的引用.全部测试代码均放在港体的个按钮单击事务的处理方法中.2.1 创建EXCel文件H/<summary>/测试ExceIHandler类/<summary>/<paramname="sender"><param>/<aramname="e"><param>privatevoidbuttonTest_Click(objectsender,EventArgse)(stringexcel=string.Format('0Excel-l.xls",AppDomain1CurrentDomain.BaseDirectory,DateTime.Now.ToStringCyyyyMMddHHmmss");using(ExceIHandIerhandler=newE×celHandler(e×cel,true)(handler.Ope11OrCreate();MeSSageBOX.Show("创建EXCeI胜利！”)；handler.Save();MessageBox.Show(string.Format("lffExcel胜利!Excel路径:0",excel);)点击按钮执行后，可以行到在DebUg书目下多了个Excel文件.iyProjctsT皿UinUD*taViaaliztioaTnginf;.DataViualizatioabiftDbu文件9»©9收麻工具帮助QP0后退1，上搜索文件荚国地址(B)|JDb1<文件和文件夷任务J创陡一个新文件夹将这个文件夹发布到9ffb白共享此文件夹JJWicrosoft.OfficeInterop.Exceldlli*1TngngDataVi三ualraton.ext;TanncDataVisu«diration.pdb*1TailingDataVisualiration.v三ho三t.CjCe<Tn<nDataVisuoliz&tion.v三host.exe.nmifest)T4ncnInterop,dll囱I11CInterop.pdb1esfvw,“三>*色蜓的Ex”】文传此文件的完整路径为:F:MyProjectsXTanging.DataVisuaizationTanging.DataVisualizatlonbinDebugExcel-202.xls.r'.:之后的例子的Excel的文件的路径须要为你自己创建的Excel的相应路径.下面将举例说明对此Excel文件进行操作.2.2 创建自己的工作表WorksheetprivatevoidbuttonTeSjeliCk(ObjeCtsender,EventArgse)(stringexcel=string.FormatC0Excel-202.×ls",AppDomaIn.CurrentDomain.BaseDirectory);using(ExceIHandIerhandler=newEXCeIHandler(excel,false)设置其次个参数为false衣示干脆打开现力的Excel文档<handler.OpenOrCreate();创建一个WorksheetWorksheetsheet=handler.AddWorksheetCTestSheet");删除除TeStSheet之外的具余WOrkSheethandler.DeleteWorksheet×cept(sheet);handler.Save();再次打开创建的EXCel,可以乔到新建的Worksheet2.3 单元格、Range等的掾作下而设EAl到E5的单元格样式，并且设置单元格值等privatevoidbuttonTest_Cllck(objectsender,EventArgse)(stringexcel=string.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.SetRangeFormat(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("0E×cel-202.xls",AppDomain1CurrentDomain.BaseDirectory);using(ExceIHandIerhandler=newExceHandler(e×cel,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();table.Columns.AddRange(newDataCoIumnf<newDataCOIUmn(测试列l")fnewDataCOI1.Imn("测试列2"),newDataCOIUmn(“测试列3");Randomrandom=newRandom(20);for(inti=O;i<10;i+)<table.Rows.Add(random.Next(100OO),random.Next(100OO),random.Next(100OO);)从第6行第例导入数据handler.ImportDataTable(sheet,"Slil",true,newstring"测试列1”,"测试列2","测试列3"),6,1,table);handler.Save();结果如下图:2.5 插入图片下面插入张图片到EXCel的第行第五列，代码如下：privatevoidbuttonTest-Click(objectsender,EventArgse)stringexcel=string.FormatC0Excel-202.×ls",AppDomaIn-CurrentDomain.BaseDirectory);using(ExceIHandIerhandler=newEXCelHandler(excel,false)设置其次个参数为false表示卜脆打开现力的Excel文档handler.OpenOrCreate();孩得Worksheet对象Worksheetsheet=handler.GetWorksheet("TestSheef);图片地址string=string.Format("0test.png",AppDomain.CurrentDomain.BaseDirectory);从第1行第5列插入图片Picturepic=handler.Addlmage(sheet,1,5);handler.Save();>效果如下图;5fd,!5EXCeI-20100314181502JdS0HMBO-Mic.B>tAIB/口-IlA'a'II二laA字依陶玷J市算变图梏式ls融条件格式A，M%I庭哥国表悟格式IAFr1司3单元格律式格式S2字_2_|珏式单元格Ai三三三jjerr2",!«入页面布层公式58据二W三1A测试BCD2测试23456测试导入表格7涕试列1测试列2测试列38174768567477982702357368108671475845181164872658161238168746981513520726953953142185586474315754737201951666003899129317797710188318192021史阴G图片1G,TitleTe!3、ExceIHandIer类完整源码usingSystem;usingSystem.IO;usingSyStern.Reflection;usingSyStem.Text;usingXls=Microsoft1Office.Interop.Excel;usingSystem.Data;usingSystem.Collections.Generic;usingSystem.Runtime-InteropServices;namespaceMyPrograme/<summary>/Excel处理类III<summary>/<remarks>rttJ11lExcel.掾作工作表，设巴单元格样式对齐方式等，导入内存、数据库的数据虫,插入图片到Excel等/remarkspublicsealedclassExceIHandIer:!Disposable#region构造函数/<summary>/ExceIHandIer的构造函数，/<summary>publicExcelHandler(strlng):this(,false)/<summary>/EXCelHarKner的构造画数H!<summary>H!<paramname="">Excel文件名.书定路径vparam>publicE×celHandler(strlng):this(,false)III<summary>III创建ExceIHandIerim.指定文件名以及是否创建新的Excel文件HI<summary>HI<paramname="">Excel文件名，肯定路径<param>/<paramname="createNew">是再创if新的EXCel文件vparam>publicExcelHandler(string,boolCreateNew)(this.=;this.ifCreateNew=CreateNew;*endreglon#region字段和典性privatestaticreadonlyobject-missing=Missing.Value;privatestringH!<summary>/Excel文件名III<summary>publicstring<getreturn>set<_=value;)/<summary>/是否新建Excel文件H!<summary>privateboolIfCreateNew;privateXls-Application_app;/<summary>/'3前EXCel应用程.序/<summary>publicXls-ApplicationApp(getreturn_app;set<_app=value;)private×ls.Workbooks_aIIWorkbooks;H!<summary>/iilEXCel应用程序所打开的全部Excel工作簿III<summary>publicXlsAVorkbooksAlIWorkbooksgetreturn-allWorktx>oks;set<_allWorkbooks=value;)privateXls-Workbook_CUrrentWOrkbook;Ill<summary>III当前EXCel工作符/<summary>publicXlsAVorkbookCurrentWorkbookgetreturn_CUrrentWOrkbOok;set-currentWorkbook=value;)privateXIs1Worksheets-allWorksheets;/<summary>/当前Excel工作袍内的全部SheetU!<summary>publicXIs1WorksheetsAIIWorksheetsgetreturn-allWorksheets;set<_allWorksheets=value;)privateXls-Worksheet_CUrTentWOrkSheet;/<summary>/当前Excel中激活的SheetH!<summary>publicXIsAVorksheetCurrentWorksheetgetreturn_currentWorksheet;set-Currentworksheet=value;)*endregion#region初始化操作，打开或者创建文件H!<summary>/初始化,假如不创建新文件干脆打开，否则创建新文件/<summary>publicvoidOpenOrCreateO(this.App=newXls.ApplicationCIassO;this.AIIWorkbooks=this.App.Workbooks;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,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;)*endregion#regionExcelSheet相关操作等/<summary>III依据工作衣名获得Excel工作表对象的引用/<summary>H!<paramname="sheetName"><param>III<returns><retrns>publicXIs1WorksheetGetWorksheet(stringSheetNarne)returnIhls-CurrentWorkbook.SheetssheetNameasXlsAVorksheet;)/<summary>III依樵工作表。弓愀知EXCel工作表对象的引用HI<summary>U!<paramname="index"><param>III<returns><returns>publicXls.WorksheetGetWorksheet(intindex)returnthis.CurrentWorkbook.Sheets.get-Item(lnde×)as×ls.Worksheet;III<summary>/给当前工作簿添加工作表并返回的方法重载,添加工作表后不使K激活H/<summary>III<paramname="sheetName"><param>III<returns><returns>publicXIsAVorksheetAddWorksheet(stringSheetName)(returnthis.AddWorksheet(sheetNameffalse);)/<summary>/给当前1：作簿添加I：作表并返回III<summary>/<paramname="sheetName”>工作表名<param>HI<paramname="actlvated”>创建后是否使其激活<aram>/<returns><returns>publicXIsAVorksheetAddWorksheet(stringSheetName,boolactivated)(Xls.Worksheetsheet=this.CurrentWorkbook.Worksheets.Add(Type.Missing,Type1Missing,1,Type.Missing)asXls.Worksheet;sheet.Name=SheetName;If(activated)(sheet.Activate();returnsheet;/<summary>/重命名工作费III<summary>/<paramname="sheet”>工作表对象vparam>/<paramname="newName"工作表新名称<param>/<returns><returns>publicXIsAVorksheetRenameWorkSheet(XIS.Worksheelsheet,stringnewName)<sheet.Name=newName;returnsheet;/<summary>/Jfi命名工作表/<summary>/<paramname="oldName">Ifi41<param>Hf<paramname="newName”>新名称vparam>/<returns><returs>publicXIsAVorksheetRenameWorkSheet(StnngoldName,stringnewName)(XIs1Worksheetsheet=this.GetWorksheet(OldName);returnthis.RenameWorksheet(sheet,newName);)/<summary>/州除工作表/<summary>/<paramname=,'sheetName”>工作衣名vparam>publicvoidDeIeteWorkSheet(StnngSheetName)<if(this.CurrentWorkbook.Worksheets-Count<=1)(thrownewInValidOPeratiOnEXCePtion("I：作簿至少须吆一个可视化的工作表！this.GetWorksheet(SheetName)1DeIeteO;/<summary>/州除除公敌sheet指定外的K余工作表III<summary>/<paramname="sheet"><param>publicvoidDeleteWorksheetExcept(×ls.Worksheetsheet)foreach(Xis.WorksheetWSinthis.CurrentWorkbook.Worksheets)(if(sheetI=WS)ws.Delete();)#endregion*region单元格，Range相关操作III<summary>/设置单元格的值/<summary>III<paramname="sheet">工作表vparam>HI<paramname="rowNumber”>单元格行号<param>III<paramname=''columnNumber''>单元格列号vparam>/<paramname="value”>单元格vParam>publicvoidSetCellValue(×ls.Worksheetsheet,introwNumber,intCoIumnNumbei;objectvalue)<sheet.Cells(rowNumberrCOlumnNumber=value;)/<summary>/合并於元格/<summary>/<paramname="sheet">Tf¾<param>/<paramname="rowNumbeT'>第一个单元格行号vparam>/<paramIIame="coumnNumberl">第一个单元格列号vparam>III<paramname="rowNumber2">结束单元格行号vparam>H!<paramname="columnNumber2">结束单元格列号vparam>publicvoidMergeCells(×ls.Worksheefsheet,ntrowNumberlfintCoIumnNumberl,introwNumber2,intcolumNumber2)<Xls.Rangerange=this.GetRange(sheet,rowNumberl,CoIumnNumberl,rowNumber2,coumnNumber2);range.Merge(Type1Missing);)III<summary>/获得RangexJ/<summary>III<paramname="sheet">工作表<param>/<paramname="rowNumberT'>第一个单元格行号vparam>/<paramname="COlumnNumberl">fi个雅元格列号<param>Hf<paramname="rowNumber2">结束单元恪行号vparam>III<paramname="columnNumber2">结束他元格列号vparam>III<returns><returns>publicXls.RangeGetRange(Xls.Worksheetsheet,introwNumberl,intCoIumnNumberl,introwNumber2,intcolumnNumber2)returnsheet.get_Range(sheet.CellsrowNumberl,COlumnNumberl,sheet.CellsrowNumber2,CoIUmnNUmber2);)*endregion#region设置单元格，Range的样式、对齐方式自动携行等III<summary>Ill自动调整,设过门动换行以及自动调整列宽/<summary>HI<paramname="ragew><param>publicvoidAutoAdjustment(×ls.Rangerange)(range.WrapText=true;range.AutoFit();)/<summary>III设置Range的单元格样式H!<summary>/Vremarks>将各项值设置为跣认值/remarks/<paramname="range"><param>publicvoidSetRangeFOrmat(XlS.Rangerange)this.SetRangeFormat(range,11,Xs.Constants.xlAutomatic,×ls.Constants.xlColor1,Xs.Constants.×I1.eft);)/<summary>/设置Range的单元格样式/<summary>/remarks将各项值设置为取UJfi/remarksIII<paramname="sheet"><param>H!<paramname="rowNumberl"><param>/<paramname="coumnNmberl"><param>/VParamname="rowNumber2"><param>/<paramname="columNumber2"><param>publicvoidSetRangeFormat(Xls.Worksheetsheet,introwNumberl,intCoIumnNumberl,introwNumber2,intcolumNumber2)thls.SetRangeFormat(sheet,rowNumberl,COlumnNumberl,rowNumber2,columNumber2,11,×ls.Constants.XlAutomatic);)III<summary>/设置Range的单元格样式/<summary>/<paramname="sheet"><param>/<paramname="rowNumberl">第,个维元格行号<param>/<paramname="columnNumberl">第个总元格列号vparam>III<paramname="rowNumber2">结束单元恪行号vparam>/<paramname="columnNumber2">结束单元格列号vparam>III<paramname="fontSize"><param>H!<paramname="fontName"><param>publicvoidSetRangeFormat(×s.Worksheetsheet,introwNumberl,intCoIumnNumberl,introwNumber2,intcolumNumber2,objectfontSize,objectfontName)<this.SetRa11geFormat(this.GetRa11ge(sheet,rowNumberl,CoIumnNumberl,rowNumber2,columNumber2),fontSize,fontName,Xs.Constants.xlColorl,Xs.Constants.xl1.eft);/<summary>/设置Range的单元恪样式III<summary>III<paramname="range">Ra11ge)<param>III<paramname="fontSize”>字体大小<param>/<paramname="fontName”>字体名称<param>/<paramname="color”字体颜色vparam>/<paramname="horizontalAlignment">4<T*fr<param>publicvoidSetRangeFOrmat(XIS.Rangerange,objectfontsize,objectfontName,×ls.Constantscolor;Xls,ConstantshorizontalAlignment)<range.Font.Color=color;range.Font.Size=fontsize;range.Font.Name=fontName;range.HorizontaIAIignment=horizontalAlignment;)*endregion#region导入内存中的DataTabIe/<summary>/导入内存中的数据表到EXCeI中/<summary>HIVremarks>干腌9入到工作表的最起始部分/remarksIII<paramname="sheet"><param>H!<paramname="headerTitle"><param>/<paramname="showTitle"><param>/<paramname="headers"><param>/<paramname="table"><aram>publicvoidImportDataTable(Xls.Worksheetsheet,stringheaderTitle,boolShowTitIe,objectheaders,DataTabIetable)this.ImportDatalabe(sheet,headerTitle,ShowTitIe,headers,1,1,table);)/<summary>/导入内存中的数据表到EXCeI中/<summary>/remarks干脆导入到工作衣的最起始部分，且不显示标时行(/remarksIII<param11ame="sheet"><param>Ifl<paramname="headers"><param>/<paramname="table"><param>publicvoidImPortDataTable(XIS.Worksheetsheet,objectheaders,DataTabIetable)<this.ImportDataTable(sheetfnull,false,headers,table);/<summary>/导入内存中的数据表到EXCel中/<summary>IIIVremarks>标遨行每一列及DataTabIe标网一样/remarksH/<paramname="sheet"><param>Hf<paramname="table"><param>publicvoidImportDataTable(Xls.Worksheetsheet,DataTabIetable)1.isf<string>headers=new1.st<string>();foreach(DataCoIumncolumnintable.Columns)(headers.Add(column.Caption);this.ImportDatalabe(sheetrheaders.ToArray(),table);)/<summary>/导入内存中的数抠表到EXCeI中III<summary>/<paramname="sheet">f<param>H1<paramname="headerTitle">表格标lvparam>/<