/// <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); }