Example: Editing Spreadsheet Cells

From Apache OpenOffice Wiki
< Documentation‎ | DevGuide
Revision as of 07:35, 29 May 2015 by BMarcelly (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search



The method insertSpreadsheet() returns a com.sun.star.sheet.XSpreadsheet interface. This interface is passed to the method below, which shows how to access and modify the content and formatting of single cells. The interface com.sun.star.sheet.XSpreadsheet returned by insertSpreadsheet() is derived from com.sun.star.table.XCellRange. By working with it, cells can be accessed immediately using getCellByPosition():

  void cellWork(XSpreadsheet xRange) {
 
      com.sun.star.beans.XPropertySet xPropSet = null;
      com.sun.star.table.XCell xCell = null;
 
      // Access and modify a VALUE CELL
      xCell = xRange.getCellByPosition(0, 0);
      // Set cell value.
      xCell.setValue(1234);
 
      // Get cell value.
      double nDblValue = xCell.getValue() * 2;
      xRange.getCellByPosition(0, 1).setValue(nDblValue);
 
      // Create a FORMULA CELL
      xCell = xRange.getCellByPosition(0, 2);
      // Set formula string.
      xCell.setFormula("=1/0");
 
      // Get error type.
      boolean bValid = (xCell.getError() == 0);
      // Get formula string.
      String aText = "The formula " + xCell.getFormula() + " is ";
      aText += bValid ? "valid." : "erroneous.";
 
      // Insert a TEXT CELL using the XText interface
      xCell = xRange.getCellByPosition(0, 3);
      com.sun.star.text.XText xCellText = (com.sun.star.text.XText)
          UnoRuntime.queryInterface(com.sun.star.text.XText.class, xCell);
      com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();
      xCellText.insertString(xTextCursor, aText, false);
 
      //--------------------
      // get the cell properties
      //--------------------
 
      xPropSet = (com.sun.star.beans.XPropertySet)
        UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class,
        xCell);
 
      //--------------------
      // set the cell's background color
      //--------------------
      Integer bg_color = new Integer(0x008000);
      xPropSet.setPropertyValue("CellBackColor", bg_color);
 
      //--------------------
      // set the cell's foreground (text) color
      //--------------------
      Integer fg_color = new Integer(0x800000);
      xPropSet.setPropertyValue("CharColor", fg_color);
 
      //--------------------
      // set horizontal alignment to 'centered'
      //--------------------
      xPropSet.setPropertyValue("HoriJustify", com.sun.star.table.CellHoriJustify.CENTER);
 
      //--------------------
      // set vertical alignment to 'top'
      //--------------------
      xPropSet.setPropertyValue("VertJustify", com.sun.star.table.CellVertJustify.TOP);
 
      //--------------------
      // use bold font
      //--------------------
      xPropSet.setPropertyValue("CharWeight", com.sun.star.awt.FontWeight.BOLD);
      xPropSet.setPropertyValue("CharWeightAsian", com.sun.star.awt.FontWeight.BOLD);
      xPropSet.setPropertyValue("CharWeightComplex", com.sun.star.awt.FontWeight.BOLD);
 
      //--------------------
      // add a cell border      
      //--------------------
 
      // one point seems to be 1/72 inch
      double lineWidthInPoints = 2.5;
 
      // convert to units of 1/100 mm
      short integerLineWidth = (short) Math.round(2540 / 72.0 * lineWidthInPoints);
 
      final String[] borderNames = {
        "LeftBorder", "RightBorder", "TopBorder", "BottomBorder"
      };
 
      for (int i = 0; i < borderNames.length; ++i)
      {
        com.sun.star.table.BorderLine borderLine = new com.sun.star.table.BorderLine();
        borderLine.OuterLineWidth = integerLineWidth;
        xPropSet.setPropertyValue(borderNames[i], borderLine);
      }
 
  }

See also Working with a Spreadsheet Document (Example).

Running an example showing how to find out which properties an object provides (with an object of type com.sun.star.table.XCell instead of mxDocCursor), one gets for example the following properties (OpenOffice.org 2.4):

Attribute Name Type Remark
AbsoluteName string READONLY
AsianVerticalMode boolean
BottomBorder com.sun.star.table.BorderLine
CellBackColor long
CellProtection com.sun.star.util.CellProtection
CellStyle string
CharColor long
CharContoured boolean
CharCrossedOut boolean
CharEmphasis short
CharFont short
CharFontCharSet short
CharFontCharSetAsian short
CharFontCharSetComplex short
CharFontFamily short
CharFontFamilyAsian short
CharFontFamilyComplex short
CharFontName string
CharFontNameAsian string
CharFontNameComplex string
CharFontPitch short
CharFontPitchAsian short
CharFontPitchComplex short
CharFontStyleName string
CharFontStyleNameAsian string
CharFontStyleNameComplex string
CharHeight float
CharHeightAsian float
CharHeightComplex float
CharLocale com.sun.star.lang.Locale
CharLocaleAsian com.sun.star.lang.Locale
CharLocaleComplex com.sun.star.lang.Locale
CharPosture com.sun.star.awt.FontSlant
CharPostureAsian com.sun.star.awt.FontSlant
CharPostureComplex com.sun.star.awt.FontSlant
CharRelief short
CharShadowed boolean
CharStrikeout short
CharUnderline short
CharUnderlineColor long
CharUnderlineHasColor boolean
CharWeight float see also com.sun.star.awt.FontWeight
CharWeightAsian float see also com.sun.star.awt.FontWeight
CharWeightComplex float see also com.sun.star.awt.FontWeight
CharWordMode boolean
ChartColumnAsLabel boolean
ChartRowAsLabel boolean
ConditionalFormat com.sun.star.sheet.XSheetConditionalEntries
ConditionalFormatLocal com.sun.star.sheet.XSheetConditionalEntries
ConditionalFormatXML com.sun.star.sheet.XSheetConditionalEntries READONLY
DiagonalBLTR com.sun.star.table.BorderLine
DiagonalTLBR com.sun.star.table.BorderLine
FormulaLocal string
FormulaResultType com.sun.star.table.CellContentType READONLY
HoriJustify com.sun.star.table.CellHoriJustify
IsCellBackgroundTransparent boolean
IsTextWrapped boolean
LeftBorder com.sun.star.table.BorderLine
NumberFormat long
NumberingRules com.sun.star.container.XIndexReplace
Orientation com.sun.star.table.CellOrientation
ParaAdjust short
ParaBottomMargin long
ParaIndent short
ParaIsCharacterDistance boolean
ParaIsForbiddenRules boolean
ParaIsHangingPunctuation boolean
ParaIsHyphenation boolean
ParaLastLineAdjust short
ParaLeftMargin long
ParaRightMargin long
ParaTopMargin long
Position com.sun.star.awt.Point READONLY
RightBorder com.sun.star.table.BorderLine
RotateAngle long
RotateReference com.sun.star.table.CellVertJustify
ShadowFormat com.sun.star.table.ShadowFormat
ShrinkToFit boolean
Size com.sun.star.awt.Size READONLY
TableBorder com.sun.star.table.TableBorder
TopBorder com.sun.star.table.BorderLine
UserDefinedAttributes com.sun.star.container.XNameContainer
Validation com.sun.star.beans.XPropertySet
ValidationLocal com.sun.star.beans.XPropertySet
ValidationXML com.sun.star.beans.XPropertySet READONLY
VertJustify com.sun.star.table.CellVertJustify
WritingMode short


Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages