This macro is to be run from the Calc program.
How does it work?
When the user starts the macro, a dialog is opened:
The user insets values for the upper and lower triangles, and clicks the submit button.
Then the macro draws two triangles in the cell selected. Each triangle is empty or contains a numeric value.
The program uses 3 main components:
- The spreadsheet document.
- A dialog.
- The draw-page part of the document, since graphics are not part of the cell contents. So, you would use a draw page to draw shapes.
The Code
This macro will create the dialog, add controls, and execute the dialog. A callback function will be attached to the submit button, and will draw the triangles.
Imports are not included; A link to the relevant data type will replace the import.
// Define the triangle type in the cell
enum TriangleType {
UPPER, LOWER
}
public class KakuroCell {
public XNameContainer m_xDlgModelNameContainer = null; // Allows to access controls by name.
public XControlContainer m_xDlgContainer = null; // The dialog contains controls, such as buttons, input fields, etc.
public XMultiServiceFactory m_xMSFDialogModel = null;
public XControl m_xDialogControl = null;
public XTopWindow m_xTopWindow;
public XComponentContext m_xContext;
public XModel m_doc;
public XSpreadsheetDocument m_spreadSheetDoc;
public XMultiServiceFactory doc_multiServiceFactory;
private void createDialog(XMultiComponentFactory _xMCF) {
try {
Object oDialogModel = _xMCF.createInstanceWithContext("com.sun.star.awt.UnoControlDialogModel", m_xContext);
// The XMultiServiceFactory of the dialog model is needed to instantiate the controls...
m_xMSFDialogModel = (XMultiServiceFactory) UnoRuntime.queryInterface(XMultiServiceFactory.class, oDialogModel);
// The named container is used to insert the created controls into...
m_xDlgModelNameContainer = (XNameContainer) UnoRuntime.queryInterface(XNameContainer.class, oDialogModel);
String[] sPropertyNames = new String[] {"Height", "Moveable", "Name","PositionX","PositionY", "Step", "TabIndex","Title","Width"};
Object[] oObjectValues = new Object[] { new Integer(129), Boolean.TRUE, "KakuroDialog", new Integer(95),new Integer(100), new Integer(1), new Short((short) 0), "Kakuro Cell", new Integer(149)};
setPropertyValues(sPropertyNames, oObjectValues);
// create the dialog...
Object oUnoDialog = _xMCF.createInstanceWithContext("com.sun.star.awt.UnoControlDialog", m_xContext);
m_xDialogControl = (XControl) UnoRuntime.queryInterface(XControl.class, oUnoDialog);
// The scope of the control container is public...
m_xDlgContainer = (XControlContainer) UnoRuntime.queryInterface(XControlContainer.class, oUnoDialog);
m_xTopWindow = (XTopWindow) UnoRuntime.queryInterface(XTopWindow.class, m_xDlgContainer);
// link the dialog and its model...
XControlModel xControlModel = (XControlModel) UnoRuntime.queryInterface(XControlModel.class, oDialogModel);
m_xDialogControl.setModel(xControlModel);
} catch (com.sun.star.uno.Exception exception) {
exception.printStackTrace(System.out);
}
}
// Define the dialog at the model - keep in mind to pass the property names in alphabetical order!
public XMultiComponentFactory m_xMCF;
public XWindowPeer m_xWindowPeer; // This will ensure a dialog is opened.
public void setPropertyValues(String[] PropertyNames, Object[] PropertyValues){
try{
XMultiPropertySet xMultiPropertySet = (XMultiPropertySet) UnoRuntime.queryInterface(XMultiPropertySet.class, m_xDlgModelNameContainer);
xMultiPropertySet.setPropertyValues(PropertyNames, PropertyValues);
} catch (com.sun.star.uno.Exception ex) {
ex.printStackTrace(System.out);
}}
public void insertSubmitButton(String name, String label, short tabIndex, int posX, int posY, int width, int height){
try {
Object oSubmitButton = m_xMSFDialogModel.createInstance("com.sun.star.awt.UnoControlButtonModel");
String[] sPropertyNames= new String[]{ "BackgroundColor", "DefaultButton", "Height", "Label", "Name", "PositionX", "PositionY", "PushButtonType", "TabIndex", "Toggle", "Width"};
Object [] oObjectValues=new Object[]{ new Integer(0x006e7f50), Boolean.FALSE, new Integer(height), label, name, new Integer(posX), new Integer(posY), new Short((short)com.sun.star.awt.PushButtonType.STANDARD_value), new Short(tabIndex), Boolean.TRUE, new Integer(width)};
XMultiPropertySet xSBModelMPSet = (XMultiPropertySet) UnoRuntime.queryInterface(XMultiPropertySet.class, oSubmitButton);
xSBModelMPSet.setPropertyValues(sPropertyNames, oObjectValues);
m_xDlgModelNameContainer.insertByName(name, xSBModelMPSet);
XControl xButtonControl = m_xDlgContainer.getControl(name);
XButton xButton = (XButton) UnoRuntime.queryInterface(XButton.class, xButtonControl);
xButton.addActionListener(new XActionListener() {
@Override
public void disposing(EventObject evtObj) {
// TODO Auto-generated method stub
}
@Override
public void actionPerformed(com.sun.star.awt.ActionEvent evt) {
// TODO Auto-generated method stub
try {
Object src=evt.Source;
XControl xControl=UnoRuntime.queryInterface(XXDialogControl.class, src);
Object controlModel=xControl.getModel();
XPropertySet ctlPropertySet=UnoRuntime.queryInterface(XPropertySet.class, controlModel);
String name=(String)ctlPropertySet.getPropertyValue("Name");
if (name.equals("Submit")){
ctlPropertySet.setPropertyValue("Enabled", Boolean.FALSE);
XControl upperCtl=m_xDlgContainer.getControl("UpperValue");
XControlModel upperModel = upperCtl.getModel();
XPropertySet upperPropertySet = UnoRuntime.queryInterface(XPropertySet.class, upperModel);
XControl lowerCtl=m_xDlgContainer.getControl("LowerValue");
XControlModel lowerModel = lowerCtl.getModel();
XPropertySet lowerPropertySet = UnoRuntime.queryInterface(XPropertySet.class, lowerModel);
String upperText = (String)upperPropertySet.getPropertyValue("Text");
String lowerText = (String)lowerPropertySet.getPropertyValue("Text");
int upperNumericValue = 0;
int lowerNumericValue = 0;
try {
upperNumericValue=Integer.parseInt(upperText);
} catch (NumberFormatException nfe) {
// Leave the numeric value zero
}
try {
lowerNumericValue=Integer.parseInt(lowerText);
} catch (NumberFormatException nfe) {
// Leave the numeric value zero
}
draw_cell (upperNumericValue, lowerNumericValue);
XDialog xDialog = (XDialog) UnoRuntime.queryInterface(XDialog.class, m_xDialogControl);
xDialog.endExecute();
}
} catch (Exception e){
e.printStackTrace();
}
}
});
} catch (Exception e){
e.printStackTrace();
}
}
// draw_cell - Get the cell's place and size, and draw the 2 triangles over it.
public void draw_cell (int upperNumericValue, int lowerNumericValue) throws Exception{
Object xSelection = m_doc.getCurrentSelection();
XCellRange xCellRange = UnoRuntime.queryInterface(XCellRange.class, xSelection);
XCell cell=null;
try {
cell=xCellRange.getCellByPosition(0, 0);
} catch (IndexOutOfBoundsException e) {
// TODO Auto-generated catch block
throw new Exception("No cells selected.");
}
XCellAddressable xCellAddressable=UnoRuntime.queryInterface(XCellAddressable.class, cell);
CellAddress cellAddress = xCellAddressable.getCellAddress();
short sheetIx = cellAddress.Sheet;
XSpreadsheets sheets = m_spreadSheetDoc.getSheets();
XIndexAccess xIndexAccess = UnoRuntime.queryInterface(XIndexAccess.class, sheets);
Object currentSheet = xIndexAccess.getByIndex(sheetIx);
// Get the draw page properties from the spreadsheet
XDrawPageSupplier xDrawPageSupplier=UnoRuntime.queryInterface(XDrawPageSupplier.class, currentSheet);
XDrawPage drawPage = xDrawPageSupplier.getDrawPage();
doc_multiServiceFactory = UnoRuntime.queryInterface(XMultiServiceFactory.class, m_doc);
XPropertySet cellPropertySet=UnoRuntime.queryInterface(XPropertySet.class, cell);
// Get the start point and the size of the cell.
Point pt=(Point)cellPropertySet.getPropertyValue("Position");
int ptx = pt.X;
int pty = pt.Y;
Size sz=(Size)cellPropertySet.getPropertyValue("Size");
int width = sz.Width;
int height = sz.Height;
drawTriangle (drawPage, cell, ptx, pty, width, height, upperNumericValue, TriangleType.UPPER);
drawTriangle (drawPage, cell, ptx, pty, width, height, lowerNumericValue, TriangleType.LOWER);
}
// Draw the triangle according to its coordinates and type, add its numeric value in it.
private void drawTriangle(XDrawPage drawPage, XCell cell, int ptx, int pty, int width,
int height, int numericValue, TriangleType triangleType) {
// TODO Auto-generated method stub
try {
Object shapeObj = doc_multiServiceFactory.createInstance("com.sun.star.drawing.ClosedBezierShape"); // This is the type supported for polygons.
XShape shape=UnoRuntime.queryInterface(XShape.class, shapeObj);
Point shapePos=new Point(ptx, pty);
shape.setPosition(shapePos);
drawPage.add(shape);
XPropertySet descriptor=UnoRuntime.queryInterface(XPropertySet.class, shape);
Point[][] points=new Point[1][3];
points[0][0]=new Point(ptx, pty);
points[0][1]=triangleType==TriangleType.LOWER ?
/* Yes */ new Point(ptx, pty + height) :
/* No */ new Point(ptx + width, pty);
points[0][2]=new Point(ptx+width, pty+height);
PolygonFlags flags[][] = new PolygonFlags[1][3];
flags[0][0] = flags[0][1] = flags[0][2] = PolygonFlags.NORMAL; // All points are normal because the shape contains no curves.
XText shapetext=UnoRuntime.queryInterface(XText.class, shapeObj);
PolyPolygonBezierCoords coords=new PolyPolygonBezierCoords();
coords.Flags = flags;
coords.Coordinates = points;
descriptor.setPropertyValue("PolyPolygonBezier", coords);
String shapeString = numericValue!=0 ? Integer.toString(numericValue) : "";
shapetext.setString(shapeString);
XPropertySet textPropertySet = UnoRuntime.queryInterface(XPropertySet.class, shapetext);
textPropertySet.setPropertyValue("CharHeight", 12.0);
textPropertySet.setPropertyValue("CharColor", new Integer(0xffffff));
textPropertySet.setPropertyValue("TextHorizontalAdjust",
triangleType == TriangleType.LOWER ?
/* Yes */ TextHorizontalAdjust.LEFT :
/* No */ TextHorizontalAdjust.RIGHT);
textPropertySet.setPropertyValue("TextVerticalAdjust",
triangleType == TriangleType.LOWER ?
/* Yes */ TextVerticalAdjust.BOTTOM :
/* No */ TextVerticalAdjust.TOP);
textPropertySet.setPropertyValue("CharHeightComplex", 12.0);
descriptor.setPropertyValue("Visible", true);
descriptor.setPropertyValue("FillColor", new Integer(0));
descriptor.setPropertyValue("Anchor", cell);
} catch (Exception e){
e.printStackTrace();
}
}
// More dialog controls:
public void insertNumericField(String name, String label, short tabIndex, int posX, int posY, int width, int height){
try {
Object oNumericField = m_xMSFDialogModel.createInstance("com.sun.star.awt.UnoControlEditModel");
String[] sPropertyNames= new String[]{ "Height", "Name", "PositionX", "PositionY", "TabIndex", "Width"};
Object [] oObjectValues=new Object[]{ new Integer(height), name, new Integer(posX), new Integer(posY), new Short(tabIndex), new Integer(width)};
XMultiPropertySet xNFModelMPSet = (XMultiPropertySet) UnoRuntime.queryInterface(XMultiPropertySet.class, oNumericField);
xNFModelMPSet.setPropertyValues(sPropertyNames, oObjectValues);
m_xDlgModelNameContainer.insertByName(name, xNFModelMPSet);
} catch (Exception e){
e.printStackTrace();
}
}
public void insertTextlabel(String name, String label, short tabIndex, int posX, int posY, int width, int height){
try {
Object oTextLabel = m_xMSFDialogModel.createInstance("com.sun.star.awt.UnoControlFixedTextModel");
String[] sPropertyNames= new String[]{ "Height", "Label", "Name", "PositionX", "PositionY", "TabIndex", "Width", "WritingMode"};
Object [] oObjectValues=new Object[]{ new Integer(height), label, name, new Integer(posX), new Integer(posY), new Short(tabIndex), new Integer(width), new Short(com.sun.star.text.WritingMode2.LR_TB)};
XMultiPropertySet xFTModelMPSet = (XMultiPropertySet) UnoRuntime.queryInterface(XMultiPropertySet.class, oTextLabel);
xFTModelMPSet.setPropertyValues(sPropertyNames, oObjectValues);
m_xDlgModelNameContainer.insertByName(name, xFTModelMPSet);
} catch (Exception e){
e.printStackTrace();
}
}
public void insertGroupBox (String id, short tabIndex, int posX, int posY, int width, int height){
try {
Object oGroupBox=null;
oGroupBox = m_xMSFDialogModel.createInstance("com.sun.star.awt.UnoControlGroupBoxModel");
String sName="FrameControl1";
String[] sPropertyNames= new String[]{ "Height", "Name", "PositionX", "PositionY", "Width", "WritingMode"};
Object [] oObjectValues=new Object[]{ new Integer(height), sName, new Integer(posX), new Integer(posY), new Integer(width), new Short(com.sun.star.text.WritingMode2.LR_TB)};
XMultiPropertySet xGBModelMPSet = (XMultiPropertySet) UnoRuntime.queryInterface(XMultiPropertySet.class, oGroupBox);
xGBModelMPSet.setPropertyValues(sPropertyNames, oObjectValues);
m_xDlgModelNameContainer.insertByName(sName, xGBModelMPSet);
XPropertySet xGBPSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, oGroupBox);
xGBPSet.setPropertyValue("Label", "Kakuro Values");
} catch (Exception e){
e.printStackTrace();
}
}
public short executeDialog() throws Exception{
XWindow xWindow = (XWindow) UnoRuntime.queryInterface(XWindow.class, m_xDlgContainer);
// set the dialog invisible until it is executed
xWindow.setVisible(false);
Object oToolkit = m_xMCF.createInstanceWithContext("com.sun.star.awt.Toolkit", m_xContext);
XToolkit xToolkit = (XToolkit) UnoRuntime.queryInterface(XToolkit.class, oToolkit);
XWindowPeer xWindowParentPeer = xToolkit.getDesktopWindow();
m_xDialogControl.createPeer(xToolkit, xWindowParentPeer);
m_xWindowPeer = m_xDialogControl.getPeer();
XDialog xDialog = (XDialog) UnoRuntime.queryInterface(XDialog.class, m_xDialogControl);
XComponent xDialogComponent = (XComponent) UnoRuntime.queryInterface(XComponent.class, m_xDialogControl);
// the return value contains information about how the dialog has been closed...
short nReturnValue = xDialog.execute();
// free the resources...
xDialogComponent.dispose();
//xDialogComponent.dispose();
return nReturnValue;
}
// The entry point: this function will be called, when the user runs the macro.
public void start(XScriptContext xScriptContext) throws BootstrapException, Exception{
m_doc=xScriptContext.getDocument();
m_spreadSheetDoc = UnoRuntime.queryInterface(XSpreadsheetDocument.class, m_doc);
if (m_spreadSheetDoc == null)
throw new Exception("Invalid Document Type. Please call from a Calc document.");
m_xMCF = xScriptContext.getComponentContext().getServiceManager();
createDialog(m_xMCF);
insertGroupBox("abc", (short)0, 16, 20, 112, 80);
insertTextlabel("Label1", "Upper:", (short)-1, 22, 41, 22, 9);
insertNumericField("UpperValue", "", (short)1, 55, 40, 56, 12);
insertTextlabel("Label2", "Lower:", (short)-1, 22, 57, 26, 7);
insertNumericField("LowerValue", "666", (short)2, 55, 56, 56, 12);
insertSubmitButton("Submit", "Submit", (short)3, 48, 82, 46, 12);
executeDialog();
}
}
The Parcel Descriptor
<?xml version="1.0" encoding="UTF-8"?> <parcel language="Java" xmlns:parcel="scripting.dtd"> <script language="Java"> <locale lang="en"> <displayname value="Kakuro Cell"/> <description> Dialog Excercise </description> </locale> <functionname value="KakuroCell.start"/> <logicalname value="KakuroCell.start"/> <languagedepprops> <prop name="classpath" value="kakuro.jar"/> </languagedepprops> </script> </parcel>
PHP & More 