POI：支持xls/xlsx文件格式按cell类型解析相关内容（exls 2003/2007 兼容）

* POI：支持xls/xlsx文件格式按cell类型解析相关内容 * 1.支持xls和xlsx文件格式的解析（exls 2003/2007 兼容） * 2.遍历sheet总数 * 3.遍历row总数 * 4.遍历cell总数 * 5.可以判断常见数据类型 * 6.日期格式化显示 * @author Administrator

[1].[代码] POI：支持xls/xlsx文件格式按cell类型解析相关内容 跳至 [1]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

import java.io.File; import java.io.IOException; import java.text.SimpleDateFormat; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.junit.Test; /** * POI：支持xls/xlsx文件格式按cell类型解析相关内容 * 1.支持xls和xlsx文件格式的解析（exls 2003/2007 兼容） * 2.遍历sheet总数 * 3.遍历row总数 * 4.遍历cell总数 * 5.可以判断常见数据类型 * 6.日期格式化显示 * @author Administrator *  */ public class MyPoiTest {     @Test     public void getMyXLS() { //      ArrayList<Map<String,Object>> xlsMapList = new ArrayList<Map<String,Object>>();         File xlsOrxlsxFile = new File( "D:/upload_goods_asset_teml.xlsx" );         if (!xlsOrxlsxFile.exists()){             return ;         }         try {             Workbook wb = WorkbookFactory.create(xlsOrxlsxFile);             int sheetNum = wb.getNumberOfSheets();             Sheet sheet = null ;             for ( int sheetIndex = 0 ;sheetIndex<sheetNum;sheetIndex++){ //遍历sheet(index 0开始)                 System.out.println( "sheet:" +sheetIndex);                 sheet = wb.getSheetAt(sheetIndex);                 Row row = null ;                 int firstRowNum = sheet.getFirstRowNum();                 int lastRowNum = sheet.getLastRowNum();                 for ( int rowIndex = firstRowNum;rowIndex<=lastRowNum;rowIndex++ ) { //遍历row(行 0开始)                     row = sheet.getRow(rowIndex);                     if ( null != row){                         int firstCellNum = row.getFirstCellNum();                         int lastCellNum = row.getLastCellNum();                         for ( int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) { //遍历cell（列 0开始）                             Cell cell = row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL);                             if ( null != cell) {                                 Object cellValue = null ; //cellValue的值                                 switch (cell.getCellType()) {                                 case Cell.CELL_TYPE_STRING:                                     System.out.println(cell.getRichStringCellValue()                                             .getString());                                     cellValue = cell.getRichStringCellValue()                                             .getString();                                     break ;                                 case Cell.CELL_TYPE_NUMERIC:                                     if (DateUtil.isCellDateFormatted(cell)) {                                         System.out.println(cell.getDateCellValue());                                         cellValue= cell.getDateCellValue();                                         //TODO 可以按日期格式转换                                         SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );                                          String time = formatter.format(cellValue);                                         System.out.println( "formater time:" +time);                                     } else {                                         System.out.println(cell.getNumericCellValue());                                         cellValue=cell.getNumericCellValue();                                     }                                     break ;                                 case Cell.CELL_TYPE_BOOLEAN:                                     System.out.println(cell.getBooleanCellValue());                                     cellValue = cell.getBooleanCellValue();                                     break ;                                 case Cell.CELL_TYPE_FORMULA:                                     System.out.println(cell.getCellFormula());                                     cellValue = cell.getCellFormula();                                     break ;                                 default :                                     System.out.println( "not find match type." );                                 }                                 System.out.println( "value:" +cellValue);                             } else {                                 //TODO cell is null 用 *** 代替输出                                 System.out.println( "***" );                             }                         } //end cell                     } else {                         //TODO row is null                     }                 } //end row             } //end sheet         } catch (InvalidFormatException e) {             e.printStackTrace();         } catch (IOException e) {             e.printStackTrace();         }     } }