Java：apache.poi读写Excel文件
【摘要】 依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version>
</dependency>
12345
备注：poi-...
依赖
<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); }
}
文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。
原文链接：pengshiyu.blog.csdn.net/article/details/110931550
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
评论（0）