C# 调用模板将数据写入excel中

            在网上看到了一个操作Excel的类，于是保存下来供以后参考。 不过用这种方法操作Excel缺点是当数据量很大时，速度会很慢。

    这个类中可能还有很多需要修改的地方，需要用的朋友可以自己修改一下。

 

 

using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;

namespace ExcelHelperTest
{
    /// <summary>
    /// 功能说明：套用模板输出Excel，并对数据进行分页
    /// </summary>
    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">DataTable</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2”</param>
        public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
        {
            int rowCount = dt.Rows.Count;        //源DataTable行数
            int colCount = dt.Columns.Count;    //源DataTable列数
            int sheetCount = this.GetSheetCount(rowCount,rows);    //WorkSheet个数
            DateTime beforeTime;    
            DateTime afterTime;
            
            if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
                sheetPrefixName = "Sheet";

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime = DateTime.Now;

            //打开模板文件，得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
                                missing,missing,missing,missing,missing,missing,missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for(int i=1;i<sheetCount;i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
            }

            将源DataTable数据写入Excel#region 将源DataTable数据写入Excel
            for(int i=1;i<=sheetCount;i++)
            {
                int startRow = (i - 1) * rows;        //记录起始行索引
                int endRow = i * rows;            //记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if(i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将dt中的数据写入WorkSheet
                for(int j=0;j<endRow-startRow;j++)
                {
                    for(int k=0;k<colCount;k++)
                   {
                       sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
                    }
                }

                //写文本框数据
                Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
                Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
                Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");

                txtAuthor.Text = "KLY.NET的Blog";
                txtDate.Text = DateTime.Now.ToShortDateString();
                txtVersion.Text = "1.0.0.0";
            }
            #endregion

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

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

                workSheet=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();
                    }
                }
            }
            
        }

         <summary>
        /// 获取WorkSheet数量
        /// </summary>
        /// <param name="rowCount">记录总行数</param>
        /// <param name="rows">每WorkSheet行数</param>
        private int GetSheetCount(int rowCount,int rows)
        {
            int n = rowCount % rows;        //余数

            if(n == 0)
                return rowCount / rows;
            else
                return Convert.ToInt32(rowCount / rows) + 1;
        }

         <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名称依次为“Sheet-1，Sheet-2”</param>
        public void ArrayToExcel(string[,] arr,int rows,int top,int left,string sheetPrefixName)
        {
            int rowCount = arr.GetLength(0);        //二维数组行数（一维长度）
            int colCount = arr.GetLength(1);    //二维数据列数（二维长度）
            int sheetCount = this.GetSheetCount(rowCount,rows);    //WorkSheet个数
            DateTime beforeTime;    
            DateTime afterTime;
            
            if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
                sheetPrefixName = "Sheet";

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime = DateTime.Now;

            //打开模板文件，得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
                missing,missing,missing,missing,missing,missing,missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for(int i=1;i<sheetCount;i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
            }

            将二维数组数据写入Excel#region 将二维数组数据写入Excel
            for(int i=1;i<=sheetCount;i++)
            {
                int startRow = (i - 1) * rows;        //记录起始行索引
                int endRow = i * rows;            //记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if(i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将二维数组中的数据写入WorkSheet
                for(int j=0;j<endRow-startRow;j++)
                {
                    for(int k=0;k<colCount;k++)
                    {
                        sheet.Cells[top + j,left + k] = arr[startRow + j,k];
                    }
                }

                Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
                Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
                Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");

                txtAuthor.Text = "KLY.NET的Blog";
                txtDate.Text = DateTime.Now.ToShortDateString();
                txtVersion.Text = "1.0.0.0";
            }
            #endregion

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

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

                workSheet=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();
                    }
                }
            }
            
        }
    }