Java上传下载Excel

        用户想导入一批数据，或者用户从旧系统换到一个新系统，往往需要转移大量的数据，这时需要一个导入功能来导入这些数据。主要的步骤为：“准备一个Excel模板文件”、“给用户下载的Excel模板文件”、“用户上传Excel文件”、“上传文件后端代码”、“用户下载有错误提示的Excel文件”。

1、准备一个Excel模板文件。

启用Excel的宏，添加VB语言对输入数据的检查，可以及早发现错误的格式并修正，也能减轻服务器端的格式检查的压力：

2、给用户下载的Excel模板文件。

下载文件前端HTML代码，使用了Layui前端框架：

<p class="ImportFlowHead">下载模板</p>
		<div class="head">
			<a href="/f/博销宝资料导入模板.xlsm" download="博销宝资料导入模板.xlsm"><i class="layui-icon layui-icon-download-circle"></i>
				<span>点击下载导入模板<span></a>

		</div>

/f为tomcat虚拟映射路径，在pom.xml配置：

<staticContextPath>/f</staticContextPath> 
					<staticContextDocbase>D:/nbr/file/public</staticContextDocbase>
		            <contextReloadable>false</contextReloadable>
		            <useTestClasspath>true</useTestClasspath>

3、用户上传Excel文件。

(1)上传文件HTML代码。

<p class="ImportFlowHead">导入文件</p>
		<div class="importFile">
			<button class="layui-btn" id='test'><i class="layui-icon layui-icon-upload-drag"></i>选择文件</button>
			<div class="layui-upload-list">
				<table class="layui-table">
					<thead>
						<tr>
							<th>文件名</th>
							<th>大小</th>
							<th>状态</th>
							<th>操作</th>
						</tr>
					</thead>
					<tbody id="demoList"></tbody>
				</table>
			</div>
			<button id='testListAction'>上传文件</button>
			<div class="layui-progress progress layui-progress-big " lay-filter="demo" lay-showPercent="true">
				<div class="layui-progress-bar layui-bg-green " id="progressCSS" lay-percent="0%"></div>
			</div>
		</div>

(2)上传Excel文件JS代码。

var demoListView = $('#demoList')
	uploadListIns = upload.render({
		elem: '#test',
		url: importData_url,
		auto: false, //选择文件后自动上传
		accept: 'file',
		bindAction: '#testListAction',
		size: 5120,
		exts: 'xlsm',
		multiple: false,
		choose: function (obj) {
			$('#progressCSS').attr('class', 'layui-progress-bar layui-bg-green')
			var files = this.files = obj.pushFile();
			obj.preview(function (index, file, result) {
				const fileName = '博销宝资料导入模板.xlsm';
				var chooseFileName = file.name;
				if (chooseFileName != fileName) {
					console.log("文件名错误");
					layer.msg("文件名必须为“博销宝资料导入模板.xlsm”");
					setTimeout(function () {
						window.location.reload()
					}, 1000);
					return (false);
				}
				var tr = $(['<tr id="upload-' + index + '" class = "files">'
					, '<td>' + file.name + '</td>'
					, '<td>' + (file.size / 1024).toFixed(1) + 'kb</td>'
					, '<td class="state">等待上传</td>'
					, '<td>'
					, '<button class="layui-btn layui-btn-xs demo-reload layui-hide">重传</button>'
					, '<button class="layui-btn layui-btn-xs layui-btn-danger demo-delete">删除</button>'
					, '</td>'
					, '</tr>'].join(''));
				tr.find('.demo-reload').on('click', function () {
					obj.upload(index, file);
				});
			……

4、上传文件后端代码。

(1)使用MultipartFile类接收上传的Excel文件。

@RequestMapping(value = "/importEx", produces = "plain/text; charset=UTF-8", method = RequestMethod.POST)
	@ResponseBody
	public String importEx(HttpSession session, @RequestParam("file") MultipartFile file) throws IllegalStateException, IOException {
		if (!canCallCurrentAction(session, BaseAction.EnumUserScope.STAFF.getIndex())) {
			logger.debug("无权访问本Action");
			return null;
		}

(2)检查文件的格式。

private boolean checkFile(Map<String, Object> param, MultipartFile file) {
		if (!TEMPLATE_FILE_NAME_ImportData.equals(file.getOriginalFilename())) {
			param.put(JSON_ERROR_KEY, EnumErrorCode.EC_WrongFormatForInputField);
			param.put(KEY_HTMLTable_Parameter_msg, "文件名称必须是" + TEMPLATE_FILE_NAME_ImportData);
			logger.error(JSONObject.fromObject(param, JsonUtil.jsonConfig).toString());
			return false;
		}
		if (!checkFileSize(file)) {
			param.put(BaseAction.JSON_ERROR_KEY, EnumErrorCode.EC_WrongFormatForInputField);
			param.put(BaseAction.KEY_HTMLTable_Parameter_msg, "文件大小不正确");
			logger.error(JSONObject.fromObject(param, JsonUtil.jsonConfig).toString());
			return false;
		}

		return true;
	}

	// (3)、上传文件空间大小是否大于系统文件存储空间
	// <= 5MB
	private boolean checkFileSize(MultipartFile file) {
		System.out.println("file.size():" + file.getSize() / 1024 + "KB");
		ErrorInfo ecOut = new ErrorInfo();
		BxConfigGeneral maxImportFileSize = (BxConfigGeneral) CacheManager.getCache(BaseAction.DBName_Public, EnumCacheType.ECT_BXConfigGeneral).read1(BaseCache.MAX_IMPORT_FILE_SIZE, BaseBO.SYSTEM, ecOut, BaseAction.DBName_Public);
		BxConfigGeneral extraDiskSpaceSize = (BxConfigGeneral) CacheManager.getCache(BaseAction.DBName_Public, EnumCacheType.ECT_BXConfigGeneral).read1(BaseCache.EXTRA_DISK_SPACE_SIZE, BaseBO.SYSTEM, ecOut, BaseAction.DBName_Public);
		logger.info("file:" + file);
		if(maxImportFileSize == null || extraDiskSpaceSize == null) {
			logger.error("maxImportFileSize=" + maxImportFileSize + ",extraDiskSpaceSize=" + extraDiskSpaceSize);
			return false;
		}
		File diskPath = new File("D:/");
		if (file.getSize() > Integer.valueOf(maxImportFileSize.getValue())) { // ...
			logger.error("file.getSize()=" + file.getSize() + ",maxImportFileSize.getValue()=" + maxImportFileSize.getValue());
			return false;
		} else if (diskPath.getUsableSpace() - Integer.valueOf(extraDiskSpaceSize.getValue()) < file.getSize()) {
			logger.error("磁盘空间不足!");
			return false;
		}
		return true;
	}

(3)将上传的Excel文件存储到本地。

将Excel文件存储到本地，如果有格式错误，就添加批注，供用户下载查看错误信息：

logger.info("将MultipartFile文件存储到本地");
		String direct = String.format(PATH_SaveMultipartFileToDestinationDirectory, company.getDbName());
		File destDirect = new File(direct);
		if  (!destDirect.exists() && !destDirect.isDirectory()) {       
		    System.out.println("目录不存在, 创建目录");  
		    destDirect.mkdir();    
		} else {  
		    System.out.println("目录存在");  
		}
		String xlsxFilePath = String.format(PATH_SaveMultipartFileToDestination, company.getDbName());
		File destFile = new File(xlsxFilePath);
		if(!destFile.exists()) {
			destFile.createNewFile();
			System.out.println("文件创建成功!");
		} else {
			System.out.println("文件已存在!");
		}
		try {
			file.transferTo(destFile);
		} catch(Exception e) {
			param.put(BaseAction.JSON_ERROR_KEY, EnumErrorCode.EC_OtherError);
			logger.error("保存文件失败：" + e.getMessage());
			return JSONObject.fromObject(param, JsonUtil.jsonConfig).toString();
		}

(4)使用POI读取Excel的数据加载到HashMap容器中。

private boolean loadExcelSheet(Map<String, Map<String, List<String>>> mapBaseModels, Map<String, Object> param, String xlsxFilePath) {
		int sheetNO = poiXls.readExcelSheetNO(xlsxFilePath);
		System.out.println("sheetNO:" + sheetNO);
		for (int i = 0; i < sheetNO; i++) {
			String sheetName = poiXls.readExcelSheetName(xlsxFilePath, i);
			if ("".equals(sheetName)) {
				break;
			}
			if(sheetName.equals("检查格式")) {
				continue;
			}
			System.out.println("\tsheetName=" + sheetName);
			//
			List<String> row = new ArrayList<String>();
			Map<String, List<String>> mapBaseModel = new HashMap<String, List<String>>();
			List<String> listCell = poiXls.readExcelCell(xlsxFilePath, i, 0); // 第0列的所有数据
			if (listCell == null) {
				param.put(BaseAction.JSON_ERROR_KEY, EnumErrorCode.EC_WrongFormatForInputField);
				logger.error(sheetName + "表有中间空白行");
				param.put(BaseAction.KEY_HTMLTable_Parameter_msg, "格式错误，" + sheetName + "表中间不能有空行");
				param.put(KEY_needToDownload, EnumBoolean.EB_Yes.getIndex());
				return false;
			}
			// 遍历工作表的所有行
			for (int k = 0; k < listCell.size(); k++) {
				row = poiXls.readExcelRow(xlsxFilePath, i, k);
				if (row == null) {
					break;
				}
				// 以工作表的行作为key
				mapBaseModel.put(String.valueOf(k), row);
			}
			mapBaseModels.put(sheetName, mapBaseModel);
		}
		return true;
	}

(5)将Excel中的数据转为Java对象。

private boolean parseCommodityFromExcel(String dbName, int staffID, HashMap<String, Integer> brandNameSet, HashMap<String, Integer> categorySet, Map<String, Map<String, List<String>>> mapBaseModels,
			HashMap<String, Integer> packageUnitSet, HashMap<String, Integer> providerSet, Map<String, Object> param, String xlsxFilePath) {
		// 获取商品信息
		Map<String, List<String>> commoditySheet = mapBaseModels.get(SHEET_NAME_Commodity);
		if (commoditySheet == null) {
			logger.info("没有需要创建的商品信息！");
			return true;
		}
		List<String> listRowTitle = mapBaseModels.get(SHEET_NAME_Commodity).get(COMMODITY_SHEET_RowIndex); // 标题
		for (int i = 3; i < commoditySheet.size(); i++) {
			commodityTotalToCreate++;
			List<String> comm = mapBaseModels.get(SHEET_NAME_Commodity).get(i + "");
			if (comm == null) {
				continue;
			}
			Map<String, Object> params = new HashMap<String, Object>();
			putIntoHashMap(listRowTitle, comm, params);
			setCommodityProperty(params);
			Commodity commodity = new Commodity();
			commodity = (Commodity) commodity.parse1(params.toString());
			if (commodity == null) {
				String commodityField = new Commodity().doParse1_returnField(params.toString());
				addCommentInCommoditySheet(param, i, commodityField, "格式不正确", xlsxFilePath);
				commodityWrongFormatNumber++;
				continue;
			}
		……

	public BaseModel parse1(String s) {
		try {
			return doParse1(JSONObject.fromObject(s));
		} catch (JSONException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	protected BaseModel doParse1(JSONObject jo) {
		try {
			ID = jo.getInt(field.getFIELD_NAME_ID());
			status = jo.getInt(field.getFIELD_NAME_status());
			name = jo.getString(field.getFIELD_NAME_name());
			shortName = jo.getString(field.getFIELD_NAME_shortName());
			……
			listSlave1 = listCommSlave;
			// 查询商品门店集合
			List<CommodityShopInfo> listCommShopSlave = new ArrayList<CommodityShopInfo>();
			JSONArray jrCommShopSlave = jo.getJSONArray(field.getFIELD_NAME_listSlave2());
			for (int i = 0; i < jrCommShopSlave.size(); i++) {
				JSONObject object2 = jrCommShopSlave.getJSONObject(i);
				CommodityShopInfo commodityShopInfo = (CommodityShopInfo) new CommodityShopInfo().parse1(object2.toString());
				listCommShopSlave.add(commodityShopInfo);
			}
			listSlave2 = listCommShopSlave;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this;
	}

(6)使用OkHttp请求创建数据的接口。

由于创建数据的接口是比较复杂的，所以创建时还是使用OkHttp调用已写好的接口：

for (Commodity commodity : commodityListToCreate) {
			Commodity com = createCommodity("commoditySync/createEx.bx", commodity, xlsxFilePath, param, logger);
			if (com == null) {
				continue;
			}
		}

	public Commodity createCommodity(String url, Commodity commodity, String xlsxFilePath, Map<String, Object> param, Log logger) {
		String response = AppUtil.createCommodityViaOkHttp(url, commodity);
		logger.debug("创建商品响应：" + response);

	public static String createCommodityViaOkHttp(String url, Commodity commodity) {
		// 将下面塞字段的代码放进Commodity中，统一管理Model的字段，避免增减字段时忘记修改本段代码。
		Map<String, String> params = commodity.putInMap();
		String response = OkHttpUtil.post(url, params); // TODO 要做失败处理。如果是名称重复，让导入继续。
		return response;
	}

	/** post
	 *
	 * @param url
	 *            请求的url
	 * @param params
	 *            post form 提交的参数
	 * @param okHttpClient
	 * @return */
	public static String post(String url, Map<String, String> params) {
		String responseBody = "";
		FormBody.Builder builder = new FormBody.Builder();
		// 添加参数
		if (params != null && params.keySet().size() > 0) {
			for (String key : params.keySet()) {
				if (params.get(key) != null) {
					builder.add(key, params.get(key));
				}
			}
		}
		System.out.println(httpUrl + url);
		Request request = null;
		if (sessionID == null) {
			request = new Request.Builder() //
					.url(httpUrl + url) //
					.post(builder.build()) //
					.build(); //
		} else {
			request = new Request.Builder() //
					.addHeader(COOKIE, sessionID) //
					.url(httpUrl + url) //
					.post(builder.build()) //
					.build(); //
		}
		Response response = null;
		OkHttpClient okHttpClient = new OkHttpClient.Builder() //
				.connectTimeout(30, TimeUnit.SECONDS)// 设置连接超时时间
				.readTimeout(30, TimeUnit.SECONDS)// 设置读取超时时间
				.build();
		try {
			response = okHttpClient.newCall(request).execute();
			int status = response.code();
			if (status == 200) {
				if (url.equals("/staff/getTokenEx.bx")) {
					Headers headers = response.headers();
					List<String> cookies = headers.values("Set-Cookie");
					String cookie = cookies.get(0);
					sessionID = cookie.substring(0, cookie.indexOf(";"));
				}
//				System.out.println("+++++++++++++++++++调用OKHttp成功，时间:" + new Date() + "," + response.body().string());
				return response.body().string();
			} else {
				System.out.println("status:" + status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("机器人Http请求失败");
		} finally {
			if (response != null) {
				response.close();
			}
		}
		return responseBody;
	}

(7)Poi给Excel添加批注。

如果某个单元格式不正确，我们可以给该单元格添加格式，以便用户下载查看错误信息：

cell = row.createCell((short)cellNO);
			Drawing draw = sheet.createDrawingPatriarch();
			Comment comment = draw.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) 3, 3, (short) 5, 6));
			comment.setString(new XSSFRichTextString(commentContent));//设置批注内容
			cell.setCellComment(comment);
			CellStyle cellStyle = wb.createCellStyle();
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			cellStyle.setFillForegroundColor(IndexedColors.RED.index);
			cell.setCellStyle(cellStyle);
			FileOutputStream out = null;
	        try {
	             out = new FileOutputStream(file);
	             wb.write(out);
	        } catch (IOException e) {
	             e.printStackTrace();
	        } finally {
	             try {
	                 out.close();
	             } catch (IOException e) {
	                 e.printStackTrace();
	             }
	         }
		}

5、用户下载有错误提示的Excel文件。

(1)前端HTML代码。

<div class='Tips1'>
			<div>
				<i class="layui-icon layui-icon-about" style="color:#FFB300;font-size:21px;"></i><span>资料格式有误</span>
			</div>
			<p>共需创建商品 <span class='commodityTotalToCreate'>0</span>个，商品格式错误为<span
					class='commodityWrongFormatNumber'>0</span>个。共需创建会员 <span class='vipTotalToCreate'>0</span>
				个，会员格式错误为<span class='vipWrongFormatNumber'>0</span>个。供应商总数为 <span
					class='providerTotalToCreate'>0</span> 个,
				供应商格式错误个数为<span class='providerWrongFormatNumber'></span>个。
				<br> 可以点击<a class="formatError" href="" style="color:#2196F3;cursor:pointer;"
					download="博销宝资料导入模板_错误批注.xlsm"><u>下载修改.</u></a></p>
		</div>

(2)前端JS代码。

else if (res.ERROR == "EC_WrongFormatForInputField") {
				$(".Tips1").show();
				$(".icon2").show();
				$('#progressCSS').attr('class', 'layui-progress-bar layui-bg-red')
				$.ajax({
					url: downloadEx_url,
					type: method_post,
					async: true,
					data: {

					},
					dataType: "json",
					success: function (data) {
						console.log(data);
						console.log(data.filePath)
						$('.formatError').attr('href', data.filePath);

(3)后端代码。

由于之前已经把错误格式的Excel保存在服务器上了，所以直接返回给前端就可以了：

@RequestMapping(value = "/downloadEx", produces = "plain/text; charset=UTF-8", method = RequestMethod.POST)
	@ResponseBody
	public String downloadEx(HttpSession session) {
		if (!canCallCurrentAction(session, BaseAction.EnumUserScope.STAFF.getIndex())) {
			logger.debug("无权访问本Action");
			return null;
		}
		Company company = getCompanyFromSession(session);
		Map<String, Object> param = getDefaultParamToReturn(true);
		Staff staff = getStaffFromSession(session);
		// 本公司的老板才能下载
		if(staff.getRoleID() != Role.EnumTypeRole.ETR_Boss.getIndex()) {
			param.put(BaseAction.JSON_ERROR_KEY, EnumErrorCode.EC_NoPermission);
			param.put(BaseAction.KEY_HTMLTable_Parameter_msg, "没有权限");
			return JSONObject.fromObject(param, JsonUtil.jsonConfig).toString();
		}
		String xlsxFilePath = String.format(PATH_SaveMultipartFileToDestination, company.getDbName());
		// 得到要下载的文件
		File file = new File(xlsxFilePath);
		if(!file.exists()) {
			param.put(BaseAction.JSON_ERROR_KEY, EnumErrorCode.EC_OtherError);
			param.put(BaseAction.KEY_HTMLTable_Parameter_msg, "你要下载的资源已经被删除");
		}
		//
		String filePath = xlsxFilePath.replace(ImportCommodityAndVipDir, TOMCAT_MAP_DIR_DownloadCommodityAndVip);
		logger.info("下载文件路径" + filePath);
		param.put("filePath", filePath);
		param.put(KEY_HTMLTable_Parameter_msg, "");
		param.put(BaseAction.JSON_ERROR_KEY, EnumErrorCode.EC_NoError.toString());
		logger.info(JSONObject.fromObject(param, JsonUtil.jsonConfig).toString());
		return JSONObject.fromObject(param, JsonUtil.jsonConfig).toString();
	}