Searched, but could not find, a library/unit for creating spreadsheets. XLS, open doc.. or anything that would allow to create spreadsheet tabs to later on open with Excel.

Trying to see if there is one before trying to create one.

You might use the CSV, which can be easily open with Office and to generate by your application.
I am using this way to handle data to/from excel
It is a plain text file, each row is separated with CRLF (#13#10), each cell separated with tab (#9)
Also, if you'r data has no comma or " then you can use the real CSV, because CSV means Comma Separated Values :)
But the tab separated one can be easily pasted to/from Excel, by using let's say notepad.
You can experience this, since each Office behaves differently with the CSV.

2008/10/4 Francisco Reyes

It's here:

http://wiki.lazarus.freepascal.org/FPSpreadsheet

It is modular, so it suppors writing various versions of MS Office XLS and even experimental ODS and xlsx, althougth I don't recommend those. I think that Excel 5.0 was with the best support.

Download the latest source code from subversion (link in the wiki).

Unfortunatelly I haven't yet had time to update the wiki page documenting how to use it, but take a look at the examples that come with it.

It supports mostly writing spreadsheets, but it has an architecture which should support being extended to read existing spreadsheets, althougth I don't remember if I implemented this for any format.

And if would be great if you can document it in the wiki page or contribute patches if you wish to extend it.

From all the possible things to store in a Cell I think that only

numbers, strings and formulas were working.

If buying is an option: You can buy FlexCel from TMS software. It works with FPC/Lazarus.

Michael.

2008/10/4 Antal :

CSV works. HTML also works. With HTML you could set colors and sizes to the cell. But this method does not have the same behavior among Excels and Open Office.

I will try Felipe's unit.

Gustavo

errm, surely that should be a comma, not a tab!

Also, if you'r data has no comma or " then you can use the real CSV, because CSV means Comma Separated Values :)

You can use CSV with data which contains commas and quotes. Fields which contain "dodgy" characters, including commas, quotes, leading and trailing spaces, must be surrounded by quotes. If a field contains quote chars, the quote char is doubled:
"one ","two,buckle","""my""","shoe"
first field has a trailing space, second contains a comma (and 2 words), third is quoted and OpenOffice Calc seems to have quoted the fourth just for the heck of it.

But the tab separated one can be easily pasted to/from Excel, by using let's say notepad. You can experience this, since each Office behaves differently with the CSV.

Truth! But most spreadsheets can handle the above format.

Frank

The Tab as separator is just a good way to avoid using the double quote :P It just comes from the good old Lotus123.
Anyway, just try to copy/paste to a notepad from an Excel and you will see how it works.
And then, saved as .CSV reopen it with Office and it might work. Or it might not :P
I am currently using this, since it is easier to manage as the double quotes.

An additional issue with delimiters in this case is the fact, that "C" in "CSV" may not always be a comma (or that spreadsheet applications may expect different characters depending on locale - e.g. semicolons, etc.).

Tomas

CSV is occasionally referred to as something other than "Comma Separated Values". I'm not sure why. If you're using CSV, stick to what most applications understand by csv and benefit from some level of compatibility. Why make hassle for yourself? Excel (for example) can save tab delimited .txt files easily, but when you open them again you have to tell it what's used as a delimiter. If you save some data tab-delimited, but call it csv, then it's just /wrong/ when you open it again. Save it comma-delimited, and you can have any sort of text value in a cell, and it just opens.

Lack of a formal specification is a problem, but you could do worse than http://tools.ietf.org/html/rfc4180
or even
http://en.wikipedia.org/wiki/Comma-separated_values

Oh, and since it's a text file, you'll want to be careful about whether to use CR, LF or CRLF as a line delimiter, they're all valid somewhere :)

Which is all getting very off-topic so I'll shut up now.

Frank

2008/10/6 Frank Peelo

I only prefer tab delimiter because, upon copy/paste to/from Excel, you will get a tab delimited one, and you don't need to do anything special.

Oh, and since it's a text file, you'll want to be careful about whether to use CR, LF or CRLF as a line delimiter, they're all valid somewhere :)

Also, this CR/LF problem is handled by the most of the text editors :P

In our previous episode, Frank Peelo said:

Because the comma is a bad separator for countries where the comma is the decimal separator, like most of mainland Europe.

http://en.wikipedia.org/wiki/Decimal_separator#Countries_using_Arabic_numerals_with_decimal_comma http://en.wikipedia.org/wiki/Decimal_separator#Countries_using_Arabic_numerals_with_decimal_point

But CSV handles that fine! "12,345","67,890","I paid Ç12,34 for my lunch and I didn't even get chips!"
(For some reason € comes out as Ç when saved by Excel.) It's being read/written by a spreadsheet anyway, which doesn't mind putting/reading extra quote chars.

(apropos spreadsheets, awkward delimiters and being off-topic: I notice that using '-' as a delimiter *in* *the* *filename* can give the spreadsheet indigestion. Specifically in a file with a name like Data_2008-10-06.xls when specifying an output reference for the Histogram tool in the Data Analysis Toolkit. Seems to want to evaluate (Data_2008 - 10 - 06.xls) as an expression. I only mention it in the hope that it gives someone a chuckle.)

Michael Van Canneyt wrote

If buying is an option: You can buy FlexCel from TMS software. It works with FPC/Lazarus.

Thanks for the pointer.

75 EUR is a little steep for what will end up been an open source app. Specially since it will be a text based program. Will try the Lazarus component and if that doesn't work, will consider FlexCel. Also if I used FlexCel the program will pretty much never have other people work the project. Few people will pay that money so they could work on an open source project.

To those who mentioned CSV.... as I mentioned in my original post I am trying to create tabs. The ocassional conversion is fine with CSV, but when one has a process that needs to take multiple ASCII files and put it into a single spreadsheet on a regular bases, CSV is far from ideal.

Well, that's why I said "If buying is an option"...

If you ask me, it can't be hard to write the older text excel file format. See
http://chicago.sourceforge.net/devel/docs/excel/
or
http://chicago.sourceforge.net/
and
http://chicago.sourceforge.net/xlhtml/

Michael.

.
.

Well, CSV handles it fine, but the applications don't and that was exactly my point: Opening such a CSV (commas used as delimiters as described above and the whole file named *.csv) with MS Excel running on Windows with e.g. Czech locale, the whole lines end up in one cell. If you want to avoid this behaviour (with MS Excel), you need to use semicolons or start the import explicitly from a previously started MS Excel instance (rather than opening the *.csv file directly by double-clicking it in Explorer or so), because that allows specifying a non-default delimiter (like a comma in this case).

Tomas

In our previous episode, Frank Peelo said:

CSV is just that comma separated.

Quoting is one common workaround. Another is to use a different separator. And iirc Excel does that sometimes too. Maybe it is version dependant.

My favorite "csv" experience is to get csv's from the web opened in Excel, for both Dutch as English locales. Really a sport :-)

Sorry, but that is rather application depended. And there are a lot that handle, both for writing and reading, "quoted" fields not properly, though there is a relevant RFC about this in RFC4180 (http://tools.ietf.org/html/rfc4180)

My favorite "csv" experience is to get csv's from the web opened in Excel, for both Dutch as English locales. Really a sport :-)

I do this a lot, in and out of Excel and other application and Excel in fact handles this better than anything else. If the CSV file has been written properly to begin with...

Ralf

In our previous episode, Ralf A. Quint said:

Correct.

It depends heavily on the mimetype also. Application/vnd.excel or so gave the best results. But that is unstandarize OS specific, so it couldn't be used on a "neutral" government site.

And the existance of characters that Excel (XP or 2003 it was I think) could see as signal chars to switch charsets was also a problem.

Michael Van Canneyt wrote

If you ask me, it can't be hard to write the older text excel file format.

Thanks for the links will take a look later.
For now I will go with http://wiki.lazarus.freepascal.org/FPSpreadsheet and see how that works out.

Felipe Monteiro de Carvalho wrote

http://wiki.lazarus.freepascal.org/FPSpreadsheet

Finally got started on this project.
I am a little confused on the functionality of the unit excel2utils; I created a file with the exact code from the site, dropped it in the same directory as fpspreadsheet.pas and was able to create the test.xls file.

What is the purpose of the excel2utils file?
Why isn't the excel2utils in the SVN repository so one gets it with the SVN pull?

I was able to run the sample on the site and create a test.xls. However, when I tried the examples/excel5demo/excel5demo.lpr one I got it to compile and it runs without error, but no file is created.

Given that I need sheets I pretty much need the xlsbiff5 format and not the simple 2.1 method on the website.. Is that a true statement?

Francisco Reyes wrote

What is the purpose of the excel2utils file?

This unit was the very first version of fpspreadsheet. It is completely obsolete, and I just kept it in the website because I still haven't written docs for the new version. I would recommend against using it. Use the code in subversion instead.

I was able to run the sample on the site and create a test.xls. However, when I tried the examples/excel5demo/excel5demo.lpr one I got it to compile and it runs without error, but no file is created.

In which operating system did you run it? Excel 5 needs the generation of a OLE documented. I read the specs and considered writing a OLE writer, but I needed support only for Windows and I was out of time, so I just called the OLE dll, which means that it only works in Windows. Or this is what happens as far as I remember.

Given that I need sheets I pretty much need the xlsbiff5 format and not the simple 2.1 method on the website.. Is that a true statement?

Yes, this is a true statement. OOXML and OpenDocument would also work, but you will need to improve support for them in fpspreadsheet.

Felipe Monteiro de Carvalho wrote

completely obsolete, and I just kept it in the website because I still haven't written docs for the new version. I would recommend against using it.

Gotcha.

In which operating system did you run it? Excel 5 needs the generation of a OLE documented.

OpenSuse 10.3

Yes, this is a true statement. OOXML and OpenDocument would also work, but you will need to improve support for them in fpspreadsheet.

What do you mean by by that?
Tried
WriteToFile('test.ods', sfOpenDocument)

and got

An unhandled exception occurred at $000000000045FB83 : Exception : Unsuported spreadsheet format.
$000000000045FB83
$000000000045FC02
$000000000040032B

Which formats currently work under Linux?

Is there a list for discussing unit or this is the best place?

Tried replacing the xlsbiff5 unit with fpsopendocument and instead of creating a file I got a directory with .xml files.

Thanks for all your help!!

Actually there is documentation. In subversion there is a chm file with a description of the API.

I think I will be writing my next Toolbox article about fpspreadsheet, so I will take this opportunity to improve it's wiki page.

I may also improve one of the formats, not sure yet which one ...

thanks,

I just updated the wiki page, added the API reference file, added a proper example, status information

And commited some small improvements in the wiki

Don't see my response to the list..
Resending.. and updating..

completely obsolete, and I just kept it in the website because I still haven't written docs for the new version. I would recommend against using it.

Gotcha.

In which operating system did you run it? Excel 5 needs the generation of a OLE documented.

OpenSuse 10.3

I see a comment in the opendocument file that it is not finished. It currently creates a directory, but doesn't seem like the files are correct yet. Tried to make a zip out of the directory and open it with openoffice. It gave an error.

Which formats currently work under unix (Linux/FreeBSD)?

Francisco Reyes wrote

What do you mean by by that? Tried WriteToFile('test.ods', sfOpenDocument)

You need to add the unit which registers the open document format into your uses clause.

uses fpsopendocument;

for OOXML:

uses xlsxooxml;

I don't remember what exactly is working for each one, but I don't think they will be usable without improving them.

Which formats currently work under unix (Linux/FreeBSD)?

Only Excel 2

Felipe Monteiro de Carvalho wrote

>Which formats currently work under unix (Linux/FreeBSD)? Only Excel 2

Thanks for the update and for the Wiki changes.

Based on your schedule when do you think you may get the opendocument format or the Excel 5 working under Linux/FreeBSD?

Also, any plans to have the help in anything other than chm? I found some viewers/converters, so I think I will be able to open it or convert it, but would be nice if there was something other than chm. If I get to conver it, should I put it somewhere for you? Haven't tried yet, but I think I will be able to convert to PDF.

In our previous episode, Francisco Reyes said:

The document generator already generates PDFs? The problem with PDFS is that while these are great as reference, they are (IMHO) in the current setting not usable as help system.

Why not ?

Michael.

In our previous episode, Michael Van Canneyt said:

Same as a browser, but maybe worse:
How do you instrument a PDF reader platform independantly? And (specially with recent Adobe readers): speed.

That's an implementation detail, but doesn't make PDFs a priori unsuitable as help format.

I use PDF as the help system for my daytime job apps. Works fine.

Michael.

In our previous episode, Michael Van Canneyt said:

Well, let's keep this limited to FPC context, and the foreseeable future. And include the textmode IDE.

I use PDF as the help system for my daytime job apps. Works fine.

How is the speed? Does it match say a D7 help ?

Marco van de Voort wrote

How is the speed? Does it match say a D7 help ?

Not even D2005, D2007 or D2009 matches the D7 help speed! :-)

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

In our previous episode, Graeme Geldenhuys said:

> How is the speed? Does it match say a D7 help ? Not even D2005, D2007 or D2009 matches the D7 help speed! :-)

Yes, but IMHO that is pretty much more related to politics, not technical.

Indexes of RTL and FCL CHMs are read in the blink of an eye in the textmode ide, and the quality is not THAT bad.

The routines are there, and there are several html renderers, and on Windows it is native.

That doesn't mean the chm is set in stone and forever. I can imagine that e.g. OS X will prefer its native helpsystem or whatever, but CHM is a good first level, doesn't have complicated dependancies or speedproblems and it is doable short term.

Your ship sinks on the last sentence. I never will do that :-)

> I use PDF as the help system for my daytime job apps. Works fine. How is the speed? Does it match say a D7 help ?

I honestly don't know. Never tested.

The primary reason for going to PDF was that we can create the docs in LaTeX, which is a text format where layout can be largely ignored.

Having 6 people working together on a Word document is not doable, both from versioning and layouting point of view. In LaTeX it works.

Michael.

Francisco Reyes wrote

Based on your schedule when do you think you may get the opendocument format or the Excel 5 working under Linux/FreeBSD?

No idea. If you try to implement it, I could help you and that would certainly motivate me more then doing the work alone. The work is simple, just somewhat boring and needs a lot of testing. Just read the documentation for the format and start implementing it:

http://sc.openoffice.org/compdocfileformat.pdf

Also, any plans to have the help in anything other than chm?

The help is generated from the comments in the source code using Doc-O-Matic express:

http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/builddoc.bat?revision=362&view=markup

I assume you don't have Windows installed, do you? I will try to generate in another format later today.

It is possible to use any documentattion generator by inputing the adequate parameters it will use to analize the source code, but I tested many pascal doc generators and Doc-O-Matic was by far the best.

Ok, I took a look at this, to have pdf support one needs to buy the professional version of Doc-o-Matic, so if you manage to convert it, I could add it to the svn repository.

The document generator already generates PDFs? The problem with PDFS is that while these are great as reference, they are (IMHO) in the current setting not usable as help system.

I am not using the Free Pascal documentation tool. I am using documentation taken from comments in the source code, which I prefer.

There is a very nice CHM extension for Mozilla Firefox. It works very well. There is also a Gnome CHM Viewer which is also pretty good.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

In our previous episode, Michael Van Canneyt said:

Why not? We have done till now? Moreover, it only means that some format must be generated that can be used by the IDE.

And since we will generate html for the website no matter what, chm is generally an option (mostly because it roughly html in an archive, to avoid 8.3, lfn/sfn/case sensitivity and similar problems)

So if you have a chmlib as we have now, that reduces the textmode IDE support to mere keeping the html format somewhat stable, and maybe wrapping paragraphs longer than 255 lines in fpdoc to a new line.

Note that I'm talking about helpsystem now, not general documentation.

And straight LaTeX only seems to work fine when you do .ps and .pdf. Despite some improvements the html generation remains a problem.

In our previous episode, Felipe Monteiro de Carvalho said:

I already noticed, yes. Can you choose between in and out of source documentation with Doc-O-Matic, or is it always in source?

I'm not that fond of in-source docs. It has a certain principal appeal (maintaining both together), but I don't like the practice. Or at least the ones that I have seen.

In our previous episode, Graeme Geldenhuys said:

And both work fine on FPC chms btw.

And if you use kde, there is also kchmviewer. I tried to install it under Fedora (generally a gnome system), but it was very unstable there, so I can't say much about the quality.

Marco van de Voort wrote

I already noticed, yes. Can you choose between in and out of source documentation with Doc-O-Matic, or is it always in source?

It supports out of source documentation, but not in the Express (zero cost) edition:

http://www.doc-o-matic.com/featurematrix.html

I'm not that fond of in-source docs. It has a certain principal appeal (maintaining both together), but I don't like the practice. Or at least the ones that I have seen.

I find that the external documentation imposes a too big overhead. When in-source I can write it in the same editor I am used to, navigating the same source I am used to, instead of an external representation of it. Not to mention when things get renamed external docs usually have a hard time.

The external documentation duplicates information, because we already describe how things work in the comments, and then we need to describe that again in external docs. Or if we don't comment properly it becomes hard to underderstand the code.

I meant that I will not take into account the existence of a textmode IDE when writing documentation. For API reference docs, this is OK since they are generated from the sources, but not for the manuals.

As far as I'm concerned, a HTML version of the docs is still a courtesy towards some people, never a goal or requirement.

Michael.

Comments in source are generally limited to one or 2 lines. This is OK for docs that only you yourself need.

API Documentation for externals should extensively describe how something works, describing possible pitfalls and whatnot.

The sources would become totally unreadable if you would mix them with the current documentation.

Michael.

In our previous episode, Felipe Monteiro de Carvalho said:

I directly agree there is a navigation-to-docs penalty. Even if some editors/IDEs remedy that, there is a bump there.

The external documentation duplicates information, because we already describe how things work in the comments, and then we need to describe that again in external docs.

No. Only if you insist that all that be in the sources in the first place. (which is IMHO not the case if there are good external docs). Moreover it puts yet another pressure on the source files, which already has two permanent holy wars micromanaging each detail (formatting/identation and commit/VCS checkout behaviour, maybe the "don't let them be too big" is a third even) if you are in a team.

If not, the comments in the sources reduce to warn for gotcha's and other notes, that might not belong in the don't see duplication a problem if they have two separate purposes. Or better: then it is not duplication.

Or if we don't comment properly it becomes hard to underderstand the code.

Also not necesary, to study source, you could have a tool to combine the source with the external doc, with some crossref data.

Michael Van Canneyt wrote

Comments in source are generally limited to one or 2 lines. This is OK for docs that only you yourself need.

Only if you don't prepare them to be a documentation.

The sources would become totally unreadable if you would mix them with the current documentation.

Maybe for Free Pascal. I think it depends on the project.

Marco van de Voort wrote

If not, the comments in the sources reduce to warn for gotcha's and other notes, that might not belong in the don't see duplication a problem if they have two separate purposes. Or better: then it is not duplication.

Not all comments are in the documentation. Only those marked to be, like {@@ .... }

In our previous episode, Felipe Monteiro de Carvalho said:

I know, but they drown out in the noise. And then some bright guy decides to wrap all comments in code folding directives and doesn't pay attention ot that, and after that it gets really complicated and confusing :-)

It of course depends on the project, for small trivial stuff managed by one person it doesn't really matter, as long as you are consequent in either way.

Felipe Monteiro de Carvalho wrote

Ok, I took a look at this, to have pdf support one needs to buy the professional version of Doc-o-Matic

I can run windows from VM.. just have no idea what to use to open a chm.

I will just convert to PDF.
Have been sick since friday so did not get a chance to work on the project I was hoping to over the weekend using the library..

Francisco Reyes wrote

I can run windows from VM.. just have no idea what to use to open a chm.

You just double click the chm. It's the standard Windows help system in Windows XP+ (maybe older too).

In our previous episode, Felipe Monteiro de Carvalho said:

> I can run windows from VM.. just have no idea what to use to open a chm. You just double click the chm. It's the standard Windows help system in Windows XP+ (maybe older too).

IIRC it dates back to win98, and even works on win95 if you have a version with IE4 or newer.