Java web 导出Excel文件

导出Excel文件

Java web导出Excel文件就是将Excel文件流写入HttpServletResponse对象的OutputStream中，通常的做法分两步：

1. 创建Excel文件
2. 将Excel文件流写入OutputStream中；

用法快速预览：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;

/**
 * @author 张玉尧（matrix.zhang@ximalaya.com）
 * @version $Id: ExcelDemoController.java, v 0.1 2019年2月19日 下午2:31:01 张玉尧（matrix.zhang@ximalaya.com） Exp $
 */
@RestController
public class ExcelDemoController {

    @GetMapping("exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {

        // 创建Excel文件
        Workbook workbook = new HSSFWorkbook();

        // 创建Sheet
        Sheet sheet = workbook.createSheet();

        // 创建行
        Row row = sheet.createRow(0);// 从0开始计数

        // 创建单元格
        Cell cell = row.createCell(0);// 从0开始计数

        cell.setCellValue("单元格内容");// 填充单元格

        // 设置MIME类型
        response.setHeader("content-type", "application/vbn.ms-excel");
        // 设置返回内容以附近的方式下载，并且指定文件名
        // java.net.URLEncoder
        response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("测试.xls", "utf-8"));

        // 将Excel内存对象写入OutputStream
        workbook.write(response.getOutputStream());

        // 刷新缓存
        response.getOutputStream().flush();
    }
}

需要在项目中引入Apache POI依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>

创建Excel文件

这一步要做的就是拿到数据源，然后将数据源填入创建的Workbook对象中，Workbook对象是对Excel文件的封装，Sheet是对Excel文件sheet的封装，Cell是对Excel单元格的封装；

文件流写入OutputStream中

至少需要设置两个头部属性：Content-Type指定响应内容的MIME类型，Content-Disposition指定响应内容的展示形式；

完整的MIME列表查询地址

数据源和Excel文件剥离后的工具类

1. 封装的ExcelModel类

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Collections;
import java.util.List;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ExcelModel {
    @Builder.Default
    private String fileName = "数据导出";
    
    @Builder.Default
    private List<SheetModel> sheets = Collections.emptyList();
}

2. 封装的SheetModel类

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Collections;
import java.util.List;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class SheetModel {
    @Builder.Default
    private String sheetName = "sheet1";

    @Builder.Default
    private List<String> headers = Collections.emptyList();

    @Builder.Default
    private List<List<String>> rows = Collections.emptyList();
}

3. 基于ExcelModel、SheetModel的导出工具类，通过静态方法export导出Excel文件

import com.google.common.base.Preconditions;
import com.ximalaya.openapi.analysis.app.model.common.ExcelModel;
import com.ximalaya.openapi.analysis.app.model.common.SheetModel;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collections;
import java.util.List;

public abstract class ExcelUtils {

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

    /**
     * 生成文件名
     */
    private static String generateFileName(String fileName) throws UnsupportedEncodingException {

        // 为文件名添加时间信息
        fileName = fileName + "_" + DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now()) + ".xls";

        // 编码字符串，使之支持中文
        return URLEncoder.encode(fileName, "utf-8");
    }

    /**
     * 导出文件
     */
    public static void export(HttpServletResponse response, SheetModel sheetModel, String fileName) throws IOException {

        Preconditions.checkNotNull(sheetModel, "sheetModel cannot be empty");
        Preconditions.checkNotNull(fileName, "fileName cannot be null");
        Preconditions.checkArgument(CollectionUtils.isNotEmpty(sheetModel.getHeaders()), "sheet header cannot be empty");

        ExcelModel excelModel = ExcelModel.builder()
                .sheets(Collections.singletonList(sheetModel))
                .fileName(fileName)
                .build();

        export(response, excelModel);
    }

    /**
     * 导出文件
     */
    public static void export(HttpServletResponse response, ExcelModel excel) throws IOException {

        Preconditions.checkNotNull(excel, "excel cannot be empty");
        Preconditions.checkNotNull(excel.getFileName(), "fileName cannot be null");
        Preconditions.checkArgument(CollectionUtils.isNotEmpty(excel.getSheets()), "sheets cannot be empty");

        String fileName = excel.getFileName();

        // 创建Excel文件对象
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 表头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        HSSFFont headerFont = workbook.createFont();
//        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        List<SheetModel> sheets = excel.getSheets();

        for (SheetModel sheetModel : sheets) {

            Preconditions.checkArgument(CollectionUtils.isNotEmpty(sheetModel.getHeaders()), "sheet header cannot be empty");

            HSSFSheet sheet = workbook.createSheet(sheetModel.getSheetName());

            int index = 0;

            // 填充表头
            fillRowAndStyle(sheet.createRow(index++), sheetModel.getHeaders(), headerStyle);

            // 填充表格数据
            for (List<String> row : sheetModel.getRows()) {
                if (row.size() != sheetModel.getHeaders().size()) LOG.error("====> 表头的字段数与行数据字段数和不匹配");
                fillRow(sheet.createRow(index++), row);
            }
        }

        // 导出
        response.setContentType("application/vbn.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + generateFileName(fileName));

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.write(byteArrayOutputStream);

        response.setHeader("Content-Length", byteArrayOutputStream.size() + "");

        OutputStream outputStream = response.getOutputStream();
        byteArrayOutputStream.writeTo(outputStream);
        byteArrayOutputStream.close();
        outputStream.flush();
    }

    /**
     * 填充数据
     */
    private static void fillRow(HSSFRow row, List<String> fields) {
        for (int i = 0; i < fields.size(); i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(fields.get(i));
            cell.setCellValue(text);
        }
    }

    /**
     * 填充数据并加样式 (一行的所有cell都用同一样式)
     */
    private static void fillRowAndStyle(HSSFRow row, List<String> fields, HSSFCellStyle cellStyle) {
        for (int i = 0; i < fields.size(); i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(fields.get(i));
            cell.setCellValue(text);
            cell.setCellStyle(cellStyle);
        }
    }
}