使用POI做数据导入首先需要添加POI依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
测试用例
package poitest;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.junit.Test;
import java.io.FileInputStream;
/**
* @Author: mol
* @Description:
* @Date: create in 8:56 2018/3/15
*/
public class POITest {
private String fileName = "E:\\corner\\下载 (12).xls";
@Test
public void testPOi()throws Exception{
//判断文件是否是Excel表格
if(fileName.endsWith(".xls") || fileName.endsWith(".xlsx")){
//判断Excel版本
boolean is03Excel = fileName.endsWith(".xls");
FileInputStream fileInputStream = new FileInputStream(fileName);
//根据不同的版本创建对应的工作簿
Workbook workbook = is03Excel?new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
//根据下标获取工作表
Sheet sheet = workbook.getSheetAt(0);
//获取工作表最后一行的索引
int lastRowNum = sheet.getLastRowNum();
System.out.println(lastRowNum);
//循环取出每一行
for(int rowNum = 2;rowNum < sheet.getLastRowNum(); rowNum++){
//获取当前索引行
Row row = sheet.getRow(rowNum);
//获取当前行中第一个单元格row.getCell(0)获取这个单元格的内容row.getCell(0).getStringCellValue()
System.out.println("单位:"+row.getCell(0).getStringCellValue());
//Cell.CELL_TYPE_STRING将单元格的内容设为String类型 否则可能会报 Cannot get a text value from a numeric cell
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
System.out.println("已导入学生人数:"+row.getCell(1).getStringCellValue());
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
System.out.println("考试人数:"+row.getCell(2).getStringCellValue());
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
System.out.println("学习人数:"+row.getCell(3).getStringCellValue());
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
System.out.println("及格率:"+row.getCell(4).getStringCellValue());
}
}
}
}