java如何讀取excel檔案

標籤：java 讀取 excel

我在測試的時候經常需要去改動case，如果把所有case直接寫在單元測試中，非常冗餘，我希望可以直接從excel去讀取我的測試資料，經過幾次實驗終於成功了，讀取excel主要藉助poi jar包，源碼如下所示。poi jar包http://down.51cto.com/data/2012838

ps：因為測試資料僅string類型已夠用，所以代碼只支援string類型的cell 內容，如果是想支援其他資料類型，需要進一步對cell內容進行判斷，此處略過~

import java.io.BufferedInputStream;import java.io.FileInputStream;import java.io.IOException;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class readExcelSelf {        public static void main (String[] args) throws IOException{                String filePath = "F:\\ExcelDemo.xlsx";                FileInputStream isr = new FileInputStream(filePath);                BufferedInputStream bis = new BufferedInputStream(isr);                int startRowIndex = 1;//從指定行開始讀取資料，因為通常第一行都是作為標題列                String[][] content = getContent(bis,startRowIndex);//excel的資料存如"行*列"的二維數組                bis.close();                int contentLength = content.length;                for (int i=0;i<contentLength;i++){                        for(int j=0;j<content[i].length;j++){                                System.out.print(content[i][j]+"\t");                            }                        System.out.println();                    }            }    private static String[][] getContent(BufferedInputStream bis, int sri) throws IOException {                XSSFWorkbook xw = new XSSFWorkbook(bis);                XSSFRow row = null;                XSSFCell cell = null;                XSSFSheet sheet = null;                List<String[]> rowArrays = new ArrayList<String[]>();                int maxColumnSize = 0;                       for (int i=0;i<xw.getNumberOfSheets();i++){                        sheet = xw.getSheetAt(i);                        for (int rowIndex=sri;rowIndex<=sheet.getLastRowNum();rowIndex++){                                row = sheet.getRow(rowIndex);                                if(row == null){                                        continue;                                    }                                int columnSize = row.getLastCellNum();                                if(columnSize>maxColumnSize){                                        maxColumnSize = columnSize;                                    }                                String[] rowValue = new String[columnSize];                                Arrays.fill(rowValue, "");                                boolean isRowValueNull = false;                                for (int columnIndex=0;columnIndex<columnSize;columnIndex++){                                        cell = row.getCell(columnIndex);                                        if(cell== null || cell.getStringCellValue().trim().equals("")){                                                continue;                                            }                                        else{                                                rowValue[columnIndex]=cell.getStringCellValue();                    }                                        isRowValueNull = true;                                    }                                if(isRowValueNull){                                        rowArrays.add(rowValue);                                    }                            }                    }                String[][] rowColumnArray = new String[rowArrays.size()][maxColumnSize];                for(int i=0;i<rowArrays.size();i++){                        rowColumnArray[i]=rowArrays.get(i);                    }                return rowColumnArray;    }}

        
    

 

java如何讀取excel檔案