Apache POI – Reading and Writing Excel file in Java
来源于:https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/
In this article, we will discuss about how to read and write an excel file using Apache POI
1. Basic definitions for Apache POI library
This section briefly describe about basic classes used during Excel Read and Write.
HSSF
is prefixed before the class name to indicate operations related to a Microsoft Excel 2003 file.XSSF
is prefixed before the class name to indicate operations related to a Microsoft Excel 2007 file or later.XSSFWorkbook
andHSSFWorkbook
are classes which act as an Excel WorkbookHSSFSheet
andXSSFSheet
are classes which act as an Excel WorksheetRow
defines an Excel rowCell
defines an Excel cell addressed in reference to a row.
2. Download Apache POI
Apache POI library is easily available using Maven Dependencies.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
3. Apache POI library – Writing a Simple Excel
The below code shows how to write a simple Excel file using Apache POI libraries. The code uses a 2 dimensional data array to hold the data. The data is written to a XSSFWorkbook
object. XSSFSheet
is the work sheet being worked on. The code is as shown below:
package com.mkyong;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApachePOIExcelWrite {
private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";
public static void main(String[] args) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
Object[][] datatypes = {
{"Datatype", "Type", "Size(in bytes)"},
{"int", "Primitive", 2},
{"float", "Primitive", 4},
{"double", "Primitive", 8},
{"char", "Primitive", 1},
{"String", "Non-Primitive", "No fixed size"}
};
int rowNum = 0;
System.out.println("Creating excel");
for (Object[] datatype : datatypes) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : datatype) {
Cell cell = row.createCell(colNum++);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}
try {
FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
workbook.write(outputStream);
workbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("Done");
}
}
On executing the above code, you get below excel as an output.
4. Apache POI library – Reading an Excel file
The below code explains how to read an Excel file using Apache POI libraries. The function getCellTypeEnum
is deprecated in version 3.15 and will be renamed to getCellType
from version 4.0 onwards.
package com.mkyong;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
public class ApachePOIExcelRead {
private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";
public static void main(String[] args) {
try {
FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
while (cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
//getCellTypeEnum shown as deprecated for version 3.15
//getCellTypeEnum ill be renamed to getCellType starting from version 4.0
if (currentCell.getCellTypeEnum() == CellType.STRING) {
System.out.print(currentCell.getStringCellValue() + "--");
} else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
System.out.print(currentCell.getNumericCellValue() + "--");
}
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
On executing the above code, you get the below output.
Datatype--Type--Size(in bytes)--
int--Primitive--2.0--
float--Primitive--4.0--
double--Primitive--8.0--
char--Primitive--1.0--
String--Non-Primitive--No fixed size--
References
- Details about deprecation of getCellTypeEnum
- Apache POI reference regarding Deprecation
- Apache POI Maven Repo
- Apache POI API docs
最新文章
- react-redux原理分析
- iOS 运行时
- oracle数据库使用三个月的总结
- Boostrap(2)
- iOS设备通知中心精品推荐消息删除
- 配置ORACLE 客户端连接到数据库
- 新闻客户端nices
- error: Error: No resource found that matches the given name (at &#39;layout_above&#39; with value &#39;@id/btnLayout&#39;).
- 在打包程序中自动安装SQL Server数据库 .
- Hibernate(四)之对象状态及一级缓存
- Yourphp是一款完全开源免费的.核心采用了Thinkphp框架
- 一文读懂阻塞、非阻塞、同步、异步IO
- 在xp下无人值守自动安装系统
- (转)App.Config详解及读写操作
- nil
- zabbix_get无法执行agent端的脚本文件解决办法
- Field Security Profile Helper
- link和@import的区别是什么 ?
- CSS浮动为什么不会遮盖同级元素
- define用于条件编译
热门文章
- Android定制争夺战 三大主流ROM横评
- .NET:CLR via C# A Brief Look at Metadata
- git 分支管理策略 与 物理实现 --author by阮一峰 &; 小鱼
- matlab 投影
- [Android Pro] AtomicInteger的用法
- Java:java+内存分配及变量存储位置的区别
- OpenCV学习(40) 人脸识别(4)
- 系统运维技巧(三)——利用dd命令临时增加交换分区
- Terrain tessellation &;&;Threaded Rendering Vk
- Objective-C-Category类别