java導出數據到Excel文件

這是一個導出數據到Excel的工具類

方式一:maven方式需要引用的包(版本自己調整即可)

<!-- poi配置開始 -->
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.0</version>
</dependency>

<!-- poi-ooxml配置開始 -->
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.1</version>
</dependency>

方式二:傳統方式需要引用的包(版本自己調整即可)

commons-collections4-4.2.jar
commons-compress-1.18.jar
poi-4.0.0.jar
poi-ooxml-4.0.0.jar
poi-ooxml-schemas-4.0.0.jar
xmlbeans-3.0.1.jar

工具類代碼

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ToolExcelExp {
    /**
     * @描述: 導出信息到Excel文件
     */
    public static boolean exportBigDataExcel(String savePath, List<String[]> dataList) {
        long startTime = System.currentTimeMillis();
        boolean flag = false;

        // 最重要的就是使用SXSSFWorkbook，表示流的方式進行操作
        // 在內存中保持100行，超過100行將被刷新到磁盤
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sh = wb.createSheet(); // 建立新的sheet對象

        for (int i = 0, dataListSize = dataList.size(); i < dataListSize; i++) {
            String[] str = dataList.get(i);
            Row row_value = sh.createRow(i);
            for (int j = 0, strLength = str.length; j < strLength; j++) {
                Cell cel_value = row_value.createCell(j);
                cel_value.setCellValue(str[j]);
            }
        }
        FileOutputStream fileOut;
        try {
            File fileDir = new File(savePath.substring(0, savePath.lastIndexOf(File.separator)));
            if (!fileDir.exists()) {
                fileDir.mkdirs();
            }
            fileOut = new FileOutputStream(savePath);
            wb.write(fileOut);
            fileOut.close();
            wb.dispose();
            wb.close();
            flag = true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        long endTime = System.currentTimeMillis();
        System.out.println("導出完畢,共耗時" + (endTime-startTime) + "毫秒");

        return flag;
    }

    // 生成測試數據
    private static List<String[]> createData (){
        List<String[]> dataList = new ArrayList<String[]>();
        String[] headStr = new String[8];
        headStr[0] = "標題1";
        headStr[1] = "標題2";
        headStr[2] = "標題3";
        headStr[3] = "標題4";
        headStr[4] = "標題5";
        headStr[5] = "標題6";
        headStr[6] = "標題7";
        headStr[7] = "標題8";
        dataList.add(headStr);

        for (int i = 0; i < 1000000; i++) {
            String[] str = new String[8];
            str[0] = "數據的垃圾分類時間到了開房記錄圣誕節了激烈的看見了房間里都是1";
            str[1] = "sdfsdfdsfdsfffffffffffff2";
            str[2] = "320480098308409823093";
            str[3] = "sdjlfkjl立刻就餓了接口連接的4";
            str[4] = "6465213的方式多為sdfsdf5";
            str[5] = "水電費水電費水電費水電費收到6";
            str[6] = "而和任何任務而氣溫氣溫7";
            str[7] = "ssdljfls了解惡劣螺紋孔8";
            dataList.add(str);
        }
        return dataList;
    }

    public static void main(String[] args) {
        List<String[]> dataList = createData();
        exportBigDataExcel("e:\111.xlsx", dataList);
    }
}