一、概念理解
以.xls结尾的是03版本的excel。以.xlsx结尾的是07版本的excel。
ecxel中创建工作薄的对象有三个,分别是:
HSSFWorkbook:03版本
XSSFWorkbook:07版本
SXSSFWorkbook:07版本加强版
在创建workbook的时候使用不同的工作簿对象来创建即可。
二、在项目中导入依赖
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.12.2</version>
</dependency>
三、excel写出
package com.kt;
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.joda.time.DateTime;
import java.io.File;
import java.io.FileOutputStream;
public class ExcelUtil {
public static void main(String[] args) throws Exception {
writeExcel();
}
private static final String PATH = "D:"+ File.separator +"testTogit"+File.separator+"untitledExcel"+File.separator;
public static void writeExcel() throws Exception {
// 创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 创建一个sheet页
Sheet sheet = workbook.createSheet("测试excel");
// 创建行
Row row = sheet.createRow(0);
// 创建单元格
Cell cell0 = row.createCell(0);
// 设置数据
cell0.setCellValue("简单测试");
Cell cell1 = row.createCell(1);
cell1.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 使用流写出workbook
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试excel.xlsx");
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
workbook.close();
}
}
四、读入excel
package com.kt;
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.FileInputStream;
public class ExcelUtil {
public static void main(String[] args) throws Exception {
readExcel();
}
private static final String PATH = "D:"+ File.separator +"testTogit"+File.separator+"untitledExcel"+File.separator;
public static void readExcel() throws Exception {
// 获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH+"测试excel.xlsx");
// 得到工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
// 得到sheet页
Sheet sheet = workbook.getSheetAt(0);
// 得到行
Row row = sheet.getRow(0);
// 得到列
Cell cell = row.getCell(0);
// 得到列的值,并打印
System.out.println(cell.getStringCellValue());
// 关流
fileInputStream.close();
workbook.close();
}
}