Keywords

1 Introduction

With a large number of new energy connected to the grid and the rapid growth of electricity demand in some areas, China’s power supply structure and supply and demand situation has changed, which puts forward a greater demand to solve the problem of system peak regulation and trans-provincial surplus and deficiency regulation. Therefore, it is urgent to further deepen inter-provincial spot transactions, optimize the allocation of resources in a wider range, discover the time and space value of electric energy, and realize the sharing of peak regulation resources and inter-provincial surplus and deficiency adjustment by market means.

At the same time, with the continuous deepening of the construction of the spot market, it is necessary to optimize the operation mechanism of the spot market according to the operation of the spot market, study the information interaction and data integration technology of the spot market to support the coordinated operation of multiple markets, and design the overall architecture, application architecture, functional architecture, and data integration platform of the spot market for the coordinated operation of multiple markets Hardware architecture and security protection system provide technical support for information interaction and data integration of multi market coordinated operation of the power spot market.

In order to support the construction of inter provincial electricity spot market, it is necessary to develop a visual system with perfect function and friendly interface on the basis of technology research and development.

The minimum configuration of front-end display hardware recommended by this system is CPU Intel i7-7700k, memory 8GB DDR4, disk 300 gb, graphics card GTX 1060, display standard resolution 1920 × 1080. The minimum configuration of database server is CPU Intel Xeon e5–4650, memory above 16 GB DDR4 and disk 1 TB.

The required software environment includes: Microsoft operating system, HTML5 standard browser, PHP development environment, Apache server environment, relational database.

2 System Design

The whole system is divided into front-end visualization system and background data management system, as shown in Fig. 1.

Fig. 1.
figure 1

System structure

Full size image

The front end of the system has two major functional modules, data overview and operation data. All modules are developed with HTML5 technology such as webgl and canvas, and the mainstream web framework is used. The data interface is provided by the background of PHP to obtain the data of MySQL database for visualization.

The system is divided into 11 pages: login page, transaction statistics, channel path, declaration status, declaration statistics, declaration details, channel available capacity, node transaction result, channel transaction result, path transaction result and personal center. The front end structure is shown in Fig. 2.

Fig. 2.
figure 2

Schematic diagram of front end structure

Full size image

The background of this system uses the mainstream Web back-end framework. The data interface is provided by the background of PHP to obtain the data of MySQL database for visual presentation.

The system is divided into 11 pages: login page, area management, channel management, applicant management, declaration data management, channel available capacity management, channel clearance management, channel clearance path management, personal center, log management.

Because of the huge and complex data of the power system, the data source of this system is provided by the management personnel in the background through the way of importing Excel files. The Excel file is uploaded and submitted by the provinces, and then imported by the backstage management personnel according to the unit of day.

The detailed structural design of this system is shown in Fig. 3.

Fig. 3.
figure 3

The overall structure design of the system

Full size image

3 The Specific Implementation

This system content is more, limited to the length, this paper after the Taiwan management system as an example, detailed introduction of the implementation of specific functions.

3.1 Import of Excel File

The background management part of this system is developed by PHP7.3. In PHP7, the best way to import Excel is to use third-party plug-ins. PHPSpreadsheet is one of the most powerful and easy to use plug-ins and is recommended for use.

PHPSpreadsheet is a library written in pure PHP that provides a set of classes that allow you to read and write different spreadsheet file formats. PHPSpreadsheet provides a rich API that allows you to set up many cell and document properties, including styles, images, dates, functions, etc. You can use it in any Excel spreadsheet you want. The document formats supported by PHPSpreadsheet are shown in Table 1.

Table 1. Formatting sections, subsections and subsubsections.
Full size table

To use phpSpreadsheet, your system requires a PHP version greater than 7.2. In your project, you can use Composer to install PHPSpreadsheet with the following command:

figure a

After reading the contents of the file, the next step is to verify that the table header, row, and column data are correct according to the template requirements. After all the data is correct, it can be written to the appropriate database.

In the operation of the database, due to the complex structure of the Excel file, there are a lot of data to be verified. There will be several operations on the database, and there will be correlation between each other. In order to maintain the consistency of data, we use the transaction mechanism of PDO to deal with this part of content.

The transaction mechanism of PDO supports four characteristics: atomicity, consistency, isolation, and persistence. In general terms, any operation performed within a transaction, even if performed in stages, is guaranteed to be applied to the database safely and without interference from other connections at commit time. Transactional operations can also be undone automatically on request (assuming they haven’t been committed), which makes it easier to handle errors in the script.

We can use Begin Transaction to enable transactions, Commit to commit changes, and roll Back to and from operations. Here’s the relevant demo code:

figure b

3.2 Editing of Imported Data

After the Excel data is imported into the data, it should be possible to edit and modify the data according to the user’s needs. Due to the large quantity, in order to facilitate editing and modification, we use the DataGrid in the EasyUI framework for processing.

EasyUI is a set of user interface plug-ins based on jQuery. Using easyUI can greatly simplify our code and save the time and scale of master web development. While EasyUI is simple, it is powerful.

The EasyUI front-end framework contains many commonly used front-end components, among which the DataGrid is distinctive. The EASYUI Data Grid (DataGrid) displays data in a tabular format and provides rich support for selecting, sorting, grouping, and editing data. Data grids are designed to reduce development time and do not require specific knowledge of the developer. It’s lightweight, but feature-rich. Its features include cell merging, multi-column headers, frozen columns and footers, and more. For back-end data editing on our system, the DataGrid is best suited.

We use JS to generate the static content of the data table, and then request the data interface through Ajax, and then render the data table after getting the data, so as to get the results we want.

This system focuses on the use of data table editor, you can achieve online editing table data. To edit the data, when initializing the DagGrid, you need to add an edit button in the last column using the formatting function, as follows:

figure c

After editing is complete, you can change the database through the event OnAfterEdit.

4 Conclusion

By connecting MySQL database with PHP and cooperating with DataGrid of Easy UI, we completed the design and implementation of the background management system of the operation and detection system of the electric spot market. The key content of this system is to use PHP to import Excel files, and verify the validity of data format and content, and then use the transaction mechanism of PDO to complete the data writing. Data is displayed through the data network function of Easy UI, and the editor is used to complete the data editing.

With the data, in the front end can be through the API interface, access to background data, and display in the front end.