• springboot实现excel数据导入导出

    目录

    1.一些关于excel的常识

    2.使用Apache POI操作excel

    3.使用easyexcel操作excel

    4.结合mybatis-plus批量导入excel数据到数据库

    1.一些关于excel的常识

    首先关于excel的文件格式，分为xls和xlsx，分别对应03版本和07以后的版本。

    03版本的excel最大行数限制为表格共有65536行，256列；而07版本的excel则无此限制。

    关于excel中的单元格格式，一般为以下几类：

    转为java代码时，可以初略地处理为：字符串、数值（普通数字、日期）、公式三类。由于不同的格式对应不同的java数据类型，所以读取时需要进行格式的转换。

    2.使用Apache POI操作excel

    Apache POI是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改EXCEL文件。简单来说Apache POI 提供Java操作Excel进行读写的解决方案。

    首先，我们需要知道的是，不同版本的excel对应POI不同版本下的依赖：

    1.         
    2.         <dependency>
    3.             <groupId>org.apache.poigroupId>
    4.             <artifactId>poiartifactId>
    5.             <version>3.9version>
    6.         dependency>
    7.         
    8.         <dependency>
    9.             <groupId>org.apache.poigroupId>
    10.             <artifactId>poi-ooxmlartifactId>
    11.             <version>3.9version>
    12.         dependency>

    操作excel对象时，也对应不同的类：

    1. //创建工作蒲（03版）
    2. Workbook workbook_03 = new HSSFWorkbook();
    3. //创建工作蒲（07版）
    4. Workbook workbook_07 = new XSSFWorkbook();

    （1）使用POI实现excel写：

    1. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    2. import org.apache.poi.ss.usermodel.Cell;
    3. import org.apache.poi.ss.usermodel.Row;
    4. import org.apache.poi.ss.usermodel.Sheet;
    5. import org.apache.poi.ss.usermodel.Workbook;
    6. import org.joda.time.DateTime;
    7. import java.io.FileOutputStream;
    8.  
    9. public class ExcelWriter {
    10.  
    11.     private static final String path = "C:/Users/14125/Desktop/";
    12.  
    13.     public static void main(String[] args) throws Exception {
    14.         //创建工作蒲（03版）
    15.         Workbook workbook = new HSSFWorkbook();
    16.         //Workbook workbook_07 = new XSSFWorkbook();
    17.         //创建工作表
    18.         Sheet sheet = workbook.createSheet();
    19.         //创建行/列
    20.         Row row1 = sheet.createRow(0);
    21.         Cell cell_11 = row1.createCell(0);
    22.         cell_11.setCellValue("测试一下");
    23.         Cell cell_12 = row1.createCell(1);
    24.         cell_12.setCellValue("测试两下");
    25.  
    26.         Row row2 = sheet.createRow(1);
    27.         Cell cell_21 = row2.createCell(0);
    28.         cell_21.setCellValue(new DateTime().toString("yyyy-MM-dd"));
    29.  
    30.         //生成一张表（IO流）
    31.         FileOutputStream fileOutputStream = new FileOutputStream(path+"java_excel.xls");
    32.         workbook.write(fileOutputStream);
    33.         fileOutputStream.close();
    34.     }
    35. }

    逻辑上很简单，创建工作蒲——》创建表——》创建行——》创建列，从而确定一个单元格——》往单元格写入值——》生成表，使用文件流输出——》关闭流。

     （1）使用POI实现excel读：

    1. import org.apache.poi.ss.usermodel.*;
    2. import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
    3. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    4. import org.joda.time.DateTime;
    5.  
    6. import java.io.FileInputStream;
    7. import java.io.IOException;
    8. import java.util.Date;
    9.  
    10. public class ExcelReader {
    11.  
    12.     private static final String path = "C:/Users/14125/Desktop/";
    13.  
    14.     public static void main(String[] args) throws IOException {
    15.         //获取文件流
    16.         FileInputStream fileInputStream = new FileInputStream(path+"test_read.xlsX");
    17.  
    18.         //获取工作蒲对象
    19.         Workbook workbook = new XSSFWorkbook(fileInputStream);
    20.         Sheet sheet = workbook.getSheetAt(1);
    21.         //获取计算公式
    22.         FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    23.  
    24.         Row rowTitle = sheet.getRow(0);
    25.         if(rowTitle!=null) {
    26.             //获取一行中数据数
    27.             int cellCount = rowTitle.getPhysicalNumberOfCells();
    28.             //获取标题行
    29.             for (int i = 0; i < cellCount; i++) {
    30.                 Cell cell = rowTitle.getCell(i);
    31.                 if (cell != null) {
    32.                     //都为String，直接读取
    33.                     System.out.print(cell.getStringCellValue()+"| ");
    34.                 }
    35.             }
    36.             System.out.println();
    37.  
    38.             //获取行数
    39.             int rowCount = sheet.getPhysicalNumberOfRows();
    40.  
    41.             //第二行非标题行开始遍历
    42.             for (int i = 1; i < rowCount; i++) {
    43.                 Row rowData = sheet.getRow(i);
    44.                 if(rowData!=null){
    45.                     for (int j = 0; j < cellCount; j++) {
    46.                         Cell cell = rowData.getCell(j);
    47.                         if (cell != null) {
    48.                             //匹配单元格的数据类型
    49.                             int cellType = cell.getCellType();
    50.                             switch (cellType) {
    51.                                 //字符串。直接读取
    52.                                 case Cell.CELL_TYPE_STRING:
    53.                                     String StringCellValue = cell.getStringCellValue();
    54.                                     System.out.print(StringCellValue+"| ");
    55.                                     break;
    56.                                 //数字类型，转换
    57.                                 case Cell.CELL_TYPE_NUMERIC:
    58.                                     // 判断是否是日期
    59.                                     if (DateUtil.isCellDateFormatted(cell)) {
    60.                                         Date DateCellValue = cell.getDateCellValue();
    61.                                         //转换日期格式
    62.                                         System.out.print(new DateTime(DateCellValue)
    63.                                                 .toString("yyyy-MM-dd hh:mm:ss")+"| ");
    64.                                     }else {
    65.                                         //重新设置单元格的数据类型
    66.                                         cell.setCellType(Cell.CELL_TYPE_STRING);
    67.                                         //转换成字符串获取，防止数字过长
    68.                                         System.out.print(cell.toString()+"| ");
    69.                                     }
    70.                                     break;
    71.                                 //boolean类型
    72.                                 case Cell.CELL_TYPE_BOOLEAN:
    73.                                     boolean booleanCellValue = cell.getBooleanCellValue();
    74.                                     System.out.print(booleanCellValue+"| ");
    75.                                     break;
    76.                                 //公式类型
    77.                                 case Cell.CELL_TYPE_FORMULA:
    78. //                                    //计算
    79. //                                    CellValue evaluate = formulaEvaluator.evaluate(cell);
    80. //                                    //获取计算后的值
    81. //                                    String cellValuate = evaluate.formatAsString();
    82. //                                    System.out.println(cellValuate+"| ");
    83.                                     //重新设置单元格的数据类型
    84.                                     double value = cell.getNumericCellValue();
    85.                                     //转换成字符串获取，防止数字过长
    86.                                     System.out.print(value+"| ");
    87.                                     break;
    88.                                 //空白
    89.                                 case Cell.CELL_TYPE_BLANK:
    90.                                     break;
    91.                                 // 匹配不能转换的错误
    92.                                 case Cell.CELL_TYPE_ERROR:
    93.                                     break;
    94.                             }
    95.                         }
    96.                     }
    97.  
    98.                 }
    99.                 System.out.println();
    100.             }
    101.         }
    102.         //关闭流
    103.         fileInputStream.close();
    104.     }
    105. }

    上述代码逻辑如上：创建文件流——》获取excel工作簿——》获取表头行——》 获取有内容单元格行数——》遍历所有行——》在每一行中获取有内容单元格列数——》遍历该行所有单元格——》判断单元格格式，并输出内容——》直到遍历所有单元格内容

    上述代码中，核心代码为判断单元格格式，转为可处理的格式并进行输出：

    1.         //匹配单元格的数据类型
    2.         switch (cellType) {
    3.             //字符串。直接读取
    4.             case Cell.CELL_TYPE_STRING:
    5.                 String StringCellValue = cell.getStringCellValue();
    6.                 System.out.print(StringCellValue+"| ");
    7.                 break;
    8.             //数字类型，转换
    9.             case Cell.CELL_TYPE_NUMERIC:
    10.                 // 判断是否是日期
    11.                 if (DateUtil.isCellDateFormatted(cell)) {
    12.                     Date DateCellValue = cell.getDateCellValue();
    13.                     //转换日期格式
    14.                     System.out.print(new DateTime(DateCellValue)
    15.                             .toString("yyyy-MM-dd hh:mm:ss")+"| ");
    16.                 }else {
    17.                     //重新设置单元格的数据类型
    18.                     cell.setCellType(Cell.CELL_TYPE_STRING);
    19.                     //转换成字符串获取，防止数字过长
    20.                     System.out.print(cell.toString()+"| ");
    21.                 }
    22.                 break;
    23.             //boolean类型
    24.             case Cell.CELL_TYPE_BOOLEAN:
    25.                 boolean booleanCellValue = cell.getBooleanCellValue();
    26.                 System.out.print(booleanCellValue+"| ");
    27.                 break;
    28.             //公式类型
    29.             case Cell.CELL_TYPE_FORMULA:
    30.                 //计算
    31.                 CellValue evaluate = formulaEvaluator.evaluate(cell);
    32.                 //获取计算后的值
    33.                 String cellValuate = evaluate.formatAsString();
    34.                 System.out.println(cellValuate+"| ");
    35.                 //重新设置单元格的数据类型
    36.                 double value = cell.getNumericCellValue();
    37.                 //转换成字符串获取，防止数字过长
    38.                 System.out.print(value+"| ");
    39.                 break;
    40.             //空白
    41.             case Cell.CELL_TYPE_BLANK:
    42.                 break;
    43.             // 匹配不能转换的错误
    44.             case Cell.CELL_TYPE_ERROR:
    45.                 break;
    46.         }
    47.     }

    实际开发中，我们可以将上述代码封装为一个工具类，传入一个excel文件流参数即可进行后续的调用。若在存储数据库，即在 Switch中转换格式后进行存储即可

    3.使用easyexcel操作excel

    EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。可以让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

    首先引入依赖，而easyexcel中已经包含了Apache poi相关依赖：

    1.         
    2.         <dependency>
    3.             <groupId>com.alibabagroupId>
    4.             <artifactId>easyexcelartifactId>
    5.             <version>2.2.0-beta2version>
    6.         dependency>

    （1）使用easyexcel写：

    首先，创建实体类，@ExcelProperty(value="列名称",index = 列坐标)；若是包含子标题的复杂标题，使用方法为：@ExcelProperty({"主标题", "字符串标题"})。

    @ExcelIgnore 标签可以让实体类写入时，忽略该字段，不进行写入

    1. @Data
    2. @AllArgsConstructor
    3. @NoArgsConstructor
    4. public class Student {
    5.  
    6.     @ExcelProperty(index = 0)
    7.     private Integer id;
    8.  
    9.     @ExcelProperty(index = 1)
    10.     private String age;
    11.  
    12.     @ExcelProperty(index = 2)
    13.     private String name;
    14.  
    15.     @ExcelProperty(index = 3)
    16.     private String gender;
    17. }

    easyexecl进行代码如下：

    1. String fileName = TestFileUtil.getPath() + "complexHeadWrite" + System.currentTimeMillis() + ".xlsx";
    2. // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    3. EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data());

    4.结合mybatis-plus批量导入excel数据到数据库

     （2）使用easyexcel读：

    首先，创建实体类：

    1. @Data
    2. @AllArgsConstructor
    3. @NoArgsConstructor
    4. @TableName("student")
    5. public class Student {
    6.  
    7.     @TableId(value = "id",type = IdType.AUTO)
    8.     @ExcelProperty(index = 0)
    9.     private Integer id;
    10.  
    11.     @ExcelProperty(index = 1)
    12.     private String age;
    13.  
    14.     @ExcelProperty(index = 2)
    15.     private String name;
    16.  
    17.     @ExcelProperty(index = 3)
    18.     private String gender;
    19. }

    此处，为了简便mybatis-plus和easyexcel使用了同一个实体类，实际中应该为easyexcel封装一个dto，mybatis-plus单使用一个实体类，更为合理。

    编写listener代码：

    1. import com.alibaba.excel.context.AnalysisContext;
    2. import com.alibaba.excel.event.AnalysisEventListener;
    3. import com.alibaba.fastjson.JSON;
    4. import com.seven.excel.dao.StudentDao;
    5. import com.seven.excel.entities.Student;
    6. import lombok.extern.slf4j.Slf4j;
    7.  
    8. import java.util.ArrayList;
    9. import java.util.List;
    10.  
    11. @Slf4j
    12. public class StudentDataListener extends AnalysisEventListener {
    13.  
    14.     /**
    15.      * 每隔10条存储数据库，然后清理list ，方便内存回收
    16.      */
    17.     private static final int BATCH_COUNT = 10;
    18.     //缓存数据列表
    19.     private List cachedDataList = new ArrayList<>(BATCH_COUNT);
    20.  
    21.     //不可以用spring管理，需用该方法实例化对象
    22.     private StudentDao studentDao;
    23.     public StudentDataListener(StudentDao studentDao) {
    24.         this.studentDao = studentDao;
    25.     }
    26.  
    27.  
    28.     /**
    29.      * 这个每一条数据解析都会来调用
    30.      *
    31.      * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
    32.      * @param context
    33.      */
    34.     @Override
    35.     public void invoke(Student data, AnalysisContext context) {
    36.         log.info("解析到一条数据:{}", JSON.toJSONString(data));
    37.         Student student = new Student();
    38.         cachedDataList.add(student);
    39.         // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
    40.         if (cachedDataList.size() >= BATCH_COUNT) {
    41.             saveData();
    42.             // 存储完成清理 list
    43.             cachedDataList = new ArrayList<>(BATCH_COUNT);
    44.         }
    45.     }
    46.  
    47.     /**
    48.      * 所有数据解析完成了 都会来调用
    49.      * @param context
    50.      */
    51.     @Override
    52.     public void doAfterAllAnalysed(AnalysisContext context) {
    53.         // 这里也要保存数据，确保最后遗留的数据也存储到数据库
    54.         saveData();
    55.         log.info("所有数据解析完成！");
    56.     }
    57.  
    58.     /**
    59.      * 加上存储数据库
    60.      */
    61.     private void saveData() {
    62.         if(cachedDataList.size()>0){
    63.             log.info("{}条数据，开始存储数据库！", cachedDataList.size());
    64.             studentDao.insertBatchSomeColumn(cachedDataList);
    65.             log.info("存储数据库成功！");
    66.         }
    67.     }
    68. }

    此处，使用了mybatis-plus的批量插入，代码如下：

    1. @Component
    2. public class InsertBatchInjector extends DefaultSqlInjector {
    3.     @Override
    4.     public List getMethodList(Class mapperClass, TableInfo tableInfo){
    5.         List methodList = super.getMethodList(mapperClass,tableInfo);
    6.         // 过滤Insert语句中的字段
    7.         methodList.add(new InsertBatchSomeColumn(t -> !"weekend".equals(t.getColumn())
    8.                 && !"position".equals(t.getColumn())
    9.                 && !"date".equals(t.getColumn())
    10.         )); // 添加InsertBatchSomeColumn方法
    11.         return methodList;
    12.     }
    13. }

    其次，在一个新的包下面创建一个EasyBaseMapper继承baseMapper：

    1. public interface EasyBaseMapper extends BaseMapper {
    2.     /**
    3.      * 批量插入 仅适用于mysql
    4.      *
    5.      * @param entityList 实体列表
    6.      * @return 影响行数
    7.      */
    8.     Integer insertBatchSomeColumn(Collection entityList);
    9. }

    然后，在另一个包下，创建自己的dao继承该 EasyBaseMapper：

    1. @Mapper
    2. public interface StudentDao extends EasyBaseMapper {
    3. }

    注意，二者代码不可放在同一个包下！

    最后，编写控制类代码，读取excel文件，批量插入：

    1. @RestController
    2. public class StudentController {
    3.  
    4.     private static final String PATH = "C:/Users/14125/Desktop/";
    5.     @Resource
    6.     private StudentDao studentDao;
    7.  
    8.     @GetMapping("/read")
    9.     public void read(@RequestParam("filename") String fileName){
    10.         fileName = PATH + fileName;
    11.         // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    12.         EasyExcel.read(fileName, Student.class, new StudentDataListener(studentDao)).sheet().doRead();
    13.     }
    14. }
  • 相关阅读:
    SEO推广-SEO推广软件-SEO推广工具
    hippy 调试demo运行联调-mac环境准备篇
    C++中带默认值的函数参数
    如果给你一次机会，你想对前任说什么？是祝福他还是恨他
    面试官：Redis如何保证高可用？
    aws-msk-托管kafka集群的简单使用（VPC内部访问：无验证和SASL认证）
    Python Flask教程学习02
    Windows下Redis3.0集群搭建
    Swift开发基础06-闭包
    02 - QT 自定义控件封装
  • 原文地址：https://blog.csdn.net/tang_seven/article/details/128118518