简单EXCEL列表导出

5656.png

本例子，简单介绍excel导出功能，基于HttpServletResponse，网络文件下载。
依赖jar

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

工具类：

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.UUID;

/**
 * Created on 2021/8/6.
 *
 * @author bruce
 */
@Slf4j
public abstract class AbstractExcelExportInterface {
    void write(HttpServletResponse response, Workbook workbook) {
        String fileName = UUID.randomUUID().toString() + ".xls";
        pwrite(response, workbook, fileName);
    }

    void write(HttpServletResponse response, Workbook workbook, String fileName) {
        if (StringUtils.isEmpty(fileName)) {
            fileName = UUID.randomUUID().toString() + ".xls";
        }
        pwrite(response, workbook, fileName);
    }

    protected void write(HttpServletResponse response, List<List<String>> lists, String fileName) {
        if (StringUtils.isEmpty(fileName)) {
            fileName = UUID.randomUUID().toString() + ".xls";
        }
        SXSSFWorkbook workbook = new SXSSFWorkbook(lists.size());
        SXSSFSheet sheet = workbook.createSheet(fileName.substring(0, fileName.indexOf(".xls")));
        Integer rowIndex = 0;
        SXSSFRow row = null;
        SXSSFCell cell = null;
        for (List<String> rowData : lists) {
            Integer columnIndex = 0;
            row = sheet.createRow(rowIndex++);
            for (String columnVal : rowData) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(columnVal);
            }
        }
        pwrite(response, workbook, fileName);
    }

    private void pwrite(HttpServletResponse response, Workbook workbook, String fileName) {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        try {
            response.addHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            fileName = UUID.randomUUID().toString() + ".xls";
            response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
        }
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.warn("下载excel异常", e);
        }
    }
}

用法

子类继承上述工具类接口，调用父类方法实现数据写出
write(response, lists, "测试.xls");