[Solved] Writing string to a cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

[Solved] Writing string to a cell

Post by gurkand »

Hi All,

I am writing data into a cell with the following commands:

Code: Select all

Sheet = Doc.Sheets.getByName("Constants")
Cell = Sheet.getCellByPosition(3,0)
Cell.Value = Name
Sheet = Doc.Sheets.getByName("Constants")
Cell = Sheet.getCellByPosition(4,0)
Cell.Value = Number
If I assign integer's to these variables, then all is good, they are written to the cell.

But, if I assign a text, then I receive only a 0 written into that cell.

I tried assigning alphanumeric values, and guess what, the output consists of only the numeric input I have had for the variable.

I tried formatting the output cell as Text, or Dim'ing the variables, with no success.

What am I doing wrong? :crazy:

Thanks in advance,

Regards.
Last edited by gurkand on Wed Feb 28, 2018 9:48 pm, edited 1 time in total.
LibreOffice 6.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Writing string to a cell

Post by Villeroy »

Every cell has one value, one string and one formula at the same time.
A number format must not change the value or formula. A number format changes the appearance of a value but never the value itself.
All cell values are either text or numbers. Formulas may return error as a third type.
Any booleans, dates, times etc. are formatted numbers. 2018-02-28 and 43159. are formattings of the exact same cell value.

The type of value, the value and the string depend on the cell formula.
The value is the numeric result of the formula. The text is the visual representation of the cell's value according to the formatting. The formula can be seen as the "true content" which determines everything.

A blank cell has a zero value and "" as string and formula.
A cell with formula '1 (leading apostrophe) shows the text "1" and has the value 0. This is what you get from c.setString("1") or c.setFormula("'1")
A cell with formula 1 (no apostrophe) has a value of 1 and the text may be anything dependent of the format. This is what you get from c.setValue(1) or c.setFormula("1").
A cell with formula '=SUM(A1:B4) (leading apostrophe) shows the literal text =SUM(A1:B4) and has the value 0. c.setString("=SUM(A1:B4)") or c.setFormula("'=SUM(A1:B4)")
setValue(num) puts a number into the cell. The cell value is always a double, although you may use integers in your program.
setString(txt) puts a text into the cell. The value is reset to 0.
setFormual(txt) puts anything into the cell. A number ("1"), a text or a calculated expression (starting with "=").
Property FormulaLocal is the localized variant of the formula. It is the string you see in the formula bar.

[UNO, Calc] How to fill spreadsheets cells programmatically
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Writing string to a cell

Post by Lupp »

The cell.Value property has the fix type 'Double'. You cannot assign a text to it. If you try a kind of automatic conversion will turn it into 0 (zero). The cell.Type will be set to 1 (meaning 'Number') anyway.

In the respective way you get a cell containing text and having cell.Type 2 (meaning 'Text') if you try to assign a number to the property cell.String. The number will be automatically converted into text using the default format.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Writing string to a cell

Post by gurkand »

I tried the following but received a "subproc or function not defined" error.

Sub Main
Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet1")
Cell = Sheet.getCellByPosition(0,0)
setString(txt)
End Sub

I bet I must be including something but how? :?:
LibreOffice 6.3
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Writing string to a cell

Post by RoryOF »

Look up setString() in the API. It is at
https://www.openoffice.org/api/docs/com ... Range.html
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Writing string to a cell

Post by Villeroy »

First of all, you have to learn programming in any language. This is not something that can be accomplished by copy/paste or try/error. Usually you need a book or two.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Writing string to a cell

Post by gurkand »

:D :D :D :D :D :D :D :D :D

I am a Computer Engineer since 1992.

Trust me that I know a few things about programming languages.

I am asking my questions as simple to understand and simple to reply. I am struggling to understand how hard can it be to tell me the answer, directly. Seeing the code part there it must be extremely easy...
LibreOffice 6.3
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Writing string to a cell

Post by Zizi64 »

Code: Select all

setString(txt)
What is the object related to the command "setString()"? Where you want to put the string to?
I know: that is the Cell object. But the Basic interpreter will not know it. You must inform the intepreter about it:

Code: Select all

Cell.setString(txt)
/where it "txt" is a string type variable with a valid string content./
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Writing string to a cell

Post by gurkand »

Many thanks Zizi.

Very helpful explanation and it is working now.
LibreOffice 6.3
Post Reply