使用Java实现Excel导入功能

一、导入xls格式的Excel
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
MultipartFile file = multipartRequest.getFile("excelFile");  
List<Entity> entityList = new ArrayList<Entity>();
String message="";//反馈信息
Workbook wb = null;
try {
wb = Workbook.getWorkbook(file.getInputStream());
Sheet sheet = wb.getSheet(0);
int totalrows = sheet.getRows();
if(totalrows<=1)
{
message ="导入数据为空，导入失败";
modelMap.put("message", message);
return  "";
}else{
for(int i=1;i<totalrows;i++)
{
String key1=sheet.getCell(0, i).getContents().trim();
String key2=sheet.getCell(1,i).getContents().trim();
String key3=sheet.getCell(2, i).getContents().trim();
String key4=sheet.getCell(3,i).getContents().trim();
:
:
:
Entity entity=new Entity();//将取出单位数据放入实体类
entity.setKey1(key1);
entity.setKey2(key2);
entity.setKey3(key3);
entity.setKey4(key4);
entityList.add(entity); //将组装的实体放入list中
}
uploadService.saveAll(salesList);//将实体列表存入数据库
}
}catch(Exception e){
e.printStackTrace();
modelMap.put("message", e);
} 
return  "url...";
}


二、导入xlsx格式的Excel
String message="";//反馈信息
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
MultipartFile file = multipartRequest.getFile("excelFile");   
    boolean bool =false;    
if(bool){
message="有其他人在导入数据，请稍后再试。";
modelMap.put("message", message);
modelMap.put("windowId", "windowname");
return "url...";
}


bool = true;
XSSFWorkbook wb = null;
List<Entity> entityList=new ArrayList<Entity>();
try { 
InputStream fileInput = file.getInputStream();

wb=new XSSFWorkbook(fileInput);
// 创建基于stream的工作薄对象
XSSFSheet sheet = wb.getSheetAt(0);//获取sheet1中的数据
XSSFRow r = sheet.getRow(0);//获取表头
int [] column=  new int[columnSize];
for(int i=0;i<sheet.getPhysicalNumberOfRows();i++){
String name = "";
name = name+getValue(r.getCell(j));
switch(name){
case "column1": column[0] = j; break;
case "column2": column[1] = j; break;
case "column3": column[2] = j; break;
:
:
:
}
}
for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
   XSSFRow row = sheet.getRow(rowNum);
   if (row!= null) {
    Entity entity=new Entity();
    entity.setKey1(getValue(row.getCell(a[0])));
entity.setKey2(getValue(row.getCell(a[1])));
entity.setKey3(getValue(row.getCell(a[2])));
entity.setKey4(getValue(row.getCell(a[3])));
:
:
:
entityList.add(entity);
 }
}  
entityService.save(entityList);
}catch (Exception e) {
message="数据导入失败!";
e.printStackTrace();
}finally{
try {
if (null != wb)
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//将从Excel获取的数据进行格式化
public String getValue(XSSFCell cell){
String result="";
if(cell!=null){
switch (cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
Date date =cell.getDateCellValue();
SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
result =df.format(date);
}else{
String a = String.valueOf(cell.getNumericCellValue());
return a.substring(0,a.indexOf("."));
}
break;
case XSSFCell.CELL_TYPE_FORMULA:
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
result =  cell.getStringCellValue();
break;
default: 
result = ""; 
break;
}
}
return result;
}