Page 1 of 1

[Solved] Writing string to a cell

PostPosted: Wed Feb 28, 2018 10:46 am
by gurkand
Hi All,

I am writing data into a cell with the following commands:
Code: Select all   Expand viewCollapse view
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.

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 12:17 pm
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

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 6:43 pm
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.

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 8:30 pm
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? :?:

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 8:56 pm
by RoryOF

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 9:21 pm
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.

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 9:25 pm
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...

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 9:44 pm
by Zizi64
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
Cell.setString(txt)

/where it "txt" is a string type variable with a valid string content./

Re: Writing string to a cell

PostPosted: Wed Feb 28, 2018 9:47 pm
by gurkand
Many thanks Zizi.

Very helpful explanation and it is working now.