easyExcel

最新推荐文章于 2022-12-07 10:26:33 发布

qq_35366466

最新推荐文章于 2022-12-07 10:26:33 发布

阅读量200

点赞数

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/qq_35366466/article/details/120680645

版权

java 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

这篇博客详细介绍了如何使用 Apache POI Java 库来读写 Excel 文件。首先展示了如何创建和写入工作簿、工作表、行和单元格，包括设置字符串和日期值。接着，解释了 HSSFWorkbook 和 XSSFWorkbook 的区别，前者适用于小文件，后者能处理大量数据但速度较慢。最后，演示了读取 Excel 文件内容的方法，特别强调了处理不同数据类型（如字符串、日期和数字）的注意事项。

摘要由CSDN通过智能技术生成

1、POI简单写

public class writePOI {

    String path = "F:\\idea-project\\POI\\";

    @Test
    public void write01()throws Exception{
        //创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("统计表");
        //创建一个行
        Row row = sheet.createRow(0);
        //创建单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("今日步数");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("5923");

        Row row1 = sheet.createRow(1);
        Cell cell2 = row1.createCell(0);
        cell2.setCellValue("new boy");
        Cell cell3 = row1.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(s);

        FileOutputStream fileOutputStream = new FileOutputStream(path + "03统计.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        System.out.println("统计完成");
    }

    @Test
    public void write02()throws Exception{
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet("统计表");
        //创建一个行
        Row row = sheet.createRow(0);
        //创建单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("今日步数");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("5923");

        Row row1 = sheet.createRow(1);
        Cell cell2 = row1.createCell(0);
        cell2.setCellValue("new boy");
        Cell cell3 = row1.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(s);

        FileOutputStream fileOutputStream = new FileOutputStream(path + "07统计.xlsx");
        workbook.write(fileOutputStream);
        System.out.println("统计完成");
    }

}

HSSFWorkbook只能操作65536行，XSSFWorkbook可以处理超过65536行，但耗时，

（2）读

package com.gykj.poi;

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

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class readPOI {

    String path = "F:\\idea-project\\POI\\";

    @Test
    public void read01()throws Exception{

        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"03统计.xls");
        //创建一个工作簿
        Workbook workbook  = new HSSFWorkbook(inputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(0);
        //得到列
        Cell cell = row.getCell(0);
        //读取值的时候，一定要注意类型
        //getStringCellValue字符串类型
        System.out.println(cell.getStringCellValue());
        inputStream.close();

    }
}

（3）数据类型

package com.gykj.poi;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

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

public class typePOI {

    String path = "F:\\idea-project\\POI\\";

    @Test
    public void testCellType() throws Exception{
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"test.xlsx");
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题内容
        Row rowTitle = sheet.getRow(0);
        if(rowTitle != null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0;cellNum<cellCount;cellNum++){
                Cell cell = rowTitle.getCell(cellNum);
                if(cell !=null){
                    int cellType = cell.getCellType();
                    String stringCellValue = cell.getStringCellValue();
                    System.out.println(stringCellValue+"|");
                }
            }
            System.out.println();
        }

        //获取表中内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum= 1;rowNum<rowCount;rowNum++){
            Row rowData = sheet.getRow(rowNum);
            if(rowData != null){
                //读取列
                int physicalNumberOfCells = rowTitle.getPhysicalNumberOfCells();
                for(int cellNum =0; cellNum<physicalNumberOfCells;cellNum++){
                    System.out.println("["+(rowCount+1)+"-"+(cellNum+1)+"]");
                    Cell cell = rowData.getCell(cellNum);
                    //匹配数据类型
                    if(cell!=null){
                        int cellType = cell.getCellType();
                        String cellValue = "";

                        switch (cellType){
                            case XSSFCell.CELL_TYPE_STRING://字符串
                                System.out.println("String:");
                                cellValue = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN://布尔
                                System.out.println("boolean:");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case XSSFCell.CELL_TYPE_BLANK://空
                                System.out.println("blank:");
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC://数字(日期、数字）
                                System.out.println("number:");
                                if(DateUtil.isCellDateFormatted(cell)){
                                    System.out.println("date");
                                    Date dateCellValue = cell.getDateCellValue();
                                    cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                                }else {
                                    System.out.println("shuzi:");
                                    cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                                    cellValue = cell.toString();
                                }
                                break;
                            case XSSFCell.CELL_TYPE_ERROR:
                                System.out.println("type error");
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }
            }

        }
        inputStream.close();
    }

}