Examples with ComIRange com.sun.jna.platform.win32.COM.util.office.excel.ComIRange used on opensource projects

Example 1 with ComIRange

use of com.sun.jna.platform.win32.COM.util.office.excel.ComIRange in project jna by java-native-access.

the class Excelautomation_KB_219151_Mod method displayQuaterlySales.

private static void displayQuaterlySales(ComIWorksheet sheet) {
    // Determine how many quarters to display data for.
    int iNumQtrs = 4;
    for (; iNumQtrs >= 2; iNumQtrs--) {
        JOptionPane pane = new JOptionPane(String.format("Enter sales data for %d quarter(s)?", iNumQtrs), JOptionPane.QUESTION_MESSAGE);
        pane.setOptionType(JOptionPane.YES_NO_OPTION);
        JDialog dialog = pane.createDialog("Input...");
        dialog.setAlwaysOnTop(true);
        dialog.show();
        if (((Integer) pane.getValue()) == JOptionPane.YES_OPTION) {
            break;
        }
    }
    JOptionPane.showMessageDialog(null, String.format("Displaying data for %d quarter(s).", iNumQtrs));
    // Starting at E1, fill headers for the number of columns selected.
    ComIRange oResizeRange = sheet.getRange("E1", "E1").getResize(VARIANT_MISSING, iNumQtrs);
    oResizeRange.setFormula("=\"Q\" & COLUMN() - 4 & CHAR(10) & \"Sales\"");
    // Change the Orientation and WrapText properties for the headers.
    oResizeRange.setOrientation(38);
    oResizeRange.setWrapText(true);
    // Fill the interior color of the headers.
    oResizeRange.getInterior().setColorIndex(36);
    // Fill the columns with a formula and apply a number format.
    oResizeRange = sheet.getRange("E2", "E6").getResize(VARIANT_MISSING, iNumQtrs);
    oResizeRange.setFormula("=RAND()*100");
    oResizeRange.setNumberFormat("$0.00");
    // Apply borders to the Sales data and headers.
    oResizeRange = sheet.getRange("E1", "E6").getResize(VARIANT_MISSING, iNumQtrs);
    oResizeRange.getBorders().setWeight(XlBorderWeight.xlThin);
    // Add a Totals formula for the sales data and apply a border.
    oResizeRange = sheet.getRange("E8", "E8").getResize(VARIANT_MISSING, iNumQtrs);
    oResizeRange.setFormula("=SUM(E2:E6)");
    Borders oResizeRangeBorders = oResizeRange.getBorders();
    oResizeRangeBorders.setLineStyle(XlLineStyle.xlDouble);
    oResizeRangeBorders.setWeight(XlBorderWeight.xlThick);
    // Add a Chart for the selected data
    oResizeRange = sheet.getRange("E2:E6").getResize(VARIANT_MISSING, iNumQtrs);
    Chart chart = sheet.getParent().getCharts().Add(VARIANT_MISSING, VARIANT_MISSING, VARIANT_MISSING, VARIANT_MISSING);
    // Java note: Assumption is, that VARIANT_MISSING is the correct indicator
    // for missing values, it turns out, NULL is correct in this case...
    chart.ChartWizard(oResizeRange, XlChartType.xl3DColumn, VARIANT_MISSING, XlRowCol.xlColumns, null, null, null, null, null, null, null);
    chart.SeriesCollection(1).setXValues(sheet.getRange("C2", "C6"));
    for (int i = 1; i <= iNumQtrs; i++) {
        chart.SeriesCollection(i).setName("=\"Q" + Integer.toString(i) + "\"");
    }
    chart.Location(XlChartLocation.xlLocationAsObject, sheet.getName());
    // Move the chart so as not to cover your data.
    Shape shape = sheet.getShapes().Item(1);
    shape.setTop(sheet.getRows(10).getTop());
    shape.setLeft(sheet.getColumns(2).getLeft());
}

Example 2 with ComIRange

use of com.sun.jna.platform.win32.COM.util.office.excel.ComIRange in project jna by java-native-access.

the class MSOfficeExcelDemo method testExcel.

public static void testExcel() throws IOException {
    File demoDocument = null;
    ComIApplication msExcel = null;
    Factory factory = new Factory();
    try {
        System.out.println("Files in temp dir: " + Helper.tempDir.getAbsolutePath());
        ComExcel_Application excelObject = factory.createObject(ComExcel_Application.class);
        msExcel = excelObject.queryInterface(ComIApplication.class);
        System.out.println("MSExcel version: " + msExcel.getVersion());
        msExcel.setVisible(true);
        Helper.sleep(5);
        demoDocument = Helper.createNotExistingFile("jnatest", ".xls");
        Helper.extractClasspathFileToReal("/com/sun/jna/platform/win32/COM/util/office/resources/jnatest.xls", demoDocument);
        ComIWorkbook workbook = msExcel.getWorkbooks().Open(demoDocument.getAbsolutePath());
        msExcel.getActiveSheet().getRange("A1").setValue("Hello from JNA!");
        // wait 1sec. before closing
        Helper.sleep(1);
        // Save document into temp and close
        File output = new File(Helper.tempDir, "jnatest.xls");
        output.delete();
        workbook.SaveAs(output.getAbsolutePath());
        msExcel.getActiveWorkbook().Close(false);
        //			// msExcel.newExcelBook();
        msExcel.getWorkbooks().Open(output.getAbsolutePath());
        msExcel.getActiveSheet().getRange("A2").setValue("Hello again from JNA!");
        class Listener extends AbstractComEventCallbackListener implements ComIAppEvents {

            volatile boolean SheetSelectionChange_called;

            @Override
            public void errorReceivingCallbackEvent(String message, Exception exception) {
            }

            @Override
            public void SheetSelectionChange(ComIWorksheet sheet, ComIRange target) {
                SheetSelectionChange_called = true;
            }
        }
        ;
        Listener listener = new Listener();
        IComEventCallbackCookie cookie = msExcel.advise(ComIAppEvents.class, listener);
        Helper.sleep(1);
        msExcel.getActiveSheet().getRange("A5").Activate();
        Helper.sleep(1);
        msExcel.unadvise(ComIAppEvents.class, cookie);
        System.out.println("Listener was fired: " + listener.SheetSelectionChange_called);
        // close and discard changes
        msExcel.getActiveWorkbook().Close(false);
    } finally {
        // Make sure the excel instance is shut down
        if (null != msExcel) {
            msExcel.Quit();
        }
        // Release all objects acquired by the factory
        factory.disposeAll();
        if (demoDocument != null && demoDocument.exists()) {
            demoDocument.delete();
        }
    }
}