During the writing of this document, go to Party A to talk about a project. The project has not been negotiated, but it is a customer with long-term cooperation and good relationship. They just use this to practice their skills. By the way, they can make their own small broken project a little useful, and they can also attract a wave of goodwill from the customer.
Customer requirements are network management. Different cloud area network access requirements are mentioned to customers. They integrate an EXCEL to record these requirements. If the requirements pass, they configure the policies on the firewall according to the requested source IP, destination IP and port.
Due to the accumulated demand for a long time and the deleted strategy during the period, it is necessary to regularly compare whether the network strategy recorded by the applicant is consistent with the actual demand collected by the customer.
The network work order recorded by the user is as follows:
| Work order No | Applicant manufacturer | Cloud area involved | Source server | Source IP / security group | Destination server | Destination IP | Open destination port |
| 20211104-1 | Intranet access | Data acquisition server | 192.168.1.162 intranet area | Data pre database | 192.168.2.6 intranet area | 9001 | |
| 20211104-2 | Intranet | Unified portal | 10.169.130.7 | Resource reporting | 192.168.138.181 | 8080；38889 | |
| 20211105-1 | Industry Cloud | Private cloud | Private network | 172.16.22.4 | Analysis system application | 10.3.121.13 | 8080 |
| Industry Cloud - extranet | Analysis system application | 80.3.131.194 | Analysis system front-end computer | 10.0.1.84 | 9090-9099 | ||
| internet | Private network | Analysis system front-end computer | 10.133.13.84 | Map basemap service | http://cache1.online.cn | 80 | |
| internet | Network dimension | Analysis system front-end computer | 10.0.1.84 | Toponymic search service | 130.62.21.21 | 590 |
The network strategy recorded by the manufacturer is as follows:
| Source server name | Source IP | Destination server | Destination IP | Open port | remarks |
| application server | 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 | Data server | 10.0.0.2 10.0.0.3 10.0.0.4 10.0.0.5 10.0.0.6 | 6330 |
1, Demand analysis
The existing requirements are relatively simple. Compare the network requirements application form recorded by the customer with the access strategy provided by the customer manufacturer to judge whether the manufacturer's strategy is consistent with the current strategy. Since there are multiple manufacturers and there is only one network management, we can only compare the manufacturer's strategies with the customer's strategies to judge how many strategies the manufacturer has. As for whether the customer's own strategies are matched, we need to compare the strategies of all manufacturers before we can draw a conclusion.
2, Train of thought
For network policy comparison, you can't directly compare strings. Different people make records on both sides and have different recording habits. Therefore, you can only generate detailed strategies according to the records on both sides, and then compare the detailed strategies on both sides. This will generate more detailed policies. For example, the manufacturer above is only one line of EXCEL, but since there are 10 source IPS, 5 destination IPS and 1 port, 10 * 5 * 1 = 50 detailed policies will be generated. In fact, there is also a problem with the port protocol. If the port protocols recorded by the customer and the manufacturer are different, they cannot be considered to be consistent.
Because a large number of detailed strategies will be generated, manual comparison is obviously laborious and thankless. It is an ideal way to generate detailed strategies through programs and directly match the comparison results with SQL statements. We need to design two excel import functions and one excel export function, import the excel of users and manufacturers respectively, and then export an Excel to store the comparison results.
3, Question
The biggest problem of import function is the problem of excel record specification. There are some predictable problems here
1. The source IP, destination IP and port contain multiple. What is the function of distinguishing individual IP
2.IP subnet mask problem (ABCD network address problem)
3.IP segment and port segment problems (continuous IP or continuous port cannot be written one by one)
4. Internet access (website or Chinese processing)
5. Problem data (such as active, no target, no port, etc.)
6. Abandonment of work order (i.e. the network work order is recorded, but it is actually abandoned and not opened for various reasons)
These are all problems in the original documents, and there are also some technical and business problems:
1. For the beauty of the table, the user uses a lot of merged cells, line breaks and spaces. In program processing, the merged cells should be split, the spaces should be removed, and the line breaks should be replaced with separators
2. How to deal with the subsequent data after the user imports the comparison once, whether to clear all and import again or import incrementally
3. If users want to compare historical data, how to deal with it
4, Negotiation
The problem of original data in excel is simply one: standardization. That is, how to fill in the IP address and port of the original data. If it is a web application and the user enters data from the web, we can naturally restrict the user's input, but now the user imports from excel, there is no way to restrict the user, so we can only negotiate the description. After communication, specify the specifications of IP and port
1. The IP address must be completely written, such as 192.168.0.1
2. Continuous IPS are separated by a short horizontal bar, followed by the end address of the IP. For example, 192.16.0.1-11 represents 11 IPS from 192.168.0.1 to 192.168.0.11
3. The IP segment must be marked with a mask, such as 192.168.0.0/24, which cannot be represented by 192.168.0.x
4. Multiple IP S are separated by newline or stop sign, such as 192.168.0.1-11, 192.168.13-20 and 192.168.1.0/24
5. For Internet access requirements, if there is a specific source or target, fill in the domain name or IP. If it is open to all, fill in the Chinese character "Internet", As https://test.cloud.com
6. Do not fill in the IP address with Chinese characters, such as 10.168.233* docker address field
7. After filling in the port number, use parentheses to identify the port protocol (Chinese parentheses or English parentheses). Those without parentheses are considered as TCP protocols As 80(TCP)
8. Multiple consecutive ports use the short bar - connection, and the starting port of the connection needs to be consistent with the protocol
As 8080-8090 Represents the TCP protocol of 11 consecutive ports from 8080, 8081, 8082 to 8090
As 53000(UDP)-53010(UDP) Represents the UDP protocol of 11 consecutive ports from 53000 to 53010
9. Multiple different ports, separated by numbers and As 20,22,1521,3306,9001(UDP)
10. All ports are represented by "any" in English
11. The port shall not have Chinese character description
For incremental data processing, users do not have any ideas. What I consider is to add an import batch table. Each import record indicates what the import is and which import belongs to. In this way, even if duplicate data is imported many times, it can be distinguished according to the import batch number. Users can select which two to compare according to the imported batch.
5, Data sheet design
Design the data sheet according to the design idea
1. Network work order form
CREATE TABLE `t_net_order` ( `id` varchar(36) NOT NULL, `sn` varchar(255) comment 'Work order number: the work order number related to the date generated by the customer', `proposer` varchar(255) comment 'Applicant', `seq` varchar(4) DEFAULT NULL COMMENT 'Work order serial number', `source_zone` varchar(600) DEFAULT NULL COMMENT 'Source cloud area', `des_zone` varchar(600) DEFAULT NULL COMMENT 'Target cloud area', `source_server` varchar(600) DEFAULT NULL COMMENT 'Source server', `des_server` varchar(600) DEFAULT NULL COMMENT 'Target server', `source_ip` varchar(1000) DEFAULT NULL COMMENT 'source IP,The value is the source details', `des_ip` varchar(1000) DEFAULT NULL COMMENT 'target IP,The specific values are target details', `allow_port` varchar(600) DEFAULT NULL COMMENT 'Open port', `memo` varchar(200) DEFAULT NULL COMMENT 'remarks', `operator` varchar(30) DEFAULT NULL COMMENT 'Operator', `order_type` varchar(30) DEFAULT '1' COMMENT 'Work order type, 1 ordinary, 2 temporary, 3 urgent', `batch_num` varchar(50) DEFAULT NULL COMMENT 'Import batch number', `line` varchar(30) DEFAULT NULL COMMENT 'Line number, excel that 's ok', `create_time` datetime DEFAULT NULL, `modify_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='Network work order form'
Here, according to the actual situation, several fields not in excel are added.
2. Manufacturer strategy table
CREATE TABLE `t_strategy_support` ( `id` varchar(36) DEFAULT NULL, `source_server` varchar(600) DEFAULT NULL COMMENT 'Source server', `source_ip` varchar(1000) DEFAULT NULL COMMENT 'source IP', `des_server` varchar(600) DEFAULT NULL COMMENT 'Destination server', `des_ip` varchar(1000) DEFAULT NULL COMMENT 'objective IP', `open_port` varchar(1000) DEFAULT NULL COMMENT 'Open port', `memo` varchar(1000) DEFAULT NULL COMMENT 'remarks', `support` varchar(100) DEFAULT NULL COMMENT 'manufactor', `batch_num` varchar(60) DEFAULT NULL COMMENT 'Import batch number', `line` varchar(60) DEFAULT NULL COMMENT 'excel Line number in', `create_time` datetime DEFAULT NULL, `modify_time` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='Manufacturer's network strategy table'
3. Network policy table, which is the network policy generated according to the customer's network work order
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
There's nothing to say. The source IP, destination IP, open port protocol, import batch and corresponding line number are mainly to facilitate customers to check the source excel file to verify whether this strategy should be generated
4. Manufacturer's network strategy table
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
The manufacturer's network policy table is the same as the user's network policy table. You can create a new field on the same table to distinguish. However, considering that a large amount of policy data will be generated, it is processed separately. (in fact, it is lazy. The manufacturer's network policy is not generated by the work order, and there is no work order field sn at all.) if the amount of data is large, Index the source IP, target IP and port as the main comparison field.
5. Import batch table
CREATE TABLE `t_import_batch` ( `id` varchar(36) DEFAULT NULL, `batch_num` varchar(60) DEFAULT NULL COMMENT 'Import batch number', `file_name` varchar(200) DEFAULT NULL COMMENT 'Import file name', `type` varchar(20) DEFAULT NULL COMMENT 'Import type, 1.Host account, 2.Paper network work order, 3.Electronic network work order, 4.Aggregate network policy', `result` varchar(20) DEFAULT NULL COMMENT 'Import results,Success or failure', `create_time` datetime DEFAULT NULL, `modify_time` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='File import batch table'
6, Page
The page function is relatively simple, that is, two buttons for file upload. The other two buttons on the following page are the other two import functions for users.
<!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>
Function list
<table class="list">
<tr>
<td>
<h5>Import function</h5>
</td>
</tr>
<tr>
<td>
<form id="form_table" action="uploadHost" enctype="multipart/form-data" method="post" target="_self">
<button type="submit" >Import host account</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" >Import original network job</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" >Import network work order</button>
<input type="file" name="netOrderFile"/>
<font size=1px>Importing a network work order will generate a network access policy, which can be filled in in the remarks field <a class="link" th:href="@{/main/memoAvoid}" target="_self">Free policy keyword</a> To avoid generating policies</font>
</form>
</td>
</tr>
<tr>
<td>
<form id="form_table3" action="uploadCompareStrategy" enctype="multipart/form-data" method="post" target="_self">
<button type="submit" >Import aggregate policy</button>
<input type="file" name="compareStrategyFile"/>
</form>
</td>
</tr>
<tr>
<td>
<h5>Export function</h5>
</td>
</tr>
<tr>
<td>
<form id="form_table2" action="download" enctype="multipart/form-data" method="post" target="_self">
<button type="submit" >Comparison of aggregate network strategies</button>
</form>
</td>
</tr>
</table>
</body>
</html>7, Controller
Considering that users may import 2003 version of excel and 2007 version of excel, the program adds a process, but it seems that it can't be used in practice
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", "The host account file is empty");
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", "File format error");
return "myInfo/base";
}
}catch(IOException ex){
ex.printStackTrace();
model.addAttribute("status", "false");
model.addAttribute("msg", "Import exception");
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", "The network work order file is empty");
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", "File format error");
return "myInfo/base";
}
}catch(IOException ex){
ex.printStackTrace();
model.addAttribute("status", "false");
model.addAttribute("msg", "Import exception");
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", "The network work order file is empty");
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", "File format error");
return "myInfo/base";
}
}catch(IOException ex){
ex.printStackTrace();
model.addAttribute("status", "false");
model.addAttribute("msg", "Import exception");
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", "The network work order file is empty");
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", "File format error");
return "myInfo/base";
}
}catch(IOException ex){
ex.printStackTrace();
model.addAttribute("status", "false");
model.addAttribute("msg", "Import exception");
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 {
// Get output stream
os = response.getOutputStream();
// Reset output stream
response.reset();
// Set output file header
response.setHeader("Content-disposition",
"attachment; filename=Network policy comparison results.xlsx");
// Define output type
response.setContentType("application/msexcel");
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
} finally {
// close resource
try {
assert os != null;
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return "myInfo/base";
}
}
8, Service
The main logic is to merge cells, disassemble source IP, destination IP and port, and generate network policies.
8.1 processing of merged cells
When processing cells and merging cells, POI finds that only the first row and first column of the cell have values, and there is no content in other cells. Therefore, the processing method considered is to obtain the starting row and starting column of the cell, create a two-dimensional array completely corresponding to the excel table, and, Set all merged cells to the values of the first row and first column of the merged cell, and take values from the corresponding two-dimensional array when obtaining.
First, define a two-dimensional array in the class
private String[][] megrenValues;
Initialize the two-dimensional array, obtain the number of start and end rows and columns of each merged cell, and set it to the value of the corresponding cell. When obtaining the actual value of excel cells, first take the value from this two-dimensional array. If no value is obtained, it means that this cell is not a merged cell, and the value of the original table is taken directly. If there is a value, it indicates that it is a merged cell, and the value is returned.
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("The first"+i+"Merge cells");
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("Cell start row"+firstRow+"Cell termination row"+lastRow+"Cell start column"+firstColumn+"Cell termination column"+lastColumn);
for(int rowNum = firstRow;rowNum<=lastRow;rowNum++){
for(int colNum = firstColumn;colNum<=lastColumn;colNum++){
megrenValues[rowNum][colNum] = value;
System.out.println("Set cell value:"+megrenValues[rowNum][colNum]);
}
}
}Excel cell value
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 address processing
Since the IP address filling is standardized, all are made according to our preset IP address. The first is preprocessing. IP and port can be common
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;
}It is mainly to replace the newline with a stop sign, remove the space (theoretically, all spaces in the field should not exist) and replace the IP segment separator. After the processing is completed, separate it with split to obtain an array
private String[] handleIPorPortSecond(String ip){
if(ip.contains(",")){
return ip.split(",");
}else{
String[] result = new String[1];
result[0] = ip;
return result;
}
}Even a single IP or a single port form an array to facilitate unified processing, and then the IP and port should be processed separately
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;
}There are also two tools and methods: judge whether the Internet address or whether the IP segment is continuous
private boolean isDomain(String ip){
if(ip==null) return false;
boolean flag = ip.contains("internet");
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;
}Port processing
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;
}Use a map to process the returned port. The key of the map is a four layer protocol, tcp or udp. Of course, a seven layer protocol is also possible. Tools and methods
/**
* The port segment is parsed and the map is returned. The protocol is the key of the map. The default is TCP protocol
* @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 complete service code
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("Comparison results");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(new XSSFRichTextString("source IP"));
row.createCell(1).setCellValue(new XSSFRichTextString("objective IP"));
row.createCell(2).setCellValue(new XSSFRichTextString("Open port"));
row.createCell(3).setCellValue(new XSSFRichTextString("Port protocol"));
row.createCell(4).setCellValue(new XSSFRichTextString("Policy source line"));
row.createCell(5).setCellValue(new XSSFRichTextString("manufactor"));
row.createCell(6).setCellValue(new XSSFRichTextString("Network work order"));
row.createCell(7).setCellValue(new XSSFRichTextString("Work order table matching row"));
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;
}
/**
* Import the network work order and generate the corresponding network policy for later comparison
* Strategy table
* 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("The first"+i+"Merge cells");
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("Cell start row"+firstRow+"Cell termination row"+lastRow+"Cell start column"+firstColumn+"Cell termination column"+lastColumn);
for(int rowNum = firstRow;rowNum<=lastRow;rowNum++){
for(int colNum = firstColumn;colNum<=lastColumn;colNum++){
megrenValues[rowNum][colNum] = value;
System.out.println("Set cell value:"+megrenValues[rowNum][colNum]);
}
}
}
//Get the data of the memo exemption table
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. Network work order
model.setBatch_num(batch_num);
model.setLine(i+1+"");
myInfoMapper.insertNetOrder(model);
String memo = getCellValue(tmp,i,8);
//There is no network policy for the application of virtual IP and the application of load balancing
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;
}
/**
* Upload the manufacturer's comparison file for file comparison
*/
@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("Manufacturer name:"+support);
int length = sheet.getLastRowNum();
//Prevent empty sheet import
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("The first"+i+"Merge cells");
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("Cell start row"+firstRow+"Cell termination row"+lastRow+"Cell start column"+firstColumn+"Cell termination column"+lastColumn);
for(int rowNum = firstRow;rowNum<=lastRow;rowNum++){
for(int colNum = firstColumn;colNum<=lastColumn;colNum++){
megrenValues[rowNum][colNum] = value;
System.out.println("Set cell value:"+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;
}
/**
* Generate network policy to t according to the imported table_ 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];
//Port filling specification
/*
1.After filling in the port number, use parentheses to identify the port protocol (Chinese parentheses or English parentheses). Those without parentheses are considered as TCP protocols
Such as 80(TCP)
2.Multiple consecutive ports use the short bar - connection, and the starting port of the connection needs to be consistent with the protocol
For example, 8080-8090 represents the TCP protocol of 11 consecutive ports from 8080, 8081, 8082 to 8090
For example, 53000(UDP)-53010(UDP) represents the UDP protocol of 11 consecutive ports from 53000 to 53010
3. Multiple different ports, separated by numbers and
Such as 20, 22, 1521, 3306, 9001 (UDP)
4. All ports are represented by "any" in English
*/
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("Start port:"+ports[0]);
System.out.println("End port:"+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 Segment and port allow continuous IP to be input, separated by line feed or stop sign. The following types of IP may exist
* 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
* internet
* http://www.baidu.com
* www.baidu.com
*
* Port similar
* 22
* 1111-1122
* 80,443
* In order to facilitate processing, first convert all line breaks of the string into stop signs and replace all spaces in the IP
* Replace separator ~ with-
*
*/
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 The paragraph may be written as 192.168.0.1-4 or 192.168.0.1-102.168.0.4
* Return the completed IP array 192.168.0.1192.168.0.2192.168.0.3192.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;
}
/**
* The port segment is parsed and the map is returned. The protocol is the key of the map. The default is TCP protocol
* @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("internet");
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("fail");
bean.setId(id);
myInfoMapper.insertImportBatch(bean);
return batch_num;
}
private int successImportBatch(String batch_num){
return importBatchMapper.successImportBatch(batch_num);
}
}
Finally, the page effect and actual function effect
