在这里是一个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>

1
2
3
4
5
6
7
8
9
10
例子中的情景是从数据库查出了许多记录，记录的是地理信息。记录有几个字段记录的图片保存的绝对路径。根据这些字段的内容生成图片。例如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();
    // 对每一种类型生成一个sheet
    for (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供前端使用--例如下载
}

java生成excel（SXSSFWorkbook）

aizongjie

6个回复