java 使用apache POI 解析读取 Excel

wolong

　　这段时间重新作回开发，接手一个EXCEL文件读取任务。关键代码如下，代码功能尚不完全，诸君使用时需要自己斟酌。

//Excel单元格 数据格式转换,xls,xlsx文件支持
private String getCellValue(Cell cell){
String str=null;
if(cell instanceof XSSFCell){
XSSFCell xssfCell = (XSSFCell)cell;
if(xssfCell.getCellType()==Cell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(xssfCell)){
double d = xssfCell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
str = dateFormat.format(date);
}else{
double val =xssfCell.getNumericCellValue();
int intVal=new Double(val).intValue();
str = Integer.toString(intVal);
}
}else if(xssfCell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
boolean  bl= xssfCell.getBooleanCellValue();
str =Boolean.toString(bl);
}else if(xssfCell.getCellType()==Cell.CELL_TYPE_STRING){
str = xssfCell.getStringCellValue();
}else if(xssfCell.getCellType()==Cell.CELL_TYPE_FORMULA){
double formula = xssfCell.getNumericCellValue();
if(HSSFDateUtil.isValidExcelDate(formula)){
Date date = HSSFDateUtil.getJavaDate(formula);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
str = dateFormat.format(date);
}else{
int intVal=new Double(formula).intValue();
str = Integer.toString(intVal);
}
}else if(xssfCell.getCellType()==Cell.CELL_TYPE_BLANK){
str="";
}
}else if (cell instanceof HSSFCell){
HSSFCell hssfCell = (HSSFCell)cell;
if(hssfCell.getCellType()==Cell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
double d = hssfCell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
str = dateFormat.format(date);
}else{
double val =hssfCell.getNumericCellValue();
int intVal=new Double(val).intValue();
str = Integer.toString(intVal);
}
}else if(hssfCell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
boolean  bl= hssfCell.getBooleanCellValue();
str =Boolean.toString(bl);
}else if(hssfCell.getCellType()==Cell.CELL_TYPE_STRING){
str = hssfCell.getStringCellValue();
}else if(hssfCell.getCellType()==Cell.CELL_TYPE_FORMULA){
double formula = hssfCell.getNumericCellValue();
if(HSSFDateUtil.isValidExcelDate(formula)){
Date date = HSSFDateUtil.getJavaDate(formula);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
str = dateFormat.format(date);
}else{
int intVal=new Double(formula).intValue();
str = Integer.toString(intVal);
}
}else if(hssfCell.getCellType()==Cell.CELL_TYPE_BLANK){
str="";
}
}
return str;
}
　　偷个懒贴上关键代码

private void excelRead(MultipartFile file){
Info info =null;
// excel文件解析
XSSFWorkbook workbook = null;
try {
workbook = new XSSFWorkbook(file.getInputStream());
XSSFSheet sheet = workbook.getSheetAt(0);
List<Info> InfoList = new ArrayList<Info>();
for(int i=sheet.getFirstRowNum()+1;i<=sheet.getLastRowNum();i++){
Row row = sheet.getRow(i);
info = new Info();
XSSFCell id = (XSSFCell) row.getCell(0);
//读到空行时终止
if(id==null){
break;
}
info.setId(getCellValue(dealerId));
XSSFCell date =(XSSFCell)row.getCell(7);
//日期
try {
info.setDate(DateUtils.parseDate(getCellValue(date), "yyyy-MM-dd"));
} catch (ParseException e) {
e.printStackTrace();
}
//电话号码需要特殊转换
XSSFCell phone =(XSSFCell)row.getCell(8);
phone.setCellType(Cell.CELL_TYPE_STRING);
info.setPhone(getCellValue(phone));
XSSFCell installRemark =(XSSFCell)row.getCell(9);
info.setRemark(getCellValue(installRemark));
//将clientInstallInfo 对象添加至list中
infoList.add(info);
}
for(int size=0;size<infoList.size();size++){
//spring data jpa
infoService.save(infoList.get(size));
}
} catch (IOException e) {
e.printStackTrace();
}
}
　　其他的Wookbook操作代码：


//获取Excel xls,xlsx 格式文件 ,返回为List<Map<Integer,String>>
private List<Map<Integer,String>> getExcelResult(Workbook workbook){
List<Map<Integer,String>> resultList = new ArrayList<Map<Integer,String>>();
Map<Integer,String> rowMap = null;
//获取Excel文档中工作薄个数
int num = workbook.getNumberOfSheets();
if (workbook instanceof XSSFWorkbook){
//获取第一个工作薄
XSSFSheet xssfSheet =(XSSFSheet)workbook.getSheetAt(0);
int rows=xssfSheet.getPhysicalNumberOfRows();
for(int i =1;i<=rows;i++){
XSSFRow xssfRow = xssfSheet.getRow(i);
rowMap = new HashMap<Integer, String>();
System.out.println(xssfRow);
short minColIx = xssfRow.getFirstCellNum();
short maxColIx = xssfRow.getLastCellNum();
for(short colIx=minColIx; colIx<maxColIx; colIx++) {
XSSFCell xssfCell = xssfRow.getCell(colIx);
if(xssfCell == null) {
continue;
}
xssfCell.setCellType(Cell.CELL_TYPE_STRING);
int cIndex =xssfCell.getColumnIndex();
if(xssfCell.getStringCellValue().equals("")){
rowMap.put(cIndex,"");
}else{
rowMap.put(cIndex, xssfCell.getStringCellValue());
}
}
resultList.add(rowMap);
}
}else if(workbook instanceof HSSFWorkbook){
//获取第一个工作薄
HSSFSheet hssfSheet =(HSSFSheet)workbook.getSheetAt(0);
//Map<Integer,String> rowMap = null;
int rows=hssfSheet.getPhysicalNumberOfRows();
for(int i =1;i<=rows;i++){
HSSFRow hssfRow = hssfSheet.getRow(i);
rowMap = new HashMap<Integer, String>();
short minColIx = hssfRow.getFirstCellNum();
short maxColIx = hssfRow.getLastCellNum();
for(short colIx=minColIx; colIx<maxColIx; colIx++) {
int col = colIx;
HSSFCell hssfCell = hssfRow.getCell(col);
if(hssfCell == null) {
continue;
}
hssfCell.setCellType(Cell.CELL_TYPE_STRING);
int cIndex =hssfCell.getColumnIndex();
if(hssfCell.getStringCellValue().equals("")){
rowMap.put(cIndex,"");
}else{
rowMap.put(cIndex, hssfCell.getStringCellValue());
}
}
resultList.add(rowMap);
}
}
return resultList;
}