1.自定义注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by weili on 2017/7/24.
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD,ElementType.TYPE})
public @interface ExcelAttribute {
/**
* excel里的sheet名,默认是"sheet1"
* @return
*/
String sheetName() default "sheet1";
/**
* excel里对应的列名,默认为""
* @return
*/
String columnName() default "";
/**
* 列对应的排序序号,默认是0
* @return
*/
int order() default 0;
enum DataType {
String, Number, Date
}
/**
* 数据类型,可以是String,Number(数字型),Date等类型
* @return
*/
DataType type() default DataType.String;
/**
* 日期格式,默认是"yyyy-MM-dd HH:mm:ss"
* @return
*/
String datePattern() default "yyyyMMdd HH:mm:ss";
/**
* 保留小数点后的位数,默认是0
* @return
*/
int decimalNums() default 0;
/**
* 背景颜色,默认为白色"FFFFFF",
* 表示形式为颜色的十六进制字符串,常见的:
* red: "FF0000",Orange: "FFA500",yellow: "FFFF00",
* green: "008000",blue: "0000FF",purple: "800080"
* @return
*/
String fillColor() default "FFFFFF";
/**
* 字段是否放弃存储到excel里,默认为false
* @return
*/
boolean skip() default false;
}
2.生成excel的工具类
import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute;
import com.creditease.microloan.mil.tasks.exceptions.BusinessRuntimeException;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import java.awt.Color;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
/**
* Created byweili on 2017/7/20.
*/
@Component
public class ExcelUtil<T> {
/**
* 根据filePath和dataset创建文件
* @param filePath
* @param dataset
* @param <T>
*/
public static <T> void createFile(String filePath, List<T> dataset) {
SXSSFWorkbook wb = new SXSSFWorkbook();
createExcel( wb, dataset);
FileOutputStream out = null;
try {
out = new FileOutputStream(filePath);
wb.write(out);
}
catch (Exception e){
throw new BusinessRuntimeException("文件创建失败!",e);
}
finally {
if(out!=null) {
try {
out.close();
}
catch (Exception e){
throw new BusinessRuntimeException("关闭文件输出流失败!",e);
}
}
}
}
/**
* 创建sheet,并添加数据到里面
* @param wb
* @param dataset
* @param <T>
*/
private static <T> void createExcel(SXSSFWorkbook wb, List<T> dataset ) {
if(CollectionUtils.isEmpty(dataset) ) {
return;
}
T t = dataset.get(0);
// 获取实体类的所有属性,即包括public、private和proteced,但是不包括父类的申明字段
// 一个field表示一个属性
Field[] fields = t.getClass().getDeclaredFields();
// 整个类的注解,得到了定义的sheetName
ExcelAttribute classAttribute = t.getClass().getAnnotation(ExcelAttribute.class);
SXSSFSheet sheet = wb.createSheet(classAttribute.sheetName());
// excel里存储类的部分属性和顺序号
Map<Field,Integer> map = new LinkedHashMap<>();
ExcelAttribute excelAttribute = null;
for (Field field : fields) {
// 某个属性上的注解,如果没写注解或者注解里的skip为true,表示该列不会存储到excel里
excelAttribute = field.getAnnotation(ExcelAttribute.class);
if (excelAttribute != null) {
if (!excelAttribute.skip()) {
map.put(field, excelAttribute.order());
}
}
}
// 排序
List<Map.Entry<Field,Integer>> list = new ArrayList<Map.Entry<Field,Integer>>(map.entrySet());
Collections.sort(list, (o1, o2) -> o1.getValue().compareTo(o2.getValue()));
// 存储类的注解skip为false的排序后的属性对应的Field
List<Field> excelFields = new ArrayList<>();
for(Map.Entry<Field,Integer> mapping:list){
excelFields.add(mapping.getKey());
}
// excel里存储的列的ExcelAttribute
List<ExcelAttribute> attributes = new ArrayList<>();
for (int j = 0; j < excelFields.size(); j++) {
attributes.add(excelFields.get(j).getAnnotation(ExcelAttribute.class));
}
addDataToExcel(wb,dataset,excelFields, attributes,sheet);
// 自动调整列宽
sheet.trackAllColumnsForAutoSizing();
for(int i=0;i<excelFields.size();i++) {
sheet.autoSizeColumn(i);
}
}
/**
* 添加数据到excel中
* @param wb
* @param dataset
* @param excelFields
* @param attributes
* @param sheet
* @param <T>
*/
private static <T> void addDataToExcel(SXSSFWorkbook wb, List<T> dataset,List<Field> excelFields, List<ExcelAttribute> attributes,Sheet sheet) {
XSSFCellStyle style = (XSSFCellStyle)wb.createCellStyle();
// 居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// excel放入第一行列的名称
Row row = sheet.createRow(0);
for (int j = 0; j < excelFields.size(); j++) {
Cell cell = row.createCell(j);
ExcelAttribute oneAttribute = attributes.get(j);
cell.setCellValue(oneAttribute.columnName());
cell.setCellStyle(style);
}
// 添加数据到excel
for(int i=0;i<dataset.size();i++) {
// 数据行号从1开始,因为第0行放的是列的名称
row = sheet.createRow(i+1);
for(int j=0;j<attributes.size();j++) {
Cell cell = row.createCell(j);
ExcelAttribute oneAttribute = attributes.get(j);
style = (XSSFCellStyle)wb.createCellStyle();
// 居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 填充色
XSSFColor myColor = new XSSFColor(toColorFromString(oneAttribute.fillColor()));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(myColor);
// 四个边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
cell.setCellStyle(style);
try{
// 根据属性名获取属性值
String cellValue = BeanUtils.getProperty( dataset.get(i), excelFields.get(j).getName());
if(ExcelAttribute.DataType.Date.equals(oneAttribute.type()))
{
// CST格式的时间字符串转为Date对象
String CST_FORMAT = "EEE MMM dd HH:mm:ss z yyyy";
Date cstDate = new SimpleDateFormat(CST_FORMAT, Locale.US).parse(cellValue);
DateFormat df = new SimpleDateFormat(
oneAttribute.datePattern());
cell.setCellValue( df.format(cstDate) );
}
else if(ExcelAttribute.DataType.Number.equals(oneAttribute.type())) {
// 保留小数点后的位数
int decimalNums = oneAttribute.decimalNums();
StringBuilder format = new StringBuilder("#0");
for(int w=0;w<decimalNums;w++) {
if(w==0) {
format.append(".");
}
format.append("0");
}
cell.setCellValue(String.valueOf(new DecimalFormat(format.toString()).format(Double.parseDouble(cellValue))));
}
else {
cell.setCellValue(cellValue);
}
}
catch (Exception e) {
throw new BusinessRuntimeException("获取类的属性值失败!", e);
}
}
}
}
/**
* 颜色的16进制字符串转换成Color对象
* @param colorStr 例如蓝色为"0000FF"
* @return Color对象
* */
private static Color toColorFromString(String colorStr){
Color color = new Color(Integer.parseInt(colorStr, 16)) ;
return color;
}
}
3.被注解的实体类
import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute;
import lombok.AllArgsConstructor;
import lombok.Data;
import java.util.Date;
/**
* Created by weili on 2017/7/20.
*/
@Data
@AllArgsConstructor
@ExcelAttribute(sheetName = "stuSheet")
public class Stu {
@ExcelAttribute(columnName="学号",order=0,fillColor = "FF0000")
private Integer stuNo;
@ExcelAttribute(columnName="姓名",order=2,skip = true)
private String name;
@ExcelAttribute(columnName="成绩",order=5, type = ExcelAttribute.DataType.Number,decimalNums = 4)
private Double grade;
@ExcelAttribute(columnName="注册时间",order=0,datePattern = "yyyy-MM-dd HH:mm",type = ExcelAttribute.DataType.Date)
private Date loginDate;
@ExcelAttribute(columnName="是否男孩",order=4,fillColor = "FFFF00")
private Boolean isBoy;
}
package com.creditease.microloan.mil.tasks;
import com.creditease.microloan.mil.tasks.model.Stu;
import com.creditease.microloan.mil.tasks.util.ExcelUtil;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Created by weili on 2017/7/20.
*/
public class ExcelUtilTest {
@Test
public void test() {
List<Stu> dataset = new ArrayList<>();
dataset.add(new Stu(1,"zhangsan",67.9990,new Date(),true));
dataset.add(new Stu(2,"lisi",0.03,new Date(),false));
String[] fieldColumns ={"number","sname"};
String sheetName ="ss33";
String path = "/Users/apple/Desktop/11.xlsx";
ExcelUtil.createFile(path,dataset);
}
}