一、导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
二、自定义注解
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
String value() default "未知列名";
String className() default "";
}
三、创建实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
@ExcelField("教师姓名")
private String name;
@ExcelField("性别")
private String sex;
@ExcelField("电话号")
private String phone;
@ExcelField("年龄")
private Integer age;
@ExcelField("工号")
private String teacherNo;
@ExcelField("下拉框")
private List<String> ss;
}
四、编写Excel工具类
工具类实现
#使用 BiConsumer 返回类型 通过.accpet()方法进行传参
#可更改传参方式
public static BiConsumer<Object,String> getHSSFWorkbook( Class<?> c){
return(list,name)-> {
#具体实现
}
1、创建工作簿以及sheet页
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("基本数据");
//设置列宽度大小
sheet.setDefaultColumnWidth((short) 15);
//设置行高
sheet.setDefaultRowHeight((short) (15 * 20));
2、创建表头标题
int indexRow = 0;
int startIndex = 0;
HSSFCell headCell;
#从第0行开始,设置表头
HSSFRow hssfRow = sheet.createRow(indexRow++);
#通过映射得到实体类字段
Field[] fields = c.getDeclaredFields();
for (Field field : fields) {
#通过映射得到表头名称
ExcelField excelField = field.getAnnotation(ExcelField.class);
if (excelField != null) {
#设置第0行 第startIndex++列的表头
headCell = hssfRow.createCell(startIndex++);
//设置表头的样式
setHeadStyle(headCell, wb);
//设置单元格内容
headCell.setCellValue(excelField.value());
}
}
3、设置表内容
#创建行
HSSFRow hssfRowData;
#得到表内数据集合
List<Object> objects = null;
if (list instanceof List) {
objects = (List) list;
}
for (int i = 0; i< Objects.requireNonNull(objects).size(); i++) {
#从第 1 行开始插入数据
hssfRowData = sheet.createRow(indexRow++);
int temp = 0;
HSSFCell hssfCell;
#获取实体类中属性字段
Field[] fieldDatas = objects.get(i).getClass().getDeclaredFields();
for (int j=0;j<fieldDatas.length;j++) {
#字段类型
Type fieldType = fieldDatas[j].getType();
#字段名称
String fieldName = fieldDatas[j].getName();
#对于List类型字段单独判断
if ("java.util.List".equals(fieldType.getTypeName())) {
#通过getFieldValueByFieldName方法 调用get方法的到字段值
List<String> fieldValueByFieldName = (List<String>) getFieldValueByFieldName(fieldName, objects.get(i));
#设置第几列为下拉框
CellRangeAddressList region = new CellRangeAddressList(0,fieldValueByFieldName.size(), j, j);
#将list集合转变为数组
String [] wordTypes=fieldValueByFieldName.toArray(new String[fieldValueByFieldName.size()]);
#创建数据验证
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
#加载下拉列表值
DataValidationConstraint validationConstraint = validationHelper.createExplicitListConstraint(wordTypes);
#创建验证方式
DataValidation dataValidation = validationHelper.createValidation(validationConstraint, region);
#做兼容性
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
#将验证与工作簿绑定
sheet.addValidationData(dataValidation);
}else {
#对于非List类型字段进行设置值
#从第 0 列创建单元格开始
hssfCell = hssfRowData.createCell(temp++);
#得到对应的字段名 调用get方法 的到对应的字段值
Object value = getFieldValueByFieldName(fieldDatas[j].getName(), objects.get(i));
#设置样式
setDataStyle(hssfCell, wb);
#设置单元格值
setValue(hssfCell, value);
}
}
}
4、导出excel
File file = new File(name);
FileOutputStream outputStream;
try {
outputStream = new FileOutputStream(file);
wb.write(outputStream);
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
5、所封装方法
- setValue(HSSFCell hssfCell,Object value) 设置单元格值
/**
* 设置表格内容的值
* @param hssfCell 单元格对象
* @param value 单元格的值
*/
private static void setValue(HSSFCell hssfCell,Object value){
if (value instanceof String) {
hssfCell.setCellValue(value.toString());
}else if (value instanceof Integer) {
hssfCell.setCellValue((Integer) value);
} else if (value instanceof Double) {
hssfCell.setCellValue((Double) value);
} else if (value instanceof Boolean) {
hssfCell.setCellValue((Boolean) value);
} else if (value instanceof Float) {
hssfCell.setCellValue((Float) value);
} else if (value instanceof Short) {
hssfCell.setCellValue((Short) value);
}else if (value instanceof Long) {
hssfCell.setCellValue((Long) value);
} else if (value instanceof Character) {
hssfCell.setCellValue((Character) value);
}
}
- setDataStyle(HSSFCell hssfCell,HSSFWorkbook wb) 设置样式
/**
* 设置数据项单元格样式的样式
* @param hssfCell 单元格对象
* @param wb Excel对象
*/
private static void setDataStyle(HSSFCell hssfCell,HSSFWorkbook wb){
HSSFCellStyle style=wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font font=wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)11);
style.setFont(font);
hssfCell.setCellStyle(style);
}
- getFieldValueByFieldName(String fieldName, Object object) 通过相应的字段名调用get方法
/**
* 通过属性名字,调用相应的Get方法获取属性值
*
* @param object
* @param fieldName 属性名字
* @return
*/
public static Object getFieldValueByFieldName(String fieldName, Object object) {
Class<?> c = object.getClass();
try {
//设置对象的访问权限,保证对private的属性的访问
String s = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Method method = c.getMethod("get" + s);
return method.invoke(object);
} catch (Exception e) {
return null;
}
}
- setHeadStyle(HSSFCell hssfCell, HSSFWorkbook wb) 设置表头样式
/**
* 设置表头单元格样式
*/
private static void setHeadStyle(HSSFCell hssfCell, HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
style.setFont(font);//设置字体
//设置背景色
/* style.setFillForegroundColor((short) 13);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);*/
hssfCell.setCellStyle(style);
}
五、测试
public static void main(String[] args) {
List<Teacher> list=new ArrayList<>();
List<String> stringList=new ArrayList<>();
stringList.add("delete");
stringList.add("modify");
String filePath="poi.xlsx";
list.add(new Teacher("张三","男","190789799099",20,"88098900989890",stringList));
list.add(new Teacher("李四","男","190789799099",24,"88777779898880",stringList));
ExcelUtil.getHSSFWorkbook(Teacher.class).accept(list,filePath);
}
以上导出时,excel中存在下拉框列,使用以上方法进行下拉框导出存在长度限制问题,当下拉框中数据超过80个时则不会显示。
六、下拉框优化
1、创建文本簿,设置表头数据
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("基本数据");
XSSFSheet hiddenSheet = wb.createSheet("selectHidden");
int indexRow = 0;
int titleIndex = 0;
XSSFCell headCell;
XSSFRow hssfRow = sheet.createRow(indexRow++);
Field[] fields = Teacher.class.getDeclaredFields();
for (Field field : fields) {
ExcelField excelField = field.getAnnotation(ExcelField.class);
if (excelField != null) {
headCell = hssfRow.createCell(titleIndex++);
//设置单元格内容
setValue(headCell, excelField.value());
}
}
2、设置excel数据
XSSFCell xssfCell;
for (Teacher teacher : list) {
hssfRow = sheet.createRow(indexRow++);
int temp = 0;
Field[] declaredFields = teacher.getClass().getDeclaredFields();
for (Field field : declaredFields) {
String name = field.getName();
List<String> list1 = new ArrayList<>();
Object obj = getValue(name, teacher);
if (obj instanceof ArrayList) {
for (Object o : (List<?>) obj) {
list1.add((String) o);
}
test(list1, STR, 5, 5, wb, sheet, 0, hiddenSheet);
} else {
xssfCell = hssfRow.createCell(temp++);
setValue(xssfCell, obj);
}
}
}
3、导出excel
public static void test(List<?> stringList, String str, int start, int end,
XSSFWorkbook wb, XSSFSheet sheet, int index, XSSFSheet hiddenSheet){
Object[] wordTypes = stringList.toArray(new Object[0]);
XSSFCell cell;
XSSFRow row;
for (int k = 0; k < wordTypes.length; k++) {
if (hiddenSheet.getRow(k)!=null){
row=hiddenSheet.getRow(k);
}else {
row=hiddenSheet.createRow(k);
}
cell = row.createCell(index);
setValue(cell, wordTypes[k]);
}
wb.setSheetHidden(wb.getSheetIndex(hiddenSheet), true);
CellRangeAddressList addressList = new CellRangeAddressList(0, wordTypes.length, start, end);
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenSheet.getSheetName() + str + wordTypes.length);
DataValidation validation = helper.createValidation(constraint, addressList);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
}