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

导入代码: 

/**
	 * 导入
	 * @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;
	}

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值