JAVA Excle 导入

JAVA Excle 导入

前端使用饿了么UI

 <el-upload
        class="avatar-uploader"
        action='/org/pakeExcel'
        :show-file-list="false"
        :on-success="handlePreview"
        :http-request="UploadExcel"
        :before-upload="handleRemove"
        :file-list="fileList2"
        accept=".xls,.xlsx"
        list-type="picture">
        <el-button size="small" v-show="upexcelBtn" icon="el-icon-upload2" type="primary">园区导入</el-button>
      </el-upload>
     
     设置请求头        headers: {'Content-type': 'multipart/form-data'},
             
          })

后台
Controller

 /**
     * 园区批量
     * @param request
     * @return
     */
    @ResponseBody
    @PostMapping("/pakeExcel")
	@Secured("ROLE_base:park:upexcel")
    public Map pakeExcel( HttpServletRequest request ){
        MultipartHttpServletRequest muRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = muRequest.getFile("file");
        int i = 0;
		Map resultMap =new HashMap();
        try {
            InputStream ins = file.getInputStream();
            FileInputStream inputStream = (FileInputStream )ins ;
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			resultMap = orgService.parkManage ( workbook, OrgTypeEnums.AREA.getCode () );
            ins.close();
        } catch (IOException e) {
            return  ResultUtils.fail ();
        }
		i=StringUtil.toInt(resultMap.get("code"));
        if (i ==0){
            return  ResultUtils.success ();
        }
       else{
			String msg = "";
			List<Map> orgs = (List<Map>) resultMap.get("orgs");
			for (int j = 0;j<orgs.size();j++){
				Map org =orgs.get(j);
				msg = msg+"<p style='height:30px;line-height:30px;width:100%'>第"+org.get("row")+"行，厂商“"+org.get("orgName")+"”已经存在；</p>";
			}
			return ResultUtils.errorCode (201,msg+"<p style='height:30px;line-height:30px;width:100%'>请修改后重新上传</p>");
		}
    }

service

@Override
	public Map parkManage(HSSFWorkbook workbook, int orgType) {
		int jump = 1;//跳过几行
        Map resultMap = new HashMap();
		HSSFSheet hssfSheet = workbook.getSheetAt ( 0 );
		//row 每一行就是一个对象
		HSSFRow row;
        List<Map> repetitiveOrgs = new ArrayList<Map>();
		//此循环 判断机构名称唯一 不可重复( 唯一判断 要求的! )
		for (int ii = jump; ii < hssfSheet.getLastRowNum () + 1; ii++) { //循环整个excel有多少行
			row = hssfSheet.getRow ( ii ); //一个row 代表这一行 也代表一个对象
			if (row == null) {
				continue;
			}
			HSSFCell cell1 = row.getCell ( 0 ); //获取每行的第一个表格数据 (就是判断它唯一)
			if (cell1!=null){
                String orgName = PubExcelReadUtill.getCellContent ( cell1 ); //这个是字符解码
                List<Map> list1 = orgDao.seleOrgName ( orgType );
                for (Map map1 : list1) {
                    if (map1.get ( "org_name" )!=null&&map1.get ( "org_name" ).equals ( orgName )) {
                        Map<String,Object> orgMap = new HashMap<>();
                        orgMap.put("row", ii+1);
                        orgMap.put("orgName",orgName);
                        repetitiveOrgs.add(orgMap);
//					return ii+1;
                    }
                }
            }
		}
		//这个就是返回上面循环如果有重复名称 就提前返回那行那行重复
        if(repetitiveOrgs.size()>0){
            resultMap.put("code",repetitiveOrgs.size());
            resultMap.put("orgs",repetitiveOrgs);
            return  resultMap;
        }

		//遍历 表格
		for (int ii = jump; ii < hssfSheet.getLastRowNum () + 1; ii++) {
			row = hssfSheet.getRow ( ii );
			if (row == null) {
				continue;
			}
			Map drug = new HashMap (); //把数据存入map
			for (int j = 0; j < row.getLastCellNum (); j++) {
				//Cell
				HSSFCell cell = row.getCell ( (short) j );
				String s = null;
				if (cell != null) {
					//读取单元格String内容
					s = PubExcelReadUtill.getCellContent ( cell ); //这个是判断数据类型的
                    if (s.equals ( "" )){
                        s=null;
                    }
					System.out.println ( "========index======" + j + ":" + s );
				} else {
					s = null;
				}

				// 0 园区名称	1 园区地址  2 	园区电话	3 原区负责人
				if (j == 0&&s!=null) {
					drug.put ( "orgName", s );
				} else if (j == 1&&s!=null) {
					drug.put ( "companyAddress", s );
				} else if (j == 2&&s!=null) {
					drug.put ( "phone", s );
				} else if (j == 3&&s!=null) {
					drug.put ( "legalperson", s );
				}
			}
			if (drug.size ()>0){
                drug.put ( "orgId", UUIDUtil.uuid () );
                drug.put ( "orgType", orgType );
                drug.put ( "createUser", TokenUtils.getUserId () );
                orgDao.save ( drug );
            }
		}
        resultMap.put("code",0);
        return resultMap;
	}

自动识别表格数据类型

package com.navitek.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;

import java.text.SimpleDateFormat;

/**
 * @Date: 2019/5/28 0028 16:16
 * @Description:
 */
public class PubExcelReadUtill {
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

    public static String getCellContent(HSSFCell cell ) {
        String cellValue = null;
        if (null != cell) {
            switch (cell.getCellType()) {                     // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                case 0:
                    cellValue = String.valueOf((int) cell.getNumericCellValue());
                    break;
                case 1:
                    cellValue = cell.getStringCellValue();
                    break;
                case 2:
                    cellValue = cell.getNumericCellValue() + "";
                    // cellValue = String.valueOf(cell.getDateCellValue());
                    break;
                case 3:
                    cellValue = "";
                    break;
                case 4:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case 5:
                    cellValue = String.valueOf(cell.getErrorCellValue());
                    break;
            }
        } else {
            cellValue = null;
        }

        return cellValue;

    }
}