Optical fiber link table generation method and system based on optical fiber connection table

Abstract

The invention discloses an optical fiber link table generation method and system based on an optical fiber link table, and the method comprises the following steps: S1, based on an EPPlus library, obtaining an uploaded excel document by using an ASP.NET Core asynchronous model, extracting fields in a header, storing each line of data into an entity according to a deserialization technology, adding the data into a set List, and removing null lines by using a C # null data judgment method, the set List data is further optimized, an Entity Framework Core is used for carrying out asynchronous database operation, and the data is stored in a database; s2, acquiring an optical cable number, an optical cable model, an optical distribution number / port type / port number and cabinet connection equipment according to a connection table sql statement in SQL server 2008R2, and matching whether the same optical cable is connected with different equipment to form an optical fiber link table; and S3, according to the optical fiber link table generated in the step S2, obtaining data needing flag-shaped label by using a VisualBrush class in a C # WPF framework, setting a printing format by using a PrintTicket method, and carrying out label printing.

Description

Technical Field

[0001] The present invention relates to the technical field of link table generation, and more particularly to a method and system for generating an optical fiber link table based on an optical fiber connection table. Background Art

[0002] Smart substations (hereinafter referred to as smart stations) use a large number of optical fibers to transmit information. The information transmitted by one optical fiber is equivalent to the information transmitted by multiple cables in conventional stations. The on-site optical fiber layout is "tangled". If the disconnection and connection are wrong during the operation, it may lead to serious consequences such as false protection or refusal to operate. The "flag-shaped labels" and "optical fiber link table" (hereinafter referred to as "labels") posted on the site of the smart station are necessary label identifications on site, which can provide an accurate basis for the disconnection and connection of optical fibers. If the intelligent devices in the smart station are regarded as "cities", then the optical fibers between the devices can be regarded as "roads" between "cities". The "flag-shaped labels" posted on the optical fiber can be regarded as "road signs", which contain the information of the port and the destination. The "optical fiber link table" contains more comprehensive information and can be regarded as a "navigation", which contains all the port information from the starting point to the end point. The correctness of the posted "labels" must be ensured on site, otherwise it may mislead the work of troubleshooting, and even cause serious consequences such as false equipment operation and refusal to operate.

[0003] The publication number is CN112787268A, and the name is a method for constructing integrated wiring of visualization equipment, which includes the following steps: S1. Perform piping work on metal pipes; S2. Lay metal pipes; S3. Clean metal pipes; S4. Lay cables in metal pipes; S5. Lay cables in bridges and cable troughs; S6. Install cabinets and distribution frames of visualization equipment; S7. Terminate cables; S8. Debug the integrated wiring system; S9. Project acceptance of the integrated wiring system.

[0004] The publication number is CN109889594A, and the name is an intelligent management system for integrated wiring, which includes a central processing unit, a power supply, a server, a display, a controller, a modem, a router, an RFID reader and an RFID tag. The power supply, server, display, and controller are respectively connected to the central processing unit, and the controller is connected to the modem, the router, and the RFID reader in turn. The RFID tags are attached to each cable of the integrated wiring system.

[0005] However, the above technical solution is only used to record or change the content of the electronic tag according to the identification of different equipment, cables and pipelines in different areas. It cannot automatically generate a fiber link table based on the cable model, port and equipment information in the cable connection table. Manual label production is required, the label accuracy is low, and repeated rework is required.

[0006] Therefore, how to provide a method and system for generating an optical fiber link table based on an optical fiber connection table that extracts key fields, automatically integrates key information, and automatically generates labels is an urgent problem that those skilled in the art need to solve. Summary of the invention

[0007] In view of this, the present invention provides a method and system for generating an optical fiber link table based on an optical fiber connection table, aiming to solve the technical problems that there is no automatic generation of an optical fiber link table according to the optical cable model, port and device information in the optical cable connection table, manual labeling is required, the labeling accuracy is low, and repeated rework is required.

[0008] In order to achieve the above object, the present invention adopts the following technical solution:

[0009] A method for generating an optical fiber link table based on an optical fiber connection table comprises the following steps:

[0010] S1. EPPlus library uses the asynchronous model of ASP.NET Core to obtain the uploaded Excel document, extract the fields in the header, save each row of data into an entity based on the deserialization technology, add it to the collection List and use the C# method to determine the empty data to remove the empty rows, further optimize the collection List data, use Entity Framework Core to perform asynchronous database operations, and save the data into the database;

[0011] S2. According to the SQL statement for connecting tables in SQL server 2008R2, the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to the cabinet are obtained to match whether the same optical cable is connected to different equipment, and the optical fiber link table is formed;

[0012] S3. According to the fiber link table generated in S2, use the VisualBrush class in the C#WPF framework to obtain the required flag data, use the PrintTicket method to set the print format, and print the label.

[0013] Furthermore, the EPPlus library uses the asynchronous model of ASP.NET Core to obtain the uploaded xcel document:

[0014] Define a controller Controller, in the controller Controller, create an Action method named UploadExcel as the API endpoint. The UploadExcel method accepts a parameter named excelFile, the type of which is IFormFile. IFormFile is an interface provided by ASP.NET Core to represent uploaded files.

[0015] Before processing the file, validate the received excel document, check if excelFile is null or if its Length property is 0, confirm that the file has been uploaded, and use StringComparison.OrdinalIgnoreCase to ignore the case of the file extension comparison;

[0016] Copy the file contents to a memory stream and create a MemoryStream instance. This stream will be used to store the data copied from the uploaded file. Use the excelFile.CopyToAsync(stream) method to asynchronously copy the file contents to the MemoryStream, which is used to continue processing other requests while waiting for the file upload to complete.

[0017] Use the await keyword to wait for the CopyToAsync method to complete so that the next step will not be performed until the file is completely copied to the memory stream;

[0018] Create an ExcelPackage instance and pass the MemoryStream as a parameter to the ExcelPackage instance to open the Excel document. Through the ExcelPackage instance, access the workbooks and worksheets in the Excel document and traverse the rows and columns in the worksheet.

[0019] Furthermore, extract the fields in the table header and save each row of data into an entity based on the deserialization technology:

[0020] Iterate through all worksheets in the Excel file; get the header row of each worksheet; set the column name of the DataTable according to the name of the worksheet, read data from the third row, and for each row of data, create a new instance of type T, one, iterate through all attributes of type T, and if the attribute has a DescriptionAttribute attribute, get its description, find the corresponding column from the DataTable according to the description and column name, and read the cell value. If the cell value is empty, try to use the value of the previous row, convert the cell value to the type of the attribute, and set it to the attributes of the one instance, and add the filled one instance to the list.

[0021] Furthermore, add the collection List and use the C# method to determine empty data to remove empty rows, further optimize the collection List data, use Entity Framework Core to perform asynchronous database operations, and save the data to the database as follows:

[0022] Use LINQ's Where method to filter out empty strings and null values, check whether the string is empty or null through the string.IsNullOrEmpty(item) method, make sure that a reference to System.Data.SqlClient is included, then define the SQL command, create a database connection and SQL command, add parameters, traverse the filtered data, use Entity Framework Core for asynchronous database operations, and save the data to the database.

[0023] Furthermore, use Entity Framework Core to perform asynchronous database operations and save the data to the database, specifically:

[0024] Create a class that inherits from DbContext as the entry point for interacting with the database. In this class, define the database collection DbSet to represent the tables in the database, configure the database connection string, define one or more entity classes, which will be mapped to the tables in the database. The properties of each entity class correspond to the columns in the database table. Use the Add method to add new entities to the DbSet of DbContext, and use the SaveChangesAsync method to asynchronously save the changes to the database.

[0025] DbSet is a table in the database. Each DbSet property corresponds to a table in the database. The OnConfiguring method is used to configure the database connection string and other database-specific settings.

[0026] Furthermore, according to the SQL statement for connecting tables in SQL server 2008R2, the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to this cabinet are obtained as follows:

[0027] Build the table structure:

[0028] Cables table: Cable ID, Cable Model;

[0029] Connections table: Cable ID, port type, port number, and device ID of the device connected to the cabinet.

[0030] Devices table: device number DeviceID, device name DeviceName, device type DeviceType.

[0031] Furthermore, it is matched whether the same optical cable is connected to different devices, and the optical fiber link table is specifically as follows:

[0032] Select the cable number and model from the Cables table; join the Cables table through the Connections table to obtain the port information; join the Connections table through the Devices table to obtain the device name; use INNER JOIN and LEFT JOIN to find different devices connected to the same cable; use GROUP BY and HAVING clauses to ensure that the same cable is connected to multiple different devices.

[0033] Further, create a DrawingVisual object, set the text format, calculate the text position, draw the text, create a VisualBrush and set the stretch mode;

[0034] Create an XPS print document writer, set PrintTicket, get PrintTicket, set print format, and apply PrintTicket;

[0035] Creates a VisualBrush of flag data for printing.

[0036] A system for generating an optical fiber link table based on an optical fiber connection table, comprising:

[0037] Document acquisition module, used to acquire and read Excel documents;

[0038] The field extraction module is used to extract fields from the Excel document and save each row of data into an entity based on the deserialization technology;

[0039] Database module, used to store entities and table structures;

[0040] A matching module is used to find different devices connected to the same optical cable, ensuring that the same optical cable can be connected to multiple different devices;

[0041] The link combination module is used to build a complete optical fiber link table through the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to this cabinet;

[0042] The printing module is used to set printing parameters and print labels based on flag label specifications.

[0043] Furthermore, the Excel document includes .xls format and .xlsx format.

[0044] The above technical solution includes at least the following technical effects:

[0045] It can be seen from the above technical solutions that the present invention discloses a method and system for generating a fiber link table based on a fiber connection table. Compared with the prior art, the present invention reduces the workload of manual input and improves the efficiency of data management by automatically extracting data from Excel documents and converting them into entity objects; uses deserialization technology and LINQ query to filter empty data, ensures the accuracy and consistency of data, and reduces data quality problems caused by human errors; constructs a fiber link table, uses the C#WPF framework and the VisualBrush class to automatically print labels, and improves the efficiency and accuracy of label management. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying creative work.

[0047] Figure 1 The present invention is a schematic diagram of the process structure of a method for generating an optical fiber link table based on an optical fiber connection table.

[0048] Figure 2 The present invention is a schematic structural diagram of a system for generating an optical fiber link table based on an optical fiber connection table. DETAILED DESCRIPTION

[0049] The technical solutions in the embodiments of the present invention are described clearly and completely below. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0050] See attached Figure 1-2 The embodiment of the present invention discloses a method for generating a fiber link table based on a fiber connection table, comprising the following steps:

[0051] S1. Based on the NPOI library in C#.net, use the asynchronous model of ASP.NET Core to obtain the uploaded Excel document, extract the fields in the header, save each row of data into an entity based on the deserialization technology, add it to the collection List and use the C# empty data judgment method to remove empty rows, further optimize the collection List data, use Entity FrameworkCore for asynchronous database operations, and save the data into the database;

[0052] S2. According to the SQL statement for connecting tables in SQL server 2008R2, the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to the cabinet are obtained to match whether the same optical cable is connected to different equipment, and the optical fiber link table is formed;

[0053] S3. According to the fiber link table generated in S2, use the VisualBrush class in the C#WPF framework to obtain the required flag data, use the PrintTicket method to set the print format, and print the label.

[0054] To further optimize the above technical solution, based on the NPOI library in C#.net, use the asynchronous model of ASP.NET Core to obtain the uploaded xcel document:

[0055] Define a controller Controller, in the controller Controller, create an Action method named UploadExcel as the API endpoint. The UploadExcel method accepts a parameter named excelFile, the type of which is IFormFile. IFormFile is an interface provided by ASP.NET Core to represent uploaded files.

[0056] Before processing the file, validate the received excel document, check if excelFile is null or if its Length property is 0, confirm that the file has been uploaded, and use StringComparison.OrdinalIgnoreCase to ignore the case of the file extension comparison;

[0057] Copy the file contents to a memory stream and create a MemoryStream instance. This stream will be used to store the data copied from the uploaded file. Use the excelFile.CopyToAsync(stream) method to asynchronously copy the file contents to the MemoryStream, which is used to continue processing other requests while waiting for the file upload to complete.

[0058] Use the await keyword to wait for the CopyToAsync method to complete so that the next step will not be performed until the file is completely copied to the memory stream;

[0059] Create an ExcelPackage instance and pass the MemoryStream as a parameter to the ExcelPackage instance to open the Excel document. Through the ExcelPackage instance, access the workbooks and worksheets in the Excel document and traverse the rows and columns in the worksheet.

[0060] To further optimize the above technical solution, extract the fields in the table header and save each row of data into an entity based on the deserialization technology. Specifically:

[0061] Iterate through all worksheets in the Excel file; get the header row of each worksheet; set the column name of the DataTable according to the name of the worksheet, read data from the third row, and for each row of data, create a new instance of type T, one, iterate through all attributes of type T, and if the attribute has a DescriptionAttribute attribute, get its description, find the corresponding column from the DataTable according to the description and column name, and read the cell value. If the cell value is empty, try to use the value of the previous row, convert the cell value to the type of the attribute, and set it to the attributes of the one instance, and add the filled one instance to the list.

[0062] To further optimize the above technical solution, add the collection List and use the C# method to determine empty data to remove empty rows, further optimize the collection List data, use Entity Framework Core to perform asynchronous database operations, and save the data to the database as follows:

[0063] Use LINQ's Where method to filter out empty strings and null values, check whether the string is empty or null through the string.IsNullOrEmpty(item) method, make sure that a reference to System.Data.SqlClient is included, then define the SQL command, create a database connection and SQL command, add parameters, traverse the filtered data, use Entity Framework Core for asynchronous database operations, and save the data to the database.

[0064] To further optimize the above technical solution, use Entity Framework Core to perform asynchronous database operations and save data to the database, specifically:

[0065] Create a class that inherits from DbContext as the entry point for interacting with the database. In this class, define the database collection DbSet to represent the tables in the database, configure the database connection string, define one or more entity classes, which will be mapped to the tables in the database. The properties of each entity class correspond to the columns in the database table. Use the Add method to add new entities to the DbSet of DbContext, and use the SaveChangesAsync method to asynchronously save the changes to the database.

[0066] DbSet is a table in the database. Each DbSet property corresponds to a table in the database. The OnConfiguring method is used to configure the database connection string and other database-specific settings.

[0067] To further optimize the above technical solution, according to the SQL statement of the SQL server 2008R2, the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to this cabinet are obtained:

[0068] Build the table structure:

[0069] Cables table: Cable ID, Cable Model;

[0070] Connections table: Cable ID, port type, port number, and device ID of the device connected to the cabinet.

[0071] Devices table: device number DeviceID, device name DeviceName, device type DeviceType.

[0072] In order to further optimize the above technical solution, it is necessary to match whether the same optical cable is connected to different devices and combine them into an optical fiber link table, specifically:

[0073] Select the cable number and model from the Cables table; join the Cables table through the Connections table to obtain the port information; join the Connections table through the Devices table to obtain the device name; use INNER JOIN and LEFT JOIN to find different devices connected to the same cable; use GROUP BY and HAVING clauses to ensure that the same cable is connected to multiple different devices.

[0074] To further optimize the above technical solution, create a DrawingVisual object, set the text format, calculate the text position, draw the text, create a VisualBrush and set the stretch mode;

[0075] Create an XPS print document writer, set PrintTicket, get PrintTicket, set print format, and apply PrintTicket;

[0076] Creates a VisualBrush of flag data for printing.

[0077] A system for generating an optical fiber link table based on an optical fiber connection table, comprising:

[0078] Document acquisition module, used to acquire and read Excel documents;

[0079] The field extraction module is used to extract fields from the Excel document and save each row of data into an entity based on the deserialization technology;

[0080] Database module, used to store entities and table structures;

[0081] A matching module is used to find different devices connected to the same optical cable, ensuring that the same optical cable can be connected to multiple different devices;

[0082] The link combination module is used to build a fiber link table based on the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to this cabinet;

[0083] The printing module is used to set printing parameters and print labels based on flag label specifications.

[0084] To further optimize the above technical solution, the Excel document includes .xls format and .xlsx format.

[0085] Step 1: Data import and processing

[0086] 1.1Excel file reading

[0087] Create an API endpoint: First, you need to create an API endpoint to receive uploaded files. Use the IFormFile parameter to receive uploaded files.

[0088]

[0089]

[0090] In the above code, we use the ExcelPackage class (from the EPPlus library) to process the uploaded Excel file. The ExcelPackage class allows reading and writing Excel files. You need to copy the uploaded file stream into a MemoryStream and then use ExcelPackage to open and process it.

[0091] The uploadToAsync method is an asynchronous method, which allows uploading files without blocking the thread, which is very important for improving the performance and responsiveness of the application.

[0092] The specific fiber connection table is as follows:

[0093] Optical Cable Table

[0094]

[0095]

[0096] Tail Cable Table

[0097]

[0098]

[0099] 1.2 Extract header and data

[0100] Traverse all worksheets in the Excel file and find the worksheet named CableInfo. Get the header row and set the column names of DataTabledt.

[0101] Read data starting from the third line, for each line of data:

[0102] Creates a new instance of type Cable, one.

[0103] Iterate over all props of the Cable type.

[0104] If the property has a DescriptionAttribute attribute, get its description.

[0105] According to the description and column name, find the corresponding column from the DataTable and read the value of the cell. If the value of the cell is empty, try to use the value of the previous row.

[0106] Convert the cell value to the property type and set it to the property of the one instance. Add the populated one instance to the list.

[0107] 1.3 Data cleaning and storage

[0108] Use LINQ's Where method to filter out empty strings and null values:

[0109] csharp

[0110] var filteredList=list.Where(item

[0111] =>! string.IsNullOrEmpty(item.Property)).ToList();

[0112] Make sure you have included a reference to System.Data.SqlClient.

[0113] Define SQL commands, create database connections and SQL commands, and add parameters.

[0114] Traverse the filtered data and insert it into the database:

[0115]

[0116] Use Entity Framework Core for asynchronous database operations and save data to the database. Install EF Core:

[0117] Configure DbContext:

[0118] Create a class that inherits from DbContext and configure the model and database connection string.

[0119]

[0120]

[0121] Define an entity class that will map to a table in the database.

[0122]

[0123] Use the Add or AddAsync method to add new entities to the DbContext.

[0124]

[0125] Use the SaveChangesAsync method to save changes to the database asynchronously.

[0126]

[0127] Step 2: Generate a fiber link table

[0128] 2.1 Build table structure

[0129] Create three tables: Cables, Connections, and Devices.

[0130] 2.2 SQL Query

[0131] Write a SQL query to join these tables and get the required information:

[0132] SELECT C.CableID,C.CableModel,D.DeviceNameFROM Cables CINNER JOINConnections Conn ON C.CableID=Conn.CableIDLEFT JOIN Devices D ONConn.DeviceID=D.DeviceIDGROUP BY C.CableID,C.CableModel,D.DeviceNameHAVINGCOUNT(D.DeviceName)>1;

[0133] Step 3: Print the Flag Label

[0134] 3.1 Create a DrawingVisual object

[0135] Use the DrawingVisual object in WPF to draw text and shapes.

[0136] Formats text, calculates text position, and draws text.

[0137] 3.2 Set the print format

[0138] Create an XPS print document writer and set the PrintTicket:

[0139] XpsDocumentWriter writer=

[0140] PrintQueue.CreateXpsDocumentWriter("YourDocumentName");

[0141] writer.Write(yourDocument);

[0142] 3.3 Create VisualBrush and print

[0143] Create a VisualBrush of the flag data and apply it to the printed document.

[0144] Use the Print method to print out the flag labels.

[0145] Specific Examples

[0146] Assume we have an Excel file CableData.xlsx which contains the following data:

[0147] Cable number Optical cable model Optical distribution number Port Type Port Number Equipment connected to this cabinet Cable001 Model A GPN001 Type A 1 Device1 Cable001 Model A GPN002 Type B 2 Device2

[0148] Data import:

[0149] Read the Excel file, extract the above data, and save it in the database.

[0150] Generate a fiber link table:

[0151] The database is queried and it is found that Cable001 is connected to Device1 and Device2.

[0152] Print flag labels:

[0153] Create a flag label with Cable001-ModelA printed on it, and the connected devices Device1 and Device2.

[0154] Use WPF's VisualBrush to draw the label content onto a visual object.

[0155] Set the print format and print out the label content.

[0156] To optimize SQL query efficiency:

[0157] Avoid full table scans: Consider creating indexes on the columns involved in WHERE and ORDER BY to reduce the possibility of full table scans.

[0158] Avoid NULL value judgment: Try to avoid NULL value judgment on fields in the WHERE clause, because this will cause the engine to abandon the use of indexes and perform a full table scan. You can avoid NULL values ​​by setting default values.

[0159] Avoid using the != or <> operators: Using these operators in the WHERE clause will cause the engine to abandon the use of indexes and perform a full table scan.

[0160] Avoid using OR join conditions: Using OR to join conditions in the WHERE clause will cause the engine to abandon the use of indexes and perform a full table scan. You can consider using UNION ALL instead.

[0161] Use IN and NOT IN with caution: These operators also cause full table scans. For consecutive values, use BETWEEN instead of IN.

[0162] Avoid leading percent signs in LIKE queries: For example, name like'%abc%' will result in a full table scan. If you need to improve efficiency, consider full-text search.

[0163] Avoid expression operations on fields in the WHERE clause: this will cause the engine to abandon the use of indexes and perform a full table scan. Move the operation to the right of the equal sign.

[0164] Reasonable use of EXISTS and NOT EXISTS clauses: These clauses can reduce the global scan of the table and improve query efficiency.

[0165] Use numeric fields: Try to use numeric fields instead of character fields to improve query and connection performance.

[0166] Avoid non-initial search: In indexed character data, using non-initial search (such as LIKE'% L%') will prevent the engine from taking advantage of the index.

[0167] Use views to speed up queries: Sorting a subset of a table and creating a view can sometimes speed up queries and reduce disk I / O.

[0168] Use BETWEEN instead of IN: For continuous values, using BETWEEN instead of IN can improve query efficiency.

[0169] Partial use of indexes: If only some columns in the query conditions have indexes, you can consider breaking the query into multiple small queries, each involving only one index column, and then use UNION ALL to merge the results.

[0170] Suppose we have a data center and need to manage multiple fiber optic cable lines, each of which is connected to different equipment. We need to import data from an Excel document, generate a fiber link table, and print out flag labels for each fiber optic cable line.

[0171] Step S1: Data import and processing

[0172] Excel document upload: The user uploaded an Excel file containing the optical cable line information.

[0173] Data extraction:

[0174] Create DataTabledt and List <t>list.

[0175] Read the Excel file, extract the header fields, and deserialize each row of data into an entity object.

[0176] Iterate through all worksheets, read data starting from the third row, create entity objects and fill in attribute values.

[0177] Use LINQ's Where method to filter out empty strings and null values.

[0178] Data is saved to the database:

[0179] Define SQL commands, create database connections and SQL commands, and add parameters.

[0180] Traverse the filtered data and insert into the database.

[0181] Step S2: Generate a fiber link table

[0182] Build the table structure:

[0183] Cables table: contains CableID and CableModel.

[0184] Connections table: contains CableID, PortType, PortNumber, and DeviceID.

[0185] Devices table: contains DeviceID, DeviceName, and DeviceType.

[0186] SQL query:

[0187] Select the cable number and model from the Cables table.

[0188] Connect the Cables table through the Connections table to obtain port information.

[0189] Connect the Connections table through the Devices table to obtain the device name.

[0190] Use INNER JOIN and LEFT JOIN to find different devices connected to the same fiber optic cable.

[0191] Use the GROUP BY and HAVING clauses to ensure that the same fiber optic cable is connected to multiple different devices.

[0192] Step S3: Label printing

[0193] Create a DrawingVisual object:

[0194] Set text format, calculate text position, and draw text.

[0195] Create a VisualBrush and set the stretch mode.

[0196] Set the print format:

[0197] Create an XPS print document writer, set the PrintTicket, get the PrintTicket, set the print format, and apply the PrintTicket.

[0198] Print flag labels:

[0199] Create a VisualBrush for the flag data and use the Print method.

[0200] Specific Examples

[0201] Assume that the Excel file contains the following data:

[0202] CableID CableModel PortType PortNumber DeviceID 001 Multimode SC 1 101 001 Multimode SC 2 102 002 Singlemode LC 3 103 002 Singlemode LC 4 104

[0203] Data import: Import the above data into the database.

[0204] Generate a fiber link table: Query the database and find that CableID 001 is connected to two devices DeviceID 101 and DeviceID 102.

[0205] Print Labels: Create a flag label for CableID 001 showing Multimode-SC-1,2.

[0206] According to the method and system for generating an optical fiber link table based on an optical fiber connection table designed by the present invention, the workload of manual input is reduced and the efficiency of data management is improved by automatically extracting data from an Excel document and converting it into an entity object; the accuracy and consistency of the data are ensured by using deserialization technology and LINQ query to filter empty data, and the data quality problems caused by human errors are reduced; the C#WPF framework and the VisualBrush class are used to automatically print labels, and the efficiency and accuracy of label management are improved; the data is quickly extracted and processed from the Excel document, and the optical fiber link table and printed labels are quickly generated, thereby improving the response speed to emergencies.

[0207] The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the various embodiments can be referenced to each other.

[0208] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features disclosed herein.< / t>

Claims

1. A method for generating an optical fiber link table based on an optical fiber connection table, characterized in that: The following steps are involved: S1. Based on the EPPlus library, use the asynchronous model of ASP.NET Core to obtain the uploaded Excel document, extract the fields in the header, save each row of data into an entity based on the deserialization technology, add it to the collection List and use the C# method to determine the empty data to remove the empty rows, further optimize the collection List data, use Entity Framework Core to perform asynchronous database operations, and save the data into the database; S2. According to the SQL statement for connecting tables in SQL server 2008R2, the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to the cabinet are obtained to match whether the same optical cable is connected to different equipment, and the optical fiber link table is formed; S3. According to the fiber link table generated in S2, use the VisualBrush class in the C#WPF framework to obtain the required flag data, use the PrintTicket method to set the print format, and print the label.

2. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 1, characterized in that: The EPPlus library uses the asynchronous model of ASP.NET Core to obtain the uploaded xcel document: Define a controller Controller, in the controller Controller, create an Action method named UploadExcel as the API endpoint. The UploadExcel method accepts a parameter named excelFile, the type of which is IFormFile. IFormFile is an interface provided by ASP.NET Core to represent uploaded files. Before processing the file, validate the received excel document, check if excelFile is null or if its Length property is 0, confirm that the file has been uploaded, and use StringComparison.OrdinalIgnoreCase to ignore the case of the file extension comparison; Copy the file contents to a memory stream and create a MemoryStream instance. This stream will be used to store the data copied from the uploaded file. Use the excelFile.CopyToAsync(stream) method to asynchronously copy the file contents to the MemoryStream, which is used to continue processing other requests while waiting for the file upload to complete. Use the await keyword to wait for the CopyToAsync method to complete so that the next step will not be performed until the file is completely copied to the memory stream; Create an ExcelPackage instance and pass the MemoryStream as a parameter to the ExcelPackage instance to open the Excel document. Through the ExcelPackage instance, access the workbooks and worksheets in the Excel document and traverse the rows and columns in the worksheet.

3. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 2, characterized in that: Extract the fields in the table header and save each row of data into an entity based on the deserialization technology: Iterate through all worksheets in the Excel file; get the header row of each worksheet; set the column name of the DataTable according to the name of the worksheet, read data from the third row, and for each row of data, create a new instance of type T, one, iterate through all attributes of type T, and if the attribute has a DescriptionAttribute attribute, get its description, find the corresponding column from the DataTable according to the description and column name, and read the cell value. If the cell value is empty, try to use the value of the previous row, convert the cell value to the type of the attribute, and set it to the attributes of the one instance, and add the filled one instance to the list.

4. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 3, characterized in that: Add to the collection List and use C# to determine the empty data method to remove empty rows, further optimize the collection List data, use EntityFramework Core to perform asynchronous database operations, and save the data to the database as follows: Use LINQ's Where method to filter out empty strings and null values, check whether the string is empty or null through the string.IsNullOrEmpty(item) method, make sure that a reference to System.Data.SqlClient is included, then define the SQL command, create a database connection and SQL command, add parameters, traverse the filtered data, use Entity Framework Core for asynchronous database operations, and save the data to the database.

5. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 4, characterized in that: Use Entity Framework Core to perform asynchronous database operations and save data to the database, specifically: Create a class that inherits from DbContext as the entry point for interacting with the database. In this class, define the database collection DbSet to represent the tables in the database, configure the database connection string, define one or more entity classes, which will be mapped to the tables in the database. The properties of each entity class correspond to the columns in the database table. Use the Add method to add new entities to the DbSet of DbContext, and use the SaveChangesAsync method to asynchronously save the changes to the database. DbSet is a table in the database. Each DbSet property corresponds to a table in the database. The OnConfiguring method is used to configure the database connection string and other database-specific settings.

6. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 1, characterized in that: According to the SQL statement of connecting tables in SQL server 2008R2, the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to this cabinet are as follows: Build the table structure: Cables table: Cable ID, Cable Model; Connections table: Cable ID, port type, port number, and device ID of the device connected to the cabinet. Devices table: device number DeviceID, device name DeviceName, device type DeviceType.

7. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 6, characterized in that: Match whether the same optical cable connects different devices and combine them into a fiber link table, specifically: Select the cable number and model from the Cables table; join the Cables table through the Connections table to get the port information; join the Connections table through the Devices table to get the device name; use INNER JOIN and LEFT JOIN to find different devices connected to the same cable; Use the GROUP BY and HAVING clauses to ensure that the same fiber optic cable is connected to multiple different devices.

8. A method for generating an optical fiber link table based on an optical fiber connection table according to claim 1, characterized in that: Create a DrawingVisual object, set the text format, calculate the text position, draw the text, create a VisualBrush and set the stretch mode; Create an XPS print document writer, set PrintTicket, get PrintTicket, set print format, and apply PrintTicket; Creates a VisualBrush of flag data for printing.

9. A system for generating a fiber link table based on a fiber connection table, applicable to any one of the methods for generating a fiber link table based on a fiber connection table according to claims 1-8, characterized in that: include: Document acquisition module, used to acquire and read Excel documents; The field extraction module is used to extract fields from the Excel document and save each row of data into an entity based on the deserialization technology; Database module, used to store entities and table structures; A matching module is used to find different devices connected to the same optical cable, ensuring that the same optical cable can be connected to multiple different devices; The link combination module is used to build a fiber link table based on the optical cable number, optical cable model, optical distribution number / port type / port number, and the equipment connected to this cabinet; The printing module is used to set printing parameters and print labels based on flag label specifications.

10. A system for generating an optical fiber link table based on an optical fiber connection table according to claim 9, characterized in that: The Excel document includes .xls format and .xlsx format.

Images