java 导出数据到excel

一.引入依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

二.准备自定义注解

1.日期类格式化注解

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)
public @interface FieldDateFormat {
    String value() default "";
}

2.表头注解

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)
public @interface FieldDoc {
    String value() default "";
}

三.自定义实体，这里新建了 ExcelTestEntity 实体

import ttd.Annotation.FieldDateFormat;
import ttd.Annotation.FieldDoc;

import java.sql.Timestamp;

public class ExcelTestEntity {

    @FieldDoc("姓名")
    private String name;

    @FieldDoc("年龄")
    private Integer age;
    @FieldDoc("住址")
    private String address;

    @FieldDoc("生日")
    @FieldDateFormat("yyyy-MM-dd HH:mm:ss")
    private Timestamp birthDay;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Timestamp getBirthDay() {
        return birthDay;
    }

    public void setBirthDay(Timestamp birthDay) {
        this.birthDay = birthDay;
    }
}

四.导出时使用的工具类和需要的实体

/**
 * 
 */
public class ExcelEntity<T> {

    private Class<T> clazz;
    private List<T> data;

    public Class<T> getClazz() {
        return clazz;
    }

    public void setClazz(Class<T> clazz) {
        this.clazz = clazz;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }
}

package ttd.handle;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.stereotype.Service;
import ttd.Annotation.FieldDateFormat;
import ttd.Annotation.FieldDoc;
import ttd.entity.ExcelEntity;

import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.Map;

/**
 * 
 */
@Service
public class ExcelHandle {

    /**
     * * 导入excel
     *
     * @param sources key指sheet name,value指集合
     * @return
     * @throws Exception
     */
    public ByteArrayOutputStream write(Map<String, ExcelEntity<?>> sources) throws Exception {
        HSSFWorkbook workbook = null;
        ByteArrayOutputStream result = new ByteArrayOutputStream();
        try {
            workbook = new HSSFWorkbook();
            //设置头字体
            HSSFFont titleFont = workbook.createFont();
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 11);
            titleFont.setFontName("Microsoft YaHei");

            //设置内容字体
            HSSFFont contentFont = workbook.createFont();
            contentFont.setBold(false);
            contentFont.setFontHeightInPoints((short) 11);
            contentFont.setFontName("Microsoft YaHei");

            //设置标题样式
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setBorderRight(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            titleStyle.setFont(titleFont);

            //常规样式
            HSSFCellStyle general = workbook.createCellStyle();
            general.setBorderBottom(BorderStyle.THIN);
            general.setBorderLeft(BorderStyle.THIN);
            general.setBorderRight(BorderStyle.THIN);
            general.setBorderTop(BorderStyle.THIN);
            //general.setWrapText(true);
            general.setAlignment(HorizontalAlignment.LEFT);
            general.setFont(contentFont);

            for (Map.Entry<String, ExcelEntity<?>> entry : sources.entrySet()) {
                HSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(entry.getKey()));
                Field[] fields = entry.getValue().getClazz().getDeclaredFields();
                HSSFRow titleRow = sheet.createRow(0);

                for (int i = 0; i < fields.length; i++) {
                    HSSFCell titleCell = titleRow.createCell(i);
                    titleCell.setCellType(CellType.STRING);
                    FieldDoc doc = fields[i].getAnnotation(FieldDoc.class);
                    if (doc != null && StringUtils.isNotBlank(doc.value())) titleCell.setCellValue(doc.value());
                    else titleCell.setCellValue(fields[i].getName());
                    titleCell.setCellStyle(titleStyle);
                    sheet.autoSizeColumn(i);
                }
                for (Object t : entry.getValue().getData()) {
                    int i = entry.getValue().getData().indexOf(t);
                    HSSFRow content = sheet.createRow(i + 1);
                    for (int j = 0; j < fields.length; j++) {
                        HSSFCell cell = content.createCell(j);
                        cell.setCellStyle(general);
                        //修改修饰权限
                        fields[j].setAccessible(true);
                        Object object = fields[j].get(t);
                        if (object instanceof Timestamp || object instanceof java.util.Date) {
                            cell.setCellType(CellType.STRING);
                            FieldDateFormat date = fields[j].getAnnotation(FieldDateFormat.class);
                            String dateformat = "yyyy-MM-dd HH:mm:ss.fff";
                            if (date != null && StringUtils.isNotBlank(date.value()))
                                dateformat = date.value();
                            DateTimeFormatter format = DateTimeFormat.forPattern(dateformat);
                            String value = new DateTime(object).toString(format);
                            cell.setCellValue(value);
                        } else if (object instanceof Boolean) {
                            cell.setCellType(CellType.STRING);
                            if (object.equals(Boolean.TRUE)) cell.setCellValue("√");
                            else cell.setCellValue("×");
                        } else if (NumberUtils.isCreatable(object.toString())) {
                            cell.setCellType(CellType.NUMERIC);
                            cell.setCellValue(object.toString());
                        } else {
                            cell.setCellType(CellType.STRING);
                            cell.setCellValue(object.toString());
                        }
                        //设置列宽
                        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
                            int columnWidth = sheet.getColumnWidth(j) / 256;
                            int length = object.toString().getBytes().length;
                            if (columnWidth < length) sheet.setColumnWidth(j, length * 256);
                            if (columnWidth >= 100) sheet.setColumnWidth(j, 100 * 256);
                        }
                    }
                }
            }
            workbook.write(result);
        } finally {
            if (workbook != null)
                workbook.close();
        }
        return result;
    }
}