use of com.sun.jna.platform.win32.COM.util.office.excel.Borders 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());
}
Aggregations