百萬(wàn)級(jí)別的數(shù)據(jù)導(dǎo)出成Excel解決方案(JAVA)

最近做開(kāi)發(fā)時(shí)遇到一個(gè)需求誉碴，導(dǎo)出百萬(wàn)級(jí)別的數(shù)據(jù)到excel文件中。但是用傳統(tǒng)的poi方式瓣距，查尋數(shù)據(jù)庫(kù)然后poi工具寫(xiě)入excel一直內(nèi)存溢出的錯(cuò)誤黔帕，無(wú)奈中找到兩種解決方案一種是寫(xiě)出多個(gè)excel文件打包成zip給用戶(hù)，這種速度還是不快旨涝，要求多線程分頁(yè)操作數(shù)據(jù)庫(kù)蹬屹，比較麻煩侣背。另外一種是利用官方提供的一種基于XML的方案白华。此文介紹這種方案的代碼實(shí)現(xiàn)慨默，筆者在本地抽成了一個(gè)工具類(lèi)，如下弧腥。

其實(shí)對(duì)于一個(gè)Excel文件來(lái)說(shuō)厦取，最核心的是它的數(shù)據(jù)。Excel文件中的數(shù)據(jù)和樣式文件是分開(kāi)存儲(chǔ)的管搪，它們都對(duì)應(yīng)于它自己體系中的一個(gè)XML文件虾攻。有興趣的朋友可以把Excel文件的后綴名改成“.zip”，然后用壓縮文件把它解壓縮更鲁，可以看到它里面的結(jié)構(gòu)是由一堆的XML文件組成的霎箍。如果我們把解壓縮后的文件再壓縮成一個(gè)壓縮文件，并把它的后綴名改為Excel文件對(duì)應(yīng)的后綴名“.xlsx”或“.xls”澡为，然后再用Excel程序把它打開(kāi)漂坏。這個(gè)時(shí)候你會(huì)發(fā)現(xiàn)它也是可以打開(kāi)的。筆者本文所要講述的基于大量的數(shù)據(jù)生成Excel的方案就是基于這種XML文件的方案媒至，它依賴(lài)于一個(gè)現(xiàn)有的Excel文件（這個(gè)Excel文件可以在運(yùn)行時(shí)生成好）顶别，然后把我們的數(shù)據(jù)生成對(duì)應(yīng)的XML表示，再把我們的XML替換原來(lái)的XML文件拒啰，再進(jìn)行打包后就變成了一個(gè)Excel文件了驯绎。基于這種方式谋旦，筆者做了一個(gè)測(cè)試剩失，生成了一個(gè)擁有3500萬(wàn)行，5列的Excel文件册着，該文件大小為1GB拴孤，耗時(shí)412秒。這種效率比起我們應(yīng)用傳統(tǒng)的API來(lái)說(shuō)是指數(shù)倍的指蚜。

細(xì)節(jié)的實(shí)現(xiàn)詳情乞巧，請(qǐng)讀者自己參考以下示例代碼，該示例代碼是筆者從Apache官方下載的摊鸡，原地址是https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java绽媒。需要注意的是生成的XML中需要應(yīng)用到的樣式需要事先生成，需要應(yīng)用函數(shù)免猾、合并單元格等邏輯的時(shí)候是辕，可以先拿一個(gè)Excel文件應(yīng)用對(duì)應(yīng)的函數(shù)、合并邏輯猎提，再把它解壓縮后查看里面的XML文件的展現(xiàn)形式获三，然后自己拼接的時(shí)候也拼接成對(duì)應(yīng)的形式，這樣自己生成的Excel文件也會(huì)有對(duì)應(yīng)的效果。

import org.apache.poi.hssf.util.CellReference;

import org.apache.poi.openxml4j.opc.internal.ZipHelper;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFDataFormat;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.util.StringUtils;

import java.io.*;

import java.util.Calendar;

import java.util.Enumeration;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.zip.ZipEntry;

import java.util.zip.ZipFile;

import java.util.zip.ZipOutputStream;

/**

* @author wangwei （yuhui@shanshu.ai)

* @date 2018/07/20

*/

public class ExcelExportUtil {

private ExcelExportUtil() {}

private static final StringXML_ENCODING ="UTF-8";

? ? /**

*

? ? * @param sheetName EXCEL中的表名

? ? * @param absolutePath 實(shí)際服務(wù)器路徑

? ? * @param titles 表頭

? ? * @param data 表數(shù)據(jù)

? ? * @param 數(shù)據(jù)泛型

? ? * @throws Exception

*/

? ? public static void exportExcel(String sheetName, String path,String absolutePath, String[] titles, List data)

throws Exception {

// Step 1. Create a template file. Setup sheets and workbook-level objects such as

// cell styles, number formats, etc.

? ? ? ? XSSFWorkbook wb =new XSSFWorkbook();

? ? ? ? XSSFSheet sheet = wb.createSheet(sheetName);

? ? ? ? Map styles =createStyles(wb);

? ? ? ? //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml

? ? ? ? String sheetRef = sheet.getPackagePart().getPartName().getName();

? ? ? ? //save the template

? ? ? ? FileOutputStream os =new FileOutputStream(path);

? ? ? ? wb.write(os);

? ? ? ? os.close();

? ? ? ? //Step 2. Generate XML file.

? ? ? ? File tmp = File.createTempFile("sheet", ".xml");

? ? ? ? Writer fw =new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);

? ? ? ? writeDate(fw, styles, titles, data);

? ? ? ? fw.close();

? ? ? ? //Step 3. Substitute the template entry with the generated data

? ? ? ? FileOutputStream out =new FileOutputStream(absolutePath);

? ? ? ? //用心拼接生成的XML文件,替換原來(lái)模板Excel文件中對(duì)應(yīng)的XML文件疙教，再壓縮打包為一個(gè)Excel文件棺聊。

? ? ? ? substitute(new File(path), tmp, sheetRef.substring(1), out);

? ? ? ? out.close();

? ? ? ? wb.close();

? ? }

/**

* 支持的Cell樣式

*

? ? * @param wb

? ? *

? ? * @return

? ? */

? ? private static MapcreateStyles(XSSFWorkbook wb) {

Map styles =new HashMap<>();

? ? ? ? XSSFDataFormat fmt = wb.createDataFormat();

? ? ? ? XSSFCellStyle style1 = wb.createCellStyle();

? ? ? ? style1.setAlignment(HorizontalAlignment.RIGHT);

? ? ? ? style1.setDataFormat(fmt.getFormat("0.0%"));

? ? ? ? styles.put("percent", style1);

? ? ? ? XSSFCellStyle style2 = wb.createCellStyle();

? ? ? ? style2.setAlignment(HorizontalAlignment.CENTER);

? ? ? ? style2.setDataFormat(fmt.getFormat("0.0X"));

? ? ? ? styles.put("coeff", style2);

? ? ? ? XSSFCellStyle style3 = wb.createCellStyle();

? ? ? ? style3.setAlignment(HorizontalAlignment.RIGHT);

? ? ? ? style3.setDataFormat(fmt.getFormat("$#,##0.00"));

? ? ? ? styles.put("currency", style3);

? ? ? ? XSSFCellStyle style4 = wb.createCellStyle();

? ? ? ? style4.setAlignment(HorizontalAlignment.RIGHT);

? ? ? ? style4.setDataFormat(fmt.getFormat("mmm dd"));

? ? ? ? styles.put("date", style4);

? ? ? ? XSSFCellStyle style5 = wb.createCellStyle();

? ? ? ? XSSFFont headerFont = wb.createFont();

? ? ? ? headerFont.setBold(true);

? ? ? ? style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

? ? ? ? style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);

? ? ? ? style5.setFont(headerFont);

? ? ? ? styles.put("header", style5);

? ? ? ? return styles;

? ? }

/**

* 寫(xiě)出Excel Title

*

? ? * @param sw

? ? * @param styles

? ? * @param titles

? ? */

? ? private static void writeTitle(SpreadsheetWriter sw, Map styles, String[] titles) {

//insert header row

? ? ? ? try {

if (titles !=null && titles.length >0) {

sw.insertRow(0);

? ? ? ? ? ? ? ? int styleIndex = styles.get("header").getIndex();

? ? ? ? ? ? ? ? int index =0;

? ? ? ? ? ? ? ? for (String title : titles) {

sw.createCell(index, title, styleIndex);

? ? ? ? ? ? ? ? ? ? index++;

? ? ? ? ? ? ? ? }

sw.endRow();

? ? ? ? ? ? }

}catch (IOException e) {

e.printStackTrace();

? ? ? ? }

}

/**

* 寫(xiě)出Excel data

*

? ? * @param out

? ? * @param styles

? ? * @param titles

? ? * @param data

? ? * @param

? ? *

? ? * @throws Exception

*/

? ? private static void writeDate(Writer out, Map styles, String[] titles, List data)

throws Exception {

SpreadsheetWriter sw =new SpreadsheetWriter(out);

? ? ? ? sw.beginSheet();

? ? ? ? int length =0;

? ? ? ? if (titles !=null) {

writeTitle(sw, styles, titles);

? ? ? ? ? ? length = titles.length;

? ? ? ? }

//write data rows

? ? ? ? int rownum =1;

? ? ? ? if (data !=null && data.size() >0) {

for (T obj : data) {

String[] items = obj.toString().split(";");

? ? ? ? ? ? ? ? for (int i =0; i < length; i++) {

if (i == length) {

break;

? ? ? ? ? ? ? ? ? ? }

sw.insertRow(rownum);

? ? ? ? ? ? ? ? ? ? String str = items[i];

? ? ? ? ? ? ? ? ? ? if (!StringUtils.isEmpty(str)) {

sw.createCell(i, items[i]);

? ? ? ? ? ? ? ? ? ? }else {

sw.createCell(i, "");

? ? ? ? ? ? ? ? ? ? }

sw.endRow();

? ? ? ? ? ? ? ? }

rownum++;

? ? ? ? ? ? }

}

sw.endSheet();

? ? }

/**

? ? * @param zipfile the template file

? ? * @param tmpfile the XML file with the sheet data

? ? * @param entry? the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml

? ? * @param out? ? the stream to write the result to

*/

? ? private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out)throws IOException {

ZipFile zip = ZipHelper.openZipFile(zipfile);

? ? ? ? try {

ZipOutputStream zos =new ZipOutputStream(out);

? ? ? ? ? ? Enumeration en = zip.entries();

? ? ? ? ? ? while (en.hasMoreElements()) {

ZipEntry ze = en.nextElement();

? ? ? ? ? ? ? ? if (!ze.getName().equals(entry)) {

zos.putNextEntry(new ZipEntry(ze.getName()));

? ? ? ? ? ? ? ? ? ? InputStream is = zip.getInputStream(ze);

? ? ? ? ? ? ? ? ? ? copyStream(is, zos);

? ? ? ? ? ? ? ? ? ? is.close();

? ? ? ? ? ? ? ? }

}

zos.putNextEntry(new ZipEntry(entry));

? ? ? ? ? ? InputStream is =new FileInputStream(tmpfile);

? ? ? ? ? ? copyStream(is, zos);

? ? ? ? ? ? is.close();

? ? ? ? ? ? zos.close();

? ? ? ? }finally {

zip.close();

? ? ? ? }

}

private static void copyStream(InputStream in, OutputStream out)throws IOException {

byte[] chunk =new byte[1024];

? ? ? ? int count;

? ? ? ? while ((count = in.read(chunk)) >=0) {

out.write(chunk, 0, count);

? ? ? ? }

}

/**

* Writes spreadsheet data in a Writer.

* (YK: in future it may evolve in a full-featured API for streaming data in Excel)

*/

? ? public static class SpreadsheetWriter {

private final Writer_out;

? ? ? ? private int _rownum;

? ? ? ? public SpreadsheetWriter(Writer out) {

_out = out;

? ? ? ? }

public void beginSheet()throws IOException {

_out.write(""

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? +"

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? +".org/spreadsheetml/2006/main\">");

? ? ? ? ? ? _out.write("\n");

? ? ? ? }

public void endSheet()throws IOException {

_out.write("");

? ? ? ? ? ? _out.write("");

? ? ? ? }

/**

* Insert a new row

*

? ? ? ? * @param rownum 0-based row number

*/

? ? ? ? public void insertRow(int rownum)throws IOException {

_out.write("\n");

? ? ? ? ? ? this._rownum = rownum;

? ? ? ? }

/**

* Insert row end marker

*/

? ? ? ? public void endRow()throws IOException {

_out.write("\n");

? ? ? ? }

public void createCell(int columnIndex, String value, int styleIndex)throws IOException {

String ref =new CellReference(_rownum, columnIndex).formatAsString();

? ? ? ? ? ? _out.write("

? ? ? ? ? ? if (styleIndex != -1) {

_out.write(" s=\"" + styleIndex +"\"");

? ? ? ? ? ? }

_out.write(">");

? ? ? ? ? ? _out.write("" + value +"");

? ? ? ? ? ? _out.write("");

? ? ? ? }

public void createCell(int columnIndex, String value)throws IOException {

createCell(columnIndex, value, -1);

? ? ? ? }

public void createCell(int columnIndex, double value, int styleIndex)throws IOException {

String ref =new CellReference(_rownum, columnIndex).formatAsString();

? ? ? ? ? ? _out.write("

? ? ? ? ? ? if (styleIndex != -1) {

_out.write(" s=\"" + styleIndex +"\"");

? ? ? ? ? ? }

_out.write(">");

? ? ? ? ? ? _out.write("" + value +"");

? ? ? ? ? ? _out.write("");

? ? ? ? }

public void createCell(int columnIndex, double value)throws IOException {

createCell(columnIndex, value, -1);

? ? ? ? }

public void createCell(int columnIndex, Calendar value, int styleIndex)throws IOException {

createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);

? ? ? ? }

}

}

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市贞谓，隨后出現(xiàn)的幾起案子限佩，更是在濱河造成了極大的恐慌，老刑警劉巖裸弦，帶你破解...
沈念sama閱讀 218,204評(píng)論 6贊 506
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件祟同，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡理疙，警方通過(guò)查閱死者的電腦和手機(jī)晕城，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,091評(píng)論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)窖贤，“玉大人砖顷，你說(shuō)我怎么就攤上這事≈髦” “怎么了择吊？”我有些...
開(kāi)封第一講書(shū)人閱讀 164,548評(píng)論 0贊 354
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵，是天一觀的道長(zhǎng)槽奕。經(jīng)常有香客問(wèn)我几睛，道長(zhǎng)，這世上最難降的妖魔是什么粤攒？我笑而不...
開(kāi)封第一講書(shū)人閱讀 58,657評(píng)論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任所森，我火速辦了婚禮，結(jié)果婚禮上夯接，老公的妹妹穿的比我還像新娘焕济。我一直安慰自己，他們只是感情好盔几，可當(dāng)我...
茶點(diǎn)故事閱讀 67,689評(píng)論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布晴弃。她就那樣靜靜地躺著，像睡著了一般逊拍。火紅的嫁衣襯著肌膚如雪上鞠。梳的紋絲不亂的頭發(fā)上，一...
開(kāi)封第一講書(shū)人閱讀 51,554評(píng)論 1贊 305
城市分裂傳說(shuō)
那天芯丧，我揣著相機(jī)與錄音芍阎，去河邊找鬼。笑死缨恒，一個(gè)胖子當(dāng)著我的面吹牛谴咸，可吹牛的內(nèi)容都是我干的轮听。我是一名探鬼主播，決...
沈念sama閱讀 40,302評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼岭佳，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼血巍！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起驼唱，我...
開(kāi)封第一講書(shū)人閱讀 39,216評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤藻茂，失蹤者是張志新（化名）和其女友劉穎驹暑，沒(méi)想到半個(gè)月后玫恳，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,661評(píng)論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡优俘，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,851評(píng)論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年京办，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片帆焕。...
茶點(diǎn)故事閱讀 39,977評(píng)論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡惭婿，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出叶雹，到底是詐尸還是另有隱情财饥，我是刑警寧澤，帶...
沈念sama閱讀 35,697評(píng)論 5贊 347
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布折晦，位于F島的核電站钥星，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏满着。R本人自食惡果不足惜谦炒，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,306評(píng)論 3贊 330
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望风喇。院中可真熱鬧宁改，春花似錦、人聲如沸魂莫。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 31,898評(píng)論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)耙考。三九已至谜喊，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間琳骡，已是汗流浹背锅论。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 33,019評(píng)論 1贊 270
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留楣号，地道東北人最易。一個(gè)月前我還...
沈念sama閱讀 48,138評(píng)論 3贊 370
代替公主和親
正文我出身青樓怒坯，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親藻懒。傳聞我的和親對(duì)象是個(gè)殘疾皇子剔猿，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,927評(píng)論 2贊 355

百萬(wàn)級(jí)別的數(shù)據(jù)導(dǎo)出成Excel解決方案(JAVA)

推薦閱讀更多精彩內(nèi)容