（5）POI读取Excel内容

骑猪看日落 2022-07-29 08:58 186阅读 0赞

import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    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;
    import writeFileContent.ExcelUtil;
    
    public class ExcelReader { 
    
        /** * 读取excel03内容 * @param file excel03文件 * @return ArrayList<ArrayList<String>> */
        public static ArrayList<ArrayList<String>> readXLS(String file){
            ArrayList<ArrayList<String>> data=new ArrayList<ArrayList<String>>();
            try{
                InputStream is=new FileInputStream(file);
                HSSFWorkbook workbook=new HSSFWorkbook(is);//创建对Excel工作簿文件的引用 
                //遍历每个sheet表
                for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                    if (null != workbook.getSheetAt(numSheet)) {
                        HSSFSheet sheet = workbook.getSheetAt(numSheet); //获得一个sheet
                        //遍历sheet表的每一行的内容
                        for (int rowNumOfSheet = 0; rowNumOfSheet <= sheet.getLastRowNum(); rowNumOfSheet++) {
                            if (null != sheet.getRow(rowNumOfSheet)) {
                                HSSFRow row = sheet.getRow(rowNumOfSheet); //获得一行
                                ArrayList<String> rowList=new ArrayList<String>();
                                //遍历该行每个单元格的内容
                                for (int cellNumOfRow = 0; cellNumOfRow <= row.getLastCellNum(); cellNumOfRow++) {
                                    if (null != row.getCell(cellNumOfRow)) {
                                        HSSFCell cell = row.getCell(cellNumOfRow); //获得列值
                                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
       //数值类型
                                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                                DateFormat df=new SimpleDateFormat("yyyy/MM/dd");
                                                Date date=cell.getDateCellValue();
                                                rowList.add(df.format(date));
                                            }else{
                                                double value=cell.getNumericCellValue();
                                                rowList.add(String.valueOf(value));
                                            }
                                        }else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
       //布尔类型
                                            boolean bool=cell.getBooleanCellValue();
                                            rowList.add(String.valueOf(bool));
                                        }else {
                                            rowList.add(cell.getStringCellValue());
                                        }
                                    }
                                }
                                data.add(rowList);
                            }
                        }
                    }        
                }
            } catch (FileNotFoundException ex) {
                Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            }
            return data;
        }
    
        /** * 读取excel07内容 * @param file excel07文件 * @return ArrayList<ArrayList<String>> */
        public static ArrayList<ArrayList<String>> readXLSX(String file){
            ArrayList<ArrayList<String>> data=new ArrayList<ArrayList<String>>();
            try{
                //构造 XSSFWorkbook 对象，file 传入文件路径 
                XSSFWorkbook xwb = new XSSFWorkbook(file);
                //循环工作表Sheet
                for(int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++){
                    XSSFSheet sheet = xwb.getSheetAt(numSheet); 
                    if(sheet == null){
                        continue;
                    }
                    //循环行Row
                    for(int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++){
                        XSSFRow row = sheet.getRow(rowNum);
                        if(row == null){
                            continue;
                        }
                        ArrayList<String> rowList=new ArrayList<String>();
                        //循环列Cell
                        for(int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++){
                            XSSFCell cell = row.getCell(cellNum);
                            if(cell == null){
                                continue;
                            }
                            if(cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){
                                boolean bool=cell.getBooleanCellValue();
                                rowList.add(String.valueOf(bool));
                            }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
       //数值类型
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    DateFormat df=new SimpleDateFormat("yyyy/MM/dd");
                                    Date date=cell.getDateCellValue();
                                    rowList.add(df.format(date));
                                }else{
                                    double value=cell.getNumericCellValue();
                                    rowList.add(String.valueOf(value));
                                }
                            }else{
                                rowList.add(cell.getStringCellValue());
                            }
                        }
                        data.add(rowList);
                    }
                } 
            } catch (IOException ex) {
                Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            }
            return data;
        }
    
        /** * 根据文件后缀名来判断是xls还是xlsx，从而决定用哪个方法获取excel内容 * @param file 文件 * @return 内容 */
        public static ArrayList<ArrayList<String>> read(String file){
            ArrayList<ArrayList<String>> data=new ArrayList<ArrayList<String>>();
            String fileType=file.substring(file.lastIndexOf(".")).toLowerCase();
            switch(fileType){
                case ".xls":
                    data=readXLS(file);
                    break;
                case ".xlsx":
                    data=readXLSX(file);
                    break;
                default:
                    return null;             
            }
            return data;
        }
    
        public static void main(String args[]){
            String file="";
            ArrayList<ArrayList<String>> data=read(file);
            if(data!=null){
                for(ArrayList<String> list:data){
                    System.out.println(list);
                }
            }
        }
    }