C#-实现Excel导出-加载模板-导入数据到模板

ǰЩÌ칫˾×öÁ˸öÏîÄ¿£¬Óöµ½Òªµ¼³öÊý¾Ýµ½Excel£¬²¢ÒªÇóÓиñʽ£¬²»Óÿͻ§ÔÚÈ¥ÅÅ°æ¡£±¾À´ÓÃË®¾§±¨±íÖ®ÀàµÄ²å¼þ£¬×öÕâ¸ö±È½ÏºÃ£¬¹«Ë¾²»Ô¸ÌÍÇ®£¬Ö»ºÃ×Ô¼ºÐ´³ÌÐòȥʵÏÖ¡£¹ØÓÚ¸ñʽµÄ¿ØÖÆÓÐÁ½ÖÖ·½°¸£ºÒ»¡¢Ìî³äÊý¾Ýʱ»òÌïÖÐÍêÊý¾ÝºóÔÚ³ÌÐòÖпØÖƸñʽ£»¶þ¡¢Ð´ºÃÄ£°å£¬¸ñʽ¹Ì¶¨ºÃ£¬Ö»ÍùÀïÌîÊý¾Ý¾ÍºÃÁË¡£¶ÔÓÚµÚÒ»ÖÖ·½°¸£¬ÔÚÎÒǰһƪÈÕÖ¾ÀïÓнéÉÜ£¬ÏÂÃæ½éÉÜһϵڶþÖÖ·½°¸¡£usingExcel;usingSystem.IO;usingSystem.Reflection;usingSystem.Runtime.InteropServices;usingSystem.Configuration;publicpartialclassUcTaskList:Tobto.DJ.DJWebUI.UIClass.DJUcListPageBase{[DllImport(User32.dll,CharSet=CharSet.Auto)]publicstaticexternintGetWindowThreadProcessId(IntPtrhwnd,outintID);privateResultPacketToExcel(){ResultPacketrp=newResultPacket();stringexcelTemplateDPath;//EXCELÄ£°åĬÈÏ·þÎñÆ÷ÎïÀí´æ·Å·¾¶stringtempEFilePath=\\TempFiles;//EXCELÁÙʱÎļþ±£´æ·þÎñÆ÷ÎïÀí´æ·Å·¾¶stringtempEFileXPath=/TempFiles;//EXCELÁÙʱÎļþ±£´æ·þÎñÆ÷ÐéÄâ´æ·Å·¾¶Excel.Applicationapp=null;//EXCEL¶ÔÏóWorkbooksworkbooks;//¹¤×÷²¾¼¯ºÏ_Workbookworkbook;//µ±Ç°¹¤×÷²¾Sheetssheets;//SHEETÒ³¼¯ºÏ_Worksheetworksheet;//µ±Ç°SHEETÒ³#region¶ÁÈ¡ÅäÖÃÎļþÖзÏßÄ£°å·¾¶¼°Ãû³Æ²¢ÑéÖ¤ÊÇ·ñ´æÔÚ//»ñÈ¡ÅäÖÃÎļþÖзÏßÄ£°å·¾¶¼°Ãû³ÆexcelTemplateDPath=Server.MapPath(ConfigurationManager.AppSettings[ExcelTemplateDPath]);tempEFileXPath=ConfigurationManager.AppSettings[ExcelTemplateDPath]+tempEFileXPath;tempEFilePath=excelTemplateDPath+tempEFilePath;excelTemplateDPath+=\\TaskTemplet.xls;//ÑéÖ¤EXCELÄ£°åÎļþÊÇ·ñ´æÔÚif(!File.Exists(excelTemplateDPath)){rp.IsError=true;rp.Message=ÅäÖÃÎļþÖÐÖ¸¶¨µÄÄ£°åÎļþ²»´æÔÚ;returnrp;}//ÑéÖ¤EXCELÁÙʱÎļþ¼ÐÊÇ·ñ´æÔÚif(!File.Exists(tempEFilePath)){Directory.CreateDirectory(tempEFilePath);}#endregion#region´ÓÅäÖÃÎļþÖÐÈ¡ÁÐÃûΪÌî³äÊý¾Ý×ö×¼±¸ArrayListlist=newArrayList();if(ConditionArr!=null&&ConditionArr.Count0){list=TaskManageBrl.Find(ConditionArr[0],ConditionArr[1],ConditionArr[2],ConditionArr[3],bool.Parse(ConditionArr[4]),ConditionArr[5],ConditionArr[6],ConditionArr[7],0,10000,true,CurrentLoginUserInfo);}#endregiontry{#regionÆô¶¯excel½ø³Ì²¢¼ÓÔØÄ£°å//Æô¶¯EXCEL½ø³Ìapp=newExcel.Application();if(app==null){rp.IsError=true;rp.Message=Excel½ø³ÌÆô¶¯³ö´í,ÇëÈ·ÈÏÊÇ·ñÒýÓÃEXCEL×é¼þ;returnrp;}app.Visible=false;app.UserControl=true;app.DisplayAlerts=false;//¼ÓÔضÁÈ¡Ä£°åworkbooks=app.Workbooks;workbook=workbooks.Add(excelTemplateDPath);sheets=workbook.Worksheets;worksheet=(_Worksheet)sheets.get_Item(1);#endregion#regionÌî³äÊý¾Ýfor(inti=3;ilist.Count;i++){worksheet.Cells[i,1]=EmonÈÎÎñ;worksheet.Cells[i,2]=343;worksheet.Cells[i,3]=233;}#endregion#region±£´æÁÙʱÎļþworksheet.Columns.AutoFit();//ÉèÖÃ×Ô¶¯Áпítry{stringtempFileName=TaskList+System.DateTime.Now.ToString(yyyyMMddHHmmss)+.xls;tempEFilePath+=\\+tempFileName;tempEFileXPath+=/+tempFileName;worksheet.SaveAs(tempEFilePath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value);DeleteTempFiles(tempEFilePath);//Çå³ýÁÙʱÎļþ}catch//(Exceptionex){rp.IsError=true;rp.Message=EXCELÎļþÒÑÌî³äÍê±Ï,µ«ÔÚ±£´æʱ·¢Éú´íÎó;returnrp;}rp.Message=tempEFileXPath;returnrp;#endregion}catch(Exceptionex){rp.IsError=true;rp.Message=ex.ToString();returnrp;}finally{ExcelDispose(app);}}//////¹Ø±ÕExcel½ø³Ì////////////protectedvoidExcelDispose(_ApplicationCurExcel){try{if(CurExcel!=null){CurExcel.Workbooks.Close();CurExcel.Quit();#regionÇ¿ÐÐɱËÀ´ò¿ªµÄExcel½ø³ÌIntPtrexcelIPtr=newIntPtr(CurExcel.Hwnd);//µÃµ½Õâ¸ö¾ä±ú£¬¾ßÌå×÷ÓÃÊǵõ½Õâ¿éÄÚ´æÈë¿ÚintproID=0;GetWindowThreadProcessId(excelIPtr,outproID);//µÃµ½±¾½ø³ÌΨһ±êÖ¾kSystem.Diagnostics.Processpro=System.Diagnostics.Process.GetProcessById(proID);//µÃµ½¶Ô½ø³ÌkµÄÒýÓÃpro.Kill();//¹Ø±Õ½ø³Ìk#endregionCurExcel=null;}}catch//(Exceptionex){//ShowMessage(ÔÚÊÍ·ÅÄÚ´æʱ·¢Éú´íÎó£º+ex.ToString());}}//////ɾ³ý֮ǰÎÞÓõÄÁÙʱÎļþ//////protectedvoidDeleteTempFiles(stringiFileName){FileInfof=newFileInfo(iFileName);DirectoryInfodir=f.Directory;foreach(FileInfotfileindir.GetFiles()){try{tfile.Delete();}catch{}}}}±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£º