导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
java实现代码
public class ExcelUtils {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static Object readExecl() throws IOException {
File file=new File(path);
Object count = null;
if(file.exists()){
String[] split = path.split("\\.");
if(OFFICE_EXCEL_2003_POSTFIX.equals(split[split.length-1])){
count = readXls(path);
}else if(OFFICE_EXCEL_2010_POSTFIX.equals(split[split.length-1])){
count = readXlsx(path);
}
}
return count;
}
public static Object readXlsx(String path) throws IOException {
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
int numberOfSheets = xssfWorkbook.getNumberOfSheets();
int count = 0;
try {
for (int s = 0; s < numberOfSheets; s++) {
try {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
for (int i = 0; i < list.size(); i++) {
XSSFCell cell = xssfRow.getCell(i);
if(cell == null){
continue;
}
String value = getValue(cell);
String th_value = list.get(i);
}
}
}
} catch (Exception e) {
e.printStackTrace();
logger.info("读取第"+(s+1)+"个sheet异常");
}
}
} catch (NumberFormatException e) {
logger.info("读取Xlsx文件异常");
e.printStackTrace();
}finally {
is.close();
}
return count;
}
public static Object readXls(String path) throws IOException {
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
int numberOfSheets = hssfWorkbook.getNumberOfSheets();
try {
for (int s = 0; s < numberOfSheets; s++) {
try {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(s);
List<String> list = new ArrayList<>();
HSSFRow hssfRow = hssfSheet.getRow(0);
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
for (int i = 0; i < list.size(); i++) {
HSSFCell cell = hssfRow.getCell(i);
if(cell == null){
continue;
}
String value = getValue(cell);
String th_value = list.get(i);
}
}
}
} catch (Exception e) {
e.printStackTrace();
logger.info("读取第"+(s+1)+"个sheet异常");
}
}
} catch (NumberFormatException e) {
logger.info("读取Xls文件异常");
e.printStackTrace();
}finally {
is.close();
}
return count;
}
@SuppressWarnings({ "static-access" })
private static String getValue(XSSFCell xssfRow) {
if(xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC){
xssfRow.setCellType(xssfRow.CELL_TYPE_STRING);
}
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings({ "static-access" })
private static String getValue(HSSFCell hssfCell) {
if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){
hssfCell.setCellType(hssfCell.CELL_TYPE_STRING);
}
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
}