Scripting Excel with JudoScript

Before you can start scripting Excel, you need to do some checks :

• Do you have a judo.jar that is recent ( > 14 november 2004 )
• Do you have jcom.dll in the same folder as judo.jar or in the PATH ?
• Do you have Excel on your machine ?

If all this is checked, then running the following script should open Excel for you :

xlapp = new com::Excel.Application;
xlapp.Visible = true;

When manipulating Excel, you should know about the basic set up of Excel :

Application
  |-Workbooks
      |-Workbook
          |-Worksheets
              |-Ranges
                  |-Cell

Okay, ready for a little case ? Karin, a lovely lady at the department spends 4 days per month downloading a file from Oracle and then analyzing it in Excel. A little script could connect to Oracle, download data, open Excel , do the whole shebang and then prepare a mail in Outlook ready to send to her boss.

In this topic I am just going to focus on the excel part. The dump from Oracle looks somewhat like this :
PROJECTCODE   BILLINGACTIVITYCODE      CONSULTANTCODE HOURS
-----------   ------------------------ -------------- -----
MDF01234879   CODING                   CAQC1          8


She needs to have for each billingactivitycode 1 sheet, with all entries in there, sorted by project code, consultantcode and hours. There needs to be summation of hours per project code and per consultant code.

//Note : do file and string handling in JudoScript, because these can break over different versions of Excel
//Note : this could be written more efficiently, but it shows the Excel Object Model

xlapp = new com::Excel.Application;
xlapp.Visible = true;
xlworkbooks = xlapp.Workbooks;
xlworkbook  = xlworkbooks.add();
xlworksheets = xlworkbook.Worksheets;

sheetsStruct = new OrderedMap;

linecount = 1;
do "oradump.txt" as lines for ( loopIndex() > 1 ){
   temparray = $_.parseFixedPosition( 14 , 25 , 15 , 5 );
// Flat file formats change, make your scripts robuster by explicitly assigning values
   project = temparray[0];
   billcode = temparray[1];
   consultant = temparray[2];
   hours = temparray[3];
   if( sheetsStruct.has( billcode ) ){
      xlworksheet = sheetsStruct.get( billcode );
   }else{
      xlworksheet = xlworksheets.add();
      xlworksheet.name = billcode;
      sheetsStruct.add( billcode , xlworksheet );
   }
}

This doesnt do much yet, it just opens the files, checks out the billing types and creates one sheet per billing type. It also creates a structure where I hold the collection of the sheets, accessible by their name. Now I also want to add the lines that I read from the file.

I add this line : addLine( xlworksheet , project , consultant, hours );

and the corresponding function

function addLine ws , project , consultant , hours {
  //Activate the sheet
  ws.Activate();   
  //Get the current row, we used the activecell for this
  therow = xlapp.ActiveCell.Row;
  //Fill in the values
  ws.cells[ "Item" , therow , 1].Value = project;
  ws.cells[ "Item" , therow , 2].Value = consultant;
  ws.cells[ "Item" , therow , 3].Value = hours;
  //Set the new location of the active cell
  ws.cells[ "Item"  , therow+1 , 1 ].Activate();
}

Reading the help of the Object Range and checking out the methods and properties is important when you script Excel. Here I use the most important property 'Value' to set the value of a certain cell. I also use the method Activate() to change the current worksheet and to change the current active cell.

Also note the special notation : ws.cells[ "Item" , therow+1 , 1 ] , this is how a cell is accessed.

Now , this dumps the data in the excel sheets, but the columns are not adjusted to their content, for this I add the following call
fixColumns();

Which matches this function :

function fixColumns(){
  for ws in ::sheetsStruct.values(){
    ws.cells.EntireColumn.AutoFit();
  }
}

Again, we just apply the correct calls that you can find in the API, I still gave you this one, because it is what customers expect.

The entire source code is then as follows :

xlapp = new com::Excel.Application;
xlapp.Visible = true;
xlworkbooks = xlapp.Workbooks;
xlworkbook  = xlworkbooks.add();
xlworksheets = xlworkbook.Worksheets;

sheetsStruct = new OrderedMap;

linecount = 1;
do "oradump.txt" as lines{
   temparray = $_.parseFixedPosition( 14 , 25 , 15 , 5 );
// Flat file formats change, make your scripts robuster by explicitly assigning values
   project = temparray[0];
   billcode = temparray[1];
   consultant = temparray[2];
   hours = temparray[3];
   if( sheetsStruct.has( billcode ) ){
      xlworksheet = sheetsStruct.get( billcode );
   }else{
      xlworksheet = xlworksheets.add();
      xlworksheet.name = billcode;
      sheetsStruct.add( billcode , xlworksheet );
   }
   addLine( xlworksheet , project , consultant, hours );
}
fixColumns();


function addLine ws , project , consultant , hours {
  ws.Activate();   
  therow = xlapp.ActiveCell.Row;
  ws.cells[ "Item" , therow , 1].Value = project;
  ws.cells[ "Item" , therow , 2].Value = consultant;
  ws.cells[ "Item" , therow , 3].Value = hours;
  ws.cells[ "Item"  , therow+1 , 1 ].Activate();
}

function fixColumns(){
  for ws in ::sheetsStruct.values(){
    ws.cells.EntireColumn.AutoFit();
  }
}

This still doesnt do the sorting and making totals, that's for another time. And even then it still doesnt make a lot of sense for Karin, since any VBa script could do this, it's the Oracle part that makes her go whoops, and that's also for another time...

If you have any comments on the article, feel free to email me at konijn@gmail.com.
If you have questions on JudoScript, feel free to join http://groups.yahoo.com/group/judoscript/

-- Main.tdemuyt - 16 Nov 2004

Discussion about ExcelScriptingArticle