java使用SXSSFWorkbook生成具有图片与文字的Excel表格

本文主要是介绍java使用SXSSFWorkbook生成具有图片与文字的Excel表格，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在这里是一个Maven工程，在pom.xml中引入 poi依赖

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency>

例子中的情景是从数据库查出了许多记录，记录的是地理信息。记录有几个字段记录的图片保存的绝对路径。根据这些字段的内容生成图片。例如picOneAddr。

记录分为不同的类型，比如楼房，桥梁等。将每种类型生成一个sheet进行分开保存。

具体导出表格的一个大方法如下：

    public String exoprtExcel(final String userId) {//第一步：查询数据--这一步读者自行实现自己的数据查询List<PointInfo> points = null;points = this.dao.getAllCollect(userId);final Map<String, List<PointInfo>> pointMap = new HashMap<>();for (final PointInfo pointInfo : points) {final String pt = pointInfo.getPointType();if (pointMap.containsKey(pt)) {final List<PointInfo> subList = pointMap.get(pt);subList.add(pointInfo);} else {final List<PointInfo> subList = new ArrayList<>();subList.add(pointInfo);pointMap.put(pt, subList);}}//第二步：生成工作簿final SXSSFWorkbook wb = new SXSSFWorkbook();// 对每一种类型生成一个sheetfor (final Map.Entry<String, List<PointInfo>> entry : pointMap.entrySet()) {final List<PointInfo> pts = entry.getValue();// 获取每种类型的名字--作为sheet显示名称--如果不需要分sheet可忽略String typeName = "";if (this.dao.getTypeByTypeCode(pts.get(0).getPointType()) != null) {typeName = this.dao.getTypeByTypeCode(pts.get(0).getPointType()).getPointTypeName();}final Sheet sheet = wb.createSheet(typeName);//生成用于插入图片的容器--这个方法返回的类型在老api中不同final Drawing patriarch = sheet.createDrawingPatriarch();// 为sheet1生成第一行，用于放表头信息final Row row = sheet.createRow(0);// 第一行的第一个单元格的值Cell cell = row.createCell((short) 0);cell.setCellValue("详细地址");cell = row.createCell((short) 1);cell.setCellValue("经度");cell = row.createCell((short) 2);cell.setCellValue("纬度");cell = row.createCell((short) 3);for (int i = 0; i < pts.size(); i++) {final Row each = sheet.createRow(i + 1);Cell infoCell = each.createCell((short) 0);infoCell.setCellValue(pts.get(i).getAddrDetail());infoCell = each.createCell((short) 1);infoCell.setCellValue(pts.get(i).getX());infoCell = each.createCell((short) 2);infoCell.setCellValue(pts.get(i).getY());infoCell = each.createCell((short) 3);//查询获取图片路径信息--该步读者自定义PointPic pic = this.dao.getPicInfoByPointId(pts.get(i).getId());try {if (pic != null) {for (int k = 0; k < 6; k++) {//因为有六张图片，所以循环6次final short colNum = (short) (4+k);infoCell = each.createCell(colNum);BufferedImage img = null;switch (k) {case 0:if (!StringUtils.isEmpty(pic.getPicOneAddr())) {File imgFile = new File(pic.getPicOneAddr());img = ImageIO.read(imgFile);imgFile = null;}break;case 1:if (!StringUtils.isEmpty(pic.getPicTwoAddr())) {File imgFile = new File(pic.getPicTwoAddr());img = ImageIO.read(imgFile);imgFile = null;}break;case 2:if (!StringUtils.isEmpty(pic.getPicThreeAddr())) {File imgFile = new File(pic.getPicThreeAddr());img = ImageIO.read(imgFile);imgFile = null;}break;case 3:if (!StringUtils.isEmpty(pic.getPicFourAddr())) {File imgFile = new File(pic.getPicFourAddr());img = ImageIO.read(imgFile);imgFile = null;}break;case 4:if (!StringUtils.isEmpty(pic.getPicFiveAddr())) {File imgFile = new File(pic.getPicFiveAddr());img = ImageIO.read(imgFile);imgFile = null;}break;case 5:if (!StringUtils.isEmpty(pic.getPicSixAddr())) {File imgFile = new File(pic.getPicSixAddr());img = ImageIO.read(imgFile);imgFile = null;}break;}ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();ImageIO.write(img, "jpg", byteArrayOut);img = null;//设置每张图片插入位置final XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, colNum,i + 1, (short) (colNum + 1), i + 2);//参数为图片插入在表格的坐标，可以自行查看api研究参数anchor.setAnchorType(0);// 插入图片patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));byteArrayOut.close();byteArrayOut = null;}pic = null;}} catch (final Exception e) {e.printStackTrace();}}}final ByteArrayOutputStream os = new ByteArrayOutputStream();try {wb.write(os);} catch (final IOException e) {e.printStackTrace();}final byte[] content = os.toByteArray();final String url = Var.BASE_URL+ File.separator + "output.xls";//读者自定义路径final File file = new File(url);// Excel文件生成后存储的位置。OutputStream fos = null;try {fos = new FileOutputStream(file);fos.write(content);os.close();fos.close();} catch (final Exception e) {e.printStackTrace();}return url;//文件保存成功，返回url供前端使用--例如下载}