c# NPOI按模板导出

公用方法：

namespace CCCC.THE.Utility.NPOI
{
    public struct SimpleCell
    {
        /// <summary>
        /// 文本
        /// </summary>
        public string T { get; set; }
        /// <summary>
        /// colspan
        /// </summary>
        public int C { get; set; }
        /// <summary>
        /// rowspan
        /// </summary>
        public int R { get; set; }
        /// <summary>
        /// 样式 文本颜色，背景颜色，字体是否加粗,对齐,宽度
        /// </summary>
        public string S { get; set; }
        /// <summary>
        /// 备注
        /// </summary>
        public string RK { get; set; }
    }
    public struct SimpleChart
    {
        /// <summary>
        /// 标题
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 类型 chart类型  bar和line
        /// </summary>
        public string Type { get; set; }
        /// <summary>
        /// 数据起始单元格
        /// </summary>
        public int Col1 { get; set; }
        /// <summary>
        /// 数据起始行
        /// </summary>
        public int Row1 { get; set; }
        /// <summary>
        /// 数据结束单元格
        /// </summary>
        public int Col2 { get; set; }
        /// <summary>
        /// 数据结束行
        /// </summary>
        public int Row2 { get; set; }
        /// <summary>
        /// 是否应用右边Y轴，默认左边Y轴
        /// </summary>
        public bool RightY { get; set; }
    }
    public class SimpleTable
    {
        /// <summary>
        /// 行数据如
        /// </summary>
        public List<List<SimpleCell>> Rows { get; set; }
        /// <summary>
        /// 颜色
        /// </summary>
        public List<string> Colors { get; set; }
        /// <summary>
        /// 样式
        /// </summary>
        public List<string> Styles { get; set; }
        /// <summary>
        /// 冻结的行 只考虑冻结表头
        /// </summary>
        public int FRow { get; set; }
        /// <summary>
        /// 冻结的列
        /// </summary>
        public int FCol { get; set; }
        /// <summary>
        /// 图表信息
        /// </summary>
        public List<SimpleChart> Chart { get; set; }
        /// <summary>
        /// X轴数据起始单元格
        /// </summary>
        public int Col1 { get; set; }
        /// <summary>
        /// X轴数据起始行
        /// </summary>
        public int Row1 { get; set; }
        /// <summary>
        /// X轴数据结束单元格
        /// </summary>
        public int Col2 { get; set; }
        /// <summary>
        /// X轴数据结束行
        /// </summary>
        public int Row2 { get; set; }
        /// <summary>
        /// 是否设置右边Y轴
        /// </summary>
        public bool RightY { get; set; }
        /// <summary>
        /// 是否只读
        /// </summary>
        public bool IsReadOnly { get; set; }
        public string PWD { get; set; }
    }
    public struct CellStyleWrap
    {
        public int? Width { get; set; }
        public ICellStyle CellStyle { get; set; }
        public CellStyleWrap(int? w, ICellStyle cs)
            : this()
        {
            this.Width = w;
            this.CellStyle = cs;
        }
    }
    public class ExcelHelper
    {
        void CreateChart(ISheet sheet, int col1, int col2, int row1, int row2, SimpleTable st)
        {
            IDrawing drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col2, row2);
            var chart = drawing.CreateChart(anchor) as XSSFChart;
            //生成图例
            var legend = chart.GetOrCreateLegend();
            //图例位置
            legend.Position = LegendPosition.Top;
            // X轴.
            var bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
            //Y轴
            IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
            leftAxis.Crosses = (AxisCrosses.AutoZero);
            //暂时不支持双y轴
            IValueAxis rigthAxis = leftAxis;
            //if (st.RightY)
            //{
            //    rigthAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Right);
            //    rigthAxis.Crosses = (AxisCrosses.AutoZero);
            //}
            //x轴
            IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(st.Row1, st.Row2, st.Col1, st.Col2));
            IBarChartData<double, double> bardata = null;
            ILineChartData<double, double> linedata = null;
            IScatterChartData<double, double> scatterdata = null;
            foreach (SimpleChart sc in st.Chart)
            {
                //图表
                var data = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(sc.Row1, sc.Row2, sc.Col1, sc.Col1));
                SetDoubleData(sheet, sc.Col1, sc.Col2, sc.Row1, sc.Row2);
                switch (sc.Type)
                {
                    case "line":
                        if (linedata == null)
                            linedata = chart.ChartDataFactory.CreateLineChartData<double, double>();
                        var sline = linedata.AddSeries(xs, data);
                        sline.SetTitle(sc.Title);
                        break;
                    case "bar":
                        if (bardata == null)
                            bardata = chart.ChartDataFactory.CreateBarChartData<double, double>();
                        var sbar = bardata.AddSeries(xs, data);
                        sbar.SetTitle(sc.Title);
                        break;
                    case "scatter":
                        if (scatterdata == null)
                            scatterdata = chart.ChartDataFactory.CreateScatterChartData<double, double>();
                        var stline = scatterdata.AddSeries(xs, data);
                        stline.SetTitle(sc.Title);
                        break;
                    default:
                        throw new Exception("暂不支持【" + sc.Type + "】类型的图表,只支持line、bar、scatter！");
                }
            }
            if (bardata != null)
                chart.Plot(bardata, bottomAxis, leftAxis);
            if (linedata != null)
                chart.Plot(linedata, bottomAxis, leftAxis);
            if (scatterdata != null)
                chart.Plot(scatterdata, bottomAxis, leftAxis);
        }
        public void SetDoubleData(ISheet sheet, int col1, int col2, int row1, int row2)
        {
            while (row1 <= row2)
            {
                IRow row = sheet.GetRow(row1);
                if (row == null)
                    continue;
                int begcol = col1;
                while (begcol <= col2)
                {
                    ICell cell = row.GetCell(begcol);
                    if (cell.CellType == CellType.String)
                    {
                        var value = cell.StringCellValue;
                        if (!string.IsNullOrWhiteSpace(value))
                        {
                            value = value.Replace(",", "").Trim();
                            double dbval;
                            if (double.TryParse(value, out dbval))
                            {
                                cell.SetCellType(CellType.Numeric);
                                cell.SetCellValue(dbval);
                            }
                        }
                    }
                    begcol += 1;
                }
                row1 += 1;
            }
        }
        private void aa(List<short> listcolor, short color)
        {
            if (color > PaletteRecord.STANDARD_PALETTE_SIZE)
                return;
            if (listcolor.IndexOf(color) < 0)
            {
                listcolor.Add(color);
            }
        }
        private Dictionary<string, short> CreateColor(HSSFWorkbook hssfWorkbook, List<string> colors)
        {
            Dictionary<string, short> dictret = new Dictionary<string, short>();
            HSSFPalette palette = hssfWorkbook.GetCustomPalette();
            List<short> listcolor = new List<short>();
            short index = 0;
            IFont existsfont;
            while (index < hssfWorkbook.NumberOfFonts)
            {
                existsfont = hssfWorkbook.GetFontAt(index);
                aa(listcolor, existsfont.Color);
                index += 1;
            }
            index = 0;
            ICellStyle existscs;
            while (index < hssfWorkbook.NumCellStyles)
            {
                existscs = hssfWorkbook.GetCellStyleAt(index);
                aa(listcolor, existscs.FillBackgroundColor);
                aa(listcolor, existscs.FillForegroundColor);
                aa(listcolor, existscs.BottomBorderColor);
                aa(listcolor, existscs.TopBorderColor);
                aa(listcolor, existscs.LeftBorderColor);
                aa(listcolor, existscs.RightBorderColor);
                index += 1;
            }
            for (int i = 0; i < colors.Count; i++)
            {
                string color = colors[i];
                string[] colorstr = color.Replace("rgba(", "").Replace("rgb(", "").Replace(")", "").Split(',');
                byte r = Convert.ToByte(colorstr[0].Trim()), g = Convert.ToByte(colorstr[1].Trim()), b = Convert.ToByte(colorstr[2].Trim());
                HSSFColor hc = palette.FindColor(r, g, b);
                if (hc != null)
                {
                    dictret.Add(color, hc.Indexed);
                    listcolor.Add(hc.Indexed);
                    continue;
                }
                short colorindex = PaletteRecord.FIRST_COLOR_INDEX;
                short endcolorindex = PaletteRecord.STANDARD_PALETTE_SIZE;
                while (colorindex < endcolorindex)
                {
                    if (listcolor.IndexOf(colorindex) < 0)
                    {
                        palette.SetColorAtIndex(colorindex, r, g, b);
                        dictret.Add(color, colorindex);
                        listcolor.Add(colorindex);
                        break;
                    }
                    colorindex += 1;
                }
            }
            return dictret;
        }
        private Dictionary<string, XSSFColor> CreateColor(XSSFWorkbook hssfWorkbook, List<string> colors)
        {
            Dictionary<string, XSSFColor> dictret = new Dictionary<string, XSSFColor>();
            List<short> listcolor = new List<short>();
            for (int i = 0; i < colors.Count; i++)
            {
                string color = colors[i];
                string[] colorstr = color.Replace("rgba(", "").Replace("rgb(", "").Replace(")", "").Split(',');
                byte r = Convert.ToByte(colorstr[0].Trim()), g = Convert.ToByte(colorstr[1].Trim()), b = Convert.ToByte(colorstr[2].Trim());
                dictret.Add(color, new XSSFColor(new byte[] { r, g, b }));
            }
            return dictret;
        }
        private Dictionary<string, CellStyleWrap> CreateCellStyle(IWorkbook book, ICellStyle df, List<string> style, Dictionary<string, short> diccolor)
        {
            Dictionary<string, CellStyleWrap> dicret = new Dictionary<string, CellStyleWrap>();
            foreach (string str in style)
            {
                string[] strs = str.Split(';');
                string fc = strs[0];
                string bc = strs[1];
                string fw = strs[2];
                string al = strs[3];
                string w = strs[4];
                if (fw == "bold")
                    fw = "400";
                ICellStyle cs;
                if (fc != "" || bc != "" || fw != "" || al != "")
                {
                    cs = book.CreateCellStyle();
                    cs.CloneStyleFrom(df);
                }
                else
                {
                    cs = df;
                }
                short color;
                if (bc != "" && diccolor.TryGetValue(bc, out color))
                {
                    cs.FillForegroundColor = color;
                    cs.FillPattern = FillPattern.SolidForeground;
                }
                color = 0;
                short fontcolor = 0;
                if (fw != "")
                    short.TryParse(fw, out color);
                if (fc != "")
                    diccolor.TryGetValue(fc, out fontcolor);
                if (color > 0 || fontcolor > 0)
                {
                    color *= 2;
                    IFont font = cs.GetFont(book);
                    IFont fontnew = book.CreateFont();
                    fontnew.Charset = font.Charset;
                    fontnew.Color = fontcolor == 0 ? font.Color : fontcolor;
                    fontnew.FontHeight = font.FontHeight;
                    fontnew.FontHeightInPoints = font.FontHeightInPoints;
                    fontnew.FontName = font.FontName;
                    fontnew.IsItalic = font.IsItalic;
                    fontnew.IsStrikeout = font.IsStrikeout;
                    fontnew.TypeOffset = font.TypeOffset;
                    fontnew.Underline = font.Underline;
                    fontnew.Boldweight = color == 0 ? font.Boldweight : color;
                    cs.SetFont(fontnew);
                }
                switch (al.ToLower())
                {
                    case "center":
                        cs.Alignment = HorizontalAlignment.Center;
                        break;
                    case "right":
                        cs.Alignment = HorizontalAlignment.Right;
                        break;
                }
                int? with = null;
                int widthtmp;
                if (int.TryParse(w, out widthtmp))
                    with = widthtmp;
                dicret.Add(str, new CellStyleWrap(with, cs));
            }
            return dicret;
        }
        private Dictionary<string, CellStyleWrap> CreateCellStyle(IWorkbook book, ICellStyle df, List<string> style, Dictionary<string, XSSFColor> diccolor)
        {
            Dictionary<string, CellStyleWrap> dicret = new Dictionary<string, CellStyleWrap>();
            foreach (string str in style)
            {
                string[] strs = str.Split(';');
                string fc = strs[0];
                string bc = strs[1];
                string fw = strs[2];
                string al = strs[3];
                string w = strs[4];
                if (fw == "bold")
                    fw = "400";
                ICellStyle cs;
                if (fc != "" || bc != "" || fw != "" || al != "")
                {
                    cs = book.CreateCellStyle();
                    cs.CloneStyleFrom(df);
                }
                else
                {
                    cs = df;
                }
                short color;
                XSSFColor bcolor;
                if (bc != "" && diccolor.TryGetValue(bc, out bcolor))
                {
                    ((XSSFCellStyle)cs).SetFillForegroundColor(bcolor);
                    cs.FillPattern = FillPattern.SolidForeground;
                }
                color = 0;
                XSSFColor fontcolor = null;
                bool hasfc = false;
                if (fw != "")
                    short.TryParse(fw, out color);
                if (fc != "")
                    hasfc = diccolor.TryGetValue(fc, out fontcolor);
                if (color > 0 || hasfc)
                {
                    color *= 2;
                    IFont font = cs.GetFont(book);
                    IFont fontnew = book.CreateFont();
                    fontnew.Charset = font.Charset;
                    if (!hasfc)
                        fontnew.Color = font.Color;
                    else
                        ((XSSFFont)fontnew).SetColor(fontcolor);
                    fontnew.FontHeight = font.FontHeight;
                    fontnew.FontHeightInPoints = font.FontHeightInPoints;
                    fontnew.FontName = font.FontName;
                    fontnew.IsItalic = font.IsItalic;
                    fontnew.IsStrikeout = font.IsStrikeout;
                    fontnew.TypeOffset = font.TypeOffset;
                    fontnew.Underline = font.Underline;
                    fontnew.Boldweight = color == 0 ? font.Boldweight : color;
                    cs.SetFont(fontnew);
                }
                switch (al.ToLower())
                {
                    case "center":
                        cs.Alignment = HorizontalAlignment.Center;
                        break;
                    case "right":
                        cs.Alignment = HorizontalAlignment.Right;
                        break;
                }
                int? with = null;
                int widthtmp;
                if (int.TryParse(w, out widthtmp))
                    with = widthtmp;
                dicret.Add(str, new CellStyleWrap(with, cs));
            }
            return dicret;
        }
        public void ToExcel(string data, Stream stream, bool is2003)
        {
            try
            {
                SimpleTable st = JsonConvert.DeserializeObject<SimpleTable>(data);
                IWorkbook book = null;
                if (is2003)
                {
                    book = new HSSFWorkbook();
                }
                else
                {
                    book = new XSSFWorkbook();
                }
                ICellStyle dfcs = book.CreateCellStyle();
                dfcs.Alignment = HorizontalAlignment.Left;
                dfcs.VerticalAlignment = VerticalAlignment.Center;
                dfcs.BorderBottom = BorderStyle.Thin;
                dfcs.BorderLeft = BorderStyle.Thin;
                dfcs.BorderRight = BorderStyle.Thin;
                dfcs.BorderTop = BorderStyle.Thin;
                if (st.IsReadOnly)
                    dfcs.IsLocked = true;
                Dictionary<string, CellStyleWrap> dicstyle = null;
                if (is2003)
                {
                    Dictionary<string, short> dicColor = CreateColor((HSSFWorkbook)book, st.Colors);
                    dicstyle = CreateCellStyle(book, dfcs, st.Styles, dicColor);
                }
                else
                {
                    Dictionary<string, XSSFColor> dicColor = CreateColor((XSSFWorkbook)book, st.Colors);
                    dicstyle = CreateCellStyle(book, dfcs, st.Styles, dicColor);
                }
                ISheet sheet = book.CreateSheet();
                IDrawing patr = sheet.CreateDrawingPatriarch();
                int rowindex = 0;
                foreach (List<SimpleCell> cells in st.Rows)
                {
                    IRow row = sheet.CreateRow(rowindex);
                    int cellindex = 0;
                    foreach (SimpleCell sc in cells)
                    {
                        ICell cell = row.CreateCell(cellindex);
                        if (!string.IsNullOrEmpty(sc.T))
                        {
                            cell.SetCellValue(sc.T);
                        }
                        CellStyleWrap csw = default(CellStyleWrap);
                        if (sc.S != null && dicstyle.TryGetValue(sc.S, out csw))
                        {
                            cell.CellStyle = csw.CellStyle;
                        }
                        else
                        {
                            cell.CellStyle = dfcs;
                        }
                        if (!string.IsNullOrEmpty(sc.RK))
                        {
                            IComment comment = null;
                            if (is2003)
                            {
                                comment = patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, cellindex, rowindex, cellindex + 2, rowindex + 4));
                                comment.String = new HSSFRichTextString(sc.RK);
                            }
                            else
                            {
                                comment = patr.CreateCellComment(new XSSFClientAnchor(255, 125, 1023, 150, cellindex, rowindex, cellindex + 2, rowindex + 4));
                                comment.String = new XSSFRichTextString(sc.RK);
                            }
                            cell.CellComment = comment;
                        }
                        if (sc.C > 0 || sc.R > 0)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex + (sc.R == 0 ? 0 : sc.R - 1), cellindex, cellindex + (sc.C == 0 ? 0 : sc.C - 1)));
                        }
                        cellindex += 1;
                    }
                    rowindex += 1;
                }
                int lastrowindex = rowindex;
                List<SimpleCell> rows = st.Rows[st.FRow];
                int index = 0;
                foreach (SimpleCell sc in rows)
                {
                    CellStyleWrap csw = default(CellStyleWrap);
                    if (sc.S != null && dicstyle.TryGetValue(sc.S, out csw) && csw.Width != null)
                    {
                        sheet.SetColumnWidth(index, csw.Width.Value * 256);
                    }
                    index += 1;
                }
                if (st.FCol > 0 || st.FRow > 0)
                {
                    sheet.CreateFreezePane(st.FCol, st.FRow);
                }
                if (st.Chart != null && st.Chart.Count > 0)
                {
                    CreateChart(sheet, 0, 0 + 10, lastrowindex + 3, lastrowindex + 3 + 20, st);
                }
                if (st.IsReadOnly)
                {
                    sheet.ProtectSheet(st.PWD);
                }
                book.Write(stream);
            }
            catch
            {
                throw;
            }
        }
        public void ToExcel(Stream stream, DataTable[] dts, NameValueCollection[] titles, bool is2003)
        {
            try
            {
                IWorkbook book = null;
                if (is2003)
                {
                    book = new HSSFWorkbook();
                }
                else
                {
                    book = new XSSFWorkbook();
                }
                ICellStyle dfcs = book.CreateCellStyle();
                dfcs.Alignment = HorizontalAlignment.Left;
                dfcs.VerticalAlignment = VerticalAlignment.Center;
                dfcs.BorderBottom = BorderStyle.Thin;
                dfcs.BorderLeft = BorderStyle.Thin;
                dfcs.BorderRight = BorderStyle.Thin;
                dfcs.BorderTop = BorderStyle.Thin;
                int i = 0;
                foreach (DataTable dt in dts)
                {
                    ISheet sheet = book.CreateSheet(dt.TableName);
                    NameValueCollection nvc = titles[i];
                    int rowindex = 0;
                    int cellindex = 0;
                    IRow row;
                    ICell cell;
                    row = sheet.CreateRow(rowindex);
                    foreach (string key in nvc)
                    {
                        cell = row.CreateCell(cellindex);
                        cell.CellStyle = dfcs;
                        cell.SetCellValue(nvc[key]);
                        cellindex += 1;
                    }
                    foreach (DataRow dr in dt.Rows)
                    {
                        rowindex += 1;
                        row = sheet.CreateRow(rowindex);
                        cellindex = 0;
                        foreach (string key in nvc)
                        {
                            cell = row.CreateCell(cellindex);
                            cell.CellStyle = dfcs;
                            cell.SetCellValue(dr[key].ToString());
                            cellindex += 1;
                        }
                    }
                    i += 1;
                }
                book.Write(stream);
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 绑定数据前缀,所有小写，默认$
        /// </summary>
        public string Prefix
        {
            get;
            set;
        }
        /// <summary>
        /// 列表开始前缀,,所有小写默认$list.
        /// </summary>
        public string BeginListPrefix
        {
            get;
            set;
        }
        /// <summary>
        /// 列表结束前缀,所有小写,默认$listend.
        /// </summary>
        public string EndListPrefix
        {
            get;
            set;
        }
        /// <summary>
        /// 自动行高前缀,所有小写 默认$auotheight,放到列后面
        /// </summary>
        public string AutoHeightPrefix
        {
            get;
            set;
        }
        //列表序列号字符
        public string ListSeqNoStr
        {
            get;
            set;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="prefix">绑定数据前缀，默认$</param>
        /// <param name="beglistprefix">列表开始前缀,默认$list.</param>
        /// <param name="endlistprefix">列表结束前缀,默认$listend.</param>
        public ExcelHelper(string prefix = "$", string beglistprefix = "$list.", string endlistprefix = "$listend.", string seqnostr = "listseqno", string autoheight = "$auotheight")
        {
            this.Prefix = prefix;
            this.BeginListPrefix = beglistprefix;
            this.EndListPrefix = endlistprefix;
            this.ListSeqNoStr = seqnostr;
            this.AutoHeightPrefix = autoheight;
        }
        /// <summary>
        /// 检查是不是2003之前的版本，根据后缀判断
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static bool Is2003(string file)
        {
            string ext = Path.GetExtension(file);
            return ext.ToLower() == ".xls";
        }
        /// <summary>
        /// 返回正确的sheet名称
        /// </summary>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static string GetSheetName(string sheetname)
        {
            sheetname = sheetname.Replace("//", "").Replace("\"", "").Replace("[", "").Replace("]", "");
            sheetname = sheetname.Replace("<", "").Replace(">", "").Replace("\r", "").Replace("\n", "");
            sheetname = sheetname.Replace("*", "").Replace("|", "").Replace(":", "");
            sheetname = sheetname.Replace("?", "").Replace("/", "");
            return sheetname;
        }
        /// <summary>
        /// 按模板导出数据，保存到流
        /// </summary>
        /// <param name="modalfilepath">模板路径</param>
        /// <param name="dicdata">数据</param>
        /// <param name="fncreate">根据数据建立sheet</param>
        /// <param name="ms">保存到流</param>
        /// <param name="acend">数据填充完成后执行的方法</param>
        public IWorkbook Export(string modalfilepath, Func<IWorkbook, List<ExcelHelperSheetInfo>> fncreate, Action<IWorkbook, ISheet, ExcelHelperSheetInfo> acend = null)
        {
            try
            {
                IWorkbook excel = null;
                using (FileStream fs = new FileStream(modalfilepath, FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    if (Is2003(modalfilepath))
                    {
                        excel = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        excel = new XSSFWorkbook(fs);
                    }
                    List<ExcelHelperSheetInfo> list = fncreate(excel);
                    foreach (ExcelHelperSheetInfo dr in list)
                    {
                        ISheet sheet = dr.ModelSheet;
                        int modelsheetindex = excel.GetSheetIndex(sheet);
                        foreach (object sheetData in dr.NewSheetData)
                        {
                            ISheet sheetnew = excel.CloneSheet(modelsheetindex);
                            ExprotExcel(sheetnew, sheetData);
                            if (acend != null)
                            {
                                acend(excel, sheetnew, dr);
                            }
                        }
                        excel.RemoveSheetAt(modelsheetindex);
                    }
                }
                return excel;
            }
            catch (Exception ex)
            {
                Logger.Write(typeof(ExcelHelper), ex);
                throw;
            }
        }
        /// <summary>
        /// 按模板导出单个sheet
        /// </summary>
        /// <param name="sheetnew"></param>
        /// <param name="dicdata"></param>
        public void ExprotExcel(ISheet sheetnew, object dicdata)
        {
            int maxrow = sheetnew.LastRowNum;
            int minrow = 0;
            int newrowno = 0;
            sheetnew.FitToPage = true;
            while (minrow <= maxrow)
            {
                IRow row = sheetnew.GetRow(minrow);
                if (row == null)
                {
                    minrow += 1;
                    continue;
                }
                int cellindex = 0;
                int maxcell = row.LastCellNum;
                bool islist = false, islistend = false;
                int listbegnum = 0;
                string listkey = "";
                List<string> listkeys = null;
                bool hassetdata = false;
                bool autoheight = false;
                while (cellindex <= maxcell)
                {
                    var modelcell = row.GetCell(cellindex);
                    if (modelcell == null)
                    {
                        cellindex += 1;
                        continue;
                    }
                    string cellvalue = modelcell.ToString().Trim();
                    string cellvaluelower = cellvalue.ToLower();
                    if (cellvaluelower.StartsWith(BeginListPrefix))
                    {
                        islist = true;
                        listbegnum = cellindex;
                        cellvalue = cellvalue.Substring(BeginListPrefix.Length);
                        listkey = cellvalue.Split(' ')[0];
                        listkeys = new List<string>();
                        listkeys.Add(cellvalue.Split(' ')[1]);
                    }
                    else if (islist)
                    {
                        if (cellvaluelower.StartsWith(EndListPrefix))
                        {
                            islistend = true;
                            cellvalue = cellvalue.Substring(EndListPrefix.Length);
                            listkeys.Add(cellvalue);
                        }
                        else if (cellvaluelower == AutoHeightPrefix)
                        {
                            autoheight = true;
                            row.RemoveCell(modelcell);
                        }
                        else if (cellvaluelower.StartsWith(Prefix))
                        {
                            listkeys.Add(cellvalue.Substring(Prefix.Length));
                        }
                        else if (!islistend)
                        {
                            listkeys.Add("");
                        }
                    }
                    else if (cellvaluelower == AutoHeightPrefix)
                    {
                        autoheight = true;
                        row.RemoveCell(modelcell);
                    }
                    else if (cellvaluelower.StartsWith(Prefix))
                    {
                        cellvalue = cellvalue.Substring(Prefix.Length);
                        SetCellValue(modelcell, cellvalue, dicdata);
                        hassetdata = true;
                    }
                    cellindex += 1;
                }
                if (islist)
                {
                    newrowno = SetList(sheetnew, row, minrow, listkey, listkeys, listbegnum, dicdata, ListSeqNoStr, autoheight);
                    minrow += newrowno == 0 ? 1 : newrowno;
                    maxrow += newrowno;
                }
                else
                {
                    if (hassetdata && autoheight)
                    {
                        SetRowHeight(row);
                    }
                    minrow += 1;
                }
            }
        }
        /// <summary>
        /// 解析字符串转换成对象字符串例如No.ID，若是错误返回error
        /// </summary>
        /// <param name="key"></param>
        /// <param name="dicdata"></param>
        /// <returns></returns>
        public static object GetDataByKey(string key, object dicdata)
        {
            string[] keys = key.Split('.');
            object ret = dicdata;
            try
            {
                foreach (string str in keys)
                {
                    if (ret == null)
                        return null;
                    if (ret is IDictionary)
                    {
                        ret = (ret as IDictionary)[str];
                        continue;
                    }
                    if (ret is DataRow)
                    {
                        ret = (ret as DataRow)[str];
                        continue;
                    }
                    if (ret is DataRowView)
                    {
                        ret = (ret as DataRowView)[str];
                        continue;
                    }
                    PropertyInfo pi = ret.GetType().GetProperty(str, BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                    if (pi == null)
                    {
                        throw new Exception(ret.GetType().FullName + "不包含属性:" + str);
                    }
                    if (!pi.CanRead)
                    {
                        throw new Exception(ret.GetType().FullName + "属性:" + str + "不能够读");
                    }
                    ret = pi.GetValue(ret);
                }
            }
            catch (Exception ex)
            {
                return "Error:" + ex.Message;
            }
            return ret;
        }
        /// <summary>
        /// 按模板设置单元格数据
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="key"></param>
        /// <param name="dicdata"></param>
        public static void SetCellValue(ICell cell, string key, object dicdata)
        {
            object value = GetDataByKey(key, dicdata);
            if (value != null && value != DBNull.Value)
            {
                switch (value.GetType().Name.ToLower())
                {
                    case "int":
                    case "int32":
                    case "int16":
                    case "short":
                    case "byte":
                    case "double":
                    case "float":
                    case "decimal":
                    case "long":
                        cell.SetCellValue(Convert.ToDouble(value));
                        break;
                    case "datetime":
                        cell.SetCellValue(Convert.ToDateTime(value));
                        break;
                    default:
                        cell.SetCellValue(value.ToString());
                        break;
                }
            }
            else
            {
                cell.SetCellValue("");
            }
        }
        public static int SetList(ISheet sheet, IRow row, int begrowindex, string listkey, List<string> keys, int listbegcell, object dicdata, string seqnostr, bool autoheight)
        {
            bool has = false;
            int total = 0;
            object listdata = GetDataByKey(listkey, dicdata);
            if (listdata == null || listdata == DBNull.Value)
                goto lblend;
            if (!(listdata is IEnumerable))
                goto lblend;
            IEnumerator aa = (listdata as IEnumerable).GetEnumerator();
            aa.Reset();
            ICell cellnew;
            int newrowindex = begrowindex;
            for (int i = 0; aa.MoveNext(); i++)
            {
                total += 1;
                IRow newrow;
                if (i == 0)
                {
                    newrow = row;
                }
                else
                {
                    newrow = sheet.CopyRow(begrowindex, newrowindex);
                    newrow.Height = row.Height;
                }
                newrowindex += 1;
                has = true;
                int cellindex = listbegcell;
                object dr = aa.Current;
                foreach (string str in keys)
                {
                    if (str == "")
                    {
                        cellindex += 1;
                        continue;
                    }
                    cellnew = newrow.GetCell(cellindex);
                    if (str == seqnostr)
                    {
                        cellnew.SetCellValue(i + 1);
                    }
                    else
                    {
                        SetCellValue(cellnew, str, dr);
                    }
                    cellindex += 1;
                }
                if (autoheight)
                    SetRowHeight(newrow);
            }
            lblend:
            if (!has)
            {
                int cellindex = listbegcell;
                foreach (string key in keys)
                {
                    if (key == "")
                    {
                        cellindex += 1;
                        continue;
                    }
                    cellnew = row.GetCell(cellindex);
                    cellnew.SetCellValue("");
                }
                row.Height = row.Sheet.DefaultRowHeight;
            }
            return total;
        }
        public static float GetCellWidth(ICell cell)
        {
            if (cell == null)
                return 0;
            ISheet sheet = cell.Sheet;
            int mcount = sheet.NumMergedRegions;
            CellRangeAddress mergerange = null;
            for (int i = 0; i < mcount; i++)
            {
                CellRangeAddress range = sheet.GetMergedRegion(i);
                if (range.FirstColumn <= cell.ColumnIndex
                    && range.LastColumn >= cell.ColumnIndex
                    && range.FirstRow <= cell.RowIndex
                    && range.LastRow >= cell.RowIndex)
                {
                    if (range.FirstColumn == cell.ColumnIndex && range.FirstRow == cell.RowIndex)
                    {
                        mergerange = range;
                    }
                    else
                    {
                        break;
                    }
                }
            }
            if (mergerange != null)
            {
                float width = 0;
                for (int i = mergerange.FirstColumn; i <= mergerange.LastColumn; i++)
                {
                    width += sheet.GetColumnWidthInPixels(i);
                }
                return width;
            }
            return sheet.GetColumnWidthInPixels(cell.ColumnIndex);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="begrow">从0开始</param>
        /// <param name="begcol">从0开始</param>
        /// <param name="endrow">从0开始，请比真实位置多一行</param>
        /// <param name="endcol">从0开始，请比真实位置多一列</param>
        /// <param name="filepath"></param>
        public static void AddImg(ISheet sheet, int begrow, int begcol, int endrow, int endcol, string filepath)
        {
            IWorkbook excel = sheet.Workbook;
            byte[] imgdata = File.ReadAllBytes(filepath);
            PictureType imgtype = PictureType.None;
            switch (Path.GetExtension(filepath).ToLower())
            {
                case ".png":
                    imgtype = PictureType.PNG;
                    break;
                case ".jpg":
                    imgtype = PictureType.JPEG;
                    break;
                case ".gif":
                    imgtype = PictureType.GIF;
                    break;
            }
            int pictureIdx = excel.AddPicture(imgdata, imgtype);
            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            // 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再做解释
            IClientAnchor anchor = null;
            if (excel is HSSFWorkbook)
                anchor = new HSSFClientAnchor(0, 0, 0, 0, begcol, begrow, endcol, endrow);
            else
                anchor = new XSSFClientAnchor(0, 0, 0, 0, begcol, begrow, endcol, endrow);
            //把图片插到相应的位置
            IPicture pict = (IPicture)patriarch.CreatePicture(anchor, pictureIdx);
        }
        public static void SetRowHeight(IRow row)
        {
            ICell cell = null;
            ISheet sheet = row.Sheet;
            IWorkbook book = sheet.Workbook;
            IFont font = null;
            float rowheight = row.HeightInPoints;
            int begnum = row.FirstCellNum;
            StringFormat sf = new StringFormat(StringFormat.GenericDefault);
            sf.Trimming = StringTrimming.Word;
            for (; begnum <= row.LastCellNum; begnum++)
            {
                cell = row.GetCell(begnum);
                if (cell == null)
                    continue;
                string value = cell.ToString();
                if (cell.CellStyle != null)
                    font = cell.CellStyle.GetFont(book);
                if (font == null || string.IsNullOrWhiteSpace(value))
                    continue;
                float width = GetCellWidth(cell);
                if (width >= 0 && width <= 0)
                    continue;
                Font newfont = new Font(font.FontName, (float)(font.FontHeightInPoints), GraphicsUnit.Point);
                SizeF maxsize = new SizeF(width, float.MaxValue);
                int c0, lc;
                //须要每次从新生成位图进行测量
                using (var graphics = Graphics.FromHwnd(IntPtr.Zero))
                {
                    var size1 = graphics.MeasureString(value, newfont, maxsize, sf, out c0, out lc);
                    rowheight = Math.Max(rowheight, size1.Height);//(float)( lc * Math.Max(sheet.DefaultRowHeightInPoints, font.FontHeightInPoints))
                }
            }
            row.HeightInPoints = rowheight;// beishu* rowheight;
        }
        #region 设置边框样式
        /// <summary>
        /// 设置边框样式
        /// </summary>
        /// <param name="region">区域</param>
        /// <param name="sheet">工作表</param>
        /// <param name="wb">工作簿</param>
        public void SetBorderStyle(CellRangeAddress region, XSSFSheet sheet, XSSFWorkbook wb)
        {
            RegionUtil.SetBorderTop((int)BorderStyle.Thin, region, sheet, wb);
            RegionUtil.SetBorderBottom((int)BorderStyle.Thin, region, sheet, wb);
            RegionUtil.SetBorderLeft((int)BorderStyle.Thin, region, sheet, wb);
            RegionUtil.SetBorderRight((int)BorderStyle.Thin, region, sheet, wb);
        }
        /// <summary>
        /// 不设置边框样式
        /// 注：无论设不设置样式，添加区域时，都必须执行该方法，否则获取不到添加的区域
        /// </summary>
        /// <param name="region"></param>
        /// <param name="sheet"></param>
        /// <param name="wb"></param>
        public void SetNoneBorderStyle(CellRangeAddress region, XSSFSheet sheet, XSSFWorkbook wb)
        {
            RegionUtil.SetBorderTop((int)BorderStyle.None, region, sheet, wb);
            RegionUtil.SetBorderBottom((int)BorderStyle.None, region, sheet, wb);
            RegionUtil.SetBorderLeft((int)BorderStyle.None, region, sheet, wb);
            RegionUtil.SetBorderRight((int)BorderStyle.None, region, sheet, wb);
        }
        /// <summary>
        /// 设置单元格边框样式
        /// </summary>
        /// <param name="style"></param>
        public void SetBorderStyle(ICellStyle style)
        {
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
        }
        #endregion
        #region 复制样式
        /// <summary>
        ///CloneStyleFrom复制单元格样式失效，所以单独写一个方法复制样式
        /// 将resourceCell的样式复制给targetCell
        /// </summary>
        /// </summary>
        /// <param name="resourceWorkbook">源工作簿</param>
        /// <param name="targetWorkbook">目标工作簿</param>
        /// <param name="resourceCell">源单元格</param>
        /// <param name="targetCell">目标单元格</param>
        public void CopyCellStyle(XSSFWorkbook resourceWorkbook, XSSFWorkbook targetWorkbook, XSSFCell resourceCell, XSSFCell targetCell, bool isSetBorderStyle = false)
        {
            if (resourceWorkbook != null && targetWorkbook != null
                && resourceCell != null && targetCell != null)
            {
                var resourceCellStyle = resourceCell.CellStyle;
                ICellStyle targetCellStyle = targetWorkbook.CreateCellStyle();
                IFont targetCellFont = targetWorkbook.CreateFont();

                targetCellStyle.Alignment = resourceCellStyle.Alignment;
                targetCellStyle.VerticalAlignment = resourceCellStyle.VerticalAlignment;
                targetCellStyle.FillForegroundColor = resourceCellStyle.FillForegroundColor;
                targetCellStyle.FillPattern = resourceCellStyle.FillPattern;
                targetCellStyle.DataFormat = resourceCellStyle.DataFormat;
                targetCellStyle.WrapText = resourceCellStyle.WrapText;

                targetCellFont.FontHeightInPoints = resourceCellStyle.GetFont(resourceWorkbook).FontHeightInPoints;
                targetCellFont.IsBold = resourceCellStyle.GetFont(resourceWorkbook).IsBold;
                targetCellStyle.SetFont(targetCellFont);

                if (isSetBorderStyle) SetBorderStyle(targetCellStyle);
                targetCell.CellStyle = targetCellStyle;
            }
        }
        #endregion
        #region  复制单元格值
        /// <summary>
        /// 复制单元格值
        /// </summary>
        /// <param name="resourceCell">源单元格</param>
        /// <param name="targetCell">目标单元格</param>
        public void CopyCellValue(ICell resourceCell, ICell targetCell)
        {
            if (resourceCell != null && targetCell != null)
            {
                switch (resourceCell.CellType)
                {
                    case CellType.Boolean:
                        targetCell.SetCellValue(resourceCell.BooleanCellValue);
                        break;
                    case CellType.Error:
                        targetCell.SetCellValue(resourceCell.ErrorCellValue);
                        break;
                    case CellType.Formula:
                        switch (resourceCell.CachedFormulaResultType)
                        {
                            case CellType.Boolean:
                                targetCell.SetCellValue(resourceCell.BooleanCellValue);
                                break;
                            case CellType.Error:
                                targetCell.SetCellValue(resourceCell.ErrorCellValue);
                                break;
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(resourceCell))
                                {
                                    targetCell.SetCellValue(resourceCell.DateCellValue.ToString("yyyy-MM-dd"));
                                    break;
                                }
                                else
                                {
                                    targetCell.SetCellValue(resourceCell.NumericCellValue);
                                    break;
                                }
                            case CellType.String:
                                string str = resourceCell.StringCellValue;
                                if (!string.IsNullOrEmpty(str))
                                {
                                    targetCell.SetCellValue(str.ToString());
                                    break;
                                }
                                else
                                {
                                    targetCell.SetCellValue(string.Empty);
                                    break;
                                }
                            case CellType.Unknown:
                            case CellType.Blank:
                            default:
                                targetCell.SetCellValue(string.Empty);
                                break;
                        }
                        break;
                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(resourceCell))
                        {
                            targetCell.SetCellValue(resourceCell.DateCellValue.ToString("yyyy-MM-dd"));
                            break;
                        }
                        else
                        {
                            targetCell.SetCellValue(resourceCell.NumericCellValue);
                            break;
                        }
                    case CellType.String:
                        string strValue = resourceCell.StringCellValue;
                        targetCell.SetCellValue(strValue.ToString().Trim());
                        break;

                    case CellType.Unknown:
                    case CellType.Blank:
                    default:
                        targetCell.SetCellValue(string.Empty);
                        break;
                }
            }
        }
        #endregion
        #region 获取单元格的值
        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public object GetCellValue(ICell item)
        {
            if (item == null)
            {
                return string.Empty;
            }
            switch (item.CellType)
            {
                case CellType.Boolean:
                    return item.BooleanCellValue;

                case CellType.Error:
                    return ErrorEval.GetText(item.ErrorCellValue);

                case CellType.Formula:
                    switch (item.CachedFormulaResultType)
                    {
                        case CellType.Boolean:
                            return item.BooleanCellValue;

                        case CellType.Error:
                            return ErrorEval.GetText(item.ErrorCellValue);

                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(item))
                            {
                                return item.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return item.NumericCellValue;
                            }
                        case CellType.String:
                            string str = item.StringCellValue;
                            if (!string.IsNullOrEmpty(str))
                            {
                                return str.ToString();
                            }
                            else
                            {
                                return string.Empty;
                            }
                        case CellType.Unknown:
                        case CellType.Blank:
                        default:
                            return string.Empty;
                    }
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(item))
                    {
                        return item.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        return item.NumericCellValue;
                    }
                case CellType.String:
                    string strValue = item.StringCellValue;
                    return strValue.ToString().Trim();

                case CellType.Unknown:
                case CellType.Blank:
                default:
                    return string.Empty;
            }
        }
        #endregion
        #region 行复制
        /// <summary>
        /// 将一个工作表的某行复制给另一个工作表
        /// </summary>
        /// <param name="resourceWorkbook">源workBook</param>
        /// <param name="targetWorkbook">目标workBook</param>
        /// <param name="resourceSheet"></param>
        /// <param name="targetSheet"></param>
        /// <param name="sheetMergions">源工作表中的所有合并区域</param>
        /// <param name="resourceRowNum">原行数</param>
        /// <param name="targetRowNum">目标行数</param>
        /// <param name="isSetBorderStyle">是否设置边框</param>
        public void CopyRow(XSSFWorkbook resourceWorkbook, XSSFWorkbook targetWorkbook, XSSFSheet resourceSheet, XSSFSheet targetSheet, List<SheetMergions> sheetMergions, int resourceRowNum, int targetRowNum, bool isSetBorderStyle = false)
        {
            if (resourceWorkbook != null && targetWorkbook != null
               && resourceSheet != null && targetSheet != null)
            {
                var resourceRow = resourceSheet.GetRow(resourceRowNum);
                List<SheetMergions> curRowMergions = sheetMergions.Where(t => t.startRow == resourceRowNum).ToList();
                for (var i = 0; i < curRowMergions.Count; i++)
                {
                    var item = curRowMergions[i];
                    CellRangeAddress mergion = new CellRangeAddress(targetRowNum, targetRowNum + (item.endRow - item.startRow), item.startCol, item.endCol);
                    targetSheet.AddMergedRegion(mergion);
                    if (isSetBorderStyle)
                    {
                        SetBorderStyle(mergion, targetSheet, targetWorkbook);
                    }
                    else
                    {
                        SetNoneBorderStyle(mergion, targetSheet, targetWorkbook);
                    }

                    XSSFRow row = (XSSFRow)targetSheet.GetRow(targetRowNum);
                    XSSFCell cell = (XSSFCell)row.GetCell(item.startCol);

                    XSSFRow modelRow = (XSSFRow)resourceSheet.GetRow(item.startRow);
                    XSSFCell modelCel1 = (XSSFCell)modelRow.GetCell(item.startCol);
                    CopyCellValue(modelCel1, cell);
                    //CloneStyleFrom失效，所以需要重写
                    //cell.CellStyle.CloneStyleFrom(modelCel1.CellStyle);
                    CopyCellStyle(resourceWorkbook, targetWorkbook, modelCel1, cell, isSetBorderStyle);
                }
            }
        }
        #endregion
        #region 金额格式化
        /// <summary>
        /// 格式化为万元单位
        /// </summary>
        /// <param name="amount">金额</param>
        /// <param name="suffix">后缀</param>
        /// <returns></returns>
        public string FormatterLargeMoney(decimal? amount, string suffix="")
        {
            string returnStr = "";
            if (amount == null || amount == 0)
            {
                returnStr = "0.00" + suffix;
            }
            else
            {
                returnStr = Math.Round(Convert.ToDecimal(amount / 10000), 6).ToString();
                var returnStrSplit = returnStr.Split('.');
                returnStr = returnStrSplit[0] + "." + returnStrSplit[1].Substring(0, 2) + suffix;
            }
            return returnStr;
        }
        #endregion
    }
    public class ExcelHelperSheetInfo
    {
        /// <summary>
        /// 新sheet数据，每个数据项生产一个sheet页
        /// </summary>
        public List<object> NewSheetData { get; set; }
        /// <summary>
        /// 模板Sheet
        /// </summary>
        public ISheet ModelSheet { get; set; }
    }
    public class SheetMergions
    {
        public int startRow { get; set; }
        public int endRow { get; set; }
        public int startCol { get; set; }
        public int endCol { get; set; }
    }
}

Controller调用：

  [HttpGet]
        public FileResult DownLoadProjectOverviewByTemplate()
        {
            string fpath = Server.MapPath("~/Template/项目概况模板.xlsx");
            XSSFWorkbook wb = new XSSFWorkbook();
            DownLoadProjectOverview downEntity = new DownLoadProjectOverview();
            string tip = downEntity.DownloadByTemplate(fpath, ref wb);
            if (tip.Contains("出错"))
            {
                return File(new byte[0], "application/ms-excel", tip);
            }
            byte[] fileBytes;
            using (var stream = new System.IO.MemoryStream())
            {
                wb.Write(stream);
                fileBytes = stream.ToArray();
            }
            return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", tip);
        }

调用代码：
如果是完全按照模板格式来，则直接调用指定方法：

public string DownloadByTemplate(string fpath, ref XSSFWorkbook targetWorkbook,int infoID )
        {
            string tip = "";
            ExcelHelper excelHelper = new ExcelHelper();
            var entity = new SHEDRIProjectOverview();
            var downLoadData = entity.GetByInfo(infoID);//获取想要导出的数据
            if (downLoadData == null) return "出错.xls";
            string titleName = "项目信息:" + downLoadData.ProjCode;
            tip = titleName + ".xlsx";
            //这段代码是为了将单位换为万元，如果不需要，不用加这段
            var formatterNumData = new
            {
                GrandPlanAmount = excelHelper.FormatterLargeMoney(downLoadData.GrandPlanAmount, "万元"),
                GrandReportAmount = excelHelper.FormatterLargeMoney(downLoadData.GrandReportAmount, "万元"),
                GrandRevenuePlanAmount = excelHelper.FormatterLargeMoney(downLoadData.GrandRevenuePlanAmount, "万元"),
                YearGrandPlanAmount = excelHelper.FormatterLargeMoney(downLoadData.YearGrandPlanAmount, "万元"),
                YearGrandReportAmount = excelHelper.FormatterLargeMoney(downLoadData.YearGrandReportAmount, "万元"),
                YearGrandRevenuePlanAmount = excelHelper.FormatterLargeMoney(downLoadData.YearGrandRevenuePlanAmount, "万元"),
            };
            IWorkbook resourceWorkbook = excelHelper.Export(fpath, (book) =>
            {
                List<ExcelHelperSheetInfo> sinfo = new List<ExcelHelperSheetInfo>();
                ExcelHelperSheetInfo info = new ExcelHelperSheetInfo();
                sinfo.Add(info);
                info.ModelSheet = book.GetSheetAt(0);
                info.NewSheetData = new List<object>();
                Hashtable ht = new Hashtable();
                info.NewSheetData.Add(ht);
                ht.Add("masterData", downLoadData);
                ht.Add("formatterNumData", formatterNumData);
                return sinfo;
            }, (book, sheet, info) =>
            {
                //sheet.ProtectSheet("123456");
            });
            return tip;
        }

模板格式为：

以上，按照固定格式导出完成。

如果模板格式一半确定，一半不确定（比如有List数据等），则可以根据实际需要复制模板中的行到我需要导出的Excel中。特别注意，如果存在多个List数据集合，一定要注意每一模块的起始行。
比如，我这边的模板格式为这样：部分行不确定、部分列不确定的情况

则按实际需要补充：

public string DownloadByTemplate(string fpath, ref XSSFWorkbook targetWorkbook, int infoID = 0)
        {
            string tip = "";
            ExcelHelper excelHelper = new ExcelHelper();
            var entity = new SHEDRIProjectOverview();
            var downLoadData = entity.GetByInfo(infoID);
            if (downLoadData == null) return "出错.xls";
            //int cols = DateTime.Now.Month+3+1;//总列数=当前年所有月列+去年的三个月列+类型列
            int monthListLength = downLoadData.MonthList.Count();
            int cols = ((monthListLength * 2) < 11) ? 14 : ((monthListLength * 2) - 1);
            DownLoadProjectOverview downTable = new DownLoadProjectOverview();
            string titleName = "项目信息:" + downLoadData.ProjCode;
            tip = titleName + ".xlsx";
            var formatterNumData = new
            {
                GrandPlanAmount = excelHelper.FormatterLargeMoney(downLoadData.GrandPlanAmount, "万元"),
                GrandReportAmount = excelHelper.FormatterLargeMoney(downLoadData.GrandReportAmount, "万元"),
                GrandRevenuePlanAmount = excelHelper.FormatterLargeMoney(downLoadData.GrandRevenuePlanAmount, "万元"),
                YearGrandPlanAmount = excelHelper.FormatterLargeMoney(downLoadData.YearGrandPlanAmount, "万元"),
                YearGrandReportAmount = excelHelper.FormatterLargeMoney(downLoadData.YearGrandReportAmount, "万元"),
                YearGrandRevenuePlanAmount = excelHelper.FormatterLargeMoney(downLoadData.YearGrandRevenuePlanAmount, "万元"),
            };
            int templateTitleRowNum = 0;
            int templateFirstSpliRowNum = 2;
            int templateStartContract = 11;
            int modelContractDataRow = 14;//合同DataGrid数据开始
            int modelProdDeptTitleRow = 16;//参与部门标题
            int modelProdDeptDataRow = 19;//参与DataGrid数据
            int modelProdCostTitleRow = 21;//成本汇总标题
            int modelProdCostDataRow = 24;//成本汇总DataGrid数据
            int modelPlanAndReportTitleRow = 26;//计划、铺排标题
            int modelEndPlanAndReportDataRow = 34;//计划、铺排结束行
            
            IWorkbook resourceWorkbook = excelHelper.Export(fpath, (book) =>
            {
                List<ExcelHelperSheetInfo> sinfo = new List<ExcelHelperSheetInfo>();
                ExcelHelperSheetInfo info = new ExcelHelperSheetInfo();
                sinfo.Add(info);
                info.ModelSheet = book.GetSheetAt(0);
                info.NewSheetData = new List<object>();
                Hashtable ht = new Hashtable();
                info.NewSheetData.Add(ht);
                ht.Add("masterData", downLoadData);
                ht.Add("formatterNumData", formatterNumData);
                return sinfo;
            }, (book, sheet, info) =>
            {
                //sheet.ProtectSheet("123456");
            });
            XSSFSheet resourceSheet = (XSSFSheet)resourceWorkbook.GetSheetAt(0);
            List<SheetMergions> sheetMergions = GetSheetMergions(resourceSheet);

            XSSFRow templateTitleRow = (XSSFRow)resourceSheet.GetRow(templateTitleRowNum);
            XSSFCell templateTitleCell = (XSSFCell)templateTitleRow.GetCell(0);
            XSSFRow templateFirstSplitRow = (XSSFRow)resourceSheet.GetRow(templateFirstSpliRowNum);
            XSSFCell templateFirstSplitCell = (XSSFCell)templateFirstSplitRow.GetCell(0);

            XSSFSheet targetSheet = (XSSFSheet)targetWorkbook.CreateSheet("项目信息概况");
            //Excel标题
            Addmergions(targetWorkbook, targetSheet, 0, 1, 0, cols);
            XSSFRow titleRow = (XSSFRow)targetSheet.GetRow(0);
            XSSFCell titleCell = (XSSFCell)titleRow.GetCell(0);
            titleCell.SetCellValue("项目信息概况");
            excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, templateTitleCell, titleCell, false);

            //项目主信息分隔行
            Addmergions(targetWorkbook, targetSheet, 2, 2, 0, cols);
            XSSFRow projectInfoTipRow = (XSSFRow)targetSheet.GetRow(2);
            XSSFCell projectInfoTipCell = (XSSFCell)projectInfoTipRow.GetCell(0);
            projectInfoTipCell.SetCellValue("项目主信息");
            excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, templateFirstSplitCell, projectInfoTipCell, false);

            for (var i = 3; i < templateStartContract; i++)
            {
                excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, i, i);
            }
            //合同信息分隔行
            Addmergions(targetWorkbook, targetSheet, templateStartContract, templateStartContract, 0, cols);
            XSSFRow contractDataTipRow = (XSSFRow)targetSheet.GetRow(templateStartContract);
            XSSFCell contractDataTipCell = (XSSFCell)contractDataTipRow.GetCell(0);
            contractDataTipCell.SetCellValue("合同信息");
            excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, templateFirstSplitCell, contractDataTipCell, false);

            for (var i = 12; i <= 13; i++)
            {
                excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, i, i, true);
                templateStartContract = templateStartContract + 1;
            }
            int startContract = templateStartContract + 1;
            if (downLoadData.ContractList.Count > 0)
            {
                #region 合同信息
                for (var i = 0; i < downLoadData.ContractList.Count; i++)
                {
                    var item = downLoadData.ContractList[i];
                    excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, modelContractDataRow, startContract, true);
                    excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, modelContractDataRow + 1, startContract + 1, true);
                    XSSFRow contractDataRow = (XSSFRow)targetSheet.GetRow(startContract);
                    XSSFCell contractKindValueCell = (XSSFCell)contractDataRow.GetCell(0);
                    XSSFCell contractCodeValueCell = (XSSFCell)contractDataRow.GetCell(2);
                    XSSFCell contractNameValueCell = (XSSFCell)contractDataRow.GetCell(5);
                    XSSFCell contractAmountValueCell = (XSSFCell)contractDataRow.GetCell(11);
                    XSSFCell contractStatusValueCell = (XSSFCell)contractDataRow.GetCell(13);
                    string contractKindValueStr = "主合同";
                    if (item.ParentID != null && item.ParentID != 0)
                    {
                        if (item.DeOrIncrease == "0")
                        {
                            contractKindValueStr = "补充合同";
                        }
                        else
                        {
                            contractKindValueStr = "分包合同";
                        }
                    }
                    contractKindValueCell.SetCellValue(string.Format("{0}", contractKindValueStr));
                    contractCodeValueCell.SetCellValue(string.Format("{0}", item.Code));
                    contractNameValueCell.SetCellValue(string.Format("{0}", item.Name));
                    contractAmountValueCell.SetCellValue(double.Parse(excelHelper.FormatterLargeMoney(item.Amount, "")));
                    contractStatusValueCell.SetCellValue(string.Format("{0}", item.SHEDRIContractStatus));
                    startContract = startContract + 2;
                }
                #endregion
            }
            if (downLoadData.ProdDeptList.Count > 0)
            {
                int startProdDept = startContract;
                #region 参与部门
                //参与部门信息分隔行
                Addmergions(targetWorkbook, targetSheet, startProdDept, startProdDept, 0, cols);
                XSSFRow prodDeptTipRow = (XSSFRow)targetSheet.GetRow(startProdDept);
                XSSFCell prodDeptTipCell = (XSSFCell)prodDeptTipRow.GetCell(0);
                prodDeptTipCell.SetCellValue("参与部门");
                excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, templateFirstSplitCell, prodDeptTipCell, false);
                startProdDept = startProdDept + 1;
                //先复制标题过来  
                for (var i = modelProdDeptTitleRow + 1; i < modelProdDeptDataRow; i++)
                {
                    excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, i, startProdDept, true);
                    startProdDept = startProdDept + 1;
                }
                for (var i = 0; i < downLoadData.ProdDeptList.Count; i++)
                {
                    var item = downLoadData.ProdDeptList[i];
                    excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, modelProdDeptDataRow, startProdDept, true);
                    excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, modelProdDeptDataRow + 1, startProdDept + 1, true);
                    XSSFRow prodDeptDataRow = (XSSFRow)targetSheet.GetRow(startProdDept);
                    XSSFCell deptNameValueCell = (XSSFCell)prodDeptDataRow.GetCell(0);
                    XSSFCell personInChargeNameValueCell = (XSSFCell)prodDeptDataRow.GetCell(2);
                    XSSFCell amountInChargeValueCell = (XSSFCell)prodDeptDataRow.GetCell(4);
                    XSSFCell deptInRoleValueCell = (XSSFCell)prodDeptDataRow.GetCell(6);
                    XSSFCell workCategoryValueCell = (XSSFCell)prodDeptDataRow.GetCell(8);
                    deptNameValueCell.SetCellValue(string.Format("{0}", item.DeptName));
                    personInChargeNameValueCell.SetCellValue(string.Format("{0}", item.PersonInChargeName));
                    string DeptRoleInProdName = "";
                    if (item.DeptRoleInProd == "InCharge")
                    {
                        DeptRoleInProdName = "主管部门";
                    }
                    else if (item.DeptRoleInProd == "Partner")
                    {
                        DeptRoleInProdName = "参与部门";
                    }
                    else if (item.DeptRoleInProd == "MgmtDept")
                    {
                        DeptRoleInProdName = "院部";
                    }
                    amountInChargeValueCell.SetCellValue(double.Parse(excelHelper.FormatterLargeMoney(item.AmountInCharge, "")));
                    deptInRoleValueCell.SetCellValue(string.Format("{0}", DeptRoleInProdName));
                    var ProdWorkCategoryName = entity.GetDicDataName(item.ProdWorkCategory, "ProdWorkCategory");
                    workCategoryValueCell.SetCellValue(string.Format("{0}", ProdWorkCategoryName));
                    startProdDept = startProdDept + 2;
                }
                #endregion
                if (downLoadData.SumCostTable.Rows.Count > 0)
                {
                    int startProdCost = startProdDept;
                    var costArrayList = downLoadData.SumCostTable.AsEnumerable().ToArray();
                    #region 成本汇总
                    //成本汇总分隔行
                    Addmergions(targetWorkbook, targetSheet, startProdCost, startProdCost, 0, cols);
                    XSSFRow prodCostTipRow = (XSSFRow)targetSheet.GetRow(startProdCost);
                    XSSFCell prodCostTipCell = (XSSFCell)prodCostTipRow.GetCell(0);
                    prodCostTipCell.SetCellValue("成本汇总");
                    excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, templateFirstSplitCell, prodCostTipCell, false);
                    startProdCost = startProdCost + 1;
                    //先复制标题过来  
                    for (var i = modelProdCostTitleRow + 1; i < modelProdCostDataRow; i++)
                    {
                        excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, i, startProdCost, true);
                        startProdCost = startProdCost + 1;
                    }
                    var resourceRow = resourceSheet.GetRow(modelProdCostTitleRow + 1);
                    for (var i = 0; i < downLoadData.SumCostTable.Rows.Count; i++)
                    {
                        excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, modelProdCostDataRow, startProdCost, true);
                        excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, modelProdCostDataRow + 1, startProdDept + 1, true);

                        var resourceRow_Cells = resourceRow.Cells.Count;
                        var targetRow = targetSheet.GetRow(startProdCost);

                        for (var j = 0; j < resourceRow_Cells; j++)
                        {
                            var targetCell = targetRow.GetCell(j);
                            var resourceCellData = resourceRow.GetCell(j);
                            var resourceCellDataStr = excelHelper.GetCellValue(resourceCellData).ToString().Replace("(万元)", "");
                            if (resourceCellDataStr == "部门")
                            {
                                targetCell.SetCellValue(costArrayList[i]["DeptName"].ToString());
                            }
                            else if (resourceCellDataStr == "部门小计")
                            {
                                targetCell.SetCellValue(double.Parse(excelHelper.FormatterLargeMoney(Convert.ToDecimal(costArrayList[i]["DeptSum"]), "")));
                            }
                            else if (!string.IsNullOrEmpty(resourceCellDataStr))
                            {
                                var costTypeItem = downLoadData.CostTypeList.Where(t => t.Value == resourceCellDataStr).FirstOrDefault();
                                targetCell.SetCellValue(double.Parse(excelHelper.FormatterLargeMoney(Convert.ToDecimal(costArrayList[i][costTypeItem.Code + "Amount"]), "")));
                            }
                        }
                        startProdCost = startProdCost + 2;
                    }
                    #endregion
                    if (downLoadData.MonthList.Count > 0)
                    {
                        int initCols = 2;
                        int startCopyCols = 2;
                        int startPanAndReport = startProdCost;
                        int StartCopyPanAndReportColRow = startProdCost + 1;
                        #region 计划、铺排填报
                        //计划、铺排填报分隔行
                        Addmergions(targetWorkbook, targetSheet, startProdCost, startProdCost, 0, cols);
                        XSSFRow planAndReportTipRow = (XSSFRow)targetSheet.GetRow(startProdCost);
                        XSSFCell planAndReportTipCell = (XSSFCell)planAndReportTipRow.GetCell(0);
                        planAndReportTipCell.SetCellValue("计划、铺排填报");
                        excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, templateFirstSplitCell, planAndReportTipCell, false);
                        startPanAndReport = startPanAndReport + 1;
                        //计划铺排，行确定，列不确定
                        //先复制标题过来  
                        for (var i = modelPlanAndReportTitleRow + 1; i <= modelEndPlanAndReportDataRow; i++)
                        {
                            excelHelper.CopyRow((XSSFWorkbook)resourceWorkbook, targetWorkbook, resourceSheet, targetSheet, sheetMergions, i, startPanAndReport, true);
                            startPanAndReport = startPanAndReport + 1;
                        }
                        for (var j = 1; j < downLoadData.MonthList.Count; j++)
                        {
                            var item = downLoadData.MonthList[j].ToString();//2020年10月
                            var plan = excelHelper.FormatterLargeMoney(Convert.ToDecimal(downLoadData.MonthlyPlanAmount[j]), "");
                            var report = excelHelper.FormatterLargeMoney(Convert.ToDecimal(downLoadData.MonthlyReportAmount[j]), "");
                            var revenuePlan = excelHelper.FormatterLargeMoney(Convert.ToDecimal(downLoadData.MonthlyRevenuePlanAmount[j]), "");
                            int k = StartCopyPanAndReportColRow;
                            while (k < StartCopyPanAndReportColRow + 8)
                            {
                                Addmergions(targetWorkbook, targetSheet, k, k + 1, startCopyCols, startCopyCols + 1);
                                XSSFCell targetCell = (XSSFCell)targetSheet.GetRow(k).GetCell(startCopyCols);
                                XSSFCell reourCell = (XSSFCell)resourceSheet.GetRow(modelPlanAndReportTitleRow + 3).GetCell(initCols);
                                if (k == (startProdCost + 1))
                                {
                                    reourCell = (XSSFCell)resourceSheet.GetRow(modelPlanAndReportTitleRow + 1).GetCell(initCols);
                                    targetCell.SetCellValue(item);
                                }
                                else if (k == (startProdCost + 3))
                                {
                                    targetCell.SetCellValue(double.Parse(plan));
                                }
                                else if (k == (startProdCost + 5))
                                {
                                    targetCell.SetCellValue(double.Parse(report));
                                }
                                else if (k == (startProdCost + 7))
                                {
                                    targetCell.SetCellValue(double.Parse(revenuePlan));
                                }
                                excelHelper.CopyCellStyle((XSSFWorkbook)resourceWorkbook, targetWorkbook, reourCell, targetCell, true);
                                k = k + 2;
                            }
                            startCopyCols = startCopyCols + 2;
                        }
                        #endregion
                    }
                }
            }
            return tip;
        }