java操作excel之poi读取excel

1.介绍

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2.依赖

首先引入maven依赖：

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

3.工具类ExcelUtils

package com.tl.util.core.helper;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel工具类
 * <p/>
 */
public class ExcelUtils {

    /**
     * EXCEL第一行索引
     */
    public static final int EXCEL_FIRST_ROW_INDEX = 0;

    public static final String TRUE_VALUE = "Y";

    public static final String FALSE_VALUE = "N";


    /**
     * 获取单元格字符串内容
     *
     * @param cell
     * @return
     */
    private static String getCellValue(HSSFCell cell) {
        String value = "";
        // 注意：一定要设成这个，否则可能会出现乱码
        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);

        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                    } else {
                        value = "";
                    }
                } else {
                    long longVal = Math.round(cell.getNumericCellValue());
                    if (Double.parseDouble(longVal + ".0") == cell.getNumericCellValue())
                        value = String.valueOf(longVal);
                    else
                        value = String.valueOf(cell.getNumericCellValue());
                    //value =cell.getStringCellValue();
                }
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                // 导入时如果为公式生成的数据则无值
                if (!cell.getCellFormula().equals("")) {
                    try {
                        value = String.valueOf(cell.getNumericCellValue());
                    } catch (Exception ex) {
                        value = cell.getStringCellValue();
                    }
                } else {
                    value = cell.getStringCellValue() + "";
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                value = "";
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                value = (cell.getBooleanCellValue() ? TRUE_VALUE : FALSE_VALUE);
                break;
            default:
                value = "";
        }

        return value;
    }


    /**
     * 去掉字符串右边的空格
     *
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */
    private static String rightTrim(String str) {
        if (str == null) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (str.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }

    /**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     *
     * @param inputStream 读取数据的源Excel
     * @param headerIndex 表单头所在的索引
     * @return 读出的Excel中数据的内容
     * @throws IOException
     */
    public static List<Map<String, String>> parse(InputStream inputStream, int headerIndex) throws IOException {
        List<Map<String, String>> result = new ArrayList<Map<String, String>>();
        int maxcolumnIndex = 0;
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFCell cell = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet st = wb.getSheetAt(sheetIndex);
            List<String> headers = new ArrayList<String>();
            for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                int currentcolumnIndex = row.getLastCellNum() + 1;
                if (currentcolumnIndex > maxcolumnIndex) {
                    maxcolumnIndex = currentcolumnIndex;
                }
                Map<String, String> rows = new LinkedHashMap<String, String>();

                for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                    String value = "";
                    cell = row.getCell(columnIndex);
                    if (cell != null) {
                        value = getCellValue(cell);
                    }
                    if (rowIndex == headerIndex && value != null && !value.trim().equals("")) {
                        headers.add(value.trim());
                    } else {
                        if (columnIndex < headers.size()) {
                            rows.put(headers.get(columnIndex), value);
                        }
                    }
                }
                
                if (!isEmptyMap(rows)) {
                    result.add(rows);
                }
            }
        }
        return result;
    }


    private static boolean isEmptyMap(Map map) {
        boolean empty = map.isEmpty();
        if (!empty) {
            Collection values = map.values();
            for (Object value : values) {
                if (value != null && !value.toString().equals("")) {
                    return false;
                }
            }
            return true;
        }
        return empty;
    }
}

注意事项：

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
所以上面的代码仅支持excel2003，因为excel具有向下兼容的特性，同时也可以将07版的转为03版。

4.测试

 /**
     * 员工excel数据读取并导入staff表中
     *
     * @param request
     * @param stream
     * @param consumer
     * @return
     */
    @Override
    public ImportExcelResult importStaff(ImportStaffRequest request, InputStream stream, Consumer<ImportExcelResult> consumer) {
        ImportExcelResult result = new ImportExcelResult();
        List<Map<String, String>> data = null;

        try {
            data = ExcelUtils.parse(stream, ExcelUtils.EXCEL_FIRST_ROW_INDEX);
        } catch (Exception ex) {
            result.addFail("读取excel文件失败:" + ex.getMessage());
            consumer.accept(result);
            throw new ImportFailedException("读取excel文件失败:" + ex.getMessage());
        }
        if (data == null) {
            result.addFail("读取excel失败,或excel为空\"");
            consumer.accept(result);
            throw new ImportFailedException("读取excel失败,或excel为空");
        }

        result.setRowQuantity(data.size());
        for (Map<String, String> row : data) {
            String departmentName = row.get("部门名称");
            String code = row.get("人员编号");
            String name = row.get("人员姓名");
            String positionName = row.get("人员职位");
            String certificate = row.get("身份证号码");
            String sex = row.get("性别");
            String education = row.get("学历");
            String hiredate = row.get("入职年月");
            int age = Integer.valueOf(row.get("年龄"));
            String domicile = row.get("户籍");
            String address = row.get("现居地址");
            String mobile = row.get("手机号");

            Position position = positionRepository.findOneByName(positionName);
            if (position == null) {
                result.addFail("职位名称信息缺失:" + positionName);
                consumer.accept(result);
                continue;
            }
            Department department = departmentRepository.findOneByName(departmentName);
            if (position == null) {
                result.addFail("部门名称信息缺失:" + positionName);
                consumer.accept(result);
                continue;
            }

            Staff staff = findByCode(code);
            if (staff == null) {
                staff = new Staff();
            }

            if (Strings.isNotBlank(hiredate)) {
                Date date = strToDate(hiredate);
                staff.setHiredate(date);
            }

            staff.setCode(code);
            staff.setDepartment(department.getId());
            staff.setPosition(position.getId());
            staff.setRealname(name);
            staff.setCertificate(certificate);
            staff.setSex(Staff.Sex.fromDescription(sex));
            staff.setEducation(Staff.Education.fromDescription(education));
            staff.setDomicile(domicile);
            staff.setAge(age);
            staff.setAddress(address);
            staff.setMobile(mobile);
            staff.setUser(request.getUser());

            store(staff);
            result.addSuccess();
            consumer.accept(result);
        }
        return result;
    }

需要注意的是在读取list结果集的时候，获取某一列的数据的时候，一定要跟excel的列头字段保持一致，否则将读不到数据！