CN106502976A - A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB - Google Patents

Abstract

The invention relates to a method for generating a job shop scheduling Gantt chart based on Excel and VB. It includes the following steps: 1) setting the Excel template file, including setting the corresponding worksheet, and the worksheet at least includes the corresponding header; 2) setting the VB template file, including setting the form and its related attributes according to the elements, Then add related controls on the form and set their properties, and finally compile the "Generate Gantt Chart.vbp"program; Set the completed excel template file to obtain workpiece processing information; 4) Click the "Generate Gantt Chart" command button to generate the job shop scheduling Gantt chart.

Description

A method of generating job shop scheduling Gantt chart based on Excel and VB

technical field

The invention relates to the technical field of production scheduling, in particular to a method for generating a job shop scheduling Gantt chart based on Excel and VB.

Background technique

There are basically two ways to draw a Gantt chart:

One is to use special Gantt chart drawing software, such as Microsoft project, Hanwen bar chart preparation system and other software. Although these special software are very convenient and intuitive to draw Gantt charts, especially the Gantt charts drawn by Microsoft project for project schedule management can visually display project progress, but in the field of job shop scheduling, due to the huge amount of data involved , the problems encountered are complex and changeable, resulting in the existence of these special software in the use of flexibility and poor operability, poor adaptability, and bring a lot of inconvenience to users.

The other is to use programming software, such as VC++, VB and other software, to call the database through programming, and then pop up the generated Gantt chart in the form of a dialog box. Although this method can quickly process a large amount of data and has strong adaptability, once the Gantt chart is generated successfully, it cannot be directly modified, and the degree of visualization is not high.

Contents of the invention

The purpose of the present invention is to provide a method for generating a job shop scheduling Gantt chart based on Excel and VB. The method is simple, fast, easy to modify, and has a high degree of visualization. Its concrete method comprises the following steps:

1) Set the Excel template file, including setting the corresponding worksheet, which at least includes the corresponding header;

2) Set up the VB template file, including setting the form and its related attributes according to the above elements, then adding related controls on the form and setting their attributes, and finally compiling the "Draw Gantt Chart.vbp" program;

3) By loading the "Draw Gantt Chart.vbp" program, click the "Import Data" command button, call the excel template file that has been set, and obtain the workpiece information;

4) Click the "Generate Gantt Chart" command button to generate the job shop scheduling Gantt chart.

The further design of the method for generating a Gantt chart for job shop scheduling based on Excel and VB is that the worksheets are two main and auxiliary worksheets, and the label names are respectively set to "job box, Gantt chart".

The further design of the method for generating a job shop scheduling Gantt chart based on Excel and VB is to at least set in the main worksheet: "workpiece number, number of procedures, procedure number, arrival time, optional machine set, processing Time, Cmax, start time, end time, selected machine" header.

The further design of the method for generating a job shop scheduling Gantt chart based on Excel and VB is that the form is added with 3 command buttons, 2 label controls, and 2 text box controls.

The further design of the method for generating a job shop scheduling Gantt chart based on Excel and VB is that the Gantt chart is composed of plane coordinates and rectangular blocks, the length of a rectangular block represents the processing time of a process, and the left end of the rectangular block Indicates the start time, and the right end indicates the completion time.

The further design of the method of generating a job shop scheduling Gantt chart based on Excel and VB is that the plane coordinate abscissa represents processing time, and its scale line and bottom line are the row heights of the cells in the excel worksheet, The column width and border line are generated by corresponding settings. The scale is selected according to the maximum completion time Cmax, and then generated by merging and centering the corresponding cells and entering the corresponding scale value.

The further design of the method for generating a job shop scheduling Gantt chart based on Excel and VB is that the plane coordinate ordinate represents the processing machine code, and its scale line and bottom line are the row heights of the cells in the excel worksheet , column width, and border line are set accordingly. The scale is selected according to the number of machines, and then generated by merging and centering the corresponding cells and entering the corresponding machine code.

The further design of the method for generating a job shop scheduling Gantt chart based on Excel and VB is that the rectangular block is determined by determining the target area of the worksheet cell, then performing cell merging, centering horizontally, and centering vertically, and finally The cell in the target area is generated by adding a frame around it, filling it with the corresponding color, and writing the process information. The process of the same workpiece is filled with the same color.

Compared with existing methods, a method of generating a job shop scheduling Gantt chart based on Excel and VB of the present invention has the following beneficial effects:

(1) The present invention can quickly process huge data, greatly improve work efficiency, and can be reused after forming templates. The technical effect is obvious, and it reflects a relatively large project use value;

(2) The present invention utilizes the good operability of excel. After the Gantt chart is generated, it can be directly adjusted and modified in excel without changing the low-level program, which has strong flexibility and high degree of visualization;

(3) The present invention can realize the co-associated storage of scheduling data and Gantt chart.

Description of drawings

Figure 1 is an example diagram of the design of the main and auxiliary worksheets in the Excel template file.

Fig. 2 is part of the input content of this embodiment.

Figure 3 is the VB form template.

Figure 4-1 to Figure 4-2 are the core codes for importing data.

Figure 5 is the form after importing data successfully.

Figure 6-1 to Figure 6-3 are the core codes for generating the Gantt chart.

FIG. 7 is a Gantt chart of the scheduling result of this embodiment.

The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

1. Set the excel template file

1.1 Create a new workbook

In the newly created workbook, create the main worksheet with the worksheet label name "Workbox" and the auxiliary worksheet with the worksheet label name "Gantt Chart". The main worksheet should contain a header to store workpiece processing information. For example: "Job number, process number, process number, arrival time, optional machine set, processing time, Cmax, start time, end time, selected machine" header. Please refer to Figure 1.

1.2 Input content of each cell

Taking 20 workpieces, 10 processes for each workpiece, and 10 processing machines as an example, please refer to Figure 2 for some input contents of the corresponding cells in the above input area in the main worksheet.

2. Set VB template file

2.1 Create a new VB template file and set the form and its properties,

Create a new VB template file, and set form1.caption="Generate Gantt Chart" in the file.

2.2 Add controls and set related properties

First add two command buttons, whose caption properties are set to "Import Data" and "Gantt Chart" respectively; then add two label controls, whose caption properties are set to "JobNum" and "MachNum" respectively; finally add two texts Box controls, whose text properties are all set to " "; see Figure 3.

2.3 Write the program "Generate Gantt Chart.vbp"

Write the "Generate Gantt Chart.vbp" program, and save it with the "Generate Gantt Chart.vbp" program after writing and debugging successfully.

3. Load program, import data, get workpiece information

Load the "Generate Gantt Chart.vbp" program, click the import data command button, select the excel template file set in step 1, and obtain the workpiece information. The core code of the program is shown in Figure 4-1~4-2, and it is shown in Figure 5 after the data is successfully imported.

4. Generating a Gantt Chart

First generate plane coordinates, the specific steps are as follows:

Step1. Let the value of cell G2 of the worksheet named "Workbox" in excel be the maximum completion time Cmax;

Step2. Set the column width of the first column of the "Gantt Chart" worksheet to 3, and then select the column width of the specified area of the worksheet (from the second column to the Cmax+1 column) to be 100/Cmax;

Step3. Set the row height of the second row, the third row, the MachNum*2+5 row and the MachNum*2+6 row of the worksheet to 5;

Step4. Construct the abscissa: select the specified area of the worksheet (all cells from the second column of MachNum*2+6 row to the second column of MachNum*2+6 row and Cmax+1 column), and set the line type of the lower frame line to Solid line; set the scale of the abscissa (including the scale value and the minimum scale unit line), for example: because the Cmax of this example is relatively large, set the scale value to 50 and the minimum scale unit to 10, when the line MachNum*2+6 When the number of columns (assumed to be the i-th column) is an integer multiple of 10, set the right frame line of the i+1th column to be a solid line, when the number of columns of the MachNum*2+5th row (assumed to be the j-th column) is When it is an integer multiple of 50, set the right frame line of column j+1 to be a solid line, and the value of the cell corresponding to row j+1 of row MachNum*2+7 is j, and then set the first 23 cells adjacent to the cell cells and the last 26 cells are merged and centered;

Step5. Construct the vertical coordinate: Select the specified area of the worksheet (all cells from the first column of the fourth row to the first column of the MachNum*2+6 row), set the line type of the right frame line to a solid line; set the vertical The scale of the coordinates, the ordinate represents the machine code of the workpiece processing, enter M1 in the cell of the fifth row and the first column, which represents machine 1, and then based on this cell, every other cell in the first column Input M2, M3... until M&MachNum.

Then generate a rectangular block, the specific steps are as follows:

Step1. Put each process of each workpiece into the corresponding cell according to the selected machine code and start time, for example: (1, 1), representing the first process of the first workpiece, its start time is 9 , the selected machine number is 9, then its location is in the cell corresponding to row 9*2+3 and column 9+2 of the worksheet;

Step2. According to the start time and end time of the process, determine the time area of each process, then merge and center the cells in this area, and set the surrounding border lines as solid lines;

Step3. Fill the cell area determined in Step2 with color, and all processes of the same workpiece are filled with the same color.

Finally, add the end marking line and mark the maximum completion time. The specific steps are as follows:

Step1. Select the specified area of the worksheet (all the cells in the fourth row, column Cmax+1 to row MachNum*2+6, column Cmax+1), and set the line type of the right frame line to solid line;

Step2. In the fourth row, enter the maximum completion time Cmax of the instance in the cell of column Cmax+1;

Step3. Select the (Cmax-50) to (Cmax+1) columns of the fourth row, and then merge and center.

The core code of the program is shown in Figure 6-1~6-3, and the Gantt chart is generated as shown in Figure 7.

Claims (8)

1. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB, it is characterised in that comprise the steps：

1）Excel template file is set, including arranging corresponding worksheet, in the worksheet, includes at least corresponding gauge outfit；

2）VB template files are set, and including forms and its association attributes being arranged according to the key element, then add phase on forms Close control and its attribute is set, finally establishment " generating Gantt chart .vbp " program；

3) by loading " generate Gantt chart .vbp " program, " importing data command " button is clicked on, transfers and be provided with Excel template files, obtain workpiece information；

4）" generation Gantt chart " order button is clicked on, the solving job shop scheduling problem Gantt chart is generated.

2. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 1, its feature It is the worksheet for main and auxiliary 2 worksheets, tag name is respectively set to " workpiece box, Gantt chart ".

3. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 2, its feature Be in the main worksheet at least provided with：" workpiece number, process number, operation number, arrival time, optional machine collection, during processing Between, Cmax, the time started, the end time, the machine of selection " gauge outfit.

4. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 1, its feature It is that the forms add 3 order buttons, 2 label controls, 2 Inputs.

5. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 1, its feature It is that the Gantt chart is made up of plane coordinates and rectangular block, the length of a rectangular block represents the process time of a procedure, The left end of rectangular block represents that on-stream time, right-hand member represent completion date.

6. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 5, its feature Be the plane coordinates abscissa representative is process time, and its graduation mark and bottom line are to cell in excel worksheets Row high, col width, frame line are arranged accordingly and are generated；Rule is selected according to Maximal Makespan Cmax, Then generated by respective cells being merged between two parties and being input into corresponding scale value.

7. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 5, its feature Be the plane coordinates ordinate representative is processing machine code name, and its graduation mark and bottom line are to single in excel worksheets The row of first lattice is high, and col width, frame line are arranged accordingly and generated；Rule is selected according to machine quantity, then Generated by respective cells being merged between two parties and being input into corresponding machine code name.

8. a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB according to claim 5, its feature It is that the rectangular block is by determining worksheet cell target area, then carries out cell span, horizontal center, vertically Target area cell is finally added surrounding wire by centering operations, and filling respective color simultaneously writes process information and generate, The operation of same workpiece identical color filling.