最近项目需要做一个批量导入用户的功能,而且还要对多张工作表进行修改,最终将新的数据写入数据库并且将新的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();
}
}