poi excel export

poi excel export

This export relies on a template file, which makes it easy to set up table header styles. It can also be created directly without using a template.

1. Introduce poi dependencies

<dependency>
    <groupId></groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

2. Prepare template files

3. Write export code

Test code is exported directly to the local, if you need to export in the browser to give the output stream to the browser can be

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 = ().getResourceAsStream("static/Test Fill");
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // Getting the firstsheet
        Sheet sheet = (0);
        int index = 2;
        for (Student item : students) {
            Row row = (index);
            (0).setCellValue(());
            (1).setCellValue(().toString());
            (2).setCellValue(());
            index += 1;
        }

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

        (outputStream);
        ();
        ();

    }


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

        public Student() {
        }

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

        List<Student> stuAll(){
             ArrayList<Student> list = new ArrayList<>();
             for (int i = 0; i < 5; i++) {
                 Student student = new Student("John Doe" + i, 'male' ,18 );
                 (student);
             }
             return list;
         }

        public String getName() {
            return name;
        }

        public Integer getAge() {
            return age;
        }

        public Character getSex() {
            return sex;
        }
    }

}

4. After running the effect diagram

5. Do not rely on the template directly export

This way the table header content needs to be written manually by yourself.

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();
        // Create asheet，In this case, the formal parameter is the internal name，not visible
        Sheet sheet = ();
        // Set to the firstsheet，and set the user's visible name
        (0,"test (machinery etc)sheet");

        int index = 0;
        for (Student item : students) {
            Row row = (index);
            (0).setCellValue(());
            (1).setCellValue(().toString());
            (2).setCellValue(());
            index += 1;
        }

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

        (outputStream);
        ();
        ();

    }


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

        public Student() {
        }

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

        List<Student> stuAll(){
             ArrayList<Student> list = new ArrayList<>();
             for (int i = 0; i < 5; i++) {
                 Student student = new Student("John Doe" + i, 'male' ,18 );
                 (student);
             }
             return list;
         }

        public String getName() {
            return name;
        }

        public Integer getAge() {
            return age;
        }

        public Character getSex() {
            return sex;
        }
    }

}

Applying formatting in template tables

Generally used for calculations with fixed positions filled, not tested for dynamics.

Once the data is stuffed add the following code

 // Calculate the formula
FormulaEvaluator evaluator = ().createFormulaEvaluator();
().

summarize

When fetching a row or column, try to use the creation method, unless you are sure that both rows and columns can be fetched (rows and columns without manipulation, modify the format of the contents of the empty, will report the null pointer)