javaPOI

导入依赖

<!--导入依赖-->
<dependencies>
    <!--xls03-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!--xls07-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.4</version>
    </dependency>
    <!--测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>RELEASE</version>
        <scope>compile</scope>
    </dependency>
</dependencies>

文件写入

package com.leitao;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

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


/**
 * @Description: TODO
 * @Author djn
 * @Date 2021/11/3
 * @Version V1.0
 **/
public class ExcelWriteTest {

    String PATH = "E:\\IdeaWorkSpace\\";
    @Test
    public void testWrite03() throws IOException {
        /*创建一个工作薄*/
        HSSFWorkbook workbook = new HSSFWorkbook();
        /*创建一个工作表*/
        HSSFSheet sheet = workbook.createSheet("个人信息统计表-雷涛");
        /*创建第一行*/
        HSSFRow row1 = sheet.createRow(0);
        /*创建单元格*/
        HSSFCell cell11 = row1.createCell(0);
        cell11.setCellValue("姓名");
        HSSFCell cell12 = row1.createCell(1);
        cell12.setCellValue("性别");
        HSSFCell cell13 = row1.createCell(2);
        cell13.setCellValue("年龄");
        HSSFCell cell14 = row1.createCell(3);
        cell14.setCellValue("创建时间");
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        /*（1，1）*/
        cell21.setCellValue("雷涛");
        HSSFCell cell22 = row2.createCell(1);
        /*（1，2）*/
        cell22.setCellValue("男");
        HSSFCell cell23 = row2.createCell(2);
        /*（1，3）*/
        cell23.setCellValue("22");
        HSSFCell cell24 = row2.createCell(3);
        String time = new DateTime().toString("yyy-MM-dd HH:mm:ss");
        /*（1，4）*/
        cell24.setCellValue(time);
        /*生成一张表（IO流）*/
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "个人信息统计表03版本.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("个人信息表已自动生成！");
    }

    @Test
    public void testWrite07() throws IOException {
        /*创建一个工作薄 07*/
        Workbook workbook = new XSSFWorkbook();
        /*创建一个工作表*/
        Sheet sheet = workbook.createSheet("个人信息统计表-张红彬");
        /*创建第一行*/
        Row row1 = sheet.createRow(0);
        /*创建单元格*/
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("姓名");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("性别");
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("年龄");
        Cell cell14 = row1.createCell(3);
        cell14.setCellValue("创建时间");
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        /*（1，1）*/
        cell21.setCellValue("雷涛");
        Cell cell22 = row2.createCell(1);
        /*（1，2）*/
        cell22.setCellValue("男");
        Cell cell23 = row2.createCell(2);
        /*（1，3）*/
        cell23.setCellValue("22");
        Cell cell24 = row2.createCell(3);
        String time = new DateTime().toString("yyy-MM-dd HH:mm:ss");
        /*（1，4）*/
        cell24.setCellValue(time);
        /*生成一张表（IO流）*/
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "个人信息统计表07版本.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("个人信息表07已自动生成！");
    }

    @Test
    public void testWrite03BigData() throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建一个03工作薄
        Workbook workbook = new HSSFWorkbook();
        //创建一个表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum <65537 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);

            for (int cellNum = 0; cellNum <10 ; cellNum++) {
                //创建单元格
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("结束over");
        //创建流
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
        //输出
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        //输出时间
        System.out.println((double) (end-begin)/1000);
    }

    @Test
    public void testWrite07BigData() throws IOException {
        long begin = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum <100000 ; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum <10 ; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("完成over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

    @Test
    public void testWrite07BigDataS() throws IOException {
        long begin = System.currentTimeMillis();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum <100000 ; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum <10 ; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("完成over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清楚临时文件！
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
}

文件读出

package com.leitao;

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.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.IOException;

/**
 * @Description: TODO
 * @Author djn
 * @Date 2021/11/4
 * @Version V1.0
 **/
public class ExcelReadTest {
    String PATH="E:\\IdeaWorkSpace\\";
    @Test
    public void testRead03() throws IOException {
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream(PATH + "个人信息统计表03版本.xls");
        //1、创建一个工作薄，使用Excel能操作的这边它都可以操作
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //2、得到表
        Sheet sheet = workbook.getSheetAt(0);
        //3、得到行
        Row row = sheet.getRow(1);
        //4、得到列
        Cell cell = row.getCell(2);
        //读取数据，一定要注意类型getStringCellValue字符串类型
        System.out.println(cell.getStringCellValue());
        fileInputStream.close();

    }

    @Test
    public void testRead07() throws IOException {
        FileInputStream fileInputStream = new FileInputStream(PATH + "个人信息统计表07版本.xlsx");
        Workbook sheets = new XSSFWorkbook(fileInputStream);
        Sheet sheetAt = sheets.getSheetAt(0);
        Row row = sheetAt.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        fileInputStream.close();
    }
}