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）的版本，扩展名是.xlsXSSFWorkbook:是操作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的列头字段保持一致，否则将读不到数据！