需求描述:项目中需用到一个模板下载,并且需要链接数据库读取数据做到级联动,过程坎坷,记录分享一下
实现方法:Java + POI + 转成流返回
拼接Excel主体
public Workbook spliceWorkbook() {
// 创建一个excel
@SuppressWarnings("resource")
Workbook workbook = new XSSFWorkbook();
// 创建需要用户填写的sheet
XSSFSheet sheet = (XSSFSheet) workbook.createSheet("物流导入");
//设置列宽
sheet.setDefaultColumnWidth((int) ExportExcelCellConstants.DATE_CELL_WIDTH);
Row row0 = sheet.createRow(0);
row0.createCell(0).setCellValue("物流名称");
row0.createCell(1).setCellValue("物流编码");
List<LogisticsCompany> logisticsCompanies = logisticsCompanyDao.findByStatus(true);
if (logisticsCompanies == null || logisticsCompanies.size() == 0) {
return workbook;
}
//设置单元格格式
row0.getCell(0).setCellStyle(createCellStyle((XSSFWorkbook) workbook));
row0.getCell(1).setCellStyle(createCellStyle((XSSFWorkbook) workbook));
//得到物流公司名称
List<String> logisticsNameList = logisticsCompanies.stream().map(LogisticsCompany::getLogisticsCompanyName).collect(Collectors.toList());
String[] logisticsName = new String[logisticsNameList.size()];
logisticsNameList.toArray(logisticsName);
Map<String, List<String>> map = new HashMap<>();
//组装物流公司名称,物流编码
logisticsCompanies.stream().map(logisticsCompany -> {