Excel batch import tree structure template design-call for netizens

Recently, when working on a project, there was a demand, Excel batch import tree structure function, how to understand the import tree structure? It is to reflect the parent-child relationship, if for the database, it can be expressed through pid. The batch imported data is stored in the database. When we query, we need to be able to query and display the tree structure.

If it's just a simple batch import, it sounds pretty simple, but with a tree structure, it's still a bit complicated

Let me first introduce the main fields that need to be stored in the database as the primary key id, plan name, plan content, plan time and pid in order to reflect the parent-child relationship. The id and pid will definitely not be filled in by users.

At present, there are several plans, but each has its own advantages and disadvantages:

Option 1: The template

The premise of the program:

1. On the basis of the original database fields (except id and pid), add a parent plan name field

2. The name of the plan cannot be repeated


1. First, we need to query the plan table according to the plan name to see if there is a name in the plan table that is the same as the current plan name, and if so, perform the update (or prompt the user that the plan name of the information already exists in the database), Otherwise perform the insert operation

2. Query the plan table according to the parent plan name, get the parent plan id (pid), and update the pid according to the parent plan name

Advantages: simple to implement


1. The user is required to manually enter the name of the parent plan, and the user may misuse and enter the wrong name

2. The name of the plan cannot be repeated

Scheme 2: The scheme template

The premise of the program:

Need to provide users with a detailed instruction, but users may not read it, or there may be situations that users cannot understand


1. A series of verifications to confirm that the imported data is ok

2. Insert the data into the database, generate the primary key id according to the plan number, get the primary key id and plan level to map to get the pid


Unlimited plan name


High demands on users

Solution 3: The template is directly set to a tree structure

The premise of the program:

Users can design a tree structure


Fill in the data in the table according to the tree structure


Simple user operation


The header design is not easy to control, because the user cannot control how many layers of tree structure the user can import

Option Four:

First select the parent node, all imported are the child nodes of the parent node

Option 4 can be understood as an optimization based on Option 1. The position of the parent node name in Excel does not require user input, but the user selects in the drop-down box. The data in the drop-down box comes from the database.

There are two situations at this time:

Case 1: Imported data is only a child node under a parent node

At this time, the drop-down box has only one piece of data or the user does not need to select directly by default, but there is a drawback. If there are few child nodes under a certain parent node, there is no difference between batch import and manual entry. Batch import is instead Seem overkill

Case 2: Imported data is child nodes under multiple parent nodes

At this time, there are multiple pieces of data in the drop-down box. At this time, there are two possible data sources

One, all nodes of the database

Second, front-end users have selected the parent node many times, but how to achieve it, there is currently no idea

Summary: The editor has little experience at present, so the realization idea is relatively narrow. I hope that the majority of netizens can provide better ideas! Than heart ❤