public static MemoryStream TablesToExcelForXLSX(DataTable dt, DataTable reportTable, string file, int timeCount, string exportAll, string exportDatasource, string exportChart, out byte[] buf)
{
//HSSFWorkbook
//POIDocument, IWorkbook, IList, ICollection, IEnumerable, IEnumerable
//POIXMLDocument, IWorkbook, IList<ISheet>, ICollection<ISheet>, IEnumerable<ISheet>, IEnumerable
HSSFWorkbook xssfworkbook = new HSSFWorkbook();
string columnName = "";
int tmpI = 0;
if (exportAll == "1" || exportDatasource == "1")
{
#region chartsource table
if (dt != null && dt.Rows.Count != 0)
{
//sheet1
ISheet sheet = xssfworkbook.CreateSheet("DataSource");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
columnName = dt.Columns[i].ColumnName;
if (columnName.ToLower() == "linecount" || columnName.ToLower() == "sort")
{
continue;
}
if (columnName.ToLower() == "enlinename")
{
columnName = "ProductLine";
}
if (columnName.ToLower() == "thetype")
{
columnName = "Type";
}
if (i > timeCount + 3) //只显示时间跨度内的字段,加3 是除时间字段外有3字段
{
continue;
}
ICell cell = row.CreateCell(tmpI);
cell.SetCellValue(columnName);
tmpI++;
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
tmpI = 0;
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0 || j == 3)
{
continue;//过滤每行的lineCount和sort字段
}
if (j > timeCount + 3) //只显示时间跨度内的字段,加1 是除时间字段外有1字段
{
continue;
}
ICell cell = row1.CreateCell(tmpI);
var cellValue = dt.Rows[i][j].ToString();
if (tmpI == 0)//第一列为产品线名称
{
cellValue = (cellValue.IndexOf("cn:") == -1) ? cellValue : cellValue.Substring(cellValue.IndexOf("cn:") + 3);
}
cell.SetCellValue(cellValue);
tmpI++;
}
}
}
#endregion
#region datasource table
if (reportTable != null && reportTable.Rows.Count != 0)
{
//sheet2
ISheet sheet2 = xssfworkbook.CreateSheet("ReportSource");
//表头
IRow row2 = sheet2.CreateRow(0);
tmpI = 0;
for (int i = 0; i < reportTable.Columns.Count; i++)
{
columnName = reportTable.Columns[i].ColumnName;
if (columnName.ToLower() == "sort")
{
continue;
}
if (columnName.ToLower() == "thetype")
{
columnName = "Type";
}
ICell cell = row2.CreateCell(tmpI);
cell.SetCellValue(columnName);
tmpI++;
}
//数据
for (int k = 0; k < reportTable.Rows.Count; k++)
{
tmpI = 0;
IRow row1 = sheet2.CreateRow(k + 1);
for (int j = 0; j < reportTable.Columns.Count; j++)
{
if (j == 1)
{
continue;//过滤每行sort字段
}
if (j > timeCount + 1) //只显示时间跨度内的字段,加1 是除时间字段之前有一个1字段
{
continue;
}
ICell cell = row1.CreateCell(tmpI);
cell.SetCellValue(reportTable.Rows[k][j].ToString());
tmpI++;
}
}
}
#endregion
}
if (exportAll == "1" || exportChart == "1")
{
#region 插入图片
string imgpath = AppDomain.CurrentDomain.BaseDirectory + "/export/sellthrough_chart.png";
if (File.Exists(imgpath))
{
//create sheet
//HSSFSheet hssfSheet = (HSSFSheet)xssfworkbook.CreateSheet("Chart");
ISheet hssfSheet = xssfworkbook.CreateSheet("Chart");
//hssfSheet.Protect = false;
// Create the drawing patriarch. This is the top level container for all shapes.
//HSSFPatriarch patriarch = (HSSFPatriarch)hssfSheet.CreateDrawingPatriarch();
IDrawing patriarch = hssfSheet.CreateDrawingPatriarch();
//add picture data to this workbook.
byte[] bytes = System.IO.File.ReadAllBytes(imgpath);
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.PNG);
//add a picture
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 15, 30);
//HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
}
#endregion
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
buf = stream.ToArray();
//var buf = stream.ToArray();
/*
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}*/
return stream;
}
