JavaPoi建立與讀取Excel

1. Poi 包下載

2. 建立實體Java Bean–Student

public class Student {
    private int id;
    private String name;
    private int age;
    private Date birth;
    
    public Student() {  }
    
    public Student(int id, String name, int age, Date birth) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }
        
}

1. 建立Excel表

public class CreateXLS {
    private static String path = "E:/Student.xls";
    private static List<Student> mList;
    /**工作簿*/
    private static HSSFWorkbook workbook;

    static {
        mList = new ArrayList<Student>();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");
        try {
            Student user1 = new Student(1, "張三", 16,
                    dateFormat.parse("1997-03-12"));
            Student user2 = new Student(2, "李四", 17,
                    dateFormat.parse("1996-08-12"));
            Student user3 = new Student(3, "王五", 26,
                    dateFormat.parse("1985-11-12"));
            mList.add(user1);
            mList.add(user2);
            mList.add(user3);
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args) {
        workbook = new HSSFWorkbook();
        // 第二步， 在webbook中新增一個sheet，對應Excel檔案中的sheet
        HSSFSheet sheet = workbook.createSheet("學生表一");
        // 第三步，在sheet中新增表頭第0行，注意老版本poi對Excel的行數列數有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，建立單元格，並設定值表頭，設定表頭居中
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 建立一個居中格式
        // 建立第一行
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("學號");
        cell.setCellStyle(style);
        
        cell = row.createCell(1);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        
        cell = row.createCell(2);
        cell.setCellValue("年齡");        
        cell.setCellStyle(style);
        
        cell = row.createCell(3);
        cell.setCellValue("生日");
        cell.setCellStyle(style);
        // 第五步，寫入實體資料 實際應用中這些資料從資料庫得到，
        for (int i = 0; i < mList.size(); i++) {
            row = sheet.createRow(i + 1);
            Student student = mList.get(i);
            // 第四步，建立單元格，並設定值
            row.createCell((short) 0).setCellValue((double) student.getId());
            row.createCell((short) 1).setCellValue(student.getName());
            row.createCell((short) 2).setCellValue((double) student.getAge());
            cell = row.createCell((short) 3);
            cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(student.getBirth()));
        }
        
        try{
            FileOutputStream fout = new FileOutputStream(path);
            workbook.write(fout);
            fout.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }
}

1. 讀取Excel表

public class ReadXLS {
    private static String path = "E:/Student.xls";
    
    public static void main(String[] args) throws IOException {
        List<List<Map<String,String>>> list = readExcelWithTitle(path);
        System.out.println(list.toString());
    }
    
    /**
     * [[{姓名=張三, 生日=1997-03-12, 學號=1.0, 年齡=16.0}, 
     *   {姓名=李四, 生日=1996-08-12, 學號=2.0, 年齡=17.0}, 
     *   {姓名=王五, 生日=1985-11-12, 學號=3.0, 年齡=26.0}]]
     */
    public static List<List<Map<String, String>>> readExcelWithTitle(String path) throws IOException {
        String fileType = path.substring(path.lastIndexOf(".")+1, path.length());
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            inputStream = new FileInputStream(path);
            if (fileType.equals("xls")){
                workbook = new HSSFWorkbook(inputStream);
            }
            // 對應excel檔案
            List<List<Map<String, String>>> result = new ArrayList<List<Map<String,String>>>();
            // 遍歷sheet頁
            int sheetSize = workbook.getNumberOfSheets();
            
            for (int i = 0; i < sheetSize; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                // 對應sheet頁
                List<Map<String, String>> sheetList = new ArrayList<Map<String,String>>();
                // 對應所有標題
                List<String> titles = new ArrayList<String>();
                // 遍歷行
                int rowSize = sheet.getLastRowNum() + 1;
                for (int j = 0; j < rowSize; j++) {
                    Row row = sheet.getRow(j);
                    if (null == row) { // 略過空行
                        continue;
                    }
                    
                    int cellSize = row.getLastCellNum();// 行中有多少個單元格，也就是有多少列
                    if (0 == j) {
                        for (int k = 0; k < cellSize; k++) {
                            Cell cell = row.getCell(k);
                            titles.add(cell.toString());
                        }
                    } else { // 其他資料行
                        Map<String, String> rowMap = new HashMap<String, String>();
                        for (int k = 0; k < cellSize; k++) {
                            Cell cell = row.getCell(k);
                            String key = titles.get(k);
                            String value = null;
                            if (null != cell) {
                                value = cell.toString();
                            }
                            rowMap.put(key, value);
                        }
                        sheetList.add(rowMap);
                    }
                }
                result.add(sheetList);              
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != workbook) {
                workbook.close();
            }
            if (null != inputStream) {
                inputStream.close();
            }
        }       
        return null;
    }
}