使用模板导出Excel【原创】

　　

C#-Doce

 

using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Reflection;

public class ExcelHelper
{
    protected string templetFile = null;
    protected string outputFile = null;
    protected object missing = Missing.Value;

    /// <summary>
    /// 构造函数，需指定模板文件和输出文件完整路径
    /// </summary>
    /// <param name="templetFilePath">Excel模板文件路径</param>
    /// <param name="outputFilePath">输出Excel文件路径</param>
    public ExcelHelper(string templetFilePath, string outputFilePath)
    {
        if (templetFilePath == null)
            throw new Exception("Excel模板文件路径不能为空！");

        if (outputFilePath == null)
            throw new Exception("输出Excel文件路径不能为空！");

        if (!File.Exists(templetFilePath))
            throw new Exception("指定路径的Excel模板文件不存在！");

        this.templetFile = templetFilePath;
        this.outputFile = outputFilePath;

    }

    /// <summary>
    /// 将DataTable数据写入Excel文件（套用模板并分页）
    /// </summary>
    /// <param name="dt">DataSet集合，里面的表个数必须和sheetName数组相等</param>
    /// <param name="rows">每个WorkSheet写入多少行数据</param>
    /// <param name="top">行索引</param>
    /// <param name="left">列索引</param>
    /// <param name="isRrwriteColumn">是否重写列名</param>
    /// <param name="sheetName">WorkSheet名字,必须和DataSet集合里面的DataTable个数对应</param>
    public void DataTableToExcel(DataSet ds, int rows, int top, int left, bool isRrwriteColumn, params string[] sheetName)
    {
        int tableCount = ds.Tables.Count;
        if (tableCount != sheetName.Length)
        {
            throw new Exception("DataTable个数和WorkSheet不匹配");
        }
        DateTime beforeTime;
        DateTime afterTime;

        //创建一个Application对象
        beforeTime = DateTime.Now;
        Excel.Application app = new Excel.ApplicationClass();
        app.Visible = false;
        afterTime = DateTime.Now;

        //打开模板文件
        Excel.Workbook workBook = app.Workbooks._Open(templetFile, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing);

        Excel.Worksheet sheet = null;
        for (int i = 1; i < tableCount; i++)
        {
            ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
        }

        #region 将源DataTable数据写入Excel
        DataTable dt = null;
        for (int i = 1; i <= tableCount; i++)
        {
            dt = ds.Tables[i - 1];
            int iRows = dt.Rows.Count, iCols = dt.Columns.Count;
            //获取要写入数据的WorkSheet对象，并重命名
            sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
            sheet.Name = sheetName[i - 1];
            if (isRrwriteColumn)
            {
                for (int k = 0; k < iCols; k++)
                {
                    sheet.Cells[1, left + k] = dt.Columns[k].ColumnName;
                }
            }

            object[,] dataArray = new object[iRows, iCols];
            for (int j = 0; j < iRows; j++)
            {
                for (int k = 0; k < iCols; k++)
                {
                    dataArray[j, k] = dt.Rows[j][k];
                }
            }
            //将dt中的数据写入WorkSheet
            sheet.get_Range(sheet.Cells[top, left], sheet.Cells[iRows, iCols]).Value2 = dataArray;
        }
        #endregion

        #region 输出Excel文件并退出
        try
        {
            workBook.SaveCopyAs(outputFile);
            //workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive,
            //    missing, missing, missing, missing, missing);
            workBook.Close(false, null, null);
            app.Workbooks.Close();
            app.Application.Quit();
            app.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

            sheet = null;
            workBook = null;
            app = null;

            GC.Collect();
        }
        catch (Exception e)
        {
            //throw e;
        }
        finally
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;

                if (startTime > beforeTime && startTime < afterTime)
                {
                    myProcess.Kill();
                }
            }
        }
        #endregion
    }
}

部分代码参考自：http://www.cnblogs.com/lingyun_k/archive/2005/07/12/191740.aspx