前言
项目中上传解析Excel的功能,非常普遍,因为方便,接下来就写一个工具类对Excel进行解析
具体方案
引入poi 阿帕奇依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
工具类的书写
public class ExcelUtils {
private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
// 文件类型 后缀
public static final String EXCEL_2003_SUFFIX = ".xls";
public static final String EXCEL_2007_SUFFIX = ".xlsx";
/**
* @Descriptioin: 读取Excel转换为内存中的List,方便校验
* Todo:非常抱歉这个传参有误使用下面的方法
* @param fileName
* @return
private static List<Object[]> readExcelToList(String fileName) {
String suffixName = fileName.substring(fileName.lastIndexOf("."));
InputStream inputStream;
try {
inputStream = new FileInputStream(new File(fileName));
if(EXCEL_2003_SUFFIX.equalsIgnoreCase(suffixName)){
return readOld(inputStream);
}else if(EXCEL_2007_SUFFIX.equalsIgnoreCase(suffixName)){
return readNew(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
return new ArrayList<Object[]>();
}
*/
/**
* @Descriptioin: 读取Excel转换为内存中的List,方便校验
* 本方法是修改后的,可放心使用
* @param fileName
* @return
*/
private static List<Object[]> readExcelToList(MultipartFile file) {
String fileName = file.getOriginalFilename();
String suffixName = fileName.substring(fileName.lastIndexOf("."));
InputStream inputStream;
try {
inputStream = new FileInputStream(new File(fileName));
if(EXCEL_2003_SUFFIX.equalsIgnoreCase(suffixName)){
return readOld(inputStream);
}else if(EXCEL_2007_SUFFIX.equalsIgnoreCase(suffixName)){
return readNew(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
return new ArrayList<Object[]>();
}
/**
* List<Object[]> 返回的是不包含excel标题头 纯数据的集合
*/
public static List<Object[]> readOld(InputStream inputStream) throws IOException {
HSSFWorkbook hssfWorkbook = null;
List<Object[]> list = new ArrayList<Object[]>();
try {
hssfWorkbook = new HSSFWorkbook(inputStream);
HSSFSheet xssfSheet = hssfWorkbook.getSheetAt(0);
HSSFRow hssfRow = null;
HSSFCell hssfCell = null;
Object cellValue = null;
for(int i=1 ; i<= xssfSheet.getPhysicalNumberOfRows();i++){
hssfRow = xssfSheet.getRow(i);
if (null == hssfRow){
continue;
}
Object[] values = new Object[xssfSheet.getRow(0).getLastCellNum()];
for(int j=0; j<=hssfRow.getLastCellNum();j++){
hssfCell = hssfRow.getCell(j);
if(null == hssfCell){
continue;
}
switch (hssfCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = hssfCell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(hssfCell)){
cellValue = DateUtil.getJavaDate(hssfCell.getNumericCellValue());
break;
}else{
cellValue = NumberToTextConverter.toText(hssfCell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:
cellValue = hssfCell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = hssfCell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
default:
cellValue = cellValue.toString();
}
values[j] = cellValue;
}
list.add(values);
}
return list;
} catch (IOException e) {
logger.error("文件格式有误,请检查:{}",e);
return null;
}finally {
if(null != inputStream) inputStream.close();
if(null !=hssfWorkbook) hssfWorkbook.close();
}
}
/**
* List<Object[]> 返回的是不包含excel标题头 纯数据的集合
*/
public static List<Object[]> readNew(InputStream inputStream) throws IOException {
XSSFWorkbook xssfWorkbook = null;
List<Object[]> list = new ArrayList<Object[]>();
try {
xssfWorkbook = new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
XSSFRow xssfRow = null;
XSSFCell xssfCell = null;
Object cellValue = null;
for(int i=1 ; i<= xssfSheet.getPhysicalNumberOfRows();i++){
xssfRow = xssfSheet.getRow(i);
if (null == xssfRow){
continue;
}
Object[] values = new Object[xssfSheet.getRow(0).getLastCellNum()];
for(int j=0; j<=xssfRow.getLastCellNum();j++){
xssfCell = xssfRow.getCell(j);
if(null == xssfCell){
continue;
}
switch (xssfCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = xssfCell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(xssfCell)){
cellValue = DateUtil.getJavaDate(xssfCell.getNumericCellValue());
break;
}else{
cellValue = NumberToTextConverter.toText(xssfCell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_BOOLEAN:
cellValue = xssfCell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = xssfCell.getCellFormula();
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
default:
cellValue = cellValue.toString();
}
values[j] = cellValue;
}
list.add(values);
}
return list;
} catch (IOException e) {
logger.error("文件格式有误,请检查:{}",e);
return null;
}finally {
if(null != inputStream) inputStream.close();
if(null !=xssfWorkbook) xssfWorkbook.close();
}
}
}