use of com.sun.jna.platform.win32.COM.util.office.excel.ComIWorksheet 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());
}
use of com.sun.jna.platform.win32.COM.util.office.excel.ComIWorksheet in project jna by java-native-access.
the class Excelautomation_KB_219151_Mod method main.
public static void main(String[] args) throws IOException {
// Initialize COM Subsystem
Ole32.INSTANCE.CoInitializeEx(Pointer.NULL, Ole32.COINIT_MULTITHREADED);
// Initialize Factory for COM object creation
Factory fact = new Factory();
// Set LCID for calls to english locale. Without this formulas need
// to be specified in the users locale.
fact.setLCID(new LCID(0x0409));
try {
// Start excel application
ComExcel_Application excel = fact.createObject(ComExcel_Application.class);
ComIApplication excelApp = excel.queryInterface(ComIApplication.class);
// Set visiblite of application
excelApp.setVisible(true);
Helper.sleep(5);
// Get a new workbook.
ComIWorkbook wb = excelApp.getWorkbooks().Add();
ComIWorksheet sheet = wb.getActiveSheet();
// Add table headers going cell by cell.
sheet.getCells().getItem(1, 1).setValue("First Name");
sheet.getCells().getItem(1, 2).setValue("Last Name");
sheet.getCells().getItem(1, 3).setValue("Full Name");
sheet.getCells().getItem(1, 4).setValue("Salary");
// Create an array to set multiple values at once.
SAFEARRAY saNames = safeVariantArrayFromJava(new String[][] { { "John", "Smith" }, { "Tom", "Brown" }, { "Sue", "Thomas" }, { "Jane", "Jones" }, { "Adam", "Johnson" } });
// Fill A2:B6 with an array of values (First and Last Names).
VARIANT valueHolder = new VARIANT();
valueHolder.setValue(Variant.VT_ARRAY | Variant.VT_VARIANT, saNames);
sheet.getRange("A2", "B6").setValue(valueHolder);
saNames.destroy();
// Fill C2:C6 with a relative formula (=A2 & " " & B2).
sheet.getRange("C2", "C6").setFormula("= A2 & \" \" & B2");
// Fill D2:D6 with a formula(=RAND()*100000) and apply format.
sheet.getRange("D2", "D6").setFormula("=RAND()*100000");
sheet.getRange("D2", "D6").setNumberFormat("$0.00");
// AutoFit columns A:D.
sheet.getRange("A1", "D2").getEntireColumn().AutoFit();
displayQuaterlySales(sheet);
File tempFile = Helper.createNotExistingFile("exceloutput", ".xlsx");
System.out.println("Writing output to: " + tempFile.getAbsolutePath());
wb.SaveAs(tempFile.getAbsolutePath());
excelApp.setUserControl(true);
} finally {
fact.disposeAll();
Ole32.INSTANCE.CoUninitialize();
}
System.exit(0);
}
use of com.sun.jna.platform.win32.COM.util.office.excel.ComIWorksheet 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();
}
}
}
Aggregations