public void exportExcel(Long activityId, HttpServletResponse response) throws IOException
{
// 获取统计报表信息
List<ProductInfo> productInfoList = reportDao.queryStatisticReport(activityId);
// 创建一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表sheet
HSSFSheet sheet = workbook.createSheet("统计报表");
// 设置单元格每列的宽度
sheet.setColumnWidth(0, 50 * 256);
sheet.setColumnWidth(1, 50 * 256);
sheet.setColumnWidth(2, 10 * 256);
// 表格信息内容的样式
HSSFCellStyle style = workbook.createCellStyle();
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 行数
int rowCount = 0;
// 第一行的标题行
HSSFRow row0 = sheet.createRow(rowCount++);
// 列表格
HSSFCell cell_title = row0.createCell(0);
cell_title.setCellValue("统计报表");
// 标题样式
HSSFCellStyle style_title = workbook.createCellStyle();
style_title.setAlignment(HorizontalAlignment.CENTER);
style_title.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体样式
HSSFFont font = workbook.createFont();
//字号
font.setFontHeightInPoints((short) 16);
// 红色字体
font.setColor(HSSFFont.COLOR_RED);
style_title.setFont(font);
cell_title.setCellStyle(style_title);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
// 第二行的表头
HSSFRow row1 = sheet.createRow(rowCount++);
// 表头样式
HSSFCellStyle style_header = workbook.createCellStyle();
style_header.setAlignment(HorizontalAlignment.CENTER);
style_header.setVerticalAlignment(VerticalAlignment.CENTER);
style_header.setFillForegroundColor(new HSSFColor.SKY_BLUE().getIndex());
style_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 列表格
HSSFCell cell_header0 = row1.createCell(0);
cell_header0.setCellValue("展示商品名称");
cell_header0.setCellStyle(style_header);
HSSFCell cell_header1 = row1.createCell(1);
cell_header1.setCellValue("SKU商品名称");
cell_header1.setCellStyle(style_header);
HSSFCell cell_header2 = row1.createCell(2);
cell_header2.setCellValue("数量");
cell_header2.setCellStyle(style_header);
if (!StringUtils.isEmpty(productInfoList))
{
for (ProductInfo productInfo : productInfoList)
{
List<SbomInfo> sbomInfoList = productInfo.getSbomInfoList();
for (int i = 0; i < sbomInfoList.size(); i++)
{
// 行表格
HSSFRow row = sheet.createRow(rowCount);
SbomInfo sbomInfo = sbomInfoList.get(i);
if (i == 0)
{
// 列表格
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(productInfo.getDisPrdName());
cell0.setCellStyle(style);
// 含有2个以上数据的,则需要合并单元格
if (sbomInfoList.size() > 1)
{
sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount + sbomInfoList.size() - 1, 0, 0));
}
}
// 列表格
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue(sbomInfo.getSbomName());
cell1.setCellStyle(style);
// 列表格
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue(sbomInfo.getNumber());
cell2.setCellStyle(style);
// 行数增1
rowCount = rowCount + 1;
}
}
}
// 设置文件名
String fileName = "统计报表.xls";
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("Pragma", "No-cache");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
workbook.close();
}
导出Excel表格: