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;//读取Excel2003if(file.getOriginalFilename().toLowerCase.endsWith(".xls")){return returnArray = read2003Excel(file, count, total);}//读取Excel2007if(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;}