17.05.18 利用POI導出百萬級別的Excel文件

上一篇文章 [ 12.12利用poi進行Excel的導出和導入 ]，記錄了如何用POI導出Excel文件心赶，但有一個缺陷规肴，因為導出的是.xls格式的文件镊折，當數(shù)據(jù)量大于65536行的時候焕盟，就會出錯秋秤。所以現(xiàn)在更改為導出xlsx文件，最大可支持1048576行脚翘。

2018.02.04

注：新增提供項目demo灼卢，一鍵使用；并提供生成的樣板文件查看下載
https://pan.baidu.com/s/1c2uCQy8

2017.11.28
注：提供所需JAR包及工具類下載来农，調用案列下面有詳細描述
https://pan.baidu.com/s/1c2uCQy8

  • Maven依賴(2018.02.04新增)

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-collections4</artifactId>
  <version>4.1</version>
</dependency>

<dependency>
  <groupId>org.apache.xmlbeans</groupId>
  <artifactId>xmlbeans</artifactId>
  <version>2.3.0</version>
</dependency>

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.16</version>
</dependency>

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

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

  • 工具類ExcelUtils(頂部提供下載)

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    //默認列寬度
    private final int DEFAULT_COLUMN_SIZE = 30;
    //刷新寫入硬盤數(shù)據(jù)閥值
    private final int flushRows = 1000;
    //聲明一個模板工作薄(寫入流式數(shù)據(jù))
    private Workbook writeDataWorkBook;
    //樣式列表
    private Map<String, CellStyle> cellStyleMap;
    //Excel當前數(shù)據(jù)行數(shù)(將要寫入數(shù)據(jù)的索引數(shù))
    private int currentRowNum = 0;
    //數(shù)據(jù)輸出流
    private OutputStream outputStream;

    /**
     * 斷言Excel文件寫入之前的條件
     *
     * @param directory 目錄
     * @param fileName  文件名
     * @return file
     * @throws IOException
     */
    private File assertFile(String directory, String fileName) throws IOException {
        File tmpFile = new File(directory + File.separator + fileName + ".xlsx");
        if (tmpFile.exists()) {
            if (tmpFile.isDirectory()) {
                throw new IOException("File '" + tmpFile + "' exists but is a directory");
            }
            if (!tmpFile.canWrite()) {
                throw new IOException("File '" + tmpFile + "' cannot be written to");
            }
        } else {
            File parent = tmpFile.getParentFile();
            if (parent != null) {
                if (!parent.mkdirs() && !parent.isDirectory()) {
                    throw new IOException("Directory '" + parent + "' could not be created");
                }
            }
        }
        return tmpFile;
    }

    /**
     * 日期轉化為字符串,格式為yyyy-MM-dd HH:mm:ss
     */
    private String getCnDate(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(date);
    }

    /**
     * Excel 導出鞋真，POI實現(xiàn)，先寫入Excel標題沃于，與writeExcelData配合使用
     * 先使用writeExcelTitle再使用writeExcelData
     *
     * @param directory   目錄
     * @param fileName    文件名
     * @param sheetName   sheetName
     * @param columnNames 列名集合
     * @param sheetTitle  表格標題
     */
    public void writeExcelTitle(String directory, String fileName, String sheetName, List<String> columnNames,
                                String sheetTitle) throws Exception {
        File tmpFile = assertFile(directory, fileName);
        exportExcelTitle(tmpFile, sheetName, columnNames, sheetTitle);
        loadTplWorkbook(tmpFile);
    }

    /**
     * Excel 導出涩咖，POI實現(xiàn)赶袄，寫入Excel數(shù)據(jù)行列，與writeExcelTitle配合使用
     * 先使用writeExcelTitle再使用writeExcelData
     *
     * @param directory 目錄
     * @param fileName  文件名
     * @param sheetName sheetName
     * @param objects   數(shù)據(jù)信息
     */
    public void writeExcelData(String directory, String fileName, String sheetName, List<List<Object>> objects)
            throws Exception, IOException {
        File tmpFile = assertFile(directory, fileName);
        outputStream = new FileOutputStream(tmpFile);
        exportExcelData(sheetName, objects);
    }

    /**
     * 釋放資源
     */
    public void dispose() throws Exception {
        try {
            if (writeDataWorkBook != null) {
                writeDataWorkBook.write(outputStream);
            }
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
            if (cellStyleMap != null) {
                cellStyleMap.clear();
            }
            cellStyleMap = null;
            outputStream = null;
            writeDataWorkBook = null;
        } catch (IOException e) {
            throw new Exception(e);
        }
    }

    /**
     * 導出字符串數(shù)據(jù)
     *
     * @param file        文件名
     * @param columnNames 表頭
     * @param sheetTitle  sheet頁Title
     */
    private void exportExcelTitle(File file, String sheetName, List<String> columnNames,
                                  String sheetTitle) throws Exception {
        Workbook tplWorkBook = new XSSFWorkbook();
        Map<String, CellStyle> cellStyleMap = styleMap(tplWorkBook);
        // 表頭樣式
        CellStyle headStyle = cellStyleMap.get("head");
        // 生成一個表格
        Sheet sheet = tplWorkBook.getSheet(sheetName);
        if (sheet == null) {
            sheet = tplWorkBook.createSheet(sheetName);
        }
        //最新Excel列索引,從0開始
        //int lastRowIndex = sheet.getLastRowNum();
        // 設置表格默認列寬度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 合并單元格
        sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, 0, columnNames.size() - 1));
        // 產生表格標題行
        Row rowMerged = sheet.createRow(currentRowNum);
        Cell mergedCell = rowMerged.createCell(0);
        mergedCell.setCellStyle(headStyle);
        mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
        //寫入成功一行數(shù)據(jù)遞增行數(shù)
        currentRowNum = currentRowNum + 1;
        // 產生表格表頭列標題行
        Row row = sheet.createRow(currentRowNum);
        for (int i = 0; i < columnNames.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            RichTextString text = new XSSFRichTextString(columnNames.get(i));
            cell.setCellValue(text);
        }
        //寫入成功一行數(shù)據(jù)遞增行數(shù)
        currentRowNum = currentRowNum + 1;
        try {
            OutputStream ops = new FileOutputStream(file);
            tplWorkBook.write(ops);
            ops.flush();
            ops.close();
        } catch (IOException e) {
            throw new Exception(e);
        }
    }

    /**
     * 加載模板文件
     */
    private void loadTplWorkbook(File file) throws Exception {
        try {
            XSSFWorkbook tplWorkBook = new XSSFWorkbook(new FileInputStream(file));
            writeDataWorkBook = new SXSSFWorkbook(tplWorkBook, flushRows);
            cellStyleMap = styleMap(writeDataWorkBook);
        } catch (IOException e) {
            throw new Exception("Excel模板文件不存在");
        }
    }

    /**
     * 導出字符串數(shù)據(jù)
     *
     * @param objects 目標數(shù)據(jù)
     */
    private void exportExcelData(String sheetName, List<List<Object>> objects) throws Exception, IOException {
        // 正文樣式
        CellStyle contentStyle = cellStyleMap.get("content");
        //正文整數(shù)樣式
        CellStyle contentIntegerStyle = cellStyleMap.get("integer");
        //正文帶小數(shù)整數(shù)樣式
        CellStyle contentDoubleStyle = cellStyleMap.get("double");
        // 生成一個表格
        Sheet sheet = writeDataWorkBook.getSheet(sheetName);
        if (sheet == null) {
            throw new Exception("讀取Excel模板錯誤");
        }
        // 設置表格默認列寬度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 遍歷集合數(shù)據(jù),產生數(shù)據(jù)行,前兩行為標題行與表頭行
        for (List<Object> dataRow : objects) {
            Row row = sheet.createRow(currentRowNum);
            for (int j = 0; j < dataRow.size(); j++) {
                Cell contentCell = row.createCell(j);
                Object dataObject = dataRow.get(j);
                if (dataObject != null) {
                    if (dataObject instanceof Integer) {
                        contentCell.setCellStyle(contentIntegerStyle);
                        contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
                    } else if (dataObject instanceof Double) {
                        contentCell.setCellStyle(contentDoubleStyle);
                        contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
                    } else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
                        contentCell.setCellStyle(contentStyle);
                        contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
                    } else if (dataObject instanceof Date) {
                        contentCell.setCellStyle(contentStyle);
                        contentCell.setCellValue(getCnDate((Date) dataObject));
                    } else {
                        contentCell.setCellStyle(contentStyle);
                        contentCell.setCellValue(dataObject.toString());
                    }
                } else {
                    contentCell.setCellStyle(contentStyle);
                    // 設置單元格內容為字符型
                    contentCell.setCellValue("");
                }
            }
            //寫入成功一行數(shù)據(jù)遞增行數(shù)
            currentRowNum = currentRowNum + 1;
        }
        try {
            ((SXSSFSheet) sheet).flushRows(flushRows);
        } catch (IOException e) {
            throw new Exception(e);
        }
    }

    /**
     * 創(chuàng)建單元格表頭樣式
     *
     * @param workbook 工作薄
     */
    private CellStyle createCellHeadStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 設置邊框樣式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //設置對齊樣式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字體
        Font font = workbook.createFont();
        // 表頭樣式
        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 把字體應用到當前的樣式
        style.setFont(font);
        return style;
    }

    /**
     * 創(chuàng)建單元格正文樣式
     *
     * @param workbook 工作薄
     */
    private CellStyle createCellContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 設置邊框樣式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //設置對齊樣式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字體
        Font font = workbook.createFont();
        // 正文樣式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字體應用到當前的樣式
        style.setFont(font);
        return style;
    }

    /**
     * 單元格樣式(Integer)列表
     */
    private CellStyle createCellContent4IntegerStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 設置邊框樣式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //設置對齊樣式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字體
        Font font = workbook.createFont();
        // 正文樣式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字體應用到當前的樣式
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//數(shù)據(jù)格式只顯示整數(shù)
        return style;
    }

    /**
     * 單元格樣式(Double)列表
     */
    private CellStyle createCellContent4DoubleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 設置邊框樣式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //設置對齊樣式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字體
        Font font = workbook.createFont();
        // 正文樣式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字體應用到當前的樣式
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留兩位小數(shù)點
        return style;
    }

    /**
     * 單元格樣式列表
     */
    private Map<String, CellStyle> styleMap(Workbook workbook) {
        Map<String, CellStyle> styleMap = new LinkedHashMap<>();
        styleMap.put("head", createCellHeadStyle(workbook));
        styleMap.put("content", createCellContentStyle(workbook));
        styleMap.put("integer", createCellContent4IntegerStyle(workbook));
        styleMap.put("double", createCellContent4DoubleStyle(workbook));
        return styleMap;
    }

}

  • 使用案例

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import com.utils.ExcelUtils;

public class Demo {
    public static void main(String[] args) throws Exception {

        // 保存路徑
        String savePath = "C:\\Users\\czjcz\\Desktop";
        // 文件名稱
        String fileName = "poi-test";
        // 第一行表格的總名稱
        String sheetTitle = "測試bbbbb";
        // 底部表格名稱
        String sheetName = "測試aaaa";

        // 模擬要導出的數(shù)據(jù)
        List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();// 要導出的數(shù)據(jù)
        Map<String, Object> item = new HashMap<String, Object>();
        item.put("id", 1);
        item.put("name", "王佳佳");
        item.put("company", "XXX有限公司");
        item.put("phone", "13111111111");
        item.put("email", "xxx@qq.com");
        item.put("province", "廣東省");
        item.put("address", "XXXXXXXX街道41號");
        allList.add(item);

        List<String> columnNames = new LinkedList<>();
        columnNames.add("ID");
        columnNames.add("姓名");
        columnNames.add("公司");
        columnNames.add("手機");
        columnNames.add("電子郵件");
        columnNames.add("地區(qū)(省份)");
        columnNames.add("聯(lián)系地址");

        ExcelUtils exportExcel2007 = new ExcelUtils();
        exportExcel2007.writeExcelTitle(savePath, fileName, sheetName, columnNames, sheetTitle);

        for (int j = 0; j < 1; j++) {
            List<List<Object>> objects = new LinkedList<>();
            for (int i = 0; i < allList.size(); i++) {
                Map<String, Object> busiUser = allList.get(i);
                List<Object> dataA = new LinkedList<>();
                dataA.add(busiUser.get("id"));
                dataA.add(busiUser.get("name"));
                dataA.add(busiUser.get("company"));
                dataA.add(busiUser.get("phone"));
                dataA.add(busiUser.get("email"));
                dataA.add(busiUser.get("province"));
                dataA.add(busiUser.get("address"));
                objects.add(dataA);
            }

            try {
                exportExcel2007.writeExcelData(savePath, fileName, sheetName, objects);
            } catch (IOException e) {
                e.printStackTrace();
            }
            objects.clear();
        }
        exportExcel2007.dispose();// 釋放資源
        System.out.println("生成excel文檔成功抠藕，所在路徑：" + savePath +"\\"+ fileName + ".xlsx");
    }
}

  • 導出Excel文件樣板

頂部鏈接有提供下載查看
最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市蒋困，隨后出現(xiàn)的幾起案子盾似，更是在濱河造成了極大的恐慌，老刑警劉巖雪标，帶你破解...
    沈念sama閱讀 211,639評論 6贊 492
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件零院，死亡現(xiàn)場離奇詭異，居然都是意外死亡村刨，警方通過查閱死者的電腦和手機告抄，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,277評論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來嵌牺，“玉大人打洼，你說我怎么就攤上這事∧娲猓” “怎么了募疮？”我有些...
    開封第一講書人閱讀 157,221評論 0贊 348
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長僻弹。 經常有香客問我阿浓，道長，這世上最難降的妖魔是什么蹋绽？ 我笑而不...
    開封第一講書人閱讀 56,474評論 1贊 283
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任芭毙，我火速辦了婚禮，結果婚禮上卸耘，老公的妹妹穿的比我還像新娘退敦。我一直安慰自己，他們只是感情好鹊奖，可當我...
    茶點故事閱讀 65,570評論 6贊 386
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布苛聘。 她就那樣靜靜地躺著，像睡著了一般忠聚。 火紅的嫁衣襯著肌膚如雪设哗。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 49,816評論 1贊 290
  • 城市分裂傳說
    那天两蟀，我揣著相機與錄音网梢，去河邊找鬼。 笑死赂毯，一個胖子當著我的面吹牛战虏，可吹牛的內容都是我干的拣宰。 我是一名探鬼主播，決...
    沈念sama閱讀 38,957評論 3贊 408
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼烦感，長吁一口氣：“原來是場噩夢啊……” “哼巡社！你這毒婦竟也來了？” 一聲冷哼從身側響起手趣，我...
    開封第一講書人閱讀 37,718評論 0贊 266
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤晌该，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后绿渣，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體朝群，經...
    沈念sama閱讀 44,176評論 1贊 303
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 36,511評論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年中符，在試婚紗的時候發(fā)現(xiàn)自己被綠了姜胖。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,646評論 1贊 340
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡淀散，死狀恐怖右莱，靈堂內的尸體忽然破棺而出，到底是詐尸還是另有隱情档插，我是刑警寧澤隧出，帶...
    沈念sama閱讀 34,322評論 4贊 330
  • ?日本核電站爆炸內幕
    正文 年R本政府宣布，位于F島的核電站阀捅，受9級特大地震影響胀瞪，放射性物質發(fā)生泄漏。R本人自食惡果不足惜饲鄙，卻給世界環(huán)境...
    茶點故事閱讀 39,934評論 3贊 313
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一凄诞、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧忍级，春花似錦帆谍、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,755評論 0贊 21
  • 一樁弒父案汛蝙，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至朴肺，卻和暖如春窖剑，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背戈稿。 一陣腳步聲響...
    開封第一講書人閱讀 31,987評論 1贊 266
  • 情欲美人皮
    我被黑心中介騙來泰國打工西土， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人鞍盗。 一個月前我還...
    沈念sama閱讀 46,358評論 2贊 360
  • 代替公主和親
    正文 我出身青樓需了，卻偏偏與公主長得像跳昼，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子肋乍，可洞房花燭夜當晚...
    茶點故事閱讀 43,514評論 2贊 348