Difference between revisions of "Documentation/DevGuide/Spreadsheets/Example: Editing Spreadsheet Cells"
From Apache OpenOffice Wiki
< Documentation | DevGuide
OOoWikiBot (Talk | contribs) m (FINAL VERSION FOR L10N) |
|||
(18 intermediate revisions by 3 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]--> | ||
− | + | <syntaxhighlight lang="Java"> | |
void cellWork(XSpreadsheet xRange) { | void cellWork(XSpreadsheet xRange) { | ||
Line 41: | Line 41: | ||
com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor(); | com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor(); | ||
xCellText.insertString(xTextCursor, aText, false); | 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); | ||
+ | } | ||
+ | |||
} | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | 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 | ||
+ | |||
+ | ! Attribute Name | ||
+ | ! Type | ||
+ | ! Remark | ||
+ | |- | ||
+ | | AbsoluteName | ||
+ | | string | ||
+ | | READONLY | ||
+ | |- | ||
+ | | AsianVerticalMode | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | BottomBorder | ||
+ | | <idl>com.sun.star.table.BorderLine</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CellBackColor | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | CellProtection | ||
+ | | <idl>com.sun.star.util.CellProtection</idl> | ||
+ | | | ||
+ | |- | ||
+ | | 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 | ||
+ | | <idl>com.sun.star.lang.Locale</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CharLocaleAsian | ||
+ | | <idl>com.sun.star.lang.Locale</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CharLocaleComplex | ||
+ | | <idl>com.sun.star.lang.Locale</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CharPosture | ||
+ | | <idl>com.sun.star.awt.FontSlant</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CharPostureAsian | ||
+ | | <idl>com.sun.star.awt.FontSlant</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CharPostureComplex | ||
+ | | <idl>com.sun.star.awt.FontSlant</idl> | ||
+ | | | ||
+ | |- | ||
+ | | CharRelief | ||
+ | | short | ||
+ | | | ||
+ | |- | ||
+ | | CharShadowed | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | CharStrikeout | ||
+ | | short | ||
+ | | | ||
+ | |- | ||
+ | | CharUnderline | ||
+ | | short | ||
+ | | | ||
+ | |- | ||
+ | | CharUnderlineColor | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | CharUnderlineHasColor | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | CharWeight | ||
+ | | float | ||
+ | | see also <idl>com.sun.star.awt.FontWeight</idl> | ||
+ | |- | ||
+ | | CharWeightAsian | ||
+ | | float | ||
+ | | see also <idl>com.sun.star.awt.FontWeight</idl> | ||
+ | |- | ||
+ | | CharWeightComplex | ||
+ | | float | ||
+ | | see also <idl>com.sun.star.awt.FontWeight</idl> | ||
+ | |- | ||
+ | | CharWordMode | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ChartColumnAsLabel | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ChartRowAsLabel | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ConditionalFormat | ||
+ | | <idl>com.sun.star.sheet.XSheetConditionalEntries</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ConditionalFormatLocal | ||
+ | | <idl>com.sun.star.sheet.XSheetConditionalEntries</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ConditionalFormatXML | ||
+ | | <idl>com.sun.star.sheet.XSheetConditionalEntries</idl> | ||
+ | | READONLY | ||
+ | |- | ||
+ | | DiagonalBLTR | ||
+ | | <idl>com.sun.star.table.BorderLine</idl> | ||
+ | | | ||
+ | |- | ||
+ | | DiagonalTLBR | ||
+ | | <idl>com.sun.star.table.BorderLine</idl> | ||
+ | | | ||
+ | |- | ||
+ | | FormulaLocal | ||
+ | | string | ||
+ | | | ||
+ | |- | ||
+ | | FormulaResultType | ||
+ | | <idl>com.sun.star.table.CellContentType</idl> | ||
+ | | READONLY | ||
+ | |- | ||
+ | | HoriJustify | ||
+ | | <idl>com.sun.star.table.CellHoriJustify</idl> | ||
+ | | | ||
+ | |- | ||
+ | | IsCellBackgroundTransparent | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | IsTextWrapped | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | LeftBorder | ||
+ | | <idl>com.sun.star.table.BorderLine</idl> | ||
+ | | | ||
+ | |- | ||
+ | | NumberFormat | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | NumberingRules | ||
+ | | <idl>com.sun.star.container.XIndexReplace</idl> | ||
+ | | | ||
+ | |- | ||
+ | | Orientation | ||
+ | | <idl>com.sun.star.table.CellOrientation</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ParaAdjust | ||
+ | | short | ||
+ | | | ||
+ | |- | ||
+ | | ParaBottomMargin | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | ParaIndent | ||
+ | | short | ||
+ | | | ||
+ | |- | ||
+ | | ParaIsCharacterDistance | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ParaIsForbiddenRules | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ParaIsHangingPunctuation | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ParaIsHyphenation | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | ParaLastLineAdjust | ||
+ | | short | ||
+ | | | ||
+ | |- | ||
+ | | ParaLeftMargin | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | ParaRightMargin | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | ParaTopMargin | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | Position | ||
+ | | <idl>com.sun.star.awt.Point</idl> | ||
+ | | READONLY | ||
+ | |- | ||
+ | | RightBorder | ||
+ | | <idl>com.sun.star.table.BorderLine</idl> | ||
+ | | | ||
+ | |- | ||
+ | | RotateAngle | ||
+ | | long | ||
+ | | | ||
+ | |- | ||
+ | | RotateReference | ||
+ | | <idl>com.sun.star.table.CellVertJustify</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ShadowFormat | ||
+ | | <idl>com.sun.star.table.ShadowFormat</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ShrinkToFit | ||
+ | | boolean | ||
+ | | | ||
+ | |- | ||
+ | | Size | ||
+ | | <idl>com.sun.star.awt.Size</idl> | ||
+ | | READONLY | ||
+ | |- | ||
+ | | TableBorder | ||
+ | | <idl>com.sun.star.table.TableBorder</idl> | ||
+ | | | ||
+ | |- | ||
+ | | TopBorder | ||
+ | | <idl>com.sun.star.table.BorderLine</idl> | ||
+ | | | ||
+ | |- | ||
+ | | UserDefinedAttributes | ||
+ | | <idl>com.sun.star.container.XNameContainer</idl> | ||
+ | | | ||
+ | |- | ||
+ | | Validation | ||
+ | | <idl>com.sun.star.beans.XPropertySet</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ValidationLocal | ||
+ | | <idl>com.sun.star.beans.XPropertySet</idl> | ||
+ | | | ||
+ | |- | ||
+ | | ValidationXML | ||
+ | | <idl>com.sun.star.beans.XPropertySet</idl> | ||
+ | | READONLY | ||
+ | |- | ||
+ | | VertJustify | ||
+ | | <idl>com.sun.star.table.CellVertJustify</idl> | ||
+ | | | ||
+ | |- | ||
+ | | WritingMode | ||
+ | | short | ||
+ | | | ||
+ | |} | ||
Latest revision as of 15:03, 3 January 2021
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). |