一.简介
JavaPOI是由Apache免费提供的针对微软Office文档操作的JavaAPI.
对应的Jar包依赖是:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
Poi包结构
HSSF一读写Microsoft Excel XLS==>HSSF对应97-2003版本的Excel
XSSF一读写Microsoft Excel OOXML XLSX==>XSSF则对应2007版本的Excel
HWPF一读写Microsoft Word Doc
HSLF一提供读写Microsoft PowerPoint
本教程我们所使用的是XSSF来操作Excel。
二.入门案例
首先了解我们的POI的使用对象(如上图)。
1.读案例
我们测试读取下面文档内容:
package com.jack;
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.IOException;
public class Reader {
public static void main(String[] args) throws IOException {
//1.获取工作簿
XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\Administrator\\Desktop\\test1.xlsx");
//2.获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//3.获取行
for(Row row:sheet){
//4.获取单元格
for (Cell cell : row) {
System.out.print(cell.getStringCellValue()+" ");
}
System.out.println();
}
}
}
运行输出结果:
I am jack
I am tom
I am rose
Process finished with exit code 0
另外的写法(不用增强for循环):
package com.jack;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
public class Reader {
public static void main(String[] args) throws IOException {
//1.获取工作簿
XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\Administrator\\Desktop\\test1.xlsx");
//2.获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//3.获取最后一行的编号,编号从0开始
int lastRowNum = sheet.getLastRowNum();
for(int i=0;i<=lastRowNum;i++){
XSSFRow row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
for(int j=0;j<lastCellNum;j++){
//4.获取当前行最后一个单元格的编号,从1开始,这里注意比较蛋疼是1开始的
XSSFCell cell = row.getCell(j);
System.out.print(cell.getStringCellValue()+" ");
}
System.out.println();
}
workbook.close();
}
}
2.写案例
接下来测试写入内容到excel中:
package com.jack;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class Reader {
public static void main(String[] args) throws IOException {
//1.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//2.创建工作表
XSSFSheet sheet = workbook.createSheet();
//3.创建行
XSSFRow row = sheet.createRow(0);
//4,创建单元格
row.createCell(0).setCellValue("I am Rose!!!");
row.createCell(1).setCellValue("I am Jack!!!");
row.createCell(2).setCellValue("I am Tom!!!");
XSSFRow row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("I am Jack");
row1.createCell(1).setCellValue("I am Tom");
row1.createCell(2).setCellValue("I am Rose");
FileOutputStream outputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test.xlsx");
//5.写入数据
workbook.write(outputStream);
outputStream.flush();
//6.释放资源
outputStream.close();
workbook.close();
}
}
三.实战练习
链接:https://pan.baidu.com/s/1yHRWgH1MqdcCRm7GBf9apA
提取码:ck7y
1.Excel数据存放到数据库
2.数据库数据存放到Excel
3.样式添加