学习用Sts做一个SpringBoot的WEB项目（6)实战：Excel导入以及功能比对_学习用sts做一个springboot的web项目(6)-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42250593/article/details/121201921

写这篇文档期间，到甲方那里谈一个项目。项目没有谈成，但是是长期合作的且关系不错的客户，正好用这个练练手，顺便让自己做的这个小破项目有点用武之地，也能在客户那拉一波好感。

客户需求是网络管理，不同的云区网络访问需求提到客户这里来，他们整了一个EXCEL来记录这些需求，如果需求通过了，就在防火墙上根据请求的源IP和目的IP以及端口来配置策略。

由于积累了很长一段时间的需求，加上期间也有被删除掉的策略，现在需要定期比对申请方自己记录的网络策略和客户实际收集到的需求是否一致。

用户自己记录的网络工单部分如下

工单编号

申请厂家

涉及云区

源服务器

源IP/安全组

目的服务器

目的IP

开放目的端口

20211104-1

内网区访问

数据采集服务器

192.168.1.162内网区

数据前置库

192.168.2.6内网区

9001

20211104-2

内网

统一门户

10.169.130.7

资源填报

192.168.138.181

8080；38889

20211105-1

行业云

私有云

专网

172.16.22.4

分析系统应用

10.3.121.13

8080

行业云-外网

分析系统应用

80.3.131.194

分析系统前置机

10.0.1.84

9090-9099

互联网

专网

分析系统前置机

10.133.13.84

地图底图服务

http://cache1.online.cn

互联网

网维

分析系统前置机

10.0.1.84

地名搜索服务

130.62.21.21

590

厂家自己记录的网络策略部分如下

源服务器名称	源IP	目的服务器	目的IP	开放端口	备注
应用服务器	10.0.1.4 10.0.1.5 10.0.1.10 10.0.1.11 10.0.1.12 10.0.1.13 10.0.1.14 10.0.1.15 10.0.1.26 10.0.1.27	数据服务器	10.0.0.2 10.0.0.3 10.0.0.4 10.0.0.5 10.0.0.6	6330

一、需求分析

现有的需求是比较简单的，根据客户自己记录的网络需求申请单，和客户厂家提供的访问策略进行比对，判断厂家的策略是否与当前策略一致。由于厂家有多个，网络管理只有一个，只能拿厂家的策略来比对客户的策略，来判断这个厂家有多少策略是多出来的，至于客户自己的策略是否有多配，需要把所有厂家的策略都比对过后才能得出结论。

二、思路

网络策略比对，不能直接拿字符串进行比较，两边做记录的都是不同的人，有不同的记录习惯，所以只能根据两边的记录情况，来生成明细策略，然后对两边的明细策略进行比对。这样会产生较多的明细策略，比如上面厂家仅是一行EXCEL，但是由于源IP有10个，目的IP有5个，端口1个，因此会产生10*5*1=50条明细策略。这里其实还有一个端口协议的问题，客户和厂家记录的端口协议如果不一样，也不能认为他们是一致的。

由于会产生大量的明细策略，人工比对显然是费力不讨好的，通过程序来生成明细策略，用SQL语句直接匹配比对结果，是比较理想的做法。我们要设计两个excel导入功能和一个excel导出功能，分别导入用户和厂家的excel，然后导出一个excel存放比对结果。

三、问题

导入功能的最大问题，就是excel记录规范的问题，这里存在一些可以预见的问题

1.源IP、目的IP、端口包含多个，以什么作为区分单独IP的功能

2.IP子网掩码问题（ABCD类网络地址问题）

3.IP段、端口段问题（连续IP或者连续端口，不可能让人一个个写出来）

4.互联网访问（网址或者中文处理问题）

5.问题数据（如有源无目标，或者无端口等）

6.废弃工单问题（即网络工单做了记录，但是实际因各种原因被废弃未开通的）

这些都是原始文件上的问题，在技术和业务上也存在一些问题：

1.用户为了表格美观，大量使用了合并单元格，换行符，空格，程序处理上合并单元格要拆分，空格要去掉，换行要替换成分隔符

2.用户一次导入比对后，后续数据如何处理，是全部清除后重新导入，还是增量导入

3.用户如果要对历史数据做比对，如何处理

四、协商

excel中原始数据的问题，说白了就是一个：规范化。即原始数据的IP地址、端口应该要怎么填。如果是一个web应用，用户从web录入数据，我们自然可以限制用户怎么输入，但是现在用户是从excel中导入，那就没办法约束用户了，只能协商说明。经过沟通，明确IP和端口的规范

1.IP地址必须写全，如192.168.0.1

2.连续IP使用短横杠分隔，分隔符后为IP的末尾地址，如192.16.0.1-11表示从192.168.0.1到192.168.0.11的11个IP

3.IP段必须标注掩码，如192.168.0.0/24，不能使用192.168.0.x表示

4.多个IP使用换行符或者顿号分隔，如192.168.0.1-11、192.168.13-20、192.168.1.0/24

5..互联网访问需求，如果有具体的源或者目标，填写域名或者IP，如果是开放所有，填写汉字“互联网”，如 https://test.cloud.com

6.不要填写IP的时候出现汉字备注如：10.168.233.* docker地址段

7.填写端口号后用括号标识端口的协议(可以是中文括号也可以是英文括号)，不加括号标识的一律认为是TCP协议如 80(TCP)

8.连续多个端口使用短横杠 - 连接,连接的起始端口需要协议一致

如 8080-8090 表示8080、8081、8082一直到8090连续11个端口的TCP协议
如 53000(UDP)-53010(UDP) 表示从53000到53010连续11个端口的UDP协议

9.多个不同端口，使用顿号、分隔如 20、22、1521、3306、9001（UDP）

10.全部端口使用英文“any”表示

11.端口不要有汉字描述

对于增量数据处理用户没有什么思路，我考虑的做法是，增加一个导入批次表，每一次导入的记录都标示这条导入是导入了什么，属于哪一次导入，这样就算多次导入重复数据，也能根据导入批次号来区分。用户自己根据导入批次来选择哪两个做数据对比。

五、数据表设计

根据设计思路，来设计数据表

1.网络工单表

CREATE TABLE `t_net_order` (
  `id` varchar(36) NOT NULL,
  `sn` varchar(255) comment '工单号，客户自己生成的日期有关的工单序号',
  `proposer` varchar(255) comment '申请方',
  `seq` varchar(4) DEFAULT NULL COMMENT '工单序号',
  `source_zone` varchar(600) DEFAULT NULL COMMENT '源云区',
  `des_zone` varchar(600) DEFAULT NULL COMMENT '目标云区',
  `source_server` varchar(600) DEFAULT NULL COMMENT '源服务器',
  `des_server` varchar(600) DEFAULT NULL COMMENT '目标服务器',
  `source_ip` varchar(1000) DEFAULT NULL COMMENT '源IP,取值具体为源明细表',
  `des_ip` varchar(1000) DEFAULT NULL COMMENT '目标IP,取值具体为目标明细表',
  `allow_port` varchar(600) DEFAULT NULL COMMENT '开放端口',
  `memo` varchar(200) DEFAULT NULL COMMENT '备注',
  `operator` varchar(30) DEFAULT NULL COMMENT '操作人',
  `order_type` varchar(30) DEFAULT '1' COMMENT '工单类型，1普通，2临时，3加急',
  `batch_num` varchar(50) DEFAULT NULL COMMENT '导入批次号',
  `line` varchar(30) DEFAULT NULL COMMENT '行号，excel行',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='网络工单表'

这里根据实际情况增加了几个excel中没有的字段。

2.厂家策略表

CREATE TABLE `t_strategy_support` (
  `id` varchar(36) DEFAULT NULL,
  `source_server` varchar(600) DEFAULT NULL COMMENT '源服务器',
  `source_ip` varchar(1000) DEFAULT NULL COMMENT '源IP',
  `des_server` varchar(600) DEFAULT NULL COMMENT '目的服务器',
  `des_ip` varchar(1000) DEFAULT NULL COMMENT '目的IP',
  `open_port` varchar(1000) DEFAULT NULL COMMENT '开放端口',
  `memo` varchar(1000) DEFAULT NULL COMMENT '备注',
  `support` varchar(100) DEFAULT NULL COMMENT '厂家',
  `batch_num` varchar(60) DEFAULT NULL COMMENT '导入批次号',
  `line` varchar(60) DEFAULT NULL COMMENT 'excel中的行号',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='厂家网络策略表'

3.网络策略表，这是根据客户的网络工单生成的网络策略

CREATE TABLE `t_net_strategy` (
  `id` varchar(36) DEFAULT NULL,
  `sn` varchar(36) DEFAULT NULL,
  `source_ip` varchar(768) DEFAULT NULL,
  `des_ip` varchar(768) DEFAULT NULL,
  `port` varchar(255) DEFAULT NULL,
  `protocol` varchar(255) DEFAULT NULL,
  `batch_num` varchar(255) DEFAULT NULL,
  `line` varchar(255) DEFAULT NULL,
  KEY `idx_net_strategy_sip` (`source_ip`) USING BTREE,
  KEY `idx_net_strategy_dip` (`des_ip`) USING BTREE,
  KEY `idx_net_strategy_port` (`port`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

没什么好说的，源IP目的IP开放端口端口协议，导入批次和对应的行号，主要是方便客户反查源excel文件，来验证是不是应该生成这条策略

4. 厂家网络策略表

CREATE TABLE `t_net_strategy_compare` (
  `id` varchar(36) DEFAULT NULL,
  `sn` varchar(36) DEFAULT NULL,
  `source_ip` varchar(1000) DEFAULT NULL,
  `des_ip` varchar(1000) DEFAULT NULL,
  `port` varchar(255) DEFAULT NULL,
  `protocol` varchar(255) DEFAULT NULL,
  `batch_num` varchar(255) DEFAULT NULL,
  `line` varchar(255) DEFAULT NULL,
  `support` varchar(255) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

厂家的网络策略表和用户的网络策略表一样，可以在同一个表上新建一个字段来做区分，但是考虑到实际会产生大量的策略数据，因此还是分开处理了，（其实就是偷懒，厂家的网络策略不是由工单产生，根本没有工单号字段sn）如果后面数据量大，作为主要比对字段的源IP，目标IP，端口要添加索引。

5.导入批次表

CREATE TABLE `t_import_batch` (
  `id` varchar(36) DEFAULT NULL,
  `batch_num` varchar(60) DEFAULT NULL COMMENT '导入批次号',
  `file_name` varchar(200) DEFAULT NULL COMMENT '导入文件名',
  `type` varchar(20) DEFAULT NULL COMMENT '导入类型，1.主机台账，2.纸质网络工单，3.电子网络工单，4.总集网络策略',
  `result` varchar(20) DEFAULT NULL COMMENT '导入结果,成功或者失败',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='文件导入批次表'

六、页面

页面功能比较简单，就是两个文件上传的按钮，下面的页面另外两个按钮是帮用户做的另外两个导入功能。

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
	<script>
	</script>
</head>
<meta charset="UTF-8">
<link rel="stylesheet" th:href="@{/common/css/myInfo.css}"/>
<body>
	功能列表
    <table class="list">
    <tr>
    	<td>
    		<h5>导入功能</h5>
    	</td>
    </tr>
		<tr>
		<td>
		<form  id="form_table" action="uploadHost"  enctype="multipart/form-data" method="post" target="_self">
        <button type="submit" >导入主机台账</button>
        <input  type="file" name="file"/>
		</form>
		</td>
	</tr>
	<tr>
		<td>
		<form  id="form_table1" action="uploadNetOrderPaper" enctype="multipart/form-data" method="post" target="_self">
        <button type="submit"   >导入原始网络工单</button>
        <input  type="file" name="netOrderPaperFile"/>
		</form>
		</td>
	</tr>
	<tr>
	<td>
		<form  id="form_table2" action="uploadNetOrder" enctype="multipart/form-data" method="post" target="_self">
        <button type="submit" >导入网络工单</button>
        <input  type="file" name="netOrderFile"/>
		<font size=1px>导入网络工单会生成网络访问策略，可以通过在备注字段填写&nbsp;<a class="link" th:href="@{/main/memoAvoid}" target="_self">免策略关键字</a>&nbsp;来避免生成策略</font>
		</form>
	</td>
	</tr>
	<tr>
		<td>
			<form id="form_table3" action="uploadCompareStrategy" enctype="multipart/form-data" method="post" target="_self">
        <button type="submit" >导入总集策略</button>
        <input  type="file" name="compareStrategyFile"/>
		</form>
		</td>
	</tr>
	<tr>
		<td>
			<h5>导出功能</h5>
		</td>
	</tr>
		<tr>
	<td>
		<form  id="form_table2" action="download" enctype="multipart/form-data" method="post" target="_self">
        <button type="submit" >总集网络策略比对情况</button>
		</form>
	</td>
	</tr>
	</table>
</body>
</html>

七、Controller

考虑到用户可能会导入2003版的excel和2007版的excel，程序添加了一个处理，但是实际来看好像用不上

import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.dyz.base.BaseAction;
import com.dyz.service.MyInfoService;

@Controller
@RequestMapping(value="main/myInfo")
public class MyInfoAction extends BaseAction{
	
	@Autowired
	private MyInfoService myInfoService;
	
	@RequestMapping(value="base")
	public String getBase(){
		return "myInfo/base";
	}
	
	@RequestMapping(value="uploadHost")
	public String upload(@RequestParam("file") MultipartFile file, HttpServletRequest request,Model model){
		if (file.isEmpty()) {
			model.addAttribute("status", "false");
			model.addAttribute("msg", "主机台账文件为空");
			return "myInfo/base";
		}
		
		String filename = file.getOriginalFilename();
		System.out.println(filename);
		Workbook wb = null;
		try{
		if(filename.endsWith("xls")){
			 wb = new HSSFWorkbook(file.getInputStream()); 
			 myInfoService.uploadHost(wb,1,filename);
		}else if(filename.endsWith("xlsx")){
			 wb = new XSSFWorkbook(file.getInputStream());
			 myInfoService.uploadHost(wb,2,filename);
		}else{
			model.addAttribute("status", "false");
			model.addAttribute("msg", "文件格式错误");
			return "myInfo/base";
		}
		}catch(IOException ex){
			ex.printStackTrace();
			model.addAttribute("status", "false");
			model.addAttribute("msg", "导入异常");
			return "myInfo/base";
		}
		return "myInfo/base";
	}
	
	
	@RequestMapping(value="uploadNetOrderPaper")
	public String uploadNetOrderPaper(@RequestParam("netOrderPaperFile") MultipartFile file, HttpServletRequest request,Model model){
			if (file.isEmpty()) {
				model.addAttribute("status", "false");
				model.addAttribute("msg", "网络工单文件为空");
				return "myInfo/base";
			}
			String filename = file.getOriginalFilename();
			Workbook wb = null;
			try{
			if(filename.endsWith("xls")){
				 wb = new HSSFWorkbook(file.getInputStream()); 
				 myInfoService.uploadNetOrderPaper(wb,1,filename);
			}else if(filename.endsWith("xlsx")){
				 wb = new XSSFWorkbook(file.getInputStream());
				 myInfoService.uploadNetOrderPaper(wb,2,filename);
			}else{
				model.addAttribute("status", "false");
				model.addAttribute("msg", "文件格式错误");
				return "myInfo/base";
			}
			}catch(IOException ex){
				ex.printStackTrace();
				model.addAttribute("status", "false");
				model.addAttribute("msg", "导入异常");
				return "myInfo/base";
			}
		
		return "myInfo/base";
	}
	
	@RequestMapping(value="uploadNetOrder")
	public String uploadNetOrder(@RequestParam("netOrderFile") MultipartFile file, HttpServletRequest request,Model model){
			if (file.isEmpty()) {
				model.addAttribute("status", "false");
				model.addAttribute("msg", "网络工单文件为空");
				return "myInfo/base";
			}
			String filename = file.getOriginalFilename();
			Workbook wb = null;
			try{
			if(filename.endsWith("xls")){
				 wb = new HSSFWorkbook(file.getInputStream()); 
				 myInfoService.uploadNetOrder(wb,1,filename);
			}else if(filename.endsWith("xlsx")){
				 wb = new XSSFWorkbook(file.getInputStream());
				 myInfoService.uploadNetOrder(wb,2,filename);
			}else{
				model.addAttribute("status", "false");
				model.addAttribute("msg", "文件格式错误");
				return "myInfo/base";
			}
			}catch(IOException ex){
				ex.printStackTrace();
				model.addAttribute("status", "false");
				model.addAttribute("msg", "导入异常");
				return "myInfo/base";
			}
		
		return "myInfo/base";
	}
	
	
	@RequestMapping(value="uploadCompareStrategy")
	public String uploadCompareStrategy(@RequestParam("compareStrategyFile") MultipartFile file, HttpServletRequest request,Model model){
			if (file.isEmpty()) {
				model.addAttribute("status", "false");
				model.addAttribute("msg", "网络工单文件为空");
				return "myInfo/base";
			}
			String filename = file.getOriginalFilename();
			Workbook wb = null;
			try{
			if(filename.endsWith("xls")){
				 wb = new HSSFWorkbook(file.getInputStream()); 
				 myInfoService.uploadCompareStrategy(wb,1,filename);
			}else if(filename.endsWith("xlsx")){
				 wb = new XSSFWorkbook(file.getInputStream());
				 myInfoService.uploadCompareStrategy(wb,2,filename);
			}else{
				model.addAttribute("status", "false");
				model.addAttribute("msg", "文件格式错误");
				return "myInfo/base";
			}
			}catch(IOException ex){
				ex.printStackTrace();
				model.addAttribute("status", "false");
				model.addAttribute("msg", "导入异常");
				return "myInfo/base";
			}
		
		return "myInfo/base";
	}
	
	
	
	@RequestMapping(value = "download", method = RequestMethod.POST)
	public String download(HttpServletResponse reponse){
		Workbook workbook = myInfoService.download();
		OutputStream os = null;
        try {
            // 获取输出流
            os = response.getOutputStream();
            // 重置输出流
            response.reset();
            // 设定输出文件头
            response.setHeader("Content-disposition",
                    "attachment; filename=网络策略比对结果.xlsx");
            // 定义输出类型
            response.setContentType("application/msexcel");
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                assert os != null;
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
		return "myInfo/base";
	}
}

八、Service

主要逻辑是处理合并单元格、拆解源IP、目的IP、端口，生成网络策略。

8.1合并单元格的处理

在处理单元格合并单元格时，POI查询到只收单元格的首行首列字段有值，其他格子里都没有内容，因此考虑的处理方式是：获取单元格的起始行和起始列，创建一个与excel表完全对应的二维数组，根据单元格的情况，把所有合并的单元格都设置成该合并单元格的首行首列的值，获取的时候从对应的二维数组中取值。

首先在类里面定义一个二维数组

private String[][] megrenValues;

初始化这个二维数组，获取每一个合并单元格的起止行列数，并设置成对应的单元格的值。获取excel单元格实际值的时候先从这个二维数组取值，如果取到没有值，则表示这一格不是合并单元格，直接取原表的值。如果有值，则表示是合并单元格，返回该值。

        Sheet sheet = wb.getSheetAt(0);
	    int length = sheet.getLastRowNum();
	    megrenValues = new String[length+1][10];
		
	    String batch_num = this.getImportBatch(fileName, "2");
		List<CellRangeAddress> range = sheet.getMergedRegions();
		int firstColumn = 0;
		int lastColumn = 0;
		int firstRow = 0;
		int lastRow = 0;
		int i = 0;
		for(CellRangeAddress cra : range){
			System.out.println("第"+i+"个合并单元格");
			i++;
			firstColumn = cra.getFirstColumn();
			lastColumn = cra.getLastColumn();
			firstRow = cra.getFirstRow();
			lastRow = cra.getLastRow();
			Row tmp = sheet.getRow(firstRow);
			Cell tmpcell = tmp.getCell(firstColumn);
			String value = ExcelUtil.getCellValue(tmpcell);
			System.out.println(value);
			System.out.println("单元格起始行"+firstRow+"单元格终止行"+lastRow+"单元格起始列"+firstColumn+"单元格终止列"+lastColumn);
			
			for(int rowNum = firstRow;rowNum<=lastRow;rowNum++){
				for(int colNum = firstColumn;colNum<=lastColumn;colNum++){
					megrenValues[rowNum][colNum] = value;
					System.out.println("设置单元格值:"+megrenValues[rowNum][colNum]);
				}
			}
		}

Excel单元格取值

private  String getCellValue(Row row,int rowNum,int colNum){
		String value = megrenValues[rowNum][colNum];
		if(value!=null&&(!"".equals(value)))
			return value;
		Cell cell = row.getCell(colNum);
		return ExcelUtil.getCellValue(cell).trim();
	}

8.2 IP地址处理

由于规范了IP地址填写，所有按照我们预设的IP地址来做出来，首先是预处理，IP和端口可以通用

private String handleIPorPortFirst(String ip){
		if(ip==null)
			return "";
		ip = ip.trim().
				replaceAll("\n", "、").
				replaceAll(" ", "").
				replaceAll("~", "-");
		if(ip.endsWith("、"))
			ip = ip.substring(0,ip.length()-1);
		return ip;
	}

主要是把换行统一替换成顿号，去空格（理论上字段中所有空格都不应该存在）和替换IP段分隔符，处理完成后进行split分隔，获取一个数组

private String[] handleIPorPortSecond(String ip){
		if(ip.contains("、")){
			return ip.split("、");
		}else{
		  String[] result =	new String[1];
		  result[0] = ip;
		  return result;
		}
	}

即使是单一IP或者单一端口，也组成一个数组，方便统一处理，然后IP和端口要分开处理了

private List<String> getIPLast(String[] ips){
		List<String> list = new ArrayList<String>();
		for(String ip:ips){
			if(isDomain(ip)){
				list.add(ip);
			}else if(ip.contains("-")){
				String[] sips = getIPSegement(ip);
				for(String s:sips){
					list.add(s);
				}
			}else{
				if(!ip.contains("/"))
					ip=ip+"/32";
				list.add(ip);
			}
		}
		return list;
	}

这里还有两个工具方法：判断是否互联网地址或者是否连续IP段

private boolean isDomain(String ip){
		if(ip==null) return false;
		boolean flag = ip.contains("互联网");
		if(flag)
			return flag;
		String regex=".*[a-zA-Z]+.*";  
	    Matcher m=Pattern.compile(regex).matcher(ip);  
	    return m.matches();
	}

private  String[] getIPSegement(String ip){
		String[] tmp = ip.split("-");
		int index = tmp[0].lastIndexOf(".");
		String prefix = tmp[0].substring(0,index);
		String startStr = tmp[0].substring(index+1).trim();
		if(tmp[1].contains("."))
			tmp[1]=tmp[1].replace(prefix, "").replace(".", "").trim();
		int start = Integer.parseInt(startStr);
		int end = Integer.parseInt(tmp[1]);
		String[] result = new String[end-start+1];
		for(int i=0;i<result.length;i++){
			result[i]=prefix+"."+start+"/32";
			start++;
		}
		return result;
	}

端口处理

private Map<String, List<String>> getPortLast(String[] ports) {
		Map<String,List<String>> map = new HashMap<String,List<String>>();
		for(String port:ports){
			if(port.contains("-")){
			  Map<String,List<String>> m = getPortSegement(port);
			  for(String key:m.keySet()){
				  if(map.keySet().contains(key)){
					  List<String> tmpList = map.get(key);
					  List<String> tmp1List = m.get(key);
					  for(String tmp:tmp1List){
						  tmpList.add(tmp);
					  }
				  }else{
					  map.put(key, m.get(key));
				  }
			  }
			}else{
				String protocol = "TCP";
				int index = port.lastIndexOf("(");
				if(index<0)
					index = port.lastIndexOf("（");
				if(index>0){
					protocol = port.substring(index+1).
					replace("(", "").
					replace(")", "").
					replace("（", "").
					replace("）", "");
					port = port.substring(0,index).trim();
				}	
				if(map.keySet().contains(protocol)){
					List<String> s = map.get(protocol);
					if(port.contains(".0")){
						port = port.replace(".0", "");
					}
					s.add(port);
				}else{
					List<String> list = new ArrayList<String>();
					list.add(port);
					map.put(protocol, list);
				}
				
			}
		}
		return map;
	}

用一个map来处理返回的端口，map的key是四层协议，tcp或者udp，当然有七层协议也是可以的，工具方法

/**
	 * 端口段解析，返回map，其中协议为map的key，默认是TCP协议
	 * @param port
	 * @return
	 */
	private Map<String,List<String>> getPortSegement(String port){
		String[] tmp = port.split("-");
		int index = tmp[0].lastIndexOf("(");
		String protocol = "TCP";
		if(index<0)
			index = tmp[0].lastIndexOf("（");
		if(index>0){
			protocol = tmp[0].substring(index+1).
			replace("(", "").
			replace(")", "").
			replace("（", "").
			replace("）", "");
			tmp[0] = tmp[0].substring(0,index).trim();
			tmp[1] = tmp[1].substring(0,index).trim();
		}
		int begin = Integer.parseInt(tmp[0]);
		int end = Integer.parseInt(tmp[1]);
		int size = end-begin+1;
		List<String> result = new ArrayList<String>();
		for(int i=0;i<size;i++){
			result.add(""+begin);
			begin ++;
		}
		
		Map<String,List<String>> resultMap = new HashMap<String,List<String>>();
		resultMap.put(protocol, result);
		return resultMap;
	}

8.3 Service完整代码

package com.dyz.service.impl;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.dyz.mapper.ImportBatchMapper;
import com.dyz.mapper.MyInfoMapper;
import com.dyz.mapper.NetOrderPaperMapper;
import com.dyz.model.Host;
import com.dyz.model.ImportBatch;
import com.dyz.model.NetOrder;
import com.dyz.model.NetOrderDes;
import com.dyz.model.NetOrderModel;
import com.dyz.model.NetOrderPort;
import com.dyz.model.NetOrderSource;
import com.dyz.model.NetStrategy;
import com.dyz.model.NetStrategyCompare;
import com.dyz.model.Order;
import com.dyz.model.Strategy;
import com.dyz.model.StrategyCompareResult;
import com.dyz.service.MyInfoService;
import com.dyz.util.CommonUtil;
import com.dyz.util.ExcelUtil;

@Service
public class MyInfoServiceImpl implements MyInfoService{

	
	@Autowired
	private MyInfoMapper myInfoMapper;
	
	@Autowired
	private ImportBatchMapper importBatchMapper;
	
	@Autowired
	private NetOrderPaperMapper netOrderPaperMapper;
	
	private String[][] megrenValues;
	

	@Override
	public Workbook download() {
		List<StrategyCompareResult> list = myInfoMapper.getStrategyCompareList();
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("比对结果");
		Row row = sheet.createRow(0);
		row.createCell(0).setCellValue(new XSSFRichTextString("源IP"));
		row.createCell(1).setCellValue(new XSSFRichTextString("目的IP"));
		row.createCell(2).setCellValue(new XSSFRichTextString("开放端口"));
		row.createCell(3).setCellValue(new XSSFRichTextString("端口协议"));
		row.createCell(4).setCellValue(new XSSFRichTextString("策略来源行"));
		row.createCell(5).setCellValue(new XSSFRichTextString("厂家"));
		row.createCell(6).setCellValue(new XSSFRichTextString("网络工单号"));
		row.createCell(7).setCellValue(new XSSFRichTextString("工单表匹配行"));
		int point = 1;
		for(StrategyCompareResult bean:list){
			Row content = sheet.createRow(point);
			content.createCell(0).setCellValue(new XSSFRichTextString(bean.getSourceIp()));
			content.createCell(1).setCellValue(new XSSFRichTextString(bean.getDesIp()));
			content.createCell(2).setCellValue(new XSSFRichTextString(bean.getPort()));
			content.createCell(3).setCellValue(new XSSFRichTextString(bean.getProtocol()));
			content.createCell(4).setCellValue(new XSSFRichTextString(bean.getLine()));
			content.createCell(5).setCellValue(new XSSFRichTextString(bean.getSupport()));
			content.createCell(6).setCellValue(new XSSFRichTextString(bean.getSn()));
			content.createCell(7).setCellValue(new XSSFRichTextString(bean.getOrderLine()));
			point++;
		}
		
		return workbook;
		
	}
	/**
	 * 导入网络工单，并生成对应的网络策略，用于后期比对
	 * 策略表
	 * t_net_strategy
	 */
	@Override
	public int uploadNetOrder(Workbook wb, int type,String fileName){
		Sheet sheet = wb.getSheetAt(0);
		int length = sheet.getLastRowNum();
	    megrenValues = new String[length+1][13];
	    String batch_num = this.getImportBatch(fileName, "3");
		List<CellRangeAddress> range = sheet.getMergedRegions();
		int firstColumn = 0;
		int lastColumn = 0;
		int firstRow = 0;
		int lastRow = 0;
		int i = 0;
		for(CellRangeAddress cra : range){
			System.out.println("第"+i+"个合并单元格");
			i++;
			firstColumn = cra.getFirstColumn();
			lastColumn = cra.getLastColumn();
			firstRow = cra.getFirstRow();
			lastRow = cra.getLastRow();
			Row tmp = sheet.getRow(firstRow);
			Cell tmpcell = tmp.getCell(firstColumn);
			String value = ExcelUtil.getCellValue(tmpcell);
			System.out.println(value);
			System.out.println("单元格起始行"+firstRow+"单元格终止行"+lastRow+"单元格起始列"+firstColumn+"单元格终止列"+lastColumn);
			
			for(int rowNum = firstRow;rowNum<=lastRow;rowNum++){
				for(int colNum = firstColumn;colNum<=lastColumn;colNum++){
					megrenValues[rowNum][colNum] = value;
					System.out.println("设置单元格值:"+megrenValues[rowNum][colNum]);
				}
			}
		}

		//获取备注免出表数据
		List<String> avoidMemo = myInfoMapper.getAvoidMemo();
		for(i=1;i<length+1;i++){
			Row tmp = sheet.getRow(i);
			String sn = getCellValue(tmp,i,0);
			Order order = myInfoMapper.getOrderBySn(sn);
			String id = "";
			if(order==null){
				id = myInfoMapper.getUUID();
				order = new Order();
				order.setId(id);
				order.setSn(sn);
				order.setProposer(getCellValue(tmp,i,1));
				order.setType("1");
				myInfoMapper.insertOrder(order);
			}else{
				id = order.getId();
			}
			
			NetOrderModel model = new NetOrderModel();
			String source_ip = getCellValue(tmp,i,4);
			String des_ip = getCellValue(tmp,i,9);
			String dnat_port = getCellValue(tmp,i,7);
			String allow_port = getCellValue(tmp,i,10);
			String modelId = myInfoMapper.getUUID();
			model.setId(modelId);
			model.setOrder_id(id);
			model.setSn(sn);
			model.setProposer(getCellValue(tmp,i,1));
			model.setSource_zone(getCellValue(tmp,i,2));
			model.setSource_server(getCellValue(tmp,i,3));
			model.setSource_ip(source_ip);
			model.setDes_server(getCellValue(tmp,i,7));
			model.setDes_ip(des_ip);
			model.setAllow_port(allow_port);
			model.setMemo(getCellValue(tmp,i,8));
			model.setOperator(getCellValue(tmp,i,9));
			model.setOrder_type("2");//2是网络工单
			model.setBatch_num(batch_num);
			model.setLine(i+1+"");
			myInfoMapper.insertNetOrder(model);
			
			String memo = getCellValue(tmp,i,8);
			//申请虚拟IP的和申请负载均衡的单子没有网络策略
			boolean flag = false;
			for(String keyword:avoidMemo){
				if(memo==null){
					flag = false;
					break;
				}else{
					if(memo.contains(keyword)){
						flag = true;
						break;
					}
				}
					
			}
			if(!flag){
				handleNetStrategy(source_ip,des_ip,allow_port,sn,i);
			}
		}
		successImportBatch(batch_num);
		return 1;
	}
	/**
	 * 上传厂家的比对文件，进行文件比对
	 */
	@Override
	public int uploadCompareStrategy(Workbook wb,int type,String fileName){
		String batch_num = this.getImportBatch(fileName, "4");
		Iterator<Sheet> sheets = wb.sheetIterator();
		while(sheets.hasNext()){
		Sheet sheet = sheets.next();
		String support = sheet.getSheetName();
		System.out.println("厂家名："+support);
		int length = sheet.getLastRowNum();
		//防止空sheet导入
		if(length<=1)
			continue;
	    megrenValues = new String[length+1][6];
	    
		List<CellRangeAddress> range = sheet.getMergedRegions();
		int firstColumn = 0;
		int lastColumn = 0;
		int firstRow = 0;
		int lastRow = 0;
		int i = 0;
		for(CellRangeAddress cra : range){
			System.out.println("第"+i+"个合并单元格");
			i++;
			firstColumn = cra.getFirstColumn();
			lastColumn = cra.getLastColumn();
			firstRow = cra.getFirstRow();
			lastRow = cra.getLastRow();
			Row tmp = sheet.getRow(firstRow);
			Cell tmpcell = tmp.getCell(firstColumn);
			String value = ExcelUtil.getCellValue(tmpcell);
			System.out.println(value);
			System.out.println("单元格起始行"+firstRow+"单元格终止行"+lastRow+"单元格起始列"+firstColumn+"单元格终止列"+lastColumn);
			
			for(int rowNum = firstRow;rowNum<=lastRow;rowNum++){
				for(int colNum = firstColumn;colNum<=lastColumn;colNum++){
					megrenValues[rowNum][colNum] = value;
					System.out.println("设置单元格值:"+megrenValues[rowNum][colNum]);
				}
			}
		}
		
		for(i=1;i<length+1;i++){
			Row tmp = sheet.getRow(i);
			String server_name = getCellValue(tmp,i,0);
			if("".equals(server_name))
				continue;
			Strategy bean = new Strategy();
			String sip = getCellValue(tmp,i,1);
			String des_name = getCellValue(tmp,i,2);
			String dip = getCellValue(tmp,i,3);
			String open_port = getCellValue(tmp,i,4);
			String memo = getCellValue(tmp,i,5);
			bean.setSource_server(server_name);
			bean.setSource_ip(sip);
			bean.setDes_server(des_name);
			bean.setDes_ip(dip);
			bean.setMemo(memo);
			bean.setSupport(support);
			bean.setBatch_num(batch_num);
			bean.setLine(i+1+"");
			myInfoMapper.insertStrategy(bean);
			
			handleNetStrategyCompare(sip,dip,open_port,support,i);
		}
		
		}
		successImportBatch(batch_num);
		return 1;
	}
	
	
	/**
	 * 根据导入的表，生成网络策略到t_net_strategy
	 * @param source_ip
	 * @param des_ip
	 * @param allow_port
	 * @return
	 */
	private int handleNetStrategy(String source_ip, String des_ip, String allow_port,String sn,int line) {
		// TODO Auto-generated method stub
		source_ip = handleIPorPortFirst(source_ip);
		des_ip = handleIPorPortFirst(des_ip);
		allow_port = handleIPorPortFirst(allow_port);
		String[] sips = handleIPorPortSecond(source_ip);
		String[] dips = handleIPorPortSecond(des_ip);
		String[] ports = handleIPorPortSecond(allow_port);
		List<String> sipList = getIPLast(sips);
		List<String> dipList = getIPLast(dips);
		Map<String,List<String>> portMap = getPortLast(ports);
		for(String sip:sipList){
			for(String dip:dipList){
				for(String protocol:portMap.keySet()){
					List<String> list = portMap.get(protocol);
					for(String port:list){
						NetStrategy bean = new NetStrategy();
						bean.setLine(line+1+"");
						bean.setDes_ip(dip);
						bean.setSource_ip(sip);
						if(port!=null)
							port = port.replace(".0", "");
						bean.setPort(port);
						bean.setProtocol(protocol);
						bean.setSn(sn);
						myInfoMapper.createStrategy(bean);
					}
				}
			}
		}
		
		return 1;
	}
	
	private String handlePort(String port,String protocol){
		if(port==null) return "";
		return port.trim().replace(protocol, "").replace("(", "").replace(")","").replace("（", "").replace("）","");
	}
	
	private String getProtocol(String port){
		if(port.contains("(")||port.contains("（")){
			int begin = port.indexOf("(");
			if(begin<0)
				begin = port.indexOf("（");
			return port.substring(begin+1).replace(")", "").replace("）", "");
		}
		return "TCP";
	}
	
	private int handlePort(String str,String modelId,String type,String sn){
		//String[] pre_ports = str.trim().split("、");
		str = handleIPorPortFirst(str);
		String[] pre_ports = handleIPorPortSecond(str);
		for(int k=0;k<pre_ports.length;k++){
			String s  = pre_ports[k];
			//端口填写规范
			/*
			1.填写端口号后用括号标识端口的协议(可以是中文括号也可以是英文括号)，不加括号标识的一律认为是TCP协议
			   如   80(TCP)
			2.连续多个端口使用短横杠 - 连接,连接的起始端口需要协议一致
			   如   8080-8090  表示8080、8081、8082一直到8090连续11个端口的TCP协议
			   如   53000(UDP)-53010(UDP)   表示从53000到53010连续11个端口的UDP协议
			3. 多个不同端口，使用顿号、分隔
			   如   20、22、1521、3306、9001（UDP）  
			4. 全部端口使用英文“any”表示
			*/
			String protocol = "TCP";
			if(s.contains("-")){
				String[] ports = s.split("-");
				int startPort = 0;
				int endPort = 0;
				protocol = getProtocol(ports[0]);
				ports[0] = handlePort(ports[0],protocol); 
				ports[1] = handlePort(ports[1],protocol);
				System.out.println("起始端口:"+ports[0]);
				System.out.println("结束端口:"+ports[1]);
				startPort = Integer.parseInt(ports[0]);
				endPort = Integer.parseInt(ports[1]);
				for(int port = startPort;port<=endPort;port++){
					NetOrderPort netOrderPort = new NetOrderPort();
					netOrderPort.setSn(sn);
					netOrderPort.setOrder_id(modelId);
					netOrderPort.setPort(Integer.toString(port));
					netOrderPort.setSeq(k+1+"");
					netOrderPort.setProtocol(protocol);
					netOrderPort.setType(type);
					myInfoMapper.insertNetOrderPort(netOrderPort);
				}
			}else{
				String port = s;
				protocol = getProtocol(port);
				port = handlePort(port,protocol);
				NetOrderPort netOrderPort = new NetOrderPort();
				netOrderPort.setSn(sn);
				netOrderPort.setOrder_id(modelId);
				netOrderPort.setPort(port);
				netOrderPort.setSeq(k+1+"");
				netOrderPort.setProtocol(protocol);
				netOrderPort.setType(type);
				myInfoMapper.insertNetOrderPort(netOrderPort);
			}
		}
		return 1;
	}
	/*
	 * IP段和端口允允许输入连续IP，使用换行符或者顿号分隔，可能存在以下类型IP
	 * 172.16.0.0/16
	 * 192.168.1.13
	 * 192.168.1.13-18
	 * 192.168.1.13~18
	 * 192.168.1.13-192.168.1.18
	 * 192.168.1.13-18、192.168.1.22
	 * 互联网
	 * http://www.baidu.com
	 * www.baidu.com
	 * 
	 * 端口类似
	 * 22
	 * 1111-1122
	 * 80、443
	 * 为方便处理，首先将字符串的换行符全部转换为顿号，替换掉IP中所有的空格
	 * 将分隔符~替换成 - 
	 * 
	 */
	private String handleIPorPortFirst(String ip){
		if(ip==null)
			return "";
		ip = ip.trim().
				replaceAll("\n", "、").
				replaceAll(" ", "").
				replaceAll("~", "-");
		if(ip.endsWith("、"))
			ip = ip.substring(0,ip.length()-1);
		return ip;
	}
	
	/**
	 * IP段可能写成192.168.0.1-4或者192.168.0.1-102.168.0.4
	 * 返回完成的IP数组192.168.0.1,192.168.0.2,192.168.0.3,192.168.0.4
	 * @param ip
	 * @return
	 */
	private  String[] getIPSegement(String ip){
		String[] tmp = ip.split("-");
		int index = tmp[0].lastIndexOf(".");
		String prefix = tmp[0].substring(0,index);
		String startStr = tmp[0].substring(index+1).trim();
		if(tmp[1].contains("."))
			tmp[1]=tmp[1].replace(prefix, "").replace(".", "").trim();
		int start = Integer.parseInt(startStr);
		int end = Integer.parseInt(tmp[1]);
		String[] result = new String[end-start+1];
		for(int i=0;i<result.length;i++){
			result[i]=prefix+"."+start+"/32";
			start++;
		}
		return result;
	}
	/**
	 * 端口段解析，返回map，其中协议为map的key，默认是TCP协议
	 * @param port
	 * @return
	 */
	private Map<String,List<String>> getPortSegement(String port){
		String[] tmp = port.split("-");
		int index = tmp[0].lastIndexOf("(");
		String protocol = "TCP";
		if(index<0)
			index = tmp[0].lastIndexOf("（");
		if(index>0){
			protocol = tmp[0].substring(index+1).
			replace("(", "").
			replace(")", "").
			replace("（", "").
			replace("）", "");
			tmp[0] = tmp[0].substring(0,index).trim();
			tmp[1] = tmp[1].substring(0,index).trim();
		}
		int begin = Integer.parseInt(tmp[0]);
		int end = Integer.parseInt(tmp[1]);
		int size = end-begin+1;
		List<String> result = new ArrayList<String>();
		for(int i=0;i<size;i++){
			result.add(""+begin);
			begin ++;
		}
		
		Map<String,List<String>> resultMap = new HashMap<String,List<String>>();
		resultMap.put(protocol, result);
		return resultMap;
	}
	
	private boolean isDomain(String ip){
		if(ip==null) return false;
		boolean flag = ip.contains("互联网");
		if(flag)
			return flag;
		String regex=".*[a-zA-Z]+.*";  
	    Matcher m=Pattern.compile(regex).matcher(ip);  
	    return m.matches();
	}
	
	private String[] handleIPorPortSecond(String ip){
		if(ip.contains("、")){
			return ip.split("、");
		}else{
		  String[] result =	new String[1];
		  result[0] = ip;
		  return result;
		}
	}
	
	private List<String> getIPLast(String[] ips){
		List<String> list = new ArrayList<String>();
		for(String ip:ips){
			if(isDomain(ip)){
				list.add(ip);
			}else if(ip.contains("-")){
				String[] sips = getIPSegement(ip);
				for(String s:sips){
					list.add(s);
				}
			}else{
				if(!ip.contains("/"))
					ip=ip+"/32";
				list.add(ip);
			}
		}
		return list;
	}
	private Map<String, List<String>> getPortLast(String[] ports) {
		Map<String,List<String>> map = new HashMap<String,List<String>>();
		for(String port:ports){
			if(port.contains("-")){
			  Map<String,List<String>> m = getPortSegement(port);
			  for(String key:m.keySet()){
				  if(map.keySet().contains(key)){
					  List<String> tmpList = map.get(key);
					  List<String> tmp1List = m.get(key);
					  for(String tmp:tmp1List){
						  tmpList.add(tmp);
					  }
				  }else{
					  map.put(key, m.get(key));
				  }
			  }
			}else{
				String protocol = "TCP";
				int index = port.lastIndexOf("(");
				if(index<0)
					index = port.lastIndexOf("（");
				if(index>0){
					protocol = port.substring(index+1).
					replace("(", "").
					replace(")", "").
					replace("（", "").
					replace("）", "");
					port = port.substring(0,index).trim();
				}	
				if(map.keySet().contains(protocol)){
					List<String> s = map.get(protocol);
					if(port.contains(".0")){
						port = port.replace(".0", "");
					}
					s.add(port);
				}else{
					List<String> list = new ArrayList<String>();
					list.add(port);
					map.put(protocol, list);
				}
				
			}
		}
		return map;
	}
	private  String getCellValue(Row row,int rowNum,int colNum){
		String value = megrenValues[rowNum][colNum];
		if(value!=null&&(!"".equals(value)))
			return value;
		Cell cell = row.getCell(colNum);
		return ExcelUtil.getCellValue(cell).trim();
	}
	
	private void handleNetStrategyCompare(String source_ip, String des_ip,
			String allow_port, String support, int line) {
		// TODO Auto-generated method stub
		source_ip = handleIPorPortFirst(source_ip);
		des_ip = handleIPorPortFirst(des_ip);
		allow_port = handleIPorPortFirst(allow_port);
		String[] sips = handleIPorPortSecond(source_ip);
		String[] dips = handleIPorPortSecond(des_ip);
		String[] ports = handleIPorPortSecond(allow_port);
		List<String> sipList = getIPLast(sips);
		List<String> dipList = getIPLast(dips);
		Map<String,List<String>> portMap = getPortLast(ports);
		for(String sip:sipList){
			for(String dip:dipList){
				for(String protocol:portMap.keySet()){
					List<String> list = portMap.get(protocol);
					for(String port:list){
						NetStrategyCompare bean = new NetStrategyCompare();
						bean.setLine(line+1+"");
						bean.setDes_ip(dip);
						bean.setSource_ip(sip);
						if(port!=null)
							port = port.replace(".0", "");
						bean.setPort(port);
						bean.setProtocol(protocol);
						bean.setSupport(support);
						myInfoMapper.createStrategyCompare(bean);
					}
				}
			}
		}
	}
	
	private String getImportBatch(String fileName,String type){
		String batch_num = CommonUtil.getTimeSeq();
		ImportBatch bean = new ImportBatch();
		bean.setBatch_num(batch_num);
		bean.setFile_name(fileName);
		String id = myInfoMapper.getUUID();
		bean.setType(type);
		bean.setResult("失败");
		bean.setId(id);
		myInfoMapper.insertImportBatch(bean);
		return batch_num;
	}
	
	private int successImportBatch(String batch_num){
		return importBatchMapper.successImportBatch(batch_num);
	}
	
	
}

最后页面效果和实际功能效果