MAVEN POI
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
代码:
package zy.learn.one;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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;
public class Main {
public static void main(String[] args) {
analysis("D://test.xlsx");
}
/**
* 用来解析 excel 文件
* @param filePath 文件路径
* @return
*/
public static List<Map<String, String>> analysis(String filePath) {
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
List<Map<String, String>> list = null;
String cellData = null;
// 用来存储第一行 (列名行)
ArrayList<String> arrayList = new ArrayList<String>();
workbook = readExcel(filePath);
if (workbook != null) {
// 用来存放表中数据
list = new ArrayList<Map<String, String>>();
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取第一行
row = sheet.getRow(0);
// 获取最大列数
int colnum = row.getPhysicalNumberOfCells();
// 把所有的行的名字都添加到集合中
for (int i = 0; i < colnum; i++) {
arrayList.add(String.valueOf(row.getCell(i)));
}
for (int i = 1; i < rownum; i++) {
// 用map 来临时存储 遍历出来一列的值
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(arrayList.get(j), cellData);
}
} else {
break;
}
list.add(map);
}
}
System.out.println(list);
return list;
}
/**
* 读取excel 文件内数据
*
* @param filePath 文件路径
* @return
*/
public static Workbook readExcel(String filePath) {
Workbook workbook = null;
if (filePath == null) {
return null;
}
// 判断文件是以什么 xls 还是 xlsx 保存
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return workbook = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return workbook = new XSSFWorkbook(is);
} else {
workbook = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
/**
* 判断单元格的内容 属于什么类型的数据 相应进行转换
*
* @param cell 数据对象
* @return
*/
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
cellValue = stringDateProcess(cell);
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: // 异常
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
} else {
cellValue = "";
}
return cellValue;
}
/**
* 日期格式转换方法
*
* @param cell 数据对象
* @return
*/
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;
}
}
效果: