本文主要是介绍java Excel 大数据导出（百万级）100W/38秒，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
今天博主在研究Excel大数据导出性能，发现个意外惊喜，给大家分享下。
第一次博主使用的是POI Excel HSSF的导出方式：
这种方法是Excel 2003版本常用的一种导出方式。 以19.5W数据为例，导出耗时36秒
HSSFWorkbook workBook = new HSSFWorkbook();
另外需要注意一点Excel 2003一个页签只能放65535行数据、256列，所以我分3个页签导出，每个65000条。
第二次博主用线程池的方式导出：
友情链接：https://raising.iteye.com/blog/2414251
性能是改善挺多的，但是写起来麻烦。
1：你先要用线程池来管理线程和线程任务的分配与回收。
2：每个线程待处理的资源，在submit任务时，需保证每个任务不会互相重复。
3：在使用集合的时候还要考虑线程的安全性。
第三次博主使用的是POI Excel SXSSF的导出方式：
SXSSFWorkbook workBook = new SXSSFWorkbook();
注意 Excel 2007一个页签只能放1048576行数据、16384列，这次以100W为例。
发现导出时间为38秒，比HSSF方式快了太多了，而且代码写起来也容易，博主都被吓了一跳。
想要了解：HSSFWorkbook 与 SXSSFWorkbook 区别的
可以去POI官网查看：http://poi.apache.org/spreadsheet/index.html
好了废话不多说，上图上代码。
前台界面：
ExcelExport.java //导出控制入口类
ExcelExport2003.java //Excel 2003导出类
ExcelExport2007.java //Excel 2007导出类
ThreadExcelExport.java //Excel 线程池导出
/*** */
package com.kingdee.eas.custom.test.client;import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;import javax.swing.JFileChooser;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;import com.ibm.as400.util.commtrace.Data;
import com.kingdee.bos.ctrl.kdf.table.IRow;
import com.kingdee.bos.ctrl.kdf.table.KDTable;
import com.kingdee.bos.ctrl.swing.KDToolBar;
import com.kingdee.bos.ctrl.swing.KDWorkButton;
import com.kingdee.bos.ui.face.UIRuleUtil;
import com.kingdee.eas.cp.odm.web.ChooseRedHeadBean;
import com.kingdee.eas.util.client.EASResource;/*** @author tanrt* 2018.12.10* excel导出*/
public class ExcelExport {/* 常用组件：HSSFWorkbook excel的文档对象HSSFSheet excel的表单HSSFRow excel的行HSSFCell excel的格子单元HSSFFont excel字体HSSFDataFormat 日期格式HSSFHeader sheet头HSSFFooter sheet尾（只有打印的时候才能看到效果）样式：HSSFCellStyle cell样式辅助操作包括：HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表*//*** 调用案例* 在需要调用的界面 onload方法中添加* ExcelExport.getExcelExport(tblMain, toolBar);* *//*** @author gectan * 2018-12-11* 参数 tblMain 表编辑器名称* 参数 toolBar 工具栏* */public static void getExcelExport(final KDTable tblMain, KDToolBar toolBar){KDWorkButton btnExport = new KDWorkButton();btnExport.setIcon(EASResource.getIcon("imgTbtn_output"));btnExport.setToolTipText("自定义导出");btnExport.setText("自定义导出");toolBar.addRightComponent(btnExport, 20);btnExport.addActionListener(new ActionListener(){@Overridepublic void actionPerformed(ActionEvent e) {ExcelExport2003.getExcelExport(); //Excel 2003导出//ExcelExport2007.getExcelExport(); //Excel 2017导出//ThreadExcelExport.getExcelExport(); //Excel 多线程导出}});}}
/** * @Title: ThreadUtils.java * @Package com.kingdee.eas.custom.test.client * @author: 谈荣涛 * @date: 2018-12-18 下午03:51:36 * @version V1.0 * @Description: TODO(用一句话描述该文件做什么) */
package com.kingdee.eas.custom.test.client;import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.JFileChooser;import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;import com.kingdee.eas.util.SysUtil;
import com.kingdee.eas.util.client.MsgBox;/** * @ClassName: ThreadUtils * @author: 谈荣涛 * @date: 2018-12-18 下午03:51:36 * @Description:TODO(线程工具类)* */
public class ExcelExport2003 {/** * @Title: getExcelExport * @Description: TODO(excel方式导出) * @param: * @return: void * @throws */public static void getExcelExport() {Timestamp nowTimestamp = new Timestamp(new Date().getTime());System.err.println("-----------------------------2003 19.5W导数开始时间：------------------------\n" + nowTimestamp);//创建HSSFWorkbook对象(excel的文档对象) POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。HSSFWorkbook workBook = new HSSFWorkbook();//建立新的sheet对象（excel的表单）HSSFSheet sheet = workBook.createSheet("Excel 2003导出测试"); //创建Excel工作表（页签）int[] width = {5000,5000,5000,5000,5000,5000,5000,5000}; for(int i=0; i < width.length; i++){sheet.setColumnWidth(i, width [i]); //设置列宽}//excel列String[] head = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"}; HSSFRow title = sheet.createRow(0); //创建标题行title.createCell(0).setCellValue("Excel 2003导出测试"); //给标题行单元格赋值 //合并单元格 构造参数依次为起始行，截至行，起始列， 截至列sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));getTitleStyle(workBook, title); //创建并初始化标题样式InitExcelHead(workBook, sheet, head); //初始化抬头和样式setExcelValue(workBook, sheet, head); //excel内容赋值 不多线程的//***************这里为了测试方便直接新建页签***************//建立新的sheet对象（excel的表单）HSSFSheet sheet2 = workBook.createSheet("数据导出2"); //创建Excel工作表（页签）int[] width2 = {5000,5000,5000,5000,5000,5000,5000,5000}; for(int i=0; i < width2.length; i++){sheet2.setColumnWidth(i, width2 [i]); //设置列宽}//excel列String[] head2 = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"}; HSSFRow title2 = sheet2.createRow(0); //创建标题行title2.createCell(0).setCellValue("Excel 2003导出测试"); //给标题行单元格赋值 //合并单元格 构造参数依次为起始行，截至行，起始列， 截至列sheet2.addMergedRegion(new CellRangeAddress(0,0,0,7));getTitleStyle(workBook, title2); //创建并初始化标题样式InitExcelHead(workBook,sheet2, head2); //初始化抬头和样式setExcelValue(workBook, sheet2, head2); //excel内容赋值 不多线程的//建立新的sheet对象（excel的表单）HSSFSheet sheet3 = workBook.createSheet("数据导出3"); //创建Excel工作表（页签）int[] width3 = {5000,5000,5000,5000,5000,5000,5000,5000}; for(int i=0; i < width3.length; i++){sheet3.setColumnWidth(i, width3 [i]); //设置列宽}//excel列String[] head3 = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"}; HSSFRow title3 = sheet3.createRow(0); //创建标题行title3.createCell(0).setCellValue("Excel 2003导出测试"); //给标题行单元格赋值 //合并单元格 构造参数依次为起始行，截至行，起始列， 截至列sheet3.addMergedRegion(new CellRangeAddress(0,0,0,7));getTitleStyle(workBook, title3); //创建并初始化标题样式InitExcelHead(workBook,sheet3, head3); //初始化抬头和样式setExcelValue(workBook, sheet3, head3); //excel内容赋值 不多线程的//***************这里为了测试方便直接新建页签***************excelExport(workBook); //导出处理 Timestamp nowTimestamp1 = new Timestamp(new Date().getTime());System.err.println("-----------------------------2003 19.5W导数结束时间：------------------------\n" + nowTimestamp1);}/*** 背景：用多线程处理Excel大批量数据导出时。* * 返回每个线程的数据下标始末，限制最大线程数* @param size 数据的导出量* @param minSize 单个线程最小执行数量* @param maxTask 最大线程数* @return * @author: 谈荣涛 * @date: 2018-12-18 下午03:51:36 * * 例如传入：(150000,50000,5)* 返回结果 [0,50000,10000,150000]*/public static int[] getIndex(int size, int minSize, int maxTask) {int listIndexCount;double sizeDb = (double) size;double minSizeDb = (double) minSize; double maxTaskDb = (double) maxTask;if (sizeDb / minSizeDb < maxTaskDb) {listIndexCount = Double.valueOf(Math.ceil(sizeDb / minSizeDb)).intValue();} else {listIndexCount = maxTask;}int each = Double.valueOf(Math.floor(sizeDb / listIndexCount)).intValue();int[] indexs = new int[listIndexCount + 1];indexs[0] = 0;int totalCount = 0;for (int i = 1; i < listIndexCount; i++) {indexs[i] = indexs[i - 1] + each;totalCount += each;}// 最后一个线程可能多分担一点indexs[listIndexCount] = size - totalCount + indexs[listIndexCount - 1];return indexs;}/** * @Title: excelExport * @Description: TODO(excel导出类) * @param: @param sheet * @return: void * @throws */private static void excelExport(HSSFWorkbook workBook) {String filePath = getSavePath(); //获取文件保存路径if(filePath == null){SysUtil.abort(); //终止程序 }String srcFile = filePath + "\\Excel导出测试根目录\\Excel多线程导出.xls";FileOutputStream fileOut = null ;try {File file = new File(srcFile);if(file.exists()){ //当文件已存在时//删除原Excel 打开新导出的Excel时，最好刷新下当前文件夹，以免重复操作有时出现缓存。file.delete(); }fileOut = new FileOutputStream(file);workBook.write(fileOut);} catch (FileNotFoundException e) {e.printStackTrace();MsgBox.showError(e.getMessage());} catch (IOException e) {e.printStackTrace();MsgBox.showError(e.getMessage());} finally {try {fileOut.close();} catch (IOException e) {e.printStackTrace();}}}/** * @Title: getSavePath * @Description: TODO(获取文件保存路径) * @param: @return * @return: String * @throws */private static String getSavePath() {// 选择保存路径String selectPath = null;JFileChooser chooser = new JFileChooser();chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);//设置只能选择目录int returnVal = chooser.showOpenDialog(null);if(returnVal == JFileChooser.APPROVE_OPTION) {selectPath =chooser.getSelectedFile().getPath() ;}return selectPath;}/** * @Title: setExcelValue * @Description: TODO( excel正文内容的填充 ) * @param: @param sheet Excel页签对象名 * @return: void * @throws */private static void setExcelValue(HSSFWorkbook workBook, HSSFSheet sheet, String[] head) {StringBuffer buffer = new StringBuffer();for(int i=0; i<65000; i++){//sheet.createRow(i+2) 2003excel参数里面的类型是int，所以一次只能导出65535条数据HSSFRow row = sheet.createRow(i+2); for(int j=0; j < head.length; j++){buffer.append("数据行"+(i+1));buffer.append("列"+(j+1));row.createCell(j).setCellValue(buffer.toString());buffer.delete(0, buffer.length());}}}/** * @param head * @param fields * @Title: CreateExcelHead * @Description: TODO(初始化Excel表头) * @param: * @return: void * @throws */private static HSSFRow InitExcelHead(HSSFWorkbook workBook, HSSFSheet sheet, String[] head) {HSSFRow row = sheet.createRow(1);HSSFCellStyle style = getHeaderStyle(workBook); //获取表头样式for(int i=0; i<head.length; i++){row.createCell(i).setCellValue(head [i]); row.getCell(i).setCellStyle(style); //设置标题样式}return row;}/*** 获取Excel表头样式(第二行)* @param workbook* @return*/public static HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {HSSFCellStyle style = workbook.createCellStyle();style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //下边框style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //左边框 style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中style.setTopBorderColor(HSSFColor.BLACK.index); //上边框颜色style.setBottomBorderColor(HSSFColor.BLACK.index);style.setLeftBorderColor(HSSFColor.BLACK.index);style.setRightBorderColor(HSSFColor.BLACK.index);HSSFFont font = workbook.createFont(); // 创建字体样式font.setFontName("宋体");font.setFontHeightInPoints((short) 14); // 字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗style.setFont(font); //给样式指定字体return style;}/*** 标题样式* @param workbook* 创建并初始化标题样式* */public static void getTitleStyle(HSSFWorkbook workbook, HSSFRow title) {HSSFCellStyle style = workbook.createCellStyle(); // 创建样式style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中HSSFFont font = workbook.createFont(); // 创建字体样式font.setFontName("宋体"); // 字体font.setFontHeightInPoints((short) 16); // 字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗style.setFont(font); //给样式指定字体title.getCell(0).setCellStyle(style); //给标题设置样式}/*** 正文样式* @param workbook* @return*/public static HSSFCellStyle getContextStyle(HSSFWorkbook workbook) {HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_LEFT);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中return style;}/*** 筛选条件样式* @param workbook* @return*/public HSSFCellStyle getFilterStyle(HSSFWorkbook workbook) {HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中HSSFFont font = workbook.createFont();font.setFontName("宋体");font.setFontHeightInPoints((short) 12);style.setFont(font);return style;}}
/** * @Title: ExcelExport2007.java * @Package com.kingdee.eas.custom.test.client * @author: 谈荣涛 * @date: 2018-12-19 下午02:37:46 * @version V1.0 * @Description: TODO(用一句话描述该文件做什么) */
package com.kingdee.eas.custom.test.client;import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.Date;import javax.swing.JFileChooser;import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi4cp.ss.usermodel.CellStyle;
import org.apache.poi4cp.ss.usermodel.Font;
import org.apache.poi4cp.ss.usermodel.Row;
import org.apache.poi4cp.ss.usermodel.Sheet;
import org.apache.poi4cp.xssf.streaming.SXSSFRow;
import org.apache.poi4cp.xssf.streaming.SXSSFWorkbook;import com.kingdee.eas.util.SysUtil;
import com.kingdee.eas.util.client.MsgBox;/** * @ClassName: ExcelExport2007 * @author: 谈荣涛 * @date: 2018-12-19 下午02:37:46 * @Description:TODO(这里用一句话描述这个类的作用)* */
public class ExcelExport2007 {/** * @Title: getExcelExport * @Description: TODO(excel方式导出) * @param: * @return: void * @throws */public static void getExcelExport() {Timestamp nowTimestamp = new Timestamp(new Date().getTime());System.err.println("-----------------------------2007 100W导数开始时间：------------------------\n" + nowTimestamp);SXSSFWorkbook workBook = new SXSSFWorkbook();//创建HSSFWorkbook对象(excel的文档对象) POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。//XSSFWorkbook workBook = new XSSFWorkbook();//建立新的sheet对象（excel的表单）Sheet sheet = workBook.createSheet("Excel 2007导出"); //创建Excel工作表（页签）int[] width = {5000,5000,5000,5000,5000,5000,5000,5000}; for(int i=0; i < width.length; i++){sheet.setColumnWidth(i, width [i]); //设置列宽}//excel列String[] head = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"}; Row title = sheet.createRow(0); //创建标题行title.createCell(0).setCellValue("Excel 2007导出测试"); //给标题行单元格赋值 //合并单元格 构造参数依次为起始行，截至行，起始列， 截至列//sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));getTitleStyle(workBook, title); //创建并初始化标题样式InitExcelHead(workBook, sheet, head); //初始化抬头和样式setExcelValue(workBook, sheet, head); //excel内容赋值 excelExport(workBook); //导出处理 Timestamp nowTimestamp1 = new Timestamp(new Date().getTime());System.err.println("-----------------------------2007 100W导数结束时间：------------------------\n" + nowTimestamp1);}/*** 标题样式* @param workbook* 创建并初始化标题样式* */public static void getTitleStyle(SXSSFWorkbook workbook, Row title) {CellStyle style = workbook.createCellStyle(); // 创建样式style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中Font font = workbook.createFont(); // 创建字体样式font.setFontName("宋体"); // 字体font.setFontHeightInPoints((short) 16); // 字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗style.setFont(font); //给样式指定字体title.getCell(0).setCellStyle(style); //给标题设置样式}/** * @param head * @param fields * @Title: CreateExcelHead * @Description: TODO(初始化Excel表头) * @param: * @return: void * @throws */private static Row InitExcelHead(SXSSFWorkbook workBook, Sheet sheet, String[] head) {Row row = sheet.createRow(1);CellStyle style = getHeaderStyle(workBook); //获取表头样式for(int i=0; i<head.length; i++){row.createCell(i).setCellValue(head [i]); row.getCell(i).setCellStyle(style); //设置标题样式}return row;}/** * @Title: setExcelValue * @Description: TODO( excel正文内容的填充 ) * @param: @param sheet Excel页签对象名 * @return: void * @throws */private static void setExcelValue(SXSSFWorkbook workBook, Sheet sheet, String[] head) {StringBuffer buffer = new StringBuffer();for(int i=0; i<1000000; i++){//sheet.createRow(i+2) 2003excel参数里面的类型是int，所以一次只能导出65535条数据Row row = sheet.createRow(i+2); for(int j=0; j < head.length; j++){buffer.append("数据行"+(i+1));buffer.append("列"+(j+1));row.createCell(j).setCellValue(buffer.toString());buffer.delete(0, buffer.length());}}}/** * @Title: excelExport * @Description: TODO(excel导出类) * @param: @param sheet * @return: void * @throws */private static void excelExport(SXSSFWorkbook workBook) {String filePath = getSavePath(); //获取文件保存路径if(filePath == null){SysUtil.abort(); //终止程序 }String srcFile = filePath + "\\Excel导出测试根目录\\Excel多线程导出.xlsx";FileOutputStream fileOut = null ;try {File file = new File(srcFile);if(file.exists()){ //当文件已存在时//删除原Excel 打开新导出的Excel时，最好刷新下当前文件夹，以免重复操作有时出现缓存。file.delete(); }fileOut = new FileOutputStream(file);workBook.write(fileOut);} catch (FileNotFoundException e) {e.printStackTrace();MsgBox.showError(e.getMessage());} catch (IOException e) {e.printStackTrace();MsgBox.showError(e.getMessage());} finally {try {fileOut.close();} catch (IOException e) {e.printStackTrace();}}}/** * @Title: getSavePath * @Description: TODO(获取文件保存路径) * @param: @return * @return: String * @throws */private static String getSavePath() {// 选择保存路径String selectPath = null;JFileChooser chooser = new JFileChooser();chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);//设置只能选择目录int returnVal = chooser.showOpenDialog(null);if(returnVal == JFileChooser.APPROVE_OPTION) {selectPath =chooser.getSelectedFile().getPath() ;}return selectPath;}/*** 获取Excel表头样式(第二行)* @param workbook* @return*/public static CellStyle getHeaderStyle(SXSSFWorkbook workbook) {CellStyle style = workbook.createCellStyle();style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //下边框style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //左边框 style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中style.setTopBorderColor(HSSFColor.BLACK.index); //上边框颜色style.setBottomBorderColor(HSSFColor.BLACK.index);style.setLeftBorderColor(HSSFColor.BLACK.index);style.setRightBorderColor(HSSFColor.BLACK.index);Font font = workbook.createFont(); // 创建字体样式font.setFontName("宋体");font.setFontHeightInPoints((short) 14); // 字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗style.setFont(font); //给样式指定字体return style;}}
这篇关于java Excel 大数据导出（百万级）100W/38秒的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
