java poi读取excel文件（xlsx）
买泛域名SSL证书 送5斤装现摘猕猴桃一箱、同时提供技开源商城搭建免费技术支持。
泛域名ssl证书 239元1年送1个月、单域名39元1年，Sectigo（原Comodo证书）全球可信证书，强大的兼容性，高度安全性,如有问题7天内可退、可开发票
加微信VX 18718058521 备注SSL证书
【腾讯云】2核2G4M云服务器新老同享99元/年，续费同价
泛域名ssl证书 239元1年送1个月、单域名39元1年，Sectigo（原Comodo证书）全球可信证书，强大的兼容性，高度安全性,如有问题7天内可退、可开发票
加微信VX 18718058521 备注SSL证书
【腾讯云】2核2G4M云服务器新老同享99元/年，续费同价
poi读取excel文件
先maven导入jar包
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8</version> </dependency>
读取后缀为“xlsx”的excel文件代码 （“xls”不适用，“xls”得用 HSSFWorkbook）。
/**
* @param file 需要读取的Excel文件
* @param sheetIndex 读取的Excel文件中的表格下标
* @return 数据的坐标，对应的值
*/
public static ArrayList<ArrayList<Object>> readExcel(File file, Integer sheetIndex) {
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
FileInputStream fileInputStream = null;
try {
ArrayList<Object> colList;
fileInputStream = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
XSSFRow row;
XSSFCell cell;
Object value;
for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
colList = new ArrayList<Object>();
//当读取行为空时
if (row == null) {
//判断是否是最后一行，不是最后一行添加上无数据的集合
if (i != sheet.getPhysicalNumberOfRows()) {
rowList.add(colList);
}
continue;
} else {
rowCount++;
}
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
if (j < 0) {
continue;
}
cell = row.getCell(j);
//当该单元格为空
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
//判断是否是该行中最后一个单元格
if (j != row.getLastCellNum()) {
colList.add("");
}
continue;
}
//根据数据类型来获取值
switch (cell.getCellTypeEnum()) {
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if (cell.getCellStyle().getDataFormatString().contains("m/d/yy")) {
value = SimpleDateFormatAddUtil.DateTOString(cell.getDateCellValue());
} else if (cell.getCellStyle().getDataFormatString().contains("yyyy/m/d")) {
value = SimpleDateFormatAddUtil.DateTOString(cell.getDateCellValue());
} else if (cell.getCellStyle().getDataFormatString().equals("General")) {
value = getRealStringValueOfDouble(cell.getNumericCellValue());
} else {
try {
value = getRealStringValueOfDouble(cell.getNumericCellValue());
}catch (Exception e){
value=cell.toString();
}
}
break;
case BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
value = "";
break;
case FORMULA: //公式类型
value = parseFormula(cell);
break;
default:
value = cell.toString();
}
colList.add(value);
}
rowList.add(colList);
}
wb.close();
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
try {
if (null != fileInputStream) {
fileInputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return rowList;
}
/**
* 解析公式
* @param cell - 单元格
* @return String - 结果
*/
public static String parseFormula(Cell cell) {
String data = null;
switch (cell.getCachedFormulaResultTypeEnum()) {
case NUMERIC:
if (0 == cell.getCellStyle().getDataFormat()) {
data = String.format("%.4f", cell.getNumericCellValue());
} else {
data = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
data = String.valueOf(cell.getRichStringCellValue());
break;
case BOOLEAN:
data = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
data = String.valueOf(cell.getErrorCellValue());
break;
default:
data = cell.getCellFormula();
}
return data;
}
private static String getRealStringValueOfDouble(Double d) {
String doubleStr = d.toString();
boolean b = doubleStr.contains("E");
int indexOfPoint = doubleStr.indexOf('.');
if (b) {
int indexOfE = doubleStr.indexOf('E');
BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
+ BigInteger.ONE.intValue(), indexOfE));
int pow = Integer.valueOf(doubleStr.substring(indexOfE
+ BigInteger.ONE.intValue()));
int xsLen = xs.toByteArray().length;
int scale = xsLen - pow > 0 ? xsLen - pow : 0;
doubleStr = String.format("%." + scale + "f", d);
} else {
Pattern p = Pattern.compile(".0$");
java.util.regex.Matcher m = p.matcher(doubleStr);
if (m.find()) {
doubleStr = doubleStr.replace(".0", "");
}
}
return doubleStr;
}
