/// <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>
/// 创建一个指定Excel 工作表名称Excel
/// </summary>
/// <param name="worksheetName">工作表名称</param>
/// <param name="excelAddress">开始填充数据Excel地址（例如：B2)</param>
/// <param name="dvData">待填充数据</param>
/// <param name="isPrintHeaders">是否显示数据源列名</param>
/// <returns></returns>
public static MemoryStream DocumentBuilder(string worksheetName, string excelAddress, System.Data.DataView dvData)
{
excelAddress.NullCheck("工作薄名称不能为空");
MemoryStream createExcelStream = new MemoryStream();
WorkBook workbook = WorkBook.CreateNew();
WorkSheet worksheet = workbook.Sheets["sheet1"];
if (worksheetName.IsNotEmpty())
{
worksheet.Name = worksheetName;
}
dvData.Table.TableName = string.Empty;
worksheet.LoadFromDataView(CellAddress.Parse(excelAddress), ExcelTableStyles.None, dvData, null);
//worksheet.LoadFromDataTable(excelAddress, dvData);
workbook.Save(createExcelStream);
return(createExcelStream);
}