使用方法：将自定义的pojo类，添加到list中，直接调用输出类的out方法就可以
（注意：这个pojo类的属性上必须使用自定义注解，index属性用来标识该属性放在哪一列，value则标示输出的中文列名）目前支持的属性：String、int、double、Date
准备两个类：1.自定义注解类、2.输出类
用到的依赖，这些依赖也可以用其他类似的工具类替换
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>
自定义注解类
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCell {
//输出列的中文列名
String value();
//该属性输出列的位置
int index();
}
输出类：
/**
*
* @author: hrw
* time: 2022/9/17 12:15
* description:根据list<Object>直接输出excel文件
*/
public class ExcelOutUtilByList {
static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* @param fileName 文件名，不包含后缀
* @param path excel保存路径,不包含文件名
* @param data 数据list，里面的pojo属性需要有注解@ExcelVaue，才能识别
* @param sdf2 自定义时间格式化类，传null则使用默认格式 “yyyy-MM-dd HH:mm:ss”
* @param comment 备注，会显示在标题旁边
* @return 文件位置
*/
public static String out(String fileName, String path, List data,SimpleDateFormat sdf2,String comment) {
if (fileName.contains(".")) {
fileName = fileName.substring(0, fileName.indexOf("."));
}
if (data == null || data.size() < 1) {
return "没有数据";
}
Map indexMap = new HashMap();
Map titleMap = new HashMap();
Class<?> aClass = data.get(1).getClass();
boolean isOk = getValueIndex(new ArrayList(), titleMap, indexMap, aClass);//获取列名和位置
if (!isOk) {
return "所有参数都没有@ExcelCell注解，读取列名失败";
}
Method[] methods = aClass.getMethods();//获取方法列表
//创建表格
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(fileName + 1);
setHerderRow(fileName,titleMap,wb,comment);//设置标题栏 和样式 第1行
setCellName(titleMap,indexMap,wb,sheet);//设置列名 第2行
HSSFCellStyle style = getContent(wb);//获取单元格样式
for (int i = 0; i < data.size(); i++) {//循环插入数据 从第三行开始插入
HSSFRow row = sheet.createRow(i + 2);
Set indexSet = indexMap.keySet();
Iterator iterator = indexSet.iterator();
while (iterator.hasNext()) {
String keyParam = (String) iterator.next();
int index = (int) indexMap.get(keyParam);
Object o = data.get(i);
for (Method method : methods) {
if (method.getName().equals(keyParam)) {
HSSFCell cell = row.createCell(index - 1);
String type = method.getGenericReturnType().toString();
try {
if (type.equals("int")) { //取出来的值类型跟excel支持的类型匹配
int value = (int) method.invoke(o,null);
cell.setCellValue(value);
} else if (type.equals("class java.util.Date")) {
Date value = (Date) method.invoke(o,null);
String format = "";
if (sdf2 != null) {
format = sdf2.format(value);
} else {
format = sdf.format(value);//Date类先格式化成String再存到cell的value中
}
cell.setCellValue(format);
} else if (type.equals("double")) {
double value = (double) method.invoke(o,null);
cell.setCellValue(value);
} else {
String value = (String) method.invoke(o,null);
cell.setCellValue(value);
}
cell.setCellStyle(style);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
}
//输出文件
String fileName2 = path + fileName + ".xls";
File file = new File(fileName2);
//创建文件输出流
try {
FileOutputStream fileOutputStream = new FileOutputStream(file);
//用最开始创建的工作簿.write进行文件写出
wb.write(fileOutputStream);
} catch (IOException e) {
e.printStackTrace();
System.out.println("导出excel异常：" + e.getMessage());
return "导出excel异常：" + e.getMessage();
}
System.out.println("导出成功：" + fileName2);
return fileName2;
}
/**
* 根据标题栏创建 各个列名
* @param titleMap 位置-中文标题
* @param indexMap 属性名-位置
* @param wb
* @param sheet
*/
private static void setCellName(Map titleMap, Map indexMap, HSSFWorkbook wb,HSSFSheet sheet) {
HSSFRow row = sheet.createRow(1);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 居中
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
Set set = indexMap.keySet();
Iterator iterator = set.iterator();
while (iterator.hasNext()) {
String cellName = (String) iterator.next();//英文属性名
int index = (int) indexMap.get(cellName);//对应位置
sheet.autoSizeColumn(index-1);//自动列宽
HSSFCell cell = row.createCell(index - 1);
cell.setCellValue((String) titleMap.get(index));//将中文名设置进去
cell.setCellStyle(style);
}
}
/**
* 设置sheet页的标题栏和样式
* @param fileName
* @param titleMap
* @param wb
* @param comment
*/
private static void setHerderRow(String fileName,Map titleMap,HSSFWorkbook wb,String comment) {
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCell cell = sheet.createRow(0).createCell(0);
if (comment != null && comment.length() > 0) {//设置备注
fileName += "（" + comment +"）";
}
cell.setCellValue(fileName);//第一行第一列设置为标题栏
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
// style.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置加粗
HSSFFont font1 = wb.createFont();
font1.setColor((short)16);;
font1.setFontHeightInPoints((short)18);
style.setFont(font1);
style.setAlignment(HorizontalAlignment.CENTER);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,titleMap.size()-1));//合并标题栏
cell.setCellStyle(style);
}
/**
* 根据要输出的类属性上的注解，获取标题栏和列位置
* @param count 固定传空list
* @param titleMap
* @param indexMap
* @param classzz
* @return
*/
public static boolean getValueIndex(List count,Map titleMap,Map indexMap, Class<?> classzz) {
boolean flag = true;
Field[] fields = classzz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
ExcelCell annotation = field.getAnnotation(ExcelCell.class);
if (annotation == null) {
continue;
}
String name = fields[i].getName();
name = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
indexMap.put(name, annotation.index());
titleMap.put(annotation.index(),annotation.value());
count.add(1);
}
if (classzz.getSuperclass() != null) {
getValueIndex(count,titleMap,indexMap,classzz.getSuperclass());
}
if (count.size() == 0) {
flag = false;
System.out.println("所有参数都没有@ExcelCell注解，读取列名失败");
}
return flag;
}
/**
* 设置数据的单元格格式
* @param wb
* @return
*/
public static HSSFCellStyle getContent(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
return style;
}
}
测试用的pojo
public class Suanshu {
@ExcelCell(value = "参数1",index = 1)
private int oneCell;
@ExcelCell(value = "参数2",index = 2)
private int twoCell;
@ExcelCell(value = "加法结果",index = 3)
private String jiafaResult;
@ExcelCell(value = "减法结果",index = 4)
private String jianfaResult;
@ExcelCell(value = "乘法结果",index = 5)
private String chengfaResult;
get set等等方法。。。
｝
输出效果：
标签：index,style,java,String,excel,List,param,value,wb From： https://www.cnblogs.com/huangruiwu/p/16712362.html