ListExcel导出(加载模板)
/// <summary>
/// List根据模板导出ExcelMemoryStream
/// </summary>
/// <param name="list"></param>
/// <param name="templdateName"></param>
public static MemoryStream ExportListByTempale(List<TemplateMode> list, string templdateName)
{
try
{
string templatePath = HttpContext.Current.Server.MapPath("/") + "/Resource/ExcelTemplate/";
string templdateName1 = string.Format("{0}{1}", templatePath, templdateName);
FileStream fileStream = new FileStream(templdateName1, FileMode.Open, FileAccess.Read);
ISheet sheet = null;
if (templdateName.IndexOf(".xlsx") == -)//
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileStream);
sheet = hssfworkbook.GetSheetAt();
SetPurchaseOrder(sheet, list);
sheet.ForceFormulaRecalculation = true;
using (MemoryStream ms = new MemoryStream())
{
hssfworkbook.Write(ms);
ms.Flush();
return ms;
}
}
else//
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fileStream);
sheet = xssfworkbook.GetSheetAt();
SetPurchaseOrder(sheet, list);
sheet.ForceFormulaRecalculation = true;
using (MemoryStream ms = new MemoryStream())
{
xssfworkbook.Write(ms);
ms.Flush();
return ms;
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 赋值单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="list"></param>
private static void SetPurchaseOrder(ISheet sheet, List<TemplateMode> list)
{
try
{
foreach (var item in list)
{
IRow row = null;
ICell cell = null;
row = sheet.GetRow(item.row);
if (row == null)
{
row = sheet.CreateRow(item.row);
}
cell = row.GetCell(item.cell);
if (cell == null)
{
cell = row.CreateCell(item.cell);
}
cell.SetCellValue(item.value);
}
}
catch (Exception)
{
throw;
}
}
#endregion