springboot实现Excel的导入导出

前言

Excel表单导入，是将Excel文件中的数据导入到数据库；而Excel表单导出，是将数据库的数据导出到Excel文件中。生成Excel比较有名的框架有Apache poi，EasyExcel
其中，EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称，因此，也受到了很多公司和个人的喜爱。

1.导入maven依赖

 <!--easyExcel-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.0-beta2</version>
    </dependency>
    
<!--fastjson-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.70</version>
    </dependency>

<!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.5</version>
    </dependency>

2.使用POI

使用POI进行数据写入

public void testWrite03() throws Exception {
        //1.创建一个工作簿，03版本使用HSSFWorkbook对象，07版本使用XSSFWorkbook对象
        Workbook workbook=new HSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("观众统计表");
        //3.创建第一行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);//（1，1）
        cell11.setCellValue("观众人数");
        Cell cell12 = row1.createCell(1); //（1，2）
        cell12.setCellValue(666);
        //创建第二行
        Row row2 = sheet.createRow(1);
        Cell cell21= row2.createCell(0);//（2，1）
        cell21.setCellValue("统计时间");
        Cell cell22= row2.createCell(1);//（2，2）
        String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);
        //生成一张表:03版本使用xls结尾，07版本使用xlsx结尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"观众统计表03.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("03表生成完毕！！");

    }

使用POI进行数据的读取

public void testRead07() throws Exception {

        //获取文件流
        FileInputStream inputStream=new FileInputStream("G:\\springboot\\Excel\\PoiExcel观众统计表07.xlsx");

         //1.创建一个工作簿，03版本使用HSSFWorkbook对象，07版本使用XSSFWorkbook对象
        Workbook workbook=new XSSFWorkbook(inputStream);
        //2.得到一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        //3.得到行
        Row row1 = sheet.getRow(0);
        //4.得到列，getStringCellValue:字符串类型，
        Cell cell11 = row1.getCell(0);//（1，1）
        System.out.println(cell11.getStringCellValue());
        //4.得到列，getNumericCellValue:数字类型，
        Cell cell12 = row1.getCell(1);//（1，1）
        System.out.println(cell12.getNumericCellValue());
        //关闭流
        inputStream.close();
    }

使用POI读取excel中不同数据类型的数据

    public void testCellType() throws Exception{
        FileInputStream inputStream=new FileInputStream("G:\\springboot\\Excel\\PoiExcel观众统计表03read.xls");
        //1.创建一个工作簿
        Workbook workbook=new HSSFWorkbook(inputStream);
        //2.得到一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        Row rowTitle = sheet.getRow(0);//3.获得标题行
        if (rowTitle!=null){
            int cellCount=rowTitle.getPhysicalNumberOfCells();//获得标题行的总列数
            for (int cellNum=0;cellNum<cellCount;cellNum++){
                Cell cell = rowTitle.getCell(cellNum);//获得标题行的每一列
                if (cell!=null){
                    int cellType=cell.getCellType();//获取每一单元格的数据类型
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+"|");
                }
            }
            System.out.println();
        }

        //获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();//获取行数
        for (int rowNum=1;rowNum<rowCount;rowNum++){//从第二行开始遍历
           Row rowData=sheet.getRow(rowNum);//获取每一列
           if (rowData!=null){
               int cellCount = rowTitle.getPhysicalNumberOfCells();//获取列数
               for (int cellNum=0;cellNum<cellCount;cellNum++){
                   
                   Cell cell=rowData.getCell(cellNum);//获取每一列

                   //匹配数据类型
                   if (cell!=null){
                       int cellType=cell.getCellType();
                       String cellValue="";

                       switch (cellType){
                           case HSSFCell.CELL_TYPE_STRING://字符串
                               System.out.println("STRING");
                               cellValue=cell.getStringCellValue();
                               break;
                           case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                               System.out.println("BOOLEAN");
                               cellValue=String.valueOf(cell.getBooleanCellValue());
                               break;
                           case HSSFCell.CELL_TYPE_BLANK://空
                               System.out.println("BLANK");
                               break;
                           case HSSFCell.CELL_TYPE_NUMERIC://数字（日期，普通数字）
                               System.out.println("NUMERIC");
                              if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                  System.out.println("日期");
                                  Date date = cell.getDateCellValue();
                                  cellValue=new DateTime(date).toString("yyyy-MM-dd");
                              }
                              else {
                                  System.out.println("数字,转换为字符串输出");
                                  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                  cellValue=cell.toString();
                              }
                               break;
                              
                           case HSSFCell.CELL_TYPE_ERROR://错误
                               System.out.println("数据类型错误");
                               break;
                       }

                       System.out.println(cellValue);
                   }
               }
           }
        }
        inputStream.close();
    }

3.使用EasyExcel

创建实体类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleDate;
    //忽略字段
    @ExcelIgnore
    private String ignore;
}

写入数据

public class TestEasy {
    
    //创建数据
    private List<DemoData> data(){
        List<DemoData> list=new ArrayList<DemoData>();
        for (int i=0;i<10;i++){
            DemoData data=new DemoData();
            data.setString("字符串"+i+1);
            data.setDate(new Date());
            data.setDoubleDate(0.26);
            list.add(data);
        }
        return list;
    }

    //根据list写入excel
    @Test
    public void simpleWrite(){
        //写法1
        String fileName="G:\\springboot\\Excel\\easytest.xlsx";
        //这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        //如果这里想使用03，则传入excelType参数即可
        //write(fileName,格式类)
        //sheet(表名)
        //doWrite(数据)
        EasyExcel.write(fileName,DemoData.class).sheet("模板").doWrite(data());
    }
 
}

读取数据

教程链接：https://www.yuque.com/easyexcel/doc/read

dao层：

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

监听器类：

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();

    private DemoDAO demoDAO;
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }




    //读取数据会执行invoke方法
    //DemoData数据类型
    //AnalysisContext分析上下文
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("解析到一条数据:"+JSON.toJSONString(data));

        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();//持久化
            // 存储完成清理 list
            list.clear();
        }
    }


    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

测试类：

    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = "G:\\springboot\\Excel\\easytest.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }