org.apache.poi 实现Excel导入导出

刺骨的言语ヽ痛彻心扉 2022-05-21 09:51 252阅读 0赞

*  **使用包**

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

*  **导入代码**  
    
    
     *  工具类入口添加了不同参数，主要解析方法为analysis()和analysisToMap()方法.
     *  analysis()解析成List< List >集合:通过get(列数)获取值
     *  analysisToMap()解析成List< Map<标题,标题对应值> >:通过get(标题)获取值(每次通过list下标获取值，表示很不舒服就写了这个方法，在内容对应的格子没有标题的情况下就获取不到值(一般也不会这么写表格吧))

/** * excel 数据解析 * * @param path excel路径 * @return * @throws IOException * @throws InvalidFormatException */
        public static List<List> analysisExcel(String path)
                throws IOException, InvalidFormatException {
            InputStream is = new FileInputStream(path);
            Workbook wb = WorkbookFactory.create(is);
            return analysis(wb, 0);
        }
    
        /** * excel 数据解析 * * @param file excel * @return * @throws IOException * @throws InvalidFormatException */
        public static List<List> analysisExcel(File file)
                throws IOException, InvalidFormatException {
            Workbook wb = WorkbookFactory.create(file);
            return analysis(wb, 0);
        }
    
    
        /** * excel 数据解析 * * @param path excel路径 * @return * @throws IOException * @throws InvalidFormatException */
        public static List<Map<String, Object>> analysisExcelToMap(String path)
                throws IOException, InvalidFormatException {
            InputStream is = new FileInputStream(path);
            Workbook wb = WorkbookFactory.create(is);
            return analysisToMap(wb, 0);
        }
    
        /** * excel 数据解析 * * @param file excel * @return * @throws IOException * @throws InvalidFormatException */
        public static List<Map<String, Object>> analysisExcelToMap(File file)
                throws IOException, InvalidFormatException {
            Workbook wb = WorkbookFactory.create(file);
            return analysisToMap(wb, 0);
        }
    
        /** * excel 数据解析 * * @param inputStream excel路径 * @return * @throws IOException * @throws InvalidFormatException */
        public static List<List> analysisExcel(InputStream inputStream)
                throws IOException, InvalidFormatException {
            Workbook wb = WorkbookFactory.create(inputStream);
            return analysis(wb, 0);
        }
    
        /** * excel 数据解析 * * @param inputStream excel路径 * @return * @throws IOException * @throws InvalidFormatException */
        public static List<Map<String, Object>> analysisExcelToMap(InputStream inputStream)
                throws IOException, InvalidFormatException {
            Workbook wb = WorkbookFactory.create(inputStream);
            return analysisToMap(wb, 0);
        }
    
        /** * 主体解析 * * @param wb * @param page 页 0 开始 * @return */
        private static List<List> analysis(final Workbook wb, int page) {
            int numberOfSheets = wb.getNumberOfSheets();
            List<List> result = new ArrayList<>(numberOfSheets);
            Sheet sheetAt = wb.getSheetAt(page);
            if (null == sheetAt) {
                return null;
            }
            // 遍历行Row
            int lastRowNum = sheetAt.getLastRowNum();
            for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
                Row row = sheetAt.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                // 遍历列Cell
                short lastCellNum = row.getLastCellNum();
                if (lastCellNum == -1) {
                    continue;
                }
                List rowList = new ArrayList(lastCellNum);
                for (int cellNum = 0; cellNum <= lastCellNum; cellNum++) {
                    Cell hssfCell = row.getCell(cellNum);
                    if (null == hssfCell) {
                        continue;
                    }
                    rowList.add(getValueCell(hssfCell));
                }
                result.add(rowList);
            }
            return result;
    
        }
    
        /** * 主体解析 * * @param wb * @param page 页 0 开始 * @return */
        private static List<Map<String, Object>> analysisToMap(final Workbook wb, int page) {
            int numberOfSheets = wb.getNumberOfSheets();
            List<Map<String, Object>> result = new ArrayList<>(numberOfSheets);
            Sheet sheetAt = wb.getSheetAt(page);
            if (null == sheetAt) {
                return null;
            }
            // 遍历行Row
            int lastRowNum = sheetAt.getLastRowNum();
            List title = null;
            for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
                Row row = sheetAt.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                // 遍历列Cell
                short lastCellNum = row.getLastCellNum();
                if (lastCellNum == -1) {
                    continue;
                }
                //如果是第一排则存到标题List里
                if (rowNum == 0) {
                    title = new ArrayList<>(lastCellNum);
                    for (int cellNum = 0; cellNum <= lastCellNum; cellNum++) {
                        Cell hssfCell = row.getCell(cellNum);
                        if (null == hssfCell) {
                            continue;
                        }
                        title.add(getValueCell(hssfCell));
                    }
                }
                HashMap<String, Object> rowMap = new HashMap<>(lastCellNum);
                for (int cellNum = 0; cellNum <= lastCellNum; cellNum++) {
                    Cell hssfCell = row.getCell(cellNum);
                    if (null == hssfCell) {
                        continue;
                    }
                    rowMap.put(!isEntityCollection(title) && cellNum < title.size() && null != title
                            .get(cellNum) ? title
                            .get(cellNum).toString() : String.valueOf(cellNum), getValueCell(hssfCell));
                }
                result.add(rowMap);
            }
            return result;
        }
    
        /** * 提取单元格中的值 * @param cell 每一行对象 */
        private static Object getValueCell(Cell cell) {
            int cellType = cell.getCellType();
            switch (cellType) {
                //String类型
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
                //String类型
                case Cell.CELL_TYPE_ERROR:
                    return cell.getErrorCellValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue();
                    }
                    HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                    return dataFormatter.formatCellValue(cell);
                case Cell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                default:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    return cell.getStringCellValue();
            }
        }

**导入代码示例(这里使用的是Spring mvc 上传文件导入)**

/** * 导入excel解析 * * @param file 文件 * @return * @throws Exception */
        @RequestMapping(value = "/importPro", method = RequestMethod.POST)
        @ResponseBody
        public HashMap<String, Object> importProduct(MultipartFile file) {
            if (file.isEmpty()) {
                return JsonWrapper.failureWrapper("msg", "文件为空");
            }
            List<List> analysisExcel;
            try {
                analysisExcel = BeanUtil.analysisExcel(file.getInputStream());
            } catch (IOException | InvalidFormatException e) {
                e.printStackTrace();
                return JsonWrapper.failureWrapper("msg", "解析表格出错");
            }
            //获取第一页表格
            return contractManager.analysisProduct(analysisExcel);
        }

*  **导出代码**  
    
    
     *  其中setResponseExportExcelHeader方法主要是设置response的header下载信息

/** * 导出表格 * * @param sheetName 表格名称 * @param title 标题 * @param values 值 List<Map<标题,值>> * @return */
        public static HSSFWorkbook exportExcel(String sheetName, String[] title,
                List<Map<String, Object>> values) {
            Assert.notNull(sheetName);
            Assert.notNull(title);
            Assert.notNull(values);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
            sheet.setColumnWidth(0, 20 * 256);
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = hssfWorkbook.createCellStyle();
            // 创建一个居中格式
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font = hssfWorkbook.createFont();
            //粗体显示style.setFont(font)
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row = sheet.createRow(0);
            //创建标题
            for (int i = 0; i < title.length; i++) {
                //声明列对象
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(style);
            }
            //创建内容
            int i = 1;
            for (Map<String, Object> content : values) {
                //创建行
                HSSFRow dataRow = sheet.createRow(i);
                //将内容按顺序赋给对应的列对象
                for (int index = 0; index < title.length; index++) {
                    //声明列对象
                    Object val = content.get(title[index]);
                    if (null == val) {
                        val = "";
                    }
                    dataRow.createCell(index).setCellValue(
                            val instanceof Date ? DateUtil.format((Date) val, "yyyy-MM-dd hh:mm:ss")
                                    : val.toString());
                }
                i++;
            }
            return hssfWorkbook;
        }
    
        /** * 设置Excel 下载 header * * final HSSFWorkbook hssfWorkbook = BeanUtil.exportExcel("测试导出", new String[]{"1", "2", "3", "4"}, content); * try { * BeanUtil.setResponseExportExcelHeader(response, "测试"); * OutputStream os = response.getOutputStream(); * hssfWorkbook.write(os); * os.flush(); * os.close(); * } catch (IOException e) { * e.printStackTrace(); * log.error("出错",e) * } * * @param response HttpServletResponse * @param name 表格名称 * @return * @throws UnsupportedEncodingException */
        public static HttpServletResponse setResponseExportExcelHeader(HttpServletResponse response,
                String name) throws UnsupportedEncodingException {
            Assert.notNull(response);
            Assert.notNull(name);
            String s = new String((name + ".xls").getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + s);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            return response;
        }

**导出示例(Srping mvc 下载)**

/** * 导出已生成链接 * * @param userId * @return */
        public HSSFWorkbook exportExcel(final String userId) {
            final WebUser webUser = webUserDao.get(userId);
            if (null == webUser) {
                return new HSSFWorkbook();
            }
            //查出数据
            List<HashMap> popularizeLinks = popularizeLinkDao
                    .getLinkDetail(webUser.getPartnerClientCode());
            String[] titles = new String[]{
       "ID", "渠道名称", "书名/页面名", "链接", "原文访问人数", "充值金额", "是否强制关注",
                    "关注章节", "创建时间"};
            List<Map<String, Object>> contents = new ArrayList<>(popularizeLinks.size());
            //组装数据
            for (HashMap<String, Object> map : popularizeLinks) {
                HashMap<String, Object> data = new HashMap<>(9);
                data.put("ID", map.get("LINK_CODE"));
                data.put("渠道名称", map.get("POPULARIZE_NAME"));
                data.put("书名/页面名", map.get("CARICATURE_NAME"));
                data.put("链接", map.get("SHORT_LINK"));
                data.put("原文访问人数", map.get("LINK_CLIICKS"));
                data.put("充值金额", map.get("moneyNum"));
                final Object attention = map.get("ATTENTION");
                data.put("是否强制关注", 1 == (Byte) attention ? "是" : "否");
                data.put("关注章节", map.get("ATTENTION_CHAPTER_CODE"));
                data.put("创建时间", map.get("CREATE_TIME"));
                contents.add(data);
            }
            return BeanUtil.exportExcel("已生成对推广链接", titles, contents);
        }
    
        /** * 下载导出数据 * * @param response * @param userToken * @throws IOException */
        @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
        public void exportExcel(HttpServletResponse response, @RequestParam String userToken)
                throws IOException {
            if (StringUtils.isBlank(userToken)) {
                response.sendRedirect("/404.jsp");
            }
            final UserToken token = BaseUtil.analysisUserToken(userToken);
            if (null == token) {
                response.sendRedirect("/404.jsp");
            }
            final HSSFWorkbook hssfWorkbook = popularizePartnerManager.exportExcel(token.getUserId());
            try {
                //上面代码中调用
                BeanUtil.setResponseExportExcelHeader(response, "已生成对推广链接");
                OutputStream os = response.getOutputStream();
                hssfWorkbook.write(os);
                os.flush();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
                log.error("生成表格出错", e);
            }
        }

###### 这里前端只需要使用跳转api地址：window.location.href = “xxxx/xxxx/xxxx” 即可实现下载 ######

目前测试使用，基本的可以使用，在判断类型出了几次错，这里需要注意（比如时间），代码并不太好，希望有错误帮我及时指出，谢谢！

###### 附上博客设置一些样式代码博客：[导入表格样式][Link 1] ######

[Link 1]: https://blog.csdn.net/liyong199012/article/details/17962139