玩具网站开发背景/海南百度总代理

这段时间重新作回开发，接手一个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 InfoList = new ArrayList();

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

//spring data jpa

infoService.save(infoList.get(size));

}

} catch (IOException e) {

e.printStackTrace();

}

}

其他的Wookbook操作代码：

//获取Excel xls,xlsx 格式文件 ,返回为List>

private List> getExcelResult(Workbook workbook){

List> resultList = new ArrayList>();

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

System.out.println(xssfRow);

short minColIx = xssfRow.getFirstCellNum();

short maxColIx = xssfRow.getLastCellNum();

for(short colIx=minColIx; 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 rowMap = null;

int rows=hssfSheet.getPhysicalNumberOfRows();

for(int i =1;i<=rows;i++){

HSSFRow hssfRow = hssfSheet.getRow(i);

rowMap = new HashMap();

short minColIx = hssfRow.getFirstCellNum();

short maxColIx = hssfRow.getLastCellNum();

for(short colIx=minColIx; 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;

}