Monday 16 October 2006

Export properties of all tables to Excel

Here is a small script to export some properties of all tables to an Excel file. Excel code take from Palle Agermark's WebLog Is there anything Excel isn't a good tool for?

 static void TablePropertiesToExcel(Args _args)  
 {  
   Dictionary dictionary = new Dictionary();  
   DictTable  dictTable;  
   int     rowCounter = 1;  
   
   SysExcelApplication excelApp = SysExcelApplication::construct();  
   SysExcelWorkBooks excelWorkBooks;  
   SysExcelWorkBook excelWorkBook;  
   SysExcelWorkSheets excelWorkSheets;  
   SysExcelWorkSheet excelWorkSheet;  
   SysExcelCells excelCells;  
   SysExcelCell excelCell;  
   COM excelRange;  
   COM excelFont;  
     
   SysOperationProgress progress = new SysOperationProgress();  
   
   #define.ColumnName(1)  
   #define.ColumnLabel(2)  
   #define.ColumnSecurityKey(3)  
   ;  
   
   progress.setCaption('Exporting to Excel');  
     
   excelWorkBooks = excelApp.workbooks();  
   excelWorkBook = excelWorkBooks.add();  
     
   excelWorkSheets = excelWorkBook.worksheets();  
   excelWorkSheet = excelWorkSheets.itemFromNum(1);  
   
   excelCells = excelWorkSheet.cells();  
   excelCells.item(1, #ColumnName).value("@SYS16033");  
   excelCells.item(1, #ColumnLabel).value("@SYS25058");  
   excelCells.item(1, #ColumnSecurityKey).value("@SYS73164");  
   
   dictTable = dictionary.tableObject(dictionary.tableNext(0));  
   
   progress.setTotal(dictionary.tableCnt());  
   
   while (dictTable)  
   {  
     rowCounter++;  
   
     progress.setCount(rowCounter-1);  
     progress.setText(dictTable.name());  
   
     excelCell = excelCells.item(rowCounter, #ColumnName);  
     excelCell.value(dictTable.name());  
   
     excelCell = excelCells.item(rowCounter, #ColumnLabel);  
     excelCell.value(dictTable.label());  
   
     excelCell = excelCells.item(rowCounter, #ColumnSecurityKey);  
     excelCell.value(securityKeyId2Name(dictTable.securityKeyId()));  
   
     dictTable = dictionary.tableObject(dictionary.tableNext(dictTable.id()));  
   }  
   
   
   excelRange = excelWorkSheet.range('A1:C1').comObject();  
   excelFont = excelRange.font();  
   excelFont.bold(true);  
   
   excelWorkSheet.columns().item(#ColumnName).autoFit();  
   excelWorkSheet.columns().item(#ColumnLabel).autoFit();  
   excelWorkSheet.columns().item(#ColumnSecurityKey).autoFit();  
   
   excelWorkSheet.name('Tables');  
   excelApp.visible(true);  
 }  
   

This posting is provided "AS IS" with no warranties. Use code at your own risk.