package com.demo.spring.util;
import com.demo.spring.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class PoiUtil {
Workbook workbook=null;
public PoiUtil(String path) throws Exception {
InputStream stream=new FileInputStream(new File(path));
int version=(path.endsWith(".xls") ? 2003:2007);
if (version==2003){
workbook=new HSSFWorkbook(stream);
}else {
workbook=new XSSFWorkbook(stream);
}
}
public List<String[]> readContent(int sheetIndex){
List<String[]> list=new ArrayList<>();
int columAmount=0;
Sheet sheet=workbook.getSheetAt(sheetIndex);
if (sheet.getRow(0)!=null){
columAmount=sheet.getRow(0).getLastCellNum();
}
for (Row row:sheet){
String[] singleRow=new String[columAmount];
User user=new User();
int n=0;
for (int i=0;i<columAmount;i++){
Cell cell=row.getCell(i,Row.CREATE_NULL_AS_BLANK);
switch (cell.getCellType()){
case Cell.CELL_TYPE_BLANK:
singleRow[n]="";
break;
case Cell.CELL_TYPE_BOOLEAN:
singleRow[n]=Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)){
singleRow[n]=String.valueOf(cell.getDateCellValue());
}else {
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp=cell.getStringCellValue().trim();
if (temp.indexOf(".")>-1){
singleRow[n]=String.valueOf(new Double(temp));
}else {
singleRow[n]=temp;
}
}
break;
case Cell.CELL_TYPE_STRING:
singleRow[n]=cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_FORMULA:
cell.setCellType(Cell.CELL_TYPE_STRING);
singleRow[n]=cell.getStringCellValue();
if (singleRow[n] != null) {
singleRow[n] = singleRow[n].replaceAll("#N/A", "").trim();
}
break;
case Cell.CELL_TYPE_ERROR:
singleRow[n]="";
break;
default:
singleRow[n] = "";
break;
}
n++;
}
if ("".equals(singleRow[0])) {
continue;
}
list.add(singleRow);
}
return list;
}
public List<User> readContentToEntity(int sheetIndex){
List<User> list=new ArrayList<>();
Sheet sheet=workbook.getSheetAt(sheetIndex);
for (Row row:sheet){
User user=new User();
user.setId((long) row.getCell(0).getNumericCellValue());
user.setName( row.getCell(1).getStringCellValue());
user.setCard( row.getCell(2).getStringCellValue());
user.setGrade( row.getCell(3).getStringCellValue());
user.setGender( row.getCell(4).getStringCellValue());
list.add(user);
}
return list;
}
}
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
推荐:整理的很齐全的execl导入导出操作