Java SpringBoot实现注解方式Excel导出

1. 添加依赖

<!-- excel工具 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.0</version>
</dependency>

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

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 ExcelAttribute {

    //列名
    String name() default "";

    //排序，实体类中从1开始排序，0被序号列占用
    int sort();

    //列宽
    int width() default 15;
}

 3. Excel工具类

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.mybatis.logging.Logger;
import org.mybatis.logging.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;

@Slf4j
public class ExcelUtils {

    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * @param list     导出的数据list
     * @param clazz    导出的对象class
     * @param fileName 导出的文件名称
     * @param response response
     * @param <T>
     */
    public static <T> void exportExcel(List<T> list, Class<T> clazz, String fileName, HttpServletResponse response) {
        SXSSFWorkbook workBook = getWorkBook(list, clazz);
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");  // .xlsx 用这个
            //response.setContentType("application/vnd.ms-excel;charset=UTF-8");  // .xls 用这个
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            workBook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
            workBook.close();
        } catch (Exception e) {
            //记录日志
            log.info(e.getMessage());
            //throw new BadRequestException(e.getMessage());
        }
    }

    private static <T> SXSSFWorkbook getWorkBook(List<T> list, Class<T> clazz) {
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        // 单元格样式
        CellStyle titleStyle = titleStyle(wb);// 表头样式
        CellStyle orderStyle = orderStyle(wb);// 序号样式
        CellStyle contentStyle = contentStyle(wb);// 普通单元格样式

        // 遍历集合数据，产生数据行
        Iterator<?> it = list.iterator();
        int index = 0;//数据条数（行数）
        int rowIndex = 0;// 每新建一个sheet此数值归零
        Sheet sheet = null;
        while (it.hasNext()) {
            if (index == 0 || index % 5000 == 0) {//此处是限制单个sheet数据量，每5000条数据新建一个sheet
                rowIndex = 0;
                //创建新的sheet
                sheet = wb.createSheet();
                Row row = sheet.createRow(0);
                row.setHeightInPoints(23);
                Field[] declaredFields = getAllFields(clazz);
                //添加序号
                Cell cellOrder = row.createCell(0);
                cellOrder.setCellValue("序号");
                cellOrder.setCellStyle(titleStyle);
                sheet.setColumnWidth(0, 10 * 256);
                //设置表头
                for (Field field : declaredFields) {
                    ExcelAttribute annotation =         field.getAnnotation(ExcelAttribute.class);
                    if (annotation != null) {
                        Cell cell = row.createCell(Integer.valueOf(annotation.sort()));
                        cell.setCellValue(annotation.name());
                        cell.setCellStyle(titleStyle);
                        //设置列的宽度
                        sheet.setColumnWidth(Integer.valueOf(annotation.sort()), (int) annotation.width() * 256);
                    }
                }
            }

            index++;
            rowIndex++;

            //设置序号值
            Row row = sheet.createRow(rowIndex);
            Cell cell = row.createCell(0);
            cell.setCellStyle(orderStyle);
            //cell.setCellValue(index);//新的sheet序号接着上个sheet的序号累加
            cell.setCellValue(rowIndex);//新的sheet序号从头开始累加
            row.setHeightInPoints(18);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = getAllFields(t.getClass());
            for (Field field : fields) {
                ExcelAttribute annotation = field.getAnnotation(ExcelAttribute.class);
                if (annotation != null) {
                    cell = row.createCell(Integer.valueOf(annotation.sort()));
                    cell.setCellStyle(contentStyle);
                    String fieldName = field.getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    try {
                        Method getMethod = t.getClass().getMethod(getMethodName, new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        cell.setCellValue(null == value ? "" : String.valueOf(value));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return wb;
    }

    //设置标题样式
    private static CellStyle titleStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = contentStyle(wb);
        //设置居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置单元格背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置单元格填充样式(使用纯色背景颜色填充)
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置字体加粗
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        //设置字体
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

    //设置单元格样式
    private static CellStyle contentStyle(SXSSFWorkbook wb) {
        //给单元格设置样式
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 设置字体
        font.setFontName("宋体");
        // 给字体设置样式
        cellStyle.setFont(font);
        // 字体设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    // 设置单元格样式
    private static CellStyle orderStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = contentStyle(wb);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle;
    }

    private static <T> Field[] getAllFields(Class<T> clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        Class superClass = clazz.getSuperclass();
        if (!superClass.equals(Object.class)) {
            Field[] superFields = superClass.getDeclaredFields();
            int sonLength = declaredFields.length;
            int superLength = superFields.length;
            // 合并两个数组
            Field[] newFields = new Field[sonLength + superLength];
            System.arraycopy(declaredFields, 0, newFields, 0, sonLength);
            System.arraycopy(superFields, 0, newFields, sonLength, superLength);
            return newFields;
        }
        return declaredFields;
    }

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 导入Excel
     */
    public static Workbook importExcel(MultipartFile file) throws IOException {
        //读取文件流
        InputStream is = file.getInputStream();
        //文件名
        String fileName = file.getOriginalFilename();
        //判断文件格式是否为excel
        boolean notNull = false;
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            //throw new BusinessException("上传文件格式不正确");
        }
        Workbook wb = null;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            //xlsx格式
            wb = new XSSFWorkbook(is);
        } else {
            //xls格式
            wb = new HSSFWorkbook(is);
        }
        return wb;
    }
}

 4. 实体类使用注解

import lombok.Data;
import java.io.Serializable;

@Data
public class UserInfoVo implements Serializable {

    @ExcelAttribute(name = "用户id", sort = 1, width = 20)
    private Long id; //用户id

    @ExcelAttribute(name = "昵称", sort = 2, width = 20)
    private String nickname; //昵称

    @ExcelAttribute(name = "生日", sort = 3, width = 20)
    private String birthday; //生日

    @ExcelAttribute(name = "性别", sort = 4, width = 20)
    private String gender; //性别

    @ExcelAttribute(name = "年龄", sort = 5, width = 20)
    private String age; //年龄

    @ExcelAttribute(name = "城市", sort = 6, width = 20)
    private String city; //城市

}

5. 调用工具类

import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
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.List;
import java.util.Map;

@RestController
@RequestMapping("/api")
public class ExcelController {

    /**
     * 导出
     */
    @PostMapping("/export")
    public void export(HttpServletResponse response, @RequestBody Map param) {
        //查询数据
        List<UserInfoVo> userInfoVoList = new ArrayList<>();//此处调用service查询需要导出的数据
        //导出
        ExcelUtils.exportExcel(userInfoVoList, UserInfoVo.class, "用户表", response);
    }
}

6.前端实现

// 导出excel
exportTb(){
	let params = JSON.parse(JSON.stringify(this.fmData));
	recordExport(params).then(res => {
		console.log(res)
		downloadFile(res, '记录', 'xlsx')
	})
}

// 下载文件
function downloadFile(obj, name, suffix) {
	/** 版本1 **/
	const url = window.URL.createObjectURL(new Blob([obj]))
	const link = document.createElement('a')
	link.style.display = 'none'
	link.href = url
	const fileName = parseTime(new Date()) + '-' + name + '.' + suffix
	link.setAttribute('download', fileName)
	document.body.appendChild(link)
	link.click()
	document.body.removeChild(link)
  
	/** 版本2 **/
	//let blob = new Blob([obj.data], {
	//	// 这里一定要和后端对应，不然可能出现乱码或者打不开文件
	//	type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
	//});
	//if (window.navigator.msSaveOrOpenBlob) {
	//	navigator.msSaveBlob(blob, fileName)
	//} else {
	//	const link = document.createElement('a');
	//	link.href = window.URL.createObjectURL(blob);
	//	link.download = name + '.' + suffix; // 在前端也可以设置文件名字
	//	link.click();
	//	//释放内存
	//	window.URL.revokeObjectURL(link.href)
	//}
}

// 记录列表导出
export function recordExport(params){
	return request({
		url: '/xxx/export',
		method: 'post',
		data: params,
		responseType: 'blob'//这个很重要，表示二进制类型，不加这个下载了打不开
	})
}