Springboot整合POI表格数据的读取

最新推荐文章于 2024-08-05 16:32:19 发布

liu.kai

最新推荐文章于 2024-08-05 16:32:19 发布

阅读量629

点赞数

分类专栏： Spring相关框架等(能与Spring整合的)

本文链接：https://blog.csdn.net/weixin_44848900/article/details/108299669

版权

Spring相关框架等(能与Spring整合的) 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

摘要
本次的工具类代码来自于博客https://www.cnblogs.com/xiaoyao-001/p/9310009.html 我在此基础上进行了部分增强,使之能够将不同sheet中的数据存放在不同的集合容器中,避免了当多个sheet中存储的数据不是同一类型的时,处理的麻烦.
写这篇博客就是为了以后在读取表格中少量数据的时候直接复制过来用就可以了,文末我将代码地址附上,有需要的朋友可以直接进行下载。下面直接开始正题

1 设计技术要点

Springboot 脚手架, POI ,Element-UI简单实现文件的上传页面(就是一个上传组件),因为Element-UI是基于Vue的所以这里也是用了Vue

2 后台重点代码及配置

导入依赖 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>show.mrkay</groupId>
    <artifactId>POI-mrkay</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <!--springboot-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
    </parent>
    <dependencies>
        <!--springboot-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>
        <dependency>
            <groupId>repository.org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

application.yml文件
因为简单这里其实就只配置了端口号为80

server:
  port: 80

封装工具类

package show.mrkay.utils.excelutils;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName: ExcelReadUtil
 * @description: 批量读取表格文件
 * @Author: MrKay
 * @Date: 2020/8/23
 */
public class ExcelReadUtil {
    private static final String EXCEL_XLS = ".xls";
    private static final String EXCEL_XLSX = ".xlsx";

    /**
     * @MethodName: excelReader
     * @Params: [url]
     * @return: java.util.List<java.util.List < java.lang.String>>
     * @Description: 根据URL读取excel中数据
     * @Author: MrKay
     * @Date: 2020/8/24
     */
    public static Map<String, List<List<String>>> excelReader(File file) throws Exception {
        // File file = new File(url);
        varifyExcelFile(file);
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = getWorkbook(inputStream, file);
        int sheetNumber = workbook.getNumberOfSheets();
        Map<String, List<List<String>>> dataMap = new HashMap<>();
        //遍历sheet
        for (int sheetIndex = 0; sheetIndex < sheetNumber; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (sheet == null) {
                continue;
            }
            //每次循环到一个不为空的sheet就创建一个集合存储数据
            List<List<String>> dataList = new ArrayList<>();
            String sheetName = sheet.getSheetName();
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                //遍历行中的每一个单元格
                List<String> cellList = new ArrayList<>();
                int cellNum = row.getLastCellNum();
                for (int cellIndex = 0; cellIndex < cellNum; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    String cellValue = getCellValue(cell);
                    cellList.add(cellValue);
                }
                dataList.add(cellList);
            }
            dataMap.put(sheetName, dataList);
        }
        inputStream.close();
        return dataMap;
    }

    /**
     * @MethodName: getCellStringValue
     * @Params: [cell]
     * @return: java.lang.String
     * @Description: 获取每一行单元格中的数据
     * @Author: MrKay
     * @Date: 2020/8/26
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null || "".equalsIgnoreCase(cell.toString().trim())) {
            return cellValue;
        }
        CellType cellType = cell.getCellTypeEnum();
        //String 类型
        if (cellType == CellType.STRING) {
            cellValue = cell.getStringCellValue().trim();
        }
        //带有数字类型
        if (cellType == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                cellValue = DateFormatUtils.format(cell.getDateCellValue().getTime(), "yyyy-MM-dd");
            } else {
                cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
            }
        }
        //Boolean类型
        if (cellType == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }
        //错误单元格
        if (cellType == CellType.ERROR) {
            cellValue = "ErrorType";
        }
        //空值
        if (cellType == CellType.BLANK) {
            return cellValue;
        }
        //公式型
        if (cellType == CellType.FORMULA) {
            cellValue = cell.getCellFormula();
        }
        return cellValue;
    }

    /**
     * @MethodName: getWorkbook
     * @Params: [inputStream, file]
     * @return: org.apache.poi.ss.usermodel.Workbook
     * @Description: 获取Workbook
     * @Author: MrKay
     * @Date: 2020/8/25
     */
    private static Workbook getWorkbook(FileInputStream inputStream, File file) throws IOException {
        Workbook workbook = null;
        if (file.getName().endsWith(EXCEL_XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        }
        if (file.getName().endsWith(EXCEL_XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * @MethodName: varifyExcelFile
     * @Params: [file]
     * @return: boolean
     * @Description: 校验文件是不是Excel文件
     * @Author: MrKay
     * @Date: 2020/8/25
     */
    private static void varifyExcelFile(File file) throws Exception {
        String fileName = file.getName();
        //判断file是否存在
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        //判断是不是Excel文件
        if (!file.isFile()) {
            throw new Exception("不是文件!");
        }
        if (!(fileName.endsWith(EXCEL_XLS) || fileName.endsWith(EXCEL_XLSX))) {
            throw new Exception("不是Excel文件");
        }
    }

}

工具类说明
1 传递参数可以是URL也可以是一个File 如果是URL我们可以直接使用File file = new File(url);来进行转换最后转换成InputStream

/*
此Map集合就是用于存储一个Excel表中如果有多个sheet,
就使用此Map集合进行封装,键就是Sheet的名字,值就是该sheet中遍历的数据;
*/
 Map<String, List<List<String>>> dataMap = new HashMap<>();
/*
此list集合用于存储每一个sheet的数据,list<String>存储的是sheet中每一行的数据
*/
 List<List<String>> dataList = new ArrayList<>();

3 前端页面

这里将简单的前端页面代码附在这里

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
</head>
<body>
<div id="app">
    <div class="upload"></div>
    <el-upload
            class="upload-demo"
            drag
            action="/excel/upload"
            multiple>
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
    </el-upload>
</div>
</body>
<script src="https://unpkg.com/vue/dist/vue.js"></script>
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script>
    new Vue({
        el: '#app',
        data: {}
    })
</script>
</html>

4 controller层代码

原本不想把controller层的代码附在这里,但是因为页面中有请求路径还是在这里直接附上吧,完整代码可以直接带gitee中拉取下载’

package show.mrkay.controller;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import show.mrkay.utils.excelutils.ExcelReadUtil;

import java.io.File;

/**
 * @ClassName: ExcelController
 * @description: 表格处理控制器
 * @Author: MrKay
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {
    @RequestMapping("/upload")
    public String excelUpload(MultipartFile file)  {
        try {
            String originalFilename = file.getOriginalFilename();
            String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
            File file1 = File.createTempFile("excel", suffix);
            file.transferTo(file1);
            System.out.println(file1.getName());
            ExcelReadUtil.excelReader(file1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

5 测试结果展示

这里的测试我直接使用的是URL的方式在test包下进行的测试,如果页面测试可以直接将文件上传就不要使用url的方式,如果使用URL的方式测试可以将工具类中的方法参数File file 改成String url 将该方法第一行代码取消注释即可

package show.mrkay.utils.excelutils;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @ClassName: ExcelReadTest
 * @description: 测试工具类
 * @Author: MrKay
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class ExcelReadTest {
    @Test
    public void excelReadUtilTest() {
        String url = "D:\\excel.xlsx";
        try {
            Map<String, List<List<String>>> dataMap = ExcelReadUtil.excelReader(url);
            Set<Map.Entry<String, List<List<String>>>> entries = dataMap.entrySet();
            for (Map.Entry<String, List<List<String>>> entry : entries) {
                String sheetName = entry.getKey();
                List<List<String>> sheetValue = entry.getValue();
                for (int i = 0; i < sheetValue.size(); i++) {
                    List<String> list = sheetValue.get(i);
                    System.out.println(sheetName + "表单的第" + (i + 1) + "行数据: " + list.toString());
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}