需求
需要解析Excel文件
/**
* @Author
* @ClassName AnalysisImportExcelUntil
* @Description 解析上传的excel数据
* @Date 2019/11/23 13:43
* @Version 1.0
*/
public class AnalysisImportExcelUntil {
private static final Logger log = Logger.getLogger(AnalysisImportExcelUntil.class);
/**
* 解析上传的excel, 默认只解析第一张Sheet
*
* @param file excel
* @param startRow 数据开始行
* @return List<String [ ]>
* @throws IOException
*/
public static List<String[]> getExcelData(MultipartFile file, int startRow) throws IOException {
int resultSize = 0;
ArrayList<String[]> resultData = new ArrayList<>(resultSize);
if (!checkFile(file)) {
log.error("上传的excel文件格式有问题");
return resultData;
}
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//获取第一个Sheet页的名字
System.out.println(workbook.getSheetName(0));
if (workbook != null) {
//获取第一张sheet工作表
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
return resultData;
}
// 重新初始化List结果大小
resultSize = sheet.getLastRowNum() + 1;
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了startRow的所有行,如果要循环除第一行以外的就firstRowNum+1
for (int rowNum = firstRowNum + startRow; rowNum <= lastRowNum; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (rowIsEmpty(row)) {
break;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getLastCellNum();
String[] cells = new String[lastCellNum];
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
resultData.add(cells);
}
workbook.close();
}
return resultData;
}
/**
* 检查文件格式
*
* @param file
* @throws IOException
*/
public static boolean checkFile(MultipartFile file) throws IOException {
if (null == file) {
log.error("文件不存在!");
return false;
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
log.error(fileName + "不是excel文件");
return false;
}
return true;
}
/**
* 获取工作簿对象
*
* @param file
* @return
*/
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith("xls")) {
//2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
//2007 及2007以上
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
log.error(e.getMessage());
}
return workbook;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
switch (cell.getCellTypeEnum()) {
case NUMERIC:
//数字
cellValue = stringDateProcess(cell);
break;
case STRING:
//字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN:
//Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
//公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK:
//空值
cellValue = "";
break;
case ERROR:
//故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
public static String stringDateProcess(Cell cell) {
String result = new String();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
return result;
}
/**
* @return * @param null
* @Author
* @Description //TODO 判断excel的row是否全为空
* @Date 2019/12/2 19:30
* @Param
*/
public static boolean rowIsEmpty(Row row) {
if (null == row) {
return true;
}
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellTypeEnum() != BLANK) {
return false;
}
}
return true;
}
}