Apache OpenOffice Community Forum

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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".

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.

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.

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

Please, do us all a favour and keep on hacking with MS Excel.