CN102722616B - Method for drawing exploration point plan based on Excel - Google Patents
Method for drawing exploration point plan based on Excel Download PDFInfo
- Publication number
- CN102722616B CN102722616B CN201210180077.2A CN201210180077A CN102722616B CN 102722616 B CN102722616 B CN 102722616B CN 201210180077 A CN201210180077 A CN 201210180077A CN 102722616 B CN102722616 B CN 102722616B
- Authority
- CN
- China
- Prior art keywords
- hole
- cad
- worksheet
- coordinate
- exploratory
- 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.)
- Active
Links
Landscapes
- Processing Or Creating Images (AREA)
Abstract
The invention relates to a method for drawing an exploration hole plan based on Excel. The method comprises the following steps: 1) setting an Exel template file, wherein the Exel template file comprises a corresponding worksheet, and the worksheet at least comprises a header, a column number acquisition area, a CAD batch command generation area for expressing picture blocks and characters of different exploration holes, a hole seat mark input area and an element input area of the exploration holes; 2) setting a CAD template file, including setting a corresponding layer according to the elements, defining the image blocks and the image block names, and compiling a small program of ' numbering and coordinate-taking ' dvb '; 3) and (3) obtaining a coordinate by loading a numbered dvb small program, generating a corresponding Excel table, obtaining the coordinate of the exploration point so as to obtain the working table, starting the CAD batch command, and drawing the plan of the exploration point. The advantages are that: the method is simple, easy to operate, flexible and changeable, strong in adaptability, convenient for batch adjustment and modification, and large in data capacity, so that the time for generating the exploration point plane layout diagram in the exploration engineering is greatly shortened, and the working efficiency is improved.
Description
Technical field
The present invention relates to geotechnical engineering investigation, relate in particular to a kind of method of drawing exploratory spot planimetric map based on Excel.
Background technology
In the geotechnical engineering investigation course of work, draw " exploratory spot floor plan " by exploratory spot classification, substantially have at present two kinds of ways:
A kind of is to use special-purpose prospecting software.Although the geotechnical engineering investigation software of specialty has obtained more general application in geotechnical engineering investigation work, but in geotechnical engineering investigation, the poor property of engineering specifications is different large, the problem running into is complicated and changeable, these business software data processings and computation process are opaque, operability is also poor simultaneously, can not conversion according to circumstances carry out adaptation operation, cause these softwares in use exist very flexible, adaptability not by force, sometimes capacity can not satisfy the demand, buyer to program the defect without weight update, to user, brought many inconvenience.
Another kind is to adopt CAD software, in CAD, first the corresponding boring of different classes of exploratory spot is made respectively to corresponding legend, again in legend corresponding to the correspondence position of exploratory spot planimetric map " sticky note ", so repeatedly " copy ", " sticky note ", until whole exploratory spot planimetric map completes.Although this method also can solve the problem of drawing exploratory spot planimetric map, inefficiency, can only be for the few situation of exploratory spot.But particularly individual event prospecting project is increasing along with production scale, prospecting project is with regard to hundreds of several thousand exploratory spots even sometimes, and so work efficiency can not meet the requirement that existing fast pace is produced.
Except above-mentioned deficiency, also there is following total weak point in above two kinds of methods: the first can not guarantee that " exploratory spot classification table " is consistent with the corresponding boring of each exploratory spot classification height in " exploratory spot floor plan "; It two is that exploration program change time can not be carried out batch modification.
Solve above-mentioned deficiency, technician is certainly best by programming targetedly, but in fact, engineering technical personnel's majority in this area is ignorant of programming, and professional programmer does not know much have less understanding to Geotechnical Engineering knowledge, often can not imagination and the demand of accurate understanding technician aspect programming.Here it is, and current professional software can not adapt to prospecting requirement of engineering, the reason that engineering technical personnel can not break away from all the time from more loaded down with trivial details drawing.
Summary of the invention
The object of the present invention is to provide a kind of method of drawing exploratory spot planimetric map based on Excel, it is fast succinct that the method is drawn exploratory spot floor plan, and consistent with each key element of corresponding exploratory spot classification table and corresponding title height.Its concrete grammar comprises the steps:
1) Exel template file is set, comprises
Corresponding worksheet is set, in described worksheet, at least comprises gauge outfit, row acquisition zone, in order to express the segment of different exploratory holes and the CAD of word, criticize coordinate input field, hole and the key element input field that order generates district, exploratory hole;
2) CAD template file is set, comprises
According to described key element, corresponding figure layer and the described segment of definition and segment name are set, and establishment " numbering is got coordinate .dvb " small routine;
3) by loading, number and get coordinate .dvb " small routine, generate corresponding Excel form, obtain the coordinate of exploratory spot, thereby obtain described worksheet, start described CAD and criticize order, draw out described exploratory spot planimetric map.
The further design of the method for described drafting exploratory hole planimetric map is, described key element at least comprises classification code name input field, hole and spare contents input field;
The further design of the method for described drafting exploratory hole planimetric map is, described worksheet is main and auxiliary 2 worksheets; In described main worksheet, at least arrange: input field and spare contents input field and " exploratory spot classification complete list " gauge outfit of the acquisition zone of described row number, described key element input field, described hole coordinate; Described hole classification code name and classification corresponding informance input field, hole are at least set in auxiliary worksheet.
The further design of the method for described drafting exploratory hole planimetric map is, in described main worksheet, row acquisition zone adopts Excel function automatically to obtain the dynamic column number of each row, for excel macro, calls.
The further design of the method for described drafting exploratory hole planimetric map is that the input of " classification " row corresponding informance in described main worksheet is from auxiliary worksheet, automatically to be searched and obtain by Excel formula by " hole classification code name ".
The further design of the method for described drafting exploratory hole planimetric map is, it is automatically to generate by excel macro order a collection of order that meets CAD order rule that described CAD criticizes order, and by whole selections and the CAD that is replicated in described worksheet, criticizes order generation district and automatically generate.
The present invention utilizes data computing function that Excel is powerful to make it the drawing function powerful with CAD to be combined, omitted the generation of CAD " SCR script file " and called link, method is simple, easy operating, flexible and changeable, strong adaptability, conveniently carry out adjustment in batches and modification, data capacity is large, once can 10000 above exploratory spots of plotting.
The present invention only needs the dvb small routine of simple excel macro order and CAD, just can greatly increase work efficiency.The time that makes to reconnoitre generation " exploratory spot floor plan " in engineering shortens greatly, and can guarantee the consistance of each exploratory spot correspondence key element expressed in " exploratory spot floor plan " that " exploratory spot classification table " that Excel makes and CAD make, accomplish rapidly and efficiently and error rate low.Reusable after formation template, technique effect is obvious, has embodied larger engineering use value.
Accompanying drawing explanation
Fig. 1 is the design example figure of the main worksheet in Excel template file.
Fig. 2 is the design example figure of the auxiliary worksheet in Excel template file
Fig. 3 is the schematic diagram that plotting has the grand design of classification segment.
Fig. 4 is the schematic diagram of the grand design of plotting segment word.
Fig. 5 is that plotting is without the schematic diagram of the grand design of classification segment.
Fig. 6 is grand shortcut setting operation interface.
Fig. 7 is the example of all kinds exploratory hole expressed in the present embodiment cad file and corresponding segment, segment name.
Fig. 8 is " numbering is got coordinate .dvb " small routine core code figure.
Fig. 9 is the sketch that exploratory hole distributes.
Figure 10 is " the exploratory spot classification complete list " generating.
Figure 11 is the different classes of exploratory spot example schematic of plotting.
Embodiment
Below in conjunction with drawings and Examples, the present invention is further illustrated.
. Excel template file is set
1.1 newly-built worksheets
In newly-built book, set up respectively the auxiliary worksheet that main worksheet and Sheet Tabs name that Sheet Tabs name is called " exhibition hole number, hole classification " are called " hole classification code name ".
Main worksheet comprises gauge outfit, row acquisition zone, in order to express the segment of different exploratory holes and the CAD of word, criticizes order generation district, and the coordinate input field, hole of exploratory hole
In main worksheet, be provided with row acquisition zone, CAD and criticize coordinate input field, hole and the various gauge outfit that order generates district, exploratory hole.The gauge outfit that gauge outfit comprises " exploratory hole classification complete list " and criticize the gauge outfit that order generates district corresponding to CAD, such as: the gauge outfit of " the segment class such as plotting hole sign ", " the word class such as plotting Kong Hao ", " plotting hole sign ", also have " the X coordinate " of coordinate input field, hole, the gauge outfit of " Y coordinate " corresponding to exploratory hole, be provided with in addition " segment ratio ", " character height ", " standby 1 " ... etc. gauge outfit.Refer to Fig. 1.
Wherein to criticize order be by generating in the grand CAD of being arranged in order to express a collection of CAD order of the corresponding segment of different exploratory holes and word to CAD.And row acquisition zone adopts Excel function automatically to obtain the dynamic column number of each row, for excel macro, call.
Corresponding informance input fields such as " hole classification code name ", " hole classification " of corresponding gauge outfit is set in auxiliary worksheet, refers to Fig. 2
1.2 each cell input contents
In main worksheet, the input content of corresponding unit lattice of above-mentioned input field and the description of use of this input content are shown in following table 1
each cell input content table of comparisons of table 1
In auxiliary worksheet, each cell input content is in Table 2.
each cell input content table of comparisons of table 2
1.3 general grand designs
Grand formation CAD for the CAD order of drawing exploratory hole planimetric map by Excel file criticizes order.According to the demand of drawing, specific design become following three kinds grand:
1) grand 1: in this Excel template file, by the expressed corresponding classification segment of different exploratory holes, carry out plotting hole sign, its macros program is shown in Fig. 3, and shortcut is " Ctrl+a ".
2) grand 2: in this Excel template file, the word classes such as plotting Kong Hao, are shown in Fig. 4, and shortcut is " Ctrl+b ".
3) grand 3: in this Excel template file, plotting hole sign is not sub-category, and all different exploratory holes all use a segment to express, and its macros program is shown in Fig. 5, and shortcut is " Ctrl+c ".Grand shortcut setting operation interface refers to as Fig. 6.
2. CAD template file is set
2.1 newly-built cad files are also set figure layer
Newly-built cad file arranges correlogram layer in this document, such as " hole sign " floor, " hole number " floor, " lead-in wire " floor, " picture frame " floor etc., and figure frame template is set in picture frame layer.
segment and corresponding segment name are set
What arrange is definitely consistent with worksheet 1,2 corresponding titles and code name for expressing segment and the corresponding segment name of dissimilar exploratory hole, classification, the title of each exploratory spot in " exploratory spot floor plan " that " the exploratory hole classification table " that guarantees that with this Excel makes made with CAD are consistent, in use procedure, also can revise flexibly as required or increase new inside segment, as long as guarantee segment content, segment name, exploratory hole classification code name, several persons of exploratory spot classification unanimously, wherein segment, segment name, hole class code name, hole classification are referring to Fig. 7.
write " numbering is got coordinate .dvb " small routine
this small routinecore code is as Fig. 8, after having write and debugged successfully, separately with " numbering is got coordinate .dvb " file save.
3. generate worksheet, start CAD and criticize order, draw out described exploratory spot planimetric map
3.1 layout, number and extract exploratory spot coordinate
The site plan generally providing according to designing unit of layouting of exploratory hole is arranged more convenient quick, but the dimensional units of the site plan that before designing unit must be provided and CAD template file (be for example with mm or take m as unit), scale factor and coordinate furnishing are consistent, and the cell D6 that the adjustment of coordinate is asked for an interview in table 1 is downward.By in described site plan copy CAD template file, next, in " hole sign " layer, by the buildings or structures feature of planning to build, according to geotechnical investigation code requirement, tentatively arrange exploratory spot again.Now can as the sign in hole, carry out arrangement of prospect hole with the segment of segment by name 7, after having arranged, should pass through through auditor's audit, form the sketch of a exploratory hole distribution, refer to Fig. 9.
Then load " numbering is got coordinate .dvb " small routine, the exploratory spot of the preliminary layout that audit is passed through, is numbered according to certain rules.The 1st exploratory spot write numbering " 1 " automatically, and the 2nd exploratory spot of point write numbering " 2 " automatically, and the 3rd exploratory spot of point write numbering " 3 " automatically ..., n exploratory spot of point write numbering " n " automatically.After numbering finishes, click the mouse right button or carriage return, " numbering is got coordinate .dvb " small routine can generate the Excel form that comprises the contents such as " numbering ", " X coordinate ", " Y coordinate " automatically.Thereby can obtain " X coordinate ", " Y coordinate " corresponding to different " numbering " exploratory holes.
generate exploratory spot classification complete list
open Excel template, comprise " numbering ", " X coordinate ", " Y coordinate " three column datas of above-mentioned automatic generation are copied to the corresponding input field of this template.Reproduction process adopts " PASTE SPECIAL " menu, and selects " numerical value " option in " PASTE SPECIAL ", can not change like this Excel template legacy data form.So far, complete " exploratory spot classification complete list ".
Exploratory spot classification complete list or other worksheets can directly be printed, and paste in Word document, CAD document or other document after also reproducible and carry out editing and composing." exploratory spot classification complete list " see for example Figure 10.
category plotting exploratory spot segment
1) in Excel template D2 cell, input segment class code name column number, for example exploratory hole classification code name is in the input " 11 " of K row.In D3 cell, input segment and insert ratio value, segment is set and inserts ratio.According to the classification of each exploratory hole of determining, in " hole classification code name " row, input the classification code name (K6 is downward) of corresponding aperture, for example input " 5 " represents general quiet inspecting hole.
2) press shortcut " Ctrl+a ", excel macro order generates a collection of order that meets CAD order rule automatically, and automatically all selects and copy.If it is not sub-category just by shortcut " Ctrl+c " to make to input segment
3) open " hole sign " layer in cad file, carry out " stickup " instruction in order line, CAD presses each exploratory spot classification automatically, plotting exploratory hole centered by each exploratory spot coordinate figure.
plotting exploratory spot numbering
1) input characters class code name column number in Excel template B2 cell, for example " 5 " are inputted at E row in exploratory spot hole number.In D4 cell, input character height value, arranges character height.
2) press shortcut " Ctrl+b ", excel macro order generates a collection of order that meets CAD order rule automatically, and automatically all selects and copy.
3) open " hole number " floor in cad file.In order line, carry out " stickup " instruction, CAD is automatically with each exploratory spot numbering of each exploratory spot coordinate figure plotting.
drawing is adjusted
1) in cad file, can choose in batches figure layer, the pel in " hole sign " floor or " hole number " floor for example, concrete grammar is: some right button → select fast (left button) → figure floor → value (floor " hole sign " or " hole number " is schemed in choosing) → definite.
2) use CAD " characteristic " function, can batch modification segment or the relevant settings in hole number.Close box, correct position is adjusted in mobile hole number in batches.
3) segment only allows to adjust size (because the position of point does not allow to change), the equal capable of regulating of size and position in hole number.
4) " the exploratory spot floor plan " generating is for example as Figure 11 (only intercepting is wherein local).
Claims (1)
1. based on Excel, draw a method for exploratory hole planimetric map, it is characterized in that comprising the steps:
1) Exel template file is set, comprise corresponding worksheet is set, in described worksheet, at least comprise gauge outfit, row acquisition zone, in order to express the segment of different exploratory holes and the CAD of word, criticize coordinate input field, hole and the key element input field that order generates district, exploratory hole;
Described worksheet is main and auxiliary 2 worksheets; In described main worksheet, at least arrange: input field and spare contents input field and " exploratory spot classification complete list " gauge outfit of the acquisition zone of described row number, described key element input field, described hole coordinate; Described key element at least comprises classification code name input field, hole and spare contents input field; Described hole classification code name and classification corresponding informance input field, hole are at least set in auxiliary worksheet;
The input of " classification " row corresponding informance in described main worksheet is from auxiliary worksheet, automatically to be searched and obtain by Excel formula by " hole classification code name ";
In described main worksheet, row acquisition zone adopts Excel function automatically to obtain the dynamic column number of each row, for excel macro, calls;
It is automatically to generate by excel macro order a collection of order that meets CAD order rule that described CAD criticizes order, and by whole selections and the CAD that is replicated in described worksheet, criticizes order generation district and automatically generate;
2) CAD template file is set, comprises newly-built cad file setting figure layer, the segment name of segment and correspondence is set and writes " numbering is got coordinate .dvb " small routine;
Described newly-built cad file setting figure layer comprise newly-built cad file, the correlogram floor that comprises " hole sign " floor, " hole number " floor, " lead-in wire " floor, " picture frame " floor is set in this document, with corresponding figure layer and definition described segment and segment name are set, and establishment " numbering is got coordinate .dvb " small routine according to described key element;
3) by loading " numbering is got coordinate .dvb " small routine, generate corresponding Excel form, obtain the coordinate of exploratory spot, thereby obtain described worksheet, start described CAD and criticize order, draw out described exploratory spot planimetric map.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN201210180077.2A CN102722616B (en) | 2012-06-04 | 2012-06-04 | Method for drawing exploration point plan based on Excel |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN201210180077.2A CN102722616B (en) | 2012-06-04 | 2012-06-04 | Method for drawing exploration point plan based on Excel |
Publications (2)
Publication Number | Publication Date |
---|---|
CN102722616A CN102722616A (en) | 2012-10-10 |
CN102722616B true CN102722616B (en) | 2014-07-16 |
Family
ID=46948376
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN201210180077.2A Active CN102722616B (en) | 2012-06-04 | 2012-06-04 | Method for drawing exploration point plan based on Excel |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN102722616B (en) |
Families Citing this family (10)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN102945305B (en) * | 2012-11-23 | 2015-05-13 | 南通太平洋海洋工程有限公司 | Method for manufacturing module electrical device support |
CN103020388B (en) * | 2012-12-28 | 2016-03-09 | 中国水利水电科学研究院 | A kind of river model test sectional board method for making |
CN105786584B (en) * | 2016-03-02 | 2019-03-19 | 武汉金思路科技发展有限公司 | A kind of adaptation Suresh Kumar BIM modeling software interface analytic method |
CN106021009A (en) * | 2016-05-25 | 2016-10-12 | 浪潮电子信息产业股份有限公司 | Method for automatically arranging test reports by using EXCEL self-contained macros |
CN107808406A (en) * | 2017-10-11 | 2018-03-16 | 中国煤炭地质总局二九勘探队 | Profile drilling column generation method |
CN108090256B (en) * | 2017-11-30 | 2021-06-25 | 江西洪都航空工业集团有限责任公司 | Lever diagram automatic drawing method based on CAD |
CN108549624B (en) * | 2018-04-18 | 2021-08-27 | 宁夏通信规划设计院(有限责任公司) | Automatic generation system and method for communication engineering design budget and description text |
CN110321542A (en) * | 2019-06-28 | 2019-10-11 | 中国水电顾问集团贵阳勘测设计研究院岩土工程有限公司 | Method for batch drawing of survey drawings by using excel editing CAD (computer-aided design) commands |
CN111159862B (en) * | 2019-12-17 | 2023-09-08 | 核工业二四三大队 | Electronic design and programming method for uranium mine drilling design instruction book |
CN112182684A (en) * | 2020-08-31 | 2021-01-05 | 大明重工有限公司 | Batch head parameterized process card generation method and system |
Citations (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN1936899A (en) * | 2006-10-10 | 2007-03-28 | 武汉地大坤迪科技有限公司 | Geological inspection column-like image drawing and compositing using CIS image document |
Family Cites Families (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20090273598A1 (en) * | 2008-05-01 | 2009-11-05 | M.E.P. Cad, Inc. | Methods and apparatuses for automatically converting objects in CAD drawing from two-dimensions to three-dimensions |
-
2012
- 2012-06-04 CN CN201210180077.2A patent/CN102722616B/en active Active
Patent Citations (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN1936899A (en) * | 2006-10-10 | 2007-03-28 | 武汉地大坤迪科技有限公司 | Geological inspection column-like image drawing and compositing using CIS image document |
Non-Patent Citations (6)
Title |
---|
Excel与CAD结合在地质制图工作中的应用;薛卫冲;《地质学刊》;20110930;第35卷(第3期);275-279 * |
利用Excel VBA实现在AutoCAD中展绘坐标点;彭四清;《测绘与空间地理信息》;20080831;第31卷(第4期);第1页左栏第2段至第3页右栏第2段,图1,图2 * |
应用Excel编制AutoCAD脚本文件实现批量展点绘图;邱章云;《矿山测量》;20050930(第3期);第1页左栏第2段至第3页右栏第1段,图1至图3 * |
彭四清.利用Excel VBA实现在AutoCAD中展绘坐标点.《测绘与空间地理信息》.2008,第31卷(第4期), |
薛卫冲.Excel与CAD结合在地质制图工作中的应用.《地质学刊》.2011,第35卷(第3期),275-279. |
邱章云.应用Excel编制AutoCAD脚本文件实现批量展点绘图.《矿山测量》.2005,(第3期), |
Also Published As
Publication number | Publication date |
---|---|
CN102722616A (en) | 2012-10-10 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CN102722616B (en) | Method for drawing exploration point plan based on Excel | |
CN102509511B (en) | Rapid map making system based on GIS (Geographic Information System) data | |
CN111475163A (en) | Method, device and equipment for generating code file of view template and storage medium | |
US8095871B2 (en) | System and method for GUI supported specifications for automating form field extraction with database mapping | |
CN106469140A (en) | A kind of report generating system and its method | |
CN104360992A (en) | WEB report design system built based on EXCEL platform and implementation method | |
CN103136317A (en) | Implement method of on-line examination and approval informatization of engineering contracts in engineering management system | |
US20130290837A1 (en) | Method and Apparatus for Correcting Document Formatting Based on Source Document | |
CN102609252A (en) | Intelligent configuration method for logic menu resources on basis of measurement instruments | |
CN107862112A (en) | Element material programming count and method for drafting suitable for AutoCAD floor plans | |
CN110968294A (en) | Business domain model establishing system and method | |
CN106325833A (en) | Code generation method for enterprise information management | |
CN104298705A (en) | Converting method of relational data and unstructured data | |
CN103049260A (en) | Method and system for displaying user interface of software at client | |
AU2021263673B2 (en) | A system and method for dynamically defining digital forms | |
CN117077650B (en) | Document generation system, method, equipment and medium based on dynamic template configuration | |
KR101022716B1 (en) | System for generating automatically electronic document and method thereof | |
CN104182427A (en) | Electronic document production system and electronic document production method thereof | |
CN105718434A (en) | Natural language formula editing method and system | |
CN101303663A (en) | Simulation test method for digital set-top box software development | |
CN1318954C (en) | Method for imjplementing report forms showing | |
CN112597750A (en) | Method, editor and medium for online designing dynamic detection report template | |
CN116795347B (en) | Map-oriented automatic comprehensive graphical programming system and device | |
CN107807911A (en) | Vector data edit methods, apparatus and system | |
CN114692280B (en) | Dynamo-based bridge family type batch creation method and Dynamo-based bridge family type batch creation system |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
C06 | Publication | ||
PB01 | Publication | ||
C10 | Entry into substantive examination | ||
SE01 | Entry into force of request for substantive examination | ||
C14 | Grant of patent or utility model | ||
GR01 | Patent grant | ||
CP03 | Change of name, title or address |
Address after: 210031 building 4, Yangzi science and technology innovation headquarters base, No.5, Pudong North Road, Jiangbei new district, Nanjing City, Jiangsu Province Patentee after: China Chemical Civil Engineering Co.,Ltd. Address before: No. 357, Dachang Yangxin Road, Liuhe District, Nanjing, Jiangsu 210044 Patentee before: CHEMICAL INDUSTRY GEOTECHNICAL ENGINEERING Co.,Ltd. |
|
CP03 | Change of name, title or address |