poi实现excel的导入导出

网上有很多java整合excel的文章，但好多使用的过期方法（比如格式转换，我这用的是poi的枚举类进行switch，不是过期方法，用cell.getCellType()获取类型是过期方法了)，自己重新将前端后端所需要的方法都重写了一遍并且加了很多注解。

1、导包

        <!--上传文件-->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <!--EXCEL操作-->
        <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>

2、前端模板(文件名：excel.jsp)

<a href="${pageContext.request.contextPath}/download">导出用户信息</a>
        <form id="upload_form" action="${pageContext.request.contextPath}/upload" enctype="multipart/form-data" method="post">
            <div >
                <label class="col-sm-2 control-label">表格上传</label>${msg}
                <input type="file" id="file" name="file" multiple="multiple" />
            </div>
                <button type="submit" class="btn btn-success btn-block bgc-color">导入用户表</button>
        </form>

3、控制层(UserController.java)

(导出方法)

    /**
     * excel文件导出
     * 
     * @param response 响应对象
     */
    @RequestMapping(value = "/download", method = RequestMethod.GET)
    public void download(HttpServletResponse response) {
        final Office office = new Office();
        // 转换二进制数据，由于没有键值，所以，一次只能下载一个文件
        response.setContentType("application/binary;charset=UTF-8"); //更改响应头，相当于Content-Type:application/octet-stream，
        try {
            List<Map<String, Object>> mapList = BeanChange.objectsToMaps(userService.selAllUser()); //Bean转换成List<Map<String, Object>>集合
            //设置文件头：最后一个参数是设置下载文件名(这里是由年月日时分秒组成)
            response.setHeader("Content-Disposition", "attachment;fileName="
                    + URLEncoder.encode(new DateTime().toString("yyyyMMddHHmmss") + ".xls", "UTF-8"));
            office.writeExcel(mapList, response,"用户表",0);
        } catch (Exception e) {
            LOGGER.error("download fail:{}",e);
            e.printStackTrace();
        }
    }

(导入方法)

    /**
     * excel文件导入
     *
     * @param file 文件
     * @return 返回excel页面
     */
    @RequestMapping(value = "/upload", method = RequestMethod.POST)
    public String upload(MultipartFile file, Model model) {
        if (file.getSize() <= 0) {
            model.addAttribute("msg", "请添加文件");
        } else {
            Boolean b = userService.uploadExcel(file);
            if (b) {
                model.addAttribute("msg", "上传成功");
            } else {
                model.addAttribute("msg", "上传失败");
            }
        }
        return "excel";
    }

(导入方法的UserService.java)

public Boolean uploadExcel(MultipartFile file) {
        final Office office = new Office();
        try {
            final List<String[]> list = office.readExcel(file);
            final Boolean b = office.uploadExcel(file, list.get(0)[0]); //将文件保存在服务器
            if (b) {
                for (int i = 2; i < list.size(); i++) {   //根据用户表添加用户
                    User user = new User();
                    user.setAccount(list.get(i)[0]);
                    user.setUsername(list.get(i)[1]);
                    user.setPassword(Md5Utils.encodePasswordUseMd5(list.get(i)[2], list.get(i)[0], 2));
                    user.setReam(list.get(i)[3]);
                    userDao.addDao(user);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

4、工具类（office.java）

(导入方法)

    private final static String XLS = "xls";    //后缀名 03版本
    private final static String XLSX = "xlsx";  //后缀名 07版本

 
   /**
     * 上传excel文档
     * 将文档复制到服务器上
     *
     * @param file   文件
     * @param header excel的标题头
     * @return Boolean 是否复制成功
     */
    public Boolean uploadExcel(MultipartFile file, String header) {
        final File file2 = new File("D:/fileupload/" + header + new DateTime().toString("yyyyMMddHHmmss") + ".xlsx");
        //复制路径以及文件名
        try {
            //复制到本地
            FileUtils.copyInputStreamToFile(file.getInputStream(), file2);
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.error("copy file on local fail:{}",e);
            return false;
        }
        return true;
    }

    /**
     * 读入excel文件，解析后返回
     *
     * @param file 文件
     * @return List<String> 数据集合
     * @throws IOException IO流异常
     */
    public List<String[]> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        final Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = Lists.newArrayList();
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            //获得当前sheet工作表
            final Sheet sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null || sheet.getLastRowNum() == 0) continue;
            //bug:当一个excel文件有几张表格都有数据时候，会被覆盖掉，只返回最后一张有数据的表格
            list = sortRow(sheet);
        }
        workbook.close();
        return list;

    }

    /**
     * 检查文件是不是excel文件
     *
     * @param file 文件
     * @throws IOException IO异常
     */
    private void checkFile(MultipartFile file) throws IOException {
        //获得文件名
        final String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
            LOGGER.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 创建workbook
     * 根据后缀名的区分来获取不同的Workbook实现类对象
     *
     * @param file 文件
     * @return workbook实现类对象
     */
    private Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        final String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            final InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(XLS)) {
                workbook = new HSSFWorkbook(is);     //2003
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(is);    //2007
            }
        } catch (IOException e) {
            LOGGER.error("get workbook fail:{}",e);
        }
        return workbook;
    }

    /**
     * 解析整张表格数据
     *
     * @param sheet 表格
     * @return List<String> 数据集合
     */
    private List<String[]> sortRow(Sheet sheet) {
        //行集合
        final List<String[]> list2 = Lists.newArrayList();
        //获得当前sheet的开始行
        final int firstRowNum = sheet.getFirstRowNum();
        //获得当前sheet的结束行
        final int lastRowNum = sheet.getLastRowNum();
        //循环所有行
        for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
            //获得当前行
            final Row row = sheet.getRow(rowNum);
            if (row == null) continue;
            String[] strings = sortCell(row);
            list2.add(strings);
        }
        return list2;
    }

    /**
     * 处理单行数据
     *
     * @param row 行对象
     * @return String[] 数据数组
     */
    private String[] sortCell(Row row) {
        //获得当前行的开始列
        final int firstCellNum = row.getFirstCellNum();
        //获得当前行的列数
        final int lastCellNum = row.getPhysicalNumberOfCells();
        //创建数据数组
        final String[] cells = new String[row.getPhysicalNumberOfCells()];
        //循环当前行
        for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            cells[cellNum] = getCellValue(cell);
        }
        return cells;
    }

    /**
     * 格式转换
     *
     * @param cell 单元格对象
     * @return String 单元格值
     */
    private String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

(导出方法)

private Workbook wk = null;   // XSSFWorkbook 是07年之后的版本，后续.xlsx, HSSWorkbook 是03版本后缀.xls
    /**
     * 输出excel文档
     *
     * @param mapList  bean对象转换过来的集合
     * @param response 响应对象
     * @param header   标题头
     * @param rowNum   起始行
     */
    public void writeExcel(List<Map<String, Object>> mapList, HttpServletResponse response, String header, int rowNum) {
        //创建工作簿
        wk = new HSSFWorkbook();
        //创建表名
        final Sheet sheet = wk.createSheet("用户表");
        //插入数据
        sortListMap(mapList, sheet, rowNum + 1);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, sheet.getRow(rowNum + 1).getLastCellNum() - 1));
        //设置表头值
        creatCell(sheet, rowNum, 0, header);
        //设置列的自适应宽度
        for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
            sheet.autoSizeColumn(i, true);
        }
        try (ServletOutputStream out = response.getOutputStream()) {  //输出流
            //workbook输出
            wk.write(out);
        } catch (Exception e) {
            LOGGER.error("out workbook fail:{}",e);
            e.printStackTrace();
        }
    }

    /**
     * 处理表格数据
     *
     * @param mapList     数据
     * @param sheet       表格
     * @param firstRowNum 除表头的起始行
     */
    private void sortListMap(List<Map<String, Object>> mapList, Sheet sheet, int firstRowNum) {
        List<String> list = null;
        int rowNum = firstRowNum;
        for (Map<String, Object> aMapList : mapList) {
            list = Lists.newArrayList(aMapList.keySet());
            //map键排序
            list.sort(String::compareTo);
            //按map键的顺序插入数据
            for (Map.Entry<String, Object> entry : aMapList.entrySet()) {
                creatCell(sheet, rowNum + 1, list.indexOf(entry.getKey()), entry.getValue().toString());
            }
            rowNum++;
        }
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {    //map键添加到次行
                creatCell(sheet, firstRowNum, i, list.get(i));
            }
        }
    }

    /**
     * 创建单元格
     *
     * @param sheet   表对象
     * @param rowNum  行号
     * @param cellNum 列号
     * @param value   值
     */
    private void creatCell(Sheet sheet, int rowNum, int cellNum, String value) {
        Row row;
        if (sheet.getRow(rowNum) != null) {
            row = sheet.getRow(rowNum);
        } else {
            row = sheet.createRow(rowNum);
        }
        Cell cell = row.createCell(cellNum);
        cell.setCellValue(value);
        cell.setCellStyle(changeCellStyle());  //单元格设置单元格属性
    }

    /**
     * 设置单元格格式
     *
     * @return CellStyle 单元格格式对象
     */
    private CellStyle changeCellStyle() {
        final CellStyle cellStyle = wk.createCellStyle();  //设置单元格属性
        cellStyle.setAlignment(HorizontalAlignment.CENTER);    //文本水平居中
        return cellStyle;
    }