导出excel文件

依赖jar包

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

工具类


import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import lombok.extern.slf4j.Slf4j;

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

/**
 * 文件导出Excel 工具类
 *
 * @author olafwang
 */
@Slf4j
public class ExcelUtils {
    /**
     * 导出Excel
     *
     * @param fileName 文件名
     * @param headers  表头(表格的第一行)
     * @param contents 内容
     * @param response HttpServletResponse
     */
    public final static void exportExcel(String fileName, String[] headers, List<String[]> contents, HttpServletResponse response) {
        // 定义输出类型
        response.setContentType("application/msexcel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(response.getOutputStream());
            WritableSheet sheet = createSheet(workbook, "Sheet", 0);

            // 写表头
            if (headers != null && headers.length > 0) {
                WritableCellFormat headerFormat = getHeaderFormat();
                for (int i = 0; i < headers.length; i++) {
                    sheet.addCell(new Label(i, 0, headers[i], headerFormat));
                }
            }

            // 写数据
            if (contents != null && contents.size() > 0) {
                WritableCellFormat contentFormat = getContentFormat();
                for (int i = 0; i < contents.size(); i++) {
                    String[] content = contents.get(i);
                    if (content != null && content.length > 0) {
                        for (int j = 0; j < content.length; j++) {
                            sheet.addCell(new Label(j, (i + 1), content[j], contentFormat));
                        }
                    }
                }
            }
            workbook.write();
        } catch (Exception e) {
            log.error("导出Excel文件失败", e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error("关闭文件失败", e);
                }
            }
        }
    }

    /**
     * 创建数据页,并设置表格属性
     *
     * @param workbook
     * @return
     */
    private static WritableSheet createSheet(WritableWorkbook workbook, String name, int index) {
        WritableSheet sheet = workbook.createSheet(name, index);
        // 设置表格属性
        jxl.SheetSettings sheetset = sheet.getSettings();
        sheetset.setProtected(false);
        return sheet;
    }

    /**
     * 表头样式
     *
     * @return
     * @throws WriteException
     */
    private static WritableCellFormat getHeaderFormat() throws WriteException {
        WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        WritableCellFormat format = new WritableCellFormat(font);
        // 线条
        format.setBorder(Border.ALL, BorderLineStyle.THIN);
        // 文字垂直对齐
        format.setVerticalAlignment(VerticalAlignment.CENTRE);
        // 文字水平对齐
        format.setAlignment(Alignment.CENTRE);
        // 文字是否换行
        format.setWrap(false);
        return format;
    }

    /**
     * 数据样式
     *
     * @return
     * @throws WriteException
     */
    private static WritableCellFormat getContentFormat() throws WriteException {
        WritableFont font = new WritableFont(WritableFont.ARIAL, 10);
        WritableCellFormat format = new WritableCellFormat(font);
        // 线条
        format.setBorder(Border.NONE, BorderLineStyle.THIN);
        // 文字垂直对齐
        format.setVerticalAlignment(VerticalAlignment.CENTRE);
        // 文字水平对齐
        format.setAlignment(Alignment.LEFT);
        // 文字是否换行
        format.setWrap(false);
        return format;
    }

}

导出示例

@GetMapping("/")
public String helloWorld(HttpServletResponse response) {

    List<Person> list = new ArrayList<>();
    list.add(new Person(1L, "姓名1", 28, "地址1"));
    list.add(new Person(2L, "姓名2", 29, "地址2"));

    String[] headers = new String[]{"ID", "名称", "年龄", "地址"};
    List<String[]> contents = new ArrayList<>(list.size());
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    for (Person person : list) {
        int i = 0;
        String[] row = new String[headers.length];
        row[i++] = person.getId() + "";
        row[i++] = person.getName();
        row[i++] = person.getAge() + "";
        row[i++] = person.getAddress();
        contents.add(row);
    }

    ExcelUtils.exportExcel("PersonInfo", headers, contents, response);

    return "Hello World";
}

这种方式只适合导出小文件,如果是比较大的文件不适合实时导出,可以做成异步任务,将导出文件先上传cos,然后通过文件下载系统直接下载需要导出的文件。

源码地址

https://github.com/wyh-spring-ecosystem-student/spring-boot-student/tree/releases

spring-boot-student-export 工程

推荐阅读更多精彩内容