需求：根据数据库中excel保存地址url，获取到Excel表格，将其中数据填充到一张新生成的excel表格中
1.设置读取excel和要填充excel的起始行列
public void setExcelData(String fileUrl, String downloadPath, String reportFilePath, String endTime) {// 读取excel开始行 14 结束行 322 int readStartRow = 14;int readEndRow = 322;// 读取excel开始列 9 结束列 12int readStartCell = 9;int readEndCell = 12;// 写入excel开始行 1， 开始列 2int writeStartRow = 1;int writeStartCell = 2;// 读取excel的第1个sheet，写入excel的第2个sheetint sheetIndex = 0;int outSheetIndex=2;//获取读取的excel，以及要填充的excelFile file = HttpUtil.downloadFileFromUrl(cmmFileUrl, downloadPath);File reportFile = FileUtil.file(reportFilePath);InputStream inputStream = null;InputStream reportInputStream = null;try {inputStream = FileUtil.getInputStream(file);reportInputStream = FileUtil.getInputStream(reportFile);setData(inputStream, reportInputStream, reportFilePath, sheetIndex, readStartRow, readEndRow, readStartCell, readEndCell, writeStartRow, writeStartCell, outSheetIndex, endTime);} catch (Exception e) {log.error("写入excel数据失败", e);} finally {try {assert inputStream != null;inputStream.close();} catch (IOException e) {log.error("关闭excel文件流失败");}try {assert reportInputStream != null;reportInputStream.close();} catch (IOException e) {log.error("关闭excel文件流失败");}FileUtil.del(file);}}
2.读取表格的文件流，进行数据填充
private static void setData(InputStream inputStream, InputStream reportInputStream, String reportFilePath, int sheetIndex, int readStartRow, int readEndRow, int readStartCell, int readEndCell,int writeStartRow, int writeStartCell, int outSheetIndex, String endTime) {Workbook wb = null;Workbook reportWb = null;OutputStream os = null;try {// 1、获取要读取的文件工作簿对象ZipSecureFile.setMinInflateRatio(-1.0d);wb= WorkbookFactory.create(inputStream);reportWb= WorkbookFactory.create(reportInputStream);// 2、获取工作表Sheet s = wb.getSheetAt(sheetIndex);Sheet rs = reportWb.getSheetAt(outSheetIndex);setValue(s, rs, readStartRow, readEndRow, readStartCell, readEndCell, writeStartRow, writeStartCell);// 2.1、工作表填入测量完成时间Sheet firstSheet = reportWb.getSheetAt(0);// 生成一个样式CellStyle style = reportWb.createCellStyle();// 设置字体Font font = reportWb.createFont();font.setFontHeightInPoints((short) 9);// 把字体应用到当前的样式style.setFont(font);//完成时间, 开始行 8, 开始列 112Row row2 = firstSheet.getRow(8);Cell cell2 = row2.createCell(112);if(StrUtil.isNotEmpty(endTime)){cell2.setCellValue(endTime);cell2.setCellStyle(style);}// 输出时通过流的形式对外输出，包装对应的目标文件os = Files.newOutputStream(Paths.get(reportFilePath));// 将内存中的workbook数据写入到流中reportWb.write(os);} catch (Exception e) {throw new RuntimeException(e);} finally {try {assert reportWb != null;reportWb.close();} catch (Exception e) {log.error("关闭ReportWb失败");}try {assert os != null;os.close();} catch (Exception e) {log.error("关闭输出流失败");}try {wb.close();} catch (Exception e) {log.error("关闭Wb失败");}}}
3.将读取excel数据填充到当前excel中
private static void setValue(Sheet s, Sheet rs, int readStartRow, int readEndRow, int readStartCell, int readEndCell, int writeStartRow, int writeStartCell) {for (int i = readStartRow; i < readEndRow; i++) {int startCell = writeStartCell;for (int j = readStartCell; j < readEndCell; j++) {// 3、获取行 开始行 14 结束行 284 开始列 9 结束列Row row = s.getRow(i);// 4、获取列Cell cell = row.getCell(j);// 5、根据数据的类型获取数据Double data = null;String data1 = null;try {data = cell.getNumericCellValue();}catch (IllegalStateException e) {data1 = cell.getStringCellValue();}catch (NullPointerException e) {break;}// 报告开始行 1, 开始列 2Row rRow = rs.getRow(writeStartRow);if (rRow == null) {rRow = rs.createRow(writeStartRow);}Cell rCell = rRow.createCell(startCell);// 5、在列中写数据if(data != null) {rCell.setCellValue(data);}if(StringUtils.isNotBlank(data1)) {rCell.setCellValue(data1);}startCell++;}writeStartRow++;}}