Java获取Excel导入的数据(业务处理后可用于数据批量导入)

需求：Excel批量导入数据

实现Excel批量导入的难点是如何获取Excel文档中的数据，只要能获取到数据，至于用于数据封装或者往数据库里增数据只需要根据不同业务进行处理即可。

代码Demo

1、poi依赖

        <dependency>
            <groupId>com.deepoove</groupId>
            <artifactId>poi-tl</artifactId>
            <version>1.9.0</version>
        </dependency>

2、前端页面

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
    <meta charset="UTF-8">
    <title>测试</title>
</head>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
<body>
<h1>测试文件上传</h1>
<form method="post" action="/excel/import">
    <input type="file" id="uploadFile" name="uploadWord">
    <input type="button" value="上传" onclick="upload()">
</form>
</body>
<script>
    function upload() {
        let file = document.getElementById("uploadFile").files[0];
        let formData = new FormData();
        formData.append("file",file);
        $.ajax({
            type: "post",
            async: false,
            data: formData,
            cache: false,
            processData : false,
            contentType : false,
            url: "/excel/import",
            success: function (res) {
                alert(res);
            }
        })
    }
</script>
</html>

3、controller（Slf4j日志记录，不需要可以删除）

package com.bootdemo.myspringboot.controller;

import com.bootdemo.myspringboot.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * @Classname ExcelController
 * @Description TODO
 * @Date 2022/4/27 14:15
 * @Created by BG
 */
@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {

    @Autowired
    public ExcelService excelService;

    @RequestMapping("import")
    public String excelImport(@RequestParam("file") MultipartFile file) {
        return excelService.excelImport(file);
    }
    
}

4、Service

package com.bootdemo.myspringboot.service.impl;

import com.bootdemo.myspringboot.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

/**
 * @Classname ExcelServiceImpl
 * @Description TODO
 * @Date 2022/4/27 14:16
 * @Created by BG
 */
@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {
    @Override
    public String excelImport(MultipartFile file) {
        //excel.XLS文件
        //HSSFWorkbook hssfWorkbook = null;
        //excel.XLSX文件
        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream inputStream = file.getInputStream();
            xssfWorkbook = new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.info("创建文件输入流失败：" + e.getMessage());
            return "创建文件输入流失败";
        }
        // 获取Excel的第一个sheet
        XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
        //获取行数
        int columnNum = sheetAt.getPhysicalNumberOfRows();
        for (int i = 0; i < columnNum; i++) {
            //获取每行
            Row row = sheetAt.getRow(i);
            //获取列数
            int lastRowNum = row.getPhysicalNumberOfCells();
            for (int j = 0; j < lastRowNum; j++) {
                //获取每列
                Cell cell = row.getCell(j);
                //第i行第j列的值(模板用string数值，如果用其他类型则用其他方法获取值)
                String cellValue = cell.getStringCellValue();
                System.out.println("第" + i + "行第" + j + "列数值为:" + cellValue);
            }
        }
        return "success";
    }
}

结果展示

1、Excel模板

 

 2、页面

3、控制台输出

 

posted @ 2022-04-27 15:28  请别耽误我写BUG  阅读(854)  评论(0)    收藏  举报