java导出多sheet的excel文件：
1.ExcelDealUtil.java
package pers.li.util;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel数据处理
*/
public class ExcelDealUtil {
public static boolean isShow = true;
public static <T> XSSFWorkbook getWorkbook(List<Map<String, Object>> listObj) {
// Collection<T> dataSet, String[] params, String[] titles
// 创建excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
for (Map<String, Object> map : listObj) {
// 定义表头
String[] title = (String[]) map.get("titles");
//定义sheet
String sheetName = (String) map.get("sheetName");
//数据
Collection<T> dataSet = (Collection<T>) map.get("list");
//列对应属性字段
String[] params = (String[]) map.get("field");
// 创建工作表sheet
XSSFSheet sheet = workbook.createSheet(sheetName);
// 创建第一行
XSSFRow row = sheet.createRow(0);
XSSFCell cell = null;
if (title == null || title.length == 0) {
System.err.println("titles不能为空！");
return null;
}
if (sheetName == null) {
System.err.println("sheetName不能为空！");
return null;
}
// 插入第一行数据的表头
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
int idx = 1;
//遍历数据，并且添加值
if (dataSet != null) {
for (Object obj : dataSet) {
// 获取到每一行的属性值数组
String[] strings = getValues(obj, params);
XSSFRow nrow = sheet.createRow(idx++);
XSSFCell ncell = null;
for (int i = 0; i < strings.length; i++) {
ncell = nrow.createCell(i);
ncell.setCellValue(strings[i]);
}
}
}
// 设置自动列宽
for (int i = 0; i < title.length; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
}
}
return workbook;
}
public static <T> XSSFWorkbook getWorkbook(Collection<T> dataSet, String[] params, String[] titles) {
// 定义表头
String[] title = titles;
// 创建excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表sheet
XSSFSheet sheet = workbook.createSheet();
// 创建第一行
XSSFRow row = sheet.createRow(0);
XSSFCell cell = null;
// 插入第一行数据的表头
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
int idx = 1;
//遍历数据，并且添加值
for (Object obj : dataSet) {
// 获取到每一行的属性值数组
String[] strings = getValues(obj, params);
XSSFRow nrow = sheet.createRow(idx++);
XSSFCell ncell = null;
for (int i = 0; i < strings.length; i++) {
ncell = nrow.createCell(i);
ncell.setCellValue(strings[i]);
}
}
//
// 设置自动列宽
for (int i = 0; i < title.length; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
}
return workbook;
}
// 根据需要输出的变量名数组获取属性值
public static String[] getValues(Object object, String[] params) {
try {
if (params != null && params.length > 0) {
String[] values = new String[params.length];
for (int i = 0; i < params.length; i++) {
Field field = object.getClass().getDeclaredField(params[i]);
// 设置访问权限为true，可以访问私有变量
field.setAccessible(true);
// 获取属性
convertValues(object, values, i, field);
}
isShow = false;
return values;
} else {
Field[] ms = object.getClass().getDeclaredFields();
String[] values = new String[ms.length];
for (int i = 0; i < ms.length; i++) {
if (isShow) {
System.err.println(ms[i].getName());
}
Field field = object.getClass().getDeclaredField(ms[i].getName());
// 设置访问权限为true，可以访问私有变量
field.setAccessible(true);
// 获取属性
convertValues(object, values, i, field);
}
isShow = false;
return values;
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
private static void convertValues(Object object, String[] values, int i, Field field) throws IllegalAccessException {
if (i == 0 && isShow) {
System.err.println("【type==】指的是，当前需要导出数据的数据类型:【您可能会根据数据类型的不同去调整此处对应关系】");
}
Class<?> type = field.getType();
if (isShow) {
System.err.println("type==" + type);
}
if (type == int.class) {
values[i] = String.valueOf((int) field.get(object));
} else if (type == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
String format = sdf.format((Date) field.get(object));
values[i] = format;
} else {
values[i] = field.get(object).toString();
}
}
}
2.测试实体类
package pers.li.util;
import java.util.Date;
public class Person {
private String name;
private int age;
private String job;
private Integer num;
private Date createTime;
private boolean aBoolean;
private Double aDouble;
public Integer getNum() {
return num;
}
public Person(String name, int age, String job, Integer num, Date createTime,Double aDouble, boolean aBoolean) {
this.name = name;
this.age = age;
this.job = job;
this.num = num;
this.createTime = createTime;
this.aBoolean = aBoolean;
this.aDouble = aDouble;
}
public void setNum(Integer num) {
this.num = num;
}
public boolean isaBoolean() {
return aBoolean;
}
public void setaBoolean(boolean aBoolean) {
this.aBoolean = aBoolean;
}
public Double getaDouble() {
return aDouble;
}
public void setaDouble(Double aDouble) {
this.aDouble = aDouble;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Person(String name, int age, String job) {
super();
this.name = name;
this.age = age;
this.job = job;
}
public Person(String name, int age, String job,Date createTime) {
super();
this.name = name;
this.age = age;
this.job = job;
this.createTime = createTime;
}
public Person(String name, int age, String job,Date createTime,Double aDouble,boolean aBoolean) {
super();
this.name = name;
this.age = age;
this.job = job;
this.createTime = createTime;
this.aBoolean=aBoolean;
this.aDouble=aDouble;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
}
3.测试 ExcelDealUtilTest.java
package pers.li.util;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel数据处理测试类
*/
public class ExcelDealUtilTest {
public static void main(String[] args) {
List<Person> list = new ArrayList<>();
list.add(new Person("张三", 15, "学生", 24, new Date(), 2.333, false));
list.add(new Person("李四", 20, "实习生", 23, new Date(), 3.444, true));
list.add(new Person("王五", 26, "Java工程师", 45, new Date(), 3.444, true));
list.add(new Person("小明", 30, "主管", 44, new Date(), 3.444, true));
//属性对应数组：
String[] field = new String[]{"name", "age", "job", "num", "createTime", "aBoolean", "aDouble"};
//列名title对应数组
String[] colume = new String[]{"姓名", "年龄", "职业", "数量", "创建时间", "boolean", "double"};
//第一种使用方式：多sheet+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
List<Map<String, Object>> listObj = new ArrayList<>();
//第一个sheet数据**************************************************
HashMap<String, Object> map = new HashMap<>();
map.put("list", list);
map.put("field", field);
map.put("titles", colume);
map.put("sheetName", "属性取值-1");
//第2个sheet数据**************************************************
HashMap<String, Object> map2 = new HashMap<>();
map2.put("list", list);
map2.put("field", null);
map2.put("titles", colume);
map2.put("sheetName", "属性取值-2");
listObj.add(map);
listObj.add(map2);
//当写了列对应关系，则按列对应关系处理
XSSFWorkbook workbook1 = ExcelDealUtil.getWorkbook(listObj);
if (workbook1 != null) {
try (
OutputStream out = new FileOutputStream("D://3.xlsx");
) {
workbook1.write(out);
System.out.println("导出完成");
} catch (IOException e) {
e.printStackTrace();
}
}
//第一种使用方式：多sheet+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//第二种使用方式：单sheet+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//单个sheet的数据导出:列值对应关系取 类的属性【按顺序取默认值】
XSSFWorkbook workbook2 = ExcelDealUtil.getWorkbook(list, null, colume);
if (workbook2 != null) {
try (
OutputStream out = new FileOutputStream("D://4.xlsx");
) {
workbook2.write(out);
System.out.println("导出完成");
} catch (IOException e) {
e.printStackTrace();
}
}
//第二种使用方式：单sheet+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//第三种使用方式：单sheet+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//单个sheet的数据导出:列值对应关系取 类的属性【列值对应值】
XSSFWorkbook workbook3 = ExcelDealUtil.getWorkbook(list, field, colume);
if (workbook3 != null) {
try (
OutputStream out = new FileOutputStream("D://5.xlsx");
) {
workbook3.write(out);
System.out.println("导出完成");
} catch (IOException e) {
e.printStackTrace();
}
}
//第三种使用方式：单sheet+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
}
}
