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

概要

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

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

前端使用了layui 的upload组件

后端使用ssm

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

步骤

1.pom引入依赖

 <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>

<!-- POI 分组维度导入使用 -->
<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);
    }
}

大功告成！