万能 excel 解析方法

不废话，直接上代码，注意避坑，如果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;


@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) {
                    // 防止一行的列都为空
                    if(row.getLastCellNum() <= 0) {
						break;
					}
					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;
							}
						}else {
							value = "";
						}
						datas.add(value);
					}
					if (ObjectUtils.isNotEmpty(datas)) {
						result.add(datas);
					}
				}
			}
			return result;
		} finally {
			if (null != inputStream) {
				inputStream.close();
			}
		}
	}
}