本文主要是介绍Java导出excel带图片(希望能帮助你们节省时间),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
第一天太慌张,下班逃跑,一夜没睡好,第二天决定搞出来。
查了好多博客,感觉都挺繁琐的,好多工具类、引入类找不到。经过一上午的琢磨,终于搞定。记录一下
借鉴了这个博主的文章
需求前端点击导出按钮,导出excel,并且带出图片
引入pom依赖
<!-- poi处理 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>com.github.ulisesbocchio</groupId><artifactId>jasypt-spring-boot-starter</artifactId><version>3.0.3</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.4</version></dependency>
我使用了excel模板,放到了resources下
使用Get请求
//通过模板文件,导入模板数据,并在浏览器端下载 startTime endTime@GetMapping("/exportAddressLog")public void exportAddressLog(@RequestParam(value = "startTime") String startTime,@RequestParam(value = "endTime") String endTime,HttpServletResponse response) throws Exception {URL resource = getClass().getClassLoader().getResource("Address.xlsx");String path = resource.getPath();//文件输入流FileInputStream in = new FileInputStream(path);//创建excel流(从指定文件流)Workbook excel = WorkbookFactory.create(in);//定义数据List<AddressLogResource> excelDataList = logService.getAddressLogList(startTime, endTime);//获取第一页Sheet sheet = excel.getSheetAt(0);//填充数据for (int i = 0; i < excelDataList.size(); i++) {//模板文件从第三行开始填Row row = sheet.createRow(i+1);int columnIndex4 = 4; // 设置第4列的宽度int columnIndex3 = 4; // 设置第4列的宽度int width4 = 20; // 设置宽度值为20个字符int width3 = 40; // 设置宽度值为20个字符sheet.setColumnWidth(columnIndex3, width3 * 256);sheet.setColumnWidth(columnIndex4, width4 * 256);row.setHeight((short) (160*10));Cell cell = row.createCell(0);Cell cell1 = row.createCell(1);Cell cell2 = row.createCell(2);Cell cell3 = row.createCell(3);Cell cell4 = row.createCell(4);Cell cell5 = row.createCell(5);//第一个单元格填排名cell.setCellValue(i + 1);//第二个单元格填姓名cell1.setCellValue(excelDataList.get(i).getAddressCoordinate());//第三个单元格填分数cell2.setCellValue(excelDataList.get(i).getAddressName());cell3.setCellValue(excelDataList.get(i).getCameraSn());LocalDateTime logTime = excelDataList.get(i).getLogTime();String dateTimeStr = logTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));System.out.println("当前字符串日期时间:" + dateTimeStr);cell4.setCellValue(dateTimeStr);//插入图片String img = excelDataList.get(i).getImagePath();imgxx(img,sheet,i,5,excel);
// cell4.setCellValue(excelDataList.get(i).getImagePath());}//设置Content-Type为appl ication/vnd.openxmLformats -officedocument. spreadsheetmL. sheetresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//设置Content-Dispositiontattachment;filename-excel.xLsx,表示将文件下载到本地,并指定文件名为excel.xLsxresponse.setHeader("Content-Disposition", "attachment;filename=excel.xlsx");// 通过输出流将Excel文件下载到客户端浏览器ServletOutputStream out = response.getOutputStream();excel.write(out);out.flush();//关闭资源out.close();excel.close();}
插入图片单独抽出的方法
//插入图片private String imgxx(String img, Sheet sheet,int i,int j,Workbook excel){URL photoFile = null;try {photoFile = new URL(img);// 先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArrayByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();//将图片读入BufferedImage对象BufferedImage bufferImg = ImageIO.read(photoFile);// 将图片写入流中ImageIO.write(bufferImg, "jpg", byteArrayOut);// 利用HSSFPatriarch将图片写入EXCELDrawing<?> patriarch = sheet.createDrawingPatriarch();// 图片一导出到单元格I3-5中 列开始:8 行开始:2 列结束:9 行结束:5XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j, i + 1);anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);// 插入图片内容Picture picture = patriarch.createPicture(anchor, excel.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));picture.resize(1,1);} catch (MalformedURLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}return null;}
导出的图片
前端不能使用ajax访问,直接使用 location.href= url
OK…收工
这篇关于Java导出excel带图片(希望能帮助你们节省时间)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!