使用Java实现Excel导入功能

ゞ浴缸里的玫瑰 2022-06-12 13:20 270阅读 0赞

一、导入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;  
\}