Excel解析导入

controller

package com.basetnt.ad.game.xiaoxiaoqi.controller;

import com.basetnt.ad.game.xiaoxiaoqi.common.Result;
import com.basetnt.ad.game.xiaoxiaoqi.model.AdConfig;
import com.basetnt.ad.game.xiaoxiaoqi.service.ResolveExcelService;
import io.swagger.annotations.Api;
import java.util.List;
import javax.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * @Desc
 * @Date 2019/4/26 15:09
 * @Author cui_yl
 */
@Api(tags = "ExcelController", description = "解析exce")
@RestController
@RequestMapping("/excel")
public class ExcelController {
	private static final Logger logger = LoggerFactory.getLogger(ExcelController.class);

	@Resource(name = "resolveExcelServiceImpl")
	private ResolveExcelService resolveExcelService;

	/**
	 * 文件上传
	 */
	@PostMapping(value = "/upload")
	public Result resolveExcel(@RequestParam("file") MultipartFile file) {
		try {
			List<AdConfig> result = resolveExcelService.resolveExcel(file);
			return Result.success(result);
		} catch (Exception e) {
			e.printStackTrace();
			return Result.error("导入失败:" + e.getMessage(), 500);
		}
	}
}

service

package com.basetnt.ad.game.xiaoxiaoqi.service;

import com.basetnt.ad.game.xiaoxiaoqi.dao.AdConfigMapper;
import com.basetnt.ad.game.xiaoxiaoqi.model.AdConfig;
import com.basetnt.ad.game.xiaoxiaoqi.model.AdConfigExample;
import com.google.gson.JsonObject;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

/**
 * @Desc
 * @Date 2019/4/26 15:13
 * @Author cui_yl
 */
@Service("resolveExcelServiceImpl")
public class ResolveExcelService  {
    /**
     *打印日志
     */
    private static final Logger logger = LoggerFactory.getLogger(ResolveExcelService.class);

    /**
     * 注册url
     */
    private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    @Autowired
    private AdConfigMapper adConfigMapper;

    @Transactional(isolation = Isolation.DEFAULT, propagation = Propagation.REQUIRED, rollbackFor=Exception.class)
    public List<AdConfig> resolveExcel(MultipartFile file) throws Exception {
        Date date = new Date();
        if (file == null) {
            throw new RuntimeException("文件不能为空");
        }

        //获取文件的名字
        String originalFilename = file.getOriginalFilename();
        if(StringUtils.isEmpty(originalFilename)){
            throw new RuntimeException("文件名不能为空");
        }

        Workbook workbook = null;
        if (originalFilename.endsWith(SUFFIX_2003)) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (originalFilename.endsWith(SUFFIX_2007)) {
            workbook = new XSSFWorkbook(file.getInputStream());
        }else {
            throw new RuntimeException("请上传.xls或者.xlsx格式的Excel");
        }

        List<AdConfig> list = new ArrayList<AdConfig>();
        AdConfig entity = null;
        JsonObject json = null;

        //获取所有的工作表的的数量
        int numOfSheet = workbook.getNumberOfSheets();
        //遍历这个这些表
        for (int i = 0; i < numOfSheet; i++) {
            //获取一个sheet也就是一个工作簿
            Sheet sheet = workbook.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            /*
             * 第0行 标题
             * 第1行 数据
             * 从1行开始 循环遍历 封装数据
             */
            for (int j = 1; j <= lastRowNum; j++) {
                Row row = sheet.getRow(j);
                boolean falg = (null == row || null == row.getCell(0) || StringUtils.isEmpty(row.getCell(0).getStringCellValue()));

                if (falg) {
                    continue;
                }

                entity = new AdConfig();
                json = new JsonObject();

                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(0).getStringCellValue();
                    entity.setPackageName(cellValue.trim());
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(1).getStringCellValue();
                    entity.setAdSpace(cellValue.trim());
                }

                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(2).getStringCellValue();
                    entity.setAdType(cellValue.trim());
                }

                if (row.getCell(3) != null) {
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(3).getStringCellValue().trim();
                    entity.setTotalNum(Integer.valueOf(cellValue.trim()));
                }

                if (row.getCell(4) != null) {
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(4).getStringCellValue().trim();
                    entity.setDays(Integer.valueOf(cellValue.trim()));
                }

                if (row.getCell(5) != null) {
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(5).getStringCellValue().trim();
                    entity.setGoldNum(Integer.valueOf(cellValue.trim()));
                }

                if (row.getCell(6) != null) {
                    row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(6).getStringCellValue().trim();
                    json.addProperty("百度", cellValue.trim());
                }

                if (row.getCell(7) != null) {
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(7).getStringCellValue().trim();
                    json.addProperty("腾讯", cellValue.trim());
                }

                if (row.getCell(8) != null) {
                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(8).getStringCellValue().trim();
                    json.addProperty("穿三甲", cellValue.trim());
                }
                entity.setWeightConfig(json.toString());
                entity.setImportTime(date);
                entity.setCreatedTime(date);
                entity.setModifiedTime(date);
                list.add(entity);
            }
        }
        if(CollectionUtils.isEmpty(list)){
           return null;
        }
        batchInsertSelective(list);
        return list;
    }

    private void batchInsertSelective(List<AdConfig> list) {
        batchDeleteByPackageName(list);
        adConfigMapper.batchInsertSelective(list);
    }

    private void batchDeleteByPackageName(List<AdConfig> list) {
        for (AdConfig record : list) {
            AdConfigExample example = new AdConfigExample();
            example.createCriteria().andPackageNameEqualTo(record.getPackageName());
            adConfigMapper.deleteByExample(example);
        }
    }
}