SpringBoot中使用POI实现Excel导入到数据库（图文教程已实践）

ゝ一纸荒年。 2023-10-18 15:47 8阅读 0赞

## 场景 ##

IDEA中开发SpringBoot项目时需要将Excel模板中数据导入的到数据库。

Excel模板如下

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

实现思想

首先将模板上传到服务器中某路径，并将当前路径存储，然后使用POI自带的

工具类获取到sheet以及行数以及每个cell，然后分别给数据库中对应的对象的属性赋值，然后插入到数据库中并返回执行结果。

## 实现 ##

### 实现文件上传 ###

页面代码

实现点击导入按钮弹窗

<div class="modal inmodal" id="apImportModel" tabindex="-1" role="dialog"  aria-hidden="true">
            <div class="modal-dialog" id="apImportDiv" th:fragment="apImportDiv">
                <div class="modal-content animated fadeIn">
                    <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button>
                    <form  class="dropzone"  id="dropzoneForm" enctype="multipart/form-data">
                        <div class="fallback">
                            <input name="file" type="file" id="file_id" />
                        </div>
                    </form>
                    <button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上传</button>
                    <button id="parseBtn" class="btn btn-info mt-2" type="button" onclick="return parseExcel()"><i class="fa fa-reply"></i>导入</button>
                </div>
            </div>
        </div>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

因为这里使用的是thymeleaf模板

thymeleaf中文参考手册下载：

[https://download.csdn.net/download/badao\_liumang\_qizhi/11021427][https_download.csdn.net_download_badao_liumang_qizhi_11021427]

在当前页面需要引入js文件

<!DOCTYPE html>
    <html xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro"
         th:replace="layout/layout(title='测试数据',cssPaths='/public/css/plugins/datapicker/datepicker3.css',jsPaths='/public/js/plugins/dataTables/datatables.min.js,/modular/receiveOrder/wmsReceiveOrder.js')">

来到/modular/receiveOrder/wmsReceiveOrder.js此目录下的js文件

在js中实现点击导入按钮弹窗功能

//导入按钮点击事件
        $("#importBtn").click(function () {
            importExcel();
        });

点击事件调用下面的方法

function importExcel(){
            var url = "/wmsReceiveOrder/doExcelImport.html";
            $('#apImportDiv').load(url, function (response,status,xhr) {
                $("#apImportModel").modal('show');
            });
        }

该方法按照url请求后台，其中load方法是让隐藏的弹窗部分显示 。

来到后台url对应的方法中

@Description("获取收获单信息列表页面")
        @RequestMapping("/wmsReceiveOrder.html")
        public String page(Model model) {
          
            return "receiveOrder/wmsReceiveOrder.html";
        }

返回当前页面并将弹窗显示

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2][]

开始设置文件上传的点击事件

在弹窗部分的代码中

<div class="modal inmodal" id="apImportModel" tabindex="-1" role="dialog"  aria-hidden="true">
            <div class="modal-dialog" id="apImportDiv" th:fragment="apImportDiv">
                <div class="modal-content animated fadeIn">
                    <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button>
                    <form  class="dropzone"  id="dropzoneForm" enctype="multipart/form-data">
                        <div class="fallback">
                            <input name="file" type="file" id="file_id" />
                        </div>
                    </form>
                    <button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上传</button>
                    <button id="parseBtn" class="btn btn-info mt-2" type="button" onclick="return parseExcel()"><i class="fa fa-reply"></i>导入</button>
                </div>
            </div>
        </div>

其中

是调用js中对应的方法uploadExcel()

//上传Excel操作
    function uploadExcel(){
        debugger
        var formData = new FormData();
        var file=$("#file_id");
        formData.append('file', file[0].files[0]);
        $.ajax({
            type: 'POST',
            url: "/wmsReceiveOrder/doImportExcel",
            cache: false,  //禁用缓存
            data:formData,
            contentType: false,
            dataType: "json",
            processData:false,
            success: function (result) {
                debugger
                alert(result.message)
            }
    
        })
    
        return false;
    }

选择文件后，会上传到url中对应的位置

要实现文件上传，需要引入commons-fileupload包

找到项目下的pom.xml

添加如下依赖

<dependency>
                    <groupId>commons-fileupload</groupId>
                    <artifactId>commons-fileupload</artifactId>
                    <version>1.3</version>
                </dependency>

来到url对应的后台方法

@Description("获取前端导入excel")
        @ResponseBody
        @RequestMapping(value = "/doImportExcel")
        public Map<String, Object> doUpload(HttpServletRequest request, @RequestParam("file") MultipartFile file) {
            Map<String, Object> result = new HashMap<String, Object>();
            String fileName = "";
            try {
                //将当前上下文初始化给  CommonsMutipartResolver （多部分解析器）
                CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
                //检查form中是否有enctype="multipart/form-data"
                if (multipartResolver.isMultipart(request)) {
                    //将request变成多部分request
                    MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
                    //获取multiRequest 中所有的文件名
                    Iterator iter = multiRequest.getFileNames();
                    while (iter.hasNext()) {
                        //一次遍历所有文件
                        MultipartFile file1 = multiRequest.getFile(iter.next().toString());
                        if (file != null) {
                            //获取上传文件名
                            fileName = file1.getOriginalFilename();
                            //获取后缀名
                            String sname = fileName.substring(fileName.lastIndexOf("."));
                            //时间格式化格式
                            SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyyMMddHHmmssSSS");
                            //获取当前时间并作为时间戳
                            String timeStamp=simpleDateFormat.format(new Date());
                            //拼接新的文件名
                            String newName ="收货单"+timeStamp+sname;
                            //指定上传文件的路径
                            String path = "F:\\" + newName;
                            //上传保存
                            file.transferTo(new File(path));
                            //保存当前文件路径
                            request.getSession().setAttribute("currFilePath", path);
                        }
                    }
                }
    
                result.put("statusCode", "200");
                result.put("message", "上传成功!");
                result.put("filename", fileName);
            } catch (Exception ex) {
                result.put("statusCode", "300");
                result.put("message", "上传失败:" + ex.getMessage());
            }
            return result;
        }

### 实现效果 ###

点击选择文件

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 3][]

选择文件后

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 4][]

然后点击上传

这时在后台controller打断点查看，可以看到后台已经获取到上传的文件，并以当前时间戳重名名存放在F盘下，将存放路径存在当前

session中。

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 5][]

可以看到F盘 下已经有了刚才上传的文件

![Image 1][]![20190319135036244.jpg][]

### 解析并导入Excel实现 ###

弹窗页面的解析Excel按钮代码

<div class="modal inmodal" id="apImportModel" tabindex="-1" role="dialog"  aria-hidden="true">
            <div class="modal-dialog" id="apImportDiv" th:fragment="apImportDiv">
                <div class="modal-content animated fadeIn">
                    <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button>
                    <form  class="dropzone"  id="dropzoneForm" enctype="multipart/form-data">
                        <div class="fallback">
                            <input name="file" type="file" id="file_id" />
                        </div>
                    </form>
                    <button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上传</button>
                    <button id="parseBtn" class="btn btn-info mt-2" type="button" onclick="return parseExcel()"><i class="fa fa-reply"></i>导入</button>
                </div>
            </div>
        </div>

点击导入按钮后调用js中的parseExcel()方法

//解析Excel操作
    function parseExcel(){
        var url = "/wmsReceiveOrder/parseExcel";
        $.ajax({
            type: 'POST',
            url: url,
            cache: false,  //禁用缓存
            contentType: false,
            dataType: "json",
            processData:false,
            success: function (result) {
                debugger
                alert(result.message)
            }
        })
        return false;
    }

ajax请求后台的url对应的方法

前提要实现Excel的导入和导出需要引入POI的包

打开项目的pom.xml

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

刚才请求到后台url对应的方法

@ResponseBody
        @RequestMapping("/parseExcel")
        public Map<String, Object> parseExcel(HttpServletRequest request) {
            Map<String, Object> result = new HashMap<String, Object>();
            Workbook workbook = null;
            //获取文件路径
            String path = (String)request.getSession().getAttribute("currFilePath");
            //获取文件格式
            String fileType = path.substring(path.lastIndexOf(".") + 1, path.length());
            try {
                InputStream stream = new FileInputStream(path);
                //如果后缀名为xls，使用HSSF
                if (fileType.equals("xls")) {
                    workbook = new HSSFWorkbook(stream);
                    //如果后缀名是xlsx，使用XSSF
                }else if (fileType.equals("xlsx")){
                    workbook = new XSSFWorkbook(stream);
                }
                Sheet sheet= workbook.getSheet("sheet1");
                //获取行数
                int rows=sheet.getPhysicalNumberOfRows();
                //List<WmsReceiveOrder> receiveOrderList =new ArrayList<WmsReceiveOrder>();
                WmsReceiveOrder receiveOrder =new WmsReceiveOrder();
                //获取第一行数据
                Row row1 =sheet.getRow(0);
                if(row1!=null){
                    //获取采购订单号
                    row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    String purchaseCode =row1.getCell(1).getStringCellValue();
                    receiveOrder.setPurchaseCode(purchaseCode);
                }
                //获取第二行数据
                Row row2 =sheet.getRow(1);
                if(row2!=null){
                    //获取供应商送货单号
                    row2.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    String deliveryCode =row2.getCell(1).getStringCellValue();
                    receiveOrder.setDeliveryCode(deliveryCode);
                    //获取送货日期
                    Date deliveryTime =row2.getCell(3).getDateCellValue();
                    receiveOrder.setDeliveryTime(deliveryTime);
                    //获取供应商名称
                    row2.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    String supplierName =row2.getCell(5).getStringCellValue();
                    receiveOrder.setSupplierName(supplierName);
                }
                //获取第三行数据
                Row row3 =sheet.getRow(2);
                if(row3!=null){
                    //获取ERP入货单号
                    row3.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    String erpInCode =row3.getCell(1).getStringCellValue();
                    receiveOrder.setErpInCod(erpInCode);
                    //获取入库日期
                    Date inTime =row3.getCell(3).getDateCellValue();
                    receiveOrder.setInTime(inTime);
                }
                receiveOrder.setRemark("测试备注");
                receiveOrder.setType(1);
                receiveOrder.setStatus("1");
                //插入receiveOrder表数据
                boolean isSaveReceiveOrder =receiveOrderService.save(receiveOrder);
                List<WmsReceiveOrderDetails> receiveOrderDetailsList = new ArrayList<WmsReceiveOrderDetails>();
                if(isSaveReceiveOrder){
                    //如果插入表头成功，获取插入数据的ID并插入详情表
                    Long receiveId=receiveOrder.getId();
                    for (int currentRow=4;currentRow<rows;currentRow++) {
    
                        WmsReceiveOrderDetails wmsReceiveOrderDetails =new WmsReceiveOrderDetails();
                        wmsReceiveOrderDetails.setReceiveId(receiveId);
                        //获取物料编号
                        sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        String materielId = sheet.getRow(currentRow).getCell(1).getStringCellValue();
                        wmsReceiveOrderDetails.setMaterielId(materielId);
                        //获取物料名称
                        sheet.getRow(currentRow).getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        String materielName = sheet.getRow(currentRow).getCell(2).getStringCellValue();
                        wmsReceiveOrderDetails.setMaterielName(materielName);
                        //获取供应商批次
                        sheet.getRow(currentRow).getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        String supplierBatch = sheet.getRow(currentRow).getCell(3).getStringCellValue();
                        wmsReceiveOrderDetails.setSupplierBatch(supplierBatch);
                        //获取生产日期
                        Date productDate = sheet.getRow(currentRow).getCell(4).getDateCellValue();
                        wmsReceiveOrderDetails.setProductDate(productDate);
                        //获取数量
                        sheet.getRow(currentRow).getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                        Long num =Long.parseLong(sheet.getRow(currentRow).getCell(5).getStringCellValue());
                        wmsReceiveOrderDetails.setNum(num);
                        //获取托盘编号
                        String salverCode = sheet.getRow(currentRow).getCell(6).getStringCellValue();
                        wmsReceiveOrderDetails.setSalverCode(salverCode);
    
                        wmsReceiveOrderDetails.setStatus("1");
    
                        wmsReceiveOrderDetails.setRemark("测试备注");
    
                        wmsReceiveOrderDetails.setDeletedFlag(false);
    
                        receiveOrderDetailsList.add(wmsReceiveOrderDetails);
                    }
                    if(receiveOrderDetailsList!=null){
                        for (WmsReceiveOrderDetails wmsReceiveOrderDetails:receiveOrderDetailsList
                                ) {
                            boolean isSaveReceiveOrderDetails =receiveOrderDetailsService.save(wmsReceiveOrderDetails);
                            if(!isSaveReceiveOrderDetails){
                                result.put("statusCode", "300");
                                result.put("message", "导入物料编号为："+wmsReceiveOrderDetails.getMaterielId()+"出错了!");
                            }else{
                                result.put("statusCode", "200");
                                result.put("message", "导入收货单成功!");
                            }
                        }
    
                    }
    
                }else{
                    result.put("statusCode", "300");
                    result.put("message", "导入收货单失败!");
                }
    
    
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return result;
        }

这里是结合具体的业务实现的Excel的导入，这里要导入的表是两个关联表。

基本思想就是通过行与列的坐标定位获取到cell中的值。

比如：

1.获取sheet页

Workbook workbook= null;

中间需要根据获取的后缀名进行初始化

workbook = new HSSFWorkbook(stream);

或者

workbook = new XSSFWorkbook(stream);

然后

Sheet sheet= workbook.getSheet("sheet1");

这里的sheet1是sheet页的名字

![Image 1][]![20190319135155987.jpg][]

2.获取行数

int rows=sheet.getPhysicalNumberOfRows();

3.获取第一行数据

Row row1 =sheet.getRow(0);

4.获取第一行的第2个Cell对象

row1.getCell(1)

注意行与Cell都是从0开始的。

5.给第一行的第二个Cell设置值的类型

row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);

具体类型在IDEA代码提示中会有。

6.获取第一行第二个Cell的String类型的值。

String purchaseCode =row1.getCell(1).getStringCellValue();

7.获取日期格式的Cell的值

Date inTime =row3.getCell(3).getDateCellValue();

### 实现效果 ###

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 6][]

[Image 1]: 
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190319134303166.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190319135003261.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[https_download.csdn.net_download_badao_liumang_qizhi_11021427]: https://download.csdn.net/download/badao_liumang_qizhi/11021427
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190319134947191.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190319134602806.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190319134607365.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190319135025247.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[20190319135036244.jpg]: https://img-blog.csdnimg.cn/20190319135036244.jpg
[20190319135155987.jpg]: https://img-blog.csdnimg.cn/20190319135155987.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20190319135259214.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70