PowerDesigner生成excel

PowerDesigner生成excel脚本代码 Option Explicit Dim rowsNum rowsNum = 0 '----------------------------------------------------------------------------- ' Main function '------------------------------------------------------------------------

PowerDesigner生成excel脚本代码

<code class=" hljs vbnet"><span class="hljs-keyword">Option</span> <span class="hljs-keyword">Explicit</span>

   <span class="hljs-keyword">Dim</span> rowsNum

   rowsNum = <span class="hljs-number">0</span>

<span class="hljs-comment">'-----------------------------------------------------------------------------</span>

<span class="hljs-comment">' Main function</span>

<span class="hljs-comment">'-----------------------------------------------------------------------------</span>

<span class="hljs-comment">' Get the current active model</span>

<span class="hljs-keyword">Dim</span> Model

<span class="hljs-keyword">Set</span> Model = ActiveModel

<span class="hljs-keyword">If</span> (Model <span class="hljs-keyword">Is</span> <span class="hljs-literal">Nothing</span>) <span class="hljs-keyword">Or</span> (<span class="hljs-keyword">Not</span> Model.IsKindOf(PdPDM.cls_Model)) <span class="hljs-keyword">Then</span>

  MsgBox <span class="hljs-string">"The current model is not an PDM model."</span>

<span class="hljs-keyword">Else</span>

 <span class="hljs-comment">' Get the tables collection</span>

 <span class="hljs-comment">'创建EXCEL APP</span>

 <span class="hljs-keyword">dim</span> beginrow

 <span class="hljs-keyword">DIM</span> EXCEL, SHEET

 <span class="hljs-keyword">set</span> EXCEL = CREATEOBJECT(<span class="hljs-string">"Excel.Application"</span>)

 EXCEL.workbooks.add(-<span class="hljs-number">4167</span>)<span class="hljs-comment">'添加工作表</span>

 EXCEL.workbooks(<span class="hljs-number">1</span>).sheets(<span class="hljs-number">1</span>).name =<span class="hljs-string">"test"</span>

 <span class="hljs-keyword">set</span> sheet = EXCEL.workbooks(<span class="hljs-number">1</span>).sheets(<span class="hljs-string">"test"</span>)



 ShowProperties Model, SHEET

 EXCEL.visible = <span class="hljs-literal">true</span>

 <span class="hljs-comment">'设置列宽和自动换行</span>

 sheet.Columns(<span class="hljs-number">1</span>).ColumnWidth = <span class="hljs-number">20</span> 

 sheet.Columns(<span class="hljs-number">2</span>).ColumnWidth = <span class="hljs-number">40</span> 
 sheet.Columns(<span class="hljs-number">3</span>).ColumnWidth = <span class="hljs-number">30</span>

 sheet.Columns(<span class="hljs-number">1</span>).WrapText =<span class="hljs-literal">true</span>

 sheet.Columns(<span class="hljs-number">2</span>).WrapText =<span class="hljs-literal">true</span>

 sheet.Columns(<span class="hljs-number">4</span>).WrapText =<span class="hljs-literal">true</span>

 <span class="hljs-keyword">End</span> <span class="hljs-keyword">If</span>

<span class="hljs-comment">'-----------------------------------------------------------------------------</span>

<span class="hljs-comment">' Show properties of tables</span>

<span class="hljs-comment">'-----------------------------------------------------------------------------</span>

<span class="hljs-keyword">Sub</span> ShowProperties(mdl, sheet)

   <span class="hljs-comment">' Show tables of the current model/package</span>

   rowsNum=<span class="hljs-number">0</span>

   beginrow = rowsNum+<span class="hljs-number">1</span>

   <span class="hljs-comment">' For each table</span>

   output <span class="hljs-string">"begin"</span>

   <span class="hljs-keyword">Dim</span> tab

   <span class="hljs-keyword">For</span> <span class="hljs-keyword">Each</span> tab <span class="hljs-keyword">In</span> mdl.tables

      ShowTable tab,sheet

   <span class="hljs-keyword">Next</span>

   <span class="hljs-keyword">if</span> mdl.tables.count > <span class="hljs-number">0</span> <span class="hljs-keyword">then</span>

        sheet.Range(<span class="hljs-string">"A"</span> & beginrow + <span class="hljs-number">1</span> & <span class="hljs-string">":A"</span> & rowsNum).Rows.<span class="hljs-keyword">Group</span>

   <span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>

   output <span class="hljs-string">"end"</span>

<span class="hljs-keyword">End</span> <span class="hljs-keyword">Sub</span>

<span class="hljs-comment">'-----------------------------------------------------------------------------</span>

<span class="hljs-comment">' Show table properties</span>

<span class="hljs-comment">'-----------------------------------------------------------------------------</span>

<span class="hljs-keyword">Sub</span> ShowTable(tab, sheet)

   <span class="hljs-keyword">If</span> IsObject(tab) <span class="hljs-keyword">Then</span>

     <span class="hljs-keyword">Dim</span> rangFlag

     rowsNum = rowsNum + <span class="hljs-number">1</span>

      <span class="hljs-comment">' Show properties</span>

      Output <span class="hljs-string">"================================"</span>

      sheet.cells(rowsNum, <span class="hljs-number">1</span>) = <span class="hljs-string">"表名"</span>
      sheet.cells(rowsNum, <span class="hljs-number">2</span>) =tab.name

      sheet.Range(sheet.cells(rowsNum, <span class="hljs-number">2</span>),sheet.cells(rowsNum, <span class="hljs-number">3</span>)).Merge

      rowsNum = rowsNum + <span class="hljs-number">1</span>

      sheet.cells(rowsNum, <span class="hljs-number">1</span>) = <span class="hljs-string">"属性名"</span>

      sheet.cells(rowsNum, <span class="hljs-number">2</span>) = <span class="hljs-string">"说明"</span>

      sheet.cells(rowsNum, <span class="hljs-number">3</span>) = <span class="hljs-string">"字段类型"</span>

      <span class="hljs-comment">'设置边框</span>

      sheet.Range(sheet.cells(rowsNum-<span class="hljs-number">1</span>, <span class="hljs-number">1</span>),sheet.cells(rowsNum, <span class="hljs-number">3</span>)).Borders.LineStyle = <span class="hljs-string">"1"</span>

<span class="hljs-keyword">Dim</span> col <span class="hljs-comment">' running column</span>

<span class="hljs-keyword">Dim</span> colsNum

colsNum = <span class="hljs-number">0</span>

      <span class="hljs-keyword">for</span> <span class="hljs-keyword">each</span> col <span class="hljs-keyword">in</span> tab.columns

        rowsNum = rowsNum + <span class="hljs-number">1</span>

        colsNum = colsNum + <span class="hljs-number">1</span>

      sheet.cells(rowsNum, <span class="hljs-number">1</span>) = col.name

      sheet.cells(rowsNum, <span class="hljs-number">2</span>) = col.comment

      sheet.cells(rowsNum, <span class="hljs-number">3</span>) = col.datatype

      <span class="hljs-keyword">next</span>

      sheet.Range(sheet.cells(rowsNum-colsNum+<span class="hljs-number">1</span>,<span class="hljs-number">1</span>),sheet.cells(rowsNum,<span class="hljs-number">3</span>)).Borders.LineStyle = <span class="hljs-string">"1"</span> 

      rowsNum = rowsNum + <span class="hljs-number">1</span>

      Output <span class="hljs-string">"FullDescription: "</span>       + tab.Name

   <span class="hljs-keyword">End</span> <span class="hljs-keyword">If</span>

<span class="hljs-keyword">End</span> <span class="hljs-keyword">Sub</span>
</code>