一. 概述

现在java后端主流框架为springboot, 而大部分企业管理系统设计都会设计excel的导入导出, 本文将介绍springboot整合Apache POI的使用

二. 使用

2.1 导包

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

2.2.1 excel导出示例

controller层

@RestController
@RequestMapping("/exportExcel")
public class exportExcel {
    @Autowired
    private CrateWorkbook crateWorkbook;
    @RequestMapping()
    public void exportExcel(HttpServletResponse response) throws Exception {
        // 创建工作表
        HSSFWorkbook workbook = crateWorkbook.crateWorkbook();
        // 文件名格式化
        String filename = URLEncoder.encode("template.xls", "UTF-8");
        OutputStream outputStream = response.getOutputStream();
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename="+filename);
        // 文件导出
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}

sevice层

@Service
public class CrateWorkbook {
    public HSSFWorkbook crateWorkbook() {
        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        String[] tableHeaders = {"id", "姓名", "年龄"};
        // 创建工作表:Sheet1
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        // 创建单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置水平对齐方式:中间对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐方式:中间对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置字体样式
        Font font = workbook.createFont();
        font.setColor(HSSFColor.RED.index);
        font.setBold(true);
        cellStyle.setFont(font);

        // 将第一行的三个单元格给合并
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
        HSSFRow row = sheet.createRow(0);
        // 设置表标题为: 通讯录
        HSSFCell beginCell = row.createCell(0);
        beginCell.setCellValue("通讯录");
        beginCell.setCellStyle(cellStyle);

        row = sheet.createRow(1);
        // 创建表头
        for (int i = 0; i < tableHeaders.length; i++) {
            HSSFCell cell = row.createCell(i);
            // 设置内容
            cell.setCellValue(tableHeaders[i]);
            // 设置格式
            cell.setCellStyle(cellStyle);
        }
        // 创建要插入的内容
        List<User> users = new ArrayList<>();
        users.add(new User(1L, "张三", 20));
        users.add(new User(2L, "李四", 21));
        users.add(new User(3L, "王五", 22));
        // 遍历插入内容
        for (int i = 0; i < users.size(); i++) {
            row = sheet.createRow(i + 2);
            User user = users.get(i);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getAge());
        }
        return workbook;
    }
}

2.3 excel导入示例

controller层

@RestController
@RequestMapping("/importExcel")
public class importExcel {
    @Autowired
    private ImportExcelUtil importExcelUtil;
    @RequestMapping()
    public Object importExcel(@RequestParam("file") MultipartFile file){
        // 获取文件输入流
        InputStream inputStream = file.getInputStream();
        // 获取缓冲流
        BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        // 获取工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        // 调用方法获取内容
        List<Map<String, Object>> result = importExcelUtil. importExcel(workbook,0);
        return result;
    }
}

sevice层

@Service
public class ImportExcelUtil {
    public List<Map<String, Object>> importExcel(Workbook wb, int sheetNum) {
        List<Map<String, Object>> dataLst = new ArrayList<>();
        // 获取指定工作表
        Sheet sheet = wb.getSheetAt(sheetNum);
        // 获取最后一行的行号(0开始)
        int totalRows = sheet.getLastRowNum();
        // 获取每行的列数, 获取内容行的格数
        int totalCells = sheet.getRow(2).getLastCellNum();

        // 获取标题行数据
        String[] head = new String[totalCells];
        // 获取首行
        Row headRow = sheet.getRow(0);
        // 遍历每个单元格的值
        for (int i = 0; i < totalCells; i++) {
            Cell cell = headRow.getCell(i);
            head[i] = getCellValue(cell);
        }

        // 遍历内容行获取数据,从2行开始,也就是行的下标为1
        for (int r = 1; r <= totalRows; r++) {
            headRow = sheet.getRow(r);
            if (null == headRow) {
                // 过滤空行,空行一下内容全部上移一行
                sheet.shiftRows(r + 1, totalRows, -1);
                r--;
                totalRows --;
                continue;
            }

            // 过滤空行, 当前行每个单元格的值都为空时, 当前行一下行全部往上移一行
            int count = 0;
            for (int i = 0; i < totalCells; i++) {
                // 获取当前单元格
                Cell cell = headRow.getCell(i);
                // 调用方法获取数值
                String cellValue = this.getCellValue(cell);
                if (null == cellValue || "".equals(cellValue)) {
                    count++;
                }
            }
            if (count == totalCells) {
                if (r + 1 > totalRows) {
                    break;
                }
                sheet.shiftRows(r + 1, totalRows, -1);
                r--;
                totalRows --;
                continue;

            }

            // 开始遍历行的单元格值
            HashMap<String, Object> map = new HashMap<String, Object>();
            // 遍历行单元格
            for (int c = 0; c < totalCells; c++) {
                Cell cell = headRow.getCell(c);
                String cellValue = this.getCellValue(cell);
                map.put(head[c], cellValue);
            }
            dataLst.add(map);
        }
        return dataLst;
    }
    // 根据单元格的格式获取值
    public String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                // 数值
                cellValue = cell.getNumericCellValue() + "";
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    cellValue = sdf.format(date);
                }else {
                    // 数字,表格中返回的数字类型是科学计数法,因此需要转换
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#.##########");
                        cellValue = format.format(value);
                    } else if (temp.equals("000000")) {
                        format.applyPattern("000000");
                        cellValue = format.format(value);
                    } else {
                        DataFormatter formatter = new DataFormatter();
                        cellValue = formatter.formatCellValue(cell);
                    }
                }
                break;

            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;

            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;

            case FORMULA: // 公式
//                    cellValue = cell.getCellFormula() + "";
                    try {
                        // 数字,表格中返回的数字类型是科学计数法,因此需要转换
                        double value = cell.getNumericCellValue();
                        CellStyle style = cell.getCellStyle();
                        DecimalFormat format = new DecimalFormat();
                        String temp = style.getDataFormatString();
                        // 单元格设置成常规
                        if (temp.equals("General")) {
                            format.applyPattern("#.##########");
                            cellValue = format.format(value);
                        } else if (temp.equals("000000")) {
                            format.applyPattern("000000");
                            cellValue = format.format(value);
                        } else {
                            DataFormatter formatter = new DataFormatter();
                            cellValue = formatter.formatCellValue(cell);
                        }
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                    break;

            case BLANK: // 空值
                cellValue = "";
                break;

            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
            }
        }
        return cellValue;
    }
}

三. 常用方法封装

下面介绍都是返回Workbook, 同学们可以更具需求再次封装
参考文章: 简单使用Apache POI

public class ExcelUtil {
    public static final int Excel2003 = 0;
    public static final int Excel2007 = 1;

    /**
     * 获取输出流
     *
     * @param response
     * @param fileName
     * @return
     * @throws IOException
     */
    public static ServletOutputStream getServletOutputStream(HttpServletResponse response, String fileName) throws IOException {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    }

/**
     * 创建一个新的Workbook, 设置标题列
     * @param headName
     * @return
     */
    public static Workbook createWorkbookModel(List<String> headNames){
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表:Sheet1
        Sheet sheet = workbook.createSheet("Sheet1");
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置水平对齐方式:中间对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐方式:中间对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置字体样式
        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setBold(true);
        cellStyle.setFont(font);

        Row row = sheet.createRow(0);
        // 创建表头
        for (int i = 0; i < headNames.size(); i++) {
            org.apache.poi.ss.usermodel.Cell cell = row.createCell(i);
            // 设置内容
            cell.setCellValue(headNames.get(i));
            // 设置格式
            cell.setCellStyle(cellStyle);
        }
        return workbook;
    }

    /**
     * 根据版本号，获取Excel poi对象
     * 
     * @param edition 版本号
     * @param in  输入流
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(int edition, InputStream in) throws IOException {
        if (edition == 0) {
            return new HSSFWorkbook(in);
        } else if (edition == 1) {
            return new XSSFWorkbook(in);
        }
        return null;
    }

    // 根据单元格的格式获取值
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    // 数值
                    cellValue = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 处理日期格式、时间格式
                        SimpleDateFormat sdf = null;
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        cellValue = sdf.format(date);
                    } else if (cell.getCellStyle().getDataFormat() == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {
                        // 数字,表格中返回的数字类型是科学计数法,因此需要转换
                        double value = cell.getNumericCellValue();
                        CellStyle style = cell.getCellStyle();
                        DecimalFormat format = new DecimalFormat();
                        String temp = style.getDataFormatString();
                        // 单元格设置成常规
                        if (temp.equals("General")) {
                            format.applyPattern("#.##########");
                            cellValue = format.format(value);
                        } else if (temp.equals("000000")) {
                            format.applyPattern("000000");
                            cellValue = format.format(value);
                        } else {
                            DataFormatter formatter = new DataFormatter();
                            cellValue = formatter.formatCellValue(cell);
                        }
                    }
                    break;

                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;

                case BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;

                case FORMULA: // 公式
//                    cellValue = cell.getCellFormula() + "";
                    try {
                        // 数字,表格中返回的数字类型是科学计数法,因此需要转换
                        double value = cell.getNumericCellValue();
                        CellStyle style = cell.getCellStyle();
                        DecimalFormat format = new DecimalFormat();
                        String temp = style.getDataFormatString();
                        // 单元格设置成常规
                        if (temp.equals("General")) {
                            format.applyPattern("#.##########");
                            cellValue = format.format(value);
                        } else if (temp.equals("000000")) {
                            format.applyPattern("000000");
                            cellValue = format.format(value);
                        } else {
                            DataFormatter formatter = new DataFormatter();
                            cellValue = formatter.formatCellValue(cell);
                        }
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                    break;

                case BLANK: // 空值
                    cellValue = "";
                    break;

                case ERROR: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }

    /**
     * 从指定excel表格中逐行读取数据
     * 
     * @param workbook  工作簿
     * @param startRow  开始行
     * @param startCol   开始列
     * @param indexSheet  工作表下标
     * @return
     */
    public static List<List<String>> getExcelString(Workbook workbook, int startRow, int startCol, int indexSheet) {
        List<List<String>> stringTable = new ArrayList<List<String>>();
        // 获取指定表对象
        Sheet sheet = workbook.getSheetAt(indexSheet);
        // 获取最大行数
        int rowNum = sheet.getLastRowNum();
        for (int i = startRow; i <= rowNum; i++) {
            List<String> oneRow = new ArrayList<String>();
            Row row = sheet.getRow(i);
            // 根据当前指针所在行数计算最大列数
            int colNum = row.getLastCellNum();
            for (int j = startCol; j <= colNum; j++) {
                // 确定当前单元格
                Cell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                // 生成一行数据
                oneRow.add(cellValue);
            }
            stringTable.add(oneRow);
        }
        return stringTable;
    }

    /**
     * 根据给定的数据直接生成workbook
     * 
     * @param workbook  工作簿
     * @param sheetName  工作表名字
     * @param data   数据
     * @return
     */
    public static Workbook createExcel(Workbook workbook, String sheetName, List<List<String>> data) {
        Sheet sheet = workbook.createSheet(sheetName);
        for (int i = 0; i < data.size(); i++) {
            List<String> oneRow = data.get(i);
            Row row = sheet.createRow(i);
            for (int j = 0; j < oneRow.size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(oneRow.get(j));
            }
        }
        return workbook;
    }
    /**
     * 给单元格设置背景色/内容/批注
     * @param wb 工作表
     * @param cell 单元格
     * @param sheet 工作蒲
     * @param annotation 批注内容
     * @param content 单元格内容
     */
    public static void setCellStyle(XSSFWorkbook wb,XSSFCell cell,XSSFSheet sheet,String annotation,String content) {
        // 创建绘图对象
        XSSFDrawing p = sheet.createDrawingPatriarch();
        // 插入单元格内容
        cell.setCellValue(content);
        // 获取批注对象
        // (int dx1, int dy1, int dx2, int dy2, short col1, int row1, short
        // col2, int row2)
        // 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
        XSSFComment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) 5, 5, (short) 10, 10));
        // 输入批注信息
        comment.setString(new XSSFRichTextString(annotation));
        // 添加作者,选中B5单元格,看状态栏
//      comment.setAuthor("toad");
        // 将批注添加到单元格对象中
        cell.setCellComment(comment);
        CellStyle cellStyle=wb.createCellStyle();
        // 设置背景色为红色
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置单元格样式
        cell.setCellStyle(cellStyle);
    }

    /**
     * 往指定的sheet表中插入数据，插入的方法是提供一组valueMap。int[]是2维数组代表需要插入的数据坐标，从0开始
     * 
     * @param workbook
     * @param sheetIndex
     * @param valueMap
     * @return
     */
    public static Workbook insertExcel(Workbook workbook, int sheetIndex, Map<int[], String> valueMap) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Iterator<Entry<int[], String>> it = valueMap.entrySet().iterator();
        while (it.hasNext()) {
            Entry<int[], String> cellEntry = it.next();
            int x = cellEntry.getKey()[0];
            int y = cellEntry.getKey()[1];
            String value = cellEntry.getValue();
            Row row = sheet.getRow(y);
            Cell cell = row.getCell(x);
            cell.setCellValue(value);
        }
        return workbook;
    }

    /**
     * 设置指定行的行高
     * 
     * @param workbook
     * @param rowHeight
     * @param sheetIndex
     * @param rowIndex
     * @return
     */
    public static Workbook setRowHeight(Workbook workbook, int rowHight, int sheetIndex, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        row.setHeight((short) rowHight);
        return workbook;
    }

    /**
     * 设置列宽
     * 
     * @param workbook
     * @param columnWidth
     * @param sheetIndex
     * @param columnIndex
     * @return
     */
    public static Workbook setColumnWidth(Workbook workbook, int columnWidth, int sheetIndex, int columnIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.setColumnWidth(columnIndex, columnWidth);
        return workbook;
    }

    /**
     * 删除指定行
     * 
     * @param workbook
     * @param sheetIndex
     * @param rowIndex
     * @return
     */
    public static Workbook removeRow(Workbook workbook, int sheetIndex, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum) {
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
        }
        if (rowIndex == lastRowNum) {
            sheet.removeRow(sheet.getRow(rowIndex));
        }
        return workbook;
    }

    /**
     * 在指定位置插入空白行
     * 
     * @param workbook
     * @param sheetIndex
     * @param rowIndex
     * @return
     */
    public static Workbook insertBlankRow(Workbook workbook, int sheetIndex, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex <= lastRowNum) {
            sheet.shiftRows(rowIndex, lastRowNum, 1);
            // 获得上一行的Row对象
            Row preRow = sheet.getRow(rowIndex - 1);
            short rowNum = preRow.getLastCellNum();
            Row curRow = sheet.createRow(rowIndex);
            // 新生成的Row创建与上一个行相同风格的Cell
            for (short i = preRow.getFirstCellNum(); i < rowNum; i++) {
                Cell cell = preRow.getCell(i);
                CellStyle style = cell.getCellStyle();
                curRow.createCell(i).setCellStyle(style);
            }
            return workbook;
        }
        return null;
    }

    /**
     * 根据sheet(0)作为模板重建workbook
     * 
     * @param workbook
     * @param sheetNum
     * @param sheetNames
     * @return
     */
    public static Workbook rebuildWorkbook(Workbook workbook, int sheetNum, String... sheetNames) {
        if(sheetNames.length == sheetNum){
            for (int i = 0; i < sheetNum; i++) {
                workbook.cloneSheet(0);
                // 生成后面的工作表并指定表名
                workbook.setSheetName(i + 1, sheetNames[i]);
            }
            // 删除第一张工作表
            workbook.removeSheetAt(0);
            return workbook;
        }
        return null;
    }
}

四. 附:excel导入出现科学计数值处理工具类

    public static Pattern pattern1 = Pattern.compile("\\d{1,10}\\.?\\d{0,5}");
    public final static Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");
    public final static DecimalFormat ds = new DecimalFormat("0");
    public final static boolean isENum(String input) {//判断输入字符串是否为科学计数法
        return pattern.matcher(input).matches();
    }

    /**
     * 判断是否为科学数值, 是则进行科学计数法转十进制数字
     * @param num 值
     * @return
     */
    public static String numConvert(String num){
        if(null != num){
            if(isENum(num)){
                Double d = 1.6D;
                //使得结果精确的初始化姿势
                BigDecimal bigDecimal = new BigDecimal(d.toString());
                bigDecimal = new BigDecimal(num);
                String str = bigDecimal.toPlainString();
                return subZeroAndDot(str);
            }
            return subZeroAndDot(num);
        }else {
            return "0";
        }
    }
    /**
     * 使用java正则表达式去掉多余的.与0
     * @param s
     * @return
     */
    public static String subZeroAndDot(String s){
        if(s.indexOf(".") > 0){
            s = s.replaceAll("0+?$", "");//去掉多余的0
            s = s.replaceAll("[.]$", "");//如最后一位是.则去掉
        }
        return s;
    }

五. 附: Workbook转InputStream

public InputStream workbookToInputStream(Workbook workbook){
        InputStream inputStream = null;
        try{
            //临时缓冲区
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            //创建临时文件
            workbook.write(out);
            byte [] bookByteAry = out.toByteArray();
            inputStream = new ByteArrayInputStream(bookByteAry);
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return inputStream;
    }

六. 附: 导出文件名带中文处理

        // 文件名
        String fileNameutf = new String(fileName.getBytes(), "UTF-8");
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+".xlsx");