/**
* excel数据导入,默认从第二行开始读取。
*
* @param inStream:导入WBS的Excel文件流
* @param sheetName:Excel的sheet页名称
* @param attributes
* excel列名称
* @param noEmptyColumn
* 是否该列为空,则表示到了结束,不再循环取值。
* @return PRM 的WBS对象
*/
public List<Map<String,?>> getDataFromExcelFile(InputStream inStream,
String sheetName, String[] attributes, int noEmptyColumn) {
List<Map<String,?>> list = new ArrayList<Map<String,?>>();
if (inStream == null)
return null;
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(new POIFSFileSystem(inStream));
} catch (IOException e) {
list = null;
return null;
}
if (wb == null)
return null;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null)
sheet = wb.getSheetAt(0);
short i = 1;
while (true) {
HSSFRow row = sheet.getRow(i++);
if (row == null) {
break;
}
boolean bool = isEmptyOfSpecialColumn(row, noEmptyColumn);
if (bool) {
break;
}
Map<String,?> map = getAttrVauleFormExcelRow(row, attributes);
list.add(map);
}
return list;
}
/**
* 通过excel一行创建Map对象,
*
* @param row选中的Excel一行
* @param attributes
* 列属性名称,按excel列从左到右的顺序
* @return
*/
private Map<String,?> getAttrVauleFormExcelRow(HSSFRow row, String[] attributes) {
Map<String,Object> map= new HashMap<String,Object>();
if (attributes == null || attributes.length < 1)
return null;
for (int i = 0; i < attributes.length; i++) {
HSSFCell cell = row.getCell(i);
Object value = formatExcelData(cell);
map.put(attributes[i], value);
}
return map;
}
/**
* 判断某行中的指定列值是否为空
*
* @param row
* @param icolumn,首列从0开始。
* @return
*/
private boolean isEmptyOfSpecialColumn(HSSFRow row, int icolumn) {
boolean result = true;
if (row == null)
return result;
else {
HSSFCell cell = row.getCell(icolumn);
Object value = formatExcelData(cell);
if (value == null)
result = true;
else
result = false;
}
return result;
}
/**
* 把从Excel取出的数据进行格式化处理,变成字符串。
*
* @param cell
* @return
*/
private Object formatExcelData(HSSFCell cell) {
Object sData = null;
if (cell == null)
sData = null;
else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
sData = cell.getStringCellValue();
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
sData = cell.getNumericCellValue();
else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
sData = "";
else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
sData = cell.getBooleanCellValue();
else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
sData = cell.getCellFormula();
else
sData = "excel data format unknown";
return sData;
}
}
* excel数据导入,默认从第二行开始读取。
*
* @param inStream:导入WBS的Excel文件流
* @param sheetName:Excel的sheet页名称
* @param attributes
* excel列名称
* @param noEmptyColumn
* 是否该列为空,则表示到了结束,不再循环取值。
* @return PRM 的WBS对象
*/
public List<Map<String,?>> getDataFromExcelFile(InputStream inStream,
String sheetName, String[] attributes, int noEmptyColumn) {
List<Map<String,?>> list = new ArrayList<Map<String,?>>();
if (inStream == null)
return null;
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(new POIFSFileSystem(inStream));
} catch (IOException e) {
list = null;
return null;
}
if (wb == null)
return null;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null)
sheet = wb.getSheetAt(0);
short i = 1;
while (true) {
HSSFRow row = sheet.getRow(i++);
if (row == null) {
break;
}
boolean bool = isEmptyOfSpecialColumn(row, noEmptyColumn);
if (bool) {
break;
}
Map<String,?> map = getAttrVauleFormExcelRow(row, attributes);
list.add(map);
}
return list;
}
/**
* 通过excel一行创建Map对象,
*
* @param row选中的Excel一行
* @param attributes
* 列属性名称,按excel列从左到右的顺序
* @return
*/
private Map<String,?> getAttrVauleFormExcelRow(HSSFRow row, String[] attributes) {
Map<String,Object> map= new HashMap<String,Object>();
if (attributes == null || attributes.length < 1)
return null;
for (int i = 0; i < attributes.length; i++) {
HSSFCell cell = row.getCell(i);
Object value = formatExcelData(cell);
map.put(attributes[i], value);
}
return map;
}
/**
* 判断某行中的指定列值是否为空
*
* @param row
* @param icolumn,首列从0开始。
* @return
*/
private boolean isEmptyOfSpecialColumn(HSSFRow row, int icolumn) {
boolean result = true;
if (row == null)
return result;
else {
HSSFCell cell = row.getCell(icolumn);
Object value = formatExcelData(cell);
if (value == null)
result = true;
else
result = false;
}
return result;
}
/**
* 把从Excel取出的数据进行格式化处理,变成字符串。
*
* @param cell
* @return
*/
private Object formatExcelData(HSSFCell cell) {
Object sData = null;
if (cell == null)
sData = null;
else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
sData = cell.getStringCellValue();
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
sData = cell.getNumericCellValue();
else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
sData = "";
else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
sData = cell.getBooleanCellValue();
else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
sData = cell.getCellFormula();
else
sData = "excel data format unknown";
return sData;
}
}