上传excel到数据库

上传excel到数据库：
package com.wisdragon.controller.dataQuality;

import com.wisdragon.comm.constant.Constant;
import com.wisdragon.controller.common.BaseController;
import com.wisdragon.model.dataQuality.RuleBase;
import com.wisdragon.model.dataQuality.RuleValue;
import com.wisdragon.service.dataQuality.IRuleBaseService;
import com.wisdragon.service.dataQuality.IRuleValueService;
import com.wisdragon.utils.JsonMapper;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

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

/**
 * Description:excel规则库上传excel文件到数据库
 * @author 韩波
 * @create 2018/10/11 13:29
 */
@Controller
@RequestMapping(value = {"/dataQuality/"})
public class ExcelUploadController extends BaseController {
    private Workbook wb;
    private Sheet sheet;
    private Row row;
    private List<List<String>> list;
    private static final Logger log = LogManager.getLogger(ExcelUploadController.class);

    @Autowired
    public IRuleBaseService ruleBaseService;
    @Autowired
    public IRuleValueService ruleValueService;

    public void ReadExcelUtils(@RequestParam("file") MultipartFile file) {
       try {
           String[] fileName = file.getOriginalFilename().split("[\\.]");
           if ("xls".equals(fileName[1]) || "xlsx".equals(fileName[1])) {
               CommonsMultipartFile cFile = (CommonsMultipartFile)file;
               DiskFileItem dfi = (DiskFileItem)cFile.getFileItem();
               InputStream ins = dfi.getInputStream();
               wb = new XSSFWorkbook(ins);
           } else {
               System.out.print("文件格式不正确！请使用.xls或.xlsx后缀文档。");
               return;
           }
           list = new ArrayList<>();
           sheet = wb.getSheetAt(0);
           int fristRow = sheet.getFirstRowNum();
           int lastRow = sheet.getLastRowNum();
           for(int i= fristRow+1; i<=lastRow; i++) {
               List<String> sList = new ArrayList<>();
               row = sheet.getRow(i);
               if(row != null) {
                   int fristcell = row.getFirstCellNum();
                   int lastcell = row.getLastCellNum();
                   for(int ii = fristcell; ii<lastcell; ii++) {
                       Cell cell = row.getCell(ii);
                        if(cell != null) {
                            sList.add(cell.toString());
                        }
                   }
               }
               list.add(sList);
           }
           for(List<String> str : list) {
               System.out.println(str.toString());
           }
       } catch (Exception e) {
            e.printStackTrace();
       }
    }

    public RuleBase arrangementRuleBase(List<String> str) {
        RuleBase rb = new RuleBase();
        log.info(str.size());
        if(str.size()==3) {
            rb.setName(str.get(0));
            rb.setDescriber(str.get(2));
            log.info(rb.toString());
        } else if(str.size()==4){
            rb.setName(str.get(0));
            rb.setDescriber(str.get(3));
            log.info(rb.toString());
        }
        return rb;
    }

    public RuleBase arrangementRuleValue(List<String> str, RuleBase rb) {
        if(str.size()==3) {
            RuleValue rv = new RuleValue();
            rv.setValueName(str.get(1));
            List<RuleValue> rlist = new ArrayList<>();
            rlist.add(rv);
            rb.setRuleValues(rlist);
            log.info(rb.toString());
        } else if(str.size()==4) {
            RuleValue rv = new RuleValue();
            rv.setValueName(str.get(1));
            rv.setValueDescriber(str.get(2));
            List<RuleValue> vlist = new ArrayList<>();
            vlist.add(rv);
            rb.setRuleValues(vlist);
            log.info(rb.toString());
        }
        return rb;
    }

    @RequestMapping(method = {RequestMethod.GET, RequestMethod.POST},value = "addRuleExcel/**")
    public String addRuleExcel(MultipartFile filePath, Integer type) {
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>"+type);
        ReadExcelUtils(filePath);
        //枚举型文件的添加
        if(type.equals(Constant.DQ_ENUMTYPE)){
            String eRuleBaseName = new String();
            Integer oldId = 0;
            for(List<String> str : list) {
                RuleBase ruleBase = arrangementRuleBase(str);
                ruleBase.setType(type);
                Integer id = 0;
                log.info(eRuleBaseName+oldId);
                if(ruleBase.getName().equals(eRuleBaseName)) {
                    id = oldId;
                } else {
                    ruleBaseService.save(ruleBase);
                    id=ruleBase.getId();
                    oldId = id;
                    eRuleBaseName = ruleBase.getName();
                }
                log.info(eRuleBaseName+oldId);
                ruleBase = arrangementRuleValue(str, ruleBase);
                for (RuleValue ruleValue:ruleBase.getRuleValues()) {
                    ruleValue.setRuleId(id);
                    log.info(">>>>>>>>>>>>>>>>.................."+ruleValue);
                    ruleValueService.addValue(ruleValue);
                }
                log.info("*******-------*****"+ruleBase);
                log.info("*******-------*****"+new JsonMapper().toJson(ruleBase));
            }
            return "redirect:/dataQuality/ruleIndex/?type="+Constant.DQ_ENUMTYPE;
        }
        //正则型文件的添加
        if(type.equals(Constant.DQ_REGULARTYPE)){
            for(List<String> str : list) {
                RuleBase ruleBase = arrangementRuleBase(str);
                ruleBase.setType(type);
                ruleBaseService.save(ruleBase);
                Integer id=ruleBase.getId();
                ruleBase = arrangementRuleValue(str, ruleBase);
                for (RuleValue ruleValue:ruleBase.getRuleValues()) {
                    ruleValue.setRuleId(id);
                    ruleValueService.addValue(ruleValue);
                }
                log.info("*******-------*****"+ruleBase);
            }
            return "redirect:/dataQuality/ruleIndex/?type="+Constant.DQ_REGULARTYPE;
        }
        //值域型文件的添加
        if(type.equals(Constant.DQ_RANGETYPE)){
            for(List<String> str : list) {
                RuleBase ruleBase = arrangementRuleBase(str);
                ruleBase.setType(type);
                ruleBaseService.save(ruleBase);
                Integer id=ruleBase.getId();
                ruleBase = arrangementRuleValue(str, ruleBase);
                for (RuleValue ruleValue:ruleBase.getRuleValues()) {
                    ruleValue.setRuleId(id);
                    log.info(">>>>>>>>>>>>>>>>.................."+ruleValue);
                    ruleValueService.addValue(ruleValue);
                }
                log.info("*******-------*****"+ruleBase);
            }
            return "redirect:/dataQuality/ruleIndex/?type="+Constant.DQ_RANGETYPE;
        }
        //基准值型文件的添加
        if(type.equals(Constant.DQ_STANDARDTYPE)){
            for(List<String> str : list) {
                RuleBase ruleBase = arrangementRuleBase(str);
                ruleBase.setType(type);
                log.info(ruleBase);
                ruleBaseService.save(ruleBase);
                Integer id=ruleBase.getId();
                log.info(">>>>>>>>>>>>>>>>>>>"+id);
                ruleBase = arrangementRuleValue(str, ruleBase);
                log.info(ruleBase);
                for (RuleValue ruleValue:ruleBase.getRuleValues()) {
                    ruleValue.setRuleId(id);
                    ruleValueService.addValue(ruleValue);
                }
                log.info("*******-------*****"+ruleBase);
            }
            return "redirect:/dataQuality/ruleIndex/?type="+Constant.DQ_STANDARDTYPE;
        }
        return "redirect:/dataQuality/ruleIndex/?type="+Constant.DQ_ENUMTYPE;
    }

}

 

 

声明：此博客为个人学习之用，如与其他作品雷同，纯属巧合，转载请指明出处！