1、导入

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)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {                       throw new Exception("上传文件格式不正确");                   }                   boolean isExcel2003 = true;                   if (fileName.matches("^.+\\.(?i)(xlsx)$")) {                       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, "失败","上传文件失败,请重试!");    		}  }

2、导出

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) {}		}	}

3、单元格样式设置

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;	}

好好学习，天天搬砖，请点击关注学习更多java小知识