An introduction on how to control Excel 2003 with J# .NET

• Download source files - 7.5 Kb

Introduction

In this article, we will be focusing on how to control Microsoft Excel 2003 with the J# language through COM automation. We will be targeting the version 1.1 of the .NET framework (Visual Studio 2003).

Open Visual Studio and select a new J# Windows Application.

Add a reference to "Microsoft Excel 11.0 Object Library".

Add a Button, and a PictureBox Win form control to the application. Click the Button twice to implement a button handler.

The application instance

The most important classes are the application, workbook, worksheet and range classes. The application class represents the Excel application with workbooks. Workbooks have worksheets with cells. Cells can be represented by ranges.

To open an instance of Excel, type:

    Microsoft.Office.Interop.Excel.ApplicationClass excel =
       new Microsoft.Office.Interop.Excel.ApplicationClass();

This will not show Excel on the desktop, but run it in the background. If you want to make it visible on the desktop, set the following property to true. There are no differences other that Excel will be visible on the desktop. This can be useful while debugging.

excel.set_Visible( true );

If you want to exit Excel, type:

excel.Quit();

One very important property that will make your system run faster is to disable the screen updating while you do operations. It is very important that you enable the screen updating when you are done. Excel will not do this for you. Remember to switch on the screen updating in your code where it will run, regardless of exceptions.

    try 
    {
        excel.set_ScreenUpdating( false );
        // ... do excel operations here
    } 
    finally
    {
        excel.set_ScreenUpdating( true );
    }

Another very important property is the alert property. This property controls if Excel should display warning dialogs or not, like the 'Save as' dialog if you forget to save the workbook before quitting Excel. Just like the screen updating property, the alert property will not reset this property for you when you are done.

    try 
    {
        excel.set_DisplayAlerts( false );
        // do excel operations here...
    }
    finally
    {
        excel.set_DisplayAlerts( true );
    }

Excel has several other display properties you may want to take a look at. In the Visual Studio IDE, type excel.set_D to show all the properties.

If you want your users to be able to edit the workbooks, do the following command:

    excel.set_UserControl( true );

The workbook

As you probably have found out, you can not do much work without a workbook. The workbook collection enables you to open new work books, save work books, and other important operations.

Open a new workbook

To open a new workbook, do the following:

    Microsoft.Office.Interop.Excel.Workbook workbook = 
        excel.get_Workbooks().Add(System.Reflection.Missing.Value );

Close a workbook

When you want to close a workbook, type:

    workbook.Close( 
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value, 
        System.Reflection.Missing.Value );

This will assume you do not want to save any changes to the workbook. If you did any changes to the workbook, you can close it and save any changes with this command:

    workbook.Close( (System.Boolean)true, "C:\\test.xls", 
        System.Type.Missing );

Please notice the .NET boxing of the Java boolean type. The second argument is the filename.

Save, SaveAs and SaveCopyAs

To save the workbook, simply call:

    workbook.Save();

A more advanced save call is the SaveAs. This call enables you to specify a filename and path, file format, password, file access mode, and more. This example will save the workbook in the C:\test.xls as an Excel format with no password.

    workbook.SaveAs( "c:\\test.xls",
        Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value );

If you want to save a copy without changing the name of the workbook instance, simply do a SaveCopyAs call:

workbook.SaveCopyAs( "c:\\copy.xls" );

Printing a workbook

We have two useful methods for printing, preview and print. The preview method will show a window on what the paper will look like. The print method will print the specified pages in the workbook on a printer.

The preview method is as follows. Please notice the .NET boxing of the Java boolean.

    workbook.PrintPreview( (System.Boolean)false );

If you want to print the workbook:

    workbook.PrintOut( From page, To page, Copies, Preview, 
        ActivePrinter, PrintToFile, Collate, PrToFilename );

Calculate the workbook

Sometimes it is useful to calculate all workbooks in one command. That can be done with the following command:

    excel.Calculate();

The worksheet

The worksheets contain cells and are inside the workbook. You get a reference to the active worksheet with the get_Activesheet() command.

    Microsoft.Office.Interop.Excel.Worksheet sheet =
        excel.get_ActiveSheet();

Fill data in the cells

There are many ways to fill in the cells in a worksheet. One way is to use a two dimensional array.

Example one:

To fill in the numbers 1 to 10 in A1 to J1, do the following:

        int values[,] = new int[1,10];
        values[0,0] = 1;
        values[0,1] = 2;
        values[0,2] = 3;
        values[0,3] = 4;
        values[0,4] = 5;
        values[0,5] = 6;
        values[0,6] = 7;
        values[0,7] = 8;
        values[0,8] = 9;
        values[0,9] = 10;
        sheet.get_Range("A1", "J1").set_Value2( values );

Example two:

The code will look like this:

        int values[,] = new int[10,1];
        values[0,0] = 1;
        values[1,0] = 2;
        values[2,0] = 3;
        values[3,0] = 4;
        values[4,0] = 5;
        values[5,0] = 6;
        values[6,0] = 7;
        values[7,0] = 8;
        values[8,0] = 9;
        values[9,0] = 10;
        sheet.get_Range("A1", "A10").set_Value2( values );

Example three:

We will, in this example, make a 10 x 10 worksheet with a text headline and a 10x9 random integer array.

        String headline[,] = new String[1,10];
        headline[0,0] = new String("Dave");
        headline[0,1] = new String("Tom");
        headline[0,2] = new String("Thomas");
        headline[0,3] = new String("Michael");
        headline[0,4] = new String("Bob");
        headline[0,5] = new String("John");
        headline[0,6] = new String("Peter");
        headline[0,7] = new String("Lars");
        headline[0,8] = new String("Jay");
        headline[0,9] = new String("Brian");
        sheet.get_Range("A1", "J10").set_Value2( headline );

        int value[,] = new int[9,10];
        System.Random random = new System.Random( );
        for( int counterA = 0; counterA < 9 ; counterA++ )
            for( int counterB = 0; counterB < 10; counterB++ )
                value[counterA, counterB] = random.Next(100);
        sheet.get_Range("A2", "J10").set_Value2( value );

Formulas

This example will add all numbers from A2 to A10 (B2 to B10, C2 to C10 and so on), and store the result in A11 (B11, C11 and so on) with bold text.

    Microsoft.Office.Interop.Excel.Font font = 
            sheet.get_Range("A11", "J11").get_Font();
    font.set_Bold( (System.Boolean)true );
    sheet.get_Range("A11", "J11").set_Formula("=SUM(A2..A10)");

Charts

Charts are useful for showing information graphically. To open a new chart, do:

    Microsoft.Office.Interop.Excel.Chart chart = 
        (Microsoft.Office.Interop.Excel.Chart)excel.get_Charts().Add( 
            System.Reflection.Missing.Value, 
            System.Reflection.Missing.Value, 
            System.Reflection.Missing.Value, 
            System.Reflection.Missing.Value );


    chart.ChartWizard( sheet.get_Range("A1", "J10"), 

    Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, 
        System.Reflection.Missing.Value, 
        Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 
        System.Reflection.Missing.Value, 
        System.Reflection.Missing.Value, 
        System.Reflection.Missing.Value, 
        "Sales", 
        "Employee", 
        "Export in percent", 
        System.Reflection.Missing.Value );

Move the chart into the sheet with:

chart.Location(
  Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, 
              sheet.get_Name() );

To move the chart on the sheet, do:

    sheet.get_Shapes().Item("Chart 1").set_Left( 2.10f );
    sheet.get_Shapes().Item("Chart 1").set_Top( 150.0f );

Resize the chart with:

    sheet.get_Shapes().Item("Chart 1").set_Width( 500.0f );
    sheet.get_Shapes().Item("Chart 1").set_Height( 300.0f );

One interesting method is the CopyPicture(). This method allows you to copy anything from a sheet and store it on the clipboard, e.g., as a bitmap. This bitmap can be copied into any J# project for further processing and conversions.

    sheet.get_Shapes().Item("Chart 1").CopyPicture(
        Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen,
        Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap );

If you add a PictureBox control to your application, you can paste the chart into the J# application with the following code:

    System.Drawing.Bitmap bitmap =  new System.Drawing.Bitmap( 
        (System.Drawing.Image)Clipboard.GetDataObject().GetData(
        System.Windows.Forms.DataFormats.Bitmap ));
    this.pictureBox1.set_Image( bitmap );

References

• MSDN.
• VBA Language References.
• Excel developers home.