首页 / MYSQL / 在excel中格式化mysql数据
在excel中格式化mysql数据
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了在excel中格式化mysql数据，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4996字，纯文字阅读大概需要8分钟。
内容图文
我在mysql中有一个像这样的表：
+-------+--------+-------------+
| child | parent | data |
+-------+--------+-------------+
| 1 | 0 | house |
| 2 | 0 | car |
| 3 | 1 | door |
| 4 | 2 | door |
| 5 | 2 | windscreen |
| 11 | 5 | wiper |
+-------+--------+-------------+
我根据this教程从excel 2007连接到mysql,除了我在系统dns中创建dns而不是用户dns,这对我有用.
我对公式有一点了解,我无法弄清楚如何获取这些表格数据：
house | door
house | wall
car | door
car | windscreen | wiper
编辑1
这里使用mysql的部分不是问题.那个mysql表很可能是一个excel表.
编辑2
现在我意识到甚至没有必要说这里有一个mysql表只是一个excel表.但这可能激发/帮助某人.
编辑3
在一些文档后,我设法解决了我的问题的最重要的方面
表db中的范围：
child parent data
1 0 car
2 0 house
3 1 door
4 2 door
5 1 window
6 2 window
7 1 windscreen
8 7 wiper
9 4 color
10 2 color
我有一个名为db的引用
=db!$A$2:OFFSET(db!$C$2,COUNTA(db!$C:$C)-2,0)
一个名字的孩子
=db!$A$2:OFFSET(db!$A$2,COUNTA(db!$A:$A)-2,0)
在具有名称构造的另一个工作表中,我从B2开始并使用以下公式：
=IFERROR(
IF(ISBLANK(B1),
LARGE(child,COUNTA($A$2:A$2)+1),
VLOOKUP(B1,db,2,0)
),".")
在名为输出的第三张表中,我从A1开始并使用公式：
=IFERROR(VLOOKUP(construct!B2,db,3,0),".")
现在最后一个挑战是在将新条目添加到主表时将构造和输出的公式设置为自动扩展,但我认为不可能.
编辑4
从db表中的sql导入时,将有一个表而不是范围,因此公式看起来会有所不同.单击表格中的任意位置,单击设计选项卡并重命名表格基础,然后在b2的构造表格中使用以下公式开始：
=IFERROR(
IF(ISBLANK(B1),
LARGE(INDIRECT("base[child]"),COUNTA($A$2:A$2)+1),
VLOOKUP(B1,base,2,0)
),".")
解决方法:
以下是我使用VBA的方法：首先创建一个类模块并将其命名为CDatum.把这段代码放在那里.
Option Explicit
Private msID As String
Private msData As String
Private msParentID As String
Public Property Get ID() As String
ID = msID
End Property
Public Property Let ID(ByVal sID As String)
msID = sID
End Property
Public Property Get Data() As String
Data = msData
End Property
Public Property Let Data(ByVal sData As String)
msData = sData
End Property
Public Property Get ParentID() As String
ParentID = msParentID
End Property
Public Property Let ParentID(ByVal sParentID As String)
msParentID = sParentID
End Property
Public Property Get ChildCount() As Long
Dim i As Long
Dim lReturn As Long
For i = 1 To gclsData.Count
If gclsData.Data(i).ParentID = Me.ID Then
lReturn = lReturn + 1
End If
Next i
ChildCount = lReturn
End Property
Public Property Get Tree() As Variant
Dim vaReturn As Variant
Dim vaChild As Variant
Dim i As Long, j As Long
Dim lChildCount As Long
Dim lRowCount As Long
Dim lOldUbound As Long
If Me.ChildCount = 0 Then
lRowCount = 1
Else
lRowCount = Me.ChildCount
End If
ReDim vaReturn(1 To lRowCount, 1 To 1)
For i = 1 To lRowCount
vaReturn(i, 1) = Me.Data
Next i
For i = 1 To gclsData.Count
If gclsData.Data(i).ParentID = Me.ID Then
lChildCount = lChildCount + 1
vaChild = gclsData.Data(i).Tree
lOldUbound = UBound(vaReturn, 2)
ReDim Preserve vaReturn(1 To lRowCount, 1 To UBound(vaReturn, 2) + UBound(vaChild, 2))
For j = 1 To UBound(vaChild, 2)
vaReturn(lChildCount, j + 1) = vaChild(1, j)
Next j
End If
Next i
Tree = vaReturn
End Property
接下来创建一个类模块并将其命名为CData并将此代码放入其中
Option Explicit
Private mcolCDatas As Collection
Private Sub Class_Initialize()
Set mcolCDatas = New Collection
End Sub
Private Sub Class_Terminate()
Set mcolCDatas = Nothing
End Sub
Public Sub Add(clsDatum As CDatum)
mcolCDatas.Add clsDatum, clsDatum.ID
End Sub
Public Property Get Count() As Long
Count = mcolCDatas.Count
End Property
Public Property Get Data(vItem As Variant) As CDatum
Set Data = mcolCDatas.Item(vItem)
End Property
Public Property Get FilterByTopLevel() As CData
Dim clsReturn As CData
Dim i As Long
Dim clsDatum As CDatum
Set clsReturn = New CData
For i = 1 To Me.Count
Set clsDatum = Me.Data(i)
If clsDatum.ParentID = 0 Then
clsReturn.Add clsDatum
End If
Next i
Set FilterByTopLevel = clsReturn
End Property
接下来插入一个标准模块并将此代码放入其中
Option Explicit
Public gclsData As CData
Sub FillClass()
Dim clsDatum As CDatum
Dim rCell As Range
Set gclsData = New CData
For Each rCell In Sheet1.Range("A2:A7").Cells
Set clsDatum = New CDatum
clsDatum.ID = rCell.Value
clsDatum.Data = rCell.Offset(0, 2).Value
clsDatum.ParentID = rCell.Offset(0, 1).Value
gclsData.Add clsDatum
Next rCell
End Sub
Sub PrintTree()
Dim clsDatum As CDatum
Dim clsTopLevel As CData
Dim i As Long
Dim ws As Worksheet
Dim vaData As Variant
Dim lRowCount As Long
FillClass
Set clsTopLevel = gclsData.FilterByTopLevel
Set ws = ThisWorkbook.Worksheets.Add
lRowCount = 1
For i = 1 To clsTopLevel.Count
Set clsDatum = clsTopLevel.Data(i)
vaData = clsDatum.Tree
ws.Cells(lRowCount, 1).Resize(UBound(vaData, 1), UBound(vaData, 2)).Value = vaData
lRowCount = lRowCount + UBound(vaData, 1)
Next i
End Sub
然后运行PrintTree子.或者您可以下载我用来测试它的工作簿并在那里进行操作.
http://www.dailydoseofexcel.com/excel/TestDataClass.zip
内容总结
以上是互联网集市为您收集整理的在excel中格式化mysql数据全部内容，希望文章能够帮你解决在excel中格式化mysql数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。