实现效果
详情生成文件-见附件
xls文件
技术
springcloud,POI
1.注意事项
1)设置单元格自适应大小,对中文支持不太好,需要额外设置
2)注意,createRow指定创建行后,再次重复创建指定行,会导致上次createRow创建的row失效
示例
HSSFRow row2 = sheet.createRow(4); //指定创建第三行
HSSFCell cell2 = row2.createCell(4);
cell2.setCellValue("4");
//有创建了一次第三行,所以会导致上面的第四行信息没了
HSSFRow row3 = sheet.createRow(4);
HSSFCell cell3 = row3.createCell(3);
cell3.setCellValue("3");
代码示例
Resource:
@PostMapping("/quote-lines/_export")
public ResponseEntity<byte[]> exportQuoteLine(@RequestBody List<String> quoteLineIdList) throws IOException {
List<QuoteLineDTO> quoteLineDTOList = quoteLineService.findQuoteLineByIds(quoteLineIdList);
if(CollectionUtils.isEmpty(quoteLineDTOList)){
log.info("exportQuoteLine->The request parameter is empty");
return null;
}
List<QuoteLineDTO> quoteLineDTOS = new ArrayList<>();
int h = 0;
for (int i = 0; i < quoteLineDTOList.size() && h < 30; i++) {
if (org.apache.commons.lang3.StringUtils.isNotBlank(quoteLineDTOList.get(i).getProductType()) && quoteLineDTOList.get(i).getProductType().equalsIgnoreCase(QuoteEnum.CTO.getName())) {
quoteLineDTOS.add(h, quoteLineDTOList.get(i));
h++;
}
}
if (CollectionUtils.isEmpty(quoteLineDTOS)) {
log.info("exportQuoteLine->The request parameter(CTO-QuoteLine) is empty");
return null;
}
String responseFileName = null;
Quote quote = quoteService.findOne(quoteLineDTOS.get(0).getQuoteId()).get();
if (quote.getBusinessUnit().equalsIgnoreCase(QuoteEnum.PCSD.getName())) {
responseFileName = ExportQuoteLineEnum.PCSD_FILENAME.getName();
} else {
responseFileName = ExportQuoteLineEnum.DCG_FILENAME.getName();
}
byte[] bytes = quoteLineService.exportQuoteLineByCTO(quoteLineDTOS, quote);
HttpHeaders headers = new HttpHeaders();
headers.set("file-name", responseFileName);
headers.setContentDispositionFormData("attachment", responseFileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//new FileOutputStream("/home/111.xls").write(bytes, 0, bytes.length)
return new ResponseEntity<>(
bytes,
headers,
HttpStatus.CREATED
);
}
service:
public byte[] exportQuoteLineByCTO(List<QuoteLineDTO> quoteLineDTOList, Quote quote) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
setHeaderCellStyle(workbook);
if (quote.getBusinessUnit().equalsIgnoreCase(QuoteEnum.PCSD.getName())) {
HSSFSheet sheet = workbook.createSheet(QuoteEnum.PCSD.getName());
sheet.setDefaultRowHeight((short) (2 * 256));
sheet.setDefaultColumnWidth(17);
//max row
int maxRow = 0;
for (int g = 0; g < quoteLineDTOList.size(); g++) {
//There are no database fields in dto->com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field "XXX"
List<CvPairsDTO> cvPairsDTOList = new ObjectMapper().readValue(quoteLineDTOList.get(g).getConfiguration(), new TypeReference<List<CvPairsDTO>>() {
});
if (cvPairsDTOList.size() > maxRow) {
maxRow = cvPairsDTOList.size();
}
}
List<HSSFRow> rowList = Arrays.asList(new HSSFRow[maxRow + 2]);
setHeaderStyle(sheet, quoteLineDTOList, rowList, maxRow);
setCellData(sheet, quoteLineDTOList, rowList);
return FileToByte.byteFromFile(workbook);
} else {//dcg
HSSFSheet sheet = workbook.createSheet(QuoteEnum.DCG.getName());
int number = 2;
for (int i = 0; i < quoteLineDTOList.size(); i++) {
sheet.createRow(number - 2).createCell(0).setCellValue(ExportQuoteLineEnum.DCG_FIRSTROW_ONE.getName());
HSSFRow sheetRow = sheet.createRow(number - 1);
sheetRow.createCell(0).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_ONE.getName());
sheetRow.createCell(1).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_TWO.getName());
sheetRow.createCell(2).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_THREE.getName());
sheetRow.createCell(3).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_FOUR.getName());
sheetRow.createCell(4).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_FIVE.getName());
sheetRow.createCell(5).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_SIX.getName());
List<FeatureCodeConfigurationDTO> quoteConfigurationDTOList = new ObjectMapper().readValue(quoteLineDTOList.get(i).getConfiguration(), new TypeReference<List<FeatureCodeConfigurationDTO>>() {
});
for (int j = 0; j < quoteConfigurationDTOList.size(); j++) {
HSSFRow row = sheet.createRow(number + j);
row.createCell(0).setCellValue(quoteLineDTOList.get(i).getSolutionId());
row.createCell(1).setCellValue(quoteLineDTOList.get(i).getProductNumber());
row.createCell(3).setCellValue(quoteConfigurationDTOList.get(j).getFeatureCode());
row.createCell(4).setCellValue(quoteConfigurationDTOList.get(j).getDescription());
row.createCell(5).setCellValue(quoteConfigurationDTOList.get(j).getQuantity());
}
number += quoteConfigurationDTOList.size() + 2;
}
return FileToByte.byteFromFile(workbook);
}
}
//Set Header Style
public void setHeaderStyle(HSSFSheet sheet, List<QuoteLineDTO> quoteLineDTOList, List<HSSFRow> rowList, int maxRow) {
HSSFRow row0 = sheet.createRow(0);
HSSFRow row1 = sheet.createRow(1);
for (int i = 0; i < quoteLineDTOList.size(); i++) {
//Set the first line of the head
HSSFCell cell11 = row0.createCell(4 * i);
cell11.setCellValue(ExportQuoteLineEnum.PCSD_FIRSTROW_ONE.getName());
cell11.setCellStyle(cellStyle);
HSSFCell cell12 = row0.createCell(4 * i + 1);
cell12.setCellValue(quoteLineDTOList.get(i).getProductNumber());
cell12.setCellStyle(cellStyle);
//Set the second line of the head
HSSFCell cell21 = row1.createCell(4 * i);
cell21.setCellValue(ExportQuoteLineEnum.PCSD_SECONDROW_ONE.getName());
cell21.setCellStyle(cellStyle);
HSSFCell cell22 = row1.createCell(4 * i + 1);
cell22.setCellValue(ExportQuoteLineEnum.PCSD_SECONDROW_TWO.getName());
cell22.setCellStyle(cellStyle);
HSSFCell cell23 = row1.createCell(4 * i + 2);
cell23.setCellValue(ExportQuoteLineEnum.PCSD_SECONDROW_THREE.getName());
cell23.setCellStyle(cellStyle);
}
//other row
for (int i = 0; i < maxRow; i++) {
rowList.set(i + 2, sheet.createRow(i + 2));
}
}
//Set data
public void setCellData(HSSFSheet sheet, List<QuoteLineDTO> quoteLineDTOList, List<HSSFRow> rowList) throws IOException {
for (int i = 0; i < quoteLineDTOList.size(); i++) {
List<CvPairsDTO> cvPairsDTOList = new ObjectMapper().readValue(quoteLineDTOList.get(i).getConfiguration(), new TypeReference<List<CvPairsDTO>>() {
});
//other row is data
for (int j = 0; j < cvPairsDTOList.size(); j++) {
rowList.get(j + 2).createCell(4 * i).setCellValue(cvPairsDTOList.get(j).getValueDescription());
}
}
}
//set cell style
public void setHeaderCellStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setBold(true);
cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
}
private static HSSFCellStyle cellStyle = null;
POI好的链接