I. Introduction
Microsoft Excel has a very powerful tabulation software, spreadsheet and other functions, is commonly used in tabulation of ordinary persons tool. Through its embedded Microsoft Excel VBA language can control the whole operation.
AutoDesk's AutoCAD works by mapping software, is the CAD market's mainstream products, so powerful, that is commonly used in software engineering drawing one of the officers. AutoDesk from the R14 version of the company after providing the VBA language interface.
In engineering drawing, often need to insert a drawing table in the figure, there are two ways. First, is to use the clipboard, copy to Microsoft Excel table to the clipboard, then open the AutoCAD file, the file then paste the clipboard to the desired location. This method is very simple, but has its inherent disadvantages. ① In the save file must be. Xls and. Dwg file, and when the lack of excel environment, then again on the table to modify. ② open multiple forms, you need a larger memory space occupied. ③ file size becomes large, the table sometimes. Dwg file in the display as an icon, not easy to observe.
The second method, namely the use of Microsoft Excel, AutoCAD VBA functions are provided, the preparation procedures convert the Microsoft Excel table by converting the original look, or the Microsoft Excel table in the text and lines to read out all information in the AutoCAD file correspond to the way in accordance with written, to ensure that the converted form consistent with the original form. This method completely avoids the shortcomings of the former kind of easy content editing form. This article focuses on this method.
Second, form the mechanism of conversion work and the specific realization
1. Mechanism of conversion work table
In the tabulation process, the two concepts often encountered, tables and boxes.
In Microsoft Excel, and the corresponding object table is a worksheet (Sheet or Worksheet), with each box corresponding to the object table is a range of cells (range), it includes only one cell (cell), can also by the merger of multiple cells.
In AutoCAD, there is no corresponding object with the table, but tables can be understood by a number of lines and text objects combinations.
Based on the above analysis, the conversion method can be found as follows:
Microsoft Excel file to read the smallest objects ---- range of cells (range) of information --- the main lines and text, and then in the AutoCAD file in the specified layer, position draw lines, write text. Through the loop, through all of the range of cells (range), 边读边 writing, the final completion of the conversion table. Conversion process, to maintain lines, text and related properties does not change.
The following work on the conversion of two forms of lines and forms the main target of the text for discussion.
2, the conversion table lines
Microsoft Excel VBA embedded in Excel files for the information we obtain provides great convenience. Typically, by accessing the range object, get a lot of information. Access to the properties of the form of range from the beginning. Each range includes many objects and attributes, such as, font object can return range of font information. By traversing, you can get the form information. The purpose of obtaining information form accurately in accordance with the position of art form lines and determine the location of the text.
Access to information in the table, there is an optimal algorithm for the problem. Following is an example on the painting line, to clarify issues and solutions.
Assumptions form the a (a> = 1) line b (b> = 1) columns, x, y for the loop variable, the table composed entirely of cells, because each cell has four sides, so that x from 1 start cycle to a, then y from a start circle to b, to read each cell of the four sides, will read a * b * 4 times, repeat reading a * b * 2 times. When x = 1, the reading on top; when y = 1 reads, on the left, the right to read the remaining cases, below. Were read a + b + a * b * 2 times. To 3 rows 4, for example, read a total of 3 +4 +3 * 4 * 2 = 31, and the actual number of edges in the same table, there is no duplication read.
Information on the merger of the cell is difficult to read. If according to the location of the cell in turn to read, then a row b out by the unit cell (cell) merged range of cells (range) only four sides, using the above method, needs to read a + b + a * b * 2 times, repeat reading a + b + a * b * 2 - 4 times. To a 3-line 4, for example, read a total of 3 +4 +3 * 4 * 2 = 31 times, repeat reading 31 - 4 = 27. Algorithm has to repeat. If the line number, column number to read, merged cell line number, column number is only one, its value is the most left and rely on the cells that line number, column number. For example, A2: E5 cells after the merger, the line number 2, the column number for the A. This combination of multiple merged cells form after the line number, column number are intermittent, not continuous, can not read the information loop. Through the study found, the function address () and properties of the cell mergearea merged cells get accurate information. Specific method: reading cells (x, y) when the cell with address () determine Baohan cells (x, y) cell Hebing c.mergearea the absolute address range of cells, if the first four characters and cells ( x, y) address of the same cell for the cells (x, y) cell range of cells for the most by the merger, the left side of the merged cell, read its four side information, or not read. Thus, a thorough reading to avoid duplication, while increasing the overall speed of reading and drawing lines.
In AutoCAD, there are several lines, consider the lines can be easily controlled properties, select the polyline. Specific command is as follows: RetVal = object.AddLightWeightPolyline (VerticesList)
The following program demonstrates the form to read and draw lines form lines of the specific process.
Sub hxw ()
Dim a as interger 'form of the maximum number of rows
Dim b as interger 'form the largest number of columns
Dim xinit as double 'insertion point x coordinate
Dim yinit as double 'insertion point y coordinate
Dim zinit as double 'insertion point z coordinate
Dim xinsert as double 'the upper left corner point of the current cell x-left-standard
Dim yinsert as double 'current cell point y top left corner of the left marked
Dim ptarray (0 to 2) as double
Dim x as integer
Dim y as integer
For x = 1 to a
For y = 1 to b
Set c = xlsheet.Range (zh (y) + Trim (Str (x)))
'To the line number, column number received cell address
Set ma = c.MergeArea
'Find the cell address C of the merged cell
If Left (Trim (ma.Address), 4) = Trim (c.Address) Then
If c.mergearea absolute address if the first 4 characters and the address of the same cell c
xl = "A1:" + ma.Address
xh = xlsheet.Range (ma.Address). Width
yh = xlsheet.Range (ma.Address). Height
Set xlrange = xlsheet.Range (xl)
xinsert = xlrange.Width - xh
yinsert = xlrange.Height - yh
xpoint = xinit + xinsert
ypoint = yinit - yinsert
If x = 1 Then
If ma.Borders (xlEdgeTop). LineStyle
<> XlNone Then
ptArray (0) = xpoint
'The first point coordinates (array subscript 0 and 1)
ptArray (1) = ypoint
ptArray (2) = xpoint + xh
'The second point coordinates (Array subscript 2 and 3)
ptArray (3) = ypoint
End If
Lineweight lwployobj, ma.Borders (xlEdgeTop). Weight
End If
If ma.Borders (xlEdgeBottom). LineStyle
<> XlNone Then
ptArray (0) = xpoint + xh
'The third point coordinates (array subscript 0 and 1)
ptArray (1) = ypoint - yh
ptArray (2) = xpoint
'The fourth point coordinates (array subscript 2 and 3)
ptArray (3) = ypoint - yh
Lineweight lwployobj,
ma.Borders (xlEdgeBottom). Weight
End If
If y = 1 Then
If ma.Borders (xlEdgeLeft). LineStyle
<> XlNone Then
ptArray (0) = xpoint
'The fourth point coordinates (array subscript 0 and 1)
ptArray (1) = ypoint - yh
ptArray (2) = xpoint
'The first point coordinates (array subscript 2 and 3)
ptArray (3) = ypoint
End If
Lineweight lwployobj, ma.Borders (xlEdgeLeft). Weight
End If
If ma.Borders (xlEdgeRight). LineStyle
<> XlNone Then
ptArray (0) = xpoint + xh
'The second point coordinates (Array subscript 0 and 1)
ptArray (1) = ypoint
ptArray (2) = xpoint + xh
'The third point coordinates (array subscript 2 and 3)
ptArray (3) = ypoint - yh
Lineweight lwployobj,
ma.Borders (xlEdgeRight). Weight
End If
Set lwployobj = moSpace.AddLightWeightPolyline (ptArray)
'Drawing a line in the AutoCAD file
With lwployobj
. Layer = newlayer.name 'where the layers specified lwployobj
. Color = acBlue 'the color of the specified lwployobj
End With
Lwployobj.Update
Next y
Next x
End Sub
'The following program control line thickness
Sub Lineweight (ByVal line As Object, u As Integer)
Select Case u
Case 1
Call line.SetWidth (0, 0.1, 0.1)
Case 2
Call line.SetWidth (0, 0.3, 0.3)
Case -4138
Call line.SetWidth (0, 0.5, 0.5)
Case 4
Call line.SetWidth (0, 1, 1)
Case Else
Call line.SetWidth (0, 0.1, 0.1)
End Select
End Sub
'The following conversion process is complete the column number
Function zh (pp As Integer) As String
If pp <26 Then
zh = Chr (64 + pp)
Else
zh = Chr (64 + Int (pp / 26)) + Chr (64 + pp Mod 26)
End If
End Function
3, table text-
Form form Text text conversion, including conversion and form their own text in the table change the location of two parts.
In AutoCAD, the text marked in many forms, and Microsoft Excel range of cells corresponding to multiple lines of text is the multiple lines of text commands. AutoCAD VBA provides the command to add multiple lines of text statement is:
RetVal = object.AddMText (InsertionPoint, Width, Text)
Properties by modifying the RetVal can control the text in the table table position.
(1). Conversion table of the characters themselves
Analysis AddMText command can be drawn: Form text location, text width, text may be added by this command. However, the form text font, size, underline, superscript feet up and down, tilt, bold, etc. can not. General approach is to modify the font shape file using the method to implement the method cumbersome and not easy to achieve, and only changed the font shape file effectively. Moreover, when the same text block of text in different font, size, underline, superscript feet up and down, tilt, bold is not the same time, using the modified method of the font shape file can not be achieved. This paper presents a direct method using Mtext order to provide the conversion.
In AddMText command, text and text properties affect the parameters of Text. Specific text in the control of the former with a certain symbol can control the text attributes the text, the specific control symbols can be found in AutoCAD help file. For example, (F Arial; Q18; W1.2; ABCDEFG) the "ABCDEFG" set to song, and right tilt of 18 degrees, the width of each character is 1.2 times the normal width.
The specific procedure is used: to read Microsoft Excel files in a certain range of cells first j characters attributes (font, size, underline, on scraps, standard, italic, bold), read the Microsoft Excel File a range of cells in a certain section j +1 character attribute, if the first j characters with the same, both control the same symbols; if different from characters from the first j +1 start, repeat the previous work.
Sub wz ()
Char = RTrim (Left (c.Characters.Caption, 256))
If Char <> Empty Then
textStr = ""
For j = 1 To Len (Char)
If c.Characters (j, 1). Font.Underline =
xlUnderlineStyleNone Then
cpt = c.Characters (j, 1). Caption
sonstr = ForeFontStr (c, j)
tempstr = ""
Do While j + 1 <= Len (Char)
sonstr1 = ForeFontStr (c, j + 1)
If sonstr1 = sonstr Then
j = j + 1
tempstr = tempstr + c.Characters (j,
1). Caption
Else
Exit Do
End If
Loop
textStr = textStr + "(" + sonstr + cpt
+ Tempstr + ")"
Else
cpt = c.Characters (j, 1). Caption
sonstr = ForeFontStr (c, j)
tempstr = ""
Do While j + 1 <= Len (Char)
sonstr1 = ForeFontStr (c, j + 1)
If sonstr1 = sonstr Then
j = j + 1
tempstr = tempstr + c.Characters (j,
1). Caption
Else
Exit Do
End If
Loop
textStr = textStr + "(L" +
sonstr + cpt + tempstr + "l)"
End If
Next j
End If
End Sub
'The following functions control the attributes of the font
Function ForeFontStr (m As Range, u As Integer) As String
a1 = "F" + m.Characters (u, 1). Font.Name + ";" 'font
a2 = IIf (m.Characters (u, 1). Font.Superscript =
True, "H0.33x; A2;", "")''marked on the foot
a3 = IIf (m.Characters (u, 1). Font.Subscript =
True, "H0.33x; A0;", "")''substandard standard
a4 = IIf (m.Characters (u, 1). Font.FontStyle =
"Tilt", "Q18;", "")''tilt
a5 = IIf (m.Characters (u, 1). Font.FontStyle =
"Bold", "W1.2;", "")''Bold
a6 = IIf (m.Characters (u, 1). Font.FontStyle =
"Bold tilt", "W1.2; Q18;", "")''Bold Inclined
ForeFontStr = a1 + a2 + a3 + a4 + a5 + a6
End Function
(2). Table conversion table position text
On the text object's properties to achieve direct control, through with .... End with the structure can easily control the height of the text, layers, colors, writing direction. As Mtext support of the positioning of the text is divided into 9 species, must be based on Microsoft Excel table arrangement of text determined to be appropriate, and then convert. The specific implementation method described in the following procedure.
Sub kz ()
With textObj 'text object
. Height = textHgt
. Layer = newlayer.Name 'set layer
. Color = acRed 'set the color
. DrawingDirection = 1 'set the writing direction
If (ma.VerticalAlignment = xlTop _
Or ma.VerticalAlignment = xlGeneral) _
And (ma.HorizontalAlignment = xlLeft _
Or ma.HorizontalAlignment = xlGeneral) _
Then. AttachmentPoint = 1''acAttachmentPointTopLeft
If (ma.VerticalAlignment = xlTop _
Or ma.VerticalAlignment = xlGeneral) _
And (ma.HorizontalAlignment = xlCenter _
Or ma.HorizontalAlignment = xlJustify _
Or ma.HorizontalAlignment = xlDistributed) _
Then. AttachmentPoint = 2''acAttachmentPointTopCenter
If (ma.VerticalAlignment = xlTop _
Or ma.VerticalAlignment = xlGeneral) _
And ma.HorizontalAlignment = xlRight _
Then. AttachmentPoint = 3''acAttachmentPointTopRight
If (ma.VerticalAlignment = xlCenter _
Or ma.VerticalAlignment = xlJustify _
Or ma.VerticalAlignment = xlDistributed) _
And (ma.HorizontalAlignment = xlLeft _
Or ma.HorizontalAlignment = xlGeneral) _
Then. AttachmentPoint = 4''acAttachmentPointMiddleLeft
If (ma.VerticalAlignment = xlCenter _
Or ma.VerticalAlignment = xlJustify _
Or ma.VerticalAlignment = xlDistributed) _
And (ma.HorizontalAlignment = xlCenter _
Or ma.HorizontalAlignment = xlJustify _
Or ma.HorizontalAlignment = xlDistributed) _
Then. AttachmentPoint = 5''acAttachmentPointMiddleCenter
If (ma.VerticalAlignment = xlCenter _
Or ma.VerticalAlignment = xlJustify _
Or ma.VerticalAlignment = xlDistributed) _
And ma.HorizontalAlignment = xlRight _
Then. AttachmentPoint = 6''acAttachmentPointMiddleRight
If ma.VerticalAlignment = xlBottom _
And (ma.HorizontalAlignment = xlLeft _
Or ma.HorizontalAlignment = xlGeneral) _
Then. AttachmentPoint = 7''acAttachmentPointBottomLeft
If ma.VerticalAlignment = xlBottom _
And (ma.HorizontalAlignment = xlCenter _
Or ma.HorizontalAlignment = xlJustify _
Or ma.HorizontalAlignment = xlDistributed) _
Then. AttachmentPoint = 8''acAttachmentPointBottomCenter
If ma.VerticalAlignment = xlBottom _
And ma.HorizontalAlignment = xlRight _
Then. AttachmentPoint = 9''acAttachmentPointBottomRight
End With
textObj.Update
End Sub
Third, functions and features introduced
The program can form in Excel all the cells in full by the original size, style file to switch to AutoCAD. In the conversion process, the conversion table lines and text conversion is the key. Text used to provide direct access to AddMtext command to convert the property to avoid the previous years, to modify the shape file for text marking methods, direct control of the form text font, size, underline, superscript feet up and down, tilt, bold, so that each text style can be well controlled are greatly enhanced the flexibility of the text label.
The program compiled using Visual BASIC, Microsoft Excel 2000 and need to run AutoCAD R14 environment, compiled by.