excel 解析 java_java实现excel解析方法

解析excel文件

//fileName为文件的名称，path为文件的路径，nameRow为excel中列的名称的起始行数，valueRow为值的起始行数，excel行数是从0开始的

public static ArrayList> readExcelrowKuaiBao(String fileName, String path, int nameRow, int valueRow) throws IOException {

ArrayList> Row = new ArrayList>();

try {

Workbook workBook = null;

try {

System.out.println(path + "/" + fileName);

workBook = new XSSFWorkbook(path + "/" + fileName);

} catch (Exception ex) {

workBook = new HSSFWorkbook(new FileInputStream(path + "/" + fileName));

}

for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {

Sheet sheet = workBook.getSheetAt(numSheet);

if (sheet == null) {

continue;

}

Row row0 = sheet.getRow(nameRow);

if (null == row0) {

continue;

}

int colum = row0.getPhysicalNumberOfCells();

List keys = new ArrayList();

for (int i = 0; i < colum; i++) {

Cell cell = row0.getCell(i);

keys.add("column" + i);

}

// 循环行Row

for (int rowNum = valueRow; rowNum <= sheet.getPhysicalNumberOfRows(); rowNum++) {

//是否到达文件末尾

boolean isEnd = true;

Row row = sheet.getRow(rowNum);

if (row == null) {

continue;

}

// 循环列Cell

Map arrCell = new HashMap();

for (int cellNum = 0; cellNum < colum; cellNum++) {

Cell cell = row.getCell(cellNum);

if (cell == null) {

arrCell.put(keys.get(cellNum), " ");

} else {

//System.out.println(getValue(cell));

arrCell.put(keys.get(cellNum), getValue(cell));

}

}

for (Object obj : keys) {//遍历key

isEnd = arrCell.get(obj) != null && !"".equals(arrCell.get(obj).toString()) ? false : true;

if (!isEnd)

break;

}

if (isEnd) {

break;

}

Row.add(arrCell);

}

}

} catch (IOException e) {

System.out.println("e:" + e);

throw e;

}

return Row;

}

private static String getValue(Cell cell) {

String result = new String();

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型

System.out.println(cell.getCellStyle().getDataFormat());

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");

}

Date date = cell.getDateCellValue();

result = sdf.format(date);

} else if (cell.getCellStyle().getDataFormat() == 181) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");

double value = cell.getNumericCellValue();

Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);

result = sdf.format(date);

} else if (cell.getCellStyle().getDataFormat() == 58) {

// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

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);

result = value + "";

}

break;

case HSSFCell.CELL_TYPE_STRING:// String类型

result = cell.getRichStringCellValue().toString();

break;

case HSSFCell.CELL_TYPE_FORMULA:

result = cell.getNumericCellValue() + "";

break;

case HSSFCell.CELL_TYPE_BLANK:

result = "";

default:

result = "";

break;

}

return result;

}

```excel数据入库

//readExcelrowKuaiBao是调用上面的方法

ArrayList> row = this.readExcelrowKuaiBao(fileName, filePath, 3, 4);

for (int i = 0; i < row.size(); i++) {

//获取第0列的值，也就是excel中第一列的值

row.get(i).get("column0").toString()；

//获取第1列的值，列数依次累加

row.get(i).get("column1").toString()；

}