HOWTO: Create/Format an Excel Workbook Using Visual J++ Automation

• Microsoft Excel 2000
• Microsoft Excel 97 for Windows
• Microsoft Visual J++, version 6.0

SUMMARY

This article describes Automating Excel 97 or Excel 2000 from a Visual J++ client using the Java programming language. The project uses the Excel type library, and illustrates object oriented programming.

MORE INFORMATION

This article parallels a Microsoft Knowledge Base article that describes the same process using Visual C++ and Microsoft Foundation Classes. For more information regarding Automation of Excel from Visual C++, please see the following:

Q179706 HowTo: Use MFC to Automate Excel and Create and Format a Workbook

Steps to Build a Visual J++ Automation Client

1. Start Visual J++ 6.0. Create a new Console Application project and name it XLJava1.



2. In the Project-Explorer window, open your Project tree and double-click the Class1.java file created for you.



3. From the Project menu, choose Add COM Wrapper, select the 'Microsoft Excel 8.0 (or 9.0 as is appropriate) Object Library,' and then click OK. This adds Java COM Wrappers that are derived from the Excel type library to your project.



4. At the top of your Class1.Java file, add the following import statements:




   import Excel8.*; // Change the 8 to 9 for Excel 2000
   // For Excel 2000 the statement should read
   // import Excel9.*; // Excel support
   import com.ms.com.*; // Variant & exception support. 
   import java.lang.InterruptedException; // Needed for Thread.sleep().
   (These are illustrated in the code sample below.) 

5. In the Class1 entry point and the main() function, add code so that your program appears as follows:
   
   

   
      /**
      * This class can take a variable number of parameters on the command
      * line. Program execution begins with the main() method. The class
      * constructor is not invoked unless an object of type 'Class1'
      * created in the main() method.
      */ 
      import excel8.*; // or - import excel9.*; for Excel 2000
      import java.lang.InterruptedException; // needed for thread.sleep
      import com.ms.com.*; // Variant & exception support<BR/>
   		 
      public class Class1
      {
      /**
      * The main entry point for the application. 
      *
      * @param args Array of parameters passed to the application
      * via the command line.
      * 
      */ 
   	
      //===================================================================
      // Add the following static member function declaration 
      // just before main()...
      // J/Direct declarations... to use Windows MessageBox
      /** @dll.import("USER32") */ 
      private static native int MessageBox(int hwndOwner, String text, 
                                           String title, int fuStyle);
      // Here is an example of its use.
      // MessageBox(0, "Hello, World", "Title", 0); //fuStyle switch
      //    values are defined in Winuser.h. Search on 'MB_OK'
      //==============================================================
   
      public static void main (String[] args)
      {
      // Force COM objects to be created on the current thread.
      // Otherwise, older VMs might not release all references
      // and Excel might continue to run after you shutdown.
      ComLib.declareMessagePumpThread();
   
      // Launch Excel 
      Variant vEmpty = new Variant();
      vEmpty.noParam();
      Application xlApp = new Application();
      xlApp.setVisible(0,true); // 1st param is LCID, Locale ID
   	
      // Get the workbook object via the object model.
      Workbooks books = xlApp.getWorkbooks();
      _Workbook book = books.Add(vEmpty,0);
   
      // Get the first sheet.		
      Sheets sheets = (Sheets)book.getSheets();
      _Worksheet sheet = (_Worksheet)sheets.getItem(new Variant(1));
   
      // Fill cells A1, B1, C1, and D1, one cell at a time, with "headers".
      Range range = sheet.getRange(new Variant("A1"),new Variant("A1"));
      range.setValue(new Variant("First Name"));
      range = sheet.getRange(new Variant("B1"),new Variant("B1"));
      range.setValue(new Variant("Last Name"));
      range = sheet.getRange(new Variant("C1"),new Variant("C1"));
      range.setValue(new Variant("Full Name"));
      range = sheet.getRange(new Variant("D1"),new Variant("D1"));
      range.setValue(new Variant("Salary"));
   
      // Format A1:D1 as bold, vertical alignment = center
      range = sheet.getRange(new Variant("A1"),new Variant("D1"));
      Font font = range.getFont();
      font.setBold(new Variant(true));
      range.setVerticalAlignment(new Variant(-4108));
   
      // Fill range A2:B6 with first and last names, 
      range = sheet.getRange(new Variant("A2"),new Variant("A2"));
      range.setValue(new Variant("John"));
      range = sheet.getRange(new Variant("B2"),new Variant("B2"));
      range.setValue(new Variant("Smith"));
      range = sheet.getRange(new Variant("A3"),new Variant("A3"));
      range.setValue(new Variant("Tom"));
      range = sheet.getRange(new Variant("B3"),new Variant("B3"));
      range.setValue(new Variant("Brown"));
      range = sheet.getRange(new Variant("A4"),new Variant("A4"));
      range.setValue(new Variant("Sue"));
      range = sheet.getRange(new Variant("B4"),new Variant("B4"));
      range.setValue(new Variant("Thomas"));
      range = sheet.getRange(new Variant("A5"),new Variant("A5"));
      range.setValue(new Variant("Jane"));
      range = sheet.getRange(new Variant("B5"),new Variant("B5"));
      range.setValue(new Variant("Jones"));
      range = sheet.getRange(new Variant("A6"),new Variant("A6"));
      range.setValue(new Variant("Adam"));
      range = sheet.getRange(new Variant("B6"),new Variant("B6"));
      range.setValue(new Variant("Johnson"));
   
      // Fill range C2:C6 with a relative formula(=A2 & " " & B2")
      range = sheet.getRange(new Variant("C2"),new Variant("C6"));
      range.setFormula(new Variant("=A2 & \" \" & B2"));
   
      // Fill D2:D6 with a formula (=RAND()*100000) and apply a 
      //  number format
      range = sheet.getRange(new Variant("D2"),new Variant("D6"));
      range.setFormula(new Variant("=RAND()*100000"));
      range.setNumberFormat(new Variant("$0.00"));
   
      // AutoFit columns A:D
      range = sheet.getRange(new Variant("A1"),new Variant("D1"));
      Range columns = range.getEntireColumn();
      columns.AutoFit();
   
      // Manipulate a variable number of columns
      //  for Quarterly Sales Data
      short NumQtrs;
      String msg;
      String msg2;
      msg2 = " "; // Initialize it to avoid compile error
      int reply;
      Range resizedrange;
      Interior interior;
      Borders borders;
   
      // Determine how many quarters to display data for.
      for(NumQtrs = 1; NumQtrs<=4; NumQtrs++)
      {
         if (NumQtrs == 1)
         {
          msg = "Enter sales data for " + NumQtrs + " quarter?";
          msg2 = "Displaying data for " + NumQtrs + " quarter";
         }
         else
         {
          msg = "Enter sales data for " + NumQtrs + " quarters?";
          msg2 = "Displaying data for " + NumQtrs + " quarters";
         }
         reply = MessageBox(0, msg, "Indicate Quarters", 36);
         if(6 == reply) break;			
      } // end For loop
   		
      MessageBox(0, msg2, "Data Setting", 0);
   
      if(5 == NumQtrs) // Limit display to <=4 quarters
      {
       NumQtrs = 4;
      }
   
      // Starting at E1, fill headers for number of columns selected.
      range = sheet.getRange(new Variant("E1"),new Variant("E1"));
      resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
      resizedrange.setFormula(
         new Variant("= \"Q\" & COLUMN()-4 & CHAR(10)& \"Sales\""));
   
      // Change the Orientation and Wrap Text properties for the headers.
      resizedrange.setOrientation(new Variant(38));
      resizedrange.setWrapText(new Variant(true));
   
      // Fill the interior colors of the headers.
      interior = resizedrange.getInterior();
      interior.setColorIndex(new Variant(36));
   
      // Fill the columns with a formula and apply a number format.
      range = sheet.getRange(new Variant("E2"),new Variant("E6"));
      resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
      resizedrange.setFormula(new Variant("=RAND()*100"));
      resizedrange.setNumberFormat(new Variant("$0.00"));
   
      // Apply Borders to the Sales data and headers
      range = sheet.getRange(new Variant("E1"), new Variant("E6"));
      resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
      borders = resizedrange.getBorders();
      borders.setWeight(new Variant(2)); // xlThin = 2
   
      // Add Totals formula for Quarterly sales data, apply a border.
      range = sheet.getRange(new Variant("E8"),new Variant("E8"));
      resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
      resizedrange.setFormula(new Variant("=SUM(E2:E6)"));
      borders = resizedrange.getBorders();
      Border bottomborder;
      bottomborder = borders.getItem(9); // Bottom border
      bottomborder.setLineStyle(new Variant(-4119)); // xlDouble = -4119
      bottomborder.setWeight(new Variant(4));  // xlThick = 4
   
      // Some more features: Add comment to cell C3 - Full 
      //    name of Tom Brown
      range = sheet.getRange(new Variant("C3"),new Variant("C3"));
      range.AddComment(new Variant("Tom was last month's top performer"));
   
      // Chart the Salaries.
      double Left = 72.; // location in points
      double Top = 144.;
      double Width = 350.;
      double Height = 250.;
      ChartObjects chartobjects = 
        (ChartObjects)sheet.ChartObjects(vEmpty,0); // explicit typecast
      ChartObject chartobject = chartobjects.Add(Left, Top, Width, Height);
   
      // Set the location and size.
      Chart chart = chartobject.getChart();
      range = sheet.getRange(new Variant("C2"), new Variant("D6"));
   
      // Full name and salary.
      range.Select();
      chart.ChartWizard(new Variant(range), // Source
                        new Variant(11),    // Gallery
                        vEmpty,             // Format
                        new Variant(1),     // PlotBy
                        new Variant(0),     // CategoryLabels
                        new Variant(1),     // SeriesLabels
                        new Variant(true),  // HasLegend
                        new Variant("Salary by Employee"), //Title
                        new Variant("Employee"), //CategoryTitle
                        new Variant("Salary"), //ValueTitle
                        vEmpty,             // ExtraTitle
                        0                   // LCID(localeID)
                        );
   		
      // Make the application visible and give the user control of 
      // Microsoft Excel.
      // xlApp.setVisible(0,true); // 1st param is LCID, Locale ID. 
      // The preceding was done at beginning of the program.
      xlApp.setUserControl(true);
   		
      MessageBox(0, "When Excel hides, Minimize VJ++ IDE to see Excel",
                 "Focus on Excel", 0);
   
      //book.setSaved(0,true); // Avoid "Save changes" dialog
      //xlApp.Quit();  // Not if using UserControl		
      }  // End of Main
      } // End of Class
    

   
   



6. From the Debug menu, click Start. The program builds, packages, and runs. When it runs, a Console window appears and some time later Excel appears. A dialog box prompting you on how may Quarters to chart will appear on top unless you click anywhere on your screen after the program starts. If you have clicked the screen the dialog box will be minimized on the Windows task bar. If the program appears to hang, restore to 'Normal' the dialog box that is on your task bar.

REFERENCES

For additional information about Visual J++ and Automation, please see the following articles in the Microsoft Knowledge Base:

Q169173 INFO: Frequently Asked Questions for Visual J++



Q169796 HOWTO: Automate Excel from Java



Q169802 SAMPLE: jword8.exe Demonstrates Automating Word 8 from Java



Q215484 HOWTO: Automate PowerPoint Using Visual J++

Q179706 HOWTO: Use MFC to Automate Excel and Create/Format a New Workbook

Q219151 HOWTO: Automate Excel from Visual Basic

Additional query words: jactivex Java Automation

Keywords          : kbAutomation kbExcel kbVJ kbVJ600 kbGrpDSO kbexcel2000 
Version           : WINDOWS:2000,6.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 12, 1999