ASP.NET数据库导入Excel格式化打印--ExcelHandler.cs

public abstract class ExcelHandler
    {
        private string templetFile = string.Empty;
        private string outputFile = string.Empty;
        private object missing = Missing.Value;
        private int rows = 10;
        private int left = 0;
        private int top = 0;
        private string sheetPrefixName = "Sheet";
        private System.Data.DataTable dt = new System.Data.DataTable();
        private Dictionary<string, string> variableParameters = new Dictionary<string, string>();
        private Dictionary<int[], string> cellParameters = new Dictionary<int[], string>();
        /// <summary>
        /// 每个WorkSheet写入多少行数据
        /// </summary>
        public int Rows
        {
            set { this.rows = value; }
            get { return this.rows; }
        }
        /// <summary>
        /// 行索引
        /// </summary>
        public int Top
        {
            set { this.top = value; }
            get { return this.top; }
        }
        /// <summary>
        /// 列索引
        /// </summary>
        public int Left
        {
            set { this.left = value; }
            get { return this.left; }
        }
        /// <summary>
        /// 列索引
        /// </summary>
        public string SheetPrefixName
        {
            set { this.sheetPrefixName = value; }
            get { return this.sheetPrefixName; }
        }
        /// <summary>
        /// 数据集
        /// </summary>
        public System.Data.DataTable Dt
        {
            set { this.dt = value; }
            get { return this.dt; }
        }
        /// <summary>
        /// 键表示excel变量的名称，值表示所赋予的值
        /// </summary>
        public Dictionary<string, string> VariableParameters
        {
            set { this.variableParameters = value; }
            get { return this.variableParameters; }
        }
        /// <summary>
        /// 键表示excel单元格的标示（1,2），值表示所赋予的值
        /// </summary>
        public Dictionary<int[], string> CellParameters
        {
            set { this.cellParameters = value; }
            get { return this.cellParameters; }
        }
        /// <summary>
        /// 构造函数，需指定模板文件和输出文件完整路径
        /// </summary>
        /// <param name="templetFilePath">Excel模板文件路径</param>
        /// <param name="outputFilePath">输出Excel文件路径</param>
        public ExcelHandler(string templetFilePath, string outputFilePath)
        {
            if (templetFilePath == string.Empty)
            {
                throw new Exception("Excel模板文件路径不能为空！");
            }
            if (outputFilePath == string.Empty)
            {
                throw new Exception("输出Excel文件路径不能为空！");
            }
            string outputFolder = outputFilePath.Substring(0, outputFilePath.LastIndexOf("//"));
            if (!Directory.Exists(outputFolder))
            {
                Directory.CreateDirectory(outputFolder);
            }
            if (!File.Exists(templetFilePath))
            {
                throw new Exception("指定路径的Excel模板文件不存在！");
            }
            this.templetFile = templetFilePath;
            this.outputFile = outputFilePath;
        }
        /// <summary>
        /// 写入Excel文件（套用模板并分页）
        /// </summary>
        public void DataTableToExcel()
        {
            int rowCount = dt.Rows.Count;
            int sheetCount = this.GetSheetCount(rowCount);
            DateTime beforeTime = DateTime.Now;
            Application app = new ApplicationClass();
            app.Visible = false;
            DateTime afterTime = DateTime.Now;
            Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Worksheet workSheet = (Worksheet)workBook.Sheets.get_Item(1);
            for (int i = 1; i < sheetCount; i++)
            {
                workSheet.Copy(missing, workBook.Worksheets[i]);
            }
            FillData(workBook, sheetCount);
            try
            {
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                Marshal.ReleaseComObject(workSheet);
                Marshal.ReleaseComObject(workBook);
                Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook = null;
                app = null;

                GC.Collect();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                DateTime startTime;
                Process[] myProcesses = Process.GetProcessesByName("Excel");
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;
                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }
        }
        /// <summary>
        /// 获取WorkSheet数量
        /// </summary>
        /// <param name="rowCount">记录总行数</param>
        private int GetSheetCount(int rowCount)
        {
            int n = rowCount % this.rows;
            if (n == 0)
            {
                return rowCount / this.rows;
            }
            else
            {
                return Convert.ToInt32(rowCount / this.rows) + 1;
            }
        }
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="workBook">Excel对象</param>
        /// <param name="sheetCount">Worksheet数量</param>
        protected abstract void FillData(Workbook workBook, int sheetCount);
        /// <summary>
        /// 写入变量的值
        /// </summary>
        /// <param name="sheet">表格</param>
        protected void SetVariableParameters(Worksheet sheet)
        {
            foreach (KeyValuePair<string, string> variableParameter in variableParameters)
            {
                ((TextBox)sheet.TextBoxes(variableParameter.Key)).Text = variableParameter.Value;
            }
        }
        /// <summary>
        /// 写入单元格的值
        /// </summary>
        /// <param name="sheet">表格</param>
        protected void SetCellParameters(Worksheet sheet)
        {
            foreach (KeyValuePair<int[], string> cellParameter in cellParameters)
            {
                sheet.Cells[cellParameter.Key[0], cellParameter.Key[1]] = cellParameter.Value;
            }
        }
    }