传入需要导出的数据列表。自动导出Excel。在导出表比较多的情况可以省点代码。
原理:
反射和注解
第一步:
先定义注解。注解的作用的,标注哪些字段需要导出,以及字段的标题。
import java.lang.annotation.*;
/**
* 少 年 辛 苦 终 身 事
* 莫 向 光 阴 惰 寸 功
* Today the best performance as tomorrow newest starter!
* Created by IntelliJ IDEA.
*
* @author : songsong.wu
* github: https://github.com/songdesy
* email: callwss@qq.com
* <p>
* Date: 2019/2/18 3:02 PM
* Description:
* Copyright(©) 2019/2/18 by songsong.wu.
**/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
String filedName() default "";
}
第二步:
在需要导出的实体类的字段上加上注解。如:
注意,使用的lombok。自行修改
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.*;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
/**
* @author
*/
@Table(name = "sys_test")
@Entity
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
@AllArgsConstructor
@NoArgsConstructor
@Data
public class SysTest implements Serializable {
private static final long serialVersionUID = -27388489913014L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private String id;
@ExcelField(filedName = "测试一")
@Column(name = "test1")
private String test1;
@ExcelField(filedName = "测试一")
@Column(name = "test2")
private Float test2;
@ExcelField(filedName = "备注")
@Column(name = "remarks")
private String remarks;
@Column(name = "history_flag")
private String historyFlag = "0";
@Column(name = "create_date")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createDate;
@Column(name = "update_date")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date updateDate;
@Column(name = "del_flag")
private String delFlag = "0";
}
第三步:
导出,使用的是poi 4.0.1
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* 少 年 辛 苦 终 身 事
* 莫 向 光 阴 惰 寸 功
* Today the best performance as tomorrow newest starter!
* Created by IntelliJ IDEA.
*
* @author : songsong.wu
* github: https://github.com/songdesy
* email: callwss@qq.com
* <p>
* Date: 2018/8/2 下午6:44
* Description:
* Copyright(©) 2018/8/2 by songsong.wu.
**/
public class ExcelExportUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);
public static void exportExcel(List list, String excelName, String sheetName) {
if (CollectionUtils.isEmpty(list)) {
return;
}
Workbook wb = createWorkbook();
CellStyle cellStyle = createCellStyle(wb);
Font font = wb.createFont();
Sheet sheet = createSheet(wb, sheetName);
int rowIndex = 1;
for (Object o : list) {
Row row = sheet.createRow(rowIndex);
int cellIndex = 0;
Class cls = o.getClass();
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Annotation annotation = field.getAnnotation(ExcelField.class);
if (annotation != null) {
Object value = fieldValue(field, o);
if (value != null) {
createCell(value, cellStyle, row, cellIndex, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, font);
}
cellIndex++;
}
}
rowIndex++;
}
createTitle(list.get(0), wb, sheet);
saveFile(wb);
}
private static void createTitle(Object o, Workbook wb, Sheet sheet) {
Row row = sheet.createRow(0);
int cellIndex = 0;
Class cls = o.getClass();
Field[] fields = cls.getDeclaredFields();
Font font = wb.createFont();
CellStyle cellStyle = createCellStyle(wb);
font.setFontHeightInPoints((short) 12);
font.setFontName("楷体");
font.setBold(true);
for (Field field : fields) {
Annotation annotation = field.getAnnotation(ExcelField.class);
if (annotation != null) {
createCell(((ExcelField) annotation).filedName(), cellStyle, row, cellIndex, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, font);
cellIndex++;
}
}
}
private static Object fieldValue(Field field, Object o) {
try {
return field.get(o);
} catch (IllegalAccessException e) {
return null;
}
}
private static void saveFile(Workbook wb) {
try (OutputStream fileOut = new FileOutputStream("/Users/wss/Downloads/excelImport.xls")) {
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}
}
private static void createCell(Object value, CellStyle cellStyle, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign, Font font) {
Cell cell = row.createCell(column);
cell.setCellValue(value.toString());
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
private static Workbook createWorkbook() {
Workbook wb = new HSSFWorkbook();
return wb;
}
private static CellStyle createCellStyle(Workbook wb) {
return wb.createCellStyle();
}
private static Sheet createSheet(Workbook wb, String sheetName) {
Sheet sheet1 = wb.createSheet(sheetName);
return sheet1;
}
public static void main(String[] args) {
SysTest sysTest = new SysTest();
sysTest.setTest1("hello");
sysTest.setTest2((float) 10);
sysTest.setRemarks("谢谢");
List list = new ArrayList();
list.add(sysTest);
ExcelExportUtil.exportExcel(list, "第一个测试", "第一个工作");
}
}
可以直接运行main方法测试!