使用poi读取Excel中的数据到数据库

大家好今儿给大家带来的是使用poi读取Excel中的数据到数据库

1.poi简单介绍

POI是Apache出品的一个开源的专门用来操作我们Microsoft Office格式档案读和写的功能(本期只介绍读取Excel数据)。

2.poi操作excel

首先在poi中操作Excel有两个对象一个是操作高版本的一个是操作低版本的分别为 HSSFWorkbook是操作xls版本的,XSSFWorkbook是操作xlsx版本的(除了这两个对象不同其他的api还是一样的)

3.代码部分(可直接用)

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

LinkedList<LinkedList> 返回值 里面的LinkedList是每行的数据 自己根据下标去取就可以了

/**
 * @author dxl
 */
public class ExcelUtil {

    

    /***
     * LinkedList<LinkedList<String>> 返回值 里面的LinkedList<String>是每行的数据 自己根据下标去取就可以了
     */


    /**
     * xlsx版本
     *
     * @param bytes    要读取Excel的文件流
     * @param startRow 从那一行开始读
     * @author dxl
     */
    public static LinkedList<LinkedList<String>> getExcelXlsx(byte[] bytes, int startRow) throws Exception {

        //最后返回数据的集合
        LinkedList<LinkedList<String>> flagList = Lists.newLinkedList();

        //获取Excel工作表
        XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bytes));

        //获取第一个工作表
        XSSFSheet sheetAt = workbook.getSheetAt(0);

        //拿到工作表中的索引循环
        for (int i = startRow; i <= sheetAt.getLastRowNum(); i++) {
            //拿到每一行
            XSSFRow row = sheetAt.getRow(i);
            if (row != null) {

                //创建封装行数据 集合 下面cell 循环一次那么就有一行数据
                LinkedList<String> list = new LinkedList<>();

                //每一个单元格
                for (Cell cell : row) {
                    if (cell != null) {
                        String cellValue = getCellValue(cell);
                        //判断一下是否是空
                        if (StringUtils.isNotEmpty(cellValue)) {
                            list.add(Optional.ofNullable(cellValue).orElse(""));
                        }
                    }
                }
                flagList.add(list);
            }
        }
        return flagList;
    }


    /**
     * xlsx版本
     *
     * @param bytes    要读取Excel的文件流
     * @param startRow 从那一行开始读
     * @author dxl
     */
    public static LinkedList<LinkedList<String>> getExcelXls(byte[] bytes, int startRow) throws Exception {

        //最后返回数据的集合
        LinkedList<LinkedList<String>> flagList = Lists.newLinkedList();

        //获取Excel工作表
        HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

        //获取第一个工作表
        HSSFSheet sheetAt = workbook.getSheetAt(0);

        //拿到工作表中的索引循环
        for (int i = startRow; i <= sheetAt.getLastRowNum(); i++) {
            //拿到每一行
            HSSFRow row = sheetAt.getRow(i);
            if (row != null) {

                //创建封装行数据 集合 下面cell 循环一次那么就有一行数据
                LinkedList<String> list = new LinkedList<>();

                //每一个单元格
                for (Cell cell : row) {
                    if (cell != null) {
                        String cellValue = getCellValue(cell);
                        //判断一下是否是空
                        if (StringUtils.isNotEmpty(cellValue)) {
                            list.add(Optional.ofNullable(cellValue).orElse(""));
                        }
                    }
                }
                flagList.add(list);
            }
        }
        return flagList;
    }


    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                //short s = cell.getCellStyle().getDataFormat();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        throw new RuntimeException("日期格式错误!!!");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = null;
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}

4.测试

 @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public void importExcel(@RequestPart("file") MultipartFile file) throws Exception {

        //从第一行开始读
        LinkedList<LinkedList<String>> excelXls = ExcelUtil.getExcelXls(file.getBytes(), 1);
        if (!excelXls.isEmpty()) {
            excelXls.forEach(excel -> {
                //使用构造赋值去存数据库
                excelDao.insert(new User(Long.parseLong(excel.get(0)),excel.get(1),excel.get(2),excel.get(3),new Date(excel.get(4))));
            });
        }

    }

5.总结

使用poi读取excel中的数据要注意版本不同的情况，以及处理数据时要注意特殊的数据。