java Excel的導入導出工具類總結

1、導入

<code>public String importExcel(@RequestParam("excelPath") MultipartFile file,@RequestParam("fileName") String fileName){
        	try {
        			if(!file.isEmpty()) {
        						BASE64Decoder decoder = new BASE64Decoder();
                    String filename = new String(decoder.decodeBuffer(fileName),"UTF-8");
                    int index = filename.lastIndexOf(".");
                    filename = filename.substring(0, index) + "_" + userid + filename.substring(index, filename.length());
                    File fileDir = FileUtils.getExeclDirFile(super.uploadPath);
                    File newFile = new File(fileDir.getAbsolutePath() + File.separator + filename);
                    file.transferTo(newFile);
                    
										//各種驗證
                   if (!fileName.matches("^.+\\.(?i)(xls)#34;) && !fileName.matches("^.+\\.(?i)(xlsx)#34;)) {
                       throw new Exception("上傳文件格式不正確");
                   }
                   boolean isExcel2003 = true;
                   if (fileName.matches("^.+\\.(?i)(xlsx)#34;)) {
                       isExcel2003 = false;
                   }
                   Workbook wb = null;
                   InputStream is = new FileInputStream(file);
         					 try{
                         if (isExcel2003) {
                             wb = new HSSFWorkbook(is);
                         } else {
                             wb = new XSSFWorkbook(is);
                         }
             						Sheet sheet = wb.getSheetAt(0);
             						Row row = sheet.getRow(2);//第一行
             						XSSFCell cell = (XSSFCell) row.getCell(1);
                        if(cell == null) {
                             return FastJsonUtils.resultSuccess(400, "失敗","格式驗證");
                        }
             						cell.setCellType(CellType.STRING);
             						String year = cell.getStringCellValue();
                        if(StringUtils.isEmpty(year)){
                         		return FastJsonUtils.resultSuccess(400, "失敗","格式驗證");
                        }
                     		//從第幾列開始
             						if(sheet.getLastRowNum() >=4) {
            	 								for (int r = 4; r  
<= sheet.getLastRowNum(); r++) {
            		 											row = sheet.getRow(r);
            		 											if (row == null){
                                         		continue;
                                       }
            		 											//獲取每列的數據
            												  XSSFCell cellDealerCode = (XSSFCell) row.getCell(0);
                                     if(cellDealerCode == null) {
                                             continue;
                                     }
                                     cellDealerCode.setCellType(CellType.STRING);
                                     String str = cellDealerCode.getStringCellValue();
                                		 //TODO 處理業務邏輯
                              }
                        }  
         					}finally {
                     if(wb != null) {wb.close(); }
                     if (is != null){is.close();}
								}
                } else {
                	return FastJsonUtils.resultSuccess(400, "失敗","文件為空!");
                }
    		} catch (Exception e) {
    				return FastJsonUtils.resultSuccess(400, "失敗","上傳文件失敗,請重試!");
    		}
  }/<code>

2、導出

<code>public void downExcel(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException {
		String filename = "要顯示的文件名.xlsx";
		InputStream input = null;
		OutputStream outputStream = null;
		SXSSFWorkbook wb = null;
		XSSFWorkbook swb = null;
		try {
			ClassPathResource cpr = new ClassPathResource("/template/"+filename);
			input = cpr.getInputStream();
			swb = (XSSFWorkbook) WorkbookFactory.create(input);
			wb = new SXSSFWorkbook(swb,-1);
			Sheet sheet = wb.getSheetAt(0); // 工作表對象
			Row nRow = null; // 行對象
			Cell nCell = null; // 列對象
      //設置格式樣式
			CellStyle style = ExportExcelUtil.setStyle(wb, null, true, true, CommonType.FONTNAME, 10, false);
			//TODO 根據自己的需求賦值
			nCell = swb.getSheetAt(0).getRow(2).getCell(1);
			nCell.setCellValue("第一個sheet頁第三行的第二格賦值");
      //處理各瀏覽器的兼容，防止亂碼
			String agent = request.getHeader("USER-AGENT");  
	    if (null != agent && (-1 != agent.indexOf("MSIE") || -1 != agent.indexOf("Trident") || -1 != agent.indexOf("Edge"))) {
	        	codedfilename = java.net.URLEncoder.encode(filename, "UTF8");  
	    } else {
	            codedfilename = new String(filename.getBytes("UTF-8"), "iso-8859-1");  
	    }  
			response.setContentType("application/msexcel;charset=UTF-8");
	    response.setCharacterEncoding("UTF-8");
	    response.addHeader("Content-Disposition", "attachment;filename=""+ codedfilename + """);
			response.flushBuffer();
			outputStream = response.getOutputStream();
			wb.write(response.getOutputStream());
			outputStream.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (swb != null) {swb.close();}
				if (wb != null) {wb.close();}
				if (outputStream != null) {outputStream.close();}
				if (input != null) {input.close();}
			} catch (Exception e) {}
		}
	}/<code>

3、單元格樣式設置

<code>public static CellStyle setStyle(Workbook wb,Object backgroundColor,boolean isAlignment,boolean isWrapText,String fontName,Object fontSize,boolean isBold) {
		CellStyle style = wb.createCellStyle();
		if(backgroundColor != null) {
			style.setFillForegroundColor(Short.valueOf(backgroundColor.toString()));// 設置背景色
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		}
		if(isAlignment) {
			style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
			style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
		}
		if(isWrapText) {
			style.setWrapText(true);//設置自動換行 
		}
		//在樣式用應用設置的字體; 
		Font font = wb.createFont();
		if(!StringUtils.isEmpty(fontName)) {
			font.setFontName(fontName);//設置字體名字 
		}
		if(fontSize != null) {
			font.setFontHeightInPoints(Short.valueOf(fontSize.toString()));//設置字體大小
		}
        if(isBold) {
        	font.setBold(true);
        }
        style.setBorderBottom(BorderStyle.THIN);//下邊框    
        style.setBorderLeft(BorderStyle.THIN);//左邊框   
        style.setBorderTop(BorderStyle.THIN);//上邊框    
    	style.setBorderRight(BorderStyle.THIN);//右邊框
        style.setFont(font);
        return style;
	}/<code>

好好學習，天天搬磚，請點擊關注學習更多java小知識

關鍵字: fileName response 導入