Question

C#操作excel，如何调用模板

Answer 1

1usingSystem;2usingSystem.IO;3usingSystem.Data;4usingSystem.Reflection;5usingSystem.Diagnostics;6usingcfg=System.Configuration;7//usingExcel;89namespaceExcelHelperTest10{11/// <summary23///构造函数，需指定模板文件和输出文件完整路径24/// </summary25/// <paramname="templetFilePath" Excel模板文件路径</param26/// <paramname="outputFilePath" 输出Excel文件路径</param27publicExcelHelper(stringtempletFilePath, stringoutputFilePath)28{29if(templetFilePath==null)30thrownewException("Excel模板文件路径不能为空！");3132if(outputFilePath==null)33thrownewException("输出Excel文件路径不能为空！");3435if(!File.Exists(templetFilePath))36thrownewException("指定路径的Excel模板文件不存在！");3738this .templetFile=templetFilePath;39this .outputFile=outputFilePath;4041}4243/// <summary44///将DataTable数据写入Excel文件（套用模板并分页）45/// </summary46/// <paramname="dt"DataTable</param47/// <paramname="rows" 每个WorkSheet写入多少行数据</param48/// <paramname="top"行索引</param49/// <paramname="left"列索引</param50/// <paramname="sheetPrefixName" WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2”</param51publicvoidDataTableToExcel(DataTabledt, introws, inttop, intleft, stringsheetPrefixName)52{53introwCount=dt.Rows.Count;//源DataTable行数54intcolCount=dt.Columns.Count;//源DataTable列数55intsheetCount=this .GetSheetCount(rowCount,rows);//WorkSheet个数56DateTimebeforeTime;57DateTimeafterTime;5859if(sheetPrefixName==null||sheetPrefixName.Trim()=="")60sheetPrefixName="Sheet";6162//创建一个Application对象并使其可见63beforeTime=DateTime.Now;64Excel.Applicationapp=newExcel.ApplicationClass();65app.Visible=true;66afterTime=DateTime.Now;6768//打开模板文件，得到WorkBook对象69Excel.WorkbookworkBook=app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,70missing,missing,missing,missing,missing,missing,missing);7172//得到WorkSheet对象73Excel.WorksheetworkSheet=(Excel.Worksheet)workBook.Sheets.get_Item(1);7475//复制sheetCount-1个WorkSheet对象76for(inti=1;i<sheetCount;i++)77{78((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);79}8081#region将源DataTable数据写入Excel82for(inti=1;i<=sheetCount;i++)83{84intstartRow=(i-1)*rows;//记录起始行索引85intendRow=i*rows;//记录结束行索引8687//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数88if(i==sheetCount)89endRow=rowCount;9091//获取要写入数据的WorkSheet对象，并重命名92Excel.Worksheetsheet=(Excel.Worksheet)workBook.Worksheets.get_Item(i);93sheet.Name=sheetPrefixName+"-"+ i.ToString();9495//将dt中的数据写入WorkSheet96for(intj=0;j<endRow-startRow;j++)97{98for(intk=0;k<colCount;k++)99{100sheet.Cells[top+ j,left+k]=dt.Rows[startRow+j][k].ToString();101}102}103104//写文本框数据105Excel.TextBoxtxtAuthor=(Excel.TextBox)sheet.TextBoxes("txtAuthor");106Excel.TextBoxtxtDate=(Excel.TextBox)sheet.TextBoxes("txtDate");107Excel.TextBoxtxtVersion=(Excel.TextBox)sheet.TextBoxes("txtVersion");108109txtAuthor.Text="KLY.NET的Blog";110txtDate.Text=DateTime.Now.ToShortDateString();111txtVersion.Text=" 1.0.0.0";112}113#endregion114115//输出Excel文件并退出116try117{118workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);119workBook.Close(null, null);120app.Workbooks.Close();121app.Application.Quit();122app.Quit();123124System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);125System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);126System.Runtime.InteropServices.Marshal.ReleaseComObject(app);127128workSheet=null;129workBook=null;130app=null;131132GC.Collect();133}134catch(Exceptione)135{136throwe;137}138finally139{140Process[]myProcesses;141DateTimestartTime;142myProcesses=Process.GetProcessesByName("Excel");143144//得不到Excel进程ID，暂时只能判断进程启动时间145foreach(ProcessmyProcessinmyProcesses)146{147startTime=myProcess.StartTime;148149if(startTimebeforeTime&&startTime<afterTime)150{151myProcess.Kill();152}153}154}155&nb