不废话，直接上代码，注意避坑，如果excel row值为时间，如：12:00 格式，记得不要设定为时间，设定为常规
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.springframework.web.multipart.MultipartFile;
import lombok.extern.slf4j.Slf4j;
/**
* @ClassName: ExcelUtils
* @Author: xiaox
* @Description:
* @Date: 2023/2/20 10:00
* @Version: 1.0
*/
@Slf4j
public class ExcelUtils {
public final static List<List<String>> getUpd(MultipartFile file, int startindex) throws IOException {
Workbook wb = null;
InputStream inputStream = null;
try {
String extension = file.getOriginalFilename().lastIndexOf(".") == -1 ? ""
: file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
if ("xls".equals(extension) || "XLS".equals(extension)) {// 2003
inputStream = file.getInputStream();
wb = new HSSFWorkbook(inputStream);
} else if ("xlsx".equals(extension) || "XLSX".equals(extension)) {// 2007
inputStream = file.getInputStream();
wb = new XSSFWorkbook(inputStream);
} else {
throw new IOException("不支持的文件类型:" + extension);
}
Sheet sht = wb.getSheetAt(0);
List<List<String>> result = new ArrayList<>(sht.getLastRowNum() + 1);
for (int i = startindex; i <= sht.getLastRowNum(); i++) {
int num = sht.getRow(0).getLastCellNum();
Row row = sht.getRow(i);
if (row != null) {
List<String> datas = new ArrayList<>(row.getLastCellNum());
for (int j = 0; j < num; j++) {
Cell cell = row.getCell(j);
String value = null;
if(null != cell) {
switch (cell.getCellType()) {
case STRING: // 字符串
value = String.valueOf(cell.getStringCellValue());
break;
case NUMERIC: // 数字
if (DateUtil.isCellDateFormatted(cell)) {
short format = cell.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if(format == 21){
sdf = new SimpleDateFormat("HH:mm:ss");
}else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
double cellValue = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(cellValue);
value = sdf.format(date);
}else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
try {
value = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
}finally{
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
value = bd.toPlainString();// 数值 用BigDecimal包装再获取plainString，可以防止获取到科学计数值
}
break;
case BLANK:
value = "";
break;
default:
break;
}
datas.add(value);
}
}
if(ObjectUtils.isNotEmpty(datas)) {
result.add(datas);
}
}
}
return result;
} finally {
if (null != inputStream) {
inputStream.close();
}
}
}
}
