Java中将jsonArray导出为Excel

2023-01-07 10:46:22

java中使用jxl导出excel时，需指定WritableSheet对象中对应于每个单元格的数据。List类型是一种常用的数据类型，它里面的元素是实体对象，当将它创建为WritableSheet对象时，它的每个实体元素可以通过简单索引循环的形式遍历到，但对实体每个属性的遍历，却不能以简单索引循环的形式实现，而只能用getter方法去逐一获取，这样，对不同的实体，均需特定的实现代码与之对应，势必会造成大量的代码冗余。
一种有效的策略是，将List转化为jsonArray，把实体对象的属性名构造为一个字符串数组，那么，只需使用该数组的索引，对每个json对象循环访问它的字符串形式的键，便可完成对属性值的遍历。

示例：

实体类Student

public class Student {
    private String name;
    private String gender;
    private int age;

    public Student(String name, String gender, int age) {
        this.name = name;
        this.gender = gender;
        this.age = age;
    }

    public String getName() {
        return name;
    }

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

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public int getAge() {
        return age;
    }

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

JsonArray导出为Exclel

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.io.File;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class JSONArrayToExcel {
    public static void main(String[] args) {

        Student student1 = new Student("张三","男",18);
        Student student2 = new Student("李四","男",19);
        Student student3 = new Student("小花","女",20);

        //实体List
        List<Student> studentList = new ArrayList<>();
        studentList.add(student1);
        studentList.add(student2);
        studentList.add(student3);

        //List转为JSONArray
        JSONArray jsonArray = JSONArray.fromObject(studentList);

        //实体属性名称数组
        Field[] fields = null;
        try {
            Class clazz = Class.forName("Student");
            fields = clazz.getDeclaredFields();
        }catch (Exception e){
            e.printStackTrace();
        }

        if (fields != null){
            String fileName = "jsonarrayToExcelTest.xls"; //文件名
            File myFile = new File("./"+fileName);

            try {
                WritableWorkbook writableWorkbook = Workbook.createWorkbook(myFile); //定义工作簿对象
                WritableSheet writableSheet = writableWorkbook.createSheet("sheet1",0); //定义sheet对象

                for (int i = 0; i < fields.length; i++) { //加入表头单元格内容
                    writableSheet.addCell(new Label(i,0,fields[i].getName()));
                }

                for (int i = 0; i < jsonArray.size(); i++) { //加入数据单元格内容
                    JSONObject json = jsonArray.getJSONObject(i);
                    for (int j = 0; j < fields.length; j++) {
                        writableSheet.addCell(new Label(j,i+1,json.get(fields[j].getName()).toString()));
                    }
                }

                writableWorkbook.write();
                writableWorkbook.close();
            }catch (Exception e){
                e.printStackTrace();
            }

        }

    }
}

参考：

码农公寓

相关文章