Java导出大批量数据(文件格式篇xls,xlsx,csv)

根据最近写出的导出方法 打算在文章中记录总结下学习心得

java导出我准备分为三篇文章介绍
分批查询导出篇https://blog.csdn.net/weixin_56567361/article/details/126647979异步多线程导出篇https://blog.csdn.net/weixin_56567361/article/details/126718950?spm=1001.2014.3001.5501

Java导出excel文件 我分为了xls,xlsx,csv三个文件格式

首先介绍下三种文件格式的优劣

xls文件	xlsx文件	csv文件
最大65536行 256列	最大1048576行 16384列	纯文本文件 无行数上线 但无法编辑表头,表内容样式
占用空间大 占用空间小	运算速度相比xls快些	占用空间小 导出后打开很迅速
		wps,,excel,均可打开(最多显示一千万条数据,推荐notepad等其他文本编辑软件打开)

导出少量数据 需要表头样式的(例如mysql中基础数据) 采用xlsx文件导出

导出大量数据 无表头样式要求(例如clickhouse中历史数据) 采用csv文件导出

目录

xlxs文件导出

csv文件导出

---

xlxs文件导出

导出效果如图 可自定义多级表头 格式颜色等 这里展示基础的单表头导出

首先pom加上 以下导出匀以poi4.1.2版本测试

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

xls导出则把方法中出现的XSSF改为HSSF

建议使用SXSSFWorkbook

从POI 3.8版本开始 提供了一种基于XSSF的低内存占用的SXSSF方式 对于大型excel文件的创建 一个关键问题就是 要确保不会内存溢出 其实 就算生成很小的excel（比如几Mb）它用掉的内存是远大于excel文件实际的size的 如果单元格还有各种格式（比如，加粗，背景标红之类的）那它占用的内存就更多了 对于大型excel的创建且不会内存溢出的 就只有SXSSFWorkbook了 它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）

controller层

    @GetMapping("/export")
    public void export(HttpServletResponse response, 对象 对象名) throws Exception {
        List<对象> list = Service.getList(对象名);
        //表头
        String[] title = new String[]{"测试1", "测试2","测试3"};
        List<String[]> rows = new ArrayList<>();
        String[] row = null;
        //表数据
        for (对象 名: list) {
            row = new String[]{名.getTest1(),名.getTest2(),名.getTest3()};
            rows.add(row);
        }
        //xlsx文件导出
        SXSSFWorkbook workbook = PoiUtils.exportExcelSXSSF(title, rows);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("导出表.xlsx", "utf-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        //处理工作表在磁盘上产生的临时文件
        workbook.dispose();
    }

PoiUtils

/**
     * 低占用内存xlsx文件导出
     *
     * @param title
     * @param rows
     * @return
     */
    public static SXSSFWorkbook exportExcelSXSSF(String[] title, List<String[]> rows) {
        //这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        // 生成一个表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置表头白字黑底居中
        Font font = workbook.createFont();
        //设置字体颜色
        font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        font.setFontName("宋体");
        //设置表头边框
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(BorderStyle.HAIR);
        headerStyle.setBorderLeft(BorderStyle.HAIR);
        headerStyle.setBorderRight(BorderStyle.HAIR);
        headerStyle.setBorderTop(BorderStyle.HAIR);
        // 创建一个居中格式
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //五十度灰
        headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFont(font);
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 10);
        //设置表内容边框
        CellStyle bodyStyle = workbook.createCellStyle();
        bodyStyle.setBorderBottom(BorderStyle.HAIR);
        bodyStyle.setBorderLeft(BorderStyle.HAIR);
        bodyStyle.setBorderRight(BorderStyle.HAIR);
        bodyStyle.setBorderTop(BorderStyle.HAIR);
        // 创建一个居中格式
        bodyStyle.setAlignment(HorizontalAlignment.CENTER);
        bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        bodyStyle.setFont(contentFont);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 18);
        sheet.setColumnWidth(title.length - 1, (int) ((40 + 0.72) * 256));
        // 循环字段名数组，创建标题行
        SXSSFRow row = sheet.createRow(0);
        for (int j = 0; j < title.length; j++) {
            // 创建列
            SXSSFCell cell = row.createCell(j);
            // 设置单元类型为String
            cell.setCellType(CellType.STRING);
            cell.setCellValue(title[j]);
            cell.setCellStyle(headerStyle);
        }
        for (int i = 0; i < rows.size(); i++) {
            // 因为第一行已经用于创建标题行，故从第二行开始创建
            row = sheet.createRow(i + 1);
            // 如果是第一行就让其为标题行
            String[] rowData = rows.get(i);
            //每一行的数据
            recyclingCellSXSSF(rowData, row, bodyStyle);
        }
        return workbook;
    }

    private static void recyclingCellSXSSF(String[] rowData, SXSSFRow row, CellStyle bodyStyle) {
        for (int j = 0; j < rowData.length; j++) {
            // 创建列
            SXSSFCell cell = row.createCell(j);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(rowData[j]);
            cell.setCellStyle(bodyStyle);
        }
    }

不需要表头样式可注掉

前端我使用的若依框架 这里也展示下代码

index.vue

     /** 导出按钮操作 */
      handleExport() {
        const queryParams = this.queryParams
        this.$confirm('是否确认导出所有信息?', '警告', {
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type: 'warning'
        })
          .then(() => {
            this.exportDataFan(queryParams)
          })
      },
      async exportDataFan(params) {
        try {
          const res = await exportChineseParsing(params)
          this.downLoad('导出表.xlsx', res)
        } catch (e) {
          console.log(e)
        }
      },
      downLoad(filename, content) {
        //filename 文件名，content 下载的内容
        var aLink = document.createElement('a')
        var blob = new Blob([content], {
          type: 'application/x-xls'
        })
        var evt = new Event('click')
        aLink.download = filename
        aLink.href = URL.createObjectURL(blob)
        aLink.click()
        URL.revokeObjectURL(blob)
      },

js文件

//导出
export function export(query) {
  return request({
    url: '/路径名/路径名/export',
    method: 'get',
    params: query,
    responseType: 'blob'
    //设置响应数据类型为 blob。这句话很重要！！！
  })
}

csv文件导出

controller层(导出参数也是需要表头,表数据 只用替换下面导出方法)

    @GetMapping("/export")
    public void export(HttpServletResponse response, 对象 对象名) throws Exception {
        List<对象> list = Service.getList(对象名);
        //表头
        String[] title = new String[]{"测试1", "测试2","测试3"};
        List<String[]> rows = new ArrayList<>();
        String[] row = null;
        //表数据
        for (对象 名: list) {
            row = new String[]{名.getTest1(),名.getTest2(),名.getTest3()};
            rows.add(row);
        }
        //创建临时csv文件
        File tempFile = PoiUtils.createTempFile(rows, title);
        //输出csv流文件，提供给浏览器下载
        PoiUtils.outCsvStream(response, tempFile);
        //删除临时文件
        PoiUtils.deleteFile(tempFile);
    }            

PoiUtils

    /**
     * 创建临时的csv文件
     *
     * @return
     * @throws IOException
     */
    public static File createTempFile(List<String[]> datas, String[] headers) throws IOException {
        File tempFile = File.createTempFile("vehicle", ".csv");
        CsvWriter csvWriter = new CsvWriter(tempFile.getCanonicalPath(), ',', StandardCharsets.UTF_8);
        // 写表头
        csvWriter.writeRecord(headers);
        for (String[] data : datas) {
            //这里如果数据不是String类型，请进行转换
            for (String datum : data) {
                csvWriter.write(datum, true);
            }
            csvWriter.endRecord();
        }
        csvWriter.close();
        return tempFile;
    }

    /**
     * 普通csv文件传浏览器
     *
     * @param response
     * @param tempFile
     * @throws IOException
     */
    public static void outCsvStream(HttpServletResponse response, File tempFile) throws IOException {
        java.io.OutputStream out = response.getOutputStream();
        byte[] b = new byte[10240];
        java.io.File fileLoad = new java.io.File(tempFile.getCanonicalPath());
        response.reset();
        response.setContentType("application/csv");
        response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode("export.csv", "UTF-8"));
        java.io.FileInputStream in = new java.io.FileInputStream(fileLoad);
        int n;
        //为了保证excel打开csv不出现中文乱码
        out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
        while ((n = in.read(b)) != -1) {
            //每次写入out1024字节
            out.write(b, 0, n);
        }
        in.close();
        out.close();
    }


    /**
     * 删除文件
     *
     * @param file
     * @return
     */
    public static boolean deleteFile(File file) {
        // 如果文件路径所对应的文件存在，并且是一个文件，则直接删除
        if (file.exists() && file.isFile()) {
            return file.delete();
        } else {
            return false;
        }
    }

CsvWriter(代码略长)

package com.hnxr.scada.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.Writer;
import java.nio.charset.Charset;

public class CsvWriter {
    private PrintWriter outputStream;
    private String fileName;
    private boolean firstColumn;
    private boolean useCustomRecordDelimiter;
    private Charset charset;
    private UserSettings userSettings;
    private boolean initialized;
    private boolean closed;
    public static final int ESCAPE_MODE_DOUBLED = 1;
    public static final int ESCAPE_MODE_BACKSLASH = 2;

    public CsvWriter(String var1, char var2, Charset var3) {
        this.outputStream = null;
        this.fileName = null;
        this.firstColumn = true;
        this.useCustomRecordDelimiter = false;
        this.charset = null;
        this.userSettings = new UserSettings();
        this.initialized = false;
        this.closed = false;
        if (var1 == null) {
            throw new IllegalArgumentException("Parameter fileName can not be null.");
        } else if (var3 == null) {
            throw new IllegalArgumentException("Parameter charset can not be null.");
        } else {
            this.fileName = var1;
            this.userSettings.Delimiter = var2;
            this.charset = var3;
        }
    }

    public CsvWriter(String var1) {
        this(var1, ',', Charset.forName("ISO-8859-1"));
    }

    public CsvWriter(Writer var1, char var2) {
        this.outputStream = null;
        this.fileName = null;
        this.firstColumn = true;
        this.useCustomRecordDelimiter = false;
        this.charset = null;
        this.userSettings = new UserSettings();
        this.initialized = false;
        this.closed = false;
        if (var1 == null) {
            throw new IllegalArgumentException("Parameter outputStream can not be null.");
        } else {
            this.outputStream = new PrintWriter(var1);
            this.userSettings.Delimiter = var2;
            this.initialized = true;
        }
    }

    public CsvWriter(OutputStream var1, char var2, Charset var3) {
        this(new OutputStreamWriter(var1, var3), var2);
    }

    public char getDelimiter() {
        return this.userSettings.Delimiter;
    }

    public void setDelimiter(char var1) {
        this.userSettings.Delimiter = var1;
    }

    public char getRecordDelimiter() {
        return this.userSettings.RecordDelimiter;
    }

    public void setRecordDelimiter(char var1) {
        this.useCustomRecordDelimiter = true;
        this.userSettings.RecordDelimiter = var1;
    }

    public char getTextQualifier() {
        return this.userSettings.TextQualifier;
    }

    public void setTextQualifier(char var1) {
        this.userSettings.TextQualifier = var1;
    }

    public boolean getUseTextQualifier() {
        return this.userSettings.UseTextQualifier;
    }

    public void setUseTextQualifier(boolean var1) {
        this.userSettings.UseTextQualifier = var1;
    }

    public int getEscapeMode() {
        return this.userSettings.EscapeMode;
    }

    public void setEscapeMode(int var1) {
        this.userSettings.EscapeMode = var1;
    }

    public void setComment(char var1) {
        this.userSettings.Comment = var1;
    }

    public char getComment() {
        return this.userSettings.Comment;
    }

    public boolean getForceQualifier() {
        return this.userSettings.ForceQualifier;
    }

    public void setForceQualifier(boolean var1) {
        this.userSettings.ForceQualifier = var1;
    }

    public void write(String var1, boolean var2) throws IOException {
        this.checkClosed();
        this.checkInit();
        if (var1 == null) {
            var1 = "";
        }

        if (!this.firstColumn) {
            this.outputStream.write(this.userSettings.Delimiter);
        }
        //默认false
        boolean var3 = this.userSettings.ForceQualifier;
        if (!var2 && var1.length() > 0) {
            var1 = var1.trim();
        }

        if (!var3 && this.userSettings.UseTextQualifier && (var1.indexOf(this.userSettings.TextQualifier) > -1 || var1.indexOf(this.userSettings.Delimiter) > -1 || !this.useCustomRecordDelimiter && (var1.indexOf(10) > -1 || var1.indexOf(13) > -1) || this.useCustomRecordDelimiter && var1.indexOf(this.userSettings.RecordDelimiter) > -1 || this.firstColumn && var1.length() > 0 && var1.charAt(0) == this.userSettings.Comment || this.firstColumn && var1.length() == 0)) {
            var3 = true;
        }

        if (this.userSettings.UseTextQualifier && !var3 && var1.length() > 0 && var2) {
            char var4 = var1.charAt(0);
            if (var4 == ' ' || var4 == '\t') {
                var3 = true;
            }

            if (!var3 && var1.length() > 1) {
                char var5 = var1.charAt(var1.length() - 1);
                if (var5 == ' ' || var5 == '\t') {
                    var3 = true;
                }
            }
        }

        if (var3) {
            this.outputStream.write(this.userSettings.TextQualifier);
            if (this.userSettings.EscapeMode == 2) {
                var1 = replace(var1, "\\", "\\\\");
                var1 = replace(var1, "" + this.userSettings.TextQualifier, "\\" + this.userSettings.TextQualifier);
            } else {
                var1 = replace(var1, "" + this.userSettings.TextQualifier, "" + this.userSettings.TextQualifier + this.userSettings.TextQualifier);
            }
        } else if (this.userSettings.EscapeMode == 2) {
            var1 = replace(var1, "\\", "\\\\");
            var1 = replace(var1, "" + this.userSettings.Delimiter, "\\" + this.userSettings.Delimiter);
            if (this.useCustomRecordDelimiter) {
                var1 = replace(var1, "" + this.userSettings.RecordDelimiter, "\\" + this.userSettings.RecordDelimiter);
            } else {
                var1 = replace(var1, "\r", "\\\r");
                var1 = replace(var1, "\n", "\\\n");
            }

            if (this.firstColumn && var1.length() > 0 && var1.charAt(0) == this.userSettings.Comment) {
                if (var1.length() > 1) {
                    var1 = "\\" + this.userSettings.Comment + var1.substring(1);
                } else {
                    var1 = "\\" + this.userSettings.Comment;
                }
            }
        }

        this.outputStream.write(var1);
        if (var3) {
            this.outputStream.write(this.userSettings.TextQualifier);
        }

        this.firstColumn = false;
    }

    public void write(String var1) throws IOException {
        this.write(var1, false);
    }

    public void writeComment(String var1) throws IOException {
        this.checkClosed();
        this.checkInit();
        this.outputStream.write(this.userSettings.Comment);
        this.outputStream.write(var1);
        if (this.useCustomRecordDelimiter) {
            this.outputStream.write(this.userSettings.RecordDelimiter);
        } else {
            this.outputStream.println();
        }

        this.firstColumn = true;
    }

    public void writeRecord(String[] var1, boolean var2) throws IOException {
        if (var1 != null && var1.length > 0) {
            for(int var3 = 0; var3 < var1.length; ++var3) {
                this.write(var1[var3], var2);
            }

            this.endRecord();
        }

    }

    public void writeRecord(String[] var1) throws IOException {
        this.writeRecord(var1, false);
    }

    public void endRecord() throws IOException {
        this.checkClosed();
        this.checkInit();
        if (this.useCustomRecordDelimiter) {
            this.outputStream.write(this.userSettings.RecordDelimiter);
        } else {
            this.outputStream.println();
        }

        this.firstColumn = true;
    }

    private void checkInit() throws IOException {
        if (!this.initialized) {
            if (this.fileName != null) {
                this.outputStream = new PrintWriter(new OutputStreamWriter(new FileOutputStream(this.fileName), this.charset));
            }

            this.initialized = true;
        }

    }

    public void flush() {
        this.outputStream.flush();
    }

    public void close() {
        if (!this.closed) {
            this.close(true);
            this.closed = true;
        }

    }

    private void close(boolean var1) {
        if (!this.closed) {
            if (var1) {
                this.charset = null;
            }

            try {
                if (this.initialized) {
                    this.outputStream.close();
                }
            } catch (Exception var3) {
            }

            this.outputStream = null;
            this.closed = true;
        }

    }

    private void checkClosed() throws IOException {
        if (this.closed) {
            throw new IOException("This instance of the CsvWriter class has already been closed.");
        }
    }

    protected void finalize() {
        this.close(false);
    }

    public static String replace(String var0, String var1, String var2) {
        int var3 = var1.length();
        int var4 = var0.indexOf(var1);
        if (var4 <= -1) {
            return var0;
        } else {
            StringBuffer var5 = new StringBuffer();

            int var6;
            for(var6 = 0; var4 != -1; var4 = var0.indexOf(var1, var6)) {
                var5.append(var0.substring(var6, var4));
                var5.append(var2);
                var6 = var4 + var3;
            }

            var5.append(var0.substring(var6));
            return var5.toString();
        }
    }

    public class UserSettings {
        public char TextQualifier = '"';
        public boolean UseTextQualifier = true;
        public char Delimiter = ',';
        public char RecordDelimiter = 0;
        public char Comment = '#';
        public int EscapeMode = 1;
        public boolean ForceQualifier = false;

        public UserSettings() {
        }
    }

    private class Letters {
        public static final char LF = '\n';
        public static final char CR = '\r';
        public static final char QUOTE = '"';
        public static final char COMMA = ',';
        public static final char SPACE = ' ';
        public static final char TAB = '\t';
        public static final char POUND = '#';
        public static final char BACKSLASH = '\\';
        public static final char NULL = '\u0000';

        private Letters() {
        }
    }
}

前端代码

index.vue

    //导出
    exportFile() {
      let params = Object.assign({}, this.form)
      this.$confirm('是否确认导出文件?', '警告', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        this.exportDataFan(params)
      })
    },

    async exportDataFan(params) {
      try {
        const res = await exportData(params)
        this.downLoad('导出文件.csv', res)
      } catch (e) {
        console.log(e)
      }
    },

    downLoad(filename, content) {
      //filename 文件名，content 下载的内容
      console.log(filename)
      console.log(content)
      var aLink = document.createElement('a')
      var blob = new Blob([content], {
        // type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' //文件类型
        type: 'application/x-xls'
        //type: 'text/csv,charset=UTF-8'
      })
      console.log(blob)
      var evt = new Event('click')
      aLink.download = filename
      aLink.href = URL.createObjectURL(blob)
      aLink.click()
      URL.revokeObjectURL(blob)
    }

js文件

//导出
export function exportData(query){
  return request({
    url:'/地址/地址/export',
    method:'get',
    params:query,
    responseType: 'blob'
  })
}

---

到此三种导出方法已经介绍完成 大家根据需求调整代码

有遇到什么问题可以留言告诉我哦 欢迎评论区讨论🤪