根据最近写出的导出方法 打算在文章中记录总结下学习心得
java导出我准备分为三篇文章介绍
分批查询导出篇/weixin_56567361/article/details/126647979异步多线程导出篇/weixin_56567361/article/details/126718950?spm=1001..3001.5501
Java导出excel文件 我分为了xls,xlsx,csv三个文件格式
首先介绍下三种文件格式的优劣
导出少量数据 需要表头样式的(例如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);// 设置单元类型为Stringcell.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.queryParamsthis.$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 = filenameaLink.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) {ment = var1;}public char getComment() {return ment;}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);}//默认falseboolean 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) == ment || 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) == ment) {if (var1.length() > 1) {var1 = "\\" + ment + var1.substring(1);} else {var1 = "\\" + ment;}}}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(ment);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 = filenameaLink.href = URL.createObjectURL(blob)aLink.click()URL.revokeObjectURL(blob)}
js文件
//导出export function exportData(query){return request({url:'/地址/地址/export',method:'get',params:query,responseType: 'blob'})}
到此三种导出方法已经介绍完成 大家根据需求调整代码
有遇到什么问题可以留言告诉我哦 欢迎评论区讨论🤪
