java excel批量导入导出

批量导入

1.Controller层

import com.baomidou.mybatisplus.plugins.Page;
import io.swagger.annotations.*;
import org.apache.poi.hssf.usermodel.HSSFRow;
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.streaming.SXSSFWorkbook;
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.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 /**
     * 批量导入excel文件,
     *
     * @param dataPermission
     * @param bgId
     * @param personId
     * @param file
     * @return
     * @throws IOException
     */
    @ApiOperation(value = "批量导入", tags = {
   "计划"}, notes = "批量导入", response = RecruitStaff.class, httpMethod = "POST")
    @PostMapping(value = "/upload")
    public Object upload(@RequestHeader(value = "X-Data-Permission", required = false) String dataPermission,
                         @RequestHeader(value ="X-Business-Group-Id") Integer bgId,
                         @RequestHeader(value = "X-Person-Id") Long personId,
                         @RequestParam(value ="file") MultipartFile file) throws IOException {
   
        String success = "success";
        String msg = "msg";
        ModelMap modelMap = new ModelMap();
        if (!file.isEmpty()) {
   
            try {
   
                modelMap = (ModelMap) recruitmentService.recruitPlanInfo(file, modelMap, personId, dataPermission, bgId);
            } catch (Exception e) {
   
                e.printStackTrace();
                modelMap.put("msg", "文件导入异常");
            }
        } else {
   
            modelMap.put("msg", "文件导入异常");
        }
        modelMap.put("dataPermission", dataPermission);
        if (success.equals(modelMap.get(msg))) {
   
            modelMap.put("msg", "导入成功");
            return setSuccessModelMap(modelMap);
        } else {
   
            modelMap.put("status", "403");
            return setFailModelMap(modelMap);
        }
    }

2.Service层

@Transactional(rollbackFor = Exception.class)
    @Override
    public Object recruitPlanInfo(MultipartFile file, ModelMap modelMap, Long personId, String dataPermission, Integer bgId) throws IOException, InvalidFormatException {
   
        StringBuilder errorMsg = new StringBuilder();
        //1.读取上传文件
        InputStream is = file.getInputStream();
        Workbook workbook = null;
        workbook = WorkbookFactory.create(is);
        //2.获取第一个工作表
        Sheet sheet1 = workbook.getSheet("计划111");
        //获取第二个工作表
        Sheet sheet2 = workbook.getSheet("计划222");
        //创建时间
        Date creationDate = new Date();

        if (sheet1 != null || sheet2 != null) {
   
            //ll
            for (int i = 4; i < sheet1.getLastRowNum(); i++) {
   
                Row row1 = sheet1.getRow(i);
                if (row1 == null && row1.equals("")) {
   
                    break;
                }
                RecruitPlanMaster recruitPlanMaster = new RecruitPlanMaster();
                RecruitStaff recruitStaff = new RecruitStaff();
                RecruitAttachment recruitAttachment = new RecruitAttachment();
                //kk
                String recruitPlanId = null;
                recruitPlanId = CommonUtil.getUUID();
                recruitPlanMaster.setRecruitPlanId(recruitPlanId);
                recruitStaff.setRecruitPlanId(recruitPlanId);
                //gg
                String recruitStaffId = "";
                recruitStaffId = CommonUtil.getUUID();
                recruitStaff.setRecruitStaffId(recruitStaffId);
                //ii
                String recruitYear = "";
                Cell cell1 = row1.getCell(0);
                recruitYear = CommonUtil.getCellValueStr(cell1);
                recruitPlanMaster.setRecruitYear(recruitYear);
                //hh
                String recruitMonth = "";
                Cell cell2 = row1.getCell(1);
                recruitMonth = CommonUtil.getCellValueStr(cell2);
                recruitPlanMaster.setRecruitMonth(recruitMonth);
                //jj
                String departmentName = "";
                Cell cell3 = row1.getCell(2);
                departmentName = CommonUtil.getCellValueStr(cell3);
                recruitPlanMaster.setDepartmentName(departmentName);
                //ff
                Integer managerPlan = null;
                Cell cell4 = row1.getCell(3);
                managerPlan = Integer.valueOf(CommonUtil.getCellValueStr(cell4));
                recruitPlanMaster.setManagerPlan(managerPlan);
                //ee
                Integer existPeople = null;
                Cell cell5 = row1.getCell(4);
                existPeople = Integer.valueOf(CommonUtil.getCellValueStr(cell5));
                recruitPlanMaster.setExistPeople(existPeople);
                //dd
                Integer vacancyPeople = null;
                Cell cell6 = row1.getCell(5);
                vacancyPeople = Integer.valueOf(CommonUtil.getCellValueStr(cell6));
                recruitPlanMaster.setVacancyPeople(vacancyPeople);
                //cc
                String recruitPostion = "";
                Cell cell7 = row1.getCell(6);
                recruitPostion = CommonUtil.getCellValueStr(cell7);
                recruitStaff.setRecruitPostion(recruitPostion);
                //bb
                String recruitCondition = "";
                Cell cell8 = row1.getCell(7);
                recruitCondition = CommonUtil.getCellValueStr(cell8);
                recruitStaff