一、Excel文件上传
1、添加apache.poi 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
2、接收文件实现方法
Controller层
@PostMapping("/importDevice")
public MessageResult importDevice(@RequestParam("ticket")String ticket,@RequestParam("file") MultipartFile file) {
Map<String,Object> _map=new HashMap<>();
_map.put("ticket",ticket);
return _IService.importDevice(file,_map);
}
service层
@Transactional
@Override
public MessageResult importDevice(MultipartFile file,Map<String, Object> _map){
try{
MessageResult _MessageResult= _GlobalUser.getUserInfo(_map.get("ticket").toString());
UserInfo _userInfo=(UserInfo)_MessageResult.getKeyValue();
_map.put("tenementguid",_userInfo.getTenementGUID());
if(!_MessageResult.getResult()) {
return MessageResult.error(_MessageResult.getMessage());
}
InputStream inputStream = file.getInputStream();
BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
StringBuilder sql=new StringBuilder();
sql.append(" delete from tb_b_ExcelTemp ; ");
for (int i = 2; i <= lastRowNum; i++) {
HSSFRow row =null;
sql.append(" insert into tb_b_ExcelTemp values( ");
for (int j=0; j<25;j++){
row = sheet.getRow(i);
sql.append("'"+row.getCell(j).getStringCellValue()+"',");
}
sql.append("'"+_userInfo.getTenementGUID()+"'");
sql.append(" ); ");
}
_map.put("sql",sql.toString());
int result=_Mapper.insertDeviceTemp(_map);
// boolean insertTempResult=result>0;
log.info(" insert结果:"+result);
// log.info(" 写入临时表结果:"+insertTempResult);
// if(!insertTempResult){
// //回滚操作
// //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
// return MessageResult.error("临时表写入失败");
// }
//写入设备表
_map.remove("sql");
boolean ImportDeviceResult=_Mapper.importDevice(_map)>0;
if(!ImportDeviceResult){
//回滚操作
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return MessageResult.error("设备表写入失败");
}
log.info(" 导入设备结果:"+ImportDeviceResult);
return MessageResult.Ok(ImportDeviceResult);
}catch (Exception e){
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return MessageResult.error(e.getMessage());
}
}
一、Excel文件下载
Map<String,Object> _map=new HashMap<>();
_map.put("ticket",ticket);
List<Map<String,Object>> list=_IService.exportDevice(_map);
if(list==null||list.size()==0){
return MessageResult.error("导出数据为空");
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("信息表");
String fileName = " exportDevice.xls";
HSSFRow row = sheet.createRow(0);
Map<String,Object> titleKey=list.get(0);
int titleIndex=0;
for(String key :titleKey.keySet()){
HSSFCell cell = row.createCell(titleIndex);
cell.setCellValue(new HSSFRichTextString(key));
titleIndex++;
}
int rowNum = 1;
int ColumnIndex=0;
for (Map<String,Object> item:list){
row = sheet.createRow(rowNum);
ColumnIndex=0;
for (String key :item.keySet()){
row.createCell(ColumnIndex).setCellValue(item.get(key).toString());
ColumnIndex++;
}
rowNum++;
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
return MessageResult.Ok(true);
…未完待续