springboot 整合poi实现excel导入导出

项目中使用到了excel文件导入导出，现在做一下纪录，以便日后查询

1,pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.bsoft</groupId>
    <artifactId>BsoftDevopsManage</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>BsoftDevopsManage</name>
    <description>BSOFT Devops Manage</description>

    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.SR3</spring-cloud.version>
    </properties>

    <dependencies>
        <!-- 引用通用工具包 -->
        <dependency>
            <groupId>cn.bsoft</groupId>
            <artifactId>BsoftCommonUtil</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
        <!-- 基本必须包 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-config</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
        </dependency>
        <!-- 定时任务和健康监控 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <!--安全认证的依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <!-- http客户端工具包 -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
        </dependency>
        <!-- feign替换JDK默认的URLConnection为okhttp -->
        <dependency>
            <groupId>io.github.openfeign</groupId>
            <artifactId>feign-okhttp</artifactId>
        </dependency>
        <!-- 自动读取配置文件内容 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>
        <!-- 数据库 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!-- 分页插件 -->
        <!-- mybatis的分页插件 -->
        <!--pageHelper基本依赖 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-autoconfigure</artifactId>
            <version>1.2.5</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>

        <!-- Druid Pool -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <!-- redis支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-cache</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-io</artifactId>
            <version>1.3.2</version>
        </dependency>
        
        <dependency>
            <groupId>commons-net</groupId>
            <artifactId>commons-net</artifactId>
            <version>3.6</version>
        </dependency>

        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.10</version>
        </dependency>


        <!--文件上传poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>

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

        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.4</version>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>


    <build>

        <!--重要 使用动态config后，如果不设置resource 会导致application.yaml中的@@找不到pom文件中的配置 -->
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
            </resource>
        </resources>

        <plugins>
            <!-- 打包时跳过单元测试 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>

            <!-- mybatis generator 自动生成代码插件 https://blog.csdn.net/shuang3281/article/details/85248680 -->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.4.0</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

2,数据导出功能实现DownloadOrgUserCountServiceImpl.java

package cn.bsoft.BsoftDevopsManage.service.orguser.impl;

import cn.bsoft.BsoftDevopsManage.entity.UserCacheData;
import cn.bsoft.BsoftDevopsManage.mybatis.dao.OrgUserMapper;
import cn.bsoft.BsoftDevopsManage.service.IFunctionService;
import cn.bsoft.BsoftDevopsManage.service.file.UploadFileService;
import cn.bsoft.BsoftDevopsManage.util.BuzAuthUtil;
import cn.bsoft.CommonUtil.entity.LhqException;
import cn.bsoft.CommonUtil.entity.WebRequestTemplate;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 下载医护端医生使用情况
 */
@Service("downloadOrgUserCount")
public class DownloadOrgUserCountServiceImpl implements IFunctionService {

   

    @Autowired
    private UploadFileService uploadFileService;
    
    
    @Autowired
    private OrgUserMapper orgUserMapper;

    /**
     * 下载医护端使用情况excel, 返回文件路径, 前端拼装域名后访问该文件并下载
     *
     * @param webRequestTemplate
     * @return
     * @throws LhqException
     */
    @SuppressWarnings("static-access")
    @Override
    public Object service(WebRequestTemplate webRequestTemplate) throws LhqException {
        JSONObject paramJson = BuzAuthUtil.datatJsonObject(webRequestTemplate);
        
        String orgUserCache = webRequestTemplate.getOrgUserCache();
        UserCacheData userCacheData = UserCacheData.getInstance(orgUserCache);
        if (null == userCacheData) {
            throw new LhqException("缓存中用户为空, 请登录");
        }
        Integer mgrUserUid = userCacheData.getMgrUserUid();
        if (null == mgrUserUid) {
            throw new LhqException("缓存中用户主键id为空, 请登录");
        }
        //列标题
        String[] title  = {"区编码","社康名", "社康编码", "各社康医护端累计使用数"};
        Map<String, Object> paramMap = new HashMap<String, Object>();
        /***
         * 区编码
         */
        if(!StringUtils.isEmpty(paramJson.getString("district"))) {
            paramMap.put("district", paramJson.getString("district"));
        }                  
        List<Map<String, Object>> orgUserCountList = orgUserMapper.getOrgUserCount(paramMap);    
        
        
      //excel文件名
      String fileName = "医护端使用情况统计";
      
     //sheet名
      String sheetName = "各社康医护端累计使用情况";
      //数据封装
      String[][] content=new String[orgUserCountList.size()][4];
      
      for (int i = 0; i < orgUserCountList.size(); i++) {
          content[i] = new String[title.length];
          Map<String, Object> obj = orgUserCountList.get(i);
          content[i][0] = (String) obj.get("district");
          content[i][1] = (String) obj.get("orgRecureName");
          content[i][2] = (String) obj.get("orgRecureUid");
          content[i][3] = obj.get("total")+"";         
      }
      
      //创建HSSFWorkbook 
      HSSFWorkbook wb = this.getHSSFWorkbook(sheetName, title, content, null);
      
      
        String[] orgUserCount = new String[orgUserCountList.size()];
        if(orgUserCountList.size()>0) {
            for(int i=0;i<orgUserCountList.size();i++) {
                Map<String,Object> temp=orgUserCountList.get(i);
                orgUserCount[i]=temp.get("district")+":"+temp.get("orgRecureName")+" "+temp.get("orgRecureUid")+" "+temp.get("total");
            }
        }
       
        try {
            Boolean flag = uploadFileService.initFtpConn();
            if (!flag) {
                throw new LhqException("连接图片服务器异常");
            }
            //上传文件至ftp服务器
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] b = os.toByteArray();
            ByteArrayInputStream in = new ByteArrayInputStream(b);
            JSONObject result = uploadFileService.upload(in, fileName, ".xlsx");
            String code = result.getString("code");
            System.out.println("上传excel至服务器反参:" + result.toJSONString());
            if ("0".equals(code)) {
                return result.getString("path");
            } else {
                throw new LhqException(result.getString("msg"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new LhqException("上传excel至服务器失败");
        } finally {
            uploadFileService.closeFtpConn();
        }
       
    }
    
    
    
    
    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    @SuppressWarnings("deprecation")
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
}

 2,模版导出实现

1)工具类 ExcelGenerateUtil.java

package cn.bsoft.BsoftDevopsManage.util;


import cn.bsoft.BsoftDevopsManage.service.file.UploadFileService;
import cn.bsoft.CommonUtil.entity.LhqException;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * excel 生成模板工具类*/
public class ExcelGenerateUtil {
    /**
     * @param @param filePath  Excel文件路径
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     */
    public static String createExcelTemplate(UploadFileService uploadFileService, String[] handers, List<String[]> downData, String[] downRows, String filePrefix) throws LhqException {
        //创建工作薄
        XSSFWorkbook xb = new XSSFWorkbook();
        //表头样式
        XSSFCellStyle style = xb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //字体样式
        XSSFFont fontStyle = xb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(fontStyle);
        XSSFDataFormat format = xb.createDataFormat();
        style.setDataFormat(format.getFormat("@"));

        //新建sheet
        XSSFSheet sheet1 = xb.createSheet("Sheet1");
        XSSFSheet sheet2 = xb.createSheet("Sheet2");
        XSSFSheet sheet3 = xb.createSheet("Sheet3");

        //生成sheet1内容 第一个sheet的第一行为标题
        XSSFRow rowFirst = sheet1.createRow(0);
        //写标题
        for (int i = 0; i < handers.length; i++) {
            //获取第一行的每个单元格
            XSSFCell cell = rowFirst.createCell(i);
            //设置每列的列宽
            if (i == 0) {
                sheet1.setColumnWidth(i, 8000);
            } else {
                sheet1.setColumnWidth(i, 4000);
            }
            //加样式
            cell.setCellStyle(style);
            //往单元格里写数据
            cell.setCellValue(handers[i]);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        }

        //设置下拉框数据
        String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        int index = 0;
        XSSFRow row = null;
        for (int r = 0; r < downRows.length; r++) {
            //获取下拉对象
            String[] dlData = downData.get(r);

            int rownum = Integer.parseInt(downRows[r]);

            if (dlData.length < 300) {
                //255以内的下拉, 超过255个报错 ,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
                sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 5000, rownum, rownum));
            } else {
                //255以上的下拉，即下拉列表元素很多的情况
                //1、设置有效性
                //String strFormula = "Sheet2!$A$1:$A$5000" ; //Sheet2第A1到A5000作为下拉列表来源数据
                //Sheet2第A1到A5000作为下拉列表来源数据
                String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$5000";
                //设置每列的列宽
                sheet2.setColumnWidth(r, 4000);
                //设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
                //下拉列表元素很多的情况
                sheet1.addValidationData(setDataValidation(strFormula, 1, 50000, rownum, rownum));

                //2、生成sheet2内容
                for (int j = 0; j < dlData.length; j++) {
                    //第1个下拉选项，直接创建行、列
                    if (index == 0) {
                        //创建数据行
                        row = sheet2.createRow(j);
                        //设置每列的列宽
                        sheet2.setColumnWidth(j, 4000);
                        //设置对应单元格的值
                        row.createCell(0).setCellValue(dlData[j]);

                    } else {
                        //非第1个下拉选项
                        int rowCount = sheet2.getLastRowNum();
                        //前面创建过的行，直接获取行，创建列
                        if (j <= rowCount) {
                            //获取行，创建列 设置对应单元格的值
                            sheet2.getRow(j).createCell(index).setCellValue(dlData[j]);
                        } else {
                            //未创建过的行，直接创建行、创建列
                            //设置每列的列宽
                            sheet2.setColumnWidth(j, 4000);
                            //创建行、创建列 //设置对应单元格的值
                            sheet2.createRow(j).createCell(index).setCellValue(dlData[j]);
                        }
                    }
                }
                index++;
            }
        }

        try {
            Boolean flag = uploadFileService.initFtpConn();
            if (!flag) {
                throw new LhqException("连接图片服务器异常");
            }
            //上传文件至ftp服务器
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                xb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] b = os.toByteArray();
            ByteArrayInputStream in = new ByteArrayInputStream(b);
            JSONObject result = uploadFileService.upload(in, filePrefix, ".xlsx");
            String code = result.getString("code");
            System.out.println("上传excel至服务器反参:" + result.toJSONString());
            if ("0".equals(code)) {
                return result.getString("path");
            } else {
                throw new LhqException(result.getString("msg"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new LhqException("上传excel至服务器失败");
        } finally {
            uploadFileService.closeFtpConn();
        }
    }

    /**
     * 设置255以上的下拉
     *
     * @param @param  strFormula
     * @param @param  firstRow   起始行
     * @param @param  endRow     终止行
     * @param @param  firstCol   起始列
     * @param @param  endCol     终止列
     * @param @return
     * @return HSSFDataValidation
     * @throws
     * @Title: SetDataValidation
     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
     */
    private static HSSFDataValidation setDataValidation(String strFormula,
                                                        int firstRow, int endRow, int firstCol, int endCol) {

        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);

        dataValidation.createErrorBox("Error", "Error");
        dataValidation.createPromptBox("", null);

        return dataValidation;
    }

    /**
     * 设置255以下的下拉
     *
     * @param @param  sheet
     * @param @param  textList
     * @param @param  firstRow
     * @param @param  endRow
     * @param @param  firstCol
     * @param @param  endCol
     * @param @return
     * @return DataValidation
     * @throws
     * @Title: setDataValidation
     * @Description: 下拉列表元素不多的情况(255以内的下拉)
     */
    private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        //DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);

        //数据有效性对象
        DataValidation data_validation = helper.createValidation(constraint, regions);
        //DataValidation data_validation = new DataValidation(regions, constraint);

        return data_validation;
    }


    /**
     * @param @param filePath  文件路径
     * @return void
     * @throws
     * @Title: delFile
     * @Description: 删除文件
     */
    public static void delFile(String filePath) {
        java.io.File delFile = new java.io.File(filePath);
        delFile.delete();
    }

}

2)模版下载功能 DownloadGoodsTemplateServiceImpl.java

package cn.bsoft.BsoftDevopsManage.service.scoremall.impl;

import cn.bsoft.BsoftDevopsManage.entity.UserCacheData;
import cn.bsoft.BsoftDevopsManage.service.IFunctionService;
import cn.bsoft.BsoftDevopsManage.service.file.UploadFileService;
import cn.bsoft.BsoftDevopsManage.util.ExcelGenerateUtil;
import cn.bsoft.CommonUtil.entity.LhqException;
import cn.bsoft.CommonUtil.entity.WebRequestTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 下载商品excel模板 service*/
@Service("download-goods-template")
public class DownloadGoodsTemplateServiceImpl implements IFunctionService {

    @Autowired
    private OrgBrandServiceImpl orgBrandService;

    @Autowired
    private OrgCategoryServiceImpl orgCategoryService;

    @Autowired
    private UploadFileService uploadFileService;

    /**
     * 下载商品excel模板, 返回文件路径, 前端拼装域名后访问该文件并下载
     *
     * @param webRequestTemplate
     * @return
     * @throws LhqException
     */
    @Override
    public Object service(WebRequestTemplate webRequestTemplate) throws LhqException {
        String orgUserCache = webRequestTemplate.getOrgUserCache();
        UserCacheData userCacheData = UserCacheData.getInstance(orgUserCache);
        if (null == userCacheData) {
            throw new LhqException("缓存中用户为空, 请登录");
        }
        Integer mgrUserUid = userCacheData.getMgrUserUid();
        if (null == mgrUserUid) {
            throw new LhqException("缓存中用户主键id为空, 请登录");
        }
        //列标题
        String[] handers = {"礼品唯一编码(修改时必传)", "礼品名称", "礼品介绍", "礼品分类", "礼品品牌商", "礼品规格", "所需积分(单位分)", "库存数量", "所属机构唯一编码", "领取地点", "咨询电话", "是否上架", "商品首图", "商品轮播图片","商品详情图片","兑换说明"};

        Map<String, Object> queryParam = new HashMap();
        queryParam.put("pagenum", "1");
        queryParam.put("pagesize", "500");
        queryParam.put("state", "1");
        String[] orgBrandStr = orgBrandService.queryOrgBrandStrArray(queryParam);
        if (null == orgBrandStr) {
            throw new LhqException("请先维护商品品牌基础信息!");
        }
        String[] orgCategoryStr = orgCategoryService.queryOrgCategoryStrArray(queryParam);
        if (null == orgCategoryStr) {
            throw new LhqException("请先维护商品分类基础信息!");
        }

        //下拉框数据
        List<String[]> downData = new ArrayList();
        String[] str3 = {"1:上架", "9:下架"};
        downData.add(orgCategoryStr);
        downData.add(orgBrandStr);
        downData.add(str3);
        //下拉的列序号数组(序号从0开始)
        String[] downRows = {"3", "4", "11"};
        try {
            // 生成表格, 存储在服务器, 返回存储路径, 前端拼装域名后访问资源
            return ExcelGenerateUtil.createExcelTemplate(uploadFileService, handers, downData, downRows,"商品导入模板");
        } catch (Exception e) {
            e.printStackTrace();
            throw new LhqException("文件下载失败!");
        }
    }
}

UploadFileService.java 

package cn.bsoft.BsoftDevopsManage.service.file;

import cn.bsoft.CommonUtil.entity.LhqException;
import cn.bsoft.CommonUtil.entity.UserCache;
import cn.bsoft.CommonUtil.entity.WebRequestTemplate;
import cn.bsoft.CommonUtil.util.FileLoadUtil;
import com.alibaba.fastjson.JSONObject;

import java.io.InputStream;

public interface UploadFileService {

    Object upload(WebRequestTemplate webRequestTemplate, UserCache userCache) throws LhqException;

    JSONObject upFile(String imgBase64) throws LhqException;

    FileLoadUtil getFileLoadUtil();

    Boolean initFtpConn();

    void closeFtpConn();

    /**
     * 通过base64字符串流上传文件至服务器
     *
     * @param imgBase64
     * @return
     */
    JSONObject upload(String imgBase64);

    /**
     * 通过文件流上传文件至服务器
     *
     * @param inputStream
     * @param fileSuffix
     * @return
     */
    JSONObject upload(InputStream inputStream,String filePrefix, String fileSuffix);

}

UploadFileServiceImpl.java

package cn.bsoft.BsoftDevopsManage.service.file.impl;

import cn.bsoft.BsoftDevopsManage.entity.UserCacheData;
import cn.bsoft.BsoftDevopsManage.service.IFunctionService;
import cn.bsoft.BsoftDevopsManage.service.file.UploadFileService;
import cn.bsoft.BsoftDevopsManage.service.logcenter.LogCenterService;
import cn.bsoft.BsoftDevopsManage.util.BuzAuthUtil;
import cn.bsoft.CommonUtil.entity.LhqException;
import cn.bsoft.CommonUtil.entity.UserCache;
import cn.bsoft.CommonUtil.entity.WebRequestTemplate;
import cn.bsoft.CommonUtil.util.FileLoadUtil;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.InputStream;

/*****
 * 文件上传接口 附件支持：PDF和图片格式，图片支持png、jpg、jpeg，单张，文件大小不超过50M）；
 *
 * 只上传文件，返回文件路径*/
@Service("fileUpload")
public class UploadFileServiceImpl implements IFunctionService, UploadFileService {

    @Autowired
    private LogCenterService logCenterService;

    @Value("${ftp.ip}")
    String ftpIp;

    @Value("${ftp.port}")
    String ftpPort;

    @Value("${ftp.username}")
    String ftpUserName;

    @Value("${ftp.password}")
    String ftpPassword;

    private static FileLoadUtil fileLoadUtil;


    @Override
    public Object service(WebRequestTemplate webRequestTemplate) throws LhqException {

        Integer cacheUserId = UserCacheData.getCacheUserId(webRequestTemplate.getOrgUserCache());
        logCenterService.info(webRequestTemplate.getUid(), webRequestTemplate.getAppcode(), Long.valueOf(cacheUserId),
                "文件上传开始执行", Thread.currentThread(), Thread.currentThread().getStackTrace());
        JSONObject paramJson = BuzAuthUtil.datatJsonObject(webRequestTemplate);

        String file = paramJson.getString("uploadFiles");

        // 获取上传文件
        if (StringUtil.isEmpty(file)) {
            throw new LhqException(103, "上传文件为空");
        }


        return upFile(file);
    }

    @Override
    public Object upload(WebRequestTemplate webRequestTemplate, UserCache userCache) throws LhqException {

        logCenterService.info(webRequestTemplate.getUid(), webRequestTemplate.getAppcode(), userCache.getUserid(),
                "文件上传开始执行", Thread.currentThread(), Thread.currentThread().getStackTrace());
        JSONObject paramJson = BuzAuthUtil.datatJsonObject(webRequestTemplate);

        String file = paramJson.getString("uploadFiles");

        // 获取上传文件
        if (StringUtil.isEmpty(file)) {
            throw new LhqException(103, "上传文件为空");
        }


        return upFile(file);
    }

    @Override
    public JSONObject upFile(String imgBase64) {

        JSONObject res = null;
        try {
            fileLoadUtil = getFileLoadUtil();
            boolean flag = fileLoadUtil.initFtpConnection(ftpIp, Integer.parseInt(ftpPort), ftpUserName, ftpPassword);
            if (flag) {
                res = fileLoadUtil.upload(imgBase64, null);
                System.out.println("成功：" + res);
            }
        } finally {
            closeFtpConn();
        }
        return res;
    }

    /**
     * base64字符串 上传文件
     *
     * @param fileBase64
     * @return
     */
    @Override
    public JSONObject upload(String fileBase64) {
        if (null == fileLoadUtil) {
            return null;
        }
        return fileLoadUtil.upload(fileBase64, null);
    }

    /**
     * 文件流 上传文件
     *
     * @param inputStream
     * @param filePrefix  文件名前缀
     * @param fileSuffix  文件名后缀
     * @return
     */
    @Override
    public JSONObject upload(InputStream inputStream, String filePrefix, String fileSuffix) {
        if (null == fileLoadUtil) {
            return null;
        }
        return fileLoadUtil.upload(inputStream, filePrefix, fileSuffix);
    }


    /**
     * 获取文件上传util
     *
     * @return
     */
    @Override
    public FileLoadUtil getFileLoadUtil() {
        if (fileLoadUtil == null) {
            fileLoadUtil = new FileLoadUtil();
        }
        return fileLoadUtil;
    }

    /**
     * 建立ftp服务器连接
     *
     * @return 失败则返回null
     */
    @Override
    public Boolean initFtpConn() {
        System.out.println("开始建立ftp服务器连接....");
        fileLoadUtil = getFileLoadUtil();
        if (StringUtils.isEmpty(ftpIp) || StringUtils.isEmpty(ftpPort) || StringUtils.isEmpty(ftpUserName) || StringUtils.isEmpty(ftpPassword)) {
            System.out.println("服务器初始化参数为空 ftpIp:" + ftpIp + " ,ftpPort:" + ftpPort + " ,ftpUserName:" + ftpUserName + " ,ftpPassword:" + ftpPassword);
            return null;
        }
        return fileLoadUtil.initFtpConnection(ftpIp, Integer.parseInt(ftpPort), ftpUserName, ftpPassword);
    }

    /**
     * 关闭Ftp连接
     */
    @Override
    public void closeFtpConn() {
        fileLoadUtil.closeFtpConnection();
        System.out.println("ftp服务器已关闭连接....");
    }

}

 3,批量导入功能实现

1)ExcelAnalysisUtil.java 解析工具类

package cn.bsoft.BsoftDevopsManage.util;


import cn.bsoft.BsoftDevopsManage.service.file.UploadFileService;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Encoder;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * excel 解析工具类*/
public class ExcelAnalysisUtil {

    /**
     * 2003- 版本的excel
     */
    private final static String excel2003L = ".xls";

    /**
     * 2007+ 版本的excel
     */
    private final static String excel2007U = ".xlsx";

    /**
     * 读取excel文件  将excel解析为List<List<String>>格式数据
     *
     * @return
     * @throws Exception
     */
    public static List<List<String>> readExcel2List(MultipartFile multiFile) throws Exception {
        if (multiFile.isEmpty()) {
            throw new Exception("必须上传Excel文件");
        }
        InputStream input = multiFile.getInputStream();

        //调用读取excel方法读取里面的数据
        return ExcelAnalysisUtil.readExcel(input, multiFile.getOriginalFilename(), 1, 0, 0);
    }


    /**
     * 解析excel, 将excel转换成List<List<String>>格式, 并上传图片至服务器;
     *
     * @param multiFile
     * @return
     */
    public static List<List<String>> readExcel2ListAndUploadImg(MultipartFile multiFile, UploadFileService uploadFileService, String orgRecureUid) throws Exception {
        if (multiFile.isEmpty()) {
            throw new Exception("必须上传Excel文件");
        }
        if (null == uploadFileService) {
            throw new Exception("必须初始化上传服务");
        }
        InputStream input = multiFile.getInputStream();

        //调用读取excel方法读取里面的数据
        return ExcelAnalysisUtil.readExcelAndUploadImg(uploadFileService, input, multiFile.getOriginalFilename(), 1, 0, 0, orgRecureUid);

    }

    /**
     * 读取excel文件 将excel解析为List<Map<String, Object>>格式数据
     *
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> readExcel2Map(MultipartFile multiFile, String filepath) throws Exception {
        if (multiFile.isEmpty()) {
            throw new Exception("必须上传Excel文件");
        }
        InputStream input = multiFile.getInputStream();

        //调用读取excel方法读取里面的数据
        return ExcelAnalysisUtil.readExcel(input, filepath);
    }

    /**
     * @param input
     * @param filepath
     * @param startrow
     * @param startcol
     * @param sheetnum
     * @return
     */
    public static List<List<String>> readExcel(InputStream input, String filepath, int startrow, int startcol, int sheetnum) {
        List<List<String>> varList = new ArrayList<>();
        try {
            Workbook wb = getWorkbook(input, filepath);
            // sheet 从0开始
            Sheet sheet = wb.getSheetAt(sheetnum);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;
            // 表格头的最后一个单元格位置
            int cellNum = sheet.getRow(0).getLastCellNum();
            for (int i = startrow; i < rowNum; i++) {
                Row row = sheet.getRow(i);

                List<String> var = new ArrayList<>();

                for (int j = startcol; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    String cellValue = getCellValue(cell);
                    var.add(cellValue);
                }
                varList.add(var);
            }
            wb.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return removeEmpty(varList);
    }

    /**
     * 将excel解析为List<Map<String, Object>>格式数据
     *
     * @param inputStream
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> readExcel(InputStream inputStream, String filepath) throws Exception {
        int sheetnum = 0, startrow = 0, startcol = 0;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            Workbook workbook = getWorkbook(inputStream, filepath);
            //sheet 从0开始
            Sheet sheet = workbook.getSheetAt(sheetnum);
            //取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;
            //表头的下标和内容
            Map<String, Object> Header = new HashMap<String, Object>();
            //行循环开始
            for (int i = startrow; i < rowNum; i++) {
                //每一行数据的map
                Map<String, Object> map = null;
                //行
                Row row = sheet.getRow(i);
                //中间如果有空行，则跳出当前循环
                if (row == null) {
                    continue;
                }
                //每行的最后一个单元格位置
                int cellNum = row.getLastCellNum();
                //记录数据的行号
                if (i != 0) {
                    map = new HashMap<String, Object>();
                    map.put("lineNumber", i + 1);
                }
                //列循环开始
                for (int j = startcol; j < cellNum; j++) {
                    Cell cell2 = row.getCell(j);
                    String cellValue = getCellValue(cell2);
                    if (i == 0) {
                        Header.put(j + "", cellValue);
                    } else {
                        map.put((String) Header.get(j + ""), cellValue);
                    }
                }
                //每一行数据的过滤,如果一行中所有列都为null,则不生成该行数据,
                if (null != map) {
                    Collection<Object> values = map.values();
                    Set<String> keySet = map.keySet();
                    //总条数
                    long count2 = keySet.stream().filter(o -> !o.equals("lineNumber")).count();
                    //为空的条数
                    long count = values.stream().filter(o -> null == o).count();
                    if (count < count2) {
                        list.add(map);
                    }
                }
            }
            workbook.close();
        } catch (Exception e) {
            throw e;
        }
        return list;
    }

    /**
     * 读取excel文件 将excel解析为List<List<String>>格式数据, 将excel中含有的图片上传至图片服务器
     *
     * @param uploadFileService 图片上传service
     * @param input
     * @param filepath
     * @param startrow
     * @param startcol
     * @param sheetnum
     * @return
     */
    private static List<List<String>> readExcelAndUploadImg(UploadFileService uploadFileService, InputStream input, String filepath, int startrow, int startcol, int sheetnum, String orgRecureUid) {
        List<List<String>> varList = new ArrayList<>();
        try {
            System.out.println("读取excel文件 将excel解析为List ,开始解析中...");
            Workbook wb = getWorkbook(input, filepath);
            // sheet 从0开始
            Sheet sheet = wb.getSheetAt(sheetnum);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;
            // 表格头的最后一个单元格位置
            int cellNum = sheet.getRow(0).getLastCellNum();

            //获取excel表中的图片, 上传图片至服务器, 并返回图片保存地址
            Map<String, String> pictureFilePathMap = getPictureUploadFilePathFromExcel(uploadFileService, wb, sheet, filepath, sheetnum);
            for (int i = startrow; i < rowNum; i++) {
                Row row = sheet.getRow(i);
                List<String> var = new ArrayList<>();
                for (int j = startcol; j < cellNum; j++) {
                    //如果根据 rownum-colnum 在map里面可以获取到数据, 则说明该单元格为图片格式
                    String urlPath = pictureFilePathMap.get(i + "-" + j);
                    if (StringUtils.isEmpty(urlPath)) {
                        //如果缓存用户机构id不为空则不使用用户传入的机构id,用缓存中的机构id
                        if (j == 8 && StringUtils.isNotEmpty(orgRecureUid)) {
                            var.add(orgRecureUid);
                        } else {
                            Cell cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            var.add(cellValue);
                        }
                    } else {
                        var.add(urlPath);
                    }
                }
                varList.add(var);
            }
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return removeEmpty(varList);

    }


    /**
     * 根据excel单元格类型获取excel单元格值
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String cellvalue = null;
        if (null != cell) {
            // 判断当前cell的Type
            switch (cell.getCellType()) {
                // 如果当前cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    short format = cell.getCellStyle().getDataFormat();
                    //excel中的时间格式
                    if (format == 14 || format == 31 || format == 57 || format == 58) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellvalue = sdf.format(date);
                    }
                    //判断当前的cell是否为Date
                    //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。
                    else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，取得该Cell的Date值
                        // 对2014-02-02格式识别不出是日期格式
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = formater.format(date);
                    } else {
                        //如果是纯数字
                        //取得当前Cell的数值
                        cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());

                    }
                    break;
                }
                //如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getStringCellValue();
                    if (StringUtils.isNotEmpty(cellvalue)) {
                        cellvalue = cellvalue.split(":")[0];
                    }
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    cellvalue = cell.getNumericCellValue() + "";
                    if (StringUtils.isNotEmpty(cellvalue)) {
                        cellvalue = cellvalue.split(":")[0];
                    }
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    cellvalue = null;
                    break;
                //默认的Cell值
                default: {
                    cellvalue = "";
                }
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    /**
     * 获取excel表中的图片, 上传图片至服务器, 并返回图片保存地址
     *
     * @return
     * @Param fis 文件输入流
     * @Param sheetNum Excel表中的sheet编号
     */
    public static Map<String, String> getPictureUploadFilePathFromExcel(UploadFileService uploadFileService, Workbook wookbook, Sheet sheet, String filepath, int sheetNum) throws Exception {
        //创建Map
        Map<String, List<PictureData>> pictures = new HashMap<String, List<PictureData>>();
        // 判断用07还是03的方法获取图片
        if (filepath.endsWith(".xls")) {
            pictures = getPictures1((HSSFSheet) sheet);
        } else if (filepath.endsWith(".xlsx")) {
            pictures = getPictures2((XSSFSheet) sheet);
        }
        System.out.println("获取excel表中的图片, 解析后的图片集合为:" + pictures.toString());

        return uploadImg(uploadFileService, pictures);
    }

    /**
     * 获取图片和位置, 支持一个单元格多图 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, List<PictureData>> getPictures1(HSSFSheet sheet) throws IOException {
        Map<String, List<PictureData>> map = new HashMap<String, List<PictureData>>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                picture.getPictureData();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                //如果存在这个坐标KEY表示相同单元格中的图片,直接集合添加该图片,不存在该坐标key直接创建添加
                if (map.containsKey(key)) {
                    map.get(key).add(pdata);
                } else {
                    List<PictureData> arrayList = new ArrayList<PictureData>();
                    arrayList.add(pdata);
                    map.put(key, arrayList);
                }
            }
        }
        return map;
    }

    /**
     * 获取图片和位置, 支持一个单元格多图 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, List<PictureData>> getPictures2(XSSFSheet sheet) throws IOException {
        Map<String, List<PictureData>> map = new HashMap<String, List<PictureData>>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    PictureData pictureData = picture.getPictureData();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    //如果存在这个坐标KEY表示相同单元格中的图片,直接集合添加该图片,不存在该坐标key直接创建添加
                    if (map.containsKey(key)) {
                        map.get(key).add(pictureData);
                    } else {
                        List<PictureData> arrayList = new ArrayList<PictureData>();
                        arrayList.add(pictureData);
                        map.put(key, arrayList);
                    }
                }
            }
        }
        return map;
    }

    /**
     * 图片上传, 返回图片保存地址, 支持一个单元格多图, 同一个单元格多图用,分隔
     *
     * @param uploadFileService
     * @param sheetMap
     * @throws Exception
     */
    public static Map<String, String> uploadImg(UploadFileService uploadFileService, Map<String, List<PictureData>> sheetMap) throws Exception {
        Map<String, String> picMap = new HashMap<>();
        if (sheetMap.isEmpty()) {
            return picMap;
        }
        System.out.println("图片上传, 返回图片保存地址 入参:" + sheetMap.toString());

        try {
            Object[] key = sheetMap.keySet().toArray();
            Boolean flag = uploadFileService.initFtpConn();
            if (!flag) {
                return picMap;
            }

            System.out.println("图片上传, 返回图片保存地址 FTP服务器建立连接成功, 开始处理图片上传:");
            for (int i = 0; i < sheetMap.size(); i++) {
                List<PictureData> pictureData = sheetMap.get(key[i]);
                for (int j = 0; j < pictureData.size(); j++) {
                    // 获取图片流
                    PictureData pic = pictureData.get(j);
                    // 获取图片索引  row-col
                    String picName = key[i].toString();
                    String[] split = picName.split("-");
                    String rowNum = split[0];
                    String colNum = split[1];
                    // 获取图片格式
                    String ext = pic.suggestFileExtension();
                    byte[] data = pic.getData();
                    String image = new BASE64Encoder().encode(data);
                    if (StringUtil.isNotEmpty(image)) {
                        image = "data:image/jpeg;base64," + image;
                    }
                    //文件上传至图片服务器, 并获取图片保存路径
                    JSONObject uploadResult = uploadFileService.upload(image);
                    System.out.println("图片上传 uploadImg result:" + uploadResult.toJSONString());
                    String code = uploadResult.getString("code");
                    if ("0".equals(code)) {
                        String path = uploadResult.getString("path");
                        String rowColKey = rowNum + "-" + colNum;
                        if (picMap.containsKey(rowColKey)) {
                            picMap.put(rowColKey, picMap.get(rowColKey) + "," + path);
                        } else {
                            picMap.put(rowColKey, path);
                        }
                    } else {
                        //图片上传失败
                        picMap.put(rowNum + "-" + colNum, "-1");
                    }
                }
            }
            System.out.println("图片上传, 返回图片保存地址 上传至图片服务器完成 picMap:" + picMap.toString());
        } catch (Exception e) {
            e.printStackTrace();
            return picMap;
        } finally {
            uploadFileService.closeFtpConn();
        }
        return picMap;
    }


    private static List<List<String>> removeEmpty(List<List<String>> datas) {
        List<List<String>> list = new ArrayList<>();
        for (List<String> data : datas) {
            int index = 0;
            for (String str : data) {
                if (StringUtils.isEmpty(str)) {
                    index++;
                }
            }
            if (index != data.size()) {
                list.add(data);
            }
        }
        return list;
    }

    public static Workbook getWorkbook(InputStream inputStream, String file) throws Exception {
        Workbook book = null;
        String fileType = file.substring(file.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            book = new HSSFWorkbook(inputStream);
        } else if (excel2007U.equals(fileType)) {
            book = new XSSFWorkbook(inputStream);
        } else {
            throw new Exception("解析文件格式有误");
        }
        return book;
    }

    /**
     * 解析excel, 将excel转换成List<List<String>>格式, 并上传图片至服务器;
     *
     * @param multiFile
     * @return
     */
    public static List<List<String>> readQuestionExcel2ListAndUploadImg(MultipartFile multiFile, UploadFileService uploadFileService) throws Exception {
        if (multiFile.isEmpty()) {
            throw new Exception("必须上传Excel文件");
        }
        if (null == uploadFileService) {
            throw new Exception("必须初始化上传服务");
        }
        InputStream input = multiFile.getInputStream();

        //调用读取excel方法读取里面的数据
        return ExcelAnalysisUtil.readQuestionExcelAndUploadImg(uploadFileService, input, multiFile.getOriginalFilename(), 1, 0, 0);

    }
    
    /**
     * 读取试题excel文件 将excel解析为List<List<String>>格式数据, 
     * 将excel中含有的图片上传至图片服务器
     * @param uploadFileService 图片上传service
     * @param input
     * @param filepath
     * @param startrow
     * @param startcol
     * @param sheetnum
     * @return
     */
    private static List<List<String>> readQuestionExcelAndUploadImg(UploadFileService uploadFileService, InputStream input, String filepath, int startrow, int startcol, int sheetnum) {
        List<List<String>> varList = new ArrayList<>();
        try {
            System.out.println("读取excel文件 将excel解析为List ,开始解析中...");
            Workbook wb = getWorkbook(input, filepath);
            // sheet 从0开始
            Sheet sheet = wb.getSheetAt(sheetnum);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;
            // 表格头的最后一个单元格位置
            int cellNum = sheet.getRow(0).getLastCellNum();

            //获取excel表中的图片, 上传图片至服务器, 并返回图片保存地址
            Map<String, String> pictureFilePathMap = getPictureUploadFilePathFromExcel(uploadFileService, wb, sheet, filepath, sheetnum);                      
            for (int i = startrow; i < rowNum; i++) {
                Row row = sheet.getRow(i);
                List<String> var = new ArrayList<>();
                for (int j = startcol; j < cellNum; j++) {
                    //如果根据 rownum-colnum 在map里面可以获取到数据, 则说明该单元格为图片格式
                    String urlPath = pictureFilePathMap.get(i + "-" + j);
                    if (StringUtils.isEmpty(urlPath)) {
                        //选项、正确答案和解析
                        if (j ==4 || j==5||j==7) {
                             Cell cell = row.getCell(j);  
                             if(cell!=null) {
                                 var.add(cell.toString());                                 
                             }else {
                                 var.add(null);
                             }
                        } else {
                            Cell cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            var.add(cellValue);
                        }
                    } else {
                        var.add(urlPath);
                    }
                }
                varList.add(var);
            }
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return removeEmpty(varList);

    }
    
}

2)BatchImportGoodsServiceImpl.java

package cn.bsoft.BsoftDevopsManage.service.scoremall.impl;

import cn.bsoft.BsoftDevopsManage.entity.OrgGoodsInfo;
import cn.bsoft.BsoftDevopsManage.entity.UserCacheData;
import cn.bsoft.BsoftDevopsManage.service.IFunctionService;
import cn.bsoft.BsoftDevopsManage.service.file.impl.UploadFileServiceImpl;
import cn.bsoft.BsoftDevopsManage.service.logcenter.LogCenterService;
import cn.bsoft.BsoftDevopsManage.util.Base64DecodedMultipartFile;
import cn.bsoft.BsoftDevopsManage.util.ExcelAnalysisUtil;
import cn.bsoft.CommonUtil.entity.LhqException;
import cn.bsoft.CommonUtil.entity.WebRequestTemplate;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 批量导入商品 service*/
@Service("batch-import-goods")
public class BatchImportGoodsServiceImpl implements IFunctionService {
    /**
     * 存系统日志到远程日志中心
     */
    @Autowired
    private LogCenterService logCenterService;

    @Autowired
    private UploadFileServiceImpl uploadFileService;

    @Autowired
    private OrgGoodsInfoServiceImpl orgGoodsInfoService;

    @Autowired
    private OrgGoodsImgServiceImpl orgGoodsImgService;

    /**
     * 批量导入商品
     * 1. 解析excel
     * 2. 获取excel图片并上传图片获取图片地址
     * 2. 循环校验参数合法性并插入商品;
     * 3. 将操作结果返回给前端;
     *
     * @param webRequestTemplate
     * @return
     * @throws LhqException
     */
    @Override
    public Object service(WebRequestTemplate webRequestTemplate) throws LhqException {
        try {
            System.out.println("开始批量导入商品, 入口: " + webRequestTemplate.toString());
            String orgUserCache = webRequestTemplate.getOrgUserCache();
            UserCacheData userCacheData = UserCacheData.getInstance(orgUserCache);
            if (null == userCacheData) {
                throw new LhqException("缓存中用户为空, 请登录");
            }
            String orgRecureUid = userCacheData.getOrgRecureUid();
            List<String> filesBase64 = webRequestTemplate.getFilesBase64();
            if (CollectionUtils.isEmpty(filesBase64)) {
                throw new LhqException("上传文件失败!");
            }
            if (filesBase64.size() > 1) {
                throw new LhqException("不支持多文件上传!");
            }
            MultipartFile uploadFile = Base64DecodedMultipartFile.base64ToMultipart(filesBase64.get(0));
            if (null == uploadFile) {
                throw new LhqException("文件转换失败!");
            }
            String originalFilename = uploadFile.getOriginalFilename();
            if (!originalFilename.endsWith(".xlsx") && !originalFilename.endsWith(".xls")) {
                throw new LhqException("上传文件格式错误, 仅支持后缀为.xlsx、.xls格式文件!");
            }

            List<List<String>> fileDataList = ExcelAnalysisUtil.readExcel2ListAndUploadImg(uploadFile, uploadFileService, orgRecureUid);
            if (CollectionUtils.isEmpty(fileDataList)) {
                throw new LhqException("上传文件格式错误, 未解析到数据, 请检查后重新上传!");
            }
            System.out.println("批量导入商品, 解析excel成功, 操作批量插入数据至数据库: " + JSONArray.toJSONString(fileDataList));
            return batchAddGoods(fileDataList, webRequestTemplate, userCacheData);
        } catch (Exception e) {
            logCenterService.error(webRequestTemplate.getUid(), webRequestTemplate.getAppcode(), 0L, "批量导入商品解析文件发生异常 ：" + e.getMessage(), Thread.currentThread(), Thread.currentThread().getStackTrace());
            throw new LhqException("文件解析失败!");
        }
    }

    /**
     * 批量插入商品
     *
     * @param fileDataList
     * @return
     */
    private JSONArray batchAddGoods(List<List<String>> fileDataList, WebRequestTemplate webRequestTemplate, UserCacheData userCacheData) throws LhqException {
        JSONArray resutJson = new JSONArray();
        Integer mgrUserUid = userCacheData.getMgrUserUid();

        try {
            for (int i = 0; i < fileDataList.size(); i++) {
                List<String> fileData = fileDataList.get(i);
                JSONObject obj = new JSONObject();
                //校验入参, 封装商品实体
                OrgGoodsInfo orgGoodsInfo = generateOrgGoodsInfo(fileData, obj, i);
                if (obj.size() != 0) {
                    resutJson.add(obj);
                    continue;
                }

                if (null == orgGoodsInfo) {
                    resutJson.add(obj);
                    continue;
                }
                //礼品唯一编码(修改时必传)
                Integer goodsUid = orgGoodsInfo.getGoodsUid();
                String goodsImg = orgGoodsInfo.getGoodsImg();
                Long price = orgGoodsInfo.getPrice();
                if (null == price) {
                    price = 0L;
                    orgGoodsInfo.setPrice(price);
                }
                orgGoodsInfo.setSource("1");

                //商品id为空则新增, 不为空则修改
                if (!StringUtils.isEmpty(goodsUid)) {
                    Map<String, Object> map = new HashMap();
                    map.put("goodsUid", goodsUid);
                    OrgGoodsInfo orgGoodsInfoDB = orgGoodsInfoService.queryOrgGoodsInfoByParam(map);
                    if (null == orgGoodsInfoDB) {
                        obj.put("msg", "关键信息填写错误,数据库中不存在该礼品唯一编码, 无法修改, 请检查礼品唯一编码(修改时必传)");
                        obj.put("state", "-1");
                        obj.put("row", i + 2);
                        obj.put("goodsImg", goodsImg);
                        resutJson.add(obj);
                        continue;
                    } else {
                        orgGoodsInfo.setGoodsUid(orgGoodsInfoDB.getGoodsUid());
                        orgGoodsInfo.setUpdateTime(new Date());
                        orgGoodsInfo.setUpdateUserUid(mgrUserUid);
                        int updateResult = orgGoodsInfoService.updateByPrimaryKeySelective(orgGoodsInfo);
                        if (updateResult > 0) {
                            obj.put("msg", "商品修改成功");
                            obj.put("state", "0");
                            obj.put("row", i + 2);
                            obj.put("goodsImg", goodsImg);
                            resutJson.add(obj);

                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("goodsUid", goodsUid);
                            jsonObject.put("goodsName", orgGoodsInfo.getGoodsName());
                            jsonObject.put("primaryUrl", orgGoodsInfo.getPrimaryUrl());
                            jsonObject.put("galleryUrl", orgGoodsInfo.getGalleryUrl());
                            jsonObject.put("descUrl", orgGoodsInfo.getDescUrl());
                            // 批量上传商品图片
                            orgGoodsImgService.updateOrgGoodsImgBatch(jsonObject, mgrUserUid);
                            continue;
                        } else {
                            obj.put("msg", "商品修改失败 " + updateResult);
                            obj.put("state", "0");
                            obj.put("row", i + 2);
                            obj.put("goodsImg", goodsImg);
                            resutJson.add(obj);
                            continue;
                        }
                    }
                } else {
                    orgGoodsInfo.setAddTime(new Date());
                    orgGoodsInfo.setAddUserUid(mgrUserUid);
                    orgGoodsInfo.setUpdateTime(new Date());
                    orgGoodsInfo.setUpdateUserUid(mgrUserUid);
                    int addResult = orgGoodsInfoService.insertSelective(orgGoodsInfo);
                    if (addResult > 0) {
                        obj.put("msg", "商品新增成功");
                        obj.put("state", "0");
                        obj.put("row", i + 2);
                        obj.put("goodsImg", goodsImg);
                        resutJson.add(obj);

                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("orgGoodsUid", goodsUid);
                        jsonObject.put("goodsName", orgGoodsInfo.getGoodsName());
                        jsonObject.put("primaryUrl", orgGoodsInfo.getPrimaryUrl());
                        jsonObject.put("galleryUrl", orgGoodsInfo.getGalleryUrl());
                        jsonObject.put("descUrl", orgGoodsInfo.getDescUrl());
                        //批量上传商品图片
                        orgGoodsImgService.addOrgGoodsImgBatch(jsonObject, mgrUserUid);
                        continue;
                    } else {
                        obj.put("msg", "商品新增失败 " + addResult);
                        obj.put("state", "-2");
                        obj.put("row", i + 2);
                        obj.put("goodsImg", goodsImg);
                        resutJson.add(obj);
                        continue;
                    }
                }
            }
            logCenterService.info(webRequestTemplate.getUid(), webRequestTemplate.getAppcode(), 0L, "批量导入商品完成, 返回前端出参：" + resutJson, Thread.currentThread(), Thread.currentThread().getStackTrace());
        } catch (Exception e) {
            JSONObject obj = new JSONObject();
            obj.put("msg", "批量导入商品发生异常 " + e.getMessage());
            obj.put("state", "-3");
            obj.put("row", "-1");
            resutJson.add(obj);
            logCenterService.error(webRequestTemplate.getUid(), webRequestTemplate.getAppcode(), 0L, "批量导入商品发生异常, 返回前端出参：" + resutJson, Thread.currentThread(), Thread.currentThread().getStackTrace());
        }
        return resutJson;
    }

    /**
     * 校验入参, 封装商品信息实体
     *
     * @param fileData
     * @param obj
     * @param index
     * @return
     */
    private OrgGoodsInfo generateOrgGoodsInfo(List<String> fileData, JSONObject obj, int index) {
        OrgGoodsInfo orgGoodsInfo = new OrgGoodsInfo();
        //礼品唯一编码(修改时必传)
        String goodsUidStr = fileData.get(0);
        Integer goodsUid = StringUtils.isEmpty(goodsUidStr) ? null : Integer.parseInt(goodsUidStr);
        //礼品名称
        String goodsName = fileData.get(1);
        //礼品介绍
        String goodsDesc = fileData.get(2);
        //礼品分类
        String categoryUid = fileData.get(3);
        //礼品品牌商
        String brandUid = fileData.get(4);
        //礼品规格
        String goodsSpec = fileData.get(5);
        //所需积分(单位分，为0时不能使用积分)
        String integral = fileData.get(6);
        //库存数量
        String stockNum = fileData.get(7);
        //所属机构
        String orgRecureUid = fileData.get(8);
        //领取地点
        String address = fileData.get(9);
        //咨询电话
        String tel = fileData.get(10);
        //是否上架 1-上架,9-下架
        String state = fileData.get(11);
        //商品图片(过渡阶段, 该字段暂时设置为首图值)
        String goodsImg = fileData.get(12);
        //商品列表首图url
        String primaryUrl = fileData.get(12);
        //商品轮播图url,多图用,隔开
        String galleryUrl = fileData.get(13);
        //商品详情图url,多图用,隔开
        String descUrl = fileData.get(14);
        //兑换说明
        String exchangeDesc = fileData.get(15);

        if (StringUtils.isEmpty(goodsName)) {
            obj.put("msg", "关键信息填写错误,请检查礼品名称");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }

        if (StringUtils.isEmpty(categoryUid)) {
            obj.put("msg", "关键信息填写错误,请检查礼品分类");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(brandUid)) {
            obj.put("msg", "关键信息填写错误,请检查礼品品牌商");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(goodsSpec)) {
            obj.put("msg", "关键信息填写错误,请检查礼品规格");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(integral)) {
            obj.put("msg", "关键信息填写错误,请检查所需积分");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(stockNum)) {
            obj.put("msg", "关键信息填写错误,请检查库存数量");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(orgRecureUid)) {
            obj.put("msg", "关键信息填写错误,请检查所属机构");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(address)) {
            obj.put("msg", "关键信息填写错误,请检查领取地点");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        if (StringUtils.isEmpty(tel)) {
            obj.put("msg", "关键信息填写错误,请检查咨询电话");
            obj.put("state", "-1");
            obj.put("row", index + 2);
            return null;
        }
        //没传入状态则默认状态为上架
        if (StringUtils.isEmpty(state)) {
            state = "1";
        }

        orgGoodsInfo.setGoodsUid(goodsUid);
        orgGoodsInfo.setGoodsName(goodsName);
        orgGoodsInfo.setGoodsDesc(goodsDesc);
        orgGoodsInfo.setCategoryUid(Integer.valueOf(categoryUid));
        orgGoodsInfo.setBrandUid(Integer.valueOf(brandUid));
        orgGoodsInfo.setGoodsSpec(goodsSpec);
        orgGoodsInfo.setIntegral(Long.valueOf(integral));
        orgGoodsInfo.setStockNum(Integer.valueOf(stockNum));
        orgGoodsInfo.setOrgRecureUid(orgRecureUid);
        orgGoodsInfo.setAddress(address);
        orgGoodsInfo.setTel(tel);
        orgGoodsInfo.setState(state);
        // 默认审核状态为 0-未审核
        orgGoodsInfo.setAuditState("0");
        orgGoodsInfo.setGoodsImg(goodsImg);
        orgGoodsInfo.setPrimaryUrl(primaryUrl);
        orgGoodsInfo.setGalleryUrl(galleryUrl);
        orgGoodsInfo.setDescUrl(descUrl);
        orgGoodsInfo.setExchangeDesc(exchangeDesc);
        return orgGoodsInfo;
    }
}