文章目录
Java-POI使用
一、前言
- Apache POI-Microsoft文档的Java API
- JDK:
1.8
- POI:
4.11
参考:
环境说明:https://poi.apache.org/devel/index.html
Component Map - 对象概述、依赖说明:https://poi.apache.org/components/index.html
上手指南:https://poi.apache.org/components/spreadsheet/quick-guide.html
二、记录
1.依赖包选择
应用 | 组件 | 依赖包 |
---|---|---|
OLE2 Filesystem | POIFS | poi |
OLE2 Property Sets | HPSF | poi |
Excel XLS | HSSF | poi |
Escher common drawings | DDF | poi |
PowerPoint PPT | HSLF | poi-scratchpad |
Word DOC | HWPF | poi-scratchpad |
Visio VSD | HDGF | poi-scratchpad |
Publisher PUB | HPBF | poi-scratchpad |
Outlook MSG | HSMF | poi-scratchpad |
WMF drawings | HWMF | poi-scratchpad |
PowerPoint PPT and PPTX | Common SL | poi-scratchpad and poi-ooxml |
Excel XLS and XLSX | Common SS | poi-ooxml |
Excel XLSX | XSSF | poi-ooxml |
PowerPoint PPTX | XSLF | poi-ooxml |
Word DOCX | XWPF | poi-ooxml |
Visio VSDX | XDGF | poi-ooxml |
OOXML | OpenXML4J | poi-ooxml plus either poi-ooxml-schemas or ooxml-schemas and ooxml-security |
- 全部依赖(前三个是重点)
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/ooxml-security -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-security</artifactId>
<version>1.1</version>
</dependency>
2.Excel读取
- 同时支持
.xls
和.xlsx
格式
package com.demo.poi;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.IOException;
public class POIExcelDemo {
public static void main(String[] args) {
try {
String path = "E://index.xlsx"; // Excel路径
File file = new File(path);
// 读取文档
Workbook workbook = WorkbookFactory.create(file);
// 读取第一页数据
Sheet sheet = workbook.getSheetAt(0);
// 循环行数据
for(int i=0; i<=sheet.getLastRowNum(); i++){
Row row = sheet.getRow(i);
// 循环列数据
for(int j=0; j<row.getLastCellNum(); j++){
Cell cell = row.getCell(j);
Object content = null;
// 判断数据类型
if(cell != null){
if(cell.getCellType().equals(CellType.NUMERIC)){ // 数字
if(DateUtil.isCellDateFormatted(cell)){ // 日期时间
content = cell.getDateCellValue();
}else{
content = cell.getNumericCellValue();
}
}else if(cell.getCellType().equals(CellType.STRING)){ // 字符串
content = cell.getStringCellValue();
}else if(cell.getCellType().equals(CellType.BLANK)){ // 空值
content = "";
}else if(cell.getCellType().equals(CellType.BOOLEAN)){ // 布尔
content = cell.getBooleanCellValue();
}else{ // CellType.FORMULA 或者 CellType.ERROR
content = cell.getStringCellValue();
}
}
System.out.println("读取第 "+j+" 列,第 "+i+" 行="+content);
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.Excel导出
package com.demo.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;
import java.util.*;
public class POIExcelExpDemo {
public static void main(String[] args) {
POIExcelExpDemo poiExcelExpDemo = new POIExcelExpDemo();
List<Map<String, Object>> mapList = new LinkedList<>();
for(int i=0; i<10; i++){
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", i);
map.put("name", "test-"+1);
map.put("address", i+" 号大街");
mapList.add(map);
}
String filePath = "F:/test.xlsx";
poiExcelExpDemo.exportExcel(mapList, filePath);
}
/**
* 导出到Excel文件
* @param mapList 数据集合
* @param filePath 文件保存路径
* */
public void exportExcel(List<Map<String, Object>> mapList, String filePath){
if(mapList == null || mapList.size() == 0){
System.out.println("数据对象为空或无可操作数据.");
return; // 跳出方法
}
// 创建工作簿
Workbook workbook = null;
if(filePath.endsWith("xlsx")){
workbook = new XSSFWorkbook();
}else if(filePath.endsWith("xls")){
workbook = new HSSFWorkbook();
}else {
System.out.println("当前文件格式不支持.【仅支持xls和xlsx】");
return; // 跳出方法
}
// 创建表
Sheet sheet = workbook.createSheet("sheet 1");
// 创建行
for(int i=0; i<=mapList.size(); i++){
Row row = sheet.createRow(i); // 创建行
Map<String, Object> tempMap = null; // 获取行数据
// 设置标题列
if(i==0){
tempMap = mapList.get(0);
Font font = workbook.createFont(); // 创建字体样式
font.setBold(true);
font.setFontHeightInPoints((short) 12);
CellStyle cellStyle = workbook.createCellStyle(); // 创建表格样式
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Iterator<String> keyIterator = tempMap.keySet().iterator();
int t = 0; // 索引
while (keyIterator.hasNext()){
Cell cell = row.createCell(t); // 创建列
cell.setCellStyle(cellStyle);
cell.setCellValue(keyIterator.next()); // 设置值
sheet.autoSizeColumn(t); // 自动调整列的宽度
t++;
}
}else{// 设置值列
tempMap = mapList.get(i-1); // 0 行是标题,第1行的数据,需要减1获取
Iterator<Object> valIterator = tempMap.values().iterator();
int v = 0; // 索引
while (valIterator.hasNext()){
Cell cell = row.createCell(v); // 创建列
Object nextVal = valIterator.next();
cell.setCellValue(nextVal == null ? "" : nextVal.toString()); // 设置值
v++;
}
}
}
// 输出文件
try(OutputStream outputStream = new FileOutputStream(filePath)){
workbook.write(outputStream);
System.out.println("导出完成:"+filePath);
}catch (Exception e){
e.printStackTrace();
}
}
}
4.Word读取
package com.demo.poi;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
public class POIWordDemo {
public static void main(String[] args) {
try {
POIWordDemo poiWordDemo = new POIWordDemo();
String path = "E://index.docx"; // Wordl路径
File file = new File(path);
if(path.indexOf(".docx") != -1){
System.out.println(poiWordDemo.readDocx(file));
}else if(path.indexOf(".doc") != -1){
System.out.println(poiWordDemo.readDoc(file));
}else {
System.out.println("不支持格式");
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取 ‘.doc’ 格式
* @param file 文件
* */
public String readDoc(File file) throws IOException {
// 读取文档
WordExtractor wordExtractor = new WordExtractor(new FileInputStream(file));
return wordExtractor.getText();
}
/**
* 读取 ‘.docx’ 格式
* @param file 文件
* */
public String readDocx(File file) throws IOException {
// 读取文档
XWPFDocument xwpfDocument = new XWPFDocument(new FileInputStream(file));
XWPFWordExtractor xwpfWordExtractor = new XWPFWordExtractor(xwpfDocument);
return xwpfWordExtractor.getText();
}
}
5.PPT读取
package com.demo.poi;
import org.apache.poi.hslf.usermodel.HSLFTable;
import org.apache.poi.hslf.usermodel.HSLFTableCell;
import org.apache.poi.hslf.usermodel.HSLFTextShape;
import org.apache.poi.sl.usermodel.PictureData;
import org.apache.poi.sl.usermodel.Slide;
import org.apache.poi.sl.usermodel.SlideShow;
import org.apache.poi.sl.usermodel.SlideShowFactory;
import org.apache.poi.sl.usermodel.Shape;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xslf.usermodel.XSLFTable;
import org.apache.poi.xslf.usermodel.XSLFTableCell;
import org.apache.poi.xslf.usermodel.XSLFTextShape;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.UUID;
public class POIPPTDemo {
public static void main(String[] args) {
try {
String path = "E://index.pptx"; // PPT路径
SlideShow slideShow = SlideShowFactory.create(new File(path));
// == 读取文字数据
List<Slide> slides = slideShow.getSlides();
// 循环每一页
for(Slide slide : slides){
System.out.println("==================【"+ slide.getSlideName() +"】");
List<Shape> shapes = slide.getShapes(); // 页内容
for(Shape shape : shapes){
if(shape instanceof HSLFTextShape){ // 文本内容[.ppt]
System.out.println(((HSLFTextShape) shape).getText());
}
if(shape instanceof XSLFTextShape){ // 文本内容[.pptx]
System.out.println(((XSLFTextShape) shape).getText());
}
if(shape instanceof HSLFTable){ // 表格内容[.ppt]
int rowNum = ((HSLFTable) shape).getNumberOfRows();
int colNum = ((HSLFTable) shape).getNumberOfColumns();
for(int i=0; i<rowNum; i++){ // 行
for(int j=0; j<colNum; j++){ // 列
HSLFTableCell cell = ((HSLFTable) shape).getCell(i, j);
if(cell != null){
System.out.println(cell.getText());
}
}
}
}
if(shape instanceof XSLFTable){ // 表格内容[.pptx]
int rowNum = ((XSLFTable) shape).getNumberOfRows();
int colNum = ((XSLFTable) shape).getNumberOfColumns();
for(int i=0; i<rowNum; i++){ // 行
for(int j=0; j<colNum; j++){ // 列
XSLFTableCell cell = ((XSLFTable) shape).getCell(i, j);
if(cell != null){
System.out.println(cell.getText());
}
}
}
}
}
}
// == 读取图片数据
List<PictureData> pictureDatas = slideShow.getPictureData();
System.out.println("pic size="+pictureDatas.size());
for(PictureData pictureData : pictureDatas){
byte [] bytes = pictureData.getData();
String picPath = "E://index//"+ UUID.randomUUID()+".jpg";
OutputStream outputStream = new FileOutputStream(new File(picPath));
outputStream.write(bytes);
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
三、其它
1.CellType类不存在
- 原因:低版本不支持
- 解决:可以使用
Cell
获取静态变量,如:Cell.CELL_TYPE_STRING
2.Excel获取行数和列数
方法 | 说明 |
---|---|
sheet.getPhysicalNumberOfRows() | 获取物理行数,排除空行 |
sheet.getLastRowNum() | 获取最后一行的行数,从 0 开始 |
row.getPhysicalNumberOfCells() | 获取物理列数,排除空列 |
row.getLastCellNum() | 获取最后一列的列数,从 1 开始 |
3.读取的日期数字转日期
- 一般使用
DateUtil.isCellDateFormatted(cell)
进行判断,也可直接将数字转换为日期
// 日期格式
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
// poi 工具
Date date = DateUtil.getJavaDate(43441.0);
String dateStr = simpleDateFormat.format( date ) ;
System.out.println(dateStr);