Java使用POI导出Excel

这篇具有很好参考价值的文章主要介绍了Java使用POI导出Excel。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、前景

在项目开发中往往需要使用到Excel的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。

操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel !

废话不多说,开始撸起来!!!

二、概念

POI官网;https://poi.apache.org/

POI官网API:https://poi.apache.org/components/spreadsheet/index.html

POI的Javadocs文档:https://poi.apache.org/apidocs/index.html

百度百科介绍:https://baike.baidu.com/item/Apache%20POI/4242784?fr=aladdin

2.1. 简介

Java使用POI导出Excel

POI不仅仅可以操作Excel,他的定位是操作Microsoft Office读和写,Microsoft Office其中包含了很多常用的办公文件,例如:Excel、ppt、word、Visio等等…

结构:

  • HSSF- 提供读写Microsoft Excel XLS格式档案的功能。
  • XSSF- 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
  • HWPF- 提供读写Word(97-2003) 的 Java 组件,XWPF是 POI 支持 Word 2007+ 的 Java组件,提供简单文件的读写功能;
  • HSLF- 提供读写Microsoft PowerPoint格式档案的功能。
  • HDGF - 提供读Microsoft Visio格式档案的功能。
  • HPBF - 提供读Microsoft Publisher格式档案的功能。
  • HSMF- 提供读Microsoft Outlook格式档案的功能。

截止目前最新的版本是5.2.3(Sep 17, 2022),现在还在不断的完善

2.2.Excel版本和相关对象

Excel有两个版本:

  • 2003版本和2007版本存在兼容性的问题!03最多只有65536行!07版本最多有1048576行!
  • 2003版本的文件名后缀是.xls
  • 2007版本的文件后缀名是.xlsx

相关对象:工作簿、工作表、行、列 对应的POI当中的对象是Workbook、Sheet、Row、Cell

Java使用POI导出Excel

03最多只有65536行,如下所示:

在poi当中往往会说超过65535行会报错,原因是poi当中0代表的是第一行!

Java使用POI导出Excel

07最多只有1048576行,如下所示:

Java使用POI导出Excel

2.3.WorkBook

首先我们知道POI中我们最熟悉的莫过于WorkBook这样一个接口,WorkBook代表的就是我们上面所提到的工作簿,WorkBook有如下三个实现类。明确一点,这三个都是WorkBook的实现类,所以用法上基本上是一致的!

Java使用POI导出Excel

HSSFWorkbook: 这个实现类是我们早期使用最多的对象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本后缀还是.xls

  • 缺点: 最多只能导出 65535行,也就是导出的数据函数超过这个数据就会报错;
  • 优点: 一般不会报内存溢出。(因为数据量还不到7w所以内存一般都够用,首先你得明确知道这种方式是将数据先读取到内存中,然后再操作)

XSSFWorkbook: 这个实现类现在在很多公司都可以发现还在使用,它是操作的Excel2003–Excel2007之间的版本,Excel的扩展名是.xlsx

  • 优点: 这种形式的出现是为了突破HSSFWorkbook的65535行局限,是为了针对Excel2007版本的1048576行,16384列,最多可以导出104w条数据;
  • 缺点: 伴随的问题来了,虽然导出数据行数增加了好多倍,但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book,Sheet,row,cell等在写入到Excel之前,都是存放在内存中的(这还没有算Excel的一些样式格式等等),可想而知,内存不溢出就有点不科学了!!!

SXSSFWorkbook : 这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

  • 优点: 这种方式不会一般不会出现内存溢出(它使用了硬盘来换取内存空间,也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来,而内存中存的都是最新的数据),并且支持大型Excel文件的创建(存储百万条数据绰绰有余)。
  • 缺点:
    • 既然一部分数据持久化到了硬盘中,且不能被查看和访问那么就会导致,在同一时间点我们只能访问一定数量的数据,也就是内存中存储的数据;
    • sheet.clone()方法将不再支持,还是因为持久化的原因;
    • 不再支持对公式的求值,还是因为持久化的原因,在硬盘中的数据没法读取到内存中进行计算;
    • 在使用模板方式下载数据的时候,不能改动表头,还是因为持久化的问题,写到了硬盘里就不能改变了;

经过了解也知道了这三种Workbook的优点和缺点,那么具体使用哪种方式还是需要看情况的:

  • 当我们经常导入导出的数据不超过7w的情况下,可以使用HSSFWorkbook或者XSSFWorkbook都行;
  • 当数据量超过7w并且导出的Excel中不牵扯对Excel的样式,公式,格式等操作的情况下,推荐使用SXSSFWorkbook;
  • 当数据量超过7w,并且我们需要操做Excel中的表头,样式,公式等,这时候我们可以使用XSSFWorkbook配合进行分批查询,分批写入Excel的方式来做;

2.4.POI依赖

hutool是一个工具合集,使用poi实际上只需要引入poi-ooxml就可以。因为poi-ooxml里面已经引入了poipoi-ooxml-schemas的依赖。

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>cn.hutool</groupId>
	<artifactId>hutool-all</artifactId>
	<version>5.8.8</version>
</dependency>

Java使用POI导出Excel

三、POI - 写

workbook常用API:
createSheet():创建Excel工作表 返回类型为HSSFSheeet

setSheetName():设置Excel工作表的名称,语法结构如下

public void setSheetName(int sheetIx,String name)

3.1.代码示例

(1)HSSFWorkbook

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new HSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。第一行
        Row row = sheet.createRow(0);
        // 4.创建列。
        // (1,1) 第一行第一列的单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("我们都一样");
        // (1,2) 第一行第二列的单元格
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);

        // 第二行。(1,0)
        Row row1 = sheet.createRow(1);
        //(2,1)第二行第一列的单元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(DateUtil.now());

        // 判断文件是否存在,不存在就创建
        if (FileUtil.isEmpty(new File(path))) {
            FileUtil.mkdir(path);
        }
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        System.out.println("03表生成成功!");
    }
}

生成的Excel如下:

Java使用POI导出Excel

最多65536行,而poi是以0为第一行,所以这里只能写65535,类似于数组以0代表第一个元素一样,一旦超过65535就会报以下异常:

Java使用POI导出Excel

注意:

  1. 假如路径下已经存在Excel文件,再次生成他会直接覆盖该文件。
  2. 使用HSSFWorkbook也可以使用xlsx结尾,正常也是可以打开的,但是超过65535同样会报错

(2)XSSFWorkbook

他是可以超过65535行的并且不会报错,并且他兼容.xls.xlsx两种格式都是可以的。这里需要注意一下,即时是使用的.xls,只要使用的是XSSFWorkbook,超过65535行同样也不会报错!

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new XSSFWorkbook(); // 07和03版本只有对象不同,其他操作一样
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。第一行
        Row row = sheet.createRow(0);
        // 4.创建列。
        // (1,1) 第一行第一列的单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("我们都一样");
        // (1,2) 第一行第二列的单元格
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);

        // 第65537行。(65537,0)
        Row row1 = sheet.createRow(65536);
        //(2,1)第二行第一列的单元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(DateUtil.now());

        // 判断文件是否存在,不存在就创建
        if (FileUtil.isEmpty(new File(path))) {
            FileUtil.mkdir(path);
        }
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        System.out.println("03表生成成功!");
    }
}

(3)SXSSFWorkbook

SXSSFWorkbook同XSSFWorkbook使用方法一样!也是不受行数限制!只不过他是硬盘换时间,在大数据量的情况下,会将内存当中的数据写到临时文件当中,这样达到释放内存,因此占用内存较小,然后速度要比XSSFWorkbook快!

使用SXSSFWorkbook wb = new SXSSFWorkbook(100)创建的工作簿在读取数据时,会根据所传入的阈值(此处是100,默认也是100)。当内存中的对象达到这个阈值时,生成一个临时文件,以临时文件进行存储,来实现分段读取与写入。举个例子:假如写入1-10行数据,然后设置的阀值是2,那么会将1-8行的数据写到硬盘,9和10行的写到内存。类似于一个队列先进先出的规则!

//当为-1的时候表示 将会把所有的行刷新到临时文件
Workbook workbook = new SXSSFWorkbook(-1);
//当为100的时候表示 将会把超过100行的数据刷新到临时文件
Workbook workbook = new SXSSFWorkbook(100);
//表示手动刷新所有数据到临时文件的方式 ,可指定参数 行数
((SXSSFSheet) sheet).flushRows()

这里需要注意的是,当每次刷新到临时文件。内存中的数据就不存在了,因此避免了OOM。有些小伙伴可能会犯还去拿行数,或者操作行数据的问题。这些数据已经被刷新到临时文件,内存中已经不存在了。所以就拿不到了。(抛异常)

SXSSF在把内存数据刷新到硬盘时,是把每个SHEET生成一个临时文件,这个临时文件可能会很大,有可以会达到G级别,如果文件的过大对你来说是一个问题,你可以使用wb.setCompressTempFiles(true);方法让SXSSF来进行压缩,当然性能也会有一定的影响。

默认的临时文件存放目录:

  • windows下:AppData\Local\Temp\poifiles文件夹下,生成一个叫poi-sxssf-sheet**************的文件
  • Linux系统下:会在/tmp/poifiles文件下生成该临时文件

代码示例: 这里我故意设置了为5000 Workbook workbook = new SXSSFWorkbook(5000);,然后在workbook.createSheet这个地方打断点,当执行完的时候临时文件已经创建了!当执行完for循环后,临时文件已经存在内容了,执行write之后会将所有内容都写入临时文件,没有执行write之前,会将超过阀值的数据提前写入临时文件当中,关于这一点大家可以自行测试!

public class ExcelWriterTest03BigData {
    public static void main(String[] args) throws IOException {
        // 开始时间
        long start = System.currentTimeMillis();
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new SXSSFWorkbook(5000);
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。
        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(rowNum + "," + cellNum);
            }
        }
        // 5.生成一张表。03版本的工作簿是以.xlsx结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "07BigDataUpGrade.xlsx");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        // 7.清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        System.out.println("07大数据量表优化后生成成功!");
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println("用时:" + ((end - start) / 1000) + "秒");
    }
}

通过以下会发现,他是写到了xml当中。然后又通过读取xml当中的内容转换到我们设置的Excel文件当中。写到Excel是个耗时的操作,于是先写到硬盘将内存释放,然后这样就是所谓的硬盘换内存。

Java使用POI导出Excel

这是执行完write方法之后文件的大小:

Java使用POI导出Excel

通过以下配置就可以实现临时文件的自定义配置。再有就是记住临时文件的清理。自带api就有实现
((SXSSFWorkbook) workbook).dispose(); 清理临时缓存文件。因为我用的是父类所以强转了。

@Component
public class ExcelConfig {
 
	private final static Logger logger = LoggerFactory.getLogger(ExcelConfig.class);
 
	@Value("${application.tmp.path}")
	private String applicationTmpPath;
 
	/**
	 * 设置使用SXSSFWorkbook对象导出excel报表时,TempFile使用的临时目录,代替{java.io.tmpdir}
	 */
	@PostConstruct
	public void setExcelSXSSFWorkbookTmpPath() {
		String excelSXSSFWorkbookTmpPath = applicationTmpPath + "/poifiles";
		File dir = new File(excelSXSSFWorkbookTmpPath);
		if (!dir.exists()) {
			dir.mkdirs();
		}
		TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
		logger.info("setExcelSXSSFWorkbookTmpPath={}", excelSXSSFWorkbookTmpPath);
	}
 
}

3.2. 性能对比

(1)HSSFWorkbook

优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快。
缺点:最多只能处理65536行,否则会抛出异常。

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 java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03BigData {
    public static void main(String[] args) throws IOException {
        // 开始时间
        long start = System.currentTimeMillis();
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new HSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum+","+cellNum);
            }
        }
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03BigData.xls");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        System.out.println("03大数据量表生成成功!");
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println("用时:"+((end-start)/1000)+"秒");
    }
}

Java使用POI导出Excel
(2)XSSFWorkbook

直接使用以上示例来测试即可,然后将Workbook 换成XSSFWorkbook

缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条。
优点:可以写较大的数据量,如20万条。

Java使用POI导出Excel
(3)SXSSFWorkbook

Java使用POI导出Excel

注意:

  • 过程中产生临时文件,需要清理临时文件。
  • 默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时件。
  • 如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)

3.3. 测试rowAccessWindowSize

对于不一样的rowAccessWindowSize值,进行耗时测试。

例子:生成三个SHEET,每一个SHEET有 200000 行记录,共60万行记录flex

  • rowAccessWindowSize:1的时候执行是30s
  • rowAccessWindowSize:100的时候执行是34s
  • rowAccessWindowSize:200的时候执行是51s
  • rowAccessWindowSize:5000的时候执行是326s
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.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

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

public class ClassSXSSFWorkBookUtil {

    public static void main(String[] args) throws IOException {
        long curr_time = System.currentTimeMillis();
        // 内存中缓存记录行数
        int rowAccess = 100;
        SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess);
        // 生成3个SHEET
        int sheetNum = 3;

        for (int i = 0; i < sheetNum; i++) {
            Sheet sh = wb.createSheet();
            // 每一个SHEET有 200000 ROW
            for (int rowNum = 0; rowNum < 200000; rowNum++) {
                Row row = sh.createRow(rowNum);
                //每行有10个CELL
                for (int cellnum = 0; cellnum < 10; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
                // 每当行数达到设置的值就刷新数据到硬盘,以清理内存,这块本质上其实不加这个poi在达到阀值也会向临时文件写数据,
                // 假如导出60w数据3个sheet,加上手动刷新是34s,然后不加是40s,所以在一定程度上来讲手动刷新要快一点
                if (rowNum % rowAccess == 0) {
                    ((SXSSFSheet) sh).flushRows();
                }
            }
        }
        FileOutputStream os = new FileOutputStream("D:\\poi\\biggrid.xlsx");
        wb.write(os);
        os.close();
        System.out.println("耗时(秒):" + (System.currentTimeMillis() - curr_time) / 1000);
    }
}

这个测试出来的结果跟电脑配置有很大关系,实际开发当中,可以采取这种方式然后看看设置多少比较快,然后进行优化!

3.4. 导出Excel样式设置

Java使用POI导出Excel

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

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

public class Test {
    public static void main(String[] args) throws IOException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立sheet对象
        HSSFSheet sheet = wb.createSheet("成绩表");

        // 设置列宽
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 25 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.setColumnWidth(4, 25 * 256);

        // 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可:cell.setCellStyle(cellStyle);
        // 合并单元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置边框(一般标题不设置边框,是标题下的所有表格设置边框)
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        //在sheet里创建第一行,参数为行索引
        HSSFRow row1 = sheet.createRow(0);
        // 合并单元格:参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

        // 创建单元格
        HSSFCell cell = row1.createCell(0);
        cell.setCellStyle(cellStyle);
        //设置单元格内容
        cell.setCellValue("学生成绩表");


        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("语文成绩");
        row2.createCell(3).setCellValue("数学成绩");
        row2.createCell(4).setCellValue("英语成绩");

        //在sheet里创建第三行
        HSSFRow row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("小明");
        row3.createCell(1).setCellValue("1班");
        row3.createCell(2).setCellValue(80);
        row3.createCell(3).setCellValue(75);
        row3.createCell(4).setCellValue(88);

        //在sheet里创建第四行
        HSSFRow row4 = sheet.createRow(3);
        row4.createCell(0).setCellValue("小红");
        row4.createCell(1).setCellValue("1班");
        row4.createCell(2).setCellValue(82);
        row4.createCell(3).setCellValue(70);
        row4.createCell(4).setCellValue(90);

        FileOutputStream fileOutputStream = new FileOutputStream("D:\\poi\\04.xlsx");
        wb.write(fileOutputStream);
        fileOutputStream.close();
    }
}

四、POI - 读

当你企图使用SXSSFWorkbook去加载一个已存在的Excel模板时,首先你应该用XSSFWorkbook去获取它 ,以下列举了常用的四种获取XSSFWorkbook的方式。

XSSFWorkbook(java.io.File file)
XSSFWorkbook(java.io.InputStream is)
XSSFWorkbook(OPCPackage pkg)
XSSFWorkbook(java.lang.String path)

4.1.代码示例

使用SXSSFWorkbook写的文档,必须使用SXSSFWorkbook来读,否则报错!同样HSSFWorkbook写入也必须用HSSFWorkbook读取!当然SXSSFWorkbook是不能用来读取的!

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 java.io.FileInputStream;
import java.io.IOException;

public class ExcelReadTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        FileInputStream fileInputStream = new FileInputStream(path + "03.xlsx");
        // 1.创建一个工作簿。使得excel能操作的,这边他也能操作。
//        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Workbook workbook = new XSSFWorkbook(fileInputStream);

        // 2.得到表。
        Sheet sheet = workbook.getSheetAt(0);
        // 3.得到行。
        Row row = sheet.getRow(0);
        // 4.得到列。
        Cell cell = row.getCell(0);
        // 读取值。一定要注意类型,否则会读取失败
        System.out.println(cell.getStringCellValue());// 字符串类型
        Cell cell1 = row.getCell(1);
        System.out.println(cell1.getNumericCellValue());// 数字类型
        // 5.关闭流。
        fileInputStream.close();
    }
}

4.2.读取不同的数据类型

这里重点会用到CellType枚举类,就是获取当前单元格的类型,CellType cellType = cell.getCellType();,旧版本poi直接获取的是int值,int cellType = cell.getCellType();,这块还是有一定的区别的,但是枚举都是用的这个类。

  • _NONE(-1), // none类型
  • NUMERIC(0), // 数值类型
  • STRING(1), // 字符串类型
  • FORMULA(2), // 公式类型
  • BLANK(3), // 空格类型
  • BOOLEAN(4), // 布尔类型
  • ERROR(5); // 错误
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelReadTestType {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.获取文件流
        FileInputStream fileInputStream = new FileInputStream(path + "会员消费商品明细表.xls");
        // 2.创建一个工作簿。使用excel能操作的这边他也可以操作。
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Workbook workbook = new XSSFWorkbook(fileInputStream);
        // 3.获取第一张表。
        Sheet sheet = workbook.getSheetAt(0);
        // 4.获取标题内容。
        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) {
                    System.out.print(cell.getStringCellValue() + "|");
                }
            }
            System.out.println();
        }
        // 5.获取表中的记录
        // 获取有多少行记录
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            // 获取每一行记录
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                // 读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    // 获得单元格
                    Cell cell = rowData.getCell(cellNum);
                    // 匹配列的数据类型
                    String cellValueByCell = getCellValueByCell(cell);
                    System.out.println(cellValueByCell);
                }
            }
            System.out.println("----");
        }
        fileInputStream.close();
    }


    //获取单元格各类型值,返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();
        switch (cellType) {
            // 数字
            case NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {
                        // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        // 日期
                        cellValue = sdf.format(cell.getDateCellValue());
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    // 数值 这种用BigDecimal包装再获取plainString,可以防止获取到科学计数值
                    cellValue = bd.toPlainString();
                }
                break;
            // 字符串
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            // Boolean
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                break;
            // 公式
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            // 空值
            case BLANK:
                cellValue = "";
                break;
            // 故障
            case ERROR:
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }
}
  • getPhysicalNumberOfRows()获取的是物理行数,也就是不包括空行(隔行)的情况。
  • getLastRowNum()获取的是最后一行的编号(编号从0开始)

注意:日常中我们进行POI读取导入EXCEL表格操作时,一定要保证工作薄干净,即有效数据区域外的单元格千万不要动。不然可能会出现,明明Excel有两条数据,但是读出来好多空格内容,往往就是我们不小心动了别的单元格导致,然后肉眼还看不出来,但是getPhysicalNumberOfRows获取行数就会有好几行空格内容!

4.3.读取公式

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

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

public class GS {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        FileInputStream fileInputStream = new FileInputStream(path + "计算公式.xls");
        // 1.创建一个工作簿。使得excel能操作的,这边他也能操作。
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 2.得到表。
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        // 拿到计算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        // 输出单元格内容
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA:
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                // 计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }
}

Java使用POI导出Excel
Java使用POI导出Excel

五、POI - 遇到的坑

5.1.为什么模板中的数据获取不到?

根据我对SXSSFWorkbook的了解,它只会加载一部分数据到内存,其余的数据全部持久化到本次磁盘。

但是当你噼里啪啦对SXSSFWorkbook进行了一顿操作时,你会忽然发现为什么SXSSFSheet.getRow(0) = null???

这是因为这些记录存在于硬盘当中!文章来源地址https://www.toymoban.com/news/detail-439716.html

到了这里,关于Java使用POI导出Excel的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Java使用poi导出excel针对不同数据列配置设置不同单元格格式(适用于通用导出excel数据)

    公司大部分业务都是查询相关的业务, 所以建了一个项目专门做数据查询, 数据中转等抽象通用的业务, 有一天给我安排了一个功能, 做excel导出, 配置好查询sql和表头字段映射后即可导出excel, 无需修改代码 后来因为导出数据要求保留几位小数或者转换成百分比等设置单元格格

    2024年02月07日
    浏览(52)
  • java poi导入Excel、导出excel

    java poi导入Excel、导出excel ReadPatientExcelUtil PoiUtils FileUtils

    2024年02月15日
    浏览(40)
  • Java Poi导出Excel表格详解

    一、导出下面的表格 二、流程详解         1、导出excel需要先将数据准备好         2、创建工作傅对象SXSSFWorkbook         3、使用工作傅对象创建sheet对象(工作页)         4、使用sheet对象创建行对象row(行对象)         5、使用row对象创建cell对象(单元格

    2024年02月10日
    浏览(48)
  • java poi实现Excel多级表头导出

    最近碰到一个导出,比较繁琐,也查询了许多博客,在其中一篇博客的基础上修改,实现了最终想要的效果。话不多说,直接上效果图 1.主代码: 2.合并单元格 3.设置表头单元格的宽度 4.填充数据(注:我这里的数据格式是ListMapString, Object类型,可以根据自己的实际情况来封

    2024年02月03日
    浏览(45)
  • Java原生POI实现的Excel导入导出(简单易懂)

    首先是Controller入口方法 这个接口在postman上传参是下面这样的: 注意里面的参数名称要和接口上的一致,不然会拿不到值 还有file那里key的类型要选file类型的,这样就可以在后面value里面选择文件 然后是Service方法 首先是Controller入口 strJson是用来接受其它参数的,一般导出的

    2024年02月11日
    浏览(44)
  • Java excel poi 使用HSSFWorkbook 导出的excel wps能打开office打不开问题解决 Excel无法打开xx.xlsx,因为文件格式或扩展名无效......

    1.在开发代码中涉及到报表导出 xlsx文件 office打不开问题 JavaPOI导出Excel有三种形式,他们分别是 1.HSSFWorkbook 2.XSSFWorkbook 3.SXSSFWorkbook。 pom文件如下 检查创建sheet代码如下 代码中用了 HSSFworkbook 去创建Sheet 导致office打不开原因就在这里 HSSFworkbook 解释如下: HSSFWorkbook:是操作Exc

    2024年02月16日
    浏览(48)
  • Java POI (2)—— Excel文件的上传与导出(实例演示)

             这里是一个demo的流程图,下面按照这个流程图做了一个简单的实现,有部分判断没有加上,实际操作中,可以根据自己的需求进行增加或者修改。并且此处还是在接受文件传入后将文件进行了下载,保存到本地的操作,这个要按照具体情况具体分析,看需求是否

    2024年02月11日
    浏览(49)
  • Java POI导出Excel时,合并单元格没有边框的问题

    今天用POI导出Excel的时候,发现导出的单元格确少边框,最后发现有2个方案可以解决。 CellRangeAddress的4个参数分别表示:起始行号,终止行号, 起始列号,终止列号

    2024年02月14日
    浏览(45)
  • java poi导出excel单元格设置自定义背景颜色(任意颜色)

    一、思考过程(看代码的移步第二点) 现有方法 现有资料多为使用 IndexedColors 设置颜色, 但是IndexedColors能设置的颜色有限 ,而需求中所要颜色都是花里胡哨的,需要真正的自定义; 而颜色的本质是rgb ,所以只要我们能自己设置rgb的值就能获取任意想要的颜色了; 源码分

    2023年04月10日
    浏览(42)
  • Java POI导出Word、Excel、Pdf文档(可在线预览PDF)

    1、导入依赖Pom.xml        dependency             groupIdorg.apache.poi/groupId             artifactIdpoi/artifactId             version3.14/version         /dependency 2、Controller   3、Service a、pdfService b、wordService c、excelService  4、Utils 5、模板截图   6、前端

    2024年02月08日
    浏览(51)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包