美文网首页
poi读写excel

poi读写excel

作者: Nick_4438 | 来源:发表于2022-09-17 16:18 被阅读0次

简介

poi是一个apache开源项目，专门针对office文档。
其中HSSF是专门针对Excel '97(-2007) 文件格式. XSSF 是专门针对2007 OOXML (.xlsx) 文件格是.

代码使用

pom.xml引入依赖

<!--        如果只使用HSSF，可以用该依赖 -->
<!--        <dependency>-->
<!--            <groupId>org.apache.poi</groupId>-->
<!--            <artifactId>poi</artifactId>-->
<!--            <version>5.2.2</version>-->
<!--        </dependency>-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

创建文件

简单创建一个excel文件

package com.example.exceldemo.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.OutputStream;
//https://poi.apache.org/components/spreadsheet/quick-guide.html#NewSheet
public class CreateFile {
    public static void main(String[] args) throws Exception{
        Workbook wb = new XSSFWorkbook();  // or new XSSFWorkbook();
        Sheet sheet1 = wb.createSheet("new sheet");
        Sheet sheet2 = wb.createSheet("second sheet");

        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
    }
}

往cell里面写值

package com.example.exceldemo.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class CreateCells {
    public static void main(String[] args) throws Exception {

        Workbook wb = new HSSFWorkbook();
//        Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(0);
// Create a cell and put a value in it.
        Cell cell = row.createCell(0);
        cell.setCellValue(1);
// Or do it on one line.
        row.createCell(1).setCellValue(1.2);
        row.createCell(2).setCellValue(
                createHelper.createRichTextString("This is a string"));
        row.createCell(3).setCellValue(true);
// Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }

    }
}

获取遍历所有数据

package com.example.exceldemo.poi;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;

import java.io.File;

public class getAll {
    public static void main(String[] args) throws Exception {
//        Workbook wb = new HSSFWorkbook();
        Workbook wb = WorkbookFactory.create(new File("content.xlsx"));
//        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

//        Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));
        DataFormatter formatter = new DataFormatter();
        Sheet sheet1 = wb.getSheetAt(0);
        for (Row row : sheet1) {
            for (Cell cell : row) {
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");
                // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
                String text = formatter.formatCellValue(cell);
                System.out.println(text);
                // Alternatively, get the value and format it yourself
                switch (cell.getCellType()) {
                    case STRING:
                        System.out.println("STRING:"+cell.getRichStringCellValue().getString());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            System.out.println("NUMERIC DATE:"+cell.getDateCellValue());
                        } else {
                            System.out.println("NUMERIC:"+cell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN:
                        System.out.println("BOOLEAN:"+cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        switch (cell.getCachedFormulaResultType()) {
                            case BOOLEAN:
                                System.out.println("FORMULA BOOLEAN:"+cell.getBooleanCellValue());
                                break;
                            case NUMERIC:
                                System.out.println("FORMULA NUMERIC:"+cell.getNumericCellValue());
                                break;
                            case STRING:
                                System.out.println("FORMULA STRING:"+cell.getRichStringCellValue());
                                break;
                            default:
                                System.out.println("FORMULA UNKNOW:"+cell.getCellFormula());
                                break;
                        }
                        break;
                    case BLANK:
                        System.out.println("BLANK:");
                        break;
                    default:
                        System.out.println("error:");
                }
            }
        }

    }

}

合并单元格

package com.example.exceldemo.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.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class Mergingcells {
    public static void main(String[] args) throws Exception {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(1);
        cell.setCellValue("This is a test of merging");
        sheet.addMergedRegion(new CellRangeAddress(
                1, //first row (0-based)
                1, //last row  (0-based)
                1, //first column (0-based)
                2  //last column  (0-based)
        ));
// Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
        wb.close();
    }
}

填充颜色

package com.example.exceldemo.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class fillsAndColor {
    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(1);
// Aqua background
        CellStyle style = wb.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
        style.setFillPattern(FillPatternType.BIG_SPOTS);
        Cell cell = row.createCell(1);
        cell.setCellValue("X");
        cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the font color.
        style = wb.createCellStyle();
        style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell = row.createCell(2);
        cell.setCellValue("X");
        cell.setCellStyle(style);
// Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
        wb.close();
    }
}
image.png

边框

package com.example.exceldemo.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class borders {
    public static void main(String[] args) throws Exception {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(1);
// Create a cell and put a value in it.
        Cell cell = row.createCell(1);
        cell.setCellValue(4);
// Style the cell with borders all around.
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(BorderStyle.MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);
// Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
        wb.close();
    }

}
image.png

公式读取缓存值

package com.example.exceldemo.poi;

import org.apache.poi.ss.usermodel.*;

import java.io.File;

public class getAllFormulaCache {
    public static void main(String[] args) throws Exception {
//        Workbook wb = new HSSFWorkbook();
        Workbook wb = WorkbookFactory.create(new File("content.xlsx"));
        Sheet sheet1 = wb.getSheetAt(0);
        Row row = sheet1.createRow(5);
        row.createCell(0).setCellValue(1.1);
        row.createCell(1).setCellValue(2);
        row.createCell(2).setCellFormula("A6+B6");  //新写入一个公式，没有缓存
        Cell cell = row.getCell(2);
        if (cell.getCellType() == CellType.FORMULA) {
            switch (cell.getCachedFormulaResultType()) {
                case BOOLEAN:
                    System.out.println("BOOLEAN:"+cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    System.out.println("NUMERIC:"+cell.getNumericCellValue());
                    break;
                case STRING:
                    System.out.println("STRING:"+cell.getRichStringCellValue());
                    break;
            }
        }
//        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
//            wb.write(fileOut);
//        }


    }

}

日志打印： NUMERIC:0.0，可以看出得出来的值为0。并非前面2个格子相加

公式读取计算值

package com.example.exceldemo.poi;

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;

public class getAllFormulaEvaluate {

    public static void main(String[] args) throws Exception {
//        Workbook wb = new HSSFWorkbook();
        Workbook wb = WorkbookFactory.create(new File("content.xlsx"));
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sheet1 = wb.getSheetAt(0);
        Row row = sheet1.createRow(5);
        row.createCell(0).setCellValue(1.1);
        row.createCell(1).setCellValue(2);
        row.createCell(2).setCellFormula("A6+B6");  //新写入一个公式，没有缓存
        Cell cell = row.getCell(2);
        if (cell.getCellType() == CellType.FORMULA) {
//            switch (cell.getCachedFormulaResultType()) {
            switch (evaluator.evaluateFormulaCell(cell)) {
                case BOOLEAN:
                    System.out.println("BOOLEAN:"+cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    System.out.println("NUMERIC:"+cell.getNumericCellValue());
                    break;
                case STRING:
                    System.out.println("STRING:"+cell.getRichStringCellValue());
                    break;
            }
        }
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }


    }

}

日志打印： NUMERIC:3.1，前面2个格子相加所得

参考文档

更多参考样例，参考官方文档
https://poi.apache.org/components/spreadsheet/quick-guide.html

相关文章

  • Java处理Excel：从POI到SPL

    xcel等类库就方便多了，其中POI最为出色。 POI具有全面而细致的xls读写能力 POI可读写多种Excel文...

  • POI excel 读写

    excel 是一个文件 file sheet row 行 cell 单元格单元格格式 : 字体格式 单元...

  • poi读写excel

    简介 poi是一个apache开源项目，专门针对office文档。其中HSSF是专门针对Excel '97(-20...

  • Java导入导出Excel

    读写Excel三种常用的技术： POI JXL FASTEXCEL 概念： 工作簿：相当于Excel文件 工作表S...

  • 25 poi操作excel

    本节介绍一下使用poi读写excel。 1、操作步骤 创建一个maven项目 poi-test 加入依赖 在src...

  • java excel读取

    java读写excel 需要引入一下jar包compile group: 'org.apache.poi', na...

  • POI读写EXCEL文件

    下载jar包并导入 可以从官网下载。 常用类 导出excel步骤 完整示例 读取数据

  • 使用poi读写excel

    首先了解以下excel文件怎么和poi中的组件对应起来的。 一个Excel文件对应于一个Workbook对象 一个...

  • Java 读写excel（POI）

    1、文件的读写 1.1、文件的读取 1.2、文件的写入 注：其中XSSF指的是excel版本为2007以上，HSS...

  • POI and EasyExcel

    Excel 03版xls 最多65536行 07版xlsx 没有限制 POI apache HSSF -读写Mic...

网友评论

      本文标题：poi读写excel

      本文链接：https://www.haomeiwen.com/subject/oskeortx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|poi读写excel|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！