Samples‎ > ‎MS Office‎ > ‎

Excel Integration Sample

import com.jniwrapper.Int32;
import com.jniwrapper.win32.automation.Automation;
import com.jniwrapper.win32.automation.IDispatch;
import com.jniwrapper.win32.automation.OfficeContainer;
import com.jniwrapper.win32.automation.OleContainer;
import com.jniwrapper.win32.automation.types.BStr;
import com.jniwrapper.win32.automation.types.Variant;
import com.jniwrapper.win32.com.ComException;
import com.jniwrapper.win32.ole.OleFunctions;
import com.sun.java.swing.plaf.windows.WindowsLookAndFeel;
import excel.excel.Range;
import excel.excel.Sheets;
import excel.excel._Application;
import excel.excel._Worksheet;
import excel.excel.impl._ApplicationImpl;
import excel.excel.impl._WorksheetImpl;
import operations.OfficeFileOperationsHandler;
import operations.OfficePrintHandler;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;


/**
 * This sample demonstrates the technique of embedding Excel application into a java application
 * using OleContainer and generated java stubs for Excel application.
 * <p/>
 * This sample requires generated stubs for COM type library:
 * Description: Microsoft Excel 11.0 Object Library
 * ProgID:      Excel.Sheet
 * GUID:        {00020813-0000-0000-C000-000000000046}
 * In the package: excel
 * <p/>
 * You can generate stubs using the Code Generator application.
 */
public class ExcelIntegrationSample extends JFrame
{
    private static final Dimension WINDOW_SIZE = new Dimension(720, 480);

    /**
     * ProgID of Excel document
     */
    private static final String DOCUMENT_PROGID = "Excel.Sheet";

    private OleContainer _container;

    public ExcelIntegrationSample()
    {
        super("ComfyJ - Excel Integration Sample");

        _container = new OfficeContainer();
        _container.createObject(DOCUMENT_PROGID);

        // setting this property instructs OleContainer instance to handle the "update" commands as "save" ones
        _container.setHandleUpdateAsSave(true);

        getContentPane().add(_container, BorderLayout.CENTER);

        // Enable open, save of excel document (Excel toolbar)
        _container.setFileOperationsHandler(new OfficeFileOperationsHandler(OfficeFileOperationsHandler.TYPE_EXCEL));

        // Enable printing, print preview (Excel toolbar)
        _container.setPrintDocumentHandler(new OfficePrintHandler());
    }

    /**
     * Returns instance of Excel application.
     *
     * @return instance of Excel application.
     */
    private _Application getApplication()
    {
        Automation automation = new Automation(_container.getOleObject());
        IDispatch dispApp = automation.getProperty("Application").getPdispVal();
        dispApp.setAutoDelete(false);

        _ApplicationImpl result = new _ApplicationImpl(dispApp);
        result.setAutoDelete(false);

        dispApp.release();

        return result;
    }

    /**
     * Modify embedded document using generated stubs.
     */
    public void modifyDocument()
    {
        System.out.println("Integration: modifying document...");
        try
        {
            _container.getOleMessageLoop().doInvokeAndWait(new Runnable()
            {
                public void run()
                {
                    _Application application = getApplication();
                    try
                    {
                        // get 1st worksheet
                        final Sheets worksheets = application.getWorksheets();
                        worksheets.setAutoDelete(false);
                        try
                        {
                            modifyFirstWorksheet(worksheets);
                        }
                        finally
                        {
                            worksheets.release();
                        }
                    }
                    finally
                    {
                        application.release();
                    }
                }
            });
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    /**
     * Modify 1st worksheet using generated stubs.
     *
     * @param worksheets worksheets of Excel document.
     */
    private void modifyFirstWorksheet(final Sheets worksheets)
    {
        IDispatch dispatch = worksheets.getItem(new Variant(1));
        dispatch.setAutoDelete(false);

        try
        {
            _Worksheet worksheet = new _WorksheetImpl(dispatch);
            worksheet.setAutoDelete(false);

            try
            {
                // set values in cells of worksheet
                setValue(worksheet, "C1", "Sum");
                setValue(worksheet, "A1", "Excel demo...");

                setValue(worksheet, "A2", "50");
                setValue(worksheet, "B2", "20");
                setValue(worksheet, "A3", "30");
                setValue(worksheet, "B3", "40");

                // set formulas for cells in worksheet
                setFormula(worksheet, "C2", "=A2+B2");
                setFormula(worksheet, "C3", "=A3+B3");

                // using optional parameters in dispatch call
//                saveAs(worksheet);
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                worksheet.release();
            }
        }
        finally
        {
            dispatch.release();
        }
    }

    private void saveAs(_Worksheet worksheet)
    {
        Variant variant = Variant.createUnspecifiedParameter();

        String outputPath = "e:\\newfile.xls";

        try
        {
            worksheet._SaveAs(new BStr(outputPath),
                    variant,
                    variant,
                    variant,
                    variant,
                    variant,
                    variant,
                    variant,
                    variant,
                    new Int32(0)
            );

            System.out.println("Saved");
        }
        catch (ComException e)
        {
            System.out.println("Can't save file " + outputPath);
        }
    }

    /**
     * Set value in cell of worksheet.
     *
     * @param worksheet modified worksheet
     * @param cell      coordinates of modified cell (f.e. "A1")
     * @param value     new value in cell
     */
    private void setValue(_Worksheet worksheet, String cell, String value)
    {
        Range range = worksheet.getRange(new Variant(cell), new Variant(cell));
        range.setAutoDelete(false);

        try
        {
            Automation automation = new Automation(range);
            try
            {
                automation.setProperty("Value", value);
            }
            finally
            {
                automation.release();
            }
        }
        finally
        {
            range.release();
        }
    }

    /**
     * Set formula for cell in worksheet.
     *
     * @param worksheet modified worksheet
     * @param cell      coordinates of cell (f.e. "A3")
     * @param value     formula (f.e. "=A1+A2")
     */
    private void setFormula(_Worksheet worksheet, String cell, String value)
    {
        Range range = worksheet.getRange(new Variant(cell), new Variant(cell));
        range.setAutoDelete(false);

        try
        {
            Automation automation = new Automation(range);
            try
            {
                automation.setProperty("Formula", value);
            }
            finally
            {
                automation.release();
            }
        }
        finally
        {
            range.release();
        }
    }

    private static void createGUI()
    {
        final ExcelIntegrationSample app = new ExcelIntegrationSample();
        app.setSize(WINDOW_SIZE);
        app.setLocationRelativeTo(null);
        app.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        try
        {
            UIManager.setLookAndFeel(new WindowsLookAndFeel());
            JPopupMenu.setDefaultLightWeightPopupEnabled(false);
        }
        catch (UnsupportedLookAndFeelException e)
        {
        }

        app.createMenu();

        app.addWindowListener(new WindowAdapter()
        {
            public void windowClosing(WindowEvent e)
            {
                app.exit();
            }
        });

        app.setVisible(true);
    }

    private void exit()
    {
        _container.destroyObject();
    }

    public static void main(String[] args)
    {
        createGUI();
    }

    private void createMenu()
    {
        JMenuBar menuBar = new JMenuBar();
        createFileMenu(menuBar);

        setJMenuBar(menuBar);
    }

    private JMenuItem modifyDocument;
    private JMenuItem showPrintPreview;
    private JMenuItem hidePrintPreview;

    private void createFileMenu(JMenuBar menuBar)
    {
        JMenu menu = new JMenu("File");
        modifyDocument = new JMenuItem(new AbstractAction("Modify document")
        {
            public void actionPerformed(ActionEvent e)
            {
                modifyDocument();
            }
        });
        hidePrintPreview = new JMenuItem(new AbstractAction("Hide Print Preview")
        {
            public void actionPerformed(ActionEvent e)
            {
                showPrintPreview.setEnabled(true);
                modifyDocument.setEnabled(true);
                hidePrintPreview.setEnabled(false);
                _container.getPrintDocumentHandler().closePrintPreview();
            }
        });
        hidePrintPreview.setEnabled(false);
        showPrintPreview = new JMenuItem(new AbstractAction("Show Print Preview")
        {
            public void actionPerformed(ActionEvent e)
            {
                showPrintPreview.setEnabled(false);
                modifyDocument.setEnabled(false);
                hidePrintPreview.setEnabled(true);
                _container.getPrintDocumentHandler().printPreview();
            }
        });
        JMenuItem exitItem = new JMenuItem(new AbstractAction("Exit")
        {
            public void actionPerformed(ActionEvent e)
            {
                exit();
                System.exit(0);
            }
        });

        menu.add(modifyDocument);
        menu.add(showPrintPreview);
        menu.add(hidePrintPreview);
        menu.addSeparator();
        menu.add(exitItem);
        menuBar.add(menu);
    }
}