Visual Basic to OpenOffice Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Visual Basic to OpenOffice Calc

Post by Texmanage »

I exporting the data from vb.net application to open office calc. each column have different datatypes. how to set datatype for particular cell. All columns setting as text.
Also how to set cell border. Im new to this open office coding. if possible someone can provide a sample application.
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Visual Basic to Open Office Calc

Post by Villeroy »

A Calc spreadsheet knows text and numbers (double precision floating point numbers) as the only two data types. Formuals may return error values as a third data type. Everything is stored as doubles unless it is text. MS Excel knows booleans as another type. In Calc booleans are stored as 0 and 1. Floating point numbers with no decimals are displayed as integers. Dates are day numbers. Times are fractions of days. Every single cell can have any data type. There are no database fields nor database records in a spreadsheet. There are not even tables since any rectangle of cells can be used as a table and there can be many such rectangles on the same sheet and you may use cells without any tabular structure. In other words: spreadsheets are no databases, no matter how hard people try to misuse spreadsheets as databases.

The Base component can be connected to a wide variety of databases via ODBC or JDBC. The Base component serves as a bridge between connected databases on one side and office documents (text and spreadsheet) on the other side. Once you have a database connection, it is very easy to pull data into Calc sheets and Calc pivot tables. Pivot tables are the maximum of database functionality you can get from a spreadsheet if the source is a database or a database-like rectangle of sheet cells.
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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Visual Basic to Open Office Calc

Post by Villeroy »

Just in case you really need to fill typed spreadsheet cells programmatically, this has been explained hundreds of times on this forum. If you found this forum and can't find any documentation, examples, tools, links then you are not a programmer anyway.
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
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: Visual Basic to Open Office Calc

Post by Texmanage »

Thanks Villeroy.. Im new to this forum. I have searched. but got no results accordingly. if u r a programmer and cant a send a sample. its ok fine. How i showing in vb.net needs to shown exactly in spreadsheet also. Not trying to use as database. You not understand the query it seems. Its ur understanding level. Forums are to help people. so only posting here. There is CellContentType for datatypes. But in all examples, it have only to check. Not to assign datatype to cell. if you dont know the answer, someone who knows will reply.
OpenOffice 3.1 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

Also how to set cell border. Im new to this open office coding.
?????
Do you know anything about the Cell styles, the Templates and the API (Application Programming Interface?). If the answer is not, then we can not help you: then you MUST study these things before.
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.
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: Visual Basic to Open Office Calc

Post by Texmanage »

Im a vb.net programmer, just need to export data to open office calc. we already using MS Excel. In addition we like to provide in Open Office Calc. I dont know abt open office codings. thats y asking for any samples.
OpenOffice 3.1 on Windows 7
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Visual Basic to Open Office Calc

Post by robleyd »

I would suggest you get Andrew Pitonyak's book http://www.pitonyak.org/OOME_3_0.odt

Another source of information is the The Apache OpenOffice API Project and you might find the MRI object inspector useful if you are actually using AOO - see [Tutorial] Introduction into object inspection with MRI
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: Visual Basic to Open Office Calc

Post by Texmanage »

Thanks for suggestion. I already checked and got clarified. i need a clarification of mask input. I mean i need format of "nnnnnn.nnn". It have EditMask option. But no samples available for that. If u know, can explain how to change numeric field decimal points. by default it shows 2 decimals and rounding off to 2 decimals.
OpenOffice 3.1 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

thats y asking for any samples.
There are lots of samples related to the API in this forum. You will find them.
There are lots of samples about the API and the StarBasic in the free macro programming books written by Andrew Pitonyak. http://www.pitonyak.org/oo.php

The api functions are same for the macro programming, and for control the Apache OpenOffice from a third party application. The API of the LibreOffice is mostly same as the API of the AOO but there are some differences by now.
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.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

I mean i need format of "nnnnnn.nnn". It have EditMask option. But no samples available for that. If u know, can explain how to change numeric field decimal points. by default it shows 2 decimals and rounding off to 2 decimals.
Ohhh, man!
There are not FIELDS in a spreadsheet. There are CELLs only. There are not NUMERIC fields in a spreadsheet, but there are Cells with float type (Value) content. You must FORMAT the content with Cell styles (or by manual formatting), if you want see 123456.789 like "numbers" in the cells.
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.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

You van create and apply cell styles by the API function, or you can apply the manual (direct) formatting properties by the API functions.
Prefer the usage of the cell styles. The predefined template files can store all of the Cell styles what you want to apply in your document, not needed recreate the styles by the program code every time.
The Styles are the most valuable property of the AOO/LO.
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.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

 Edit: Sorry this post was not sent, because there was many other posts in the queue... 
each column have different datatypes. how to set datatype for particular cell.
Which "data types" you meant? Villeroy wrote, that Calc uses Strings and Double precision floating point numbers only.

- the "Float" like data are stored as floats, and they will be visualized as strings. ("1.23456"; "-1.234566E-23")

- the "Date" like data are stored as floats, and they will be visualized as strings. ("2017-11-11 12:00" has same value as "43049,5")

- the Integer like data are stored as floats, and they will be visualized as strings. ("123456; -123456")

- the Boolean like values are stored as floats, and will be visualized as strings ("True"/"False", "1"/"0").

The double precision floats use 8 byte for storing, the old type of the strings used 256 characters/bytes (the modern type of the strings use more than 256 characters/bytes/bytepairs)


The Calc cell uses other type names:

- Value:
Stored as floats, displayed as strings.

- String:
Stored as strings, displayed as strings.

- Formula:
Stored as strings, displayed as strings (when see the formula itself), and when you see, but these strings will be interpreted as some program Code. The result of the formula will be String or Float, but each of the results types will be displayed as strings.

- Formula local:
Same as the Formula, but these are localized for example: the default name of the a function in a formula is "Concatenate()" = Hungarian "Összefűz()".


You can set the Value, the String, the Formula, or the Formula local property of the Cells, by the API functions.
You can declare some other variable types in the StarBasic or in the other supported programming languages, but you must/can pass to the cells only the types listed above.

I can repeat only that Villeroy wrote so clearly for you:
A Calc spreadsheet knows text and numbers (double precision floating point numbers) as the only two data types. Formuals may return error values as a third data type. Everything is stored as doubles unless it is text. MS Excel knows booleans as another type. In Calc booleans are stored as 0 and 1. Floating point numbers with no decimals are displayed as integers. Dates are day numbers. Times are fractions of days. Every single cell can have any data type. There are no database fields nor database records in a spreadsheet. There are not even tables since any rectangle of cells can be used as a table and there can be many such rectangles on the same sheet and you may use cells without any tabular structure. In other words: spreadsheets are no databases, no matter how hard people try to misuse spreadsheets as databases.
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.
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: Visual Basic to Open Office Calc

Post by Texmanage »

Thanks for ur reply. I got to know abt calc cell types. I used float type to display numbers. I want to set decimal digits based on input. Eg: Particular cell may have value likes, 999.99 and 999.999 . how to format this decimal digits. In vb.net there is option for that.. eg: "nnn.nn" and "nnn.nnn".
OpenOffice 3.1 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

In vb.net there is option for that.. eg: "nnn.nn" and "nnn.nnn".
You can not set it on the VALUE before you insert it into a cell programatically. Or you most insert it as TEXT (String).
But you can format the cell where the VALUE insert the number by usege a Cell style.
1.: You can preformat the cell in the Template file,
2.: or you can apply a predefined style (stored in the template) by the code,
3.: or you can create (and then apply) a new Cell style by the code,
4.: or you can format the cell directly by the code.

I can not help you a VB.NET example, but there are lots of sample code snippets written in StarBasic, Python, JavaScript in this forum, and there are samples written in StarBasic in the books linked above.

But you must study,
- how to get a spreadsheet Document/Template by API functions,
- how to get a Sheet,
- how to get a Cell,
- how to put a VALUE or a STRING into the Cell,
- how to create/apply a cell style to a Cell, or how to format directly a Cell,
- how to store the Document as .ods type file
by calling the API functions from your VB.net code.
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.
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: Visual Basic to Open Office Calc

Post by Texmanage »

Thanks for ur reply.. I have done everything. Its working fine. Just struck up with the last one. Creating template wont suits me. bcoz there are lot of options in exporting. Its changes always depends on the form. By default it was setting with 2 decimal float. If change to Text. Sum or mathematical functions not performing.
OpenOffice 3.1 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Visual Basic to Open Office Calc

Post by Zizi64 »

Creating template won't suits me. bcoz there are lot of options in exporting. Its changes always depends on the form. By default it was setting with 2 decimal float.
Then you can create lots of predefined different cellstyles in the template - manually...

Or you can create cell styles dinamically, by the code.
Here are some samples written in StarBasic:
http://ooo-forums.apache.org/en/forum/v ... 6&p=337462
https://www.google.hu/url?sa=t&rct=j&q= ... 4e6dZGsc7M
viewtopic.php?f=20&t=42863
viewtopic.php?f=45&t=28012
viewtopic.php?t=48960
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Visual Basic to Open Office Calc

Post by Villeroy »

Please, do us all a favour and keep on hacking with MS Excel.
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
Post Reply