熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

asp.net(C#)套用模板操作Excel

2013-11-13 10:08:41  來源: .NET編程 

  當需要輸出帶大量公式的Excel文檔的時候在代碼裡寫公式就太累了

  用設計好的Excel模板復制一下往裡面添加數據比較省事

  模板

  

  導出文件

  

  大氣象

  using System;

  using SystemData;

  using SystemConfiguration;

  using SystemWeb;

  using SystemWebSecurity;

  using SystemWebUI;

  using SystemWebUIWebControls;

  using SystemWebUIWebControlsWebParts;

  using SystemWebUIHtmlControls;

  using SystemIO;

  using SystemReflection;

  using MicrosoftOfficeInteropExcel;

  public partial class _Default : SystemWebUIPage

  {

  protected void Page_Load(object sender EventArgs e)

  {

  if (!IsPostBack)

  Bind();

  }

  private void Bind()

  {

  //模板文件

  string TempletFileName = ServerMapPath(template/) + templatexlsx;

  //導出文件

  string ReportFileName = ServerMapPath(xls/) + outxlsx;

  string strTempletFile = PathGetFileName(TempletFileName);

  //將模板文件復制到輸出文件

  FileInfo mode = new FileInfo(TempletFileName);

  modeCopyTo(ReportFileName true);

  //打開excel

  object missing = MissingValue;

  Application app = null;

  Workbook wb = null;

  Worksheet ws = null;

  Range r = null;

  //

  app = new MicrosoftOfficeInteropExcelApplication();

  wb = appWorkbooksOpen(ReportFileName false missing missing missing missing missing missing missing missing missing missing missing missing missing);

  appVisible = true;

  //得到WorkSheet對象

  ws = (Worksheet)wbWorksheetsget_Item();

  //添加或修改WorkSheet裡的數據

  wsCells[ ] = ;

  wsCells[ ] = ;

  wsCells[ ] = ;

  //代碼裡寫個公式

  r = (Range)wsCells[ ];

  rFormula = =A*B;

  //輸出Excel文件並退出

  wbSave();

  wbClose(null null null);

  appWorkbooksClose();

  appApplicationQuit();

  appQuit();

  SystemRuntimeInteropServicesMarshalReleaseComObject(ws);

  SystemRuntimeInteropServicesMarshalReleaseComObject(wb);

  SystemRuntimeInteropServicesMarshalReleaseComObject(app);

  ws = null;

  wb = null;

  app = null;

  }

  }

  using System;

  using SystemIO;

  using SystemData;

  using SystemReflection;

  using SystemDiagnostics;

  using cfg = SystemConfiguration;

  //using Excel;

  

  namespace ExcelHelperTest

  {

      /**//// <summary>

      /// 功能說明套用模板輸出Excel並對數據進行分頁

      /// 作    者Lingyun_k

      /// 創建日期

      /// </summary>

      public class ExcelHelper

      {

          protected string templetFile = null;

          protected string outputFile = null;

          protected object missing = MissingValue;

  

          /**//// <summary>

          /// 構造函數需指定模板文件和輸出文件完整路徑

          /// </summary>

          /// <param name=templetFilePath>Excel模板文件路徑</param>

          /// <param name=outputFilePath>輸出Excel文件路徑</param>

          public ExcelHelper(string templetFilePathstring outputFilePath)

          {

              if(templetFilePath == null)

                  throw new Exception(Excel模板文件路徑不能為空!);

  

              if(outputFilePath == null)

                  throw new Exception(輸出Excel文件路徑不能為空!);

  

              if(!FileExists(templetFilePath))

                  throw new Exception(指定路徑的Excel模板文件不存在!);

  

              thistempletFile = templetFilePath;

              thisoutputFile = outputFilePath;

  

          }

  

          /**//// <summary>

          /// 將DataTable數據寫入Excel文件(套用模板並分頁)

          /// </summary>

          /// <param name=dt>DataTable</param>

          /// <param name=rows>每個WorkSheet寫入多少行數據</param>

          /// <param name=top>行索引</param>

          /// <param name=left>列索引</param>

          /// <param name=sheetPrefixName>WorkSheet前綴名比如前綴名為Sheet那麼WorkSheet名稱依次為SheetSheet</param>

          public void DataTableToExcel(DataTable dtint rowsint topint leftstring sheetPrefixName)

          {

              int rowCount = dtRowsCount;        //源DataTable行數

              int colCount = dtColumnsCount;    //源DataTable列數

              int sheetCount = thisGetSheetCount(rowCountrows);    //WorkSheet個數

              DateTime beforeTime;

              DateTime afterTime;

  

              if(sheetPrefixName == null || sheetPrefixNameTrim() == )

                  sheetPrefixName = Sheet;

  

              //創建一個Application對象並使其可見

              beforeTime = DateTimeNow;

              ExcelApplication app = new ExcelApplicationClass();

              appVisible = true;

              afterTime = DateTimeNow;

  

              //打開模板文件得到WorkBook對象

              ExcelWorkbook workBook = appWorkbooksOpen(templetFilemissingmissingmissingmissingmissing

                                  missingmissingmissingmissingmissingmissingmissing);

  

              //得到WorkSheet對象

              ExcelWorksheet workSheet = (ExcelWorksheet)workBookSheetsget_Item();

  

              //復制sheetCount個WorkSheet對象

              for(int i=;i<sheetCount;i++)

              {

                  ((ExcelWorksheet)workBookWorksheetsget_Item(i))Copy(missingworkBookWorksheets[i]);

              }

  

              將源DataTable數據寫入Excel#region 將源DataTable數據寫入Excel

              for(int i=;i<=sheetCount;i++)

              {

                  int startRow = (i ) * rows;        //記錄起始行索引

                  int endRow = i * rows;            //記錄結束行索引

  

                  //若是最後一個WorkSheet那麼記錄結束行索引為源DataTable行數

                  if(i == sheetCount)

                      endRow = rowCount;

  

                  //獲取要寫入數據的WorkSheet對象並重命名

                  ExcelWorksheet sheet = (ExcelWorksheet)workBookWorksheetsget_Item(i);

                  sheetName = sheetPrefixName + + iToString();

  

                  //將dt中的數據寫入WorkSheet

                  for(int j=;j<endRowstartRow;j++)

                  {

                      for(int k=;k<colCount;k++)

                      {

                          sheetCells[top + jleft + k] = dtRows[startRow + j][k]ToString();

                      }

                  }

  

                  //寫文本框數據

                  ExcelTextBox txtAuthor = (ExcelTextBox)sheetTextBoxes(txtAuthor);

                  ExcelTextBox txtDate = (ExcelTextBox)sheetTextBoxes(txtDate);

                  ExcelTextBox txtVersion = (ExcelTextBox)sheetTextBoxes(txtVersion);

  

                  txtAuthorText = KLYNET的Blog;

                  txtDateText = DateTimeNowToShortDateString();

                  txtVersionText = ;

              }

              #endregion

  

              //輸出Excel文件並退出

              try

              {

                  workBookSaveAs(outputFilemissingmissingmissingmissingmissingExcelXlSaveAsAccessModexlExclusivemissingmissingmissingmissing);

                  workBookClose(nullnullnull);

                  appWorkbooksClose();

                  appApplicationQuit();

                  appQuit();

  

                  SystemRuntimeInteropServicesMarshalReleaseComObject(workSheet);

                  SystemRuntimeInteropServicesMarshalReleaseComObject(workBook);

                  SystemRuntimeInteropServicesMarshalReleaseComObject(app);

  

                  workSheet=null;

                  workBook=null;

                  app=null;

  

                  GCCollect();

              }

              catch(Exception e)

              {

                  throw e;

              }

              finally

              {

                  Process[] myProcesses;

                  DateTime startTime;

                  myProcesses = ProcessGetProcessesByName(Excel);

  

                  //得不到Excel進程ID暫時只能判斷進程啟動時間

                  foreach(Process myProcess in myProcesses)

                  {

                      startTime = myProcessStartTime;

  

                      if(startTime > beforeTime && startTime < afterTime)

                      {

                          myProcessKill();

                      }

                  }

              }

  

          }

  

  

          /**//// <summary>

          /// 獲取WorkSheet數量

          /// </summary>

          /// <param name=rowCount>記錄總行數</param>

          /// <param name=rows>每WorkSheet行數</param>

          private int GetSheetCount(int rowCountint rows)

          {

              int n = rowCount % rows;        //余數

  

              if(n == )

                  return rowCount / rows;

              else

                  return ConvertToInt(rowCount / rows) + ;

          }

  

  

          /**//// <summary>

          /// 將二維數組數據寫入Excel文件(套用模板並分頁)

          /// </summary>

          /// <param name=arr>二維數組</param>

          /// <param name=rows>每個WorkSheet寫入多少行數據</param>

          /// <param name=top>行索引</param>

          /// <param name=left>列索引</param>

          /// <param name=sheetPrefixName>WorkSheet前綴名比如前綴名為Sheet那麼WorkSheet名稱依次為SheetSheet</param>

          public void ArrayToExcel(string[] arrint rowsint topint leftstring sheetPrefixName)

          {

              int rowCount = arrGetLength();        //二維數組行數(一維長度)

              int colCount = arrGetLength();    //二維數據列數(二維長度)

              int sheetCount = thisGetSheetCount(rowCountrows);    //WorkSheet個數

              DateTime beforeTime;

              DateTime afterTime;

  

              if(sheetPrefixName == null || sheetPrefixNameTrim() == )

                  sheetPrefixName = Sheet;

  

              //創建一個Application對象並使其可見

              beforeTime = DateTimeNow;

              ExcelApplication app = new ExcelApplicationClass();

              appVisible = true;

              afterTime = DateTimeNow;

  

              //打開模板文件得到WorkBook對象

              ExcelWorkbook workBook = appWorkbooksOpen(templetFilemissingmissingmissingmissingmissing

                  missingmissingmissingmissingmissingmissingmissing);

  

              //得到WorkSheet對象

              ExcelWorksheet workSheet = (ExcelWorksheet)workBookSheetsget_Item();

  

              //復制sheetCount個WorkSheet對象

              for(int i=;i<sheetCount;i++)

              {

                  ((ExcelWorksheet)workBookWorksheetsget_Item(i))Copy(missingworkBookWorksheets[i]);

              }

  

              將二維數組數據寫入Excel#region 將二維數組數據寫入Excel

              for(int i=;i<=sheetCount;i++)

              {

                  int startRow = (i ) * rows;        //記錄起始行索引

                  int endRow = i * rows;            //記錄結束行索引

  

                  //若是最後一個WorkSheet那麼記錄結束行索引為源DataTable行數

                  if(i == sheetCount)

                      endRow = rowCount;

  

                  //獲取要寫入數據的WorkSheet對象並重命名

                  ExcelWorksheet sheet = (ExcelWorksheet)workBookWorksheetsget_Item(i);

                  sheetName = sheetPrefixName + + iToString();

  

                  //將二維數組中的數據寫入WorkSheet

                  for(int j=;j<endRowstartRow;j++)

                  {

                      for(int k=;k<colCount;k++)

                      {

                          sheetCells[top + jleft + k] = arr[startRow + jk];

                      }

                  }

  

                  ExcelTextBox txtAuthor = (ExcelTextBox)sheetTextBoxes(txtAuthor);

                  ExcelTextBox txtDate = (ExcelTextBox)sheetTextBoxes(txtDate);

                  ExcelTextBox txtVersion = (ExcelTextBox)sheetTextBoxes(txtVersion);

  

                  txtAuthorText = KLYNET的Blog;

                  txtDateText = DateTimeNowToShortDateString();

                  txtVersionText = ;

              }

              #endregion

  

              //輸出Excel文件並退出

              try

              {

                  workBookSaveAs(outputFilemissingmissingmissingmissingmissingExcelXlSaveAsAccessModexlExclusivemissingmissingmissingmissing);

                  workBookClose(nullnullnull);

                  appWorkbooksClose();

                  appApplicationQuit();

                  appQuit();

  

                  SystemRuntimeInteropServicesMarshalReleaseComObject(workSheet);

                  SystemRuntimeInteropServicesMarshalReleaseComObject(workBook);

                  SystemRuntimeInteropServicesMarshalReleaseComObject(app);

  

                  workSheet=null;

                  workBook=null;

                  app=null;

  

                  GCCollect();

              }

              catch(Exception e)

              {

                  throw e;

              }

              finally

              {

                  Process[] myProcesses;

                  DateTime startTime;

                  myProcesses = ProcessGetProcessesByName(Excel);

  

                  //得不到Excel進程ID暫時只能判斷進程啟動時間

                  foreach(Process myProcess in myProcesses)

                  {

                      startTime = myProcessStartTime;

  

                      if(startTime > beforeTime && startTime < afterTime)

                      {

                          myProcessKill();

                      }

                  }

              }

  

          }

      }

  }


From:http://tw.wingwit.com/Article/program/net/201311/12695.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.