java分享第十七天-02（封装操作excel类）-白红宇

java分享第十七天-02（封装操作excel类）

阅读量：6572 次

发布时间：2019-06-24

本文共 4060 字，大约阅读时间需要 13 分钟。

java解析EXCEL

用的是POI的JAR包，兼容EXCEL2003及2007+版本的EXCEL

所需要的JAR包：

poi-3.8.jar

poi-ooxml.jar

poi-ooxml-schemas.jar

xmlbeans.jar

代码如下：

public class ExcelReader {    private String filePath;    private String sheetName;    private Workbook workBook;        private Sheet sheet;    private List
     
       columnHeaderList;    private List
      
       
        > listData;    private List
        
         
          > mapData;    private boolean flag;    public ExcelReader(String filePath, String sheetName) {        this.filePath = filePath;        this.sheetName = sheetName;        this.flag = false;        this.load();    }        private void load() {        FileInputStream inStream = null;        try {            inStream = new FileInputStream(new File(filePath));            workBook = WorkbookFactory.create(inStream);            sheet = workBook.getSheet(sheetName);                    } catch (Exception e) {            e.printStackTrace();        }finally{            try {                if(inStream!=null){                    inStream.close();                }                            } catch (IOException e) {                                e.printStackTrace();            }        }    }    private String getCellValue(Cell cell) {        String cellValue = "";        DataFormatter formatter = new DataFormatter();        if (cell != null) {            switch (cell.getCellType()) {                case Cell.CELL_TYPE_NUMERIC:                    if (DateUtil.isCellDateFormatted(cell)) {                        cellValue = formatter.formatCellValue(cell);                    } else {                        double value = cell.getNumericCellValue();                        int intValue = (int) value;                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);                    }                    break;          case Cell.CELL_TYPE_STRING:                    cellValue = cell.getStringCellValue();                    break;                case Cell.CELL_TYPE_BOOLEAN:                    cellValue = String.valueOf(cell.getBooleanCellValue());                    break;                case Cell.CELL_TYPE_FORMULA:                    cellValue = String.valueOf(cell.getCellFormula());                    break;                case Cell.CELL_TYPE_BLANK:                    cellValue = "";                    break;                case Cell.CELL_TYPE_ERROR:                    cellValue = "";                    break;                default:                    cellValue = cell.toString().trim();                    break;            }        }        return cellValue.trim();    }    private void getSheetData() {        listData = new ArrayList
          
           
            >(); mapData = new ArrayList
            
             
              >(); columnHeaderList = new ArrayList
              
               (); int numOfRows = sheet.getLastRowNum() + 1; for (int i = 0; i < numOfRows; i++) { Row row = sheet.getRow(i); Map
               
                 map = new HashMap
                
                 (); List
                 
                   list = new ArrayList
                  
                   (); if (row != null) { for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); if (i == 0){ columnHeaderList.add(getCellValue(cell)); } else{ map.put(columnHeaderList.get(j), this.getCellValue(cell)); } list.add(this.getCellValue(cell)); } } if (i > 0){ mapData.add(map); } listData.add(list); } flag = true; } public String getCellData(int row, int col){ if(row<=0 || col<=0){ return null; } if(!flag){ this.getSheetData(); } if(listData.size()>=row && listData.get(row-1).size()>=col){ return listData.get(row-1).get(col-1); }else{ return null; } } public String getCellData(int row, String headerName){ if(row<=0){ return null; } if(!flag){ this.getSheetData(); } if(mapData.size()>=row && mapData.get(row-1).containsKey(headerName)){ return mapData.get(row-1).get(headerName); }else{ return null; } } public static void main(String[] args) { ExcelReader eh = new ExcelReader("E:\\workspace\\test.xls","Sheet1"); System.out.println(eh.getCellData(1, 1)); System.out.println(eh.getCellData(1, "test1")); } }