Excel操作(一)——模拟从数据库查询数据转换为Excel

简单的例子，表中格式为默认，没有合并单元格以及设置单元格样式等操作。

Maven仓库引入poi依赖

   <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
   </dependency>
   <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
   </dependency>
   

方法体

该方法参数为一个存放数据的List集合和标题字符串数组

public static void generateExcelByDBData(List<Student> stuList, String[] arr) throws IllegalAccessException, IOException {
        //利用反射获取Student的字段名
        Field[] fields = Student.class.getDeclaredFields();
        //创建Excel文档
        XSSFWorkbook stuBook = new XSSFWorkbook();
        //sheet对应一个工作页
        XSSFSheet firstSheet = stuBook.createSheet("firstSheet");
        //下标为0的行开始
        XSSFRow firstRow = firstSheet.createRow(0);
        XSSFCell[] firstCell = new XSSFCell[arr.length];
        //创建标题
        for (int i = 0; i < arr.length; i++) {
            firstCell[i] = firstRow.createCell(i);
            firstCell[i].setCellValue(arr[i]);
        }

        //将对象转换为Map<String,String>型的map便于设值
        List<Map<String, String>> data = new ArrayList<>();
        for (int i = 0; i < stuList.size(); i++) {
            Map<String, String> map = new HashMap<>();
            Student student = stuList.get(i);
            Field[] stuFields = student.getClass().getDeclaredFields();
            for (Field field : stuFields) {
                field.setAccessible(true);
                String m = "";
                if (field.get(student) != null) {
                    m = field.get(student).toString();
                }
                map.put(field.getName(), m);
            }
            data.add(map);
        }

        //将data里的值赋给cell单元格
        Map<String, String> dataMap;
        for (int i = 0; i < data.size(); i++) {
            //创建一行
            XSSFRow row_now = firstSheet.createRow(firstSheet.getLastRowNum() + 1);
            dataMap = data.get(i);
            for (int j = 0; j < fields.length; j++) {
                XSSFCell col = row_now.createCell(j);
                col.setCellValue(dataMap.get(fields[j].getName()));
            }
        }

        OutputStream out = new FileOutputStream("d:/student.xlsx");
        stuBook.write(out);
        out.close();
    }
}

Main方法中代码为

        //模拟从数据库查询到的数据
        //假设Student类的字段作为表的标题
        String[] arr = {"学号", "姓名", "年龄", "性别", "地址"};
        List<Student> stuList = new ArrayList<>();
        stuList.add(new Student(1, "张三", 20, "男", "湖北省武汉市江夏区金融港"));
        stuList.add(new Student(2, "李四", 22, "男", "湖北省武汉市江夏区金融港"));
        stuList.add(new Student(2, "於五", 23, "男", "湖北省武汉市江夏区金融港"));
        try {
            generateExcelByDBData(stuList, arr);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

运行代码，在目标地址生成.xlsx文件

如图