Java中excel文件导入和导出POI框架

墨蓝 2022-03-24 15:22 274阅读 0赞

导入代码：

/**
    	 * 导入
    	 * @param msg
    	 * @return
    	 * @throws IOException
    	 */
    	@AuthExclude(internetForbidden = false)
    	public String importData(Message msg) throws IOException {
    		QueryRole queryRole = getParamBean(msg, QueryRole.class);
    		FileUpload file = ThreadContextHolder.get().getUploadedFile("file");
     		return roleDetailService.importData(file, queryRole.getRoleId());
    	}
    
    /**
    	 * 导入
    	 * @param file
    	 * @param roleId
    	 * @return
    	 * @throws IOException
    	 */
    	@Transaction(propagation = JtxPropagationBehavior.PROPAGATION_SUPPORTS)
    	public String importData(FileUpload file,Integer roleId) throws IOException{
    
    		List<Map<Integer,String>> dataList =  ExcelRead.readExcel(file);
    		CmpRoleDetail bean = new CmpRoleDetail();
    		for(int i = 1;i< dataList.size();i++){
    			bean.setCreateOaCode(CommonUtil.oaCode());
    			bean.setCreateOaName(CommonUtil.currentUser().getName());
    			bean.setCreateTime(CommonUtil.getNowDate());
    			bean.setDeleteMark(false);
    			bean.setSourceCode(dataList.get(i).get(1));
    			bean.setSourceName(dataList.get(i).get(2));
    			bean.setConfigCode(dataList.get(i).get(3));
    			bean.setConfigName(dataList.get(i).get(4));
    			bean.setDescription(dataList.get(i).get(5));
    			bean.setRoleId(roleId);
    			roleDetailDao.saveRoleDetail(bean);
    		}
    
    		return CommonUtil.success("导入成功");
    	}
    
    /**
    	 * 
    	 * @param file
    	 * @return
    	 * @throws IOException
    	 */
    	public static List<Map<Integer, String>> readExcel(FileUpload file)
    			throws IOException {
    		 List<Map<Integer, String>> list = new ArrayList<Map<Integer,String>>();
    	        
    	        HSSFWorkbook wb = null;
    	        HSSFSheet sheet = null; 
    	        HSSFRow row = null;
    			try {
    	            wb = new HSSFWorkbook(file.getFileInputStream());
    	        } catch (IOException e) {
    	            e.printStackTrace();
    	        }
    	        sheet = wb.getSheetAt(0);
    	        // 得到总行数
    	        int rowNum = sheet.getLastRowNum();
    	        row = sheet.getRow(0);
    	        //列数
    	        int colNum = row.getPhysicalNumberOfCells();
    	        //int cellnum = sheet.getDefaultColumnWidth();
    	        // 正文内容应该从第二行开始,第一行为表头的标题
    	        for (int i = 1; i <= rowNum; i++) {
    	            row = sheet.getRow(i);
    	            Map<Integer,String> map = new HashMap<Integer, String>();
    	       
    	            for (int k = 1; k < colNum ; k++) {
    					map.put(k, row.getCell(k)+"");
    					System.out.println(row.getCell(k));
    				}
    	            list.add(map);
    	        }
    	        return list;
    	}

导出代码：

/**
    	 * 导出列表
    	 * 
    	 * @param msg
    	 * @return
    	 * @throws Exception
    	 */
    	@AuthExclude(internetForbidden = false)
    	public Message exportData(Message msg) throws Exception {
    		Message m = new Message();
    		QueryRole queryRole = getParamBean(msg, QueryRole.class);
    		List<CmpRoleDetail> list = roleDetailService.exportList(queryRole);
    		String filedName = "角色配置人员列表";
    		@SuppressWarnings("static-access")
    		byte[] file = roleDetailService.exportExcel(list, filedName);
    		String downName = (filedName.trim() + ".xls");
    		downName = new String(downName.getBytes(), "ISO-8859-1");
    		if (null != file) {
    			m.setStatus(HttpStatus.HTTP_OK);
    			m.setHead("Content-Type", "application/vnd.ms-excel");
    			m.setHead(Message.CONTENT_LENGTH, file.length);
    			m.setHead("Content-Disposition", "attachment;filename=\"" + downName + "\"");
    			m.setBody(file);
    		} else {
    			throw new CommonException("can't find file");
    		}
    		return m;
    	}
    
    /**
    	 * 导出列表
    	 * 
    	 * @param list
    	 * @param filedName
    	 * @return
    	 * @throws FileNotFoundException
    	 */
    	public static byte[] exportExcel(List<CmpRoleDetail> list, String filedName) throws FileNotFoundException {
    		byte[] bytes = null;
    		// 创建HSSFWorkbook对象(excel的文档对象)
    		HSSFWorkbook wb = new HSSFWorkbook();
    		// 建立新的sheet对象（excel的表单）
    		HSSFSheet sheet = wb.createSheet(filedName);
    		sheet.setDefaultColumnWidth(20);
    		sheet.setDefaultRowHeight((short) (256 * 4));
    
    		HSSFRow row2 = sheet.createRow(0);
    		// 创建单元格并设置单元格内容
    		row2.createCell(0).setCellValue("所属单位");
    		row2.createCell(1).setCellValue("OA账号");
    		row2.createCell(2).setCellValue("姓名");
    		row2.createCell(3).setCellValue("备注");
    
    		HSSFRow rown = null;
    		// 在sheet里创建第N行
    		for (int i = 0; i < list.size(); i++) {
    			rown = sheet.createRow(i + 1);
    			rown.createCell(0).setCellValue(list.get(i).getSourceName());
    			rown.createCell(1).setCellValue(list.get(i).getConfigCode());
    			rown.createCell(2).setCellValue(list.get(i).getConfigName());
    			rown.createCell(3).setCellValue(list.get(i).getDescription());
    		}
    		ByteArrayOutputStream os = new ByteArrayOutputStream();
    		try {
    			wb.write(os);
    			bytes = os.toByteArray();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		return bytes;
    	}