poi的excel匯出

poi的excel匯出

這個匯出依賴於模板檔案，可便捷設定表頭樣式。 也可以不使用模板，直接建立。

1.引入poi依賴

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

2.準備模板檔案

3.編寫匯出程式碼

測試程式碼直接匯出到本地，如需要在瀏覽器中匯出將輸出流交給瀏覽器即可

public class GenerateExcel {
    public static void main(String[] args) throws IOException {
        GenerateExcelXlsx();
    }


   static void GenerateExcelXlsx() throws IOException {

       List<Student> students = new Student().stuAll();
       InputStream inputStream = GenerateExcel.class.getClassLoader().getResourceAsStream("static/測試填充excel.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // 獲取第一個sheet
        Sheet sheet = workbook.getSheetAt(0);
        int index = 2;
        for (Student item : students) {
            Row row = sheet.createRow(index);
            row.createCell(0).setCellValue(item.getName());
            row.createCell(1).setCellValue(item.getSex().toString());
            row.createCell(2).setCellValue(item.getAge());
            index += 1;
        }

       FileOutputStream outputStream = new FileOutputStream("C:\\Users\\pyb\\Desktop\\a.xlsx");

        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();

    }


    static class Student{
        private String name;
        private Integer age;
        private Character sex;

        public Student() {
        }

        public Student(String name, Character sex, Integer age) {
            this.name = name;
            this.sex = sex;
            this.age = age;
        }

        List<Student> stuAll(){
             ArrayList<Student> list = new ArrayList<>();
             for (int i = 0; i < 5; i++) {
                 Student student = new Student("張三" + i, '男' ,18 );
                 list.add(student);
             }
             return list;
         }

        public String getName() {
            return name;
        }

        public Integer getAge() {
            return age;
        }

        public Character getSex() {
            return sex;
        }
    }

}

4.執行後效果圖

5.不依賴模板直接匯出

這種方式表頭內容需要自己手動寫，

public class GenerateExcel {
    public static void main(String[] args) throws IOException {
        GenerateExcelXlsx2();
    }

   static void GenerateExcelXlsx2() throws IOException {

        List<Student> students = new Student().stuAll();
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 建立一個sheet，這裡面形參是內部名稱，不可見
        Sheet sheet = workbook.createSheet();
        // 設定為第幾個sheet，並設定使用者可見名稱
        workbook.setSheetName(0,"測試sheet");

        int index = 0;
        for (Student item : students) {
            Row row = sheet.createRow(index);
            row.createCell(0).setCellValue(item.getName());
            row.createCell(1).setCellValue(item.getSex().toString());
            row.createCell(2).setCellValue(item.getAge());
            index += 1;
        }

       FileOutputStream outputStream = new FileOutputStream("C:\\Users\\pyb\\Desktop\\b.xlsx");

        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();

    }


    static class Student{
        private String name;
        private Integer age;
        private Character sex;

        public Student() {
        }

        public Student(String name, Character sex, Integer age) {
            this.name = name;
            this.sex = sex;
            this.age = age;
        }

        List<Student> stuAll(){
             ArrayList<Student> list = new ArrayList<>();
             for (int i = 0; i < 5; i++) {
                 Student student = new Student("張三" + i, '男' ,18 );
                 list.add(student);
             }
             return list;
         }

        public String getName() {
            return name;
        }

        public Integer getAge() {
            return age;
        }

        public Character getSex() {
            return sex;
        }
    }

}

應用模板表格中的格式

一般應用與固定位置填充完畢的計算，動態的沒測試過

資料塞入完畢後加入以下程式碼

 // 計算公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateAll();

總結

獲取某行或者某列時候，儘量用建立方式，除非確定行和列都能被獲取到（行列不做操作、修改格式的情況下內容是空，會報空指標）