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