EXCEL导入

来源：互联网发布：淘宝摄影店编辑：程序博客网时间：2024/05/22 09:44

废话不多说，直接上代码：

Workbook workbook=null;//XSSFWorkbook workBook=null;try{try {workbook=WorkbookFactory.create(new FileInputStream("c:\\test.excel"));} catch (InvalidFormatException e) {// TODO Auto-generated catch blocke.printStackTrace();}Sheet sheet=workbook.getSheet("Sheet1");int realCell[]=new int[14];//获取真实行int realRows=ExcelUtil.findRealRows(sheet,3,realCell);//获取物理行int rows=sheet.getPhysicalNumberOfRows();//循环行for(int i=0;i<rows;i++){if(i>2&&i<3+realRows){Row row=sheet.getRow(i);if(row!=null){int cells=row.getPhysicalNumberOfCells();String value="";for(int j=0;j<cells;j++){Cell cell=row.getCell(j);if(cell!=null){switch(cell.getCellType()){case Cell.CELL_TYPE_NUMERIC:if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {                         Date theDate = cell.getDateCellValue();                      SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                      value+= dff.format(theDate)+",";                  }else{                  HSSFDataFormatter dataFormatter = new HSSFDataFormatter();                    value+= dataFormatter.formatCellValue(cell)+",";                  }  break;case Cell.CELL_TYPE_STRING:value+=cell.getStringCellValue()+",";break;case Cell.CELL_TYPE_BLANK:value+=""+",";break;default:value+=""+",";}}}if(value.indexOf(",")!=-1){String[] val=value.split(",",-1);if(val.length==15){TakeChangeEntity entity=new TakeChangeEntity();entity.setCategory(val[0]);entity.setFirmNo(val[1]);entity.setFirmName(val[2]);entity.setBrand(val[3]);entity.setChangeBeforeTake(val[4]);entity.setChangeBeforeBottom(val[5]);if(!"".equals(val[6])){entity.setChangeBeforeStartDate(DateUtil.YMDCNFmt(val[6]));}if(!"".equals(val[7])){entity.setChangeBeforeEndDate(DateUtil.YMDCNFmt(val[7]));}entity.setChangeAfterTake(val[8]);entity.setChangeAfterTakeBottom(val[9]);if(!"".equals(val[10])){entity.setChangeAfterStartDate(DateUtil.YMDCNFmt(val[10]));}if(!"".equals(val[11])){entity.setChangeAfterEndDate(DateUtil.YMDCNFmt(val[11]));}entity.setActiviteContent(val[12]);entity.setRemark(val[13]);takeList.add(entity);}}}}}}catch(IOException e){result.put(SystemConstant.APP_RESULT_KRY, SystemConstant.APP_RESULT_FAILE);result.put(SystemConstant.APP_RESULT_MESSAGE_KEY, "导入失败");e.printStackTrace();}

在导入的时候，当EXCEL对空行设置样式时，会导致将空行也读出，因此，写一个获取真实行的方法：

public static int findRealRows(Sheet sheet,int startIndex, int... flag) {        int row_real = 0;        int rows = sheet.getPhysicalNumberOfRows();// 此处物理行数统计有错误,        int size = flag.length;        try {        for (int i = startIndex; i < rows; i++) {            Row row = sheet.getRow(i);            int total = 0;            ArrayList<Integer> blank =new ArrayList<Integer>();            int type=-1;            String s = null;            for(int j:flag){                if(!(row.getCell(j) == null)&&row.getCell(j).getCellType()<2){                    type=row.getCell(j).getCellType();                    row.getCell(j).setCellType(1);                }                if (row.getCell(j) == null||row.getCell(j).getStringCellValue().matches("^\\s+$")||row.getCell(j).getCellType()>2) {                    total++;                    if(!(row.getCell(j) == null)&&row.getCell(j).getCellType()<2){                    row.getCell(j).setCellType(type);                    }                    blank.add(j);                }            }            // 如果所有列都是空说明就该返回            if (total == flag.length) {                return row_real;            } else if (total == 0) {                row_real++;            } else {            }        }        } catch (NullPointerException e) {           e.printStackTrace();        }        return row_real;    }

阅读全文

0 0