java+poi+excel2007批量修改/导入用户信息

 

最近项目需要做一个批量导入用户的功能，而且还要对多张工作表进行修改，最终将新的数据写入数据库并且将新的excel下载到本地，在网上看了一下，方式有很多种，开始是用jxl，但是后来发现jxl对excel操作起来并不是很方便，后来就改用poi了，其实还是挺简单的，下面把核心代码和大家分享一下，因为项目急，临时看文档写的，难免有不足之处，望大家多提建议，更希望大家都能坚持共享的精神，谢谢。

 

需要poi_3.7_api的可以去我的资源里下，http://download.csdn.net/detail/tongkp/3751105

 

	/**
	 * 解析、修改xlsx文件
	 */
	private void updateXLSX(){
		String path = upload.getPath(); 
		 try {
//			XSSFWorkbook workbook = new XSSFWorkbook("E:\\temp\\Import_users.xlsx");
			XSSFWorkbook workbook = new XSSFWorkbook(path);
			int sheets = workbook.getNumberOfSheets();		//获取工作表数量
			for(int i = 0; i < sheets; i++){
				if(i == 0){
					eduBasicInfo = new String[3];
				}else if(i == 1 || i == 2){
					tempArr = new String[6];
				}else if(i > 3){
					tempArr = new String[10];
				}
				
				XSSFSheet xsheet = workbook.getSheetAt(i);
				String sheetname = xsheet.getSheetName().toLowerCase();		//工作表名称
				int rows = xsheet.getLastRowNum()+1;		//获取工作表行数
				for(int j = 0; j < rows; j++){
					if(j > 0){
						XSSFRow xrow = xsheet.getRow(j);
						int cells = xrow.getLastCellNum();		//获取工作表列数
						if(i > 3){
							cells = 10;
						}
						for(int k = 0; k < cells; k++){
							 String temStr = "";
							XSSFCell xcell = xrow.getCell(k);
							if((i == 1 || i == 2 || i > 3) && (k == 2 || k == 7)){
								String rdom = "";
								XSSFCell xcell2 = xrow.getCell(0);
								if(pwdrdo.equals("1") && xcell2 != null){
									switch(xcell2.getCellType()){
										case XSSFCell.CELL_TYPE_STRING:
											rdom = xcell2.getRichStringCellValue().toString();
											break;
										case XSSFCell.CELL_TYPE_NUMERIC:
											rdom = String.valueOf((int)xcell2.getNumericCellValue());
											break;
									}
									rdom = rdom.substring(rdom.length() - 4);
								}else{
									rdom = String.valueOf((Math.random()*9+1)*100000);		//六位随机数
									rdom = rdom.substring(0, 6);
								}
								if(xcell == null ){			//写入初始密码
									xcell = xrow.createCell(k);
									xcell.setCellValue(rdom);
								}else if(xcell.equals("")){
									xcell.setCellValue(rdom);
								}
							}
							if(xcell != null){
								switch(xcell.getCellType()){
									case XSSFCell.CELL_TYPE_STRING:		//字符串
										temStr = xcell.getRichStringCellValue().toString();
										break;
									case XSSFCell.CELL_TYPE_NUMERIC:	//数值
										if(!DateUtil.isCellDateFormatted(xcell)){
											temStr = String.valueOf((int)xcell.getNumericCellValue());
										}else{
											temStr = xcell.getDateCellValue().toString();
										}
										break;
									case XSSFCell.CELL_TYPE_BLANK:		//空白值
										System.out.println(path+"的第"+i+"个工作表第"+j+"行第"+k+"列为空值。");
										break;
									case XSSFCell.CELL_TYPE_BOOLEAN:	//布尔值
										System.out.println(path+"的第"+i+"个工作表第"+j+"行第"+k+"列为布尔值。");
										break;
									case XSSFCell.CELL_TYPE_FORMULA:	//公式
										System.out.println(path+"的第"+i+"个工作表第"+j+"行第"+k+"列为公式。");
										break;
									case XSSFCell.CELL_TYPE_ERROR:		//错误
										System.out.println(path+"的第"+i+"个工作表第"+j+"行第"+k+"列为错误值。");
										break;
								}
								if(sheetname.equals(SCHOOL_BASIC_INFO)){
									eduBasicInfo[k] = temStr;
								}else{
									tempArr[k] = temStr;
								}
							}
						}
						createFun(sheetname);		//根据不同的工作表做相应的处理
					}
				}
			}
			String tempStr = uploadFileName;			//将修改后的文件输出
/*			path = path.substring(0, path.lastIndexOf("\\"));
			path = path + tempStr;
			fileOut = new FileOutputStream("F:\\temp\\aaa\\new_Import_users.xlsx"); 
			fileOut = new FileOutputStream(path); 
			fileOut.close();*/
			HttpServletResponse response = getResponse();
			response.setHeader("Connection", "close"); 
			response.setHeader("Content-Type", "application/vnd.ms-excel;charset=utf-8"); 
			response.setHeader("Content-Disposition", "attachment;filename=" 
			                + new String(tempStr.getBytes(), "iso-8859-1")); 
			workbook.write(response.getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		} catch (FindException e) {
			e.printStackTrace();
		} catch (AddException e) {
			e.printStackTrace();
		}
	}