[Solved] Writing string to a cell

Creating a macro - Writing a Script - Using the API

[Solved] Writing string to a cell

Postby gurkand » Wed Feb 28, 2018 10:46 am

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.
Last edited by gurkand on Wed Feb 28, 2018 9:48 pm, edited 1 time in total.
LibreOffice 6.3
gurkand
 
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Writing string to a cell

Postby Villeroy » Wed Feb 28, 2018 12:17 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Writing string to a cell

Postby Lupp » Wed Feb 28, 2018 6:43 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Writing string to a cell

Postby gurkand » Wed Feb 28, 2018 8:30 pm

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
gurkand
 
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Writing string to a cell

Postby RoryOF » Wed Feb 28, 2018 8:56 pm

Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29778
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Writing string to a cell

Postby Villeroy » Wed Feb 28, 2018 9:21 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Writing string to a cell

Postby gurkand » Wed Feb 28, 2018 9:25 pm

: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
gurkand
 
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Writing string to a cell

Postby Zizi64 » Wed Feb 28, 2018 9:44 pm

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./
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8486
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Writing string to a cell

Postby gurkand » Wed Feb 28, 2018 9:47 pm

Many thanks Zizi.

Very helpful explanation and it is working now.
LibreOffice 6.3
gurkand
 
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests