Method for importing CAD histogram data into Excel for data verification
By importing CAD bar chart data into Excel and performing positional characteristic extraction and logical operations, the problems of computational complexity and error caused by the complexity of data in CAD are solved, achieving efficient and accurate data verification.
Patent Information
- Application Number
- CN202511098821.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-08-06
- Publication Date
- 2025-11-07
AI Technical Summary
In geophysical logging, CAD columnar data is numerous and cannot be correlated, making manual calculations complex, error-prone, and affecting work efficiency and accuracy.
Import CAD bar chart data into Excel, extract data based on location characteristics and perform logical operations, use Excel formulas to validate and filter data, and mark abnormal data.
It improves data processing efficiency, reduces human error, ensures data accuracy, and is suitable for batch data verification, especially for the rapid screening of thousands of data points.
Smart Images
Figure CN120911447A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the technical field of data processing, in particular to a CAD column chart data import Excel data verification method. BACKGROUND
[0002] In the geophysical well logging work, the well interpretation personnel often need to draw geological column chart, such as Figure 1 The data in the geological column chart is numerous, and the calculation formula is complicated, Figure 1 The numbers in the column chart shown in the figure are as many as 1000 data, and the manual calculation of data is extremely complicated. The logical relationship between each data is complex, but the data in CAD cannot be associated with each other, and the operation of thousands of times becomes extremely difficult. The operation result is often wrong, which brings great inconvenience to the reader, not only the work is tedious, time-consuming and laborious, the efficiency is extremely low, but also the input error is easy to occur, which makes it difficult to guarantee the accuracy of the data. At the same time, when manually processing these corresponding relationships, it is easy to make mistakes such as wrong filling and missing filling due to fatigue or negligence, which seriously affects the work quality and progress. Therefore, a CAD column chart data import Excel data verification method is urgently needed to solve the above problems. SUMMARY
[0003] The main purpose of the present application is to provide a CAD column chart data import Excel data verification method, which can effectively solve the problems of low work efficiency and large manual error.
[0004] To achieve the above purpose, the technical scheme adopted by the present application is: A CAD column chart data import Excel data verification method, the method comprising the following steps: S1, extracting CAD column chart data output as an Excel document S101, using CAD to open the column chart which needs to be processed, the CAD table contains data to be processed, selecting and clicking "Tools, Data Extraction, Create New Data Extraction" in the menu bar, and entering the data extraction continuous dialog box option; S102, data extraction-start (page 1, total 8 pages) Specifically, selecting "create new data extraction" in the "data extraction-start" dialog box, and then clicking "next"; S103, data extraction-define data source (page 2, total 8 pages) Specifically, enter the "data extraction - define data source" dialog box, select "select objects in the current graph", click the "select objects in the current graph" button, and then select the 64 data in the CAD graph (select the data from left to right), then click "ENTER", return to the data extraction - define data source (page 2 of 8), click "next"; S104, data extraction - select objects (page 3 of 8) Specifically, enter the "data extraction - select objects" dialog box, and then click "next"; S105, data extraction - select features (page 4 of 8) Specifically, enter the "data extraction - select features" dialog box, select the position X, position Y, and value according to the position characteristics of the number, and then click "next"; S106, data extraction - optimize data (page 5 of 8) Specifically, enter the "data extraction - optimize data" dialog box, select "column sorting options", and then set "first priority position Y in descending order; second priority position X in ascending order"; Then click "OK", select "next" in the returned "data extraction - optimize data" dialog box; S107, data extraction - select output (page 6 of 8) Specifically, enter the "data extraction - optimize data" dialog box, select "output data to external file", click "…", save the document as an Excel document and click "save", and then select "next"; S108, follow the prompts until data extraction - complete (page 8 of 8) Specifically, enter the "data extraction - complete" dialog box and click "finish" to output the Excel table data; The Excel table data is completely consistent with the data in the CAD table.
[0005] At this time, the data in the "E" column of the Excel table corresponds to the data on the CAD, specifically, the data in the "E" column of the Excel table is a group of four data respectively corresponding to the data in the CAD; S2, filter and edit the data in the Excel table Specifically, the data in the "E" column of the Excel table is one-to-one corresponding to the CAD data; S201, number the "A" column in the Excel table every 4 values as 1, 2, 3, 4, and cycle the numbering; S202, select the entire column "A" in the Excel table, select and click "Sort and Filter, Filter" in the menu bar, filter out the depth value, create a new "sheet2" in the Excel table, and modify the "sheet2" name to "Data Verification"; S203, filter out the data in "sheet1" and paste it into "Data Verification" table respectively, and get a data table in "Data Verification" table which is completely consistent with the position data of the CAD table and has the same position; Specifically, the first row of the "Data Verification" table is provided with "cumulative depth", "layer thickness", "length of mining", "mining rate", the "1" number in column "A" of the Excel table is filtered, the values in column D are synchronously copied and pasted into "cumulative depth" of the "Data Verification" table; the "2" number in column "A" of the Excel table is filtered, the values in column D are synchronously copied and pasted into "layer thickness" of the "Data Verification" table; the "3" number in column "A" of the Excel table is filtered, the values in column D are synchronously copied and pasted into "length of mining" of the "Data Verification" table; the "4" number in column "A" of the Excel table is filtered, the values in column D are synchronously copied and pasted into "mining rate" of the "Data Verification" table; S204, insert "operation result" column as "B" between "cumulative depth" and "layer thickness" columns, write formula "=A3-A2" in B3 cell, select the cell area in B3 cell where the calculation formula has been written, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows that need to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of corresponding contents; S205, insert "comparison judgment" column as "D" between "layer thickness" and "length of mining" columns, write formula "=B3-C3" in D3 cell, select the cell area in D3 cell where the calculation formula has been written, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows that need to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of corresponding contents; S206, insert "operation result" column as "F" between "length of mining" and "mining rate" columns, write formula "=E3,C3*100" in F3 cell, select the cell area in F3 cell where the calculation formula has been written, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows that need to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of corresponding contents; S207, insert "contrast judgment" column as "H" on the right side of "take rate" column, write formula = "F3-G3" in H3 cell, select the cell area in which the calculation formula has been written in H3 cell, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of corresponding contents.
[0006] S3, data output The calculation data output is marked, and all abnormal data greater than 1 in the contrast judgment column is error data.
[0007] Compared with the prior art, the present application has the following beneficial effects: 1. The method extracts data according to the position coordinate characteristics of the data in the CAD drawing, and imports the data into the Excel table to expand logical operation. The user can also extract data according to other characteristics such as color, font format, font size, rotation, inclination, etc. The operation is flexible and convenient, and the application scope is wide.
[0008] 2. The method has compact and simple steps, and does not require complex operation steps. The Excel table formula editing is simple and easy to learn, and the operation difficulty is not great. The table is made once and can be used repeatedly, which is convenient and flexible.
[0009] 3. This scheme is not only suitable for column chart data collection and data acceptance, but also suitable for coal report book CAD map data collection, industrial drawing, building drawing and other data collection and calculation, and has wide application.
[0010] 4. The method is especially suitable for batch data calculation, especially for the calculation of hundreds or thousands of data, which greatly reduces the trouble of manual operation one by one, quickly screens error data, and the calculation accuracy of thousands of data is 100%. Thousands of operations, one second result, high calculation efficiency, worth popularizing. BRIEF DESCRIPTION OF DRAWINGS
[0011] Figure 1 It is a schematic diagram of the prior art CAD table of the present application; Figure 2 It is a schematic diagram of the prior art CAD table of the present application; Figure 1 Figure 3 It is a schematic diagram of the present application writing error data in an embodiment; Figure 4 It is an operation guide of data extraction-start in the present application; Figure 5 The operation guide for data extraction-definition of data source in the application; Figure 6 The operation guide for data extraction-optimization of data in the application; Figure 7 The operation guide for data extraction-selection of output in the application; Figure 8 The corresponding diagram of the final filling results of the Excel table and the CAD table in the application; Figure 9 The schematic diagram of the cyclic numbering of the Excel table in the application; Figure 10 The schematic diagram of the screening of the Excel table in the application; Figure 11 The corresponding diagram of the final filling results of the data verification table and the CAD table in the application; Figure 12 The result schematic diagram of the data output in the application. DETAILED DESCRIPTION
[0012] In order to make the technical means, creative features, purposes and effects achieved by the application easy to understand, the application will be further described below in combination with specific embodiments.
[0013] A method for importing CAD column chart data into Excel for data verification, the method comprising the following steps: S1, extracting CAD column chart data output as an Excel document S101, using CAD to open a column chart that needs to be processed, the CAD table containing data to be processed, selecting and clicking "Tools, Data Extraction, Create New Data Extraction" in the menu bar, and entering the data extraction continuous dialog box options; S102, data extraction-start (page 1, total 8 pages) Specifically, enter the "Data Extraction-Start" dialog box and select "Create New Data Extraction", then click "Next". S103, data extraction-definition of data source (page 2, total 8 pages) Specifically, enter the "Data Extraction-Definition of Data Source" dialog box, select "Select Objects in Current Graphics", and then click the "Select Objects in Current Graphics" button. Select 64 data in the CAD chart (select data from left to right), and then click "ENTER". Return to "Data Extraction-Definition of Data Source" (page 2, total 8 pages), and click "Next". S104, data extraction-selection of objects (page 3, total 8 pages) Specifically, enter the "Data Extraction-Selection of Objects" dialog box, and then click "Next". S105, data extraction-select feature (page 4, 8 pages in total) Specifically, enter the "data extraction-select feature" dialog box, filter according to the position characteristics of the number, select: position X, position Y, value, and click "next"; S106, data extraction-optimize data (page 5, 8 pages in total) Specifically, enter the "data extraction-optimize data" dialog box, select "column sorting options", and then set "first priority position Y in descending order; second priority position X in ascending order"; Then click "OK", select "next" in the returned "data extraction-optimize data" dialog box; S107, data extraction-select output (page 6, 8 pages in total) Specifically, enter the "data extraction-optimize data" dialog box, select "output data to external file", click "…", save the document as an Excel document and click "save", then select "next"; S108, follow the prompt operation until data extraction-complete (page 8, 8 pages in total) Specifically, enter the "data extraction-complete" dialog box and click "finish" to output the Excel table data; The Excel table data is completely consistent with the data in the CAD.
[0014] At this time, because we choose 4 columns and 14 rows of data in CAD, the user can also determine it according to the actual situation. We continue to filter 4 columns and 14 rows of data here. The column data in the value of the Excel table data is an array of every 4 numbers, and the data in the value E column in the Excel table is the data on the CAD. The data in the value E column in the table is every 4 data corresponding to the data in the CAD.
[0015] S2, filter and edit the data in the Excel table Specifically, the column data in the value of the Excel table data is one-to-one corresponding to the CAD data; S201, number the "A" column in the Excel table with every 4 values as 1, 2, 3, 4, and cycle the numbering; S202, select the "A" column in the Excel table, select and click "sort and filter, filter" in the menu bar, filter out the depth value, and create a new "sheet2" in the Excel table, and modify the "sheet2" name to "data verification"; S203, the data in "sheet1" is filtered out and pasted into the "data verification" table respectively, and a data table completely consistent with the position data of the CAD table and having the same position is obtained in the "data verification" table; Specifically, the first row of the "data verification" table is provided with "cumulative depth", "layer thickness", "length of mining", "mining rate", the "1" number in the "A" column of the Excel table is filtered, the values in the D column are synchronously copied and pasted into the "cumulative depth" of the "data verification" table; the "2" number in the "A" column of the Excel table is filtered, the values in the D column are synchronously copied and pasted into the "layer thickness" of the "data verification" table; the "3" number in the "A" column of the Excel table is filtered, the values in the D column are synchronously copied and pasted into the "length of mining" of the "data verification" table; the "4" number in the "A" column of the Excel table is filtered, the values in the D column are synchronously copied and pasted into the "mining rate" of the "data verification" table; S204, inserting a "calculation result" column as "B" between the "cumulative depth" and "layer thickness" columns, writing a formula "=A3-A2" in the B3 cell, selecting the cell area in which the calculation formula has been written in the B3 cell, moving the mouse to the lower right corner of the area frame line, clicking the left mouse button when the mouse pointer becomes a black cross cursor, and dragging down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of the corresponding content; S205, inserting a "comparison judgment" column as "D" between the "layer thickness" and "length of mining" columns, writing a formula "=B3-C3" in the D3 cell, selecting the cell area in which the calculation formula has been written in the D3 cell, moving the mouse to the lower right corner of the area frame line, clicking the left mouse button when the mouse pointer becomes a black cross cursor, and dragging down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of the corresponding content; S206, inserting a "calculation result" column as "F" between the "length of mining" and "mining rate" columns, writing a formula "=E3,C3*100" in the F3 cell, selecting the cell area in which the calculation formula has been written in the F3 cell, moving the mouse to the lower right corner of the area frame line, clicking the left mouse button when the mouse pointer becomes a black cross cursor, and dragging down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of the corresponding content; S207, insert the "contrast judgment" column as "H" on the right side of the "take rate" column, write the formula "=F3-G3" in the H3 cell, select the cell area in which the formula has been written in the H3 cell, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of the corresponding content.
[0016] S3, data output After calculating and outputting the data, we can see the error data in columns D and H at a glance. Sometimes, the operation results in columns B, D, F and H have multiple decimal places. This is because the floating point number calculation of Excel operation has a difference of less than 0.02. Therefore, all numbers greater than 1 in the contrast judgment column are error data.
[0017] It should be noted that the specific installation method, circuit connection method and control method in the present application are all conventional designs, and the present application will not be described in detail.
Claims
1. A method for importing CAD histogram data into Excel for data validation, characterized in that, The method comprises the following steps: S1, extracting CAD column chart data output to an Excel document; S2, screening and editing data in the Excel table; S3, data output.
2. The method for importing CAD columnar chart data into Excel for data verification according to claim 1, characterized in that: The step S1 specifically comprises the following steps: S101, using CAD to open a column chart which needs data processing, the CAD table containing data to be processed, selecting and clicking "Tools, Data Extraction, Create New Data Extraction" in the menu bar to enter the data extraction continuous dialog box options; S102, data extraction-start; S103, data extraction-define data source; S104, data extraction-select objects; S105, data extraction-select properties; S106, data extraction-optimize data; S107, data extraction-select output; S108, following the prompt operation until data extraction-complete, that is, outputting Excel table data.
3. The method of claim 2, wherein the Excel table data output by the step S108 is completely consistent with the data in the CAD table.
4. The method for importing CAD columnar chart data into Excel for data verification according to claim 3, characterized in that: The step S2 specifically comprises the following steps: S201, numbering every 4 values in the "A" column in the Excel table as 1, 2, 3, and 4, and performing cyclic numbering; S202, selecting the entire "A" column in the Excel table, selecting and clicking "Sort & Filter, Filter" in the menu bar to filter out the depth value, creating a new "sheet2" in the Excel table, and modifying the name of "sheet2" as "data verification"; S203, filtering out the data in "sheet1" and pasting them into the "data verification" table respectively, obtaining a data table in the "data verification" table which is completely consistent with the position data of the CAD table and has the same position, and setting "cumulative depth", "layer thickness", "length of mining", and "mining rate" in the first row of the "data verification" table; S204, inserting an "operation result" column as "B" between the "cumulative depth" and "layer thickness" columns, writing a formula "=A3-A2" in the B3 cell, selecting the cell area in which the calculation formula has been written in the B3 cell, moving the mouse to the lower right corner of the area frame line, clicking the left mouse button when the mouse pointer becomes a black cross cursor, and dragging down until all the rows that need to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of corresponding contents; S205, inserting an "comparison judgment" column as "D" between the "layer thickness" and "length of mining" columns, writing a formula "=B3-C3" in the D3 cell, selecting the cell area in which the calculation formula has been written in the D3 cell, moving the mouse to the lower right corner of the area frame line, clicking the left mouse button when the mouse pointer becomes a black cross cursor, and dragging down until all the rows that need to be processed in the table, so that the formula is copied to all rows in the column, thereby realizing the judgment of each row of data to be processed in the table and the generation of corresponding contents; S206, in the "length of mining", "mining rate" column, insert the "operation result" column as "F", write the formula "=E3, C3*100" in F3 cell, select the cell area where the calculation formula has been written in F3 cell, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thus realizing the judgment of each row of data to be processed in the table and the generation of the corresponding content; S207, insert the "comparison judgment" column as "H" to the right of the "mining rate" column, write the formula "=F3-G3" in H3 cell, select the cell area where the calculation formula has been written in H3 cell, move the mouse to the lower right corner of the area frame, click the left mouse button when the mouse pointer becomes a black cross cursor, and drag down until all the rows to be processed in the table, so that the formula is copied to all rows in the column, thus realizing the judgment of each row of data to be processed in the table and the generation of the corresponding content.
5. The method for importing CAD columnar chart data into Excel for data verification according to claim 1, characterized in that: The specific implementation of step S203 is: selecting "1" number in column "A" of the Excel table, synchronously copying and pasting the values in column D to "cumulative depth" in the "data verification" table; selecting "2" number in column "A" of the Excel table, synchronously copying and pasting the values in column D to "layer thickness" in the "data verification" table; selecting "3" number in column "A" of the Excel table, synchronously copying and pasting the values in column D to "mining length" in the "data verification" table; selecting "4" number in column "A" of the Excel table, synchronously copying and pasting the values in column D to "mining rate" in the "data verification" table.
6. The method for importing CAD columnar chart data into Excel for data verification according to claim 1, characterized in that: The specific implementation of step S3 is to calculate the data output, and the abnormal data is marked. All abnormal data greater than 1 in the comparison judgment column is error data.