1、pom.xml引入相关依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.18</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>
2、读取Excel
/**
* 读取excel
* @param file excel文件
* @param Suffix 文件后缀名,判断是不是excel文件,区分excel版本
* @return
*/
public static Workbook readExcel(File file, String Suffix){
Workbook wb = null;
if(file==null){
return null;
}
InputStream is = null;
try {
is = new FileInputStream(file);
if("xls".equals(Suffix)){
return wb = new HSSFWorkbook(is);
}else if("xlsx".equals(Suffix)){
return wb = new XSSFWorkbook(is);
}else{
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
3、读取文件的第一列数据
/**
* 获取excel第一个sheet的第一行数据
* @param file
* @param suffix
* @return
*/
public static LinkedHashSet<String> getTopRow(File file, String suffix){
LinkedHashSet<String> result = new LinkedHashSet<>();
Workbook wb = readExcel(file, suffix); //文件
Sheet sheet = wb.getSheetAt(0); //sheet
Row row = sheet.getRow(0);
for (int i=0; i<row.getLastCellNum(); i++){
String cellData = (String) getCellFormatValue(row.getCell(i));
result.add(cellData.replaceAll(" ", ""));
}
return result;
}
4、私有方法getCellFormatValue转换单元格的值
private static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判断cell类型
CellType cellType = cell.getCellType();
switch(cellType){
case NUMERIC:{
if(DateUtil.isCellDateFormatted(cell)){
//excel文件内的日期列若设置单元格格式为日期,读取的值和文件内看到的不一样,
//需要根据num对应的格式进行转换,最好是直接在上传页面强制要求单元格格式为文本一劳永逸,
//因为num对应的日期格式不同版本的jar包不一样,成本太高但是工期够就无所谓了可慢慢研究。
short num = cell.getCellStyle().getDataFormat();
String format = ExcelConstant.dateFormatMap.get(num);
SimpleDateFormat df = new SimpleDateFormat(format);
cellValue = df.format(cell.getDateCellValue());
}else{
cell.setCellType(CellType.STRING); //将数值型cell设置为string型
cellValue = cell.getStringCellValue();
}
break;
}
case FORMULA:{
//判断cell是否为日期格式
if(DateUtil.isCellDateFormatted(cell)){
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
}else{
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = cell.getRichStringCellValue().getString();
break;
}
}else{
cellValue = "";
}
return cellValue;
}