@PostMapping("/customerStatisticsForPC")
public ResponseEntity<byte[]> customerStatisticsForPC(HttpServletRequest request, StatsQuery statsQuery) throws IOException {
UserDetail user = userInfoService.getUserInfo(request);
List<PurchaseOrSaleDetailsVo> list = statsServiceImpl.customerStatisticsForPCList(user, statsQuery);
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成单元格字体样式
HSSFCellStyle headerCellStyle = workbook.createCellStyle();
// 字体格式
HSSFFont font = workbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 24);
headerCellStyle.setFont(font);
String type = "";
if (statsQuery.getType().equals(CustomerEnum.PURCHASE.getCode())) {
type = "采购";
}
if (statsQuery.getType().equals(CustomerEnum.SALE.getCode())) {
type = "销售";
}
HSSFSheet sheet = workbook.createSheet("企业" + type + "统计导出");
HSSFRow row1 = sheet.createRow(0);
HSSFCell cell1 = row1.createCell(3);
cell1.setCellValue("企业" + type + "统计表");
cell1.setCellStyle(headerCellStyle);
customerStatisticsForPC(workbook, sheet, font, statsQuery, user, list);
HSSFCellStyle allCellStyle = workbook.createCellStyle();
// 设置背景色
allCellStyle.setFillForegroundColor((short) 13);
HttpHeaders headers = new HttpHeaders();
String fileName = "企业" + type + "详情表.xls";
headers.setContentDispositionFormData("attachment", new String(fileName.getBytes("utf-8"), "ISO8859-1"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
ByteArrayOutputStream output = new ByteArrayOutputStream();
workbook.write(output);
return new ResponseEntity<byte[]>(output.toByteArray(), headers, HttpStatus.CREATED);
}
public void customerStatisticsForPC(HSSFWorkbook workbook, HSSFSheet sheet, HSSFFont font, StatsQuery statsQuery, UserDetail user, List<PurchaseOrSaleDetailsVo> list) {
// 生成单元格字体样式
HSSFCellStyle titleCellStyle = workbook.createCellStyle();
HSSFRow row2 = sheet.createRow(1);
HSSFCell cell2 = row2.createCell(0);
HSSFCell lastCell = row2.createCell(4);
font.setFontHeightInPoints((short) 16);
titleCellStyle.setFont(font);
if (StringUtils.isNotBlank(statsQuery.getStartDay()) || StringUtils.isNotBlank(statsQuery.getEndDay())) {
cell2.setCellValue("统计时间段: " + statsQuery.getStartDay() + " 至 " + statsQuery.getEndDay());
cell2.setCellStyle(titleCellStyle);
}
lastCell.setCellValue("制表人:" + user.getName());
lastCell.setCellStyle(titleCellStyle);
HSSFRow headerRow = sheet.createRow(2);
String[] header = new String[]{"客户名称", "货物名称", "运单编号", "车牌号", "毛重", "皮重", "净重", "过皮时间", "过毛时间", "备注"};
List<String> headerList = Arrays.asList(header);
for (int i = 0; i < header.length; i++) {
HSSFCell cell = headerRow.createCell(i);
cell.setCellValue(header[i]);
}
int rowNumber = 2;
int startRowNumber = rowNumber + 1;
int endRowNumber = startRowNumber;
String startStr = "";
String endStr = "";
int first = 0;
int last = 0;
if (list.size() != 0) {
for (int j = 0; j < list.size() - 1; j++) {
startStr = list.get(j).getCustomerId();
endStr = list.get(j + 1).getCustomerId();
if (j + 1 == list.size() - 1) {
endRowNumber++;
endStr = TextUtils.getUUID();
}
if ( startStr.equals(endStr)) {
endRowNumber++;
} else {
if ( startRowNumber != endRowNumber) {
sheet.addMergedRegion(new CellRangeAddress(startRowNumber, endRowNumber, 0, 0));
sheet.setHorizontallyCenter(true);
sheet.setVerticallyCenter(true);
}
//合并结束
startRowNumber = endRowNumber + 1;
endRowNumber = startRowNumber;
}
}
rowNumber = 2;
startRowNumber = rowNumber + 1;
endRowNumber = startRowNumber;
for (int j = 0; j < list.size() - 1; j++) {
startStr = list.get(j).getGoodsCategoryName();
endStr = list.get(j + 1).getGoodsCategoryName();
if ( StringUtils.isNotBlank(startStr) && StringUtils.isNotBlank(endStr) && startStr.equals(endStr)) {
endRowNumber++;
} else {
if (startRowNumber != endRowNumber) {
sheet.addMergedRegion(new CellRangeAddress(startRowNumber, endRowNumber, 1, 1));
sheet.setHorizontallyCenter(true);
sheet.setVerticallyCenter(true);
}
//合并结束
startRowNumber = endRowNumber + 1;
endRowNumber = startRowNumber;
}
}
}
for (PurchaseOrSaleDetailsVo li : list) {
rowNumber++;
HSSFRow row = sheet.createRow(rowNumber);
HSSFCellStyle cellStyleFirst2 = workbook.createCellStyle();
cellStyleFirst2.setAlignment(HorizontalAlignment.CENTER);
cellStyleFirst2.setVerticalAlignment(VerticalAlignment.CENTER);
//客户名
HSSFCell cell001 = row.createCell(0);
cell001.setCellStyle(cellStyleFirst2);
cell001.setCellValue(li.getCustomerName());
// 货物名称名
HSSFCellStyle cellStyle002 = workbook.createCellStyle();
cellStyle002.setAlignment(HorizontalAlignment.CENTER);
cellStyle002.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCell cell00 = row.createCell(1);
cell00.setCellStyle(cellStyle002);
cell00.setCellValue(li.getGoodsCategoryName());
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("0.000"));
//运单编号
HSSFCell cell003 = row.createCell(2);
cell003.setCellValue(li.getCode());
cell003.setCellStyle(cellStyle);
//车牌号
HSSFCell cell004 = row.createCell(3);
cell004.setCellValue(li.getVehicleNo());
cell004.setCellStyle(cellStyle);
//毛重,皮重
if (statsQuery.getType().equals(CustomerEnum.PURCHASE.getCode())) {
HSSFCell cell005 = row.createCell(4);
cell005.setCellValue(li.getWeightFirst() + "");
cell005.setCellStyle(cellStyle);
HSSFCell cell006 = row.createCell(5);
cell006.setCellValue(li.getWeightSecond() + "");
cell006.setCellStyle(cellStyle);
//过皮时间
HSSFCell cell09 = row.createCell(7);
if (ObjectUtils.isNotEmpty(li.getWeightSecondTime())) {
cell09.setCellValue(li.getWeightSecondTime() + "");
} else {
cell09.setCellValue("");
}
cell09.setCellStyle(cellStyle);
//过毛时间
HSSFCell cell010 = row.createCell(8);
if (ObjectUtils.isNotEmpty(li.getWeightFirstTime())) {
cell010.setCellValue(li.getWeightFirstTime() + "");
} else {
cell010.setCellValue("");
}
cell010.setCellStyle(cellStyle);
}
if (statsQuery.getType().equals(CustomerEnum.SALE.getCode())) {
HSSFCell cell005 = row.createCell(4);
cell005.setCellValue(li.getWeightSecond());
cell005.setCellStyle(cellStyle);
HSSFCell cell006 = row.createCell(5);
cell006.setCellValue(li.getWeightFirst());
cell006.setCellStyle(cellStyle);
HSSFCell cell011 = row.createCell(7);
if (ObjectUtils.isNotEmpty(li.getWeightFirstTime())) {
cell011.setCellValue(li.getWeightFirstTime() + "");
} else {
cell011.setCellValue("");
}
cell011.setCellStyle(cellStyle);
HSSFCell cell013 = row.createCell(8);
if (ObjectUtils.isNotEmpty(li.getWeightSecondTime())) {
cell013.setCellValue(li.getWeightSecondTime() + "");
} else {
cell013.setCellValue("");
}
cell013.setCellStyle(cellStyle);
}
//净重
HSSFCell cell007 = row.createCell(6);
cell007.setCellValue(li.getNetWeightFirst() + "");
cell007.setCellStyle(cellStyle);
//备注
HSSFCell cell011 = row.createCell(9);
cell011.setCellValue(li.getRemarks());
cell011.setCellStyle(cellStyle);
}
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3000);
sheet.setColumnWidth(2, 3000);
sheet.setColumnWidth(3, 3000);
sheet.setColumnWidth(4, 3000);
sheet.setColumnWidth(5, 3000);
sheet.setColumnWidth(6, 3000);
sheet.setColumnWidth(7, 3000);
sheet.setColumnWidth(8, 3000);
sheet.setColumnWidth(9, 3000);
}
出现问题:wps好,office上行样式有误,
解决: 将 行样式 放在循环外,即可解决。