POI java生成excel文件

一、jar包依赖
根据需要下载版本依赖
maven: https://mvnrepository.com

 <!--依赖的jar包-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>版本号</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>版本号</version>
        </dependency>
        <!--日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
           <version>版本号</version>
        </dependency>

        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
           <version>版本号</version>
        </dependency>

二、代码简例

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClassName ExcelPoi
 * @Deacription TODO
 * @Author cgm
 * @Date 2021/2/18 11:24
 * @Version 1.0
 **/
public class ExcelPoiTest {
    @Test
    public void testWrite03() throws IOException {
        //创建一个03版本的工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("POI写操作");
        //创建行
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        //创建单元格
        Cell cell1 = row1.createCell(0);
        Cell cell2 = row1.createCell(1);
        //向单元格写入数据
        cell1.setCellValue("POI");
        cell2.setCellValue("第一行");
        //生成一张表
        String path = "C:\\ideal\\poi";
        FileOutputStream fileOutputStream = new FileOutputStream(path+"03.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("03表生成完毕");
    }

    @Test
    public void testWrite07() throws IOException {
        //创建一个07的工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("POI写操作2");
        //创建行
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        //创建单元格
        Cell cell1 = row1.createCell(0);
        Cell cell2 = row1.createCell(1);
        //向单元格写入数据
        cell1.setCellValue("poi");
        cell2.setCellValue("first");
        //生成一张表
        String path = "C:\\ideal\\poi";
        FileOutputStream fileOutputStream = new FileOutputStream(path+"07.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("07表生成完毕");

    }

    /**
     * 03版本   后缀.xls
     * **缺点：最多只能处理 65536 行，否则会抛出异常
     * **优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。
     * @throws Exception
     */

    @Test
    public void HSSFWriteBigData() throws IOException {
        //起始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("大数据写HSSF");
        //写入数据
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 4; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        //生成表
        String path = "C:\\ideal\\";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println(end - begin + "ms");
        System.out.println("BigData03表生成完毕");
    }

    /**
     * 07版本 后缀.xlsx
     * **缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如 100 万条数据。
     * **优点：可以写较大的数据量
     * @throws Exception
     */
    @Test
    public void SXSSFWriteBigData() throws IOException {
        //起始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("大数据写SXSSF");
        //写入数据
        for (int rowNum = 0; rowNum < 65537; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 14; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        //生成表
        String path = "C:\\ideal\\";
        FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println(end - begin + "ms");
        System.out.println("BigData07表生成完毕");
    }

}