POI Excel 表格工具类

public class PoiUtil {
 
	public static final String HEADERINFO = "headInfo";
	public static final String DATAINFON = "dataInfo";
 
	/**
	 * 
	 * @Title: getWeebWork
	 * @Description: TODO(根据传入的文件名获取工作簿对象(Workbook))
	 * @param filename
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWeebWork(String filename) throws IOException {
		Workbook workbook = null;
		if (null != filename) {
			String fileType = filename.substring(filename.lastIndexOf("."),
					filename.length());
			FileInputStream fileStream = new FileInputStream(new File(filename));
			if (".xls".equals(fileType.trim().toLowerCase())) {
				//workbook = new HSSFWorkbook(fileStream);// 创建 Excel 2003 工作簿对象
			} else if (".xlsx".equals(fileType.trim().toLowerCase())) {
				workbook = new XSSFWorkbook(fileStream);// 创建 Excel 2007 工作簿对象
			}
		}
		return workbook;
	}
	/**
     * Excel读取 操作
     */
    public static List<List<String>> readExcel(InputStream is,String filename)
            throws IOException {
    	 Workbook wb = null;
    	if(filename.equals("xls")) {
    		wb = new HSSFWorkbook();  
    	}else if(filename.equals("xlsx")) {
    		wb = new XSSFWorkbook();  
    	}
       
        try {
            try {
				wb = WorkbookFactory.create(is);
			} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
      
        System.out.println("totalRows"+totalRows);

        /** 得到Excel的列数 */
        int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
       
        System.out.println("totalCells"+totalCells);
        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 循环Excel的行 */
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);          
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                rowLst.add(cell.toString());
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    /**
     * 将数据写入到excel中
     */
    public static HSSFWorkbook   makeExcel(List<List<String>> result) {

        //第一步，创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第二部，在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet("错误的数据");
        //第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("IMEI");
        cell = row.createCell(1);
        cell.setCellValue("表号");
        cell = row.createCell(2);
        cell.setCellValue("资产号");
        cell = row.createCell(3);
        cell.setCellValue("设备名称");
        //第五步，写入数据
        for(int i=0;i<result.size();i++) {

            List<String> oneData = result.get(i);
            HSSFRow row1 = sheet.createRow(i + 1);
            for(int j=0;j<oneData.size();j++) {

                 //创建单元格设值
                row1.createCell(j).setCellValue(oneData.get(j));
            }
        }

//        //将文件保存到指定的位置
//        try {
            FileOutputStream fos = new FileOutputStream("E:\\result.xls");
            workbook.write(fos);
            System.out.println("写入成功");
            fos.close();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
        return workbook;
    }

}

controller

@SysLog("批量添加设备")
    @RequestMapping("/addAllDevice")
    @RequiresPermissions("app:device:addAllDevice")
    public R addAllDevice(MultipartFile file,@RequestParam Map<String, Object> params) {
    	
    	try {
    		String fileName=file.getOriginalFilename();
    		String ends=null;
    		//判断文件是否是excel文件  
            if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){  
            	return R.error("不是excel文件");
            }
            if(fileName.endsWith("xls")) {
            	ends="xls";
            }else if(fileName.endsWith("xlsx")) {
            	ends="xlsx";
            }
			InputStream input=file.getInputStream();
			
			List<List<String>> readExcel = PoiUtil.readExcel(input,ends);
			List<List<String>> errorExcel = new ArrayList<List<String>>(); //出错误的数据
			System.out.println(readExcel);
			if(readExcel.size()==0) {
				return R.error("Excel无数据");
			}	
			for(int i=0;i<readExcel.size();i++){
				Device device=new Device();
                for(int x=0;x<readExcel.get(i).size();x++){
                    switch (x){
                        case 0:
                        	device.setImei(readExcel.get(i).get(x));
                            break;
                        case 1:
                        	device.setNumber(readExcel.get(i).get(x));
                            break;
                        case 2:
                        	device.setAssets(readExcel.get(i).get(x));                           
                            break;
                        case 3:
                        	device.setDeviceName(readExcel.get(i).get(x));                       
                            break;               
                    }
                }
                
                       	           	
            	List<Device> existDev = deviceService.isExist(device.getImei(), device.getNumber());           
            	if(existDev.size() != 0) {
            		List<String> errorlist=new ArrayList<String>();
            		errorlist.add(device.getImei());
            		errorlist.add(device.getNumber());
            		errorlist.add(device.getAssets());
            		errorlist.add(device.getDeviceName());
            		errorExcel.add(errorlist);
            		//return R.error(9, "第"+(i+1)+"行中的设备号或表号已存在");
            	}else {
            		//业务逻辑
            		
            	}
               
            }
			if(errorExcel.size()!=0) {	
					//将有错的数据写入到Excel，并且输出到本地磁盘
				HSSFWorkbook workbook =PoiUtil.makeExcel(errorExcel);
				try {
						SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
						String uuidFileName = UUID.randomUUID().toString() + ".xls";
						String filePath = sdf.format(new Date()) + File.separator  + uuidFileName;
						String path = errorFilePath + filePath;
						File saveFile = new File(path);
						if (!saveFile.getParentFile().exists()) {
			                saveFile.getParentFile().mkdirs();
			            }
			            FileOutputStream fos = new FileOutputStream(path);
			            workbook.write(fos);
			            System.out.println("写入成功");
			            fos.close();
			            return R.error(10,"数据出错").put("filePath", filePath).put("size", errorExcel.size());
					} catch (IOException e) {
						e.printStackTrace();
					}				
			}			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return R.ok();
    }