POI渲染Excel表格模板替换其中指定表格参数，以及Microsoft联机文档查看器遇到的坑

109 阅读 0 评论 72 点赞

我是靠谱客的博主知性画板，最近开发中收集的这篇文章主要介绍POI渲染Excel表格模板替换其中指定表格参数，以及Microsoft联机文档查看器遇到的坑，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

POI渲染表格替换指定address参数

- pom.xml
- Util工具类
- - 划重点excel文件渲染数据核心方法
  - POJO
  - 下边介绍下小编这里对线上office展示采用的方式
- 坑！
- 缺点

pom.xml

        <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>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-contrib</artifactId>
            <version>3.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>

Util工具类

我们日常开发中很少将静态问题放置在本地服务器，更多的是放在各种云，例如小编目前就是存储在百度云的BOS中，由于构建poi对象时，小编的方式是根据已有模板创建HSSFWorkbook/XSSFWorkbook,所以首先需要获取模板文件的输入流，BOS的Util中已经提供了获取指定文件输入流的方法，但是! 这个流有问题，根据他不能完成渲染数据，后来没办法，只能先下载到本地，然后再获取输入流，这样最后才成功了。

	/**
     * 从网络Url中下载文件
     * 由于linux 与 windows的文件分隔符不一致，所以强烈建议使用 File.separator
     * urlStr 文件地址
     * fileName 保存在本地文件的名称
     * savePath 保存文件的地址 (注意，文件地址与文件名之间添加了file路径）
     */
    public static FileInputStream downLoadFromUrl(String urlStr, String fileName, String savePath) {
        try {
            URL url = new URL(urlStr);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            //设置超时间为3秒
            conn.setConnectTimeout(3 * 1000);
            //防止屏蔽程序抓取而返回403错误
//            conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
            //得到输入流
            InputStream inputStream = conn.getInputStream();
            //获取自己数组
            byte[] getData = readInputStream(inputStream);

            //文件保存位置
            File saveDir = new File(savePath);
            if (!saveDir.exists()) {
                saveDir.mkdir();
            }
            File file = new File(saveDir + File.separator + "file" + File.separator + fileName);
            FileOutputStream fos = new FileOutputStream(file);
            fos.write(getData);
            fos.close();
            inputStream.close();
            return new FileInputStream(saveDir + File.separator + "file" + File.separator + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 从输入流中获取字节数组
     */
    public static byte[] readInputStream(InputStream inputStream) throws IOException {
        byte[] buffer = new byte[1024];
        int len = 0;
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        while ((len = inputStream.read(buffer)) != -1) {
            bos.write(buffer, 0, len);
        }
        bos.close();
        return bos.toByteArray();
    }

划重点excel文件渲染数据核心方法

/**
 *
 * 功能描述:
 *
 * @Auther: 读少
 * @Date: 2019/3/26 17:25
 * fileName 文件名称
 * in 模板文档输入流
 * out 模板文档输出流
 * resultValue （这里边封装了要渲染参数的坐标index，以及值value）
 * startTime，endTime由于小编处理的是数据报表 所以有抄表区间开始时间，截止时间
 *
 */
 public static void applyDataForReport(String fileName, InputStream in, FileOutputStream out,
                                          List<ReportFormValue> resultValue, CmReportFormsModel model, String startTime,
                                          String endTime) {
        String copyTimeCellIndex = model.getCopyTimeCellIndex();
        try {
            if (fileName.endsWith("xls")) {

                HSSFWorkbook workbook = new HSSFWorkbook(in);
                in.close();
                HSSFSheet sheet = workbook.getSheetAt(0);
                resultValue.forEach(o -> {
                    CellAddress cellAddress = new CellAddress(o.getIndex());
                    HSSFRow row = sheet.getRow(cellAddress.getRow());
                    if (row == null) {
                        row = sheet.createRow(cellAddress.getRow());
                    }
                    HSSFCell cell = row.getCell(cellAddress.getColumn());
                    if (cell == null) {
                        cell = row.createCell(cellAddress.getColumn());
                    }
                    cell.setCellValue(Double.valueOf(o.getValue()));
                });
                if (copyTimeCellIndex != null && !"".equals(copyTimeCellIndex)) {
                    String[] index = copyTimeCellIndex.split(",");
                    for (String tempIndex : index) {
                        CellAddress cellAddress = new CellAddress(tempIndex);
                        HSSFRow row = sheet.getRow(cellAddress.getRow());
                        if (row == null) {
                            row = sheet.createRow(cellAddress.getRow());
                        }
                        HSSFCell cell = row.getCell(cellAddress.getColumn());
                        if (cell == null) {
                            cell = row.createCell(cellAddress.getColumn());
                        }
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
                        DateTimeFormatter reF = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
                        LocalDateTime.parse(startTime, df).format(reF);
                        cell.setCellValue(
                                "抄表区间：" + LocalDateTime.parse(startTime, df).format(reF) + "-" + LocalDateTime
                                        .parse(endTime, df).format(reF));
                    }
                }
                sheet.setForceFormulaRecalculation(true);
                workbook.write(out);
                in.close();
                out.flush();
                out.close();
            } else {
                //xlxs文件

                XSSFWorkbook workbook = new XSSFWorkbook(in);
                in.close();
                XSSFSheet sheet = workbook.getSheetAt(0);
                resultValue.forEach(o -> {
                    CellAddress cellAddress = new CellAddress(o.getIndex());
                    XSSFRow row = sheet.getRow(cellAddress.getRow());
                    if (row == null) {
                        row = sheet.createRow(cellAddress.getRow());
                    }
                    XSSFCell cell = row.getCell(cellAddress.getColumn());
                    if (cell == null) {
                        cell = row.createCell(cellAddress.getColumn());
                    }
                    cell.setCellValue(Double.valueOf(o.getValue()));
                });
                if (copyTimeCellIndex != null && !"".equals(copyTimeCellIndex)) {
                    String[] index = copyTimeCellIndex.split(",");
                    for (String tempIndex : index) {
                        CellAddress cellAddress = new CellAddress(tempIndex);
                        XSSFRow row = sheet.getRow(cellAddress.getRow());
                        if (row == null) {
                            row = sheet.createRow(cellAddress.getRow());
                        }
                        XSSFCell cell = row.getCell(cellAddress.getColumn());
                        if (cell == null) {
                            cell = row.createCell(cellAddress.getColumn());
                        }
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
                        DateTimeFormatter reF = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
                        LocalDateTime.parse(startTime, df).format(reF);
                        /**
                      	 *根据指定位置添加，模板渲染时间
                         */
                        cell.setCellValue(
                                "抄表区间：" + LocalDateTime.parse(startTime, df).format(reF) + "-" + LocalDateTime
                                        .parse(endTime, df).format(reF));
                    }
                }
                sheet.setForceFormulaRecalculation(true);
                workbook.write(out);
                in.close();
                out.flush();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("渲染数据异常" + e.getMessage());
        }
    }

POJO

/**
 * @author 读少
 * @date 2019/3/26 17:33
 * 属性可删减
 */
@Data
public class ReportFormValue {
    private String index;
    private String value;
    private Integer pt;
    private Integer ct;
    private Integer valueType;
    private Integer dateType;
    private String orgMeterCode;
    private String paramCode;
}

/**
 *
 * 功能描述: excel模板
 *
 * @Auther: 读少
 * @Date: 2019/3/26 17:25
 */
@Data
public class CmReportFormsModel {
    private Integer id;

    private Integer orgId;

    private String modelName;

    private String modelBosUrl;

    private Integer modelType;

    private String copyTimeTemplate;

    private Integer publishStatus;

    private Integer state;

    private String creator;

    private Date createTime;

    private String updator;

    private Date updateTime;

    private String remark;

    private String copyTimeCellIndex;

}

综上服务器端渲染核心代码已完成，其中out输出流，开发者可自行处理，从responset.getOutputStream()，或者new一个输出流写到本地服务器，均可，无非就是返回给前端的方式不一样罢了。

下边介绍下小编这里对线上office展示采用的方式

网址：https://products.office.com/zh-CN/office-online/view-office-documents-online
实现大致效果如下，还可以实现下载，打印，全屏等功能。
其中黄色格子即为渲染数据位置

坑！

如果表格中存在公式，例如F12 = F11+F10+F9 建议不要采用office编辑文档后上传模板文件，这样这份文件中的公式值在该组件中无法线上显示，需下载后查看，无论xls，xlsx结果都一样。
解决办法，非常骚气，用wps编辑一下表格，触发提示保存，保存之后再重新上传文档就可以在线上查看到公式单元格了。