/// <summary>
/// 创建一个带计算公式的ExcelTable模版
/// </summary>
/// <param name="tableName">工作表名称</param>
/// <param name="beginAddress">开始址址</param>
/// <param name="colums">字典存储，key,列名，value，计算公式
/// (例如:"ROUND(IF(OR([@社保缴纳类别值]=1,[@社保缴纳类别值]=3,[@社保缴纳类别值]=5), [@医疗有效基数], 0)*0.008,2)");
/// </param>
/// <returns></returns>
public static byte[] CreateFormulaTableTemplate(string tableName, string beginAddress, IDictionary <string, string> colums)
{
WorkBook workbook = WorkBook.CreateNew();
WorkSheet worksheet = workbook.Sheets["sheet1"];
worksheet.Tables.Add(tableName, beginAddress, colums);
return(workbook.SaveAsBytes());
}
/// <summary>
/// 创建文档，并将数填充到ExcelTable中
/// </summary>
/// <param name="worksheetName">工作表名称</param>
/// <param name="beginAddress">开始单元格</param>
/// <param name="tableName">ExcelTable名称</param>
/// <param name="dvData">数据源</param>
/// <param name="tableStyle">ExcelTable 样式</param>
/// <param name="isPrintHeaders"></param>
/// <returns></returns>
public static byte[] CreateDocumentAndTable(string worksheetName, string beginAddress, string tableName, System.Data.DataView dvData, ExcelTableStyles tableStyle)
{
WorkBook workbook = WorkBook.CreateNew();
WorkSheet worksheet = workbook.Sheets["sheet1"];
if (worksheetName.IsNotEmpty())
{
worksheet.Name = worksheetName;
}
dvData.Table.TableName = tableName;
worksheet.LoadFromDataView(CellAddress.Parse(beginAddress), tableStyle, dvData, null);
//worksheet.LoadFromDataView(beginAddress, dvData, tableName, tableStyle);
return(workbook.SaveAsBytes());
}
/// <summary>
/// 根据指定工作表上的ExcelTable名称，填充数据
/// </summary>
/// <param name="input">Excel模版流文件</param>
/// <param name="dv">待填充数据</param>
/// <param name="worksheetName">工作薄名称</param>
/// <param name="tableName">ExcelTable名称</param>
/// <returns></returns>
public static byte[] FillExcelTable(Stream input, DataView dv, string worksheetName, string tableName)
{
dv.NullCheck("数据源不能为空！");
input.NullCheck("数据模板为空！");
WorkBook workbook = WorkBook.Load(input);
WorkSheet worksheet = workbook.Sheets[worksheetName];
worksheet.NullCheck(string.Format("不存在指定的{0}工作薄！", worksheetName));
Table excelTable = worksheet.Tables[tableName];
excelTable.NullCheck(string.Format("不存在指定的{0}Excel表格！", tableName));
excelTable.Rows.Clear();
excelTable.FillData(dv);
return(workbook.SaveAsBytes());
}