完整版excel上传导入读写批量数据并将反馈结果写入远程exel中

 ?1 package com.XXXX.XXXXX.utils; ?2 ??3 import java.io.ByteArrayOutputStream; ?4 import java.io.File; ?5 import java.io.FileInputStream; ?6 import java.io.FileNotFoundException; ?7 import java.io.FileOutputStream; ?8 import java.io.IOException; ?9 import java.io.InputStream; 10 import java.net.HttpURLConnection; 11 import java.net.URL; 12 import java.text.DecimalFormat; 13 import java.util.ArrayList; 14 import java.util.List; 15 ?16 import org.apache.commons.io.FileUtils; 17 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 18 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 19 import org.apache.poi.ss.usermodel.Cell; 20 import org.apache.poi.ss.usermodel.CellType; 21 import org.apache.poi.ss.usermodel.Row; 22 import org.apache.poi.ss.usermodel.Sheet; 23 import org.apache.poi.ss.usermodel.Workbook; 24 import org.apache.poi.ss.usermodel.WorkbookFactory; 25 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 26 import org.slf4j.Logger; 27 import org.slf4j.LoggerFactory; 28 import org.springframework.util.ResourceUtils; 29 ?30 import com.sun.javafx.scene.control.skin.TreeTableRowSkin; 31 import com.topband.cloud.common.utils.DateFormatUtil; 32 import com.topband.cloud.common.utils.StringUtil; 33 ?34 public class ReadExcelUtil { 35 ?????36 ????private static final String EXCEL_XLS = ".xls"; 37 ????private static final String EXCEL_XLSX = ".xlsx"; 38 ?????39 ????/** 40 ?????*读取excel数据 41 ?????* @throws Exception ?42 ?????* 43 ?????*/ 44 ????public static List<List<String>> readExcelInfo(String url) throws Exception{ 45 // ???????支持excel2003、2007 46 ????????File excelFile = new File(url);//创建excel文件对象 47 ????????InputStream is = new FileInputStream(excelFile);//创建输入流对象 48 ????????checkExcelVaild(excelFile); 49 ????????Workbook workbook = getWorkBook(is, excelFile); 50 // ???????Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010 51 // ???????获取Sheet数量 52 ????????int sheetNum = workbook.getNumberOfSheets(); 53 ????????sheetNum = 1;//限制模板只在一个工作簿上操作 54 // ?????创建二维数组保存所有读取到的行列数据，外层存行数据，内层存单元格数据 55 ????????List<List<String>> dataList = new ArrayList<List<String>>(); 56 // ???????遍历工作簿中的sheet,第一层循环所有sheet表 57 ????????for(int index = 0;index<sheetNum;index++){ 58 ????????????Sheet sheet = workbook.getSheetAt(index); 59 ????????????if(sheet==null){ 60 ????????????????continue; 61 ????????????} 62 // ???????????如果当前行没有数据跳出循环，第二层循环单sheet表中所有行 63 ????????????for(int rowIndex=0;rowIndex<=sheet.getLastRowNum();rowIndex++){ 64 ????????????????System.out.println(sheet.getLastRowNum()+"===="); 65 ????????????????Row row = sheet.getRow(rowIndex); 66 ????????????????if(row==null){ 67 ????????????????????continue; 68 ????????????????} 69 // ???????????????遍历每一行的每一列，第三层循环行中所有单元格 70 ????????????????List<String> cellList = new ArrayList<String>(); 71 ????????????????for(int cellIndex=0;cellIndex<row.getLastCellNum();cellIndex++){ 72 ????????????????????Cell cell = row.getCell(cellIndex); 73 ????????????????????System.out.println(cellIndex); 74 ????????????????????cellList.add(getCellValue(cell)); 75 ????????????????} 76 ????????????????dataList.add(cellList); 77 ????????????} 78 ?????????????79 ????????} 80 ????????is.close(); 81 ????????return dataList; 82 ????????} 83 ????/** 84 ?????*获取单元格的数据,暂时不支持公式 85 ?????* ?86 ?????* 87 ?????*/ 88 ????????public static String getCellValue(Cell cell){ 89 ????????????CellType cellType = cell.getCellTypeEnum(); 90 ????????????if(cellType==null){ 91 ????????????????return null; 92 ????????????} 93 ????????????String cellValue = ""; 94 ????????????if(cell==null || cell.toString().trim().equals("")){ 95 ????????????????return null; 96 ????????????} 97 ?????????????98 ????????????if(cellType==CellType.STRING){ 99 ????????????????cellValue = cell.getStringCellValue().trim();100 ????????????????return cellValue = StringUtil.isEmpty(cellValue)?"":cellValue;101 ????????????}102 ????????????if(cellType==CellType.NUMERIC){103 ????????????????if (HSSFDateUtil.isCellDateFormatted(cell)) { ?//判断日期类型104 ?????????????????????cellValue = DateFormatUtil.formatDurationYMD(cell.getDateCellValue().getTime());105 ?????????????????} else { ?//否106 ?????????????????????cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue()); 107 ?????????????????} 108 ????????????????return cellValue;109 ????????????}110 ????????????if(cellType==CellType.BOOLEAN){111 ????????????????cellValue = String.valueOf(cell.getBooleanCellValue());112 ????????????????return cellValue;113 ????????????}114 ????????????return null;115 ????????????116 ????????}117 ????/**118 ?????*判断excel的版本，并根据文件流数据获取workbook119 ?????* @throws IOException 120 ?????*121 ?????*/122 ????public static Workbook getWorkBook(InputStream is,File file) throws Exception{123 ????????124 ????????Workbook workbook = null;125 ????????if(file.getName().endsWith(EXCEL_XLS)){126 ????????????workbook = new HSSFWorkbook(is);127 ????????}else if(file.getName().endsWith(EXCEL_XLSX)){128 ????????????workbook = new XSSFWorkbook(is);129 ????????}130 ????????131 ????????return workbook;132 ????}133 ????/**134 ?????*校验文件是否为excel135 ?????* @throws Exception 136 ?????* 137 ?????*138 ?????*/139 ????public static void checkExcelVaild(File file) throws Exception {140 ????????String message = "该文件是EXCEL文件！";141 ????????if(!file.exists()){142 ????????????message = "文件不存在！";143 ????????????throw new Exception(message);144 ????????}145 ????????if(!file.isFile()||((!file.getName().endsWith(EXCEL_XLS)&&!file.getName().endsWith(EXCEL_XLSX)))){146 ????????????message = "文件不是Excel";147 ????????????throw new Exception(message);148 ????????}149 ????}150 ????/**151 ?????*校验上传的excel模板是否正确152 ?????* 153 ?????* 154 ?????*155 ?????*/156 ????public static boolean checkExcelTemplate(String url){157 ????????????try {158 ????????????????List<List<String>> list = ReadExcelUtil.readExcelInfo(url);159 ????????????????for(int i=0;i<list.size();i++){160 ????????????????????if(i==0){161 ????????????????????????if(!list.get(i).get(0).trim().equals("公司ID")||!list.get(i).get(1).trim().equals("产品ID")||!list.get(0).get(2).trim().equals("设备类型")162 ????????????????????????????????||!list.get(i).get(3).trim().equals("设备型号")||!list.get(i).get(4).trim().equals("设备名称")){163 ????????????????????????????return false;164 ????????????????????????}165 ????????????????????}166 ????????????????????if(i==2){167 ????????????????????????if(!list.get(i).get(0).trim().equals("设备ID")||!list.get(i).get(1).trim().equals("结果")){168 ????????????????????????????return false;169 ????????????????????????}170 ????????????????????}171 ????????????} 172 ????????????????}catch (Exception e) {173 ????????????????// TODO Auto-generated catch block174 ????????????????e.printStackTrace();175 ????????????}176 ????????????return true;177 178 ????????}179 ????/**180 ?????* 将反馈结果写入excel中181 ?????* */182 ????public static void writeExcelResult(String url,List<Integer> result) throws Exception{183 // ???????支持excel2003、2007184 ????????File excelFile = new File(url);//创建excel文件对象185 ????????InputStream is = new FileInputStream(excelFile);//创建输入流对象186 ????????checkExcelVaild(excelFile);187 ????????Workbook workbook = getWorkBook(is, excelFile);188 // ???????Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010189 // ???????获取Sheet数量190 ????????int sheetNum = workbook.getNumberOfSheets();191 ????????sheetNum = 1;//限制模板只在一个工作簿上操作192 // ???????遍历工作簿中的sheet,第一层循环所有sheet表193 ????????for(int index = 0;index<sheetNum;index++){194 ????????????Sheet sheet = workbook.getSheetAt(index);195 ????????????if(sheet==null){196 ????????????????continue;197 ????????????}198 // ???????????如果当前行没有数据跳出循环，第二层循环单sheet表中所有行199 ????????????for(int rowIndex=3;rowIndex<=sheet.getLastRowNum();rowIndex++){200 ????????????????Row row = sheet.getRow(rowIndex);201 ????????????????row.createCell(1).setCellValue(result.get(rowIndex-3));202 ????????????}203 ????????????204 ????????}205 ????????FileOutputStream outputStream = new FileOutputStream(url);206 ????????workbook.write(outputStream);207 ????????outputStream.close();208 ????????}209 ??????/** 210 ?????* 根据地址获得客户上传的excel字节流 211 ?????* @param fileUrl 网络连接地址 212 ?????* @return 213 ?????*/ ?214 ????public static byte[] getExcelFromAliyun(String fileUrl){ ?215 ????????try { ?216 ????????????URL url = new URL(fileUrl); ?217 ????????????HttpURLConnection conn = (HttpURLConnection)url.openConnection(); ?218 ????????????conn.setRequestMethod("GET"); ?219 ????????????conn.setConnectTimeout(5 * 1000); ?220 ????????????InputStream inStream = conn.getInputStream();//通过输入流获取excelFile数据 ?221 ????????????byte[] excelFile = readInputStream(inStream);//得到excelFile的二进制数据 ?222 ????????????return excelFile; ?223 ????????} catch (Exception e) { ?224 ????????????e.printStackTrace(); ?225 ????????} ?226 ????????return null; ?227 ????} ?228 ????/** 229 ?????* 从网上得到的输入流中获取数据转换为二进制数据 230 ?????* @param inStream 输入流 231 ?????* @return 232 ?????* @throws Exception 233 ?????*/ ?234 ????public static byte[] readInputStream(InputStream inStream) throws Exception{ ?235 ????????ByteArrayOutputStream outStream = new ByteArrayOutputStream(); ?236 ????????byte[] buffer = new byte[1024]; ?237 ????????int len = 0; ?238 ????????while( (len=inStream.read(buffer)) != -1 ){ ?239 ????????????outStream.write(buffer, 0, len); ?240 ????????} ?241 ????????inStream.close(); ?242 ????????return outStream.toByteArray(); ?243 ????}244 ????/** 245 ?????* 将文件写入到目标目录中 246 ?????* @param excel 文件数据流247 ?????* @param fileName 文件保存时的名称 248 ?????*/ ?249 ????public static void writeFileToDest(byte[] excelFile, File dest){ ?250 ????????try { ?251 ????????????FileOutputStream out = new FileOutputStream(dest); ?252 ????????????out.write(excelFile); ?253 ????????????out.flush(); ?254 ????????????out.close(); ?255 ????????} catch (Exception e) { ?256 ????????????e.printStackTrace(); ?257 ????????} ?258 ????}259 ????/***260 ?????* 261 ?????* 在项目中创建临时文件262 ?????* @throws IOException 263 ?????* */264 ????public static File createTempFile(String fileName) throws IOException{265 ????????File path = new File(ResourceUtils.getURL("classpath:").getPath());266 ????????if(!path.exists()) path = new File("");267 ????????File upload = new File(path.getAbsolutePath(),"static/images/upload/");268 ????????if(!upload.exists()) upload.mkdirs();269 ????????File tempFile = new File(upload+"/"+fileName);270 ????????if(!tempFile.getParentFile().exists()){271 ????????????tempFile.getParentFile().mkdirs();//创建父级文件路径272 ????????????tempFile.createNewFile();//创建文件273 ????????}274 ????????return tempFile;275 ????}276 }

 ?1 @PostMapping("/addBatchDevice") ?2 ????public ResponseObj addBatchDevice(@RequestBody JSONObject obj) throws IOException{ ?3 ????????logger.info("导入批量设备："+obj.toJSONString()); ?4 ????????ResponseObj response = new ResponseObj(); ?5 ????????String id = obj.getString("id"); ?6 ????????BatchRecord batchRecord = deviceService.selectBatchRecordById(id); ?7 ????????String path = aliConstants.aliyunHostOuter+"/"+batchRecord.getFilePath(); ?8 // ???????将该该文件下载出来保存到项目中 ?9 ????????byte[] excelFile = ReadExcelUtil.getExcelFromAliyun(path); 10 ????????File tempFile = ReadExcelUtil.createTempFile(batchRecord.getFileName()); 11 ????????ReadExcelUtil.writeFileToDest(excelFile, tempFile); 12 ????????String url = tempFile.getAbsolutePath(); 13 ????????String companyId = null; 14 ????????String productId = null; 15 ????????Integer deviceType = null; 16 ????????String model = null; 17 ????????String deviceName = null; 18 ????????boolean flag = ReadExcelUtil.checkExcelTemplate(url); 19 ????????if(!flag){ 20 ????????????response.setData(Defined.STATUS_ERROR); 21 ????????????response.setMessage("文件有误，请根据模板上传文件。"); 22 ????????????return response; 23 ????????} 24 ????????List<Integer> result = new ArrayList<Integer>();//存放反馈信息 ?25 ????????try { 26 ????????????List<List<String>> list = ReadExcelUtil.readExcelInfo(url); 27 ????????????for(int i=0;i<list.size();i++){ 28 ????????????if(i==0||i==2){ 29 ????????????????continue;//跳过模板第1,3行 30 ????????????} ????31 ????????????if(i==1){ 32 ????????????????companyId = list.get(i).get(0); 33 ????????????????productId = list.get(i).get(1); 34 ????????????????deviceType = Integer.valueOf(list.get(i).get(2)); 35 ????????????????model = list.get(i).get(3); 36 ????????????????deviceName = list.get(i).get(4); 37 ????????????} 38 ????????????if(i>2){ 39 // ???????????????new一个对象按照相应的字段设置进去就可以了，这里省略对象设置值，字段如下： 40 ????????????????Device device = new Device(); 41 ????????????????String deviceId = IdGen.uuid(); 42 ????????????????device.setId(deviceId); 43 ????????????????//省略部分业务代码 44 ????????????????DeviceFields deviceFields = new DeviceFields(); 45 ????????????????deviceFields.setId(IdGen.uuid()); 46 ????????????????deviceFields.setDeviceId(deviceId); 47 ????????????????//省略部分业务代码 48 ????????????????Gateway gateway = new Gateway(); 49 ????????????????gateway.setId(IdGen.uuid()); 50 ????????????????//省略部分业务代码 51 ????????????????if(!deviceService.checkDeviceUidRepeat(uid)){ 52 // ???????????????????重复，返回sheet行号，并写入sheet表单中 53 ????????????????????result.add(1); 54 ????????????????????continue; 55 ????????????????} 56 // ???????????????关联表一个事务处理 57 ????????????????boolean flg = deviceService.addDeviceEtc(device, deviceFields, gateway); 58 ????????????????if(!flg){ 59 ????????????????????result.add(1); 60 ????????????????}else{ 61 ????????????????????result.add(0); 62 ????????????????} 63 ????????????} 64 ????????} 65 // ???????????将反馈结果写入文件0-成功，1-失败 66 ????????????ReadExcelUtil.writeExcelResult(url, result); 67 ????????????AliYunFileSetting setting = new AliYunFileSetting(); 68 ????????????setting.setAccessKeyId(aliConstants.accessKeyId); 69 ????????????setting.setAccessKeySecret(aliConstants.accessKeySecret); 70 ????????????setting.setBucketName(aliConstants.bucketName); 71 ????????????setting.setEndpoint(aliConstants.endpoint); 72 ????????????AliyunFileManager manager = AliyunFileManager.getInstance(setting); 73 ????????????InputStream is = new FileInputStream(tempFile); 74 ????????????String relativePath =aliConstants.excelFilePath;//相对路径 75 ????????????boolean fg = manager.upload(is, relativePath, batchRecord.getFileName());//上传文件与客户上传后生成的文件名相同。 76 ????????????if(fg){ 77 ????????????????logger.info("反馈已经成功写入文件中！"); 78 // ???????????更新批量记录 79 ????????????????batchRecord.setUpdateTime(DateUtil.getNowTimestamp()); 80 ????????????????deviceService.updateBatchRecordByPrimaryKey(batchRecord); 81 ????????????} 82 ????} catch (Exception e) { 83 ????????????// TODO Auto-generated catch block 84 ????????????e.printStackTrace(); 85 ????????} 86 ????????response.setMessage("批量导入设备成功！"); 87 ????????response.setStatus(Defined.STATUS_SUCCESS); 88 ????????return response; 89 ????} 90 ????@PostMapping("uploadExcel") 91 ?????public ResponseObj uploadExcel(@RequestParam("excelFile") MultipartFile file,@RequestParam("companyId") String companyId, 92 ?????????????@RequestParam("productId") String productId,HttpServletRequest request) throws Exception { 93 ????????ResponseObj response = new ResponseObj(); 94 ????????response.setData(Defined.STATUS_SUCCESS); 95 ????????response.setMessage("文件上传成功！"); 96 ????????ResponseObj resp = new ResponseObj(); 97 ????????resp.setData(Defined.STATUS_ERROR); 98 ????????resp.setMessage("不是文件！"); 99 ????????AliYunFileSetting setting = new AliYunFileSetting();100 ????????setting.setAccessKeyId(aliConstants.accessKeyId);101 ????????setting.setAccessKeySecret(aliConstants.accessKeySecret);102 ????????setting.setBucketName(aliConstants.bucketName);103 ????????setting.setEndpoint(aliConstants.endpoint);104 ????????AliyunFileManager manager = AliyunFileManager.getInstance(setting);105 ????????if(file.isEmpty()){106 ????????????response.setData(Defined.STATUS_ERROR);107 ????????????response.setMessage("不是文件！");108 ????????????return response;109 ???????????}110 ????????String fileName = file.getOriginalFilename();111 ????????long fileSize = file.getSize();112 ????????File tempFile = ReadExcelUtil.createTempFile(fileName);113 ????????String relativePath =aliConstants.excelFilePath;//相对路径114 ????????String dir = ?aliConstants.aliyunHostOuter+"/"+relativePath;//云端绝对路径115 ????????File dest = new File(dir);116 ????????if(!dest.exists()){ //判断文件目录是否存在117 ????????????dest.mkdir();//118 ????????}119 ????????try {120 ????????????file.transferTo(tempFile); 121 ????????????InputStream is = new FileInputStream(tempFile);122 ????????????String suffix=fileName.substring(fileName.lastIndexOf("."));//获取原始文件后缀.xlxs(含点)123 ????????????String newFileName = IdGen.uuid()+suffix;124 ????????????boolean result = manager.upload(is, relativePath, newFileName);//上传文件，并建立随机文件名。125 // ?????????boolean result = manager.upload(is, dir, fileName);//上传阿里云，固定文件名126 ????????????if(result){127 ?????????????????response.setData(dir+"/"+newFileName);//返回新建文件名的绝对路径128 // ????????????????数据库存入相对路径129 ?????????????????BatchRecord batchRecord = new BatchRecord();130 ?????????????????batchRecord.setFileName(newFileName);131 ?????????????????batchRecord.setFilePath(relativePath+"/"+newFileName);132 ?????????????????batchRecord.setFileSize(fileSize);133 ?????????????????batchRecord.setIsDelete((byte) 0);134 ?????????????????batchRecord.setStatus((byte) 0);135 ?????????????????batchRecord.setId(IdGen.uuid());136 ?????????????????batchRecord.setCreateTime(DateUtil.getNowTimestamp());137 // ????????????????batchRecord.setUpdateTime(DateUtil.getNowTimestamp());138 ?????????????????batchRecord.setCompanyId(companyId);139 ?????????????????batchRecord.setProductId(productId);140 ?????????????????Integer resultNum ?= deviceService.addBatchRecord(batchRecord);141 ?????????????????if(resultNum>0){142 ?????????????????????tempFile.delete();143 ?????????????????return response;144 ?????????????????}145 ?????????????????return resp;146 ????????????}else{147 ????????????????resp.setMessage("文件上传失败！");148 ????????????????return resp;149 ????????????}150 ????????} catch (IllegalStateException e) {151 ????????????// TODO Auto-generated catch block152 ????????????e.printStackTrace();153 ????????????resp.setMessage("文件上传异常！");154 ????????????return resp;155 ????????} catch (IOException e) {156 ????????????// TODO Auto-generated catch block157 ????????????e.printStackTrace();158 ????????????resp.setMessage("文件上传异常！");159 ????????????return resp;160 ????????}161 ????}