// 以下代码去除其他信息 仅供参考
List<Obj> a= demo.getOutboundReportDetail(null);
Map<String, List<Obj>> map =
a.stream().collect(Collectors.groupingBy(Obj::getSupplierName));
HSSFWorkbook workbook = new HSSFWorkbook();
// 设置第一个sheet的名称
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultRowHeight((short) (2*256));
sheet.setDefaultColumnWidth(20);
String fileName = "";//设置文件名
Cell cell = null;
CellStyle style = excelUtil.getRow1CellStyle(workbook);//标题
CellStyle row1CellStyle0 = excelUtil.getRow1CellStyle0(workbook);
CellStyle rowCellStyle = excelUtil.getRow1CellStyle1(workbook);
CellStyle row1CellStyle2 = excelUtil.getRow1CellStyle2(workbook);
CellStyle row1CellStyle3 = excelUtil.getRow1CellStyle3(workbook);
// 开始添加excel第一行表头（excel中下标是0）
HSSFRow row = sheet.createRow(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//合并表头
cell = row.createCell(0);
style.setAlignment(HorizontalAlignment.CENTER);
cell.setCellValue("*************");//标题
cell.setCellStyle(style);
BigDecimal count = list.stream().map(getCount).reduce(add);
BigDecimal Money = list.stream().map(getTotalPrice).reduce(add);
int rowNum = 1;
for (String string : map.keySet()) {
List<OutboundProductVO> excelList =
JSON.parseArray(JSON.toJSONString(map.get(string)), OutboundProductVO.class);
BigDecimal sumCount = excelList.stream().map(OutboundProductVO::getCount).reduce(BigDecimal.ZERO,BigDecimal::add);
BigDecimal sumTotal= excelList.stream().map(OutboundProductVO::getTotalPrice).reduce(BigDecimal.ZERO,BigDecimal::add);
row = sheet.createRow(rowNum);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,6));
cell.setCellValue("****"+string);
cell.setCellStyle(row1CellStyle0);
rowNum++;
//表头
row=sheet.createRow(rowNum);
cell = row.createCell(0);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("****");
cell = row.createCell(1);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("****");
cell = row.createCell(2);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("****");
cell = row.createCell(3);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("**");
cell = row.createCell(4);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("**");
cell = row.createCell(5);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("**");
cell = row.createCell(6);
cell.setCellStyle(rowCellStyle);
cell.setCellValue("***");
rowNum++;
//表体数据
for (int i = 0; i <= excelList.size(); i++) {
row=sheet.createRow(rowNum);
if (i == excelList.size()){
cell = row.createCell(0);
cell.setCellStyle(row1CellStyle2);
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,4));//合并前面的表头
cell.setCellValue("**");
cell = row.createCell(1);
cell.setCellStyle(row1CellStyle2);
cell = row.createCell(2);
cell.setCellStyle(row1CellStyle2);
cell = row.createCell(3);
cell.setCellStyle(row1CellStyle2);
cell = row.createCell(4);
cell.setCellStyle(row1CellStyle2);
cell = row.createCell(5);
cell.setCellStyle(row1CellStyle2);
cell.setCellValue(Count);
cell = row.createCell(6);
cell.setCellStyle(row1CellStyle2);
cell.setCellValue(Total);
}else {
cell = row.createCell(0);
cell.setCellValue(Code);
cell = row.createCell(1);
cell.setCellValue(Name);
cell = row.createCell(2);
cell.setCellValue(Specification);
cell = row.createCell(3);
cell.setCellValue(Price);
cell = row.createCell(4);
cell.setCellValue(Unit);
cell = row.createCell(5);
cell.setCellValue(Count);
cell = row.createCell(6);
cell.setCellValue(Price);
}
rowNum++;
}
rowNum++;
}
row=sheet.createRow(rowNum);
cell = row.createCell(0);
cell.setCellValue("**");
cell.setCellStyle(row1CellStyle3);
cell = row.createCell(1);
cell.setCellStyle(row1CellStyle3);
cell = row.createCell(2);
cell.setCellStyle(row1CellStyle3);
cell = row.createCell(3);
cell.setCellStyle(row1CellStyle3);
cell = row.createCell(4);
cell.setCellStyle(row1CellStyle3);
cell = row.createCell(5);
cell.setCellStyle(row1CellStyle3);
cell.setCellValue(count);
cell = row.createCell(6);
cell.setCellStyle(row1CellStyle3);
cell.setCellValue(Money);
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
public class ExcelUtil {
public static void downLoadExcel(String fileName, HttpServletResponse response, HttpServletRequest request, Workbook workbook) throws IOException {
ServletOutputStream outputStream = null;
try {
try {
// 设置文件名的编码格式
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
}
// 设置文件的编码格式
response.setContentType("application/octet-stream;charset=ISO8859-1");
// 设置响应头添加附件
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// 设置不需要缓存
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.addHeader("fileName",fileName);
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
} catch (IOException e) {
} finally {
workbook.close();
if (outputStream != null) {
outputStream.flush();
outputStream.close();
}
}
}
public static CellStyle getRow1CellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//设置水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体
Font font = workbook.createFont();
//设置字号
font.setFontHeightInPoints((short) 15);
//设置是否为斜体
font.setItalic(false);
//设置是否加粗
font.setBold(true);
//设置字体颜色
font.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(font);
return cellStyle;
}
public static CellStyle getRow1CellStyle0(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//设置字体
Font font = workbook.createFont();
//设置字号
font.setFontHeightInPoints((short) 12);
//设置是否为斜体
font.setItalic(false);
//设置是否加粗
font.setBold(true);
//设置字体颜色
font.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(font);
return cellStyle;
}
public static CellStyle getRow1CellStyle1(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//设置下边框
cellStyle.setBorderBottom(BorderStyle.THIN);
//设置上边框
cellStyle.setBorderTop(BorderStyle.THIN);
//设置走边框
cellStyle.setBorderLeft(BorderStyle.THIN);
//设置右边框
cellStyle.setBorderRight(BorderStyle.THIN);
//设置字体
Font font = workbook.createFont();
//设置字号
font.setFontHeightInPoints((short) 12);
//设置是否为斜体
font.setItalic(false);
//设置是否加粗
font.setBold(true);
//设置字体颜色
font.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(font);
//设置背景
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);;
return cellStyle;
}
public static CellStyle getRow1CellStyle2(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//设置下边框
cellStyle.setBorderBottom(BorderStyle.THIN);
//设置上边框
cellStyle.setBorderTop(BorderStyle.THIN);
//设置走边框
cellStyle.setBorderLeft(BorderStyle.THIN);
//设置右边框
cellStyle.setBorderRight(BorderStyle.THIN);
//设置字体
Font font = workbook.createFont();
//设置字号
font.setFontHeightInPoints((short) 11);
//设置是否为斜体
font.setItalic(false);
//设置是否加粗
font.setBold(true);
//设置字体颜色
font.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(font);
return cellStyle;
}
public static CellStyle getRow1CellStyle3(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//设置下边框
cellStyle.setBorderBottom(BorderStyle.THIN);
//设置上边框
cellStyle.setBorderTop(BorderStyle.THIN);
//设置走边框
cellStyle.setBorderLeft(BorderStyle.THIN);
//设置右边框
cellStyle.setBorderRight(BorderStyle.THIN);
//设置字体
Font font = workbook.createFont();
//设置字号
font.setFontHeightInPoints((short) 12);
//设置是否为斜体
font.setItalic(false);
//设置是否加粗
font.setBold(true);
//设置字体颜色
font.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(font);
//设置背景
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);;
return cellStyle;
}
}
大致效果
