@RequestMapping("/importData")
public View importData(@RequestParam("files") UploadedFile[] files,
@RequestParam("cityCode") String cityCode){
List<White> whiteList=new ArrayList<White>();
if(!files[0].getPath().endsWith(".xls") && !files[0].getPath().endsWith(".xlsx")){
return new JsonView("文件格式错误!");
}
try {
String str = batchUpload(files[0]);
if(str != null &&! str.isEmpty()){
whiteList=readXls(str);
}
} catch (IOException e) {
logger.debug(e.toString());
}
int successNum=0;
for(White white: whiteList){
if(!StringUtils.isNullOrEmpty(cityCode)){
white.setCityCode(cityCode);
}
iWhiteHandler.addWhite(white);
successNum++;
}
String response="成功导入"+successNum+"条数据!";
Remoting<?> remoting = ContextHolder.holder.getLastRemoting();
AjaxResult<String> result = AjaxResult.create(remoting.isDone(), response, remoting.getLastErrMsg());
iSysLogHandler.Log(SysLogMode.MU_White.value,SysLogType.logImport.value,"");
return new JsonView(result);
}
//上传
public String batchUpload(UploadedFile files) throws IOException{
String newFilePath = "";
if(files !=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String prefix = sdf.format(new Date());
int i = 0;
String filePath = files.getPath(); //路径
String fileName = String.format("%s-%s", prefix,(++i)); //文件名
String ext = filePath.substring(filePath.lastIndexOf(".")+1); //文件后缀名
// String name1 = fileName +"."+ ext;
String dir = Paths.get(filePath).getParent().toString(); //盘符
String newName = dir + File.separator + fileName +"."+ ext; //拼接路径
File excelFile = new File(files.getPath());
File newFile = new File(newName);
// 新建文件
if (!newFile.getParentFile().exists()) {
newFile.getParentFile().mkdirs();
}
if (newFile.exists()) {
newFile.delete();
}
FileUtils.copyFile(excelFile,newFile); //复制文件
newFilePath = newFile.getPath();
}
return newFilePath;
}
//解析并添加到white对象
public List <White> readXls(String filePath) throws IOException {
InputStream is = new FileInputStream(filePath);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
List<White> list = new ArrayList<White>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
White white=new White();
HSSFCell carNo = hssfRow.getCell(0);
HSSFCell unit = hssfRow.getCell(1);
HSSFCell person = hssfRow.getCell(2);
HSSFCell validationTime = hssfRow.getCell(3);
HSSFCell remark = hssfRow.getCell(4);
white.setVeNumber(carNo.getStringCellValue());
white.setBestowUnit(unit.getStringCellValue());
white.setBestowPeople(person.getStringCellValue());
white.setUsefuLlife(validationTime.getDateCellValue());
white.setRemark(remark.getStringCellValue());
list.add(white);
}
}
}
return list;
}
public View importData(@RequestParam("files") UploadedFile[] files,
@RequestParam("cityCode") String cityCode){
List<White> whiteList=new ArrayList<White>();
if(!files[0].getPath().endsWith(".xls") && !files[0].getPath().endsWith(".xlsx")){
return new JsonView("文件格式错误!");
}
try {
String str = batchUpload(files[0]);
if(str != null &&! str.isEmpty()){
whiteList=readXls(str);
}
} catch (IOException e) {
logger.debug(e.toString());
}
int successNum=0;
for(White white: whiteList){
if(!StringUtils.isNullOrEmpty(cityCode)){
white.setCityCode(cityCode);
}
iWhiteHandler.addWhite(white);
successNum++;
}
String response="成功导入"+successNum+"条数据!";
Remoting<?> remoting = ContextHolder.holder.getLastRemoting();
AjaxResult<String> result = AjaxResult.create(remoting.isDone(), response, remoting.getLastErrMsg());
iSysLogHandler.Log(SysLogMode.MU_White.value,SysLogType.logImport.value,"");
return new JsonView(result);
}
//上传
public String batchUpload(UploadedFile files) throws IOException{
String newFilePath = "";
if(files !=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String prefix = sdf.format(new Date());
int i = 0;
String filePath = files.getPath(); //路径
String fileName = String.format("%s-%s", prefix,(++i)); //文件名
String ext = filePath.substring(filePath.lastIndexOf(".")+1); //文件后缀名
// String name1 = fileName +"."+ ext;
String dir = Paths.get(filePath).getParent().toString(); //盘符
String newName = dir + File.separator + fileName +"."+ ext; //拼接路径
File excelFile = new File(files.getPath());
File newFile = new File(newName);
// 新建文件
if (!newFile.getParentFile().exists()) {
newFile.getParentFile().mkdirs();
}
if (newFile.exists()) {
newFile.delete();
}
FileUtils.copyFile(excelFile,newFile); //复制文件
newFilePath = newFile.getPath();
}
return newFilePath;
}
//解析并添加到white对象
public List <White> readXls(String filePath) throws IOException {
InputStream is = new FileInputStream(filePath);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
List<White> list = new ArrayList<White>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
White white=new White();
HSSFCell carNo = hssfRow.getCell(0);
HSSFCell unit = hssfRow.getCell(1);
HSSFCell person = hssfRow.getCell(2);
HSSFCell validationTime = hssfRow.getCell(3);
HSSFCell remark = hssfRow.getCell(4);
white.setVeNumber(carNo.getStringCellValue());
white.setBestowUnit(unit.getStringCellValue());
white.setBestowPeople(person.getStringCellValue());
white.setUsefuLlife(validationTime.getDateCellValue());
white.setRemark(remark.getStringCellValue());
list.add(white);
}
}
}
return list;
}