將(Oracle)資料庫表匯出到Excel，並組建檔案（C#實現）

將(Oracle)資料庫表匯出到Excel，並組建檔案（C#實現）

關鍵詞： C#,    ASP.NET,    Excel                                          

需添加項目引用：

1. .NET->System.Data.OracleClient.dll

2. COM->Microsoft Excel 11.0 Object Library

代碼如下：

using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Configuration;
using System.Collections;
using Excel;

namespace thscjy
{
 ///

 /// 套用模板輸出Excel，產生xls檔案和html檔案
 /// Author: Liu Wen
 /// Date Created: 2006-8
 ///
 public class ExportExcel
 {
  #region variable member 成員變數
  protected string templateFile = null;
  protected string excelFile = null;
  protected string htmlFile = null;
  protected object missing = Missing.Value;
  Excel.ApplicationClass app;
  Excel.Workbook book;
  Excel.Worksheet sheet;
  Excel.Range range;
  private DateTime beforeTime;  //Excel啟動之前時間
  private DateTime afterTime;  //Excel啟動之後時間
  //private int processID;
  #endregion

  ///

  /// 建構函式，將一個已有Excel活頁簿作為模板，並指定輸出路徑
  ///
  /// Excel模板檔案路徑

  /// Excel輸出檔案路徑

  /// Html輸出檔案路徑

  public ExportExcel(string templateFile, string excelFile, string htmlFile)
  {
   if (templateFile == null)
    throw new Exception("Excel模板檔案路徑不可為空！");

   if (excelFile == null)
    throw new Exception("Excel輸出檔案路徑不可為空！");

   if (htmlFile == null)
    throw new Exception("Html輸出檔案路徑不可為空！");

   if (!File.Exists(templateFile))
    throw new Exception("指定路徑的Excel模板檔案不存在！");

   this.templateFile = templateFile;
   this.excelFile = excelFile;
   this.htmlFile = htmlFile;

   //建立一個Application對象
   beforeTime = DateTime.Now;
   app = new ApplicationClass();
   //app.Visible = true;
   //processID = Process.GetCurrentProcess().Id;
   afterTime = DateTime.Now;

   //開啟模板檔案，得到WorkBook對象
   try
   {
    book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing,
     missing,missing,missing,missing,missing,missing,missing,missing,missing);
   }
   catch (Exception e)
   {
    throw e;
   }
   //得到WorkSheet對象
   sheet = (Excel.Worksheet)book.Sheets.get_Item(1);
  }

  #region 插入報表參數
  ///

  /// 錄入報表的參數（TJ統計用）
  ///
  /// 填報單位

  /// 年月

  /// 填報人

  /// 填報日期

  /// 插入資料的儲存格

  public void InsertArgs(string department, string date, string accountant, string dateCreated, string cellID)
  {
   sheet.get_Range("A3", missing).Value2 = "填報單位："+department;
   sheet.get_Range("D3", missing).Value2 = date;
   sheet.get_Range(cellID, missing).Value2 = "部門負責人：　　　　　　　填報人："+accountant+"　聯絡電話：                 報送時間："+dateCreated;
   //sheet.get_Range("I8", missing).Value2 = "填報日期："+dateCreated;
  }

  ///

  /// 錄入報表的參數（JH計劃用）
  ///
  /// 標題

  public void InsertArgsJH(string name)
  {
   sheet.get_Range("A1", missing).Value2 = name;
  }

  ///

  /// 錄入報表的參數（JH計劃用）
  ///
  /// 標題

  /// 年份

  public void InsertArgsJH(string name, string year)
  {
   sheet.get_Range("A1", missing).Value2 = name;
   sheet.get_Range("D2", missing).Value2 = year;
  }

  ///

  /// 錄入報表的參數（JH計劃用）
  ///
  /// 標題

  /// 部門

  /// “部門”儲存格ID

  /// 年份

  /// “年份”儲存格ID

  public void InsertArgsJH(string name, string department, string depCellId, string year, string yearCellId)
  {
   sheet.get_Range("A1", missing).Value2 = name;
   sheet.get_Range(depCellId, missing).Value2 = department;
   sheet.get_Range(yearCellId, missing).Value2 = year;
  }

  ///

  /// 錄入報表的參數（JH計劃用）
  ///
  /// 標題

  /// 注水

  /// 注氣

  /// 措施工作量

  public void InsertArgsJH(string name, string water, string gas, string workload)
  {
   sheet.get_Range("A1", missing).Value2 = name;
   sheet.get_Range("C2", missing).Value2 = water;
   sheet.get_Range("E2", missing).Value2 = gas;
   sheet.get_Range("G2", missing).Value2 = workload;
  }
  #endregion

  #region 匯出Excel方法

  ///

  /// 將DataTable資料匯出到Excel（可動態插入行）
  ///
  /// DataTable

  /// 插入行的索引

  /// 插入列的索引

  public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex)
  {
   int rowCount = dt.Rows.Count;  //DataTable行數
   int colCount = dt.Columns.Count; //DataTable列數
   int iRow;
   int iCol;

   //將資料匯出到相應的儲存格
   for (iRow = 0; iRow < rowCount; iRow++)
   {
    //插入新行
    this.InsertRows(sheet, iRow+rowIndex);
    //填充當前行
    for (iCol = 0; iCol < colCount; iCol++)
    {
     sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString();
    }
   }
   this.DeleteRows(sheet, rowCount+rowIndex); 

   //this.OutputFile();

   //Excel.QueryTables qts = sheet.QueryTables;
   //Excel.QueryTable qt = qts.Add(,,);
   //qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
   //qt.Refresh();
  }

  ///

  /// 將DataTable資料匯出到Excel（可動態插入行）
  ///
  /// DataTable

  /// 插入資料的起始儲存格

  public void DataTableToExcel(System.Data.DataTable dt, string cellID)
  {
   int rowIndex = sheet.get_Range(cellID, missing).Row;
   int colIndex = sheet.get_Range(cellID, missing).Column;
   int rowCount = dt.Rows.Count;  //DataTable行數
   int colCount = dt.Columns.Count; //DataTable列數
   int iRow;
   int iCol;

   //利用二維數組批量寫入
   string[,] array = new string[rowCount,colCount];
   for (iRow = 0; iRow < rowCount; iRow++)
   {
    for (iCol = 0; iCol < colCount; iCol++)
    {
     array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
    }
   }

   for (iRow = 0; iRow < rowCount; iRow++)
   {
    this.InsertRows(sheet, iRow+rowIndex);
   }
   this.DeleteRows(sheet, rowCount+rowIndex);

   range  = sheet.get_Range(cellID, missing);
   range = range.get_Resize(rowCount, colCount);
   range.Value2 = array;
  }

  ///

  /// 將DataTable資料匯出到Excel（固定）
  ///
  /// DataTable

  /// 插入資料的起始儲存格

  public void DataTableToExcel2(System.Data.DataTable dt, string cellID)
  {
   int rowCount = dt.Rows.Count;  //DataTable行數
   int colCount = dt.Columns.Count; //DataTable列數
   int iRow;
   int iCol;

   //利用二維數組批量寫入
   string[,] array = new string[rowCount,colCount];
   for (iRow = 0; iRow < rowCount; iRow++)
   {
    for (iCol = 0; iCol < colCount; iCol++)
    {
     array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
    }
   }

   range  = sheet.get_Range(cellID, missing);
   range = range.get_Resize(rowCount, colCount);
   range.Value2 = array;
  }
  #endregion

  ///

  /// 最後調用，釋放相關資源，完成
  ///
  public void Finalize()
  {
   this.OutputFile();
   GC.Collect();
   //this.KillExcelProcess();
  }

  ///

  /// 輸出產生的Excel, Html檔案
  ///
  private void OutputFile()
  {
   //如果檔案已存在，刪除，重建
   if (File.Exists(excelFile))
   {
    File.Delete(excelFile);
   }
   if (File.Exists(htmlFile))
   {
    File.Delete(htmlFile);
   }
   try
   {
    book.SaveAs(excelFile, missing, missing, missing, missing, missing,
     Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing);

    book.SaveAs(htmlFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing,
     Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
   }
   catch (Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  ///

  /// 在工作表中插入行，並調整其他行以留出空間
  ///
  /// 當前工作表

  /// 欲插入的行索引

  private void InsertRows(Excel.Worksheet sheet, int rowIndex)
  {
   range = (Excel.Range)sheet.Rows[rowIndex, missing];  
   //object Range.Insert(object shift, object copyorigin);
   //shift: Variant類型，可選。指定單元格的調整方式。可以為下列 XlInsertShiftDirection 常量之一：
   //xlShiftToRight 或 xlShiftDown。如果省略該參數，Microsoft Excel 將根據地區形狀確定調整方式。
   range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);  
  }

  ///

  /// 在工作表中刪除行
  ///
  /// 當前工作表

  /// 欲刪除的行索引

  private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
  {
   range = (Range)sheet.Rows[rowIndex, missing];
   range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
  }

  ///

  /// 退出Excel，並且釋放調用的COM資源
  ///
  private void Dispose()
  {
   book.Close(missing, missing, missing);
   app.Workbooks.Close();
   app.Quit();

   if (range != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
    range = null;
   }
   if (sheet != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
    sheet = null;
   }
   if (book != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
    book = null;
   }
   if (app != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
    app = null;
   }

   //System.GC.Collect();
   //GC.WaitForPendingFinalizers();
   //this.KillExcelProcess();
   //Process pro = Process.GetProcessById(processID);
   //pro.Kill();
  }

  ///

  /// 結束Excel進程
  ///
  private void KillExcelProcess()
  {
   DateTime startTime;
   Process[] processes = Process.GetProcessesByName("Excel");

   //得不到Excel進程ID，暫時只能判斷進程啟動時間
   foreach (Process process in processes)
   {
    startTime = process.StartTime;
    if(startTime > beforeTime && startTime < afterTime)
    {
     process.Kill();
    }
   }
  }

 }
}