excel批量导入数据

工具类：
import com.github.pagehelper.util.StringUtil;  
  6 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;


public class ExcelUtil {

    private ExcelUtil() { throw new IllegalStateException("Utility class"); }

    public static String getPostfix(String path) {
        String pathContains = ".";
        if (StringUtils.isEmpty(path) || !path.contains(pathContains)) {
            return null;
        }
        return path.substring(path.lastIndexOf(pathContains) + 1, path.length()).trim();
    }

    /**
     * 解析Excel文件
     *
     * @return
     */
    public static Workbook analysisExcelFile(MultipartFile file) throws BusinessException {
        if (file == null || file.getSize() == 0) {
            throw new BusinessException("不能上传空文件");
        }
        //得到文件名称
        String fileName = file.getOriginalFilename();
        //获取文件的拓展名
        String postfix = ExcelUtil.getPostfix(fileName);

        Workbook workbook;
        try {
            InputStream is = file.getInputStream();
            String fileType = "xlsx";
            if (fileType.equals(postfix)) {
                workbook = new XSSFWorkbook(is);
            } else {
                workbook = new HSSFWorkbook(is);
            }
        } catch (IOException e) {
            throw new BusinessException("文件解析失败");
        }
        if (workbook == null || workbook.getSheetAt(0) == null) {
            throw new BusinessException("不能上传空文件");
        }
        return workbook;
    }

    /**
     * 将Cell定义为文本类型，取值
     *
     * @param cell Cell
     * @return 字符串格式的值
     */
    public static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    /**
     * 判断上传文件表头是否正确
     *
     * @param titleRow    标题行
     * @param titleValues 模板标题
     * @return true/false
     */
    public static boolean validExcelTitle(Row titleRow, String[] titleValues) {
        if (titleRow == null) {
            // 模板不正确
            return false;
        }
        //判断上传文件的标题行是否符合
        for (int i = 0; i < titleValues.length; i++) {
            String titleValue = titleValues[i];
            Cell cell = titleRow.getCell(i);
            if (cell == null) {
                return false;
            }
            String cellValue = cell.getStringCellValue();
            if (!titleValue.equals(cellValue)) {
                return false;
            }
        }

        return true;
    }

    /**
     * 验证关键数据不为空
     *
     * @param rowItem   行号
     * @param valueList 数据
     * @param item      验证字段序号
     */
    public static String validKeyValue(int rowItem, List<String> valueList, String[] titleNames, int[] item) {
        boolean isEmpty = true;
        Integer firstEmptyItem = null;
        for (int i : item) {
            if (StringUtil.isEmpty(valueList.get(i))) {
                if (firstEmptyItem == null) {
                    firstEmptyItem = i;
                }
                if (!isEmpty) {
                    break;
                }
            } else {
                isEmpty = false;
            }
        }

        if (isEmpty) {
            return "第" + rowItem + "行为空数据";
        } else if (firstEmptyItem != null) {
            return "第" + rowItem + "行的" + titleNames[firstEmptyItem] + "为空";
        }

        return null;
    }

    /**
     * 生成Excel2007
     *
     * @param title   报表名称
     * @param headers 标题行名称
     * @return XSSFWorkbook
     */
    private static XSSFWorkbook exportExcel2007(String sheetName, String title, String[] headers) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet;
        if (StringUtils.isEmpty(sheetName)) {
            sheet = workbook.createSheet();
        } else {
            sheet = workbook.createSheet(sheetName);
        }

        // 创建标题样式1
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        XSSFFont headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 20);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        // 创建标题样式1
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        XSSFFont titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 10);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        //报表名称
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
        XSSFCell cellTitle = row.createCell(0);
        cellTitle.setCellValue(title);
        cellTitle.setCellStyle(headerStyle);

        XSSFRow rowHeader = sheet.createRow(1);
        XSSFCell cellHeader;
        for (int i = 0; i < headers.length; i++) {
            cellHeader = rowHeader.createCell(i);
            cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
            cellHeader.setCellStyle(titleStyle);
        }

        return workbook;
    }



    /**
     * 生成Excel2007
     *
     * @param set      Excel生成参数
     * @param response 服务器响应
     * @return
     */
    public static void exportExcel2007(ExcelSet set, HttpServletResponse response) {
        // 声明一个工作薄
        XSSFWorkbook workbook = exportExcel2007(set.getSheetName(), set.getReportName(), set.getTitleName());

        List<String[]> values = set.getValues();
        if (values != null && !values.isEmpty()) {
            // 生成一个表格
            XSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 0; i < values.size(); i++) {
                XSSFRow valueRow = sheet.createRow(i + 2);
                String[] objs = values.get(i);
                for (int j = 0; j < objs.length; j++) {
                    Cell cell = valueRow.createCell(j);
                    cell.setCellValue(objs[j]);
                }
            }
        }

        setResponseHeader(response, set.getFileName());
        try {
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 发送响应流方法
     *
     * @param response 服务器响应
     * @param fileName 下载文件的文件名
     */
    private static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            response.reset();
            String charsetName = "UTF-8";
            fileName = new String(fileName.getBytes(), charsetName);
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.addHeader("Transfer-Encoding","chunked");

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 生成导出Excel文件名称
     * @param did 经销商编码
     * @return String
     */
    public static String getExcelFileName(String did){
        String nowStr = DateUtil.formartDate(new Date(),"yyyyMMddHHmmss");
        return did+"_"+nowStr+".xlsx";
    }
}


Service：
@Transactional(rollbackFor = Exception.class)
public List<String> uploadSupplierExcelFile(String pDid,String pUserId,MultipartFile pExcelFile) throws BusinessException {
    // 解析文件
    Workbook workbook = ExcelUtil.analysisExcelFile(pExcelFile);
    // 获取文件的第一个sheet页
    Sheet sheet = workbook.getSheetAt(0);
    //验证模板是否正确
    //模板表头标题内容
    String [] titleValues = ExcelTitle.EXCEL_SUPPLIER_TITLE;
    Row firstRow = sheet.getRow(0);
    if(!ExcelUtil.validExcelTitle(firstRow,titleValues)){
        throw  new BusinessException("上传模板错误");
    }

    //错误信息保存集合
    List<String> templateErrorList = new ArrayList<>();
    //保存<供应商编号,DID>，用于检测，是否存在相同供应商
    Map<String,String> cMap = new HashMap<>();
    //要保存的供应商数据
    List<BaseSupplier> baseSupplierList = new ArrayList<>();
    //得到文件中最大行号
    int lasrRowNum = sheet.getLastRowNum();
    //最大读取列数
    int maxColumn = 8;
    // 开始数据处理
    for(int rowNum = 1; rowNum <= lasrRowNum; rowNum++){
        Row row = sheet.getRow(rowNum);
        if(row == null){
            continue;
        }
        int nowRowNum = rowNum + 1;
        //得到行数据
        List<String> valueList = new ArrayList<>(maxColumn);
        for(int i=0;i<maxColumn;i++){
            String value = ExcelUtil.getCellStringValue(row.getCell(i));
            valueList.add(value);
        }
        //验证字段的序号
        int [] validEnptyItem = {0,1,2,3,4,5,6,7};
        String errorMsg = ExcelUtil.validKeyValue(nowRowNum,valueList,ExcelTitle.EXCEL_SUPPLIER_TITLE,validEnptyItem);
        if(!StringUtils.isEmpty(errorMsg)){
            templateErrorList.add(errorMsg);
        }else{
            BaseSupplier baseSupplier = new BaseSupplier();
            baseSupplier.setSupplierName(valueList.get(0));
            baseSupplier.setSubDept(valueList.get(1));
            baseSupplier.setSubPerson(valueList.get(2));
            baseSupplier.setSupplierType(valueList.get(3));
            baseSupplier.setRate(valueList.get(4));
            baseSupplier.setLinkman(valueList.get(5));
            baseSupplier.setPhone(valueList.get(6));
            baseSupplier.setAddr(valueList.get(7));
            //判断本条数据是否存在空数据
            if(validExcelData(nowRowNum,baseSupplier,templateErrorList,cMap)){
                String newRowId = codeGenerationService.generateCode(CodeCom.CODE_SUPPLIER_PRE);
                baseSupplier.setRowId(newRowId);
                baseSupplier.setUserid(pUserId);
                //将数据加入到供应商列表中
                baseSupplierList.add(baseSupplier);
            }
        }

    }

    if(baseSupplierList.isEmpty()){
        templateErrorList.add("文件中无有效数据");
    }
    //保存导入数据
    if(templateErrorList.isEmpty()){
        saveBaseSuppilerrBatch(baseSupplierList);
    }


    return templateErrorList;
}

controller：

/**
 * 批量保存供应商信息
 * @param pBaseSuppliers 供应商信息
 * @return int
 */
@Transactional(rollbackFor = Exception.class)
public int saveBaseSuppilerrBatch(List<BaseSupplier> pBaseSuppliers){
    //分批次保存信息供应商信息
    //每批保存200条数据
    Integer batchCount = 200;
    int count = 0;
    for (int i = 0; i < Math.ceil(pBaseSuppliers.size()/batchCount.doubleValue()); i++) {
        List<BaseSupplier> zBaseCustomers = pBaseSuppliers.subList(i * batchCount,
                (i + 1) * batchCount > pBaseSuppliers.size() ? pBaseSuppliers.size() : (i + 1) * batchCount);
        int zCount = baseSupplierMapper.insertBatch(zBaseCustomers);
        count += zCount;
    }
    return count;
}

private boolean validExcelData(int pRowNum,BaseSupplier pSupplier,
                               List<String> pTemplateErrorList,
                               Map<String,String> cMap){

    //判断是否已有重复供应商数据
    String cNameHave = cMap.get(pSupplier.getSupplierName());
    if(cNameHave==null){
        cMap.put(pSupplier.getSupplierName(),pSupplier.getSupplierName());
    }else{
        pTemplateErrorList.add("第"+pRowNum+"行的供应商名称重复!");
        return false;
    }
    //查询已存在的数据，判断供应商名称是否已经存在
    int count = countByAlikeName(null,pSupplier.getSupplierName());
    if(count>0){
        pTemplateErrorList.add("第"+pRowNum+"行的供应商名称已存在!");
        return false;
    }
    return true;
}
/**
 * 根据名称查询符合条件的数据数量
 * @param pRowId 查询时不过滤的供应商编码
 * @param pName 供应商名称
 * @return 执行行数
 */
public int countByAlikeName(String pRowId,String pName){
    BaseSupplierExample example = new BaseSupplierExample();
    BaseSupplierExample.Criteria criteria = example.createCriteria();
    criteria.andSupplierNameLike(pName);
    if(pRowId!=null){
        criteria.andRowIdNotEqualTo(pRowId);
    }
    return baseSupplierMapper.countByExample(example);
}

保存方法：
/**
 * 批量导入供应商数据
 * @param pExcelFile
 * @return
 */
@ApiOperation(value = "批量导入供应商资料", notes = "批量导入供应商资料")
@PostMapping(value = "/uploadSupplierExcelFile")
public JsonResult uploadCustomerExcelFile(
        @ApiParam(name = "pExcelFile", value = "导入文件", required = true)
        @RequestParam("pExcelFile") MultipartFile pExcelFile) {
    UserInfo info = this.getUserInfo();
    String pDid = info.getDid();
    String pUserId = info.getUserId();
    logger.info("批量导入供应商资料接口请求参数,pDid:{},pUserId:{}", pDid, pUserId);
    try {
        List<String> errorMsgList = baseSupplierService.uploadSupplierExcelFile(pDid, pUserId, pExcelFile);
        if (errorMsgList != null && !errorMsgList.isEmpty()) {
            return JsonResult.get(HttpReturnEnums.ParaError, errorMsgList);
        }
    } catch (BusinessException e) {
        return JsonResult.get(HttpReturnEnums.ParaError, null, e.getMessage());
    }
    return JsonResult.get(HttpReturnEnums.Success);
}

mapper:
int insertBatch(@Param("supplierList") List<BaseSupplier> record);

xml  sql:
<insert id="insertBatch" parameterType="com.shinho.dc3.master.models.po.BaseSupplier" >
    insert into base_supplier (
    row_id, supplier_name, sub_dept,
    sub_person, supplier_type, rate,
    linkman, phone, addr,userid,did)
    values
    <foreach collection="supplierList" index="index" item="supplierList" separator=",">
        (
        #{supplierList.rowId,jdbcType=VARCHAR}, #{supplierList.supplierName,jdbcType=VARCHAR}, #{supplierList.subDept,jdbcType=VARCHAR},
        #{supplierList.subPerson,jdbcType=VARCHAR}, #{supplierList.supplierType,jdbcType=VARCHAR}, #{supplierList.rate,jdbcType=VARCHAR},
        #{supplierList.linkman,jdbcType=VARCHAR}, #{supplierList.phone,jdbcType=VARCHAR}, #{supplierList.addr,jdbcType=VARCHAR},
        #{supplierList.userid,jdbcType=VARCHAR}, #{supplierList.did,jdbcType=VARCHAR}
        )
    </foreach>
</insert>
public class ExcelTitle {
    /**
     * EXCEL导入表头-供应商档案
     */
    public static final String [] EXCEL_SUPPLIER_TITLE = {"供应商名称","分管部门","分管人员","供应商类型","税率","联系人","联系电话","供应商地址"};
}