2021-08-10

EXCEL报表导入

第一次博客生涯

前言

我们往往会碰到这样的需求，批量导入数据，我们可以通过导入EXCEL数据来实现

一、读取EXCEL文件，并且写入到临时文件中

private File getFilePath(UploadFile uploadFile) {
		File file = uploadFile.getFile();
		String path = System.getProperty("java.io.tmpdir");
		File newFile = new File(path, uploadFile.getSimpleName() + "." + uploadFile.getType());
		InputStream in = null;
		OutputStream out = null;
		try {
			in = new NetilerFileInputStream(file);
			out = new FileOutputStream(newFile);
			IOUtil.write(in, out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtil.close(in);
			IOUtil.close(out);
		}
		return newFile;
	}

二、读取临时文件，将数据写入到准备好的VIEW对象中

	/**
	 * 获取excal内容
	 * 
	 * @param filePath
	 * @param k
	 */
	private ExcelView getExcel(String filePath, int k) {
		List<HashMap<String, String>> resultList = new ArrayList<HashMap<String, String>>();
		Workbook wb = null;
		Sheet sheet = null;
		Row row = null;
		wb = readExcel(filePath);
		int colnum = 0;
		int rownum = 0;
		if (wb != null) {
			// 获取sheet
			sheet = wb.getSheetAt(k);
			// 获取最大行数
			rownum = sheet.getPhysicalNumberOfRows();
			// 获取第一行
			row = sheet.getRow(0);
			if (row == null) {
				return null;
			}
			if(rownum>1) {
				// 获取最大列数
				colnum = row.getPhysicalNumberOfCells();
				for (int i = 0; i < rownum; i++) {
					HashMap<String, String> map = new HashMap<String, String>();
					row = sheet.getRow(i);
					row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
					row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
					String ip = String.valueOf(row.getCell(0).getStringCellValue());
					String contact = String.valueOf(row.getCell(1).getStringCellValue());
					String seat = String.valueOf(row.getCell(2).getStringCellValue());
					if(!ip.toLowerCase().equals("ip")) {
						map.put("ip", ip);
						map.put("contact", contact);
						map.put("seat", seat);
						resultList.add(map);
					}
				}
			}
		}
		return new ExcelView(resultList, rownum, colnum);
	}

	// 读取excel
	private Workbook readExcel(String filePath) {

		Workbook wb = null;
		if (filePath == null) {
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = new FileInputStream(new File(filePath));
			if (".xls".equals(extString)) {
				return wb = new HSSFWorkbook(is);
			} else if (".xlsx".equals(extString)) {
				return wb = new XSSFWorkbook(is);
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

三、将VIEW对象中的数据写入到数据库中，然后删除临时文件

@Override
	public String uploadFile(UploadFile uploadFile) {
		if(!uploadFile.getType().equals("xls")&&!uploadFile.getType().equals("xlsx")) {
			return "只支持xls跟xlsx格式的文件";
		}
		// 读取xls和xlsx文件
		File newFile = getFilePath(uploadFile);
		// 循环读取sheet
		for (int k = 0; k < 1; k++) {
			// 读取excel
			ExcelView excel = null;
			try {
				excel = getExcel(newFile.getPath(), k);
			} catch (Exception e) {
				return "读取EXCEL失败,请检查格式是否正确";
			}
			if(excel==null) {
				return "读取EXCEL失败,请检查格式是否正确";
			}
			List<HashMap<String, String>> resultList = excel.getResultList();
			if(resultList!=null&&resultList.size()>0) {
				for (HashMap<String, String> hashMap : resultList) {
					IpContact ipContact = new IpContact();
					ipContact.setIp(hashMap.get("ip"));
					ipContact.setContact(hashMap.get("contact"));
					ipContact.setSeat(hashMap.get("seat"));
					save(ipContact);
				}
			}
		}
		newFile.delete();
		return "success";
	}

总结

end