public static byte[] ExportByTemplateOption2(string templateFileName, ExcellExportOption option, List <SetExcelDto> setList, int insertPicRowIndex, byte[] picBytes, int insertTableRowIndex, DataTable table)
{
var workbook = OpenExcel(templateFileName);
var sheet = workbook.GetSheetAt(0);
FillSheet(sheet, table, insertTableRowIndex);
SetupByOption(workbook, option);
//指定单元格赋值
if (setList != null)
{
SetExcelValue(workbook, setList);
}
//插入图片
if (picBytes != null)
{
int pictureIndex = workbook.AddPicture(picBytes, PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(50, 50, 225, 225, 0, insertPicRowIndex, 1, insertPicRowIndex + 5);
IPicture pict = patriarch.CreatePicture(anchor, pictureIndex);
}
byte[] data = null;
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
data = ms.ToArray();
ms.Close();
ms.Dispose();
}
workbook.Close();
return(data);
}
private static void SetupByOption(IWorkbook workbook, ExcellExportOption option)
{
if (option == null)
{
return;
}
// 合并单元格
if (option.SheetMergedCell != null)
{
foreach (var sheetMergedCellOption in option.SheetMergedCell)
{
var sheet = workbook.GetSheetAt(sheetMergedCellOption.Key);
foreach (var item in sheetMergedCellOption.Value)
{
sheet.AddMergedRegion(item);
}
}
}
// 重算
if (option.FormulaRecalculationSheetIndex != null)
{
foreach (var index in option.FormulaRecalculationSheetIndex)
{
workbook.GetSheetAt(index).ForceFormulaRecalculation = true;
}
}
}
public static void Export(string outputFileName, ExcellExportOption option, params DataTable[] tables)
{
var workbook = CreateWorkbook();
for (var i = 0; i < tables.Length; i++)
{
var table = tables[i];
var sheetName = string.IsNullOrEmpty(table.TableName) ? $"sheet{i}" : table.TableName;
var sheet = CreateSheetHeader(workbook, table, sheetName);
FillSheet(sheet, table);
AutoSizeColumns(sheet);
}
SetupByOption(workbook, option);
ToFile(workbook, outputFileName);
workbook.Close();
}
public static void ExportByTemplateOption(string outputFileName, string templateFileName, ExcellExportOption option, params DataTable[] tables)
{
var workbook = OpenExcel(templateFileName);
for (var i = 0; i < tables.Length; i++)
{
var sheet = workbook.GetSheetAt(i);
FillSheet(sheet, tables[i]);
}
SetupByOption(workbook, option);
ToFile(workbook, outputFileName);
workbook.Close();
}
public static void ExportByTemplate <T>(List <T> entitys, string outputFileName, string templateFileName, ExcellExportOption option = null)
{
var workbook = OpenExcel(templateFileName);
var data = OutputExcel <T>(workbook, entitys);
workbook.Write(new MemoryStream(data));
SetupByOption(workbook, option);
ToFile(workbook, outputFileName);
workbook.Close();
}
public static byte[] ExportByte <T>(List <T> entitys, string outputFileName, string[] titlesColumns = null, ExcellExportOption option = null)
{
IWorkbook workbook = CreateWorkbook(outputFileName);
var data = OutputExcel(workbook, entitys, titlesColumns);
//workbook.Write(new MemoryStream(data));
//SetupByOption(workbook, option);
//ToFile(workbook, outputFileName);
//workbook.Close();
return(data);
}