在SSM中使用POI-4.0.1实现Excel模板下载、Excel导入功能

一、引入jar包

需要引入的jar包
poi-4.0.1.jar
poi-ooxml-4.0.1.jar
poi-ooxml-schemas-4.0.1.jar
xmlbeans-3.0.2.jar
commons-collections4-4.2.jar
commons-compress-1.19.jar

传送门：https://pan.baidu.com/s/1sWCpuhOEPv0Oq7ltMBDf_Q
提取码：ybk6

二、代码实现

Controller层——下载Excel模板

模板存放路径

@RequestMapping("downloadExcelTemplate")
@ResponseBody
JSONObject downloadExcelTemplate(HttpServletResponse response,HttpServletRequest request,String fileName) throws Exception{
    JSONObject obj = new JSONObject();
    try {
        if(fileName!=null){
            //校验文件是否为Excel格式
            if(fileName.endsWith(".xls")||fileName.endsWith(".xlsx")){
                // 设置输出的格式
                response.reset();
                // 设置要下载的文件的名称
                response.setHeader("Content-disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
                // 通知客服文件的MIME类型
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                //获取文件的路径
                String excelPath = request.getSession().getServletContext().getRealPath("/template/"+fileName);

                //创建输入流对象，读取指定的文件
                FileInputStream input = new FileInputStream(excelPath);
                //创建文件输出流对象
                OutputStream out=response.getOutputStream();
                byte[] b = new byte[2048];
                int len;
                while ((len = input.read(b)) != -1) {
                    out.write(b, 0, len);
                };
                input.close();
            }
        }
    } catch (Exception e) {
        obj.element("message", e.getMessage());
    }
    return obj;
}

Service层

方法1——解析Excel表格，获取数据
【注】若Excel表中有一条数据插入数据库失败时，则需要进行回滚操作，关于事务回滚的实现可参考下面这篇博客：
链接: https://blog.csdn.net/weixin_47279190/article/details/107507037

@SuppressWarnings("resource")
@Transactional
public boolean importVehicelExcel(MultipartFile file) throws Exception {   
    boolean res = false;

    Workbook workbook = null ;
    String fileName = file.getOriginalFilename(); 
    if(fileName.endsWith(XLS)){  
        //xls文件  
        workbook = new HSSFWorkbook(file.getInputStream());  
    }else if(fileName.endsWith(XLSX)){  
        //xlsx文件  
        workbook = new XSSFWorkbook(file.getInputStream());  
    }
    else{ 
        throw new RuntimeException("文件不是Excel文件！");
    }

    Sheet sheet = workbook.getSheetAt(0);
    int rows = sheetRealRows(sheet);
    //计算导入成功数据条数
    int count = 0;	
    //本表格标题占3行
    if(rows==3){ throw new RuntimeException("请填写数据！"); }

    //数据行从第4行开始，故下标从3开始 
    for (int i = 3; i < rows; i++) {
        Row row = sheet.getRow(i);

        if(row != null) {
            Map<String, Object> mapper = new HashMap<String, Object>();
            Vehicle vehicle = new Vehicle();
            //根据Excel模板读取每一行的数据
            //车牌号
            String busname = getCellValue(row.getCell(0));
            if(vehicleMapper.findByBusName(busname)>0) { 
                //抛出异常，事务回滚——即前面已经执行的操作全部撤销
                throw new RuntimeException("车牌号重复！");
            } 
            mapper.put("busName", busname);
            //车牌颜色
            String carplatecolor = getCellValue(row.getCell(1));
            mapper.put("carPlateColor", carplatecolor);
            //座位数
            String seatStr = getCellValue(row.getCell(4));
            if (!StringUtils.isEmpty(seatStr)) {
                Integer seats = Integer.parseInt(seatStr);
                vehicle.setSeats(seats);
                mapper.put("seats", seats);
            }

            int success =  vehicleMapper.vehicleInsert(mapper);
            if(success>0) count++;
        }
    }
    //插入成功的行数等于Excel的有效数据行数，即总行数-标题行数（本表格标题占3行）
    if (count == rows-3) res =  true;	
    return res;
}

方法2——获取单元格内容

/**
	 * 获取单元格内容
	 * @param cell
	 * @return
	 */
	public String getCellValue(Cell cell) {
		 String value = "";
		 if (cell != null) {
			 // 以下是判断数据的类型
			 if(CellType.NUMERIC.equals(cell)) {
				 if ("General".equals(cell.getCellStyle().getDataFormatString())) {
	                 value = String.valueOf(cell.getNumericCellValue());
	             } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
	                 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	                 value = sdf.format(cell.getDateCellValue());
	             } else {
	                 value = String.valueOf(cell.getNumericCellValue());
	             }
			 }else {
                 cell.setCellType(CellType.STRING);//设置成String
                 value = cell.getStringCellValue();
             }
	 
		 }
		 return value;
	}

方法3——获取Excel表格有效数据行数（不包括带格式的空行）

public int sheetRealRows(Sheet sheet) {
		int rows = sheet.getLastRowNum();//原始数据行数
		int realRows=0;//有效数据行数
    
		//模板表格前三行为标题，有效数据从第四行开始（可修改）
		for (int i = 3; i <= rows; i++) {
			Row r = sheet.getRow(i);
            if(r == null){ 
            	break;
            }  
            //将第一列单元格内容统一设为字符串格式（假设该字段数据不能为空）
            r.getCell(0).setCellType(CellType.STRING);
            String valueString = r.getCell(0).getStringCellValue();
            //若不为空，则有效数据行数+1
            if(valueString!=null && valueString!="") {
            	realRows++;
            }else {
            	break;
            }
		}
		return realRows+3;
	}

前端

<!-- 批量导入弹出框 -->
<div style="display: none;">
	<div id="vehicleImportDiv">
		<div>
			<a href="javascript:;" class="easyui-linkbutton" iconCls="icon-save" plain="true" onclick='downloadTemplate()'>下载xls模板</a>
			<a href="javascript:;" class="easyui-linkbutton" iconCls="icon-save" plain="true" onclick='downloadTemplate2()'>下载xlsx模板</a>
		</div>
		<input id="vehicleImportFilebox" class="easyui-filebox" style="width:300px">
		<a href="javascript:;" class="easyui-linkbutton" iconCls="icon-save" plain="true" onclick='saveImportFile()'>确定</a>
	</div>
</div>

function saveImportFile(){
	var formData = new FormData();
	formData.append("vehicleExcel", $('#vehicleImportFilebox').filebox("files")[0]);
	$.ajax({
		url:"vehicleExcelImport",
		type:'POST',
        data: formData,
        cache: false,        // 不缓存数据
        processData: false,  // 不处理数据
        contentType: false,  // 不设置内容类型
        success: function (data) {           //成功回调
        	vehicleExcelImportRecieve(data);
        }
	
	})
}