/// <summary>
/// 将Excel数据填充到指定的模板
/// </summary>
/// <param name="input">模板文件件流</param>
/// <param name="worksheetName">工作表名称</param>
/// <param name="dvData">待填充数据</param>
/// <returns></returns>
public static MemoryStream FillExcelTemplatesByDefinedName(Stream input, string worksheetName, System.Data.DataView dvData, bool buyColumnName = false)
{
input.NullCheck("模版流不能为这空！");
dvData.NullCheck("填充的数据不能为空");
worksheetName.NullCheck("工作薄名称不能为空");
MemoryStream fillExcelStream = new MemoryStream();
WorkBook package = WorkBook.Load(input);
WorkSheet worksheet = package.Sheets[worksheetName];
worksheet.NullCheck("指定的工作表名称不存在！");
Dictionary <string, CellAddress> dtExcel = new Dictionary <string, CellAddress>();
List <string> formulaDictionary = new List <string>();
DefinedName nameRange;
CellAddress cellAddress = default(CellAddress);
Cell formulaCell = null;
foreach (DataColumn dc in dvData.Table.Columns)
{
if (buyColumnName)
{
nameRange = worksheet.Names[dc.ColumnName];
}
else
{
nameRange = worksheet.Names[string.IsNullOrEmpty(dc.Caption) ? dc.ColumnName : dc.Caption];
}
if (nameRange != null)
{
cellAddress = CellAddress.Parse(nameRange.Address.StartColumn, nameRange.Address.StartRow);
dtExcel.Add(dc.ColumnName, cellAddress);
formulaCell = worksheet.Cells[cellAddress.RowIndex + 1, cellAddress.ColumnIndex];
if (formulaCell != null)
{
if (formulaCell.Formula.IsNotEmpty())
{
formulaDictionary.Add(dc.ColumnName);
}
}
}
}
int formulaRowIndex = cellAddress.RowIndex + 1, addRowCount = 0;
worksheet.InserRows(formulaRowIndex, dvData.Count - 1, formulaRowIndex);
foreach (DataRowView dr in dvData)
{
foreach (KeyValuePair <string, CellAddress> key in dtExcel)
{
if (!formulaDictionary.Contains(key.Key))
{
worksheet.Cells[formulaRowIndex + addRowCount, key.Value.ColumnIndex].Value = dr[key.Key];
}
}
addRowCount++;
}
package.Save(fillExcelStream);
fillExcelStream.Seek(0, SeekOrigin.Begin);
return(fillExcelStream);
}
/// <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();
}