Spring使用POI导入excel 配合layui upload组件

朱雀 2022-12-01 14:06 116阅读 0赞

# 概要 #

具体功能是前端点击按钮选择.xlsx(或者.xls)文件

后端接收后解析 每行一条记录 插入数据库中

前端使用了layui 的upload组件

后端使用ssm

POI导出excel可见[Spring使用POI导出excel(使用网络请求)][Spring_POI_excel]

# 步骤 #

### 1.pom引入依赖 ###

<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>

### 2.前端部分 ###

layui.use('upload', function () {
            var upload = layui.upload;
            var loadingIndex;
    
            //执行实例
            upload.render({
                elem: "#btn_import",
                url: $appRoot + '/pubcityExt/importCityExcel', //上传接口
                accept: 'file',
                exts: 'xlsx',
                before: function (obj) { 
                    layer.load(1); //上传loading
                },
                done: function (res) {
                    //上传完毕回调，关闭loading
                    layer.closeAll('loading');
                    if (res.status == true) {
                        layer.msg('导入成功', {icon: 1});
                    } else {
                        layer.msg(res.msg, {icon: 2});
                    }
    
                },
                error: function () {
                    //请求异常回调
                }
            })
        })

### 3.后端接口编写 ###

/**
         * 导入城市编码文件.
         *
         * @param file
         * @return
         */
        @RequestMapping("/importCityExcel")
        @ResponseBody
        public Map<String, Object> importCityExcel(@RequestParam(value = "file") MultipartFile file) {
            Map<String, Object> resultMap = new HashMap<String, Object>();
            try {
                poiUtil.importSubject(file);
    
                resultMap.put("status", true);
                resultMap.put("msg", "操作成功!");
            } catch (Exception e) {
                logger.error("导入城市文件失败", e);
                resultMap.put("status", false);
                resultMap.put("msg", "操作失败!");
            }
    
            return resultMap;
        }

这里注意的是

必须使用

@ResponseBody

注解，否则前端一直报错，无法接收回调

layui官方解释说返回值必须是json，实际返回map就可以

### 4.poi工具类编写 ###

package com.inspur.kdds.pubcity.service;
    
    import com.inspur.kdds.pubcity.entity.PubCity;
    import com.inspur.management.core.util.StringUtil;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.util.ObjectUtils;
    import org.springframework.util.StringUtils;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 城市文件导入
     */
    @Service
    public class CityPoiUtil {
    
        private static final Logger logger = LoggerFactory.getLogger(CityPoiUtil.class);
    
        @Autowired
        private PubCityExtService pubCityExtService;
    
        /**
         * 导入文件.
         *
         * @throws Exception
         */
        public void importSubject(MultipartFile file) throws Exception {
            //解析Excel
            //根据Excel文件创建工作簿
            Workbook wb = new XSSFWorkbook(file.getInputStream());
            //获取Sheet
            XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);//参数：索引
            //写入数据
            writeImportDataToMysql(sheet);
        }
    
    
        /**
         * 将数据保存至excel.
         *
         * @param sheet
         */
        public void writeImportDataToMysql(XSSFSheet sheet) {
    
            List<PubCity> list = new ArrayList<>();
            System.out.println(sheet.getLastRowNum());
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);//根据索引获取每一个行
                String[] values = new String[row.getLastCellNum()];
                for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    cell.setCellType(CellType.STRING);
                    String value = cell.getStringCellValue().trim();
    
                    values[cellNum] = value;
                }
                if(StringUtil.isEmpty(values[0]) || StringUtil.isEmpty(values[1])) {
                    continue;
                }
    
                // 省级城市去掉最后的0000
                if (values[0].endsWith("0000")) {
                    values[0] = values[0].substring(0, values[0].length() - 4);
                    // 市级城市去掉最后的00
                } else if (values[0].endsWith("00")) {
                    values[0] = values[0].substring(0, values[0].length() - 2);
                }
    
                PubCity pubCity = new PubCity();
    
                if (values[0].length() == 2) {
                    pubCity.setParentId("");
                } else {
                    pubCity.setParentId(values[0].substring(0, values[0].length() - 2));
                }
    
                pubCity.setCityId(values[0]);
                pubCity.setCityName(values[1]);
                list.add(pubCity);
            }
            //批量保存用户
            pubCityExtService.saveAll(list);
        }
    }

# 大功告成！ #

[Spring_POI_excel]: https://blog.csdn.net/weixin_42089175/article/details/108255673