Difference between revisions of "Documentation/DevGuide/Spreadsheets/Example: Editing Spreadsheet Cells"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (removed second 'end of table delimiter')
m (old fashioned table syntax caused copy problems)
(10 intermediate revisions by 2 users not shown)
Line 9: Line 9:
 
The method <code>insertSpreadsheet()</code> returns a <idl>com.sun.star.sheet.XSpreadsheet</idl> 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> returned by <code>insertSpreadsheet()</code> is derived from <idl>com.sun.star.table.XCellRange</idl>. By working with it, cells can be accessed immediately using <code>getCellByPosition()</code>:  
 
The method <code>insertSpreadsheet()</code> returns a <idl>com.sun.star.sheet.XSpreadsheet</idl> 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> returned by <code>insertSpreadsheet()</code> is derived from <idl>com.sun.star.table.XCellRange</idl>. By working with it, cells can be accessed immediately using <code>getCellByPosition()</code>:  
 
<!--[SOURCE:Spreadsheet/GeneralTableSample.java]-->
 
<!--[SOURCE:Spreadsheet/GeneralTableSample.java]-->
 
+
<source lang="Java">
 
   void cellWork(XSpreadsheet xRange) {
 
   void cellWork(XSpreadsheet xRange) {
 
    
 
    
Line 43: Line 43:
 
        
 
        
 
       //--------------------
 
       //--------------------
       // set the cell's background color
+
       // get the cell properties
 
       //--------------------
 
       //--------------------
 
        
 
        
      Integer bg_color = new Integer(0x008000);
 
 
       xPropSet = (com.sun.star.beans.XPropertySet)
 
       xPropSet = (com.sun.star.beans.XPropertySet)
 
         UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class,
 
         UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class,
 
         xCell);
 
         xCell);
 
        
 
        
 +
      //--------------------
 +
      // set the cell's background color
 +
      //--------------------
 +
      Integer bg_color = new Integer(0x008000);
 
       xPropSet.setPropertyValue("CellBackColor", bg_color);
 
       xPropSet.setPropertyValue("CellBackColor", bg_color);
 
        
 
        
Line 56: Line 59:
 
       // set the cell's foreground (text) color
 
       // set the cell's foreground (text) color
 
       //--------------------
 
       //--------------------
     
 
 
       Integer fg_color = new Integer(0x800000);
 
       Integer fg_color = new Integer(0x800000);
      xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class,
 
      xCell);
 
     
 
 
       xPropSet.setPropertyValue("CharColor", fg_color);
 
       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);
 +
      }
 +
     
 +
   }</source>
  
Running [[Documentation/DevGuide/ProUNO/Properties#Example:_find_out_which_properties_an_object_provides|an example showing how to find out which properties an object provides]] (with an object of type <idl>com.sun.star.table.XCell</idl> instead of mxDocCursor), one gets for example the following properties (OpenOffice.org 2.4):
+
See also [[Documentation/DevGuide/FirstSteps/Example:_Working_with_a_Spreadsheet_Document|Working with a Spreadsheet Document (Example)]].
 +
 
 +
Running [[Documentation/DevGuide/ProUNO/Properties#Example:_find_out_which_properties_an_object_provides|an example showing how to find out which properties an object provides]] (with an object of type <idl>com.sun.star.table.XCell</idl> instead of <code>mxDocCursor</code>), one gets for example the following properties (OpenOffice.org 2.4):
 +
 
 +
{|border="1" cellpadding=4 style="border-collapse:collapse;"
 +
 
 +
|-bgcolor=#EDEDED
  
{| class="wikitable"
 
|-
 
 
! Attribute Name
 
! Attribute Name
 
! Type
 
! Type
Line 238: Line 280:
 
| CharWeight
 
| CharWeight
 
| float
 
| float
|
+
| see also <idl>com.sun.star.awt.FontWeight</idl>
 
|-
 
|-
 
| CharWeightAsian
 
| CharWeightAsian
 
| float
 
| float
|
+
| see also <idl>com.sun.star.awt.FontWeight</idl>
 
|-
 
|-
 
| CharWeightComplex
 
| CharWeightComplex
 
| float
 
| float
|
+
| see also <idl>com.sun.star.awt.FontWeight</idl>
 
|-
 
|-
 
| CharWordMode
 
| CharWordMode

Revision as of 07:35, 29 May 2015



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