OpenDocument spreadsheet (.ods) reader

[Inverness]

Request: A function that can read the specified spreadsheet file and return a two-dimensional array of cell values for each sheet in the file.

Rationale: Many servers use different formats of text files for storing static read-only data like item archetypes. This often requires using a text editor that doesn't allow for much flexibility as far as comments and readability goes. Using spreadsheets allows a wide variety of sophisticated techniques to be used to generate the values of the cells, such as macros and formulas. It also makes editing much easier since the columns can be annotated and formatted in helpful ways, and multiple related worksheets can be grouped into one file. Additionally, these files can be read and written by either OpenOffice Calc or Microsoft Excel. ODS files are ZIP files containing XML files. In most languages this is something that could be done by script, but not with Graal unforunately, unless something has changed.

Source: The source of this feature request is this wiki article explaining its extensive use in Dragon Age: Origins by BioWare. Most of the previous paragraph was quoted from the article: http://social.bioware.com/wiki/datoolset/index.php/2DA

I'm quite used to having to make do with what Graal has though, so not expecting implementation, but figured I should get it out there anyways.

[WhiteDragon]

Originally Posted by Inverness

ODS files are ZIP files containing XML files.

You could try decompressfile and loadxml.

[Inverness]

Originally Posted by WhiteDragon

You could try decompressfile and loadxml.

I can't get decompressfile() to work. The NPC-Server has folder rights and stuff, but no go.

[fowlplay4]

Originally Posted by Inverness

I can't get decompressfile() to work. The NPC-Server has folder rights and stuff, but no go.

Are you extracting a .zip file or the .oda? For whatever reason unapproved extensions just don't work with File I/O functions. I.e: The .db thing that cbk ran into.

[Inverness]

Originally Posted by fowlplay4

Are you extracting a .zip file or the .oda? For whatever reason unapproved extensions just don't work with File I/O functions. I.e: The .db thing that cbk ran into.

I know, I accounted for that, no luck.

[LoneAngelIbesu]

Well, if it's possible to just write your own parser, why not just ask Stefan to enable the .ods and .xls extensions?

[Inverness]

Originally Posted by LoneAngelIbesu

Well, if it's possible to just write your own parser, why not just ask Stefan to enable the .ods and .xls extensions?

Firstly, xls is a binary format, not going to bother with it. Secondly, xlsx is only supported by Excel 2007. Thirdly, ods can be read and written by both Excel and OpenOffice Calc, as I said in the first post.

And it isn't possible to write my own until I decompressfile will work properly.

[LoneAngelIbesu]

Originally Posted by Inverness

SAnd it isn't possible to write my own until I decompressfile will work properly.

And doesn't it not work properly, because file I/O doesn't work with unapproved extensions? My suggestion was to ask Stefan to add those extensions, so that you COULD write it.

[Inverness]

Originally Posted by LoneAngelIbesu

And doesn't it not work properly, because file I/O doesn't work with unapproved extensions? My suggestion was to ask Stefan to add those extensions, so that you COULD write it.

Tried renaming to .txt, didn't work. I'd think Stefan would make .zip workable if that function exists.

[Loriel]

Originally Posted by Inverness

Source: The source of this feature request is this wiki article explaining its extensive use in Dragon Age: Origins by BioWare. Most of the previous paragraph was quoted from the article: http://social.bioware.com/wiki/datoolset/index.php/2DA

"Before they can be used by the toolset or the game, 2DAs must be converted from Excel files into a more efficient form."

Integrating a spreadsheet processor into Graal that understands macros and formula seems a bit overkill, at least convert it to a CSV text file or something before uploading. :I

[WhiteDragon]

Originally Posted by Loriel

"Before they can be used by the toolset or the game, 2DAs must be converted from Excel files into a more efficient form."

Integrating a spreadsheet processor into Graal that understands macros and formula seems a bit overkill, at least convert it to a CSV text file or something before uploading. :I

This is what I told Inverness, but he seemed to think CSV wasn't powerful enough.

[Loriel]

Originally Posted by WhiteDragon

This is what I told Inverness, but he seemed to think CSV wasn't powerful enough.

He is supposed to do his fancy formula magic in Excel or whatever, then just convert it to a CSV to feed it to the server. Or, hell, write a Excel macro that spits out a gs2 array definition.

What I was trying to get at is that as I understand it, this Dragon Age thing does not have Excel inside the actual engine either.

[Twinny]

Originally Posted by Loriel

He is supposed to do his fancy formula magic in Excel or whatever, then just convert it to a CSV to feed it to the server. Or, hell, write a Excel macro that spits out a gs2 array definition.

What I was trying to get at is that as I understand it, this Dragon Age thing does not have Excel inside the actual engine either.

This is what I thought when I read,

PHP Code:

2DAs must be converted from Excel files into a more efficient form. 


[coreys]

I'm thinking that Inverness would find more ideal to be able to just edit the OpenDocument spreadsheet and upload it to have changes take effect, instead of having to run some macro every time and update an array in a script somewhere.

I'm not sure that, in the scope of Graal, that doing all this is completely worth while, though...unless you're planning some huge server on the scale of Dragon Age lol

[Inverness]

Originally Posted by Loriel

"Before they can be used by the toolset or the game, 2DAs must be converted from Excel files into a more efficient form."

Integrating a spreadsheet processor into Graal that understands macros and formula seems a bit overkill, at least convert it to a CSV text file or something before uploading. :I

No, Graal would only read the text values of the cells. You can get cell values--which are entered manually or results of formulas--by parsing the XML, formulas used to produce the values are stored elsewhere and are ignored. Everything else is only for the benefit of the person editing the spreadsheet.

Originally Posted by WhiteDragon

This is what I told Inverness, but he seemed to think CSV wasn't powerful enough.

Things like comments, formulas, and color coding would need to be preserved, that is the whole point. You can't do that with CSV. Having to produce a new CSV file every time you edit the spreadsheet seems troublesome to me, it would just save a lot of time for a function to be able to read the spreadsheet file and covert it into a format usable for scripting.

Originally Posted by coreys

I'm thinking that Inverness would find more ideal to be able to just edit the OpenDocument spreadsheet and upload it to have changes take effect, instead of having to run some macro every time and update an array in a script somewhere.

Exactly.

Originally Posted by coreys

I'm not sure that, in the scope of Graal, that doing all this is completely worth while, though...unless you're planning some huge server on the scale of Dragon Age lol

Considering how relatively easy it is to open a ZIP file and parse some XML it is not time-consuming to implement.

[benpoke103]

Isn't there a way you could convert it from To/From SQL?

[Inverness]

Originally Posted by benpoke103

Isn't there a way you could convert it from To/From SQL?

Of course the data from the spreadsheet could be converted into an SQL table if some find it useful in that format.

[coreys]

I think I can see his point. In essence a spreadsheet is basically a more visual form of an SQL table (and much easier to work with, not that SQL is difficult). While there is the SQL Explorer readily available, it is just much easier and a much more viable solution to use a spreadsheet.

You have to think about development as something involving more than just yourself. Just because you are easily able to use the tools already available to us doesn't mean that others on your team are. Spreadsheets would make it trivial to have just about anybody with a good idea to be able to tweak stats and create items, characters, and such, so that you don't have to. That's the real value I see in this, because I, much like many of the people in this thread, am perfectly able to edit an ini file or manipulate SQL. But I know people on projects I'm on who get a lot more done and make better content if they could manipulate the data too.

[fowlplay4]

Well if this isn't added there's Excel 2003 XML Format (works in OpenOffice too), which looks pretty easy to parse and doesn't require any decompression.

[coreys]

Originally Posted by fowlplay4

Well if this isn't added there's Excel 2003 XML Format (works in OpenOffice too), which looks pretty easy to parse and doesn't require any decompression.

It's not valid XML
That or any XML parser I've seen on Graal doesn't consider it valid xml

[Twinny]

I think at this point in time people should stop suggesting new ways of doing things and should actually do things. By saying you need these kind of additions isn't really enabling anything but is limiting yourself. Common theme lately seems to be putting wrappers around wrappers for what could be simple tasks with mass levels of obfuscation yet nothing ever comes out of it.

[Admins]

Extracting zips is not working on server-side yet because the folder-access rights check is not working for the npcserver yet. XML should work fine though, it's using the standard libxml2.