普元nui：上传下载、读取Excel文件信息

最新推荐文章于 2022-11-03 16:39:10 发布

喜只狼果冻

最新推荐文章于 2022-11-03 16:39:10 发布

阅读量866

点赞数 1

分类专栏： Work

本文链接：https://blog.csdn.net/qq_36750352/article/details/101051233

版权

Work 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

Report.jsp


<style type="text/css">
div {
	text-align: center;
	margin-top: 20px;
	margin-bottom: 10px;
}

#sub {
	color: #3474D7;
	background: white;
}

#dow {
	width: 200px;
	margin-top: 10px;
	opacity: 0.5;
	border-radius: 25px;
	letter-spacing: 15px;
}
</style>

<body>
	<div class="nui-fit">
		<form id="form" action="<%=request.getContextPath() + "/syzcdDksqbbb/upload.do"%>" method="post" enctype="multipart/form-data">
			<input class="nui-htmlfile" name="upload" style="width: 250px" />
		</form>
		<a class="nui-button" id="dow" iconCls="icon-down" onclick="downClick"> 模板下载</a>
	</div>
	<div class="nui-toolbar">
		<a class="nui-button" id="sub" iconCls="icon-ok" onclick="upClick">
			<fmt:message key="确认" />
		</a>
		<span style="display: inline-block; width: 25px;"></span>
		<a class="nui-button" iconCls="icon-close" onclick="cloClick" style="width: 60px;">
			<fmt:message key="关闭" />
		</a>
	</div>

	<script type="text/javascript">
		$(function() {
			$J.get("sub").enable();
		})
		/* 上传 */
		function upClick() {
			if (check()) {
				$("#form").submit();
				$J.get("sub").disable();
			}
		}
		/* 关闭 */
		function cloClick() {
			$J.closedialog("ok");
		}
		/* 下载 */
		function downClick() {
			var reqUrl = appContext + "/syzcdDksqbbb/download.do";
			location.href = reqUrl;
		}
		/* 格式校验 */
		function check() {
			/* 文件路径不能为空 */
			var upload = $J.getbyName("upload");
			var pathLocal = upload.value;
			if (pathLocal == "") {
				$J.cap4jAlert(message.common.uploadPrompt);
				return;
			}
			/* 根据客户端的操作系统获取分隔符 */
			var separate = "\\";
			if (!isWindows) {
				separate = "/";
			}
			/* 文件名称不能超过128 */
			if (pathLocal.substring(pathLocal.lastIndexOf(separate) + 1,
					pathLocal.length).length > 128) {
				$J.cap4jAlert(message.common.maxLengthValidationMsg,
						message.common.prompt);
				return;
			}
			/* 	文件后缀名 */
			var postfix = pathLocal.substring(pathLocal.lastIndexOf(".") + 1);
			if (postfix != "xls") {
				$J.cap4jAlert(message.staff.excelUploadPrompt);
				upload.setValue("");
				upload.setText("");
				return;
			}
			return true;
		}
	</script>
</body>

ctrler

@FunctionDesc("下载")
	@ResponseBody
	@RequestMapping(value = "/download")
	public void download(HttpServletRequest request, HttpServletResponse response) {
		String fileDir = request.getSession().getServletContext().getRealPath("/");
		String tempName = "\\views\\syfh_zcd\\report\\uploadReport\\template\\ReporttablExcel.xls";
		String path = fileDir + tempName;
		File file = new File(path);
		String filename = file.getName();
		InputStream fis;
		try {
			fis = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);
			fis.close();
			response.reset();
			response.addHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode("reportDemo.xls", "UTF-8"));
			response.addHeader("Content-Length", "" + file.length());
			OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=gb2312");
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	@FunctionDesc("批量提交SyzcdDksqbbb")
	public String upload(HttpServletRequest req, @RequestParam("upload") MultipartFile uploadFile, Model model) throws IOException {
		log.info("批量提交SyzcdDksqbbb");
		String path = req.getSession().getServletContext().getRealPath("/") + "/views\\syfh_zcd\\report\\uploadReport\\";
		File filePath = new File(path);
		// 先判断地址是否存在
		if (!filePath.exists()) {
			// 目录不存在创建这个目录
			filePath.mkdirs();
		}
		// 文件相对路径，没有带目录
		String originalFilename = uploadFile.getOriginalFilename();
		// 查找“.”在文件名的下标，以便将文件名与文件后缀分开
		int indexOf = originalFilename.indexOf(".");
		// 文件名称
		String name = originalFilename.substring(0, indexOf);
		// 文件后缀.xml
		String tail = originalFilename.substring(indexOf);
		// 当前时间
		String dateNow = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		// 重新命名文件名称
		String nameNew = name + dateNow + tail;
		String pathName = path + nameNew;
		// 获取输入流
		InputStream in = uploadFile.getInputStream();
		OutputStream out = new FileOutputStream(new File(pathName));
		String str = uploadFile.getOriginalFilename();
		byte data[] = new byte[1024 * 1024];
		int len = 0;
		while ((len = in.read(data)) != -1) {
			out.write(data, 0, len);
		}
		in.close();
		out.close();
		// 解析Excel获取数据
		ReadExcel readExcel = new ReadExcel();
		int flag = readExcel.doExcel(pathName, nameNew);
		req.setAttribute("flag", flag);
		return "\\syfh_zcd\\report\\reportApter";
	}

ReadExcel.java

public class ReadExcel {
	private static final String XLS = "xls";
	private static final String XLSX = "xlsx";
	private static List<Map<Integer, String>> listData = null;
	private SyzcdDksqbbbServiceImpl syzcdDksqbbbServiceImpl = ContextUtil.getBean("iSyzcdDksqbbbService", SyzcdDksqbbbServiceImpl.class);
	/**
	 * 日志记录类.
	 */
	private Logger logger = LoggerFactory.getLogger(ReadExcel.class);
	public int doExcel(String... args) {
		String pathName = args[0];
		String nameNew = args[1];
		int flag = 0;
		logger.info("解析xml数据：ReadExcel doExcel：" + pathName);
		readExcel(pathName);
		List<SyzcdDksqbbbVO> listVO = new ArrayList<SyzcdDksqbbbVO>();
		for (int i = 0; i < listData.size(); i++) {
			SyzcdDksqbbbVO vo = new SyzcdDksqbbbVO();
			vo.setCnname(listData.get(i).get(0).trim());
			vo.setUnitcode(listData.get(i).get(1).trim());
			listVO.add(vo);
		}
		// "文件上传失败，请确认文件重新上传！"
		if (listVO == null || listVO.size() < 1) {
			flag = 0;
			return flag;
		}
		// 批量入库
		syzcdDksqbbbServiceImpl.deleteList(listVO);
		syzcdDksqbbbServiceImpl.saveList(listVO);
		flag=1;
		return flag;
	}
	/**
	 * 将Excel文件内容存储到list
	 * 
	 * @param filePath
	 *            文件路径
	 * @return
	 * @return list
	 */
	public List<Map<Integer, String>> readExcel(String pathName) {
		listData = new LinkedList<Map<Integer, String>>();
		Workbook workbook = null;
		// 格式效验
		this.preReadCheck(pathName);
		workbook = this.getWorkbook(pathName);
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			Sheet sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				logger.info("Excel文件无sheet页");
			}
			int firstRowIndex = sheet.getFirstRowNum();
			int lastRowIndex = sheet.getLastRowNum();
			// 读取首行 即,表头
			Row firstRow = sheet.getRow(firstRowIndex);
			for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
				Cell cell = firstRow.getCell(i);
				String cellValue = this.getCellValue(cell);// 将表头格式装换后返回
				logger.info(" " + cellValue + "\t");
			}
			// 读取数据行
			for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
				Map<Integer, String> mapData = new LinkedHashMap<Integer, String>();
				Row currentRow = sheet.getRow(rowIndex);// 当前行
				if (currentRow == null) {
					continue;
				}
				// 读取每一行的单元格数据
				int key = 0;
				int getFirstCellNum = currentRow.getFirstCellNum(); // 首列
				int getLastCellNum = currentRow.getLastCellNum();// 最后一列
				for (int columnIndex = getFirstCellNum; columnIndex < getLastCellNum; columnIndex++) {
					Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
					String currentCellValue = this.getCellValue(currentCell);// 当前单元格的值
					mapData.put(key, currentCellValue);
					key++;
				}
				logger.info(" mapData" + mapData + "\t");
				if (mapData != null && mapData.size() > 0) {
					listData.add(mapData);
					logger.info("addlist" + listData + "\t");
				}
				try {
					if (workbook != null) {
						workbook.close();
					}
				} catch (IOException e) {
					logger.error("解析Excel文件错误..." + e.getMessage());
					e.printStackTrace();
				}
			}
		}
		return listData;
	}
	// Excel文件名格式效验
	private void preReadCheck(String pathName) {
		File file = new File(pathName);
		try {
			if (!file.exists()) {
				throw new FileNotFoundException("传入的文件不存在：" + pathName);
			}
			if (!(pathName.endsWith(XLS) || pathName.endsWith(XLSX))) {
				throw new BusinessException("传入的文件不是excel");
			}
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}
	// 获得workbook对象
	private Workbook getWorkbook(String pathName) {
		Workbook workbook = null;
		try {
			InputStream is = new FileInputStream(pathName);
			if (pathName.endsWith(XLS)) {
				workbook = new HSSFWorkbook(is);
			} else if (pathName.endsWith(XLSX)) {
				workbook = new XSSFWorkbook(is);
			}
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		logger.info("获得workbook");
		return workbook;
	}
	// 将表头格式装换后返回
	private String getCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
		// 加上下面这句，临时把它当做文本来读取
		cell.setCellType(Cell.CELL_TYPE_STRING);
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
}

ReportAfter.jsp

		$(function() {
			var flag = "<c:out value='${flag}'/>"; 
			switch (flag) {
			case "0":
				msg = "文件上传失败，请检测文件重新上传！";
				break;
			case "1":
				msg = "文件上传成功！";
				break;
			default:
				msg = "文件上传失败，请联系管理员！";
			}
			$J.cap4jConfirm(msg + "是否继续上传？", "提示", function(action) {
				if (action == 'ok') {
					window.location.href = appContext
							+ "/syzcdDksqbbb/report.do";
				} else {
					$J.closedialog("ok")
				}
			});

		})

喜只狼果冻

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
普元nui：上传下载、读取Excel文件信息

Report.jsp<style type="text/css">div { text-align: center; margin-top: 20px; margin-bottom: 10px;}#sub { color: #3474D7; background: white;}#dow { width: 200px; margin-top: 10p...
复制链接

扫一扫