SpringBoot之导出Excel工具类

1，添加poi依赖

<properties>
    <org.apache.poi.ooxml.version>3.13</org.apache.poi.ooxml.version>
</properties>
 
<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>${org.apache.poi.ooxml.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>${org.apache.poi.ooxml.version}</version>
    </dependency>
</dependencies>

2，自定义表格注解

@Target({ ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {

    String value() default "";

    int col() default 0;

    int width() default 0;
}

3，ExcelUtils编写

package com.zyjournals.web.sysadmin.config.excel;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * @Description:
 * @Auther: sp
 * @Date: 2020/8/26 14:23
 */
public class ExcelUtil {

    /**
     * 用户信息导出类
     * @param response 响应
     * @param fileName 文件名
     * @param dataList 导出的数据
     */
    public static <T> void uploadExcelAboutUser(HttpServletResponse response,String fileName,List<T> dataList, Class<T> cls){
        //声明输出流
        OutputStream os = null;
        //设置响应头
        setResponseHeader(response,fileName);
        try {
            Field[] fields = cls.getDeclaredFields();
            List<Field> fieldList = Arrays.stream(fields)
                    .filter(field -> {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        if (annotation != null && annotation.col() > 0) {
                            field.setAccessible(true);
                            return true;
                        }
                        return false;
                    }).sorted(Comparator.comparing(field -> {
                        int col = 0;
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        if (annotation != null) {
                            col = annotation.col();
                        }
                        return col;
                    })).collect(Collectors.toList());

            //获取输出流
            os = response.getOutputStream();
            //内存中保留1000条数据，以免内存溢出，其余写入硬盘
            //SXSSFWorkbook wb = new SXSSFWorkbook(1000);
            HSSFWorkbook wb = new HSSFWorkbook();
            //获取该工作区的第一个sheet
            Sheet sheet1 = wb.createSheet("sheet1");

            CellStyle cellStyle = wb.createCellStyle();
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            Font font = wb.createFont();
            cellStyle.setFont(font);

            for (int i = 0; i < dataList.size(); i++) {
                AtomicInteger ai = new AtomicInteger();
                {
                    Row row = sheet1.createRow(ai.getAndIncrement());
                    AtomicInteger aj = new AtomicInteger();
                    //写入头部
                    fieldList.forEach(field -> {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        String columnName = "";
                        if (annotation != null) {
                            columnName = annotation.value();
                        }
                        Cell cell = row.createCell(aj.getAndIncrement());

                        //CellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

                        //Font font = wb.createFont();
                        cellStyle.setFont(font);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(columnName);
                    });
                }
                //设置内容行
                if (!CollectionUtils.isEmpty(dataList)) {
                    dataList.forEach(t -> {
                        Row row1 = sheet1.createRow(ai.getAndIncrement());
                        AtomicInteger aj = new AtomicInteger();
                        fieldList.forEach(field -> {
                            //Class<?> type = field.getType();
                            Object value = "";
                            try {
                                //value = field.get(t);
                                value = t.getClass().getMethod("get" + initStr(field.getName())).invoke(t);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            Cell cell = row1.createCell(aj.getAndIncrement());
                            cell.setCellStyle(cellStyle);
                            if (value != null) {
                                cell.setCellValue(value.toString());
                            }
                        });
                    });
                }
                for (int x = 0; x < fieldList.size(); x++) {
                    sheet1.autoSizeColumn(x);
                    sheet1.setColumnWidth(x,sheet1.getColumnWidth(x)*17/10);
                }
            }
            //将整理好的excel数据写入流中
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭输出流
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
        设置浏览器下载响应头
     */
    private static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private static String initStr(String old){  // 将单词的首字母大写
        String str = old.substring(0,1).toUpperCase() + old.substring(1) ;
        return str ;
    }

}

4，定义导出实体类,需要返回特殊格式的，重写getter方法

package com.zyjournals.web.sysadmin.module.account.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.zyjournals.web.sysadmin.config.excel.ExcelColumn;
import lombok.Data;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * t_purchase
 * @author Administrator
 * @date 2020-08-14 16:27:12
 */
@Data
public class ExportPurchaseListVO {

    /**
     * 报刊名称
     */
    @ExcelColumn(value = "报刊名称", col = 1)
    private String journalName;

    /**
     * 文章名称
     */
    @ExcelColumn(value = "文章名称", col = 2)
    private String articleName;

    /**
     * DOI号
     */
    @ExcelColumn(value = "DOI号", col = 3)
    private String doi;

    /**
     * 购买金额
     */
    @ExcelColumn(value = "购买金额（元）", col = 4)
    private BigDecimal amountOfPurchase;

    /**
     * 创建时间
     */
    @JsonFormat(locale ="zh", timezone ="GMT+8", pattern ="yyyy-MM-dd HH:mm:ss")
    @ExcelColumn(value = "购买具体时间", col = 5)
    private Date createdTime;

    public String getCreatedTime() {
        if(createdTime!=null){
            return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(createdTime);
        }
        return "";
    }
}

5，调用

@GetMapping(value = "/purchase/exportPurchaseById")
    public void exportPurchaseById(@RequestParam(value = "id")Long id,
            @RequestParam(value = "pageNo",defaultValue = "1",required = false) Integer pageNo,
            @RequestParam(value = "pageSize",defaultValue = "10",required = false)Integer pageSize, HttpServletResponse response){
        PurchaseDTO purchaseDTO = new PurchaseDTO();
        purchaseDTO.setId(id);
        purchaseDTO.setPageNo(pageNo);
        purchaseDTO.setPageSize(pageSize);
        JsonListResult<Purchase> purchaseJsonListResult = this.queryByList(purchaseDTO);
        List<ExportPurchaseListVO> listVOS = MyBeanUtils.transform(purchaseJsonListResult.getItems(), ExportPurchaseListVO.class);
        ExcelUtil.uploadExcelAboutUser(response,"购买明细.xlsx",listVOS,ExportPurchaseListVO.class);
    }