power designer 16.5 批量输出表格和视图到excel

'******************************************************************************'* File: Pdm2Excel.vbs'* Title: pdm export to excel'* Purpose: To export the tables and views to Excel'* model: Physical Data model'* Objects: Table, View'* Author: TangTao'* Created: 2017-05-03'* Version: 1.0'******************************************************************************Option ExplicitDim rowIndex '记录表格行总数，也是行指针，全局变量rowIndex = 0' 引用power designer对象，以便遍历tabDim modelSet model = ActivemodelIf (model Is Nothing) Or (Not model.IsKindOf(PdPDM.cls_model)) ThenMsgBox "The current model is not an PDM model."ElseDIM excel, sheetset excel = CREATEOBJECT("Excel.Application")excel.workbooks.add(-4167) '添加只包含一个sheet页的workbookexcel.workbooks(1).sheets(1).name ="tt" '设置sheet名set sheet = excel.workbooks(1).sheets("tt") '获取该sheet页printModel model, sheet '调用printmodel(mdl, sheet)方法excel.visible = truesetExcelFormat sheet 'setExcelFormat(sheet)方法End If'-----------------------------------------------------------------------------' 设置excel格式属性'-----------------------------------------------------------------------------Sub setExcelFormat(sheet)'设置列宽和自动换行sheet.Columns(1).ColumnWidth = 15 '列宽sheet.Columns(2).ColumnWidth = 15sheet.Columns(3).ColumnWidth = 15sheet.Columns(4).ColumnWidth = 15sheet.Columns(5).ColumnWidth = 15sheet.Columns(6).ColumnWidth = 15sheet.Columns(7).ColumnWidth = 15'sheet.Columns(1).WrapText =true '自动换行'sheet.Columns(3).WrapText =trueEnd Sub'-----------------------------------------------------------------------------' 打印表头'-----------------------------------------------------------------------------Sub printTabTitle(tab, sheet)If IsObject(tab) Then' 设置第1行表头rowIndex = rowIndex + 1sheet.cells(rowIndex, 1) = "表名"sheet.cells(rowIndex, 2) = tab.name' 合并(rowIndex,2)到(rowIndex,3)范围内单元格sheet.Range(sheet.cells(rowIndex, 2),sheet.cells(rowIndex, 3)).Merge' 合并(rowIndex,4)到(rowIndex,7)范围内单元格sheet.cells(rowIndex, 4) = tab.codesheet.Range(sheet.cells(rowIndex, 4),sheet.cells(rowIndex, 7)).Merge' 设置第2行表头rowIndex = rowIndex + 1sheet.cells(rowIndex, 1) = "列名(name)"sheet.cells(rowIndex, 2) = "列名(code)"sheet.cells(rowIndex, 3) = "注释(comment)"sheet.cells(rowIndex, 4) = "数据类型(data type)"sheet.cells(rowIndex, 5) = "主键(primary key)"sheet.cells(rowIndex, 6) = "外键(foreign key)"sheet.cells(rowIndex, 7) = "非空(mandatory)"' 设置边框sheet.Range(sheet.cells(rowIndex - 1, 1),sheet.cells(rowIndex, 7)).Borders.LineStyle = "1"' 设置单元格颜色sheet.Range(sheet.cells(rowIndex - 1, 1),sheet.cells(rowIndex - 1, 7)).Interior.colorindex = 15End IfEnd Sub'-----------------------------------------------------------------------------' 打印模型'-----------------------------------------------------------------------------Sub printModel(mdl, sheet)' 通过mdl遍历所有表格Dim tabFor Each tab In mdl.tablesprintTable tab,sheet ' 调用printTable(tab,sheet)函数Next' 通过mdl遍历所有视图Dim viewFor Each view In mdl.viewsprintView view,sheet ' 调用printView(view,sheet)函数NextEnd Sub'-----------------------------------------------------------------------------' 打印表格'-----------------------------------------------------------------------------Sub printTable(tab, sheet)' 与上一表格留出两行空行rowIndex = rowIndex + 2If IsObject(tab) Then' 设置表头，rowIndex+2printTabTitle tab, sheet ' 调用printTabTitle(tab,sheet)函数' 循环遍历每列，输出信息Dim colDim colNumcolNum = 0for each col in tab.columnsprintCol col, sheet ' 调用printCol(col,sheet)函数colNum = colNum + 1next' 设置列边框sheet.Range(sheet.cells(rowIndex - colNum + 1, 1), sheet.cells(rowIndex, 7)).Borders.LineStyle = "1"End IfEnd Sub'-----------------------------------------------------------------------------' 打印列'-----------------------------------------------------------------------------Sub printCol(col, sheet)   'StoprowIndex = rowIndex + 1sheet.cells(rowIndex, 1) = col.namesheet.cells(rowIndex, 2) = col.codesheet.cells(rowIndex, 3) = col.commentsheet.cells(rowIndex, 4) = col.datatype' 设置主键、外键、非空标志If col.Primary Thensheet.cells(rowIndex, 5) = "P"sheet.cells(rowIndex, 5).VerticalAlignment = 2 ' 垂直居中      sheet.cells(rowIndex, 5).HorizontalAlignment = 3 ' 水平居中Elsesheet.cells(rowIndex, 5) = ""End IfIf col.ForeignKey Thensheet.cells(rowIndex, 6) = "F"sheet.cells(rowIndex, 6).VerticalAlignment = 2 ' 垂直居中      sheet.cells(rowIndex, 6).HorizontalAlignment = 3 ' 水平居中Elsesheet.cells(rowIndex, 6) = ""End IfIf col.Mandatory Thensheet.cells(rowIndex, 7) = "M"sheet.cells(rowIndex, 7).VerticalAlignment = 2 ' 垂直居中      sheet.cells(rowIndex, 7).HorizontalAlignment = 3 ' 水平居中Elsesheet.cells(rowIndex, 7) = ""End If' 如果是power designer中的复制列，将改行字体修改为灰色If col.Replica Thensheet.Range(sheet.cells(rowIndex, 1), sheet.cells(rowIndex, 7)).Font.Color = RGB(150, 150, 150) End IfEnd Sub'-----------------------------------------------------------------------------' 打印视图抬头'-----------------------------------------------------------------------------Sub printViewTitle(view, sheet)If IsObject(view) Then' 设置第1行表头rowIndex = rowIndex + 1sheet.cells(rowIndex, 1) = "视图名"sheet.cells(rowIndex, 2) = view.name' 合并(rowIndex,3)到(rowIndex,4)范围内单元格sheet.cells(rowIndex, 3) = view.codesheet.Range(sheet.cells(rowIndex, 3),sheet.cells(rowIndex, 4)).Merge' 设置第2行表头rowIndex = rowIndex + 1sheet.cells(rowIndex, 1) = "列名(name)"sheet.cells(rowIndex, 2) = "列名(code)"sheet.cells(rowIndex, 3) = "注释(comment)"sheet.cells(rowIndex, 4) = "数据类型(data type)"' 设置边框sheet.Range(sheet.cells(rowIndex - 1, 1),sheet.cells(rowIndex, 4)).Borders.LineStyle = "1"' 设置单元格颜色sheet.Range(sheet.cells(rowIndex - 1, 1),sheet.cells(rowIndex - 1, 4)).Interior.colorindex = 34End IfEnd Sub'-----------------------------------------------------------------------------' 打印视图'-----------------------------------------------------------------------------Sub printView(view, sheet)' 与上一表格留出两行空行rowIndex = rowIndex + 2If IsObject(view) Then' 设置表头，rowIndex+2printViewTitle view, sheet ' 调用printViewTitle(view,sheet)函数' 循环遍历每列，输出信息Dim colDim colNumcolNum = 0for each col in view.columnsrowIndex = rowIndex + 1sheet.cells(rowIndex, 1) = col.namesheet.cells(rowIndex, 2) = col.codesheet.cells(rowIndex, 3) = col.commentsheet.cells(rowIndex, 4) = col.datatypecolNum = colNum + 1next' 设置列边框sheet.Range(sheet.cells(rowIndex - colNum + 1, 1), sheet.cells(rowIndex, 4)).Borders.LineStyle = "1"End IfEnd Sub

参考vbs设置excel格式链接:

http://blog.csdn.net/llbacyal/article/details/9208545/

http://mimmy.iteye.com/blog/1622365vbs excel color index

vbs excel color index