使用ApachePOI进行Excel文件的操作
事前准备
public class ApachePOITest {
//文件路径
public static final String PATH = "D:\\1.xlsx";
//模拟数据
private final List<Student> list;
//打印日志
private final Logger logger = Logger.getLogger(ApachePOITest.class.getName());
public ApachePOITest() {
list = new ArrayList<>();
list.add(new Student("瑟夫", 25));
list.add(new Student("稣法", 26));
list.add(new Student("维斯", 22));
list.add(new Student("捷特", 19));
}
//内部类
static class Student {
String name;
int age;
public Student(String name, int age) {
this.name = name;
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", age=" + age +
'}';
}
}
}
文件写入
void write() {
//创建一个excel
try (XSSFWorkbook work = new XSSFWorkbook();) {
//创建一个sheet
XSSFSheet sheet = work.createSheet();
//创建一个row
XSSFRow rowHead = sheet.createRow(0);
//设置第一行
rowHead.createCell(0).setCellValue("姓名");
rowHead.createCell(1).setCellValue("年龄");
for (int i = 1; i < list.size() + 1; i++) {
XSSFRow row = sheet.createRow(i);
row.createCell(0).setCellValue(list.get(i - 1).name);
row.createCell(1).setCellValue(list.get(i - 1).age);
}
work.write(Files.newOutputStream(Paths.get(PATH)));
logger.info("文件写入完成");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
文件读取
void read() {
ArrayList<Student> students = new ArrayList<>();
try (FileInputStream fileInputStream = new FileInputStream(PATH);) {
XSSFWorkbook excel = new XSSFWorkbook(fileInputStream);
//获得第一个工作表
XSSFSheet sheetAt = excel.getSheetAt(0);
//获得最后一行的行号
int lastRowNum = sheetAt.getLastRowNum();
for (int i = 1; i <= lastRowNum; i++) {
//获得当前行
XSSFRow row = sheetAt.getRow(i);
//获取最后一列的了列号 short lastCellNum = row.getLastCellNum();
Student student = new Student(row.getCell(0).getStringCellValue(), (int) row.getCell(1).getNumericCellValue());
students.add(student);
}
excel.close();
logger.info("文件读取完成: " + students);
} catch (IOException e) {
throw new RuntimeException(e);
}
}