依赖
<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); } }
