Java导入Excel文档到数据库

• 前言
• 业务背景
• • 如何导入
• 认识需要接触到的类
• • Workbook.class
  • • HSSFWorkbook.class
    • XSSFWorkbook.class
    • SXSSFWorkbook.class
  • Sheet.class
  • Row.class
  • Cell.class
• 测试结果
• • Excel文件内的数据
  • 对应实体类Student.class
  • 测试类
  • Maven依赖
  • 测试结果
• 导入工具类的实现 -- ExcelImportSheet.class
• 导入数据库
• 写在最后

前言

如果有时间紧，任务重，直接看结果和实现即可。
好了，上任鹅城！

业务背景

在日常的业务环节中，不可避免地要接触到对Excel表格的操作，比如将Excel文件中的数据导入到数据库，或者将数据库中的数据导出成Excel文件给客户下载。今天我们暂时先聊聊导入的环节。

如何导入

如果是导入Excel的话，最主要的业务逻辑是：将一行的数据放入对应的实体类中，遍历所有的行执行以下这种放入操作即可。简单的流程如下：

看起来是不是很简单，可能只有下面这个写的有点模糊。

再次分析以下大概就是这样了

至此，导入的大概流程应该没问题了，具体细节我们代码中来分析。

认识需要接触到的类

Workbook.class

还是照常点开这个接口类的源码，看看最上面的注释

High level representation of a Excel workbook.
 This is the first object most users will construct whether 
 they are reading or writing a workbook. 
 It is also the top level object for 
 creating new sheets/etc.
/**
Excel工作簿的高级表示。
这是大多数用户在读或写工作簿时首先要构造的对象。
它也是创建新工作表等的顶层对象。
**/

可以近似看成一个Excel文件对应一个Workbook。我们前面说了，这是一个接口，那么其具体的实现类才是我们真正用到的对象。
我们看看Workbook的实现类有哪些。

啊？竟然是三个，就像我当初的谈过的三个女朋友：一个喜欢我的，但那时候我想要自由，想要天边的云，远处的海，山那头的风景；
一个我喜欢的，但她打呼噜；
还有一个相互喜欢却没走到最后的…

emo五分钟…好了回到正题。

我们知道Excel文件对应的后缀有两种：xls和xlsx。

不同后缀对应上面不同的实现类，但上面的实现类有三种，后缀只有两种，难道有升级版吗？没错就是升级版！

HSSFWorkbook.class

该对象对应的是xls后缀的文件，为2003版的Excel。
说起2003，那时候我还很年轻（甚至在穿开裆裤），我还头发还有很多，我还。。。

XSSFWorkbook.class

此类对应的是xlsx后缀的文件，是2007年版本的Excel。
说起2007，那年我还很开心，我还没戴眼镜，我视力很好，我。。。

SXSSFWorkbook.class

该类也是对应的xlsx后缀的文件，对应2010版的Excel。相较于XSSFWorkbook，其对于大文件的处理方案更加完善，尽可能避免了文件过大时导致的内存溢出。

说起2010，。。。好像没什么说的。

没想到吧，诶，蚌住了。继续继续

Sheet.class

我们知道一个Excel文件，有多个Sheet，什么是Sheet不知道？
Sheet就是Holy shit，翻译过来就是神圣的**

回到正题，先看注释

High level representation of a Excel worksheet.
Sheets are the central structures within a workbook, 
and are where a user does most of his spreadsheet work.
The most common type of sheet is the worksheet,
which is represented as a grid of cells.
Worksheet cells can contain text, numbers,
dates, and formulas.
Cells can also be formatted.
/*
Excel工作表的高级表示。
工作表是工作簿中的中心结构，用户在其中完成电子表格的大部分工作。
最常见的工作表类型是工作表，
它表示为单元格网格。
工作表单元格可以包含文本、数字、日期和公式。
单元格也可以被格式化。
*/

我们再打开一个Excel文件，看左下角，有的只有一个，但点击右侧的加号就会创建一个新的，我这里有四个，还可以自己命名。

也就是说一个Sheet就对应一张工作表，也能理解为一个Workbook中包含有多个Sheet。

好的，现在就该从里面取出数据了，Java说万物皆对象，那么猜猜Sheet里面有什么对象呢？

三文鱼举手：老师，老师，我知道！（夹着嗓子）

老师： 好，三文鱼同学，你来回答。

三文鱼：Holy Shfit!

老师：？？？

给我叉出去！

好的，回到正题，表嘛，那不得有行、有列对象吗？

Row.class

点开注释，瞅瞅类最上面的注释，很明显，我不翻译都知道，这是行类！

High level representation of a row of a spreadsheet.
//电子表格一行的高级表示。

妈蛋，POI的类全都是High level开头，全员高级是吧！

我们再来看列，我找找啊方法啊。。。

额。。。没找到。。。不过我找到了另一个Cell

Cell.class

注释来咯，哈哈哈哈哈哈哈哈哈哈哈

High level representation of a cell in a row of 
a spreadsheet.
Cells can be numeric, formula-based or string-based (text). 
The cell type specifies this. 
String cells cannot conatin numbers and numeric cells
cannot contain strings (at least according to our model). 
Client apps should do the conversions themselves.
Formula cells have the formula string,
as well as the formula result,
 which can be numeric or string.
 /*
 一行中单元格的高级表示
电子表格。
单元格可以是数字的、基于公式的或基于字符串的(文本)。
单元格类型指定了这一点。
字符串单元格不能包含数字和数值单元格
不能包含字符串(至少根据我们的模型)。
客户端应用程序应该自己进行转换。
公式单元格具有公式字符串，
以及公式结果，
可以是数字或字符串。
 */

也就是说，Workbook中用Cell代表一个单元格，也就是我们Excel中见到的一个个小格子
你看这单元格，让我想起来杨宗纬的《空白格》：我想你是爱我的，我猜你也舍不得~~

阿珍再爱我一次！

测试结果

好了，话不多说，直接发车。

Excel文件内的数据

要跟实体类里的对应

对应实体类Student.class

要跟excel表里的对应 属性只能比excel表里的列多，不能比列少

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

/**
 * @author 三文鱼先生
 * @title
 * @description
 * @date 2022/8/16
 **/
@Setter
@Getter
@ToString
public class Student {
    private String id;
    private String name;
    private String subject;
    private String className;
    private double grade;
    //是否缺课
    private boolean ifCutClass;
    private String teacher;
}

测试类

记得填写自己的路径，别用我的，男人的电脑不能给别人碰！

import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

/**
 * @author 三文鱼先生
 * @title
 * @description
 * @date 2022/8/11
 **/
public class TestForParseExcel {
    public static void main(String[] args) throws ClassNotFoundException, InvocationTargetException, InstantiationException, IllegalAccessException, NoSuchMethodException, IOException {
        Map<String , String> map = new HashMap<>();
        //表头与键值对的映射关系
        map.put("学号", "id");
        map.put("姓名" , "name");
        map.put("科目" , "subject");
        map.put("分数" , "grade");
        map.put("班级" , "className");
        map.put("任课教师" , "teacher");
        map.put("是否缺课" , "ifCutClass");
        try(
                //这里面的对象会自动关闭
                InputStream in = new FileInputStream(new File("F:\\学习记录\\测试数据\\Student.xlsx"));
                //用流来构建工作簿对象
                Workbook workbook = ExcelImportSheet.getTypeFromExtends(in , "Student.xlsx")
                ) {

            //根据名称获取单张表对象 也可以使用getSheetAt(int index)获取单张表的对象 获取第一张表
            Sheet sheet = workbook.getSheetAt(0);
            List<Student> list = ExcelImportSheet.getListFromExcel(sheet , Student.class , map);

            for (Student student : list) {
                //底层数据库操作 insert什么的
                System.out.println(student.toString());
            }
        }catch(IOException exception) {
            exception.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //写着好看的
        }
    }

}

Maven依赖

客官：依赖呢？

三文鱼：说出来能换个赞吧？

客官：快说！

三文鱼：有用的让我放进去了，比没用的还有用！

客官：没用的呢？

三文鱼：当场也放进去了！

客官：啊！！！啊！！！！

…

    <dependencies>

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

        <!-- poi 读取word doc-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.15</version>
        </dependency>

        <!-- poi 读取word docx-->
<!--        <dependency>-->
<!--            <groupId>fr.opensagres.xdocreport</groupId>-->
<!--            <artifactId>xdocreport</artifactId>-->
<!--            <version>1.0.6</version>-->
<!--        </dependency>-->

        <!-- poi xml-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>


        <dependency>
            <groupId>fr.opensagres.xdocreport</groupId>
            <artifactId>org.apache.poi.xwpf.converter.xhtml</artifactId>
            <version>1.0.6</version>
        </dependency>

    </dependencies>

测试结果

这里可以看到，数据没什么问题了，取出来的都是Student对象。

导入工具类的实现 – ExcelImportSheet.class

白嫖怪：你这代码多少钱一斤

三文鱼：两块钱一斤

白嫖怪：卧槽，你这代码是cv做的还是vc做的？

三文鱼：这可都是手撸的代码，你要不要吧！你要不要？

白嫖怪：居然是手撸，来个工具类。

三文鱼：十五斤，五北。

…

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @author 三文鱼先生
 * @title
 * @description 导入工具类
 * @date 2022/8/17
 **/
public class ExcelImportSheet {

    /**
     * @description 根据文件后缀获取相应的Workbook对象
     * @author 三文鱼先生
     * @date 9:46 2022/8/17
     * @param in 用于构建Workbook对象的输入流
     * @param fileName 文件名称
     * @return org.apache.poi.ss.usermodel.Workbook
     **/
    public static Workbook getTypeFromExtends(InputStream in , String fileName) throws Exception {
        String[] str = fileName.split("\\.");
        //获取文件后缀
        String extend = str[1];
        if(extend.equals("xls")) {
            //2003版的excel
            return new HSSFWorkbook(in);
        } else if(extend.equals("xlsx")){
            //2007版的excel
            return new XSSFWorkbook(in);
        }else {
            throw new Exception("请检查文件类型是否正确。");
        }
    }

    /**
     * @description 将单个sheet里的数据获取到List<T>的泛型列表里面
     * @author 三文鱼先生
     * @date 9:47 2022/8/17
     * @param sheet 单个的工作表
     * @param cs 生成的对象类名
     * @param map 表头与对象属性映射
     * @return java.util.List<T>
     **/
    public static <T> List<T> getListFromExcel(Sheet sheet , Class cs , Map<String , String> map) throws Exception {
        T e;
        List<T> list = new ArrayList<>();
        //根据第一行获取表头对应的属性顺序
        List<String> paramsList = getMethodFromFirstRow(sheet , map);
        //根据类和属性顺序的List 获取属性对应的类型属性
        List<Class> typeClass = getParamsType(cs , paramsList);
        //遍历所有行 从第二行开始 首行是表头字段
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
            //单元行
            Row row = sheet.getRow(i);
            //一行对应一个T，将对象强转为泛型
            e = (T) cs.newInstance();
            //遍历单元行的每一列 设置值给泛型e
            for (int j = 0; j < row.getLastCellNum(); j++) {
                //获取一个单元格
                Cell cell = row.getCell(j);
                //调用泛型对象的set方法设置单元格里的值 这也就是为什么我们要获取属性顺序以及其对应的类型
                cs.getMethod(getSetterMethodName(paramsList.get(j)) , typeClass.get(j))
                        .invoke(e , getValueFromType(cell , typeClass.get(j)));
            }
            list.add(e);
        }
        return list;
    }

    /**
     * @description 获取属性的setter方法
     * @author 三文鱼先生
     * @date 9:54 2022/8/17
     * @param param 属性
     * @return java.lang.String 返回一个setXxx
     **/
    public static String getSetterMethodName(String param) {
        char[] chars = param.toCharArray();
        //首字母大写
        if(Character.isLowerCase(chars[0])) {
            chars[0] -= 32;
        }
        //拼接set方法
        return "set" + new String(chars);
    }

    /**
     * @description 从第一行（表头）获取字段对应的属性的顺序
     * @author 三文鱼先生
     * @date 9:51 2022/8/17
     * @param sheet 工作表
     * @param map 表头字段与对象属性的映射
     * @return java.util.List<java.lang.String> 属性的集合
     **/
    public static List<String> getMethodFromFirstRow(Sheet sheet , Map<String,String> map) throws Exception {
        //获取表头
        Row row = sheet.getRow(sheet.getFirstRowNum());
        //获取到的属性列表
        List<String> paramsList = new ArrayList<>();
        //遍历表头
        for(int i = row.getFirstCellNum(); i  < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            //获取行字符串的值
            String str = cell.getStringCellValue();
            //键值对映射获取对应方法名称
            if(map.containsKey(str)) {
                //获取对应属性的set方法
                paramsList.add(map.get(str));
            } else {
                throw new Exception("请检查首行数据是否正确。");
            }
        }
        return  paramsList;
    }

    /**
     * @description  根据对象和属性顺序列表，返回对应顺序的参数类型List
     * @author 三文鱼先生
     * @date 9:55 2022/8/17
     * @param cs 对象类
     * @param paramsList 表头对应的属性顺序List
     * @return java.util.List<java.lang.Class>
     **/
    public static List<Class> getParamsType(Class cs , List<String> paramsList) {
        List<Class> typeClass = new ArrayList<>();
        //对象的所有属性
        Field[] fields = cs.getDeclaredFields();
        //临时的属性 - 类型映射
        Map<String , Class> map = new HashMap();
        //获取属性名称及类型
        for (Field field : fields) {
            map.put(field.getName(), field.getType());
        }
        //遍历属性List获取对应的类型List
        for (String s : paramsList) {
            typeClass.add(map.get(s));
        }
        return typeClass;
    }


    /**
     * @description 根据对应的Class获取将对应的值类型
     * @author 三文鱼先生
     * @date 9:59 2022/8/17
     * @param cell
     * @param cs
     * @return java.lang.Object
     **/
    public static Object getValueFromType(Cell cell , Class cs) {
        //字符串类型
        if (String.class.equals(cs)) {
            //设置对应的类型
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        } else if(boolean.class.equals(cs)){
            //boolean类型
            cell.setCellType(CellType.BOOLEAN);
            return cell.getBooleanCellValue();
        }else if (Date.class.equals(cs)) {
            //日期类型 此种数据并未测试
            return cell.getDateCellValue();
        } else if (int.class.equals(cs) || Integer.class.equals(cs)){
            //int类型
            cell.setCellType(CellType.NUMERIC);
            return (int)cell.getNumericCellValue();
        } else if(double.class.equals(cs) || Double.class.equals(cs)) {
            //double类型
            cell.setCellType(CellType.NUMERIC);
            return cell.getNumericCellValue();
        }
        //这里还可以填充其他类型
        else {
            //未知类型 默认为错误类型
            return cell.getErrorCellValue();
        }
    }
}

导入数据库

这一部分的话应该问题不大了，可以看看我之前的文章：Mybatis中对数据库的增删改查

写在最后

做起来不难，但是要点时间。其实这个工具类还有点其他的问题，诸如：

只能获取单张表的List

表头和属性把必须一一对应，其实可能有的业务里面只需要Excel表中的几列

没有用到上面的提过的XSSFWorkbook.class

没有对单元格内的公式进行处理

…

等等问题。

针对上述的部分问题，我已经有了大致思路：

第一个问题只能获取单张表的List，将上面的三个参数，都用List再包裹一层即可，就是参数传递可能会麻烦一点

表头和属性必须一一对应的问题，用一个数组记录下有用的列就好了，遇到的不是该数组里面的列跳过就行

…

客官：那你为什么不做呢？

三文鱼：因为我懒~

好了，本文就到这里了，我们下次再见~