Java导出Excel工具类

依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

工具类

package com.cnooc.crm.web.customer.shanghai;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Excel工具类
 *
 * @author zhangdj
 * @date 2019/8/20
 */
public class ExcelUtil {

    /**
     * 导出数据到Excel
     *
     * @param response
     * @param headers       表头
     * @param fileName      文件名(不需要加后缀名)
     * @param list          要导出的数据
     * @param sheetName     工作簿名称
     * @param propertyNames 要写入的实例中的属性名
     * @param <T>           要写入数据的实例类型
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, String[] headers,
                                       String fileName, List<T> list, String sheetName, String[] propertyNames) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //第一行 表头
        HSSFRow heardersRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            heardersRow.createCell(i).setCellValue(headers[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            //从第二行开始写数据
            HSSFRow row = sheet.createRow(i + 1);
            T t = list.get(i);
            //得到T类型中的所有字段
            Field[] fields = t.getClass().getDeclaredFields();
            if (propertyNames != null && propertyNames.length != 0) {
                //遍历要写入Excel的属性值字段 如果与类中的属性字段匹配到了 就写值
                for (int j = 0; j < propertyNames.length; j++) {
                    for (int k = 0; k < fields.length; k++) {
                        Field field = fields[k];
                        if (!field.getName().equals(propertyNames[j])) {
                            continue;
                        }
                        //字段名称匹配 向单元格中写内容
                        setCellValue(t, field, row, j);
                    }
                }
            } else {
                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    setCellValue(t, field, row, j);
                }
            }
        }
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes(), "ISO8859-1") + ".xlsx");
        workbook.write(response.getOutputStream());
        if (workbook != null) {
            workbook.close();
        }
    }

    /**
     * 指定单元格数值
     *
     * @param t
     * @param field
     * @param row       单元格所在行
     * @param cellIndex 单元格所在列
     * @throws Exception
     */
    private static <T> void setCellValue(T t, Field field, HSSFRow row, int cellIndex) throws Exception {
        HSSFCell cell = row.createCell(cellIndex);
        Object propertyValue = getPropertyValue(t, field);
        if (propertyValue != null) {
            if (propertyValue instanceof Date) {
                String s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(propertyValue);
                cell.setCellValue(s);
            } else {
                cell.setCellValue(propertyValue.toString());
            }
        }
    }

    /**
     * 获取属性值
     *
     * @param t     实例
     * @param field 实例中的字段
     * @return
     * @throws Exception
     */
    private static <T> Object getPropertyValue(T t, Field field) throws Exception {
        // 获取Bean的某个属性的描述符
        PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), t.getClass());
        // 获得用于读取属性值的方法
        Method method = propertyDescriptor.getReadMethod();
        // 读取属性值
        return method.invoke(t);
    }
}

测试

package com.cnooc.crm.web.customer.shanghai;

import com.cnooc.crm.web.common.response.AppResponse;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author zhangdj
 * @date 2019/8/20
 */
@RestController
@Api(tags = {"Excel工具类下载测试"})
@RequestMapping("excelUtilTest")
@Slf4j
public class ExcelUtilTestController {

    @GetMapping("excelUtilTest")
    @ApiOperation(value = "导出Excel", notes = "返回Excel文件")
    public AppResponse downloadFailLog(HttpServletResponse response) {
        try {
            List<User> userList = new ArrayList<>();
            User user1 = new User(1L, "唐僧", "123", new Date(), "小西天", 10);
            User user2 = new User(2L, "孙悟空", "123", new Date(), "花果山", 10);
            User user3 = new User(3L, "猪八戒", "123", new Date(), "高老庄", 10);
            User user4 = new User(4L, "沙和尚", "123", new Date(), "流沙河", 10);
            User user5 = new User(5L, "白龙马", "123", new Date(), "小西天", 10);
            userList.add(user1);
            userList.add(user2);
            userList.add(user3);
            userList.add(user4);
            userList.add(user5);
            String[] headers = {"姓名", "出生日期", "年龄"};
            String fileName = "我的文档";
            String[] propertyNames = {"username", "birthDate", "age"};
            ExcelUtil.exportExcel(response, headers, fileName, userList, "工作簿", propertyNames);
            return AppResponse.ok("导出Excel成功");
        } catch (Exception e) {
            log.error(e.getMessage());
            return AppResponse.error("请求失败");
        }

    }
}

实体类

package com.cnooc.crm.web.customer.shanghai;

import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.Date;

/**
 * @author zhangdj
 * @date 2019/8/20
 */
@Data
@AllArgsConstructor
public class User {
    private Long id;
    private String username;
    private String password;
    private Date birthDate;
    private String address;
    private Integer age;
}

注：参考其他人的，原文链接找不到了