解析excel
解析上传的excel
1 public List<Asset> readXlsxMessage(String path) throws IOException, ParseException { 2 InputStream is = new FileInputStream(path); 3 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); 4 5 Asset asset = null; 6 List<Asset> set = new ArrayList<Asset>(); 7 // for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) { 8 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); 9 if (xssfSheet == null) { 10 return null; 11 } 12 int i = xssfSheet.getLastRowNum(); 13 for (int rowNum = 1; rowNum <= i; rowNum++) { 14 XSSFRow xssfRow = xssfSheet.getRow(rowNum); 15 if (xssfRow != null) { 16 asset = new Asset(); 17 XSSFCell assetId = xssfRow.getCell(1); 18 XSSFCell title = xssfRow.getCell(2); 19 XSSFCell assetDesc = xssfRow.getCell(3); 20 XSSFCell keyword = xssfRow.getCell(4); 21 XSSFCell anchor = xssfRow.getCell(5); 22 XSSFCell category = xssfRow.getCell(6); 23 XSSFCell subCategory = xssfRow.getCell(7); 24 XSSFCell duration = xssfRow.getCell(8); 25 XSSFCell uploadTime= xssfRow.getCell(9); 26 XSSFCell posterUrl = xssfRow.getCell(10); 27 XSSFCell imgUrl = xssfRow.getCell(11); 28 XSSFCell videoUrl = xssfRow.getCell(12); 29 XSSFCell state1 = xssfRow.getCell(13); 30 XSSFCell state2 = xssfRow.getCell(15); 31 XSSFCell state3 = xssfRow.getCell(17); 32 33 String st1=getValue(state1).trim(); 34 String st2=getValue(state2).trim(); 35 String st3=getValue(state3).trim(); 36 if("通过".equals(st1)&&st1.equals(st2)&&st1.equals(st3)&&!getValue(assetId).isEmpty()){ 37 asset.setAssetId(getValue(assetId)); 38 if(StringUtils.isNoneBlank(getValue(title))){ 39 asset.setTitle(getValue(title)); 40 } 41 if(StringUtils.isNoneBlank(getValue(assetDesc))){ 42 asset.setAssetDesc(getValue(assetDesc)); 43 } 44 if(StringUtils.isNoneBlank(getValue(keyword))){ 45 asset.setKeyword(getValue(keyword)); 46 } 47 if(StringUtils.isNoneBlank(getValue(anchor))){ 48 asset.setAnchor(getValue(anchor)); 49 } 50 if(StringUtils.isNoneBlank(getValue(category))){ 51 aasset.setCategory(getValue(category)); 52 } 53 if(StringUtils.isNoneBlank(getValue(subCategory))){ 54 asset.setSubCategory(getValue(subCategory)); 55 } 56 if(StringUtils.isNoneBlank(getValue(duration))){ 57 asset.setDuration(toSecond(getValue(duration))); 58 } 59 if(StringUtils.isNoneBlank(getValue(uploadTime))){ 60 asset.setUploadTime(Timestamp.valueOf(getValue(uploadTime))); 61 } 62 if(StringUtils.isNoneBlank(getValue(posterUrl))){ 63 asset.setPosterUrl(getValue(posterUrl)); 64 } 65 if(StringUtils.isNoneBlank(getValue(imgUrl))){ 66 asset.setImgUrl(getValue(imgUrl)); 67 } 68 if(StringUtils.isNoneBlank(getValue(videoUrl))){ 69 asset.setVideoUrl(getValue(videoUrl)); 70 } 71 asset.setImgStatus(0); 72 asset.setVideoStatus(0); 73 set.add(fengxingSpAsset); 74 } 75 } 76 } 77 // } 78 if (is != null) { 79 is.close(); 80 } 81 return set; 82 } 83
1 public List<Asset> readXlsMessage(String path) throws IOException, ParseException{ 2 InputStream is = new FileInputStream(path); 3 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 4 5 Asset asset = null; 6 List<Asset> set = new ArrayList<Asset>(); 7 // for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) { 8 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); 9 if (hssfSheet == null) { 10 return null; 11 } 12 int i = hssfSheet.getLastRowNum(); 13 for (int rowNum = 1; rowNum <= i; rowNum++) { 14 HSSFRow hssfRow = hssfSheet.getRow(rowNum); 15 if (hssfRow != null) { 16 asset = new Asset(); 17 HSSFCell assetId = hssfRow.getCell(1); 18 HSSFCell title = hssfRow.getCell(2); 19 HSSFCell assetDesc = hssfRow.getCell(3); 20 HSSFCell keyword = hssfRow.getCell(4); 21 HSSFCell anchor = hssfRow.getCell(5); 22 HSSFCell category = hssfRow.getCell(6); 23 HSSFCell subCategory = hssfRow.getCell(7); 24 HSSFCell duration = hssfRow.getCell(8); 25 HSSFCell uploadTime= hssfRow.getCell(9); 26 HSSFCell posterUrl = hssfRow.getCell(10); 27 HSSFCell imgUrl = hssfRow.getCell(11); 28 HSSFCell videoUrl = hssfRow.getCell(12); 29 HSSFCell state1 = hssfRow.getCell(13); 30 HSSFCell state2 = hssfRow.getCell(15); 31 HSSFCell state3 = hssfRow.getCell(17); 32 33 String st1=getValue(state1).trim(); 34 String st2=getValue(state2).trim(); 35 String st3=getValue(state3).trim(); 36 if("通过".equals(st1)&&st1.equals(st2)&&st1.equals(st3)&&!getValue(assetId).isEmpty()){ 37 asset.setAssetId(getValue(assetId)); 38 if(StringUtils.isNoneBlank(getValue(title))){ 39 asset.setTitle(getValue(title)); 40 } 41 if(StringUtils.isNoneBlank(getValue(assetDesc))){ 42 asset.setAssetDesc(getValue(assetDesc)); 43 } 44 if(StringUtils.isNoneBlank(getValue(keyword))){ 45 asset.setKeyword(getValue(keyword)); 46 } 47 if(StringUtils.isNoneBlank(getValue(anchor))){ 48 asset.setAnchor(getValue(anchor)); 49 } 50 if(StringUtils.isNoneBlank(getValue(category))){ 51 asset.setCategory(getValue(category)); 52 } 53 if(StringUtils.isNoneBlank(getValue(subCategory))){ 54 asset.setSubCategory(getValue(subCategory)); 55 } 56 if(StringUtils.isNoneBlank(getValue(duration))){ 57 asset.setDuration(toSecond(getValue(duration))); 58 } 59 if(StringUtils.isNoneBlank(getValue(uploadTime))){ 60 asset.setUploadTime(Timestamp.valueOf(getValue(uploadTime))); 61 } 62 if(StringUtils.isNoneBlank(getValue(posterUrl))){ 63 asset.setPosterUrl(getValue(posterUrl)); 64 } 65 if(StringUtils.isNoneBlank(getValue(imgUrl))){ 66 asset.setImgUrl(getValue(imgUrl)); 67 } 68 if(StringUtils.isNoneBlank(getValue(videoUrl))){ 69 asset.setVideoUrl(getValue(videoUrl)); 70 } 71 asset.setImgStatus(0); 72 asset.setVideoStatus(0); 73 set.add(fengxingSpAsset); 74 } 75 } 76 } 77 // } 78 if (is != null) { 79 is.close(); 80 } 81 return set; 82 }
84 // 获取单元格的值 全部转换为 String类型 85 /** 86 * @param cell 87 * @return 88 */ 89 private String getValue(Cell cell) { 90 if (cell != null && !cell.toString().equals("")) { 91 if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { 92 return String.valueOf(cell.getBooleanCellValue()); 93 } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { 94 cell.setCellType(Cell.CELL_TYPE_STRING); 95 return String.valueOf(cell.getStringCellValue()); 96 } else { 97 return String.valueOf(cell.getStringCellValue()); 98 } 99 } else { 100 return ""; 101 } 102 }
续写上传的excel并导出
1 /** 2 * 支持读取 xlsx 3 * 4 * @param path 5 * @param file 6 * @return 7 * @throws Exception 8 */ 9 public void readXlsxMessage(String path, HttpServletResponse response) throws Exception { 10 InputStream is = new FileInputStream(path); 11 OutputStream fileOut = response.getOutputStream(); 12 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); 13 if (is != null) { 14 is.close(); 15 } 16 17 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) { 18 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet); 19 if (xssfSheet == null) { 20 continue; 21 } 22 // Read the Row 23 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { 24 XSSFRow xssfRow = xssfSheet.getRow(rowNum); 25 if (xssfRow != null) { 26 XSSFCell assetId = xssfRow.getCell(3); 27 XSSFCell customerCode = xssfRow.createCell(27); 28 29 if (StringUtils.isBlank(getValue(assetId))) { 30 continue; 31 }
//从url获取需要写入的结果 32 StringBuffer url = new StringBuffer("http://XXXXXXXXX?XXXXXX&xmlStr=<oss-request><property+name=\"searchKey\"+value=\""); 33 url.append(getValue(assetId)); 34 url.append("\"/><property+name=\"searchType\"+value=\"7\"/></oss-request>"); 35 JSONObject getRes = HttpRequestUtil.httpGETRequest(url.toString().trim()); 36 if("0".equals(getRes.getJSONObject("result").getString("retCode"))){ 37 customerCode.setCellValue(getRes.getJSONObject("data").getString("customerCode")); 38 }else{ 39 customerCode.setCellValue("查询失败"); 40 } 41 } 42 } 43 } 44 response.setContentType("application/X-excel"); 45 response.setHeader("Content-Disposition", 46 "attachment;filename=" + new String("文件名.xlsx".getBytes("GBK"), "ISO-8859-1")); 47 try { 48 xssfWorkbook.write(fileOut); 49 } catch (Exception e) { 50 51 } 52 if (fileOut != null) { 53 fileOut.close(); 54 } 55 } 56 57 /** 58 * 59 * 读取excell文件 ，支持Xls 60 * 61 * @param path 62 * @param file 63 * @return 64 * @throws Exception 65 */ 66 public void readXlsMessage(String path,HttpServletResponse response) throws Exception { 67 OutputStream fileOut = response.getOutputStream(); 68 InputStream is = new FileInputStream(path); 69 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 70 if (is != null) { 71 is.close(); 72 } 73 74 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 75 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 76 if (hssfSheet == null) { 77 continue; 78 } 79 // Read the Row 80 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 81 HSSFRow hssfRow = hssfSheet.getRow(rowNum); 82 if (hssfRow != null) { 83 HSSFCell assetId = hssfRow.getCell(3); 84 HSSFCell customerCode = hssfRow.createCell(27); 85 if (StringUtils.isBlank(getValue(assetId))) { 86 continue; 87 } 88 StringBuffer url = new StringBuffer("http://XXX"); 89 url.append(getValue(assetId)); 90 url.append("\"/><property+name=\"searchType\"+value=\"7\"/></oss-request>"); 91 JSONObject getRes = HttpRequestUtil.httpGETRequest(url.toString().trim()); 92 if(0==new Integer(getRes.getJSONObject("result").getString("retCode"))){ 93 JSONObject object = getRes.getJSONObject("data"); 94 String string = object.getString("customerCode"); 95 customerCode.setCellValue(getRes.getJSONObject("data").getString("customerCode")); 96 }else{ 97 customerCode.setCellValue("查询失败"); 98 } 99 } 100 } 101 } 102 response.setContentType("application/X-excel"); 103 response.setHeader("Content-Disposition", 104 "attachment;filename=" + new String("文件名.xls".getBytes("GBK"), "ISO-8859-1")); 105 try { 106 hssfWorkbook.write(fileOut); 107 } catch (Exception e) { 108 } 109 if (fileOut != null) { 110 fileOut.close(); 111 } 112 113 }
新建excel并导出
1 public void exportAssetFile(HttpServletResponse response, AssetFile assetFile) 2 throws IOException { 3 // Integer count = findYoukuSPAssetFileListCount(youkuAssetFile); 4 /* 5 * int pageSize=50000; int pageNo=0; 6 */ 7 OutputStream fileOut = response.getOutputStream(); 8 SXSSFWorkbook wb = new SXSSFWorkbook(120); 9 // int s=(count+50000)/pageSize;//页数sheet 10 11 // int j = 1;//行 12 // for (int i = 0; i < s; i++) { 13 Sheet sheet = wb.createSheet("sheet表名"); 14 Row header = sheet.createRow((short) 0); 15 sheet.createFreezePane(0, 1, 0, 1); 16 header.createCell(0).setCellValue("序号"); 17 header.createCell(1).setCellValue("资产id"); 18 header.createCell(2).setCellValue("资产名称"); 19 header.createCell(3).setCellValue("总集数"); 20 header.createCell(4).setCellValue("视频顺序"); 21 header.createCell(5).setCellValue("文件唯一ID"); 22 header.createCell(6).setCellValue("文件名称"); 23 header.createCell(7).setCellValue("创建时间"); 24 header.createCell(8).setCellValue("是否收费"); 25 header.createCell(9).setCellValue("AuditResult"); 26 //获取要写入的资产 27 List<AssetFile> list = youkuAssetFileList(assetFile); 28 /* 29 * List<YoukuSpAssetFile> list =null; if(pageNo!=s){ list 30 * =(List<YoukuSpAssetFile>) 31 * youkuAssetFileList(youkuAssetFile,pageNo*pageSize,pageSize); }else 32 * if(pageNo==s){ list =(List<YoukuSpAssetFile>) 33 * youkuAssetFileList(youkuAssetFile,pageNo*pageSize,count%50000); } 34 */ 35 36 // 写入sheet 37 witerToSheet(sheet, list); 38 //设置列宽 39 for (int k = 0; k < 9; k++) { 40 if (k == 6) { 41 sheet.setColumnWidth(6, (int) ((40 + 0.72) * 256)); 42 } else if (k == 2) { 43 sheet.setColumnWidth(2, (int) ((25 + 0.72) * 256)); 44 } else if (k == 8 || k == 9) { 45 sheet.setColumnWidth(8, (int) ((7 + 0.72) * 256)); 46 sheet.setColumnWidth(9, (int) ((7 + 0.72) * 256)); 47 } else { 48 sheet.autoSizeColumn(k); 49 } 50 } 51 // j=1; 52 // pageNo++; 53 // } 54 55 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); 56 Date time = new Date(); 57 String fileName = "YouKuSPAssetFile-" + sdf.format(time) + ".xlsx"; 58 response.setContentType("application/X-excel"); 59 response.setHeader("Content-Disposition", 60 "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO-8859-1")); 61 try { 62 wb.write(fileOut); 63 } catch (Exception e) { 64 } 65 if (fileOut != null) { 66 fileOut.close(); 67 } 68 } 69 70 private void witerToSheet(Sheet sheet, List<YoukuSpAssetFile> list) { 71 int j = 1; 72 for (AssetFile map : list) { 73 Row row = sheet.createRow(j++); 74 Cell cell0 = row.createCell(0); 75 cell0.setCellValue((j - 1)); 76 Cell cell1 = row.createCell(1); 77 cell1.setCellValue(String.valueOf(map.getAssetId())); 78 Cell cell2 = row.createCell(2); 79 cell2.setCellValue(String.valueOf(map.getAssetName())); 80 Cell cell3 = row.createCell(3); 81 cell3.setCellValue(String.valueOf(map.getItems())); 82 Cell cell4 = row.createCell(4); 83 cell4.setCellValue(String.valueOf(map.getSequence())); 84 Cell cell5 = row.createCell(5); 85 cell5.setCellValue(String.valueOf(map.getFileId())); 86 Cell cell6 = row.createCell(6); 87 cell6.setCellValue(String.valueOf(map.getFileName())); 88 Cell cell7 = row.createCell(7); 89 cell7.setCellValue(String.valueOf( 90 new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(map.getCreateTime().getTime()))); 91 Cell cell8 = row.createCell(8); 92 cell8.setCellValue(String.valueOf(map.getPaid() != null && map.getPaid() == 0 ? "免费" : "收费")); 93 Cell cell9 = row.createCell(9); 94 cell9.setCellValue(String.valueOf("")); 95 } 96 }
