<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
为了测试,也可以加入测试依赖:
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
为了减少实体类的代码,也可以加入lombok的依赖:
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
@Test
void testImport() throws IOException, InvalidFormatException {
String fileName = "F:\\test.xlsx";
List<Object[]> list = new ArrayList<>();
InputStream inputStream = new FileInputStream(fileName);
Workbook workbook = WorkbookFactory.create(inputStream);
int numberOfSheets = workbook.getNumberOfSheets();
System.out.println("numberOfSheets="+numberOfSheets);
Sheet sheet = workbook.getSheetAt(0);
//获取sheet的行数
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
//过滤表头行
if (i == 0) {
continue;
}
//获取当前行的数据
Row row = sheet.getRow(i);
Object[] objects = new Object[row.getPhysicalNumberOfCells()];
int index = 0;
for (Cell cell : row) {
if (cell.getCellType().equals(CellType.NUMERIC)) {
objects[index] = (int) cell.getNumericCellValue();
}
if (cell.getCellType().equals(CellType.STRING)) {
objects[index] = cell.getStringCellValue();
}
if (cell.getCellType().equals(CellType.BOOLEAN)) {
objects[index] = cell.getBooleanCellValue();
}
if (cell.getCellType().equals(CellType.ERROR)) {
objects[index] = cell.getErrorCellValue();
}
index++;
}
list.add(objects);
}
System.out.println(list);
}
@Test
void testPoiImport() throws IOException {
//--1 创建一个不存在的excel文件
String fileName = "";
Workbook wb = null;
if(fileName.endsWith(".xls")) {
wb = new HSSFWorkbook();
} else if(fileName.endsWith(".xlsx")) {
wb = new XSSFWorkbook();
} else {
System.out.println("创建文件的格式错误");
return;
}
try{
OutputStream output = new FileOutputStream(fileName);
wb.write(output);
}catch(FileNotFoundException e) {
System.out.println("文件创建失败,失败原因为:" + e.getMessage());
throw new FileNotFoundException();
}
System.out.println(fileName + "文件创建成功!");
//--2 创建sheet
String sheetName = "学生信息";
Sheet sheet = wb.getSheet(sheetName);
if(sheet == null) {
System.out.println("表单" + sheetName + "不存在,试图创建该sheet,请稍后……");
sheet = wb.createSheet(sheetName);
System.out.println("名为" + sheetName +"的sheet创建成功!");
}
//--3 创建行
int rowNum = 1;
Row row = sheet.getRow(rowNum);
if(row == null) {
System.out.println("行号为:" + rowNum + "的行不存在,正试图创建该行,请稍后……");
row = sheet.createRow(rowNum);
System.out.println("行号为:" + rowNum + "的行创建成功!");
}
//--4 创建单元格
int cellNum = 1;
Cell cell = row.getCell(cellNum);
if(cell == null) {
System.out.println("该单元格不存在,正在试图创建该单元格,请稍后……");
cell = row.createCell(cellNum);
System.out.println("该单元格创建成功!");
}
System.out.println("excel导出成功");
}