C#中通过Excel模板导出数据

一、实现效果

1.1、编写特定格式的Excel模板

 1.2、调用Excel模板导出数据

二、实现核心

        #region   通过模板导出Excel文件

        /// <summary>
        /// 通过模板导出Excel文件
        /// </summary>
        /// <param name="dt">需要导出的DataTable表</param>
        /// <param name="templatePath">使用的的Excel模板路径和名称</param>
        /// <param name="outFilePath">导出的Excel模板路径和名称</param>
        /// <param name="sheetName">sheetName</param>
        /// <param name="startRowIndex">开始添加数据行的索引</param>
        public void ExcelExportByTemplate(DataTable dt, string templatePath, string outFilePath, string sheetName,
            int startRowIndex = 0)
        {
            try
            {
                IWorkbook workBook;
                var outPath = "";
                var fileExt = Path.GetExtension(outFilePath);
                if (fileExt == ".xlsx")
                {
                    outPath = outFilePath.Substring(0, outFilePath.Length - 5) + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
                    File.Copy(templatePath, outPath, true);
                    using (var fs = File.Open(outPath, FileMode.Open, FileAccess.Read, FileShare.Write))
                    {
                        workBook = new XSSFWorkbook(fs);
                    }
                }
                else if (fileExt == ".xls")
                {
                    outPath = outFilePath.Substring(0, outFilePath.Length - 4) + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
                    File.Copy(templatePath, outPath, true);
                    using (var fs = File.Open(outPath, FileMode.Open, FileAccess.Read, FileShare.Write))
                    {
                        workBook = new HSSFWorkbook(fs);
                    }
                }
                else
                {
                    workBook = null;
                }

                if (workBook != null)
                {
                    var style = workBook.CreateCellStyle();
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    var sheet = string.IsNullOrEmpty(sheetName) ? workBook.GetSheetAt(0) : workBook.GetSheet(sheetName);

                    for (var i = startRowIndex; i < dt.Rows.Count; i++)
                    {
                        var row = sheet.CreateRow(i + 1);
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            var cell = row.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            cell.CellStyle = style;
                        }
                    }
                }

                var file = new FileStream(outPath, FileMode.Create);

                workBook?.Write(file);

                file.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception:" + ex.Message);
            }
        }

        #endregion 

三、使用方法

        //导出数据到Excel模板文件中
        private void simpleButton5_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)gridControl1.DataSource;

            ExcelExportByTemplate(dt, @"C:\Users\Administrator\Desktop\导出Excel\导出数据模板.xlsx",
                @"C:\Users\Administrator\Desktop\导出Excel\通过模板导出的数据.xlsx","Sheet1",1);
        }