设置excel模板，根据模板变量自动填充数据

这是扩展的方法类。

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ExcelUtility.Base;

namespace Common
{
    class ExcelUnityExtend
    {
        public static  SheetFormatterContainer sheetContrainer;

        private static void InsertStringDataToexcel(Dictionary<string, string> StringData)
        {
            foreach (string key in StringData.Keys)
            {
                PartFormatterBuilder keyName = new PartFormatterBuilder();
                keyName.AddFormatter(key, StringData[key]);
                sheetContrainer.AppendFormatterBuilder(keyName);
            }
        }
        private static void insertTableDataToExcel(Dictionary<string, string> TableData,DataTable dt,string startParamName,string endParamName)
        {
            if (endParamName==null)
            {
                TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(dt.Select(), startParamName);
                Dictionary<string, Func<DataRow, object>> dataFunc = new Dictionary<string, Func<DataRow, object>>();
                foreach (string key in TableData.Keys)
                {
                    dataFunc.Add(key, r => r[TableData[key]]);
                }
                tableFormatterBuilder.AddFormatters(dataFunc);
                sheetContrainer.AppendFormatterBuilder(tableFormatterBuilder);
            }
            else
            {
                RepeaterFormatterBuilder<DataRow> tableFormatterBuilder = new RepeaterFormatterBuilder<DataRow>(dt.Select(), startParamName,endParamName);
                Dictionary<string, Func<DataRow, object>> dataFunc = new Dictionary<string, Func<DataRow, object>>();
                foreach (string key in TableData.Keys)
                {
                    dataFunc.Add(key, r => r[TableData[key]]);
                }
                tableFormatterBuilder.AddFormatters(dataFunc);
                sheetContrainer.AppendFormatterBuilder(tableFormatterBuilder);
            }

        }

        /// <summary>
        /// 将 字符 填充到excel模板中
        /// </summary>
        /// <param name="StringData">字符</param>
        /// <returns>工作薄格式容器</returns>
        public static SheetFormatterContainer insertDataToExcel(Dictionary<string, string> StringData)
        {
            sheetContrainer = new SheetFormatterContainer();
            InsertStringDataToexcel(StringData);
            return sheetContrainer;
        }

        /// <summary>
        /// 将 表格 填充到excel模板中
        /// </summary>
        /// <param name="TableData">excel中模板变量与表格名的关系</param>
        /// <param name="dt">表格</param>
        /// <param name="startParamName">开始标志符</param>
        /// <param name="endParamName">截止标识符</param>
        /// <returns>工作薄格式容器</returns>
        public static SheetFormatterContainer insertDataToExcel(Dictionary<string, string> TableData,DataTable dt, string startParamName, string endParamName)
        {
            sheetContrainer = new SheetFormatterContainer();
            if (dt == null)
                return sheetContrainer;
            insertTableDataToExcel(TableData, dt, startParamName, endParamName);
            }
            return sheetContrainer;
        }

        /// <summary>
        /// 将 字符+表格 填充到excel模板中
        /// </summary>
        /// <param name="StringData">字符</param>
        /// <param name="dtstringData">excel中模板变量与表格名的关系</param>
        /// <param name="dt">表格</param>
        /// <param name="startParamName">填充表格开始标识符</param>
        /// <param name="endParamName">填充表格结束标识符</param>
        /// <returns>工作薄格式容器</returns>
        public static SheetFormatterContainer insertDataToExcel(Dictionary<string, string> StringData, Dictionary<string, string> TableData, DataTable dt, string startParamName, string endParamName)
        {
            sheetContrainer = new SheetFormatterContainer();
            InsertStringDataToexcel(StringData);
            if (dt == null)
                return sheetContrainer;
            insertTableDataToExcel(TableData, dt, startParamName, endParamName);
            }
            return sheetContrainer;
        }
    }
    
}

这是如何调用

        Dictionary<string, string> LoadStringSheetData()
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("companycode1", companyCode);
            dic.Add("companycode2", companyCode);
            return dic;
        }
        Dictionary<string, string> LoadDatatableSheetData()
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("rowindex", "rowindex");
            dic.Add("productcode", "productcode");
            return dic;
        }
        Datatable createDt()
        {
            Datatable dt = new Datatable();
            dt.Add("rowindex",typeof(int));
            dt.Add("productcode",typeof(int));
            dt.Rows.Add(1, 123456);
            return createDt;
        }
        public void main()
        {
            Dictionary<string, string> StringData = new Dictionary<string, string>();
            Dictionary<string, string> DatatableData = new Dictionary<string, string>;
            DataTable dt = new DataTable;
            StringData = LoadStringSheetData();
            DatatableData = LoadDatatableSheetData();
            dt = createDt();
            sheetData = ExcelUnityExtend.insertDataToExcel(StringData, DatatableData, dt , "lv_begin", "lv_end");
            formatterContainerDic.Add("sheet1", sheetData);
        }

 excel 模板