exp：Java生成excel

用到apache.poi

• HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
• XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
• SXSSFWorkbook 是专门用来处理大量数据写入 Excel2007的问题的
• 读取仍然是“XSSFWorkbook”，写入则为“SXSSFWorkbook”

代码：

1、自定义Excel注解类

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@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、类上加注解

import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute;
import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.Date;

@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;
}

3、ExcelUtil.java

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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;

@Component
public class ExcelUtil<T> {

    /**
     * 根据filePath和dataset创建excel文件
     * @param filePath
     * @param dataset
     * @param <T>
     */
    public static <T> void createFile(String filePath, List<T> dataset) {
        XSSFWorkbook wb = new XSSFWorkbook();
        createExcel( wb, dataset);
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filePath);
            wb.write(out);
        }
        catch (Exception e){
            throw new BusinessRuntimeException("文件创建失败！",e);
        }
        finally {
            try {
                out.close();
            }
            catch (Exception e) {
                throw new BusinessRuntimeException("关闭文件输出流失败！",e);
            }
        }
    }

    /**
     * 添加新数据到已有的excel
     * @param filePath
     * @param dataset
     * @param <T>
     */
    public static <T> void appendDataToExcel(String filePath, List<T> dataset)  {
        if(CollectionUtils.isEmpty(dataset) ) {
            return;
        }
        FileOutputStream out = null;
        try{
            XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(filePath));
            // 原来的sheet
            Sheet sheet = workbook.getSheetAt(0);
            T t = dataset.get(0);
            // 获取t的excelFields 和 attributes信息
            List<Object> list = getExcelFieldsAndAttributes(t);
            List<Field> excelFields = (List<Field>)list.get(0);
            List<ExcelAttribute> attributes = (List<ExcelAttribute>)list.get(1);
            addDataToExcel(workbook,dataset,excelFields, attributes,sheet);
            out = new FileOutputStream(filePath);
            workbook.write(out);
        }
        catch (Exception e){
            throw new BusinessRuntimeException("excel文件不存在！",e);
        }
        finally {
            try {
                out.close();
            }
            catch (Exception e) {
                throw new BusinessRuntimeException("关闭文件输出流失败！",e);
            }
        }

    }

    /**
     * 创建sheet，并添加数据到里面
     * @param wb
     * @param dataset
     * @param <T>
     */
    private static <T> void createExcel(XSSFWorkbook wb, List<T> dataset ) {
        if(CollectionUtils.isEmpty(dataset) ) {
            return;
        }
        T t = dataset.get(0);
        // 整个类的注解,得到了定义的sheetName
        ExcelAttribute classAttribute = t.getClass().getAnnotation(ExcelAttribute.class);
        XSSFSheet sheet = wb.createSheet(classAttribute.sheetName());
        // 获取t的excelFields 和 attributes信息
        List<Object> list = getExcelFieldsAndAttributes(t);
        List<Field> excelFields = (List<Field>)list.get(0);
        List<ExcelAttribute> attributes = (List<ExcelAttribute>)list.get(1);
        addColumnLineToExcel(wb,dataset,excelFields, attributes,sheet);
        addDataToExcel(wb,dataset,excelFields, attributes,sheet);
        // 自动调整列宽
        //sheet.trackAllColumnsForAutoSizing();
        for(int i=0;i<excelFields.size();i++) {
            sheet.autoSizeColumn(i);
        }

    }

    /**
     * 获取需要存储的类的excelFields 和 attributes信息
     * @param t
     * @return
     */
    private static <T> List<Object> getExcelFieldsAndAttributes(T t){
        List<Object> res = new ArrayList<>();
        //  一个field表示一个属性
        Field[] fields = t.getClass().getDeclaredFields();
        // 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));
        }
        res.add(excelFields);
        res.add(attributes);
        return res;
    }

    /**
     * 添加第一行标题栏到表格中
     * @param wb
     * @param dataset
     * @param excelFields
     * @param attributes
     * @param sheet
     * @param <T>
     */
    private static <T> void addColumnLineToExcel(XSSFWorkbook 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中
     * @param wb
     * @param dataset
     * @param excelFields
     * @param attributes
     * @param sheet
     * @param <T>
     */
    private static <T> void addDataToExcel(XSSFWorkbook wb, List<T> dataset,List<Field> excelFields, List<ExcelAttribute> attributes,Sheet sheet) {
        XSSFCellStyle style = null;
        // sheet原有行数
        int rowNums = sheet.getLastRowNum()+1;
        // 添加数据到excel
        for(int i=0;i<dataset.size();i++) {
            // 数据行号从1开始,因为第0行放的是列的名称
            Row row = sheet.createRow(i+rowNums);
            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);
                // 填充色
                if(!"".equals(oneAttribute.fillColor())){
                    XSSFColor myColor = new XSSFColor(toColorFromString(oneAttribute.fillColor()));
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(myColor);
                }
                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;
    }

}