JAVA使用POI动态读取简单EXCEL数据
改Java用于动态读取一些简单的Excel的数据, 复杂的表格未测试过。个人笔记,自用勿喷!
package com.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;
/**
* 读取EXCLE中表名称
* @author LG
*
*/
public class ReadExcel {
/**
* 根据文件名、sheel名读取excel中信息
* @param fileName 文件名
* @param sheel
* @return
* @throws Exception
*/
public static List<Map<String,Object>> readXls(String fileName, String sheel) throws Exception {
String path = "D:\\work\\" + fileName;
Workbook workbook = null;
InputStream input = new FileInputStream(path);
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
boolean is2003 = true;
//判断是否为2003版Excel
if (!fileName.endsWith("xls")) {
is2003 = false;
}
if (is2003 == true) {
workbook = new HSSFWorkbook(input);
} else {
workbook = new XSSFWorkbook(input);
}
// 循环工作表Sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
String sheetName = sheet.getSheetName();
if (sheetName.trim().equals(sheel)) {
// 循环行Row
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row != null) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
String location = "R" + rowNum + "C" + i ;
map.put(location, getValue(cell));
}
list.add(map);
}
}
}
}
return list;
}
/**
* 转换格式
* @param Cell
* @return
*/
private static String getValue(Cell cell) {
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
return String.valueOf(cell.getNumericCellValue()) ;
case HSSFCell.CELL_TYPE_STRING: // 字符串
return String.valueOf(cell.getStringCellValue()) ;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
return String.valueOf(cell.getBooleanCellValue()) ;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
return String.valueOf(cell.getCellFormula()) ;
case HSSFCell.CELL_TYPE_BLANK: // 空值
return "" ;
case HSSFCell.CELL_TYPE_ERROR: // 故障
return "类型错误" ;
default:
return "未知类型" ;
}
} else {
return "类型为空";
}
}
public static void main(String [] args) {
String fileName = "XXX.xls";
String sheel = "Sheet1";
ReadExcel excel = new ReadExcel();
try {
String str = "''";
List<Map<String,Object>> list = excel.readXls(fileName,sheel);
for (Map<String, Object> map : list) {
for (Entry<String, Object> entry : map.entrySet()) {
String aa= (String) entry.getKey();
String bb= (String) map.get(aa);
}
}
System.out.println(str);
} catch (Exception e) {
e.printStackTrace();
}
}
}