private Stream Create(IList <DataTable> datatables, IList <ColumnTree> columnTrees, CellStyleSettings cellStyleSettings = null)
{
if (cellStyleSettings == null)
{
cellStyleSettings = new CellStyleSettings {
BorderStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin
};
}
if (datatables.Count != columnTrees.Count)
{
throw new Exception($"表头数量[{columnTrees.Count}]和表格数量[{datatables.Count}]不一致.");
}
using (var excelPackage = new ExcelPackage())
{
for (int i = 0; i < datatables.Count; i++)// ar dataTable in dataToExport.Tables)
{
var dataTable = datatables[i];
string tablename = string.IsNullOrEmpty(dataTable.TableName) ? "sheet1" : dataTable.TableName;
var columnTree = columnTrees[i];
var sheet = excelPackage.Workbook.Worksheets.Add(tablename);
//create merged header cells
var headerCreateor = new ExceHeaderCreatorEPPlus(columnTree, sheet);
IList <string> columnFormats;
int headerHeight = headerCreateor.CreateHeader(out columnFormats);
//create body
FillSheetEpplusWithLoadRange(sheet, datatables[i], headerHeight, columnFormats, cellStyleSettings);
}
Stream stream = new MemoryStream();
excelPackage.SaveAs(stream);
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
}
public System.IO.Stream Create(DataTable dataTable, ColumnTree columnTree, CellStyleSettings cellStyleSettings = null)
{
return(Create(new List <DataTable> {
dataTable
}, new List <ColumnTree> {
columnTree
}, cellStyleSettings));
}
public Stream Create(DataSet dataToExport, IList <ColumnTree> columnTrees, CellStyleSettings cellStyleSettings = null)
{
return(Create(FetchFrom(dataToExport), columnTrees, cellStyleSettings));
}
public Stream Create(DataSet dataToExport, CellStyleSettings cellStyleSettings = null)
{
return(Create(dataToExport, CreateColumnTrees(dataToExport), cellStyleSettings));
}
public System.IO.Stream Create(DataSet dataset, ColumnTree columnTree, CellStyleSettings cellStyleSettings = null)
{
return(Create(FetchFrom(dataset), new List <ColumnTree> {
columnTree
}, cellStyleSettings));
}
public System.IO.Stream Create(DataTable dataTable, CellStyleSettings cellStyleSettings = null)
{
return(Create(dataTable, CreateColumnTree(dataTable), cellStyleSettings));
}
public Stream Create <T>(IList <T> data, IDictionary <string, string> propertyNameMaps = null, CellStyleSettings cellStyleSettings = null)
{
var dataTable = new DataTableConverter <T>().Convert(data, propertyNameMaps);
return(Create(dataTable));
}
private void FillSheetEpplusWithLoadRange(ExcelWorksheet sheet, DataTable dataTable, int startRow, IList <string> columnFormats, CellStyleSettings cellStyleSettings)
{
//A workbook must have at least on cell, so lets add one...
int rows = dataTable.Rows.Count;
int columns = dataTable.Columns.Count;
//fill data
var cells = sheet.Cells[startRow + 1, 1, rows, columns];
cells.LoadFromDataTable(dataTable, false);
//format
for (int i = 0; i < columnFormats.Count; i++)// format in columnFormats)
{
string format = columnFormats[i];
if (!string.IsNullOrEmpty(format))
{
var columnCells = sheet.Cells[startRow + 1, i + 1, startRow + rows, i + 1];
//columnCells
columnCells.Style.Numberformat.Format = format;
}
}
//style
var bodyCells = sheet.Cells[startRow + 1, 1, startRow + rows, columns];
if (cellStyleSettings != null)
{
foreach (var cell in bodyCells)
{
cell.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
}
}
