excelDemo

excel

一.poi

1. 引入依赖

<!--        03版-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- excel 07版-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

<!--        日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.6</version>
        </dependency>

<!--        test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

2.excel写入

package com.hw;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;

/**
 * @author 黄巍
 * @description
 * @create 2022-05-29-16:11
 */
public class ExcelWriteTest {

    String path = "E:\\IO\\";


    /**
     * 作者 黄巍
     * 功能描述 写入大量数据，07版
     * 参数
     * 日期  17:09 2022/5/29
     * 返回值 void
     */
    @Test
    public void excel07TestBigData(){

        long begin = System.currentTimeMillis();
        // 1.生成工作簿
        Workbook workbook = new SXSSFWorkbook();
        // 2.创建一个工作表
        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");

        BufferedOutputStream bufferedOutputStream = null;
        try {
            // 输出
            bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(new File(path + "myexcel07BigData.xlsx")));

            workbook.write(bufferedOutputStream);
            // 清除临时文件
            ((SXSSFWorkbook)workbook).dispose();

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != bufferedOutputStream){
                try {
                    bufferedOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        Long end = System.currentTimeMillis();

        System.out.println((double)(end - begin) / 1000);
    }

    /**
     * 作者 黄巍
     * 功能描述 excel写入数据 07版
     * 参数
     * 日期  17:10 2022/5/29
     * 返回值 void
     */
    @Test
    public void excel07Test(){
        // 1.生成工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("黄巍的测试表");
        // 3.创建一行
        Row row = sheet.createRow(0);
        // 4.创建一个单元格
        Cell cell11 = row.createCell(0);
        cell11.setCellValue("姓名");
        Cell cell12 = row.createCell(1);
        cell12.setCellValue("age");
        Cell cell13 = row.createCell(2);
        cell13.setCellValue("date");

        // 3.创建一行
        Row row1 = sheet.createRow(1);
        // 4.创建一个单元格
        Cell cell21 = row1.createCell(0);
        cell21.setCellValue("黄巍");
        Cell cell22 = row1.createCell(1);
        cell22.setCellValue("28");

        Cell cell23 = row1.createCell(2);
        String time = new DateTime().toString("yyyy_MM_dd HH_mm_ss");
        cell23.setCellValue(time);


        BufferedOutputStream bufferedOutputStream = null;
        try {
            // 输出
            bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(new File(path + "myexcel07.xlsx")));

            workbook.write(bufferedOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != bufferedOutputStream){
                try {
                    bufferedOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 作者 黄巍
     * 功能描述 excel写入数据 03版
     * 参数
     * 日期  17:10 2022/5/29
     * 返回值 void
     */
    @Test
    public void excel03Test(){
        // 1.生成工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("黄巍的测试表");
        // 3.创建一行
        Row row = sheet.createRow(0);
        // 4.创建一个单元格
        Cell cell11 = row.createCell(0);
        cell11.setCellValue("姓名");
        Cell cell12 = row.createCell(1);
        cell12.setCellValue("age");
        Cell cell13 = row.createCell(2);
        cell13.setCellValue("date");

        // 3.创建一行
        Row row1 = sheet.createRow(1);
        // 4.创建一个单元格
        Cell cell21 = row1.createCell(0);
        cell21.setCellValue("黄巍");
        Cell cell22 = row1.createCell(1);
        cell22.setCellValue("28");

        Cell cell23 = row1.createCell(2);
        String time = new DateTime().toString("yyyy_MM_dd HH_mm_ss");
        cell23.setCellValue(time);


        BufferedOutputStream bufferedOutputStream = null;
        try {
            // 输出
            bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(new File(path + "myexcel.xls")));

            workbook.write(bufferedOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != bufferedOutputStream){
                try {
                    bufferedOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

3.excel读取

package com.hw;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.*;
import java.text.SimpleDateFormat;

/**
 * @author 黄巍
 * @description
 * @create 2022-05-29-16:11
 */
public class ExcelReadTest {

    String path = "E:\\IO\\";

    /**
     * 作者 黄巍
     * 功能描述 根据单元格中数值类型的不同，获取单元格参数
     * 参数 cell
     * 日期  17:44 2022/5/29
     * 返回值 java.lang.String
     */
    protected String getValue(Cell cell) {
        Object obj = "";
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case ERROR:
                    obj = cell.getErrorCellValue();
                    break;
                case NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
                        obj = sdf.format(cell.getDateCellValue());
                    } else {
                        cell.setCellType(CellType.STRING);
                        obj = cell.getStringCellValue();
                    }
                    break;
                case STRING:
                    obj = cell.getStringCellValue();
                    break;
                case FORMULA:
                    try {
                        obj = String.valueOf(cell.getStringCellValue());
                    } catch (IllegalStateException e) {
                        obj = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
            }
        }
        return obj.toString().trim().replace('，', ',')
                .replace('（', '(').replace("）", ")");
    }


    /**
     * 作者 黄巍
     * 功能描述 excel读取数据 07版
     * 参数
     * 日期  17:10 2022/5/29
     * 返回值 void
     */
    @Test
    public void excel07ReadTest() throws IOException {

        // 1.获得输入流
        BufferedInputStream bis = null;
        bis = new BufferedInputStream(new FileInputStream(new File(path + "myexcel07.xlsx")));
        // 2.生成工作簿
        Workbook workbook = new XSSFWorkbook(bis);
        // 3.获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 4.获取数据
        // 获取该表中总共有多少行
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < physicalNumberOfRows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            // 获取该行总共有多少个单元格
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < physicalNumberOfCells; cellNum++) {
                Cell cell = row.getCell(cellNum);
                System.out.println(getValue(cell));
            }
        }

        // 关闭资源
        if (null != bis){
            bis.close();
        }

    }
    /**
     * 作者 黄巍
     * 功能描述 excel读取数据 07版
     * 参数
     * 日期  17:10 2022/5/29
     * 返回值 void
     */
    @Test
    public void excel03ReadTest() throws IOException {

        // 1.获得输入流
        BufferedInputStream bis = null;
        bis = new BufferedInputStream(new FileInputStream(new File(path + "myexcel03.xls")));
        // 2.生成工作簿
        Workbook workbook = new HSSFWorkbook(bis);
        // 3.获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 4.获取数据
        // 获取该表中总共有多少行
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < physicalNumberOfRows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            // 获取该行总共有多少个单元格
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < physicalNumberOfCells; cellNum++) {
                Cell cell = row.getCell(cellNum);
                System.out.println(getValue(cell));
            }
        }

        // 关闭资源
        if (null != bis){
            bis.close();
        }

    }

}

二.Easyexcel

1.依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.1</version>
        </dependency>

参考

https://alibaba-easyexcel.github.io/index.html