POI导入导出及动态复杂表头生成

来源：互联网发布：youtube for mac 编辑：程序博客网时间：2024/06/06 04:39

在此之前打开POI分类已存在三篇文章介绍了POI的使用及流下载，近期项目发现之前的使用中是有缺陷的，今天做一下总结，从现在起我们可以忽略前三篇文章对POI的使用，但是流下载方式还是可以参考的，这里还是再提一笔，流下载使用ajax会出异常，不生效，我是用的是提交form表单的方式来进行流下载，很遗憾，暂时取不到返回值

下面我们继续谈一下POI的使用

首先需要强调几点，POI对于Excel2003和Excel2007+是区别对待的，分别使用了HSSFWorkbook和XSSFWorkbook，但它们实现了同一个接口Workbook,这对我们的编程实现提供了一定的灵活性

建议：对于导出，我们直接使用XSSFWorkbook即可，HSSFWorkbook是存在一定缺陷，之前使用过HSSFWorkbook，数据量达到65536就会报错导出失败，近期做新的项目才发现原因

相对于导入工作，需要动态对文件识别Excel2003或是Excel2007+,有些文章写到通过后缀名来辨别，这存在一个问题，用户修改后缀之后，结果可想而知，幸亏POI提供了一个便利的方法Workbook create(InputStream inp)，通过输入流动态创建，GOOD！！！下面，我也会提供相关的用法。在这里还要给一个提示，使用3.9版本这种方法报错，在此，我来提供一个maven info

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

没错，这三个包都是需要的，正是为了实现这个功能，我对此封装了一个POI的工具包

import org.apache.poi.hssf.usermodel.*;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.NumberToTextConverter;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.*;public class PoiUtil {    private PoiUtil() {    }    /**     * Excel2003和Excel2007+创建方式不同     * Excel2003使用HSSFWorkbook 后缀xls     * Excel2007+使用XSSFWorkbook 后缀xlsx     * 此方法可保证动态创建Workbook     *     * @param is     * @return     */    public static Workbook createWorkbook(InputStream is) throws IOException, InvalidFormatException {        return WorkbookFactory.create(is);    }    /**     *导入数据获取数据列表     * @param wb     * @return     */    public static List<List<Object>> getDataList(Workbook wb) {        List<List<Object>> rowList = new ArrayList<>();        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {            Sheet sheet = wb.getSheetAt(sheetNum);            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {                Row row = sheet.getRow(i);                if (null == row)                    continue;                List<Object> cellList = new ArrayList<>();                for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {                    Cell cell = row.getCell(j);                    cellList.add(getCellValue(cell));                }                rowList.add(cellList);            }        }        return rowList;    }    private static String getCellValue(Cell cell) {        String cellvalue = "";        if (cell != null) {            // 判断当前Cell的Type            switch (cell.getCellType()) {                // 如果当前Cell的Type为NUMERIC                case HSSFCell.CELL_TYPE_NUMERIC: {                    short format = cell.getCellStyle().getDataFormat();                    if (format == 14 || format == 31 || format == 57 || format == 58) {   //excel中的时间格式                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                        double value = cell.getNumericCellValue();                        Date date = DateUtil.getJavaDate(value);                        cellvalue = sdf.format(date);                    }                    // 判断当前的cell是否为Date                    else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。                        // 如果是Date类型则，取得该Cell的Date值           // 对2014-02-02格式识别不出是日期格式                        Date date = cell.getDateCellValue();                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");                        cellvalue = formater.format(date);                    } else { // 如果是纯数字                        // 取得当前Cell的数值                        cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());                    }                    break;                }                // 如果当前Cell的Type为STRIN                case HSSFCell.CELL_TYPE_STRING:                    // 取得当前的Cell字符串                    cellvalue = cell.getStringCellValue().replaceAll("'", "''");                    break;                case HSSFCell.CELL_TYPE_BLANK:                    cellvalue = null;                    break;                // 默认的Cell值                default: {                    cellvalue = " ";                }            }        } else {            cellvalue = "";        }        return cellvalue;    }    /**     * 此方法生成表头并写入表头名称     *     * @param nodes 节点     * @param sheet 工作簿     * @param style 单元格样式     * @return 数据加载开始行     */    public static int generateHeader(List<HeaderNode> nodes, Sheet sheet, CellStyle style) {        Map<RowKey, Row> hssfRowMap = new HashMap<>();        int dataStartRow = 0;        for (HeaderNode node : nodes) {            if (!(node.firstRow == node.getLastCol() || node.getFirstCol() == node.getLastCol())) {                CellRangeAddress cra = new CellRangeAddress(node.getFirstRow(), node.getLastRow(),                        node.getFirstCol(), node.getLastCol());                sheet.addMergedRegion(cra);            }            dataStartRow = dataStartRow >= node.getLastRow() ? dataStartRow : node.getLastRow();            RowKey key = new RowKey();            key.setFirstRow(node.getFirstRow());            key.setLastRow(node.getLastRow());            Row row = hssfRowMap.get(key);            if (null == row) {                row = sheet.createRow(node.getFirstRow());                hssfRowMap.put(key, row);            }            Cell cell = row.createCell(node.getFirstCol());            cell.setCellValue(node.getName());            cell.setCellStyle(style);        }        return dataStartRow+1;    }    public static class HeaderNode {        private String name;        private int firstRow;        private int lastRow;        private int firstCol;        private int lastCol;        public String getName() {            return name;        }        public void setName(String name) {            this.name = name;        }        public int getFirstRow() {            return firstRow;        }        public void setFirstRow(int firstRow) {            this.firstRow = firstRow;        }        public int getLastRow() {            return lastRow;        }        public void setLastRow(int lastRow) {            this.lastRow = lastRow;        }        public int getFirstCol() {            return firstCol;        }        public void setFirstCol(int firstCol) {            this.firstCol = firstCol;        }        public int getLastCol() {            return lastCol;        }        public void setLastCol(int lastCol) {            this.lastCol = lastCol;        }    }    private static class RowKey {        private int firstRow;        private int lastRow;        public int getFirstRow() {            return firstRow;        }        public void setFirstRow(int firstRow) {            this.firstRow = firstRow;        }        public int getLastRow() {            return lastRow;        }        public void setLastRow(int lastRow) {            this.lastRow = lastRow;        }        @Override        public boolean equals(Object o) {            if (this == o) return true;            if (!(o instanceof RowKey)) return false;            RowKey key = (RowKey) o;            return firstRow == key.firstRow &&                    lastRow == key.lastRow;        }        @Override        public int hashCode() {            return Objects.hash(firstRow, lastRow);        }    }    public static void main(String[] args) {        // 第一步，创建一个webbook，对应一个Excel文件        Workbook workbook = new XSSFWorkbook();        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet        Sheet sheet = workbook.createSheet("测试");        // 第四步，创建单元格，并设置值表头 设置表头居中        CellStyle style = workbook.createCellStyle();        style.setAlignment(HorizontalAlignment.CENTER); // 水平居中格式        style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中        List<HeaderNode> nodes = new ArrayList<>();        HeaderNode headerNode1 = new HeaderNode();        headerNode1.setName("test1");        headerNode1.setFirstRow(0);        headerNode1.setLastRow(1);        headerNode1.setFirstCol(0);        headerNode1.setLastCol(5);        nodes.add(headerNode1);        HeaderNode headerNode34 = new HeaderNode();        headerNode34.setName("test4");        headerNode34.setFirstRow(3);        headerNode34.setLastRow(4);        headerNode34.setFirstCol(0);        headerNode34.setLastCol(5);        nodes.add(headerNode34);        HeaderNode headerNode2 = new HeaderNode();        headerNode2.setName("test2");        headerNode2.setFirstRow(2);        headerNode2.setLastRow(2);        headerNode2.setFirstCol(0);        headerNode2.setLastCol(4);        nodes.add(headerNode2);        HeaderNode headerNode3 = new HeaderNode();        headerNode3.setName("test3");        headerNode3.setFirstRow(2);        headerNode3.setLastRow(2);        headerNode3.setFirstCol(5);        headerNode3.setLastCol(5);        nodes.add(headerNode3);        generateHeader(nodes, sheet, style);        try {            FileOutputStream output = new FileOutputStream("e:\\workbook.xls");            workbook.write(output);            output.flush();        } catch (IOException e) {            e.printStackTrace();        }    }}

下面是导出报表及流下载的代码

       List<PoiUtil.HeaderNode> nodes = new ArrayList<>();        for (RpStyleItem item : styleItems){            PoiUtil.HeaderNode headerNode = new PoiUtil.HeaderNode();            headerNode.setFirstRow(item.getRow() - 1);            headerNode.setLastRow(headerNode.getFirstRow()+item.getSizeY() - 1);            headerNode.setFirstCol(item.getCol() - 1);            headerNode.setLastCol(headerNode.getFirstCol() + item.getSizeX() - 1);            headerNode.setName(item.getName());            nodes.add(headerNode);        }        // 第一步，创建一个webbook，对应一个Excel文件        // XSSFWorkbook针对于excel2007+        // HSSFWorkbook针对于Excel2003(数据超过65536会报错)        Workbook workbook = new XSSFWorkbook();        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet        Sheet sheet = workbook.createSheet(template.getName());        // 第三步，创建单元格样式        CellStyle style = workbook.createCellStyle();        style.setAlignment(HorizontalAlignment.CENTER); // 水平居中格式        style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中        int rowNum = PoiUtil.generateHeader(nodes,sheet,style);        String sql = template.getReportSql().replace("\n", " ");        String reportDate = com.jrq.components.date.DateUtil.dateFormat(new Date(), "yyyyMMdd");        //测试数据 String reportDate = "20170711";        List<Map<String, Object>> datas = reportService.listReportData(reportDate, sql);        for (Map<String, Object> map : datas){            Collection<Object> values = map.values();            int index = 0; //cell单元格索引            Row row = sheet.createRow(rowNum++);            for (Object obj : values){                row.createCell(index++).setCellValue(obj.toString());            }        }      /* 直接将将文件保存在本地，供测试样式使用        try {            OutputStream output=new FileOutputStream("e:\\workbook.xls");            workbook.write(output);            output.flush();            output.close();        } catch (IOException e) {            e.printStackTrace();        }*/        String downFileName = new String(template.getType().getName()+reportDate+".xls");        try {            //若不进行编码在IE下会乱码            downFileName = URLEncoder.encode(downFileName, "UTF-8");        } catch (UnsupportedEncodingException e) {            e.printStackTrace();        }        try {            // 清空response            response.reset();            response.setContentType("application/msexcel");//设置生成的文件类型            response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名            response.setHeader("Content-Disposition", "attachment; filename=" + downFileName);            OutputStream os=response.getOutputStream();            workbook.write(os);            os.flush();            os.close();        } catch (IOException e) {            LOG.error("文件流操作异常");            jr.setRet("文件流操作异常");            return jr;        }        jr.setSuc(JsonResponse.SUCCESS);        return jr;

下面是导入功能的代码

        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;        MultipartFile file = multipartRequest.getFile("fileUpload");        if (!file.isEmpty()) {            Workbook workbook = PoiUtil.createWorkbook(file.getInputStream());            if (null != workbook) {                List<List<Object>> list = PoiUtil.getDataList(workbook);}}

可以看到这里是前端上传了excel文件这里，前端的HTML也贴一下

   <form method="POST" enctype="multipart/form-data" id="form1"                                  action="/center/addition/list/uploadfile">                                <input class="typeahead scrollable" type="file" id="fileUpload"                                       name="fileUpload"/>                                <input type="submit" value="上传" id="btn_submit"/>                            </form>

阅读全文

0 0