C# 实现根据Excel模板批量导出Excel数据的方法

最近在开发绩效考核管理的功能中，接收到一个需求，根据Excel模板批量导出每个员工的绩效考核数据。由于页面批量导出比较麻烦，因此做了个后台程序批量导出。

原先思路是这样的：根据人力资源部提供的Excel表格，制作Excel模板（区分普通员工和管理人员）->以插入Sheet单元格的方式在Excel模板中赋值，生成Excel模板。下面是完整代码的具体实现，这里我采用了单例模式调用Excel读取。

///Using引用
using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel; 
//
namespace AttendanceView.Helpers
{
public class ExportByTemplete
    {
            public static ExportByTemplete mySingle = null;
            public static object obj = new object();

        public static ExportByTemplete getInstance()
        {
            //判断是否实例化过
            if (mySingle == null)
            {
                //进入lock
                lock (obj)
                {
                    //判断是否实例化过
                    if (mySingle == null)
                    {
                        mySingle = new ExportByTemplete();
                        // mySingle.Name = name;
                    }
                }
            }
            return mySingle;
        }
        

        /// <summary>
        /// 根据Templete模板生成Excel数据
        /// </summary>
        /// <param name="TemlpetePath"></param>
        /// <param name="CurrentPath"></param>
        /// <param name="mySheet"></param>
        public  void OperatorExcel(string TemlpetePath, string FileSavePath, Tuple<DataRow,DataRow[]> tuple,string Templete)
        {
            Microsoft.Office.Interop.Excel.Application excel; //声明excel对象
            excel = new Excel.ApplicationClass(); //创建对象实例,这时在系统进程中会多出一个excel进程
            try
            {
                object missing = System.Reflection.Missing.Value; //Missing 用于调用带默认参数的方法。
                object readOnly = true;
                excel.Visible = false; //是否显示excel文档
                                       //Open Original Excel File
                excel.Application.Workbooks.Open(TemlpetePath, missing, readOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                excel.Application.Workbooks.Add(true);
                _Workbook myBook; //声明Workbook类
                _Worksheet mySheet; //声明Worksheet类
                myBook = excel.Workbooks[1]; //获取excel程序的工作簿
                mySheet = (Worksheet)myBook.ActiveSheet; //获取Workbook的活动工作表（最上层的工作表）。
                this.SetCellValues(mySheet, tuple, Templete);
                //Save As  Path
                mySheet.SaveAs(FileSavePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //释放Excel对象,但在Asp.net Web程序中只有转向另一个页面的时候进程才结束
                //可以考虑使用KillExcelProcess()杀掉进程
                //ReleaseComObject 方法递减运行库可调用包装的引用计数。详细信息见MSDN
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                myBook.Close(null, null, null);
                excel.Workbooks.Close();
                mySheet = null;
                myBook = null;
                missing = null;
                excel.Quit();
                excel = null;
            }
            catch(Exception ex)
            {
                KillExcelProcess();
                throw ex;
            }
            finally
            {
                KillExcelProcess();  //可以把KillExcelProcess();放在该处从而杀掉Excel的进程
            }
        }
        private void KillExcelProcess()
        {
            System.Diagnostics.Process[] myProcesses;
            DateTime startTime;
            myProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (System.Diagnostics.Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;
                myProcess.Kill();
            }
        }

        private void SetCellValues(_Worksheet worksheet, Tuple<DataRow,DataRow[]> tuple,string Templete)
        {
            switch (Templete)
            {
                #region  普通员工
                case Helpers.Const.EmployeeTemplete:
                    worksheet.Cells[4, 3] = tuple.Item1["姓名"].ToString();
                    worksheet.Cells[5, 3] = tuple.Item1["入职日期"].ToString();
                    worksheet.Cells[4, 5] = tuple.Item1["部门"].ToString();
                    worksheet.Cells[5, 5] = tuple.Item1["考核周期"].ToString();
                    worksheet.Cells[4, 8] = tuple.Item1["岗位"].ToString();
                    worksheet.Cells[5, 8] = tuple.Item1["等级"].ToString();
                    for (int i = 1; i < tuple.Item2.Length; i++)
                    {
                        worksheet.Cells[8 + i, 2] = tuple.Item2[i]["Goals"].ToString();
                        worksheet.Cells[8 + i, 4] = tuple.Item2[i]["Measurement"].ToString();
                        worksheet.Cells[8 + i, 6] = tuple.Item2[i]["SelfAssesment"].ToString();
                        worksheet.Cells[8 + i, 8] = tuple.Item2[i]["MidYearReview"].ToString();
                        worksheet.Cells[8 + i, 10] = tuple.Item2[i]["ManagersSuggestion"].ToString();
                    }
                    worksheet.Cells[18, 8] = tuple.Item1["工作质量"].ToString();
                    worksheet.Cells[19, 8] = tuple.Item1["有效沟通"].ToString();
                    worksheet.Cells[20, 8] = tuple.Item1["团队合作"].ToString();
                    worksheet.Cells[21, 8] = tuple.Item1["创新"].ToString();
                    worksheet.Cells[22, 8] = tuple.Item1["客户导向"].ToString();
                    worksheet.Cells[23, 8] = tuple.Item1["产品知识"].ToString();
                    ///                    
                    worksheet.Cells[27, 6] = tuple.Item1["需要提升的地方"].ToString();
                    worksheet.Cells[28, 6] = tuple.Item1["职业发展"].ToString();
                    worksheet.Cells[29, 6] = tuple.Item1["感兴趣职业"].ToString();
                    worksheet.Cells[30, 6] = tuple.Item1["工作变更"].ToString();
                    worksheet.Cells[32, 2] = tuple.Item1["部门领导意见"].ToString();
                    break;
                #endregion
                #region 部门领导
                case Helpers.Const.ManagerTemplete:
                    worksheet.Cells[4, 3] =tuple.Item1["姓名"].ToString();
                    worksheet.Cells[5, 3] = tuple.Item1["入职日期"].ToString();
                    worksheet.Cells[4, 5] = tuple.Item1["部门"].ToString();
                    worksheet.Cells[5, 5] = tuple.Item1["考核周期"].ToString();
                    worksheet.Cells[4, 9] = tuple.Item1["岗位"].ToString();
                    worksheet.Cells[5, 9] = tuple.Item1["等级"].ToString();
                    for (int i = 0; i < tuple.Item2.Length; i++)
                    {
                        worksheet.Cells[9 + i, 2] = tuple.Item2[i]["Goals"].ToString();
                        worksheet.Cells[9 + i, 4] = tuple.Item2[i]["Measurement"].ToString();
                        worksheet.Cells[9 + i, 6] = tuple.Item2[i]["SelfAssesment"].ToString();
                        worksheet.Cells[9 + i, 8] = tuple.Item2[i]["MidYearReview"].ToString();
                        worksheet.Cells[9 + i, 10] = tuple.Item2[i]["ManagersSuggestion"].ToString();
                    }
                    worksheet.Cells[19, 9] =tuple.Item1["管理工作"].ToString();
                    worksheet.Cells[20, 9] =tuple.Item1["结果导向"].ToString();
                    worksheet.Cells[21, 9] =tuple.Item1["发展直接下属"].ToString();
                    worksheet.Cells[22, 9] =tuple.Item1["团队合作"].ToString();
                    worksheet.Cells[23, 9] =tuple.Item1["计划于组织"].ToString();
                    worksheet.Cells[24, 9] =tuple.Item1["领导力"].ToString();
                    ///                    
                    worksheet.Cells[28, 6] =tuple.Item1["需要提升的地方"].ToString();
                    worksheet.Cells[29, 6] =tuple.Item1["职业发展"].ToString();
                    worksheet.Cells[30, 6] =tuple.Item1["感兴趣职业"].ToString();
                    worksheet.Cells[31, 6] =tuple.Item1["工作变更"].ToString();
                    worksheet.Cells[33, 2] = tuple.Item1["部门领导意见"].ToString();
                    break;
                #endregion
                default:
                    break;
            }
        }
    }
}

为了演示效果，这里我们新建一个Winform页面进行查看效果。Winform界面如下所示，查出员工的绩效考核数据，然后执行导出功能，进行导出。下面会贴出相关代码。

运行完成之后会生成每个人的Excel数据，如下所示，生成的文件名为姓名+部门+岗位。后续如果代码优化的话，也可以对所有结果进行压缩，然后邮件发送给相关人员。这里我就不再相关代码了。

 

其中导出功能的代码如下所示。我这里实现的是，先把生成数据都放到临时目录Temp下面，然后从临时目录中移动到Export目录下面。待数据全部转换完成之后，直接打开Export目录可以查看生成的数据。由于公司绩效管理模板是保密的，因此这里就不和大家分享了。大家可以按照自己的模板，修改相关的模板插入的单元格的下标即可。

        private async   void BtnOutExcel_Click(object sender, EventArgs e)
        {
            string SavePath = Path.Combine(Application.StartupPath, "Export");
            string TempPath = Path.Combine(Application.StartupPath, "Temp");
            if (!Directory.Exists(SavePath))
            {
                Directory.CreateDirectory(SavePath);
            }
            if (!Directory.Exists(TempPath))
            {
                Directory.CreateDirectory(TempPath);
            }
            try
            {
                FrmTips.ShowTips(null,"温馨提示:后台正在导出中，请耐心等待。。。");
                DataTable data = (DataTable)Dgv.DataSource;
                if (data == null || data.Rows.Count < 1)
                {
                    FrmTips.ShowTipsWarning(null, "不能保存空数据");
                    return;
                }
                DataTable dataTable = lvElecCenterService.GetEmployeeDetail();
                ExportByTemplete exportByTemplete = new ExportByTemplete();

                foreach (DataRow dr in data.Rows)//循环遍历生成文件
                {
                    DataRow[] dataRows = dataTable.Select($@"EmployeeKPIID={(long)dr["EmployeeKPIID"]}");
                    Tuple<DataRow, DataRow[]> tuple = new Tuple<DataRow, DataRow[]>(dr, dataRows);
                    string TempeltePath = Path.Combine(Application.StartupPath, "Templete", Utils.Const.ManagerTemplete);
                    string SaveFilePath = Path.Combine(TempPath, $"{tuple.Item1["部门"]}-{tuple.Item1["姓名"]}-{Utils.Const.ManagerTemplete}");
                    if ((Boolean)dr["IsManager"])
                    {
                     await Task.Factory.StartNew(()=>ExportByTemplete.getInstance().OperatorExcel(TempeltePath, SaveFilePath, tuple, Utils.Const.ManagerTemplete));
                    }
                    else
                    {
                        TempeltePath = Path.Combine(Application.StartupPath, "Templete", Utils.Const.EmployeeTemplete);
                        SaveFilePath = Path.Combine(TempPath, $"{tuple.Item1["部门"]}-{tuple.Item1["姓名"]}-{Utils.Const.EmployeeTemplete}");
                        await Task.Factory.StartNew(() => ExportByTemplete.getInstance().OperatorExcel(TempeltePath, SaveFilePath, tuple, Utils.Const.EmployeeTemplete));
                    }
                }

            }
            catch (Exception ex)
            {
                FrmTips.ShowTipsWarning(null, ex.Message);
            }
            finally
            {
                Utils.FileOperator.MoveFolder(TempPath, SavePath);
                FrmTips.ShowTipsInfo(null, "温馨提示，数据已导出完成，正在打开。。。");
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.InitialDirectory = SavePath;
                openFileDialog.ShowDialog();
            }
        }

Excel模板的名称我们可以定义一个Const类进行存储，如下所示：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AttendanceView.Helpers
{
    public class Const
    {
        public const string EmployeeTemplete = "2020绩效考核表(员工).xlsx";  
        public const string ManagerTemplete = "2020绩效考核表(管理人员).xlsx";
    }
    
}