PowerDesigner导出excel按表名排序
PowerDesigner导出excel按表名排序
一、导出到第一个sheet页,并按表名排序
1 '将powerdesigner 建好的表导出为excel文件 2 '1、使用Ctrl+Shift+X打开编辑页面 3 '2、复制以下代码 4 Option Explicit 5 Dim rowsNum 6 rowsNum = 2 7 8 Dim Model 9 Set Model = ActiveModel 10 If (Model Is Nothing) Or (Not Model.IsKindOf(PdPDM.cls_Model)) Then 11 Debug.print "null" 12 Else 13 Dim tableCount, maxTableNum, tableNames 14 tableCount = Model.tables.count 15 maxTableNum = tableCount - 1 16 'MsgBox tableCount 17 18 tableNames = SortTableList(Model, tableCount, maxTableNum) 19 20 ' Get the tables collection 21 '创建EXCEL APP 22 dim beginrow 23 DIM EXCEL, SHEET 24 set EXCEL = CREATEOBJECT("Excel.Application") 25 EXCEL.workbooks.add '添加工作表 26 SET sheet = EXCEL.workbooks(1).sheets(1) 27 sheet.name ="数据库表结构" 28 29 sheet.Range(sheet.cells(1, 1),sheet.cells(1, 9)).Merge 30 sheet.cells(1, 1) ="标题" 31 sheet.Range(sheet.cells(1, 1),sheet.cells(1, 9)).Interior.Color=rgb(146,208,80) 32 33 rowsNum=2 34 beginrow = rowsNum+1 35 36 Dim i,tab 37 For i=0 To maxTableNum 38 tab=tableNames(1,i) 39 TableLoop Model.tables.Item(tab),SHEET 40 41 'Dim tab 42 'For Each tab In SortTables(Model.tables) 43 'TableLoop tab,SHEET 44 Next 45 46 47 EXCEL.visible = true 48 '设置列宽和自动换行 49 sheet.Columns(1).ColumnWidth =10 50 sheet.Columns(2).ColumnWidth =15 51 sheet.Columns(4).ColumnWidth =20 52 sheet.Columns(5).ColumnWidth =15 53 sheet.Columns(6).ColumnWidth =15 54 55 sheet.Columns("C:C").EntireColumn.AutoFit 56 sheet.Columns("i:i").EntireColumn.AutoFit 57 End If 58 59 Sub TableLoop(tab, sheet) 60 If IsObject(tab) Then 61 Dim rangFlag 62 rowsNum = rowsNum + 1 63 64 sheet.cells(rowsNum, 1) = "表名" 65 sheet.Range(sheet.cells(rowsNum, 3),sheet.cells(rowsNum, 9)).Merge 66 sheet.cells(rowsNum, 2)=tab.code 67 sheet.cells(rowsNum, 3)=tab.name 68 sheet.Range(sheet.cells(rowsNum, 1),sheet.cells(rowsNum, 9)).Borders.LineStyle = "1" 69 sheet.Range(sheet.cells(rowsNum, 1),sheet.cells(rowsNum, 9)).Interior.Color=rgb(141,180,226) 70 sheet.Range(sheet.cells(rowsNum, 1),sheet.cells(rowsNum, 9)).Borders.Weight ="3" 71 72 rowsNum = rowsNum + 2 73 sheet.cells(rowsNum, 1) = "中文名" 74 sheet.cells(rowsNum, 2) = "字段名" 75 sheet.cells(rowsNum, 3) = "类型" 76 sheet.cells(rowsNum, 4) = "长度" 77 sheet.cells(rowsNum, 5) = "主键" 78 sheet.cells(rowsNum, 6) = "索引" 79 sheet.cells(rowsNum, 7) = "不可空" 80 sheet.cells(rowsNum, 8) = "默认值" 81 sheet.cells(rowsNum, 9) = "说明" 82 sheet.Range(sheet.cells(rowsNum,1),sheet.cells(rowsNum,9)).Interior.Color=rgb(166,166,166) 83 84 Dim col ' running column 85 Dim colsNum 86 colsNum = 0 87 for each col in tab.columns 88 rowsNum = rowsNum + 1 89 colsNum = colsNum + 1 90 sheet.cells(rowsNum, 1) = col.name 91 sheet.cells(rowsNum, 2) = col.code 92 sheet.cells(rowsNum, 3) = col.datatype 93 sheet.cells(rowsNum, 4) = IIF(col.Length<>0,col.Length,"") 94 sheet.cells(rowsNum, 5) = IIF(col.Primary,"√","") 95 sheet.cells(rowsNum, 6) = IIF(col.Primary,"√","") 96 sheet.cells(rowsNum, 7) = IIF(col.Mandatory,"√","") 97 sheet.cells(rowsNum, 8) = "无" 98 sheet.cells(rowsNum, 9) = col.comment 99 next 100 101 '设置边框 102 DIM RanagBorder 103 SET RanagBorder =sheet.Range(sheet.cells(rowsNum-colsNum,1),sheet.cells(rowsNum,9)) 104 RanagBorder.Borders.LineStyle = "1" 105 'RaneBorderFun RanagBorder 106 107 rowsNum = rowsNum + 1 108 109 End If 110 End Sub 111 112 '----------------------------------------------------------------------------- 113 ' Get Table List Sort By Name 114 '----------------------------------------------------------------------------- 115 Function SortTableList(mdl, count, maxNum) 116 Dim tabs() 117 ReDim tabs(2, count) 118 Dim tab, i, j, temp 119 120 'output "get table names" 121 For i = 0 To maxNum 122 temp = mdl.tables.Item(i).name 123 tabs(0, i) = temp 124 'output temp 125 Next 126 127 'output "sort table names ,if you want desc then update tabs(0,j)>tabs(0,i)" 128 For i = 0 To maxNum 129 For j = i + 1 To maxNum 130 If tabs(0, j) < tabs(0, i) Then 131 temp = tabs(0, i) 132 tabs(0, i) = tabs(0, j) 133 tabs(0, j) = temp 134 End If 135 Next 136 Next 137 138 'output "put table index to sorted names" 139 i = 0 140 For Each tab In Model.tables 141 temp = tab.name 142 'output tab.name 143 For j = 0 To maxNum 144 If (tabs(0, j)) = temp Then 145 tabs(1, j) = i 146 End If 147 Next 148 i = i + 1 149 Next 150 'For i = 0 To maxNum 151 ' output tabs(0, i) + "-" & tabs(1, i) 152 'Next 153 SortTableList = tabs 154 End Function 155 156 157 function IIF(flg,tstr,fstr) 158 if flg then 159 IIF= tstr 160 else 161 IIF= fstr 162 end if 163 End function
参考:
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~