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