需添加項目引用：
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
///
/// 構(gòu)造函數(shù)，將一個已有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;
//創(chuàng)建一個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 插入報表參數(shù)
///
/// 錄入報表的參數(shù)（TJ統(tǒng)計用）
///
///
填報單位
///
年月
///
填報人
///
填報日期
///
插入數(shù)據(jù)的單元格
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+" 聯(lián)系電話： 報送時間："+dateCreated;
//sheet.get_Range("I8", missing).Value2 = "填報日期："+dateCreated;
}
///
/// 錄入報表的參數(shù)（JH計劃用）
///
///
標題
public void InsertArgsJH(string name)
{
sheet.get_Range("A1", missing).Value2 = name;
}
///
/// 錄入報表的參數(shù)（JH計劃用）
///
///
標題
///
年份
public void InsertArgsJH(string name, string year)
{
sheet.get_Range("A1", missing).Value2 = name;
sheet.get_Range("D2", missing).Value2 = year;
}
///
/// 錄入報表的參數(shù)（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;
}
///
/// 錄入報表的參數(shù)（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數(shù)據(jù)導出到Excel（可動態(tài)插入行）
///
///
DataTable
///
插入行的索引
///
插入列的索引
public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex)
{
int rowCount = dt.Rows.Count; //DataTable行數(shù)
int colCount = dt.Columns.Count; //DataTable列數(shù)
int iRow;
int iCol;
//將數(shù)據(jù)導出到相應的單元格
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數(shù)據(jù)導出到Excel（可動態(tài)插入行）
///
///
DataTable
///
插入數(shù)據(jù)的起始單元格
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行數(shù)
int colCount = dt.Columns.Count; //DataTable列數(shù)
int iRow;
int iCol;
//利用二維數(shù)組批量寫入
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數(shù)據(jù)導出到Excel（固定）
///
///
DataTable
///
插入數(shù)據(jù)的起始單元格
public void DataTableToExcel2(System.Data.DataTable dt, string cellID)
{
int rowCount = dt.Rows.Count; //DataTable行數(shù)
int colCount = dt.Columns.Count; //DataTable列數(shù)
int iRow;
int iCol;
//利用二維數(shù)組批量寫入
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
///
/// 最后調(diào)用，釋放相關資源，完成
///
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();
}
}
///
/// 在工作表中插入行，并調(diào)整其他行以留出空間
///
///
當前工作表
///
欲插入的行索引
private void InsertRows(Excel.Worksheet sheet, int rowIndex)
{
range = (Excel.Range)sheet.Rows[rowIndex, missing];
//object Range.Insert(object shift, object copyorigin);
//shift: Variant類型，可選。指定單元格的調(diào)整方式?？梢詾橄铝?XlInsertShiftDirection 常量之一：
//xlShiftToRight 或 xlShiftDown。如果省略該參數(shù)，Microsoft Excel 將根據(jù)區(qū)域形狀確定調(diào)整方式。
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，并且釋放調(diào)用的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();
}
///
/// 結(jié)束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();
}
}
}
}
}
