/**
* 读取excel工具类
* @param file 待解析的xls文件
* @param startRow 开始行
* @param startCol 开始列
* @param keyCol 作为map的主键
* @return
* @throws IOException
*/
public static Map<String,List<Object>> readXlsUtil(String file,int startRow,int startCol,int[] keyCol) throws IOException{
FileInputStream inputStream = new FileInputStream(file);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
HSSFSheet hssfsheet = hssfWorkbook.getSheetAt(0);
HSSFRow row = null;
Map<String,List<Object>> map = new HashMap<String,List<Object>>();
for(int i=startRow;i<hssfsheet.getPhysicalNumberOfRows();i++){
row = hssfsheet.getRow(i);
if(row!=null){
List<Object> list = new ArrayList<Object>();
for (int j = startCol; j < row.getPhysicalNumberOfCells(); j++) {
HSSFCell hssfCell = row.getCell(j);
int cellType = hssfCell.getCellType();
Object obj = "";
if (cellType == 0) {// date
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
if (hssfCell.getDateCellValue() == null) {
obj = "";
} else {
obj = hssfCell.getDateCellValue();
}
} else {
try {
obj = getBigDecimal(String.valueOf(hssfCell.getNumericCellValue()));
} catch (NumberFormatException ex) {
ex.printStackTrace();
}
}
} else if (cellType == 1) {
obj = String.valueOf(hssfCell.getStringCellValue());
} else if (cellType == 2) {
obj = getBigDecimal(String.valueOf(hssfCell.getNumericCellValue()));
} else if (cellType == 3) {
obj = hssfCell.getStringCellValue();
} else if (cellType == 4) {
obj = Boolean.valueOf(hssfCell.getBooleanCellValue());
}
list.add(obj.toString());
}
if(!list.isEmpty()){
String key = "";
for(int m=0;m<keyCol.length;m++){
key += String.valueOf(list.get(keyCol[m]));
}
if(!map.containsKey(key) && !"".equals(key)){
map.put(key, list);
}else if(!"".equals(key)){
System.out.println(file+" key 已经存在:"+key);
}
}else{
System.out.println(file+" 的数据不能读取,row="+(i+1));
}
}
}
return map;
}
* 读取excel工具类
* @param file 待解析的xls文件
* @param startRow 开始行
* @param startCol 开始列
* @param keyCol 作为map的主键
* @return
* @throws IOException
*/
public static Map<String,List<Object>> readXlsUtil(String file,int startRow,int startCol,int[] keyCol) throws IOException{
FileInputStream inputStream = new FileInputStream(file);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
HSSFSheet hssfsheet = hssfWorkbook.getSheetAt(0);
HSSFRow row = null;
Map<String,List<Object>> map = new HashMap<String,List<Object>>();
for(int i=startRow;i<hssfsheet.getPhysicalNumberOfRows();i++){
row = hssfsheet.getRow(i);
if(row!=null){
List<Object> list = new ArrayList<Object>();
for (int j = startCol; j < row.getPhysicalNumberOfCells(); j++) {
HSSFCell hssfCell = row.getCell(j);
int cellType = hssfCell.getCellType();
Object obj = "";
if (cellType == 0) {// date
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
if (hssfCell.getDateCellValue() == null) {
obj = "";
} else {
obj = hssfCell.getDateCellValue();
}
} else {
try {
obj = getBigDecimal(String.valueOf(hssfCell.getNumericCellValue()));
} catch (NumberFormatException ex) {
ex.printStackTrace();
}
}
} else if (cellType == 1) {
obj = String.valueOf(hssfCell.getStringCellValue());
} else if (cellType == 2) {
obj = getBigDecimal(String.valueOf(hssfCell.getNumericCellValue()));
} else if (cellType == 3) {
obj = hssfCell.getStringCellValue();
} else if (cellType == 4) {
obj = Boolean.valueOf(hssfCell.getBooleanCellValue());
}
list.add(obj.toString());
}
if(!list.isEmpty()){
String key = "";
for(int m=0;m<keyCol.length;m++){
key += String.valueOf(list.get(keyCol[m]));
}
if(!map.containsKey(key) && !"".equals(key)){
map.put(key, list);
}else if(!"".equals(key)){
System.out.println(file+" key 已经存在:"+key);
}
}else{
System.out.println(file+" 的数据不能读取,row="+(i+1));
}
}
}
return map;
}