POI导入2003和2007Excel,txt,cvs等文件并校验

这两天做的需求整理一下，导入Excel等文件并对文件进行校验

controller.java

@requestMapping(value = "uplod" ,method = RequestMethod.POST)
public String upload(@RequestParam("file") MultipartFile file, Model model){
	if(null == file.getOriginalFilename() && !"".equals(file.getOriginalFilename())){
		logger.info("上传文件失败，文件为空");
	}
	
	List<Integer> messageList = service.uploadFile(file);
	
	if(null == messageList){
		logger.info("上传失败，格式错误");
	}
	
	switch(messageList.get(0)){
		case 1:
			logger.info("缺少字段,请检查！");
			return INDEX_PAGE;
		case 2:
			logger.info("字段过多,请检查！");
			return INDEX_PAGE;
		case 3:
			logger.info("字段为空或者为存在空格,请检查！");
			return INDEX_PAGE;
		case 4:
			logger.info("字段长度过长,请检查！");
			return INDEX_PAGE;
		case 5:
			logger.info("文件存在重复字段,请检查！");
			return INDEX_PAGE;
		case 6:
			logger.info("数据库中已经存在,请检查！");
			return INDEX_PAGE;
		default:
			logger.infor("上传成功！");
			return REDIRECT_INDEX;
	}
}

service.java

public List<Integer> saveFile(MultipartFile file) throws Exception{
	List<String []> list = new ArrayList<String []>();
	//txt格式和csv格式
	if(file.getOriginalFilename().toLowerCase.endsWith(".txt") 
		|| file.getOriginalFilename().toLowerCase.endsWith(".csv")){
		BufferReader reader = new BufferReader(new InputStreamReader(file.getInputStream(),"gbk"))
		while(true){	
			String line = reader.readLine();
			if(null == line){
				break;
			}
			if(null != line && !"".equals(line)){
				String[] s = line.split("\\;");
				list.add(s);
			}
		}
		reader.close();
	} else if(file.getOriginalFilename().toLowerCase.endsWith(".xls") 
		|| file.getOriginalFilename().toLowerCase.endsWith(".xlsx")){
		String[][] excelDate = PoiUtils.getDataFormExcel(file, 1);
		for(String[] s : excelDate){
			list.add(s);
		}
	}
	
	//对读出的数据进行校验，包括字段为空校验，字段重复校验等
	List<Integer> messageList = validate(list);
	return messageList;
}

/**
 *	数据校验
 *	@param list
 *	@return 
 */
private List<Integer> validate(List<String []> list){
	List<Integer> messageList = new ArrayList<Integer>();
	int flag = 0;
	boolean saveFlag = true;
	int index = 0;
	 
	//以下开始对文档进行校验
	if(int i = 0; i < list.size(); i++){
		if(5 > list.get(i).length){
			flag = 1;
			index = i + 1;//此处为提示出问题行数,从数据开始计算,不包括文档标题行
			logger.info("上传文档缺少字段");
		}
	}
	if(int i = 0; i < list.size(); i++){
		if(6 < list.get(i).length){
			flag = 2;
			index = i + 1;//此处为提示出问题行数,从数据开始计算,不包括文档标题行
			logger.info("上传文档有过多字段");
		}
		if(6 < list.get(i).length){
			flag = 2;
			index = i + 1;//此处为提示出问题行数,从数据开始计算,不包括文档标题行
			logger.info("上传文档有过多字段");
		}
		
		if(0 != flag){
			saveFlag = false;
			break;
		}
	}
	if(saveFlag){
		for(String[] str : list){
			//保存数据库操作
		}
	}
	
	messageList.add(flag);
	messsagList.add(index);
	return messsagList;
 }

PoiUtils.java

/**
  *
  * @param file 
  * 	   excel文件
  * @param count
  *        忽略的行数
  */
public static String[][] getDataFromExcel(MultipartFile file, int count) throws Exception{
	return getDataFromExcel(file, count, 0);
}
/**
  *
  * @param file 
  * 	   excel文件
  * @param count
  *        忽略的行数
  * @param total
  *        要读取的列数
  */
public static String[][] getDataFromExcel(MultipartFile file, int count， int total) throws Exception{
	String[][] returnArray = null;
	//读取Excel2003
	if(file.getOriginalFilename().toLowerCase.endsWith(".xls")){
		return returnArray = read2003Excel(file, count, total);
	}
	//读取Excel2007
	if(file.getOriginalFilename().toLowerCase.endsWith(".xlsx")){
		return returnArray = read2007Excel(file, count, total);
	}
}
/**
 *读取2003
 */
public static String[][] read2003Excel(MultipartFile file, int count， int total) throws Exception{
	logger.info("读取2003Excel");
	List<String[]> result = new ArrayList<String[]>();
	int rowSize = 0;
	BufferInputStream in = new BufferInputStream(file.getInputStream());
	POIFSFileSystem fs = new POIFSFileSystem(in);
	HSSFWorkbook wb = new HSSFWorkbook(fs);
	HSSFCell cell = null;
	for(int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++){
		HSSFSheet st = wb.getSheetAt(sheetIndex);
		
		for(int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++){
			HSSFRow row = st.getRow(rowIndex);
			if(null == row){
				break;
			}
			int tempRowSize = 0;
			if(total != 0){
				tempRowSize = total;
			} else {
				tempRowSize = row.getLastCellNum();
			}
			if(tempRowSize > rowSize){
				rowSize = tempRowSize;
			}
			
			String[] values = new String[rowSize];
			Arrays.fill(values, "");
			boolean hasValue = false;
			for(int columnIndex = 0; columnIndex < (total > 0 ? total : row.getLastCellNum(); columnIndex++)){
				String value = "";
				cell = row.getCell(columnIndex);
				if(null != cell){
					cell.setCellType(XSSFCell.CELL_TYPE_STRING);
					switch(cell.getCellType()){
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if(HSSFDateUtil.isCellDateFormatted(cell)){
								Date date = cell.getDateCellValue();
								if(null != date){
									value = new SimpleDateFormat("yyyy-MM-dd").format(date);
								} else {
									value = "";
								}
								break;
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							if(!cell.getStringCellValue().endsWith("")){
								value = cell.getStringCellValue();
							} else {
								value = cell.getStringCellValue() + "";
							}
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							break;
						case HSSFCell.CELL_TYPE_ERROR：
							value = "";
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y" : "N");
							break;
						default:
							value = "";
					}
				}
				if(columnIndex ==0 && value.trim().equals("")){
					continue;
				}
				values[columnIndex] = value.trim();
			}
			for(int i = 0; i < values.length; i++){
				hasValue = false;
				if(null != values && !"".equals(values[i])){
					hasValue = true;
					break;
				}
			}
			if(hasValue){
				result.add(valuse);
			}
		}
	}
	in.close();
	String[][] returnArray = new String[result.size()][rowSize];
	for(int i = 0; i < returnArray.length; i++){
		returnArray[i] = (String)result.get(i);
	}
	return returnArray;
}
/**
 *读取2007
 */
public static String[][] read2003Excel(MultipartFile file, int count， int total) throws Exception{
	logger.info("读取2007Excel");
	List<String[]> result = new ArrayList<String[]>();
	int rowSize = 0;
	BufferInputStream in = new BufferInputStream(file.getInputStream());
	XSSFWorkbook xwb = new XSSFWorkbook(in);
	XSSFCell cell = null;
	for(int sheetIndex = 0; sheetIndex < xwb.getNumberOfSheets(); sheetIndex++){
		XSSFWorkbook st = xwb.getSheetAt(sheetIndex);
		
		for(int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++){
			XSSFRow row = st.getRow(rowIndex);
			if(null == row){
				break;
			}
			int tempRowSize = 0;
			if(total != 0){
				tempRowSize = total;
			} else {
				tempRowSize = row.getLastCellNum();
			}
			if(tempRowSize > rowSize){
				rowSize = tempRowSize;
			}
			
			String[] values = new String[rowSize];
			Arrays.fill(values, "");
			boolean hasValue = false;
			for(int columnIndex = 0; columnIndex < (total > 0 ? total : row.getLastCellNum(); columnIndex++)){
				String value = "";
				cell = row.getCell(columnIndex);
				if(null != cell){
					cell.setCellType(XSSFCell.CELL_TYPE_STRING);
					switch(cell.getCellType()){
						case XSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case XSSFCell.CELL_TYPE_NUMERIC:
							if(HSSFDateUtil.isCellDateFormatted(cell)){
								Date date = cell.getDateCellValue();
								if(null != date){
									value = new SimpleDateFormat("yyyy-MM-dd").format(date);
								} else {
									value = "";
								}
								break;
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;
						case XSSFCell.CELL_TYPE_FORMULA:
							if(!cell.getStringCellValue().endsWith("")){
								value = cell.getStringCellValue();
							} else {
								value = cell.getStringCellValue() + "";
							}
							break;
						case XSSFCell.CELL_TYPE_BLANK:
							break;
						case XSSFCell.CELL_TYPE_ERROR：
							value = "";
							break;
						case XSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y" : "N");
							break;
						default:
							value = "";
					}
				}
				if(columnIndex ==0 && value.trim().equals("")){
					continue;
				}
				values[columnIndex] = value.trim();
			}
			for(int i = 0; i < values.length; i++){
				hasValue = false;
				if(null != values && !"".equals(values[i])){
					hasValue = true;
					break;
				}
			}
			if(hasValue){
				result.add(valuse);
			}
		}
	}
	in.close();
	String[][] returnArray = new String[result.size()][rowSize];
	for(int i = 0; i < returnArray.length; i++){
		returnArray[i] = (String)result.get(i);
	}
	return returnArray;
}