java100萬資料量導出測試1

package com.hisense.cis.test;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.AbstractMap;
import java.util.Map;
import java.util.Set;
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.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

// 楊少平測試
//yangyifan-20210203

public class Test2 {

    public static void main(String []args) {
        getExcelExport();
    }
    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);


    }
    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);        //給标題設定樣式

    }
    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;

    }

    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());
            }
        }

    }

    private static void excelExport(SXSSFWorkbook workBook) {

        String filePath = getSavePath();  //擷取檔案儲存路徑
        if(filePath == null){            System.exit(1);
            return;
        }

        String srcFile = "D:\\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();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                fileOut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    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;
    }
    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;

    }


}