poi操作Excel

依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.huawei.audio</groupId>
    <artifactId>ExcelTools</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <!--xls(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--xlsx(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.13</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>


    </dependencies>
</project>

Excel 写操作

import com.sun.istack.internal.FinalArrayList;
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.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;


public class ExcelWriteTest {

    public static final String PATH ="D:\\java\\gupao-springboot\\ExcelTools\\";

    @Test
    public void testWrite03() throws Exception {
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();

        //2.创建一个sheet工作表
        Sheet sheet =workbook.createSheet("狂胜");
//        Sheet sheet = workbook.createSheet();
        //3.创建一个行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("APP专项");
        //(1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("环境IP");
        //(2,1)
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("智慧生活");
        //(2,2)
        Cell cell22 = row2.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(s);

        FileOutputStream outputStream = new FileOutputStream(PATH + "APP专项03.xls");
        workbook.write(outputStream);
        outputStream.close();
        System.out.println("处理完毕");
    }


    @Test
    public void testWrite07() throws Exception {
        //1.创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建一个sheet工作表
        Sheet sheet = workbook.createSheet();
        //3.创建一个行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("APP专项");
        //(1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("环境IP");
        //(2,1)
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("智慧生活");
        //(2,2)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("10.66.186.131");

        FileOutputStream outputStream = new FileOutputStream(PATH + "APP专项07.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        System.out.println("处理完毕");
    }


    @Test
    public void testWrite03Big() throws Exception {
        long begin = System.currentTimeMillis();
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建一个sheet工作表
        Sheet sheet = workbook.createSheet();
        //3.插入数据
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int i = 0; i < 10; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(i);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "APP专项03Big.xls");
        workbook.write(outputStream);
        outputStream.close();
        long endTime =  System.currentTimeMillis();
        System.out.println("耗费时间为："+(double)(endTime-begin)/1000);
        System.out.println("处理完毕");
    }

    @Test
    public void testWrite07Big() throws Exception {
        long begin = System.currentTimeMillis();
        //1.创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建一个sheet工作表
        Sheet sheet = workbook.createSheet();
        //3.插入数据
        for (int rowNum = 0; rowNum < 99999; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int i = 0; i < 10; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(i);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "APP专项03Big.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long endTime =  System.currentTimeMillis();
        System.out.println("耗费时间为："+(double)(endTime-begin)/1000);
        System.out.println("处理完毕");
    }


    @Test
    public void testWrite07BigS() throws Exception {
        long begin = System.currentTimeMillis();
        //1.创建一个工作簿
        Workbook workbook = new SXSSFWorkbook();
        //2.创建一个sheet工作表
        Sheet sheet = workbook.createSheet();
        //3.插入数据
        for (int rowNum = 0; rowNum < 99999; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int i = 0; i < 10; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(i);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "APP专项03BigS.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
        long endTime =  System.currentTimeMillis();
        System.out.println("耗费时间为："+(double)(endTime-begin)/1000);
        System.out.println("处理完毕");
    }






}

excel 读操作

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.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Date;

public class ExcelReadTest {


    @Test
    public void readTest03() throws Exception {
        //读入流文件
        FileInputStream fileInputStream = new FileInputStream(new File("APP专项03.xls"));
        //1.创建一个工作簿，
        Workbook sheets = new HSSFWorkbook(fileInputStream);
        //2.拿到第一个表单
        Sheet sheet1 = sheets.getSheetAt(0);
        //拿到所有的行
        int physicalNumberOfRows = sheet1.getPhysicalNumberOfRows();
        System.out.println("拿到所有的行"+physicalNumberOfRows);
        //拿到每一行的列
        Row row = sheet1.getRow(0);
        int rowNum = row.getPhysicalNumberOfCells();
        System.out.println("每一行的列"+rowNum);
        //(1,1)
        //得到行
        Row row1 = sheet1.getRow(0);
        //得到列
        Cell cell = row1.getCell(0);
        System.out.println("1,1单元格:"+cell.getStringCellValue());
        fileInputStream.close();
    }


    @Test
    public void readTest07() throws Exception{
        FileInputStream fileInputStream = new FileInputStream("ExcelToolsAPP专项07.xlsx");
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //表头处理
        Row rowTittle = sheet.getRow(0);
        if(rowTittle !=null){
            int cellCount = rowTittle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTittle.getCell(cellNum);
                if (cell != null ){
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue);
                }
            }
        }
        //内容处理
        for (int rowNum = 1; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
            int cellCount = rowTittle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = sheet.getRow(rowNum).getCell(cellNum);
                if(cell !=null ){
                    CellType type = cell.getCellTypeEnum();
                    String cellValue ="";
                    switch (type){
                        case STRING://字符串
                            cellValue = cell.getStringCellValue();
                            System.out.println("["+(rowNum+1)+","+(cellNum+1)+"]"+cellValue);
                            break;
                        case BOOLEAN://布尔值
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            System.out.println("["+(rowNum+1)+","+(cellNum+1)+"]"+cellValue);
                            break;
                        case BLANK://空
                            System.out.println("["+(rowNum+1)+","+(cellNum+1)+"]"+cellValue);
                            break;
                        case NUMERIC://数字
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                Date dateCellValue = cell.getDateCellValue();
                                cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd HH:mm:ss");
                                System.out.println("["+(rowNum+1)+","+(cellNum+1)+"]"+cellValue);
                            }

                    }
                }
            }

        }
        fileInputStream.close();
    }
}