Advanced spreadsheet

From NOA Documentation Wiki

Source: Jürgen Steinhilber (developer)

The following code shows some advanced methods for spread sheets:

public static void doColWidth(ISpreadsheetDocument spreadsheetDocument, String sheetName,
    int col_first, int col_last, int width) throws NoSuchElementException,
    WrappedTargetException, IndexOutOfBoundsException, UnknownPropertyException,
    PropertyVetoException, IllegalArgumentException {

  XSpreadsheets spreadsheets = spreadsheetDocument.getSpreadsheetDocument().getSheets();

  XSpreadsheet spreadsheet1 = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class,
      spreadsheets.getByName(sheetName));

  XCellRange xCellRange = spreadsheet1.getCellRangeByPosition(0, 0, col_last, 0);

  com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange)

  UnoRuntime.queryInterface(com.sun.star.table.XColumnRowRange.class, xCellRange);

  com.sun.star.beans.XPropertySet xPropSet = null;

  com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns();

  for (int i = col_first; i <= col_last; i++) {

    Object aColumnObj = xColumns.getByIndex(i);

    xPropSet = (com.sun.star.beans.XPropertySet)

    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aColumnObj);

    xPropSet.setPropertyValue("Width", width);

  }

}

public static void doColTextAlign(ISpreadsheetDocument spreadsheetDocument, String sheetName,
    int col_first, int col_last, int col_textalign) throws NoSuchElementException,
    WrappedTargetException, IndexOutOfBoundsException, UnknownPropertyException,
    PropertyVetoException, IllegalArgumentException {

  XSpreadsheets spreadsheets = spreadsheetDocument.getSpreadsheetDocument().getSheets();

  XSpreadsheet spreadsheet1 = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class,
      spreadsheets.getByName(sheetName));

  XCellRange xCellRange = spreadsheet1.getCellRangeByPosition(0, 0, col_last, 0);

  com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange)

  UnoRuntime.queryInterface(com.sun.star.table.XColumnRowRange.class, xCellRange);

  com.sun.star.beans.XPropertySet xPropSet = null;

  com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns();

  for (int i = col_first; i <= col_last; i++) {

    Object aColumnObj = xColumns.getByIndex(i);

    xPropSet = (com.sun.star.beans.XPropertySet)

    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aColumnObj);

    xPropSet.setPropertyValue("HoriJustify", col_textalign);

  }

}

public static void doColNumberFormat(ISpreadsheetDocument spreadsheetDocument, String sheetName,
    int col_first, int col_last, int col_numberformat) throws NoSuchElementException,
    WrappedTargetException, IndexOutOfBoundsException, UnknownPropertyException,
    PropertyVetoException, IllegalArgumentException {

  XSpreadsheets spreadsheets = spreadsheetDocument.getSpreadsheetDocument().getSheets();

  XSpreadsheet spreadsheet1 = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class,
      spreadsheets.getByName(sheetName));

  XCellRange xCellRange = spreadsheet1.getCellRangeByPosition(0, 0, col_last, 0);

  com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange)

  UnoRuntime.queryInterface(com.sun.star.table.XColumnRowRange.class, xCellRange);

  com.sun.star.beans.XPropertySet xPropSet = null;

  com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns();

  for (int i = col_first; i <= col_last; i++) {

    Object aColumnObj = xColumns.getByIndex(i);

    xPropSet = (com.sun.star.beans.XPropertySet)

    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aColumnObj);

    xPropSet.setPropertyValue("NumberFormat", col_numberformat);

  }

}

public static void doCellNumberFormat(XSheetCellCursor cellCursor, int col, int row,
    int cell_numberformat) throws WrappedTargetException, IndexOutOfBoundsException,
    UnknownPropertyException, PropertyVetoException, IllegalArgumentException {

  XCell cell = cellCursor.getCellByPosition(col, row);

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  com.sun.star.beans.XPropertySet xPropSet = null;

  xPropSet = (com.sun.star.beans.XPropertySet)

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  xPropSet.setPropertyValue("NumberFormat", cell_numberformat);

}

public static void doCellValue(XSheetCellCursor cellCursor, int col, int row, Object value)
    throws IndexOutOfBoundsException {

  XCell cell = cellCursor.getCellByPosition(col, row);

  XText cellText;

  if (value instanceof Double) {

    cell.setValue((Double) value);

  }
  else if (value instanceof String) {

    cellText = (XText) UnoRuntime.queryInterface(XText.class, cell);

    cellText.setString((String) value);

  }
}

public static void doCellFormula(XSheetCellCursor cellCursor, int col, int row, String formula)
    throws IndexOutOfBoundsException {

  XCell cell = cellCursor.getCellByPosition(col, row);

  cell.setFormula(formula);

}

public static void doCellColor(XSheetCellCursor cellCursor, int col, int row, int color)
    throws IndexOutOfBoundsException, UnknownPropertyException, PropertyVetoException,
    IllegalArgumentException, WrappedTargetException {

  XCell cell = cellCursor.getCellByPosition(col, row);

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  com.sun.star.beans.XPropertySet xPropSet = null;

  xPropSet = (com.sun.star.beans.XPropertySet)

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  xPropSet.setPropertyValue("CharColor", color);

  /* Beispiel für Auflistung der Property-Namen

  System.out.println("Start-CellPropertie*********************************");

  Property[] prop = xPropSet.getPropertySetInfo().getProperties();

  for(int i = 0; i < prop.length;i++){

  System.out.println(prop[i].Name);

  System.out.println(prop[i].Attributes);

  }

  System.out.println("End-CellPropertie*********************************");

  */

}

public static void doCellFontBold(XSheetCellCursor cellCursor, int col, int row)
    throws IndexOutOfBoundsException, UnknownPropertyException, PropertyVetoException,
    IllegalArgumentException, WrappedTargetException {

  XCell cell = cellCursor.getCellByPosition(col, row);

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  com.sun.star.beans.XPropertySet xPropSet = null;

  xPropSet = (com.sun.star.beans.XPropertySet)

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  xPropSet.setPropertyValue("CharWeight", com.sun.star.awt.FontWeight.BOLD);

  /* Beispiele für Fonthandling

  xPropSet.setPropertyValue("CharFontStyleName", new String("Times New 
  Roman"));

  xPropSet.setPropertyValue("CharWeight", new 
  Float(com.sun.star.awt.FontWeight.NORMAL));

  xPropSet.setPropertyValue("CharHeight", new Float(12));

  */

}

public static void doCellFontItalic(XSheetCellCursor cellCursor, int col, int row)
    throws IndexOutOfBoundsException, UnknownPropertyException, PropertyVetoException,
    IllegalArgumentException, WrappedTargetException {

  XCell cell = cellCursor.getCellByPosition(col, row);

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  com.sun.star.beans.XPropertySet xPropSet = null;

  xPropSet = (com.sun.star.beans.XPropertySet)

  UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, cell);

  xPropSet.setPropertyValue("CharPosture", com.sun.star.awt.FontSlant.ITALIC);

}

Partly, this is not NOA, but may become part of it.