Java导入复杂excel

Java 导入复杂excel,多表格中有合并以及描述

接口返回类

package com.hs.report.utils;

import com.hs.report.enums.ResultCode;
import org.apache.commons.lang.StringUtils;

import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;

public class Result <T> extends BaseDomain implements Serializable {
    private static final long serialVersionUID = -1117047807265427246L;
    private int status;
    private String msg;
    private T data;

    public Result() {
        this.status = ResultCode.SUCCESS.getStatus();
        this.msg = ResultCode.SUCCESS.getMsg();
    }

    public Result(T data) {
        this.setData(data);
        this.status = ResultCode.SUCCESS.getStatus();
        this.msg = ResultCode.SUCCESS.getMsg();
    }

    public Result(int status, String msg) {
        this.setStatus(status);
        this.setMsg(msg);
    }

    public Result(int status, String msg, T data) {
        this.setStatus(status);
        this.setMsg(msg);
        this.setData(data);
    }

    public Result<T> setError(int status, String msg) {
        this.setStatus(status);
        this.setMsg(msg);
        return this;
    }

    public Result<T> setError(ResultCode errorCode) {
        this.setStatus(errorCode.getStatus());
        this.setMsg(errorCode.getMsg());
        return this;
    }

    public boolean isSuccess() {
        return this.getStatus() == ResultCode.SUCCESS.getStatus();
    }

    public static  Result instance() {
        return new Result();
    }

    public static <T> Result instance(T data) {
        return new Result(data);
    }

    public static <T> Result instance(int status, String msg) {
        return new Result(status, msg);
    }

    public static <T> Result instance(int status, String msg, T data) {
        return new Result(status, msg, data);
    }

    public int getStatus() {
        return this.status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getMsg() {
        return this.msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public void setPeriodIdMsg(Integer periodId){
        this.msg = ResultCode.SUCCESS.getMsg() + "_" + periodId;
    }

    public T getData() {
        return this.data;
    }

    public void setData(T data) {
        this.data = data;
    }

    public Map<String,Object> toJsonMap(){
        Map<String,Object> map = new HashMap<>();
        map.put("data",this.data);
        map.put("msg",this.msg);
        map.put("status",this.status);
        return map;
    }

}

实体类

package com.hs.report.entity.week;
import lombok.Builder;
import lombok.Data;
import java.io.Serializable;
@Data
@Builder
public class SnapshotReq<T> implements Serializable {
    private static final long serialVersionUID = 4403232919132430023L;
    private String type;
    private Integer offset;
	/**
	*json字符串
	**/
    private T content;

    private Integer subNo;

    private Integer periodId;

    private String flowId;
}

Controller层

package com.hs.report.controller.week;

import com.hs.report.entity.week.SnapshotReq;
import com.hs.report.service.week.ParseExcelForGSService;
import com.hs.report.service.week.ParseExcelForOTCService;
import com.hs.report.service.week.ParseExcelForZTService;
import com.hs.report.utils.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.List;

@Slf4j
@Api(tags = "导入Excel模板")
@RestController
@RequestMapping("/investmentReport/week")
public class ImportWeekExcelController {

    @Autowired
    private ParseExcelForZTService parseExcelForZTService;

    @Autowired
    private ParseExcelForGSService parseExcelForGSService;

    @Autowired
    private ParseExcelForOTCService parseExcelForOTCService;

    @ApiOperation(value = "导入")
    @PostMapping("/import")
    public Result<List<SnapshotReq>> importExcel(@RequestParam("file") MultipartFile file, @RequestParam(value = "type") Integer type) throws Exception {
        log.info("开始解析Excel, type==={}", type);

        Result<List<SnapshotReq>> result = new Result<>();
        List<SnapshotReq> snapshotReqs = null;

        InputStream inputStream = null;
        Workbook wb = null;
        try{
            inputStream = file.getInputStream();
            String fileName = file.getOriginalFilename();

            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if(".xls".equals(fileType)){
                wb= new HSSFWorkbook(inputStream);
            }else if(".xlsx".equals(fileType)) {
                wb = WorkbookFactory.create(inputStream);
            }

            switch(type){
                //证投
                case 1:
                    snapshotReqs = parseExcelForZTService.parseExcelForZT(wb);
                    break;
                //固收
                case 2:
                    snapshotReqs = parseExcelForGSService.parseExcelForGS(wb);
                    break;
                //OTC
                case 3:
                    snapshotReqs = parseExcelForOTCService.parseExcelForOTC(wb);
                    break;
            }

            inputStream.close();
            result.setData(snapshotReqs);
            log.info("结束解析Excel, type==={}", type);
        }catch (Exception ex){
            throw new Exception("导入Excel出现异常，异常信息：" + ex.getMessage());
        }

        return result;
    }


}

Service层

package com.hs.report.service.impl.week;

import com.hs.report.entity.week.BusinessTypeDO;
import com.hs.report.entity.week.LossPayfDO;
import com.hs.report.entity.week.SnapshotReq;
import com.hs.report.entity.week.WeekLossPayfDO;
import com.hs.report.service.week.ParseExcelForGSService;
import org.apache.commons.lang.StringUtils;
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.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Service
public class ParseExcelForGSServiceImpl implements ParseExcelForGSService {
    @Override
    public List<SnapshotReq> parseExcelForGS(Workbook wb) throws InstantiationException, IllegalAccessException  {
        Sheet sheet = wb.getSheetAt(0);
        List<SnapshotReq> result = new ArrayList<>();
        int rowIndex = 0;

        //本周重点推进工作：描述
        int weekRowNum = parseTextValue(sheet, 1,rowIndex + 2, 0, result);

        //本周重点推进工作：描述
        int lastWeekRowNum = parseTextValue(sheet, 2, weekRowNum + 3, 0, result);

        //T0周损益对比：描述
        int weekLossRowNum = parseTextValue(sheet, 3, lastWeekRowNum + 3, 0, result);

        //第一个表格内容
        int oneTitleText = parseTableValue(sheet, 4, weekLossRowNum + 4, result, WeekLossPayfDO.class);

        //市场状况及交易部门损益
        int marketRowNum = parseTextValue(sheet, 5, weekLossRowNum + 3 + oneTitleText + 3, 0, result);

        //第二个表格内容
        int twoTitleText = parseTableValue(sheet, 7, marketRowNum + 4, result, LossPayfDO.class);

        //固定收益部损益分析
        int fixedRowNum = parseTextValue(sheet, 10, marketRowNum + 3 + twoTitleText + 4, 0, result);

        //第三个表格内容
        int threeTitleText = parseThreeTableValue(sheet, 11, fixedRowNum + 3, result);

        //固定收益部
        int dailyRowNum = parseTextValue(sheet, 12, fixedRowNum + 3 + threeTitleText + 3, 0, result);

        return result;
    }

    //解析文本框内容
    private int parseTextValue(Sheet sheet, int offset, int rowIndex, int cellIndex, List<SnapshotReq> result){
        SnapshotReq snapshotReq = null;
        Cell cell = sheet.getRow(rowIndex).getCell(cellIndex);
        snapshotReq = SnapshotReq.builder().offset(offset).content(cell.getStringCellValue()).build();
        result.add(snapshotReq);
        return rowIndex;
    }

    //解析第三表格部分 有合并列
    private <T> int parseThreeTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result) {
        Row row = null;
        Cell cell = null;
        SnapshotReq snapshotReq = null;
        String perPurpose = "";//上一个数据描述
        String purpose = "";//当前数据描述
        boolean flag = true;//用来记录是否是第一次循环
        //判断有几条数据
        int oneTitleText = 0;
        List<Object> dataList = new ArrayList<>();
        for(int i = rowIndex;i < sheet.getLastRowNum();i++){
            BusinessTypeDO businessTypeDO = new BusinessTypeDO();
            row = sheet.getRow(i);
            //判断此行的每一列是否都为空
            if(judgeWhetherNull(row)){
                oneTitleText++;
                break;
            }

            if(row != null){
                for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                    cell = row.getCell(j);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    purpose = cell.getStringCellValue();
                    if(j == 0){

                        if(StringUtils.isNotEmpty(purpose) && !purpose.equals(perPurpose)){
                            flag = true;
                        }

                        if(flag){//如果是第一次循环，将perPurpose初始化，以便后面的第一次判断
                            perPurpose = purpose;
                            flag = false;
                        }

                        if(StringUtils.isEmpty(purpose) || purpose.equals(perPurpose)){
                            purpose = perPurpose;
                        }else{
                            purpose = cell.getStringCellValue();
                        }
                    }

                    getThreeTitleText(businessTypeDO, purpose, j);
                }
                dataList.add(businessTypeDO);

            }else{
                break;
            }
            oneTitleText++;
        }
        snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
        result.add(snapshotReq);

        return oneTitleText - 1;
    }


    //解析表格内容
    private <T> int parseTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result, Class<T> tClass)throws IllegalAccessException, InstantiationException{
        Row row = null;
        Cell cell = null;
        SnapshotReq snapshotReq = null;
        //判断有几条数据
        int oneTitleText = 0;
        List<Object> dataList = new ArrayList<>();
        for(int i = rowIndex;i < sheet.getLastRowNum();i++){
            Object object = tClass.newInstance();
            row = sheet.getRow(i);
            //判断此行的每一列是否都为空
            if(judgeWhetherNull(row)){
                if(i == rowIndex){
                    oneTitleText = 2;
                }else{
                    oneTitleText++;
                }
                break;
            }

            if(row != null){
                for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                    cell = row.getCell(j);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    generateData(object, cell.getStringCellValue(), j);
                }
                dataList.add(object);

            }else{
                break;
            }
            oneTitleText++;
        }
        snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
        result.add(snapshotReq);

        return oneTitleText - 1;
    }

    private <T> void generateData(T object, String value, int cellIndex){
        if(object instanceof WeekLossPayfDO){
            getOneTitleText((WeekLossPayfDO) object, value, cellIndex);
        }else if(object instanceof LossPayfDO){
            getTwoTitleText((LossPayfDO) object, value, cellIndex);
        }
    }

    private void getThreeTitleText(BusinessTypeDO businessTypeDO, String value, int cellIndex) {
        switch(cellIndex){
            case 0:
                businessTypeDO.setTitle(value);
                break;
            case 2:
                businessTypeDO.setSubtitle(getContentStr(value));
                break;
            case 4:
                businessTypeDO.setValue(getContentStr(value));
                break;
        }
    }

    private String getContentStr(String content) {
        return StringUtils.isNotEmpty(content) ? content : "";
    }

    private boolean judgeWhetherNull(Row row) {
    	if(row == null){
            return true;
        }
        int count = 0;
        //单元格数量
        int rowCount = row.getLastCellNum() - row.getFirstCellNum();
        for (int c = 0; c < rowCount; c++) {
            Cell cell = row.getCell(c);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtils.isEmpty((cell+"").trim())){
                count += 1;
            }
        }

        if (count == rowCount) {
            return true;
        }
        return  false;
    }

    private void getTwoTitleText(LossPayfDO lossPayfDO, String value, int cellIndex) {
        Double data = null;
        if(cellIndex != 0){
            data = StringUtils.isNotEmpty(value) ? Double.valueOf(value) : 0.0;
        }
        switch (cellIndex){
            case 0:
                lossPayfDO.setDepName(getContentStr(value));
                break;
            case 1:
                lossPayfDO.setRiskExpo(data);
                break;
            case 2:
                lossPayfDO.setGsck(data);
                break;
            case 3:
                lossPayfDO.setActlMval(data);
                break;
            case 4:
                lossPayfDO.setFutrNetPos(data);
                break;
            case 5:
                lossPayfDO.setWeekLossPayf(data);
                break;
            case 6:
                lossPayfDO.setMthLossPayf(data);
                break;
            case 7:
                lossPayfDO.setYearLossPayf(data);
                break;
        }
    }

    private void getOneTitleText(WeekLossPayfDO weekLossPayfDO, String value, int cellIndex) {
        BigDecimal data = null;
        if(cellIndex != 0){
            data = StringUtils.isNotEmpty(value) ? new BigDecimal(value) : null;
        }
        switch (cellIndex){
            case 0:
                weekLossPayfDO.setDeptName(getContentStr(value));
                break;
            case 2:
                weekLossPayfDO.setWeekLoss(data);
                break;
            case 4:
                weekLossPayfDO.setLastWeekLoss(data);
                break;
            case 6:
                weekLossPayfDO.setYearLoss(data);
                break;
        }
    }
}