JAVA Excel 批量导入(支持Excel xls和xlsx)

最新推荐文章于 2024-07-03 14:13:18 发布

十里八方

最新推荐文章于 2024-07-03 14:13:18 发布

阅读量1.3k

点赞数

分类专栏： java基础

本文链接：https://blog.csdn.net/qq_40384470/article/details/86646643

版权

java基础专栏收录该内容

12 篇文章 0 订阅

订阅专栏

此例使用 HSSF POI技术

代码如下

util层

 /**
     * 判断字符串与数字 true 是数字  false  字符串
     * @param str
     * @return
     */
    public static boolean isNum(String str){
        return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
    }

如果excel得时间传过来是41781这样得数字格式,我们可以使用正则表达式判断然后进行转时间

//例：这是获得excel表格第6列得数据
String finishdate = row.getCell(6).getStringCellValue();
//判断是否是数字格式 true表示是  繁殖false
            boolean numeric = StringUtils.isNumeric(finishdate);
            Date date1=null;
            if (numeric==true) {
//转时间格式
                Calendar c = new GregorianCalendar(1900, 0, -1);
//使用java自带得工具类获得时间
                 date1 = DateUtils.addDays(c.getTime(), Integer.decode(finishdate));
            }
            else
            {
//根据自己喜好得格式进行转
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
                 date1= simpleDateFormat.parse(finishdate);
            }

controller层

/**
     * 导入工资excel
     * @param multipartFile
     * @return
     */
    @RequestMapping(value = "/addWageExcel",method = RequestMethod.POST)
    public Map<String,Object> addWageExcel(@RequestParam("file")MultipartFile multipartFile){
        Map<String, Object> map = new HashMap<>();
        String fileName = multipartFile.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
          map.put("error","导入文件格式不正确");
          return  map;
        }
        Boolean wageisSuccess=false;
        try {
             wageisSuccess= wageService.addWageExcel(multipartFile,fileName);
             if (wageisSuccess==true){
                 map.put("success",true);
                 return  map;
             }
        } catch (Exception e) {
           map.put("error","导入失败.请检查字段为空或格式错误");
           return  map;
        }
        map.put("error","导入失败");
        return  map;
    }

service层

 /**
     * 导入工资表
     *
     * @param file
     * @param fileName
     * @return
     */
    @Transactional
    public Boolean addWageExcel(MultipartFile file, String fileName) throws Exception {
        WageYm wageYm = new WageYm();
        boolean notNull = false;
        //获得当前时间
        Calendar instance = Calendar.getInstance();
        wageYm.setUpdateTime(Calendar.getInstance().getTime());
        //设置年
        wageYm.setWageYear(String.valueOf(instance.get(Calendar.YEAR)));
        //设置月
        wageYm.setWageMonth(String.valueOf(instance.get(Calendar.MONTH)+1));
        List<Wages> wagesList = new LinkedList<>();
        //判断该excel版本是否最新版
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        //创建输入流
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        //如果表单不为空
        if (sheet != null) {
            notNull = true;
        }
        Wages wages;
        //遍历表单的每一行
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            //获得第一行
            Row row = sheet.getRow(r);
            //创建工资对象
            wages=new Wages();
            // 获取excel计划单号
            if (row.getCell(0).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,姓名请设为文本格式)");
            }
            String name = row.getCell(0).getStringCellValue();
            if (name == null || name.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,姓名未填写)");
            }else {
                boolean num = isNum(name);
                if (num==true){
                    throw new Exception("导入失败(第" + (r + 1) + "行,姓名应为字符串)");
                }
            }
            wages.setName(name);
            if (row.getCell(1).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,工种请设为文本格式)");
            }
            String work = row.getCell(1).getStringCellValue();
            if (work == null || work.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,工种未填写)");
            }else {
                boolean num = isNum(work);
                if (num==true){
                    throw new Exception("导入失败(第" + (r + 1) + "行,工种应为字符串)");
                }
            }
            wages.setWork(work);
            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
            if (row.getCell(2).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,出勤天数请设为文本格式)");
            }
            String days = row.getCell(2).getStringCellValue();
            if (days == null || days.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,出勤天数未填写)");
            }else {
                boolean num = isNum(days);
                if (num==false){
                    throw new Exception("导入失败(第" + (r + 1) + "行,出勤天数应为数字格式)");
                }
            }
            wages.setDays(days);
            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
            if (row.getCell(3).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,岗位工资请设为文本格式)");
            }
            String postPay = row.getCell(3).getStringCellValue();
            if (postPay == null || postPay.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,岗位工资未填写)");
            }else {
                boolean num = isNum(postPay);
                if (num==false){
                    throw new Exception("导入失败(第" + (r + 1) + "行,岗位工资应为数字格式)");
                }
            }
            wages.setPostPay(Double.valueOf(postPay));
            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
            if (row.getCell(4).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,计件工资请设为文本格式)");
            }
            String piecePay = row.getCell(4).getStringCellValue();
            if (piecePay == null || piecePay.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,计件工资未填写)");
            }else {
                boolean num = isNum(piecePay);
                if (num==false){
                    throw new Exception("导入失败(第" + (r + 1) + "行,计件工资应为数字格式)");
                }
            }
            wages.setPiecePay(Double.valueOf(piecePay));
            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
            if (row.getCell(5).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,应纳税工资请设为文本格式)");
            }
            String taxablePay = row.getCell(5).getStringCellValue();
            if (taxablePay == null || taxablePay.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,应纳税工资未填写)");
            }else {
                boolean num = isNum(taxablePay);
                if (num==false){
                    throw new Exception("导入失败(第" + (r + 1) + "行,应纳税工资应为数字格式)");
                }
            }
            wages.setTaxablePay(Double.valueOf(taxablePay));
            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
            if (row.getCell(6).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,扣个税请设为文本格式)");
            }
            String deduction = row.getCell(6).getStringCellValue();
            if (deduction == null || deduction.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,扣个税未填写)");
            }else {
                boolean num = isNum(deduction);
                if (num==false){
                    throw new Exception("导入失败(第" + (r + 1) + "行,扣个税应为数字格式)");
                }
            }
            wages.setDeduction(Double.valueOf(deduction));
            row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
            if (row.getCell(7).getCellType() != 1) {
                throw new Exception("导入失败(第" + (r + 1) + "行,实发工资请设为文本格式)");
            }
            String realPay = row.getCell(7).getStringCellValue();
            if (realPay == null || realPay.isEmpty()) {
                throw new Exception("导入失败(第" + (r + 1) + "行,实发工资未填写)");
            }else {
                boolean num = isNum(realPay);
                if (num==false){
                    throw new Exception("导入失败(第" + (r + 1) + "行,实发工资应为数字格式)");
                }
            }
            wages.setRealPay(Double.valueOf(realPay));
            wages.setWageym(wageYm);
            wagesList.add(wages);
        }
        wageYm.setWagesHashSet(wagesList);
        WageYm wageYm1 = wagesRepository.save(wageYm);
        if (wageYm1!=null){
            return  true;
        }
        return  false;
    }

这里dao层直接使用springboot jpa save方法直接保存即可

excel导出~~~

IntellMachStatusDto dto = intellectMonitorService.findIntellMachStatus(bladeUser, machineType, deptCode,
			styleName, weaveloomSurplustime);
		List<IntellMachEvery> list = dto.getMachineTellList();
		//excel表头
		String[] title = {"机台号", "状态", "效率", "品种", "了机时间", "卷径", "落布时间", "轴号", "落布定长", "织轴剩余", "织轴长度", "产量"};
		//excel文件名
		String fileName = "织造监测报表" + System.currentTimeMillis() + ".xls";
		//sheet名
		String sheetName = "织造监测报表";
		Iterator<IntellMachEvery> iterator = list.iterator();
		Integer i = 0;
		String[][] val = new String[0][];
		while (iterator.hasNext()) {
			val[i] = new String[title.length];
			IntellMachEvery obj = iterator.next();
			val[i][0] = obj.getMachineCode();
			val[i][1] = obj.getStatusName();
			val[i][2] = Func.toStr(obj.getEfficiency());
			val[i][3] = obj.getStyleName();
			val[i][4] = obj.getWeaveloomSurplustimeStr();
			val[i][5] = obj.getCrimp();
			val[i][6] = obj.getCropTimeStr();
			val[i][7] = obj.getBeamName();
			val[i][8] = Func.toStr(obj.getCropFixlength());
			val[i][9] = Func.toStr(obj.getWeaveloomLength());
			val[i][10] = Func.toStr(obj.getWeaveloomFixlength());
			val[i][11] = obj.getMeters();
			i++;
		}
		HSSFWorkbook wb = ExcelUtils.getHSSFWorkbook(sheetName, title, val, null);
		try {
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

/**
	 * 导出Excel
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param values 内容
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){

		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

		//声明列对象
		HSSFCell cell = null;

		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 1);
			for(int j=0;j<values[i].length;j++){
				//将内容按顺序赋给对应的列对象
				row.createCell(j).setCellValue(values[i][j]);
			}
		}
		return wb;
	}