依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
备注：poi-ooxml 已经包含了poi、poi-ooxml-schemas、curvesapi，不需要单独引入
文件格式对应处理类
.xls => HSSFWorkbook
.xlsx => XSSFWorkbook
快速入门
写入文件
package com.demo.excel;
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.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
public class WriteExcel {
public static void main(String[] args) throws IOException {
// 创建工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("名称");
// 行
Row row = sheet.createRow(0);
// 单元格
Cell cell = row.createCell(0);
cell.setCellValue("姓名");
// 写到文件
OutputStream outputStream = new FileOutputStream("demo.xlsx");
workbook.write(outputStream);
outputStream.close();
//关闭
workbook.close();
}
}
读取文件
package com.demo.excel;
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.*;
public class ReadExcel {
public static void main(String[] args) throws IOException {
// 创建工作表
InputStream inputStream = new FileInputStream("demo.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet("名称");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String value = cell.getStringCellValue();
System.out.println(value);
//关闭
inputStream.close();
workbook.close();
}
}
Excel读写工具
package com.demo.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/**
* Excel读写工具，兼容xls 和 xlsx格式
*/
public class ExcelUtil {
/**
* 获取扩展名
*
* @param filename 文件路径
* @return
*/
public static String getSuffix(String filename) {
return filename.substring(filename.lastIndexOf("."));
}
/**
* 兼容xls xlsx文件格式
*
* @param filename 文件路径
* @return
*/
public static Workbook readWorkBook(String filename) throws IOException {
// 获取文件扩展名
String ext = getSuffix(filename);
InputStream inputStream = new FileInputStream(filename);
Workbook workbook = null;
// xls格式的需要使用HSSFWorkbook类来解析
// xlsx格式的需要使用XSSFWorkbook格式来解析
if (".xls".equals(ext.toLowerCase())) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
/**
* 创建一个文件
*
* @param filename
* @return
*/
public static Workbook createWorkBook(String filename) {
// 获取文件扩展名
String ext = getSuffix(filename);
Workbook workbook = null;
// xls格式的需要使用HSSFWorkbook类来解析
// xlsx格式的需要使用XSSFWorkbook格式来解析
if (".xls".equals(ext.toLowerCase())) {
workbook = new HSSFWorkbook();
} else {
workbook = new XSSFWorkbook();
}
return workbook;
}
/**
* 读取Excel 文件
*
* @param filename
* @return 列表数据
* @throws Exception
*/
public static List<List<String>> readExcel(String filename) throws Exception {
// 创建工作簿的对象
Workbook workBook = readWorkBook(filename);
// 定义要返回的数据结构
List<List<String>> list = new ArrayList<>();
// 根据索引获取Sheet对象
Sheet sheet = workBook.getSheetAt(0);
// 定义表头
List<String> rowList;
// 获取所有行内容， 从第0 行开始 base 0
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
rowList = new ArrayList<>();
Row row = sheet.getRow(rowIndex);
// 获取所有列值 base 1
for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
Cell cell = row.getCell(columnIndex);
//首先强制设置成string类型
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue();
// System.out.println(value);
rowList.add(value);
}
// System.out.println(map);
list.add(rowList);
}
// 关闭工作簿
workBook.close();
return list;
}
/**
* 写入到excel
*
* @param filename
* @param list
* @throws Exception
*/
public static void writeExcel(String filename, List<List<Object>> list) throws Exception {
Workbook workbook = createWorkBook(filename);
Sheet sheet = workbook.createSheet();
Row row;
Cell cell;
List<Object> rowList;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i);
rowList = list.get(i);
for (int j = 0; j < rowList.size(); j++) {
cell = row.createCell(j);
String value = String.valueOf(rowList.get(j));
cell.setCellValue(value);
}
}
// 写到文件
OutputStream outputStream = new FileOutputStream(filename);
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
}
测试
package com.demo.excel;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtilTest {
@Test
public void testReadExcel() throws Exception {
String filename = "demo.xlsx";
List<List<String>> list = ExcelUtil.readExcel(filename);
System.out.println(list);
// [[姓名, 年龄], [刘备, 42]]
}
@Test
public void testWriteExcel() throws Exception {
String filename = "demo.xlsx";
List<List<Object>> list = new ArrayList<>();
List<Object> list1 = new ArrayList<>();
list1.add("姓名");
list1.add("年龄");
List<Object> list2 = new ArrayList<>();
list2.add("刘备");
list2.add(42);
list.add(list1);
list.add(list2);
System.out.println(list);
// [[姓名, 年龄], [刘备, 42]]
ExcelUtil.writeExcel(filename, list);
}
}
