CN112364615A - Automatic Excel connection end point alignment method and device - Google Patents

Automatic Excel connection end point alignment method and device Download PDF

Info

Publication number
CN112364615A
CN112364615A CN202011318931.8A CN202011318931A CN112364615A CN 112364615 A CN112364615 A CN 112364615A CN 202011318931 A CN202011318931 A CN 202011318931A CN 112364615 A CN112364615 A CN 112364615A
Authority
CN
China
Prior art keywords
connecting line
excel
coordinates
end point
point
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
Application number
CN202011318931.8A
Other languages
Chinese (zh)
Inventor
郑自然
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Shandong Normal University
Original Assignee
Shandong Normal University
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Shandong Normal University filed Critical Shandong Normal University
Priority to CN202011318931.8A priority Critical patent/CN112364615A/en
Publication of CN112364615A publication Critical patent/CN112364615A/en
Pending legal-status Critical Current

Links

Images

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/177—Editing, e.g. inserting or deleting of tables; using ruled lines
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/26—Visual data mining; Browsing structured data
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/284—Relational databases
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F3/00—Input arrangements for transferring data to be processed into a form capable of being handled by the computer; Output arrangements for transferring data from processing unit to output unit, e.g. interface arrangements
    • G06F3/01—Input arrangements or combined input and output arrangements for interaction between user and computer
    • G06F3/048—Interaction techniques based on graphical user interfaces [GUI]
    • G06F3/0484—Interaction techniques based on graphical user interfaces [GUI] for the control of specific functions or operations, e.g. selecting or manipulating an object, an image or a displayed text element, setting a parameter value or selecting a range
    • G06F3/04845—Interaction techniques based on graphical user interfaces [GUI] for the control of specific functions or operations, e.g. selecting or manipulating an object, an image or a displayed text element, setting a parameter value or selecting a range for image manipulation, e.g. dragging, rotation, expansion or change of colour

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • General Engineering & Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Physics & Mathematics (AREA)
  • General Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • General Health & Medical Sciences (AREA)
  • Health & Medical Sciences (AREA)
  • Computational Linguistics (AREA)
  • Audiology, Speech & Language Pathology (AREA)
  • Artificial Intelligence (AREA)
  • Human Computer Interaction (AREA)
  • Processing Or Creating Images (AREA)

Abstract

The invention provides an automatic Excel connection end point alignment method and device, which comprise the following steps: finding all connecting lines in a page by using the graphic object attribute in an embedded VBA of Excel to obtain a connecting line set; judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates; traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance; updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates; traversing the connection set, and aligning all the connections until the connection set is empty; according to the method and the device, the existing connecting line is redrawn by traversing the connecting line and the cells and judging the starting point and the end point, so that the automatic alignment of the connecting line is realized.

Description

Automatic Excel connection end point alignment method and device
Technical Field
The disclosure relates to the technical field of computers, and in particular relates to an automatic Excel connection end point alignment method and device.
Background
The statements in this section merely provide background information related to the present disclosure and may not necessarily constitute prior art.
Excel, as a common spreadsheet software, not only has a strong data computing capability based on cells, but also its convenient graphic insertion and editing functions are often applied to various fields of data visualization, such as representing different elements using basic graphics or representing direct relationships of cells using arrows and connecting lines.
The inventor of the present disclosure finds that although the connection function of Excel itself (the connection with an arrow is only different in end point setting) is already convenient, the alignment mode is only to align to a grid and to a graph in the current version; the network function can only automatically align to the four corners of a certain cell, and other positions cannot be automatically aligned; if Excel self-contained alignment is not used, when the cells are connected by using the connecting lines, the positions of the end points need to be finely adjusted to be aligned with the boundaries of the cells to be connected, and when the connecting lines are more, the operation step becomes very time-consuming, thereby reducing the work efficiency.
Disclosure of Invention
In order to solve the defects of the prior art, the disclosure provides an automatic Excel connection end point alignment method and device, redrawing of the existing connection line is realized by traversing the connection line and cells and judging a starting point and an end point, and automatic alignment of the connection line is realized.
In order to achieve the purpose, the following technical scheme is adopted in the disclosure:
the first aspect of the disclosure provides an automatic Excel connection endpoint alignment method.
An Excel connection endpoint automatic alignment method is executed in an embedded VBA of Excel and comprises the following steps:
finding all connecting lines in the page by using the graphic object attributes to obtain a connecting line set;
judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates;
traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance;
updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates;
and traversing the connection set, and aligning all the connections until the connection set is empty.
The second aspect of the disclosure provides an automatic alignment device for Excel connection endpoints.
An Excel connection end point automatic alignment device comprises a processor and a display module connected with the processor, wherein the processor is loaded with Excel software, the display module is used for performing interface display of Excel, and the processor executes the following automatic alignment method:
finding all connecting lines in a page by using the graphic object attribute in an embedded VBA of Excel to obtain a connecting line set;
judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates;
traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance;
updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates;
and traversing the connection set, and aligning all the connections until the connection set is empty.
Compared with the prior art, the beneficial effect of this disclosure is:
1. according to the method and the device, the existing connecting line is redrawn through traversing the connecting line and the cells and judging the starting point and the end point, so that the automatic alignment of the connecting line is realized.
2. The method and the device disclosed by the disclosure are a supplement to the Excel alignment mode, and allow a user to roughly drag and edit the connecting lines or arrows, and all the connecting lines can be automatically adjusted only by dragging the end points to the positions near the required cells and allowing the processing function of the system.
3. The method and the device can greatly improve the drawing efficiency, and particularly for the occasion of intensive connection lines based on table elements, such as tree structure display, complete drawing, visual drawing related to a network and the like, the whole process is automatically completed without the need of selecting the connection lines by a user.
4. The method and the device have the advantages of simple and easy-to-understand core codes and high expansibility, and can be used for expanding users with VBA language programming backgrounds and further expanding and modifying the positions of the alignment points of the cell boundaries, so that different requirements are met.
Advantages of additional aspects of the disclosure will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the disclosure.
Drawings
The accompanying drawings, which are included to provide a further understanding of the disclosure, illustrate embodiments of the disclosure and together with the description serve to explain the disclosure and are not to limit the disclosure.
Fig. 1 is a schematic flowchart of an Excel connection endpoint automatic alignment method according to embodiment 1 of the present disclosure.
Fig. 2 shows values of vFlip and hFlip in different situations provided in embodiment 1 of the present disclosure.
Fig. 3 is an alignment point of a connection line and a cell provided in embodiment 1 of the present disclosure.
Fig. 4 is a schematic connection diagram before case 1 alignment provided in embodiment 1 of the present disclosure.
Fig. 5 is a schematic connection diagram after case 1 alignment provided in embodiment 1 of the present disclosure.
Fig. 6 is a schematic connection diagram before case 2 alignment provided in embodiment 1 of the present disclosure.
Fig. 7 is a schematic connection diagram after case 2 alignment provided in embodiment 1 of the present disclosure.
Detailed Description
The present disclosure is further described with reference to the following drawings and examples.
It should be noted that the following detailed description is exemplary and is intended to provide further explanation of the disclosure. Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this disclosure belongs.
It is noted that the terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of example embodiments according to the present disclosure. As used herein, the singular forms "a", "an" and "the" are intended to include the plural forms as well, and it should be understood that when the terms "comprises" and/or "comprising" are used in this specification, they specify the presence of stated features, steps, operations, devices, components, and/or combinations thereof, unless the context clearly indicates otherwise.
The embodiments and features of the embodiments in the present disclosure may be combined with each other without conflict.
Example 1:
as shown in fig. 1, an embodiment 1 of the present disclosure provides an automatic alignment method for an Excel connection endpoint, which is executed in an embedded VBA of an Excel, and includes the following steps:
finding all connecting lines in the page by using the graphic object attributes to obtain a connecting line set;
judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates;
traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance;
updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates;
and traversing the connection set, and aligning all the connections until the connection set is empty.
Specifically, the method comprises the following steps:
s1: all links are found in the page using the graphic object attributes in the VBA code.
In which a traversal loop for all the link objects in the current Excel page is constructed.
The main code traversed is as follows:
Figure BDA0002792184390000051
the third row is judged to be only the Connector type, i.e. the connection type.
S2: and traversing the connection set, judging the starting point and the end point of the connection through a graph turning function, and obtaining coordinates.
Since the VBA of Excel does not directly obtain the function of the coordinates of the endpoint of the wire, a method for finding the coordinates of the endpoint needs to be constructed. Since all graphic elements, such as connecting lines or other shapes, are considered to be surrounded by a rectangular box in the VBA, the length, width and upper-left corner coordinates of the rectangle can be obtained. Therefore, four coordinates of the rectangular box can be obtained through the length and the width. In addition, since whether a graph is inverted up and down and left and right can be judged in the VBA, and coordinates of an end point of a connecting line are determined as two of four corners, specific coordinates of a starting point and an end point of a connecting line can be determined by values obtained through the judgment. The specific algorithm is as follows:
Figure BDA0002792184390000061
Figure BDA0002792184390000071
the 5 to 26 behavior judgment process of the code judges that the judgment standard is the return values of the horizotalFlip and the VerticalFlip in the VBA. Wherein X1, Y1, X2 and Y2 represent X and Y coordinates, respectively, connecting a start point and an end point of a line. In the default case without any inversion, i.e. when both return values in line 11 are 0, the starting point of a line is the upper left corner and the ending point is the lower right corner. Thus lines 12 to 15 use Left and Top and are calculated with the length and width of the shape to get the starting point.
Similarly, the starting points can be determined according to different inversions, fig. 2 shows a schematic diagram of four cases and an inversion diagram in each case, and the relationship between the inversion values, the rectangular end points and the start points of the links can be clearly seen from fig. 2. Note how the required connection lines have no arrows, that is, the judgment of the starting point and the ending point may not be performed, and the code may be further simplified.
S3: a range of cells is traversed to determine the cell that is the smallest distance from the endpoint.
And when the coordinates of the starting point and the end point of the connecting line are obtained, automatically adjusting the coordinate values to be connected with the cells. For a certain cell, the place to be connected is any point of the circumference circle. Excel has the function of automatically aligning the four corners of the cell by connecting end points. The middle positions of the upper and lower sides of the cell are used in this embodiment as shown in fig. 3. In different implementations, the center point of the edge line on the left side and the center point of the edge line on the right side can be customized according to requirements.
The code to traverse and determine the nearest cell is as follows:
Figure BDA0002792184390000081
range in line 1 of the code ("a1: r29") is a cell that includes a link area. In other implementation manners, a large area may be selected appropriately, and the codes 7 to 12 and 14 to 18 record coordinates of upper and lower midpoints of cells nearest to the current connection end point respectively, and are recorded by adjust x1, adjust y1, adjust x2, and adjust y 2.
S4: and updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates.
After all cells have been traversed, i.e., the most appropriate alignment point has been found, the following statement is used: activesheet. Shapes. AddConnector msoConnectoreStright, adjust X1, adjust Y1, adjust X2, adjust Y2 redraws the join line.
S5: and setting a connection format, and connecting the VBA code and the interactive button.
The format of the connecting line can be further set and the shortcut key of the macro can be edited, and of course, it can be understood that the format can be automatically selected after the connecting line is aligned in other implementation processes.
Fig. 4 and 5 are examples of the result of automatic alignment of a tree diagram. It can be understood in fig. 4 that the user roughly adds and edits the line with the arrow, and it can be seen that the start and end points of the arrow are not connected to the cell. Fig. 5 shows the effect obtained after the function of the system is called, and it can be seen that all the connection terminals have been automatically connected with the middle points of the upper and lower sides of the cell.
Fig. 6 and fig. 7 are another example, and note that at this time, the node of the tree structure is not a single cell, but the method of the present embodiment is still applicable without modification, and the content of the node in the figure is used only as an illustration and has no specific meaning.
Example 2:
the embodiment 2 of the present disclosure provides an automatic alignment device for an Excel connection endpoint, including a processor and a display module connected to the processor, where the processor is loaded with Excel software, the display module is used to perform interface display of Excel, and the processor executes the following automatic alignment method:
finding all connecting lines in a page by using the graphic object attribute in an embedded VBA of Excel to obtain a connecting line set;
judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates;
traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance;
updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates;
and traversing the connection set, and aligning all the connections until the connection set is empty.
The detailed working method of the processor is the same as the Excel connection endpoint automatic alignment method provided in embodiment 1, and is not described herein again.
As will be appreciated by one skilled in the art, embodiments of the present disclosure may be provided as a method, system, or computer program product. Accordingly, the present disclosure may take the form of a hardware embodiment, a software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present disclosure may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, optical storage, and the like) having computer-usable program code embodied therein.
The present disclosure is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the disclosure. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by a computer program, which can be stored in a computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. The storage medium may be a magnetic disk, an optical disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), or the like.
The above description is only a preferred embodiment of the present disclosure and is not intended to limit the present disclosure, and various modifications and changes may be made to the present disclosure by those skilled in the art. Any modification, equivalent replacement, improvement and the like made within the spirit and principle of the present disclosure should be included in the protection scope of the present disclosure.

Claims (10)

1. An Excel connection endpoint automatic alignment method is characterized by being executed in an embedded VBA of Excel and comprising the following steps:
finding all connecting lines in the page by using the graphic object attributes to obtain a connecting line set;
judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates;
traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance;
updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates;
and traversing the connection set, and aligning all the connections until the connection set is empty.
2. The method for automatically aligning Excel wire endpoints according to claim 1, characterized in that a graph inversion function is used to judge the up-down and/or left-right inversion of a graph, and the coordinates of the start point and the end point of the wire are obtained by combining the length and the width of the rectangle and the coordinates of one corner.
3. The method for automatically aligning Excel wire endpoints according to claim 1, wherein the place where any cell can be connected is any point of one circle of the perimeter.
4. The method of claim 1, wherein the predetermined range covers all areas of the connection to be aligned.
5. The method for automatically aligning Excel wire endpoints of claim 1, wherein the wire is redrawn with updated coordinates and a set wire format.
6. The method of claim 1, wherein auto-alignment code in the VBA interfaces with interactive buttons in an Excel spreadsheet interface.
7. The Excel connection end point automatic alignment device is characterized by comprising a processor and a display module connected with the processor, wherein the processor is loaded with Excel software, the display module is used for displaying an interface of Excel, and the processor executes the following automatic alignment method:
finding all connecting lines in a page by using the graphic object attribute in an embedded VBA of Excel to obtain a connecting line set;
judging a starting point and an end point of a certain connecting line through a graph turning function and obtaining coordinates;
traversing all the cells in the preset range, calculating the distance between the current connecting end point and the specified position of the cell boundary, and finding the cell with the minimum distance;
updating the starting point and the end point of the connecting line to the coordinates of the boundary point of the nearest cell, deleting the old connecting line and redrawing the connecting line by using the updated coordinates;
and traversing the connection set, and aligning all the connections until the connection set is empty.
8. The Excel wire end automatic alignment apparatus according to claim 7, characterized in that a graph flip function is used to determine the up-down and/or left-right inversion of the graph, and the coordinates of the start point and the end point of the wire are obtained by combining the length and width of the rectangle and the coordinates of one corner.
9. The apparatus according to claim 1, wherein the connectable location for any cell is any point around the perimeter.
10. The automatic Excel connection end aligning device according to claim 1, wherein the predetermined range is an area covering all connections to be aligned;
alternatively, the first and second electrodes may be,
redrawing the connecting line by using the updated coordinates and the set connecting line format;
alternatively, the first and second electrodes may be,
the auto-align code in the VBA interfaces with the interactive buttons in the Excel form interface.
CN202011318931.8A 2020-11-23 2020-11-23 Automatic Excel connection end point alignment method and device Pending CN112364615A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202011318931.8A CN112364615A (en) 2020-11-23 2020-11-23 Automatic Excel connection end point alignment method and device

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202011318931.8A CN112364615A (en) 2020-11-23 2020-11-23 Automatic Excel connection end point alignment method and device

Publications (1)

Publication Number Publication Date
CN112364615A true CN112364615A (en) 2021-02-12

Family

ID=74532792

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202011318931.8A Pending CN112364615A (en) 2020-11-23 2020-11-23 Automatic Excel connection end point alignment method and device

Country Status (1)

Country Link
CN (1) CN112364615A (en)

Similar Documents

Publication Publication Date Title
US7925084B1 (en) Merge and removal in a planar map of an image
EP0640943B1 (en) Shape modeling apparatus and method
JP3347522B2 (en) Tool path creation device and tool path creation method
WO2012096001A1 (en) Flowchart rendering device, flowchart rendering method, and program
EP0461830A2 (en) Figure editing apparatus
US7929755B1 (en) Planar map to process a raster image
JPH08194840A (en) Graphic input and output device
EP1091325B1 (en) Defining parameters for an FEA calculation in a CAD program
US7733338B2 (en) Reduction of a mesh with preservation of flow lines
JP4046370B2 (en) 3D shape drawing method
CN112364615A (en) Automatic Excel connection end point alignment method and device
JPH064607A (en) Data display device
EP1302849A2 (en) Multi-dimensional programming device and method
CN113221499B (en) Mask layout generation method and device, computer equipment and storage medium
CN114911433A (en) Intelligent CAD drawing framing method and system and readable storage medium
CN115239846A (en) Visual modification method and device for cut file and related components
CN110362942A (en) A kind of generation method on PCB track side generates system and relevant apparatus
CN113779684B (en) Revit-based roadbed slope lattice beam construction model construction method
CN115048008B (en) Method and equipment for visualizing objects in HMI (human machine interface) picture
CN113656871B (en) Building wall drawing method
CN115270530B (en) Method, system, device and medium for rapidly establishing progress visualization model
JP2875135B2 (en) Program device for programmable controller
CN116050036B (en) Power grid diagram construction method, device and equipment
CN116153224A (en) Gray scale compensation method of pixel unit
JP2004288028A (en) Layout system, layout support system, layout program, layout support program, layout method and layout support method

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
RJ01 Rejection of invention patent application after publication
RJ01 Rejection of invention patent application after publication

Application publication date: 20210212