CN106502976A - A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB - Google Patents
A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB Download PDFInfo
- Publication number
- CN106502976A CN106502976A CN201610950075.5A CN201610950075A CN106502976A CN 106502976 A CN106502976 A CN 106502976A CN 201610950075 A CN201610950075 A CN 201610950075A CN 106502976 A CN106502976 A CN 106502976A
- Authority
- CN
- China
- Prior art keywords
- gantt chart
- excel
- scheduling problem
- job shop
- shop scheduling
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/174—Form filling; Merging
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/06—Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling
- G06Q10/063—Operations research, analysis or management
- G06Q10/0631—Resource planning, allocation, distributing or scheduling for enterprises or organisations
Abstract
The present invention relates to a kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB.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 related control on forms and its attribute is set, finally establishment " generating Gantt chart .vbp " program；3）By loading " generating Gantt chart .vbp " program, " importing data " order button is clicked on, the excel template files being provided with are transferred, work pieces process information is obtained；4）" generation Gantt chart " order button is clicked on, the solving job shop scheduling problem Gantt chart is generated.
Description
Technical field
The present invention relates to production scheduling technical field, more particularly to a kind of based on Excel and VB generation solving job shop scheduling problems
The method of Gantt chart.
Background technology
Gantt chart is drawn, there are substantially two kinds of ways at present：
One kind is soft using special Gantt chart software for drawing, such as Microsoft project, writing brush text gunter drawing workout system etc.
Part.Although these special-purpose softwares draw Gantt chart easily, very intuitively, especially Microsoft project are in the project of being directed to
The Gantt chart that draws during progress control can intuitively show project process, but in solving job shop scheduling problem field, due to being related to
Huge data volume, the problem for running into are complicated and changeable, and the software for causing these special has flexibility and operation in use
Property is very poor, and adaptability is not strong, brings many inconvenience to user.
Another kind is to adopt programming software, such as VC++, the software such as VB, calls database by programming, then with dialogue
The form of frame ejects the Gantt chart for generating.Although this method can quickly process substantial amounts of data, adaptability is also stronger,
It is that Gantt chart is once generated successfully, it is impossible to directly change, visualization is not high.
Content of the invention
It is an object of the invention to provide a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB, should
Method is succinctly quick, it is easy to change, and visualization is high.Its concrete grammar comprises 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 " drawing Gantt chart .vbp " program；
3) by loading " draw Gantt chart .vbp " program, " importing data " order 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.
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, described
Worksheet is that main and auxiliary 2 worksheets, tag name are respectively set to " workpiece box, Gantt chart ".
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, in institute
State in main worksheet at least provided with：" workpiece number, process number, operation number, arrival time, optional machine collection, process time, Cmax,
Time started, end time, the machine of selection " gauge outfit.
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, described
Forms add 3 order buttons, 2 label controls, 2 Inputs.
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, described
Gantt chart is made up of plane coordinates and rectangular block, and the length of a rectangular block represents the process time of a procedure, rectangular block
Left end represents that on-stream time, right-hand member represent completion date.
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, described
What plane coordinates abscissa was represented is process time, and its graduation mark and bottom line are, row high to the row of cell in excel worksheets
Width, frame line are arranged accordingly and are generated.Rule is selected according to Maximal Makespan Cmax, then by right
Respective cells merge placed in the middle and are input into corresponding scale value and generate.
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, described
What plane coordinates ordinate was represented is processing machine code name, and its graduation mark and bottom line are the rows to cell in excel worksheets
Height, col width, frame line are arranged accordingly and are generated.Rule is selected according to machine quantity, is then passed through to phase
Cell is answered to merge placed in the middle and be input into corresponding machine code name and generate.
A kind of method for generating solving job shop scheduling problem Gantt chart based on Excel and VB is further designed and is, described
Rectangular block is that horizontal center, vertical center are grasped by determining worksheet cell target area, then carrying out cell span
Make, target area cell is added surrounding wire finally, filling respective color simultaneously writes process information and generate, same
The operation of workpiece identical color filling.
Compared with the conventional method, a kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB of the present invention have with
Lower beneficial effect：
（1）The present invention can quickly process huge data, greatly improve operating efficiency, form reusable, skill after template
Art effect substantially, embodies larger project use value；
（2）Excellent operability of the present invention using excel, after Gantt chart is generated, without changing low layer program, just can be straight
It is connected in excel and is directly adjusted and changes, flexibility is strong, visualization is high；
（3）The present invention can realize the common associated storage for dispatching data and Gantt chart.
Description of the drawings
Fig. 1 is the design example figure of main and auxiliary worksheet in Excel template file.
Fig. 2 is the part input content of the present embodiment.
Fig. 3 is VB forms templates.
Fig. 4-1 to Fig. 4-2 is introduced into the core code of data.
Fig. 5 is introduced into the forms after data success.
Fig. 6-1 to Fig. 6-3 is the core code for generating Gantt chart.
Fig. 7 is the scheduling result Gantt chart of the present embodiment.
Below in conjunction with the accompanying drawings and embodiment the present invention is further illustrated.
1. excel template files are set
1.1 newly-built books
In newly-built book, main worksheet and the Sheet Tabs name of Sheet Tabs entitled " workpiece box " are set up respectively
The auxiliary worksheet of referred to as " Gantt chart ".Main worksheet should be used for storing work pieces process information comprising gauge outfit.For example：" workpiece number, work
Ordinal number, operation number, arrival time, optional machine collection, process time, Cmax, time started, end time, the machine of selection " table
Head.Shown in Figure 1.
1.2 each unit lattice input contents
With 20 workpiece, each 10 procedure of workpiece, as a example by 10 processing machines, the corresponding list of above-mentioned input area in main worksheet
The part input content of first lattice is shown in Figure 2.
2. VB template files are set
2.1 newly-built VB template files simultaneously set forms and its attribute,
Newly-built VB template files, arrange form1.caption=" generation Gantt chart " in this document.
2.2 interpolation controls simultaneously arrange association attributes
Add 2 order buttons first, its caption attribute is respectively set to " importing data ", " Gantt chart "；Then two are added
Individual label control, its caption attribute are respectively set to " JobNum ", " MachNum "；Finally add two Inputs,
Its text attribute is disposed as " "；Shown in Figure 3.
2.3 write " generating Gantt chart .vbp " program
" generating Gantt chart .vbp " program is write, is write after completing and debugging success, with " generating Gantt chart .vbp " program deposit.
3. loading procedure, imports data, obtains workpiece information
Loading " generating Gantt chart .vbp " program, clicks on and imports data command button, selects the excel moulds set in step 1
Plate file, obtains workpiece information.The core code of the program is as shown in Fig. 4-1 ~ 4-2, as shown in Figure 5 after importing data success.
4. Gantt chart is generated
Plane coordinates is firstly generated, which comprises the following steps that：
Step1. it is Maximal Makespan Cmax for the value of the worksheet G2 cells of " workpiece box " to make tag name in excel;
Step2. the first row col width for arranging " Gantt chart " worksheet is 3, then selectes the worksheet designated area（Secondary series is arrived
Cmax+1 is arranged）Col width be 100/Cmax；
Step3. the row for arranging the second row, the third line and the MachNum*2+5 rows and MachNum*2+6 rows of worksheet is high
For 5；
Step4. abscissa is built：Select sheet designated area（MachNum*2+6 rows secondary series is to MachNum*2+6 rows
All cells of Cmax+1 row）, the line style for arranging its lower wire is solid line；The rule of abscissa is set (including scale
Value and minimum scale unit line), for example：As the Cmax of the example is than larger, it is 50 to arrange scale value, and minimum scale unit is
10, when the columns of MachNum*2+6 rows（It is assumed to be the i-th row）For 10 integral multiple when, the right wire for arranging i+1 row is real
Line, when the columns of MachNum*2+5 rows（It is assumed to be jth row）For 50 integral multiple when, the right wire for arranging the row of jth+1 is real
Line, the cell numerical value of corresponding+1 row of MachNum*2+7 rows jth is j, then front 23 cells adjacent for the cell
Placed in the middle with rear 26 cell spans；
Step5. ordinate is built：Select sheet designated area（Fourth line first row is to MachNum*2+6 row first rows
All cells）, the line style for arranging its right wire is solid line；The scale of ordinate is set, and what ordinate was represented is work pieces process
Machine code name, be input into M1 in the cell of fifth line first row, represent machine 1, then based on the cell, the
M2 is input into according to this every a cell in one row, and M3 ... is until M＆MachNum.
Then rectangular block is generated, is comprised the following steps that：
Step1. every procedure of each workpiece is put into corresponding cell, example according to the machine code name and time started that select
Such as:(1, the first procedure of unit one 1), is represented, its time started is 9, and the machine number of selection is 9, then its institute
The position at place is just in the 9*2+3 rows of worksheet, the corresponding cell of 9+2 row；
Step2. according to the time started and end time of operation, determine the time zone per procedure, then by the region
Cell span is placed in the middle, and surrounding wire is set to solid line；
Step3. to the cell area filling color determined in Step2, all process steps identical color of same workpiece is filled out
Fill.
Finally, add end mark line, and mark Maximal Makespan, which comprises the following steps that：
Step1. select sheet designated area（Fourth line Cmax+1 arranges the institute of MachNum*2+6 rows Cmax+1 row
There is cell）, the line style for arranging its right wire is solid line；
Step2. in fourth line, Cmax+1 column unit lattice are input into example Maximal Makespan Cmax；
Step3. the of fourth line is selected（Cmax-50）Arrange（Cmax+1）Row, then merge placed in the middle.
The core code of the program has generated Gantt chart as shown in Figure 7 as shown in Fig. 6-1 ~ 6-3.
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.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610950075.5A CN106502976A (en) | 2016-11-03 | 2016-11-03 | A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610950075.5A CN106502976A (en) | 2016-11-03 | 2016-11-03 | A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN106502976A true CN106502976A (en) | 2017-03-15 |
Family
ID=58322157
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610950075.5A Pending CN106502976A (en) | 2016-11-03 | 2016-11-03 | A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106502976A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110941750A (en) * | 2019-11-28 | 2020-03-31 | 金蝶软件（中国）有限公司 | Data linkage method and related device |
| CN112346719A (en) * | 2020-10-16 | 2021-02-09 | 山东师范大学 | Framework construction method for realizing asynchronous speed animation interactive program in VBA |
| CN112837565A (en) * | 2021-01-20 | 2021-05-25 | 山东师范大学 | Multi-label teaching demonstration system and method for scatter diagram |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102981842A (en) * | 2012-11-08 | 2013-03-20 | 北京空间飞行器总体设计部 | Method of generating interactive Gantt chart |
| US20130086106A1 (en) * | 2011-10-03 | 2013-04-04 | Black Hills Ip Holdings, Llc | Systems, methods and user interfaces in a patent management system |
-
2016
- 2016-11-03 CN CN201610950075.5A patent/CN106502976A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130086106A1 (en) * | 2011-10-03 | 2013-04-04 | Black Hills Ip Holdings, Llc | Systems, methods and user interfaces in a patent management system |
| CN102981842A (en) * | 2012-11-08 | 2013-03-20 | 北京空间飞行器总体设计部 | Method of generating interactive Gantt chart |
Non-Patent Citations (2)
| Title |
|---|
| 冯世扣: "基于遗传退火算法的车间调度研究与应用", 《中国优秀硕士学位论文全文数据库工程科技II辑》 * |
| 郭锐: "大型在线设备管理中自动图表的设计", 《长春大学学报》 * |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110941750A (en) * | 2019-11-28 | 2020-03-31 | 金蝶软件（中国）有限公司 | Data linkage method and related device |
| CN110941750B (en) * | 2019-11-28 | 2023-07-07 | 金蝶软件（中国）有限公司 | Data linkage method and related device |
| CN112346719A (en) * | 2020-10-16 | 2021-02-09 | 山东师范大学 | Framework construction method for realizing asynchronous speed animation interactive program in VBA |
| CN112837565A (en) * | 2021-01-20 | 2021-05-25 | 山东师范大学 | Multi-label teaching demonstration system and method for scatter diagram |
| CN112837565B (en) * | 2021-01-20 | 2022-11-11 | 山东师范大学 | Multi-label teaching demonstration system and method for scatter diagram |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107256005B (en) | BIM technology-based steel bar numerical control machining method and equipment | |
| CN104599024A (en) | Generation system of welding process guide system for structural part | |
| CN106502976A (en) | A kind of method that solving job shop scheduling problem Gantt chart is generated based on Excel and VB | |
| CN113190146B (en) | Processing method, device, equipment and storage medium of process data | |
| CN108255489A (en) | Front-end interface code generating method, device, electronic equipment and storage medium | |
| CN103310042B (en) | For the method arranging three dimensional angular muscle | |
| CN109507954A (en) | Cnc machine tool utilizes the machining shape input unit and method of interactive program | |
| CN103257959A (en) | Method and device for generating spreadsheet report | |
| CN107862112A (en) | Element material programming count and method for drafting suitable for AutoCAD floor plans | |
| CN106843141B (en) | numerical controller | |
| CN110543303A (en) | Visual business platform | |
| CN113886362A (en) | Data storage system and storage method based on workflow engine and low-code platform | |
| CN105069222A (en) | Sheet metal process automatic generation system | |
| CN107992654A (en) | A kind of intelligence composition method | |
| CN105279269A (en) | SQL generating method and system for supporting table free association | |
| CN108038281A (en) | Shipborne equipment pedestal engineering drawing generating method | |
| CN101038583B (en) | Gold report generating and generating method thereof | |
| CN114595494A (en) | Auxiliary design tool kit based on AutoCAD software and construction method | |
| CN106127863A (en) | A kind of refrigerator foaming die series design system and method thereof | |
| CN102981842B (en) | Method of generating interactive Gantt chart | |
| CN103186365B (en) | A kind of method and system generating steering order according to character contour change | |
| CN103530436B (en) | Tooling layout drawing parameterization generation method based on AUTOCAD. NET API | |
| CN111914380A (en) | Part model determination method and device, computer equipment and storage medium | |
| CN100595769C (en) | Apparatus and method for generating dispensing pattern data for a paste dispenser | |
| CN111652489A (en) | BOM-driven intelligent manufacturing service task decomposition method and system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20170315 |
|
| WD01 | Invention patent application deemed withdrawn after publication |