上传excel文件，导出excel模板实现

最新推荐文章于 2022-09-06 10:59:02 发布

一派天真

最新推荐文章于 2022-09-06 10:59:02 发布

阅读量622

点赞数

分类专栏：后台文章标签： Excel导入

本文链接：https://blog.csdn.net/qqtingshuo/article/details/103416695

版权

该博客介绍了一种实现商品导入的功能，包括导入组别、品类和属性（通过JSON字符串处理）。同时，详细阐述了如何根据自定义模板进行导出。控制器调用前端路径，利用服务层实现导入和导出。在项目中引入了Excel相关jar包，并展示了controller、service、serviceImpl及两个工具类ConstantUtil和ExcelUtils的角色。

摘要由CSDN通过智能技术生成

此功能上商品导入，分别有导入组别，品类，属性（json字符串截取）等多功能多表实现。

导出功能，根据自定义需要的模板名称生成可配置模板

controller调用前段路径，然后导入/导出模板工具，service实现。

首先pom中导入excel jar包

        <!--exceljar-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

controller

package com.macro.mall.controller;

import com.macro.mall.common.api.CommonResult;
import com.macro.mall.service.IExcelService;
import com.macro.mall.util.ConstantUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Map;

/**
 * 上传Excel导入商品
 *
 * @author gbl
 * date 2019年12月1日17:03:55
 */
@Controller
@Api(tags = "UploadController", description = "商品导入excel")
@RequestMapping("/upload")
public class UploadController {

    @Autowired
    private IExcelService iExcelService;

    private static String filenameTemp;

    /**
     * 上传商品excel
     * @param file
     * @return
     */
    @RequestMapping(value = "/excel", method = RequestMethod.POST)
    @ApiOperation("上传excel")
    @ResponseBody
    public Map<String, Object>  uploadExecl(@RequestParam("file") MultipartFile file) {
        return iExcelService.uploadExecl(file);
    }

    /**
     * 描述：下载导入模板
     * @throws Exception
     */
    @RequestMapping( value = "/downloadExcel", method = RequestMethod.GET )
    @ApiOperation("下载excel模板")
    @ResponseBody
    public CommonResult exportWhitelistTemplate(HttpServletResponse response) {
        iExcelService.exportWhitelistTemplate(response);
        return null;
    }

    @GetMapping("/errorFile")
    public ResponseEntity<FileSystemResource> downErrorFile(HttpServletRequest request) throws Exception {
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        if (!path.exists()) {
            path = new File("");
        }
        File upload = new File(path.getAbsolutePath() + ConstantUtil.STATIC_FRONTEND_PROJECT_TEMPLATE);
        if (!upload.exists()) {
            upload.mkdirs();
        }
        filenameTemp = java.net.URLDecoder.decode(upload.getAbsolutePath().replaceAll("!", ConstantUtil.NULL_STRING), ConstantUtil.UTF_8) + File.separator + ConstantUtil.IMPORT_ERROR_MESSAGE + ConstantUtil.WORD_TXT;
        File file = new File(filenameTemp);
        String userAgent = request.getHeader(ConstantUtil.USER_AGENT).toLowerCase();
        String fileName = ConstantUtil.ERRORFILENAME;
        if (userAgent.contains(ConstantUtil.MSIE) || userAgent.contains(ConstantUtil.LIKE_GECKO)) {
            fileName = URLEncoder.encode(fileName, ConstantUtil.UTF_8);
        } else {
            fileName = new String(fileName.getBytes(ConstantUtil.UTF_8), ConstantUtil.ISO_8859_1);
        }
        if (file.exists()) {
            return export(file, fileName);
        } else {
            return null;
        }

    }

    public ResponseEntity<FileSystemResource> export(File file, String fileName) {
        if (file == null) {
            return null;
        }
        HttpHeaders headers = new HttpHeaders();
        headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
        headers.add("Content-Disposition", "attachment; filename=" + fileName + ".txt");
        headers.add("Pragma", "no-cache");
        headers.add("Expires", "0");
        return ResponseEntity
                .ok()
                .headers(headers)
                .contentLength(file.length())
                .contentType(MediaType.parseMediaType("application/octet-stream"))
                .body(new FileSystemResource(file));
    }

}

service实现

package com.macro.mall.service;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.Map;

/**
 * @author gbl
 * @Description:
 * @date 2019年12月3日11:07:17
 */
public interface IExcelService {
    /**
     * 上传execl
     * @param file
     * @return
     */
    Map<String, Object> uploadExecl(MultipartFile file);

    /**
     * 描述：下载导入模板
     * @param response
     */
    void exportWhitelistTemplate(HttpServletResponse response);
}

serivceImpl实现

package com.macro.mall.service.impl;

import com.macro.mall.bo.MallContext;
import com.macro.mall.common.api.CommonResult;
import com.macro.mall.common.api.ResultCode;
import com.macro.mall.dao.PmsProductAttributeValueDao;
import com.macro.mall.dto.GoodsListExcelVO;
import com.macro.mall.mapper.PmsProductMapper;
import com.macro.mall.mapper.PmsSkuStockMapper;
import com.macro.mall.model.*;
import com.macro.mall.service.IExcelService;
import com.macro.mall.service.PmsProductGroupService;
import com.macro.mall.service.PmsProductVarietyService;
import com.macro.mall.util.ConstantUtil;
import com.macro.mall.util.EntityUtils;
import com.macro.mall.util.ExcelUtils;
import com.macro.mall.util.ImportExcelV2Util;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.codehaus.jettison.json.JSONArray;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author gbl
 * @Description:
 * @date 2019年12月1日17:43:43
 */
@Service
@Slf4j
public class ExcelUploadImpl implements IExcelService {


    @Autowired
    private PmsProductMapper productMapper;

    @Autowired
    private PmsSkuStockMapper pmsSkuStockMapper;

    @Autowired
    private PmsProductVarietyService iVarietyService;

    @Autowired
    private PmsProductGroupService pmsProductGroupService;

    @Autowired
    private PmsProductAttributeValueDao productAttributeValueDao;

    /**
     *   判断小数点后2位的数字的正则表达式
     */
    private static Pattern pattern=Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$");

    @Override
    public Map<String, Object> uploadExecl(MultipartFile file) {
        Map<String, Object> resMap = new HashMap<>();
        // 存放解析完的excel数据
        List<Map<String, Object>> importList = new ArrayList<Map<String, Object>>();
        // 存放解析完的标题数据
        Map<String, Object> titleStringMap = new HashMap<String, Object>();
        String title = null;
        // 声明并初始化Excel文件中列名的集合
        List<String> colList = new ArrayList<String>();
        // 以下列名顺序需要与Excel文件中列名顺序匹配
        String[] titleArray = ConstantUti