Alternative to Excelwriter? (without COMate)

Just starting out? Need help? Post your questions and find answers here.
zikitrake
Addict
Addict
Posts: 834
Joined: Thu Mar 25, 2004 2:15 pm
Location: Spain

Alternative to Excelwriter? (without COMate)

Post by zikitrake »

Because my users don't always have Excel installed on their computers and Excelwriter is limited to a maximum of 144 characters per cell (255 with a hack): viewtopic.php?f=13&t=52356&hilit=excelwriter,

I am looking for an alternative to Excelwriter capable of storing information between 300 and 8000 characters per cell, regardless of whether they have the excel application on their computers or not (without relying on COMATE.

I tried to search for Flype's PureXLS, but it's not available in any of the forum links.

I also tried to generate a CSV, but depending on how you open it with Excel or Libreoffice, it gives me a thousand problems when you find cells with line break and/or special characters.
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: Alternative to Excelwriter? (without COMate)

Post by mk-soft »

8000 characters per cell on an Excel spreadsheet makes no sense.
Is rather something for a database with BLOB for large data sets of up to 8000 characters.
Reading into an Excel table is also very critical because of the large number of characters per cell.

So a database application on a SQL server.
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
zikitrake
Addict
Addict
Posts: 834
Joined: Thu Mar 25, 2004 2:15 pm
Location: Spain

Re: Alternative to Excelwriter? (without COMate)

Post by zikitrake »

mk-soft wrote:8000 characters per cell on an Excel spreadsheet makes no sense.
Is rather something for a database with BLOB for large data sets of up to 8000 characters.
Reading into an Excel table is also very critical because of the large number of characters per cell.

So a database application on a SQL server.
Hello, thank you for the answer. There are about 20-30 rows in which in each of them there is a cell with text between 700 and 1200 words (about 4000-8000 characters)... Excel is supposed to support more than 30000 characters per cell (https://support.office.com/en-us/articl ... 9d656771c3)

I can't (or don't know how) use anything other than xls files as the result is aimed at a third party application that only handles Excel files.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Alternative to Excelwriter? (without COMate)

Post by captain_skank »

I may be wrong but for newer versions of excel, aren't excel files just a container like a zip file ?

In which case you could unzip it in PB and then edit the files accordingly with the file and string commands.
ivega718
User
User
Posts: 15
Joined: Mon Feb 25, 2013 9:29 pm

Re: Alternative to Excelwriter? (without COMate)

Post by ivega718 »

I am using LIBXL for Excel Files.

http://www.libxl.com/
zikitrake
Addict
Addict
Posts: 834
Joined: Thu Mar 25, 2004 2:15 pm
Location: Spain

Re: Alternative to Excelwriter? (without COMate)

Post by zikitrake »

captain_skank wrote:I may be wrong but for newer versions of excel, aren't excel files just a container like a zip file ?
In which case you could unzip it in PB and then edit the files accordingly with the file and string commands.
Thanks! I never would have imagined it, in theory it is only necessary to fill in the file sharedStrings.xml
ivega718 wrote:I am using LIBXL for Excel Files.
http://www.libxl.com/
I saw that library, but it's paid and adds about 14 MB to the final software.
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: Alternative to Excelwriter? (without COMate)

Post by mk-soft »

Alternatively, work via ODBC driver for Excel. The 32-bit drivers are installed by default.

You can also download the 64 bit drivers from MS.
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
HanPBF
Enthusiast
Enthusiast
Posts: 563
Joined: Fri Feb 19, 2010 3:42 am

Re: Alternative to Excelwriter? (without COMate)

Post by HanPBF »

XSLX files can be unpacked with 7zip; they are normally packed XML files.

Then maybe injection in cells is enough?

The XML of Excel is a bundle of cra..

It's unfort. neither not easy to handle.
User avatar
Fig
Enthusiast
Enthusiast
Posts: 351
Joined: Thu Apr 30, 2009 5:23 pm
Location: Côtes d'Azur, France

Re: Alternative to Excelwriter? (without COMate)

Post by Fig »

Finaly, i used Comate for my project but i messed with xlsx files and it's really dirty...
Here is my feed back: (sorry automatic translation...)

0- The .xlsx file
It's actually a .zip archive containing different files ...

1- formulas
You can not edit directly via xml tags a formula or delete a formula to replace it with a value.

In addition, if a value is forced into a cell and that cell is referenced by a formula, the formula (s) will not be evaluated and there is no simple way to do it (!!).
This would require editing the computation chain and rebuilding no less than 5 different internal files to the archive of your Excel backup (a madness that we will abstain probably).

2- Values

To optimize the memory, the texts in the cells are replaced by a numerical index which references the text in an external file. Thus, the text stored in slot 1 can be referenced multiple times by just specifying its location, which reduces storage by as much.
In this case, just create the hard text with the following tags:
=

Code: Select all

SetXMLAttribute (node, "t", "inlineStr")
    node = CreateXMLNode (node, "is", 0)
    node = CreateXMLNode (node, "t" 0)
    SetXMLNodeText (node, Initial)
When saving the file with Excel, it will index itself, like a grow-up, the text.

Empty cells are mostly non-existent in the sheet file. It will be necessary to create them before modifying them.

Once you know all this, it's easier to add values ​​or create values ​​in cells.
There are 2 methods to program bugless.
But only the third works fine.

Win10, Pb x64 5.71 LTS
zikitrake
Addict
Addict
Posts: 834
Joined: Thu Mar 25, 2004 2:15 pm
Location: Spain

Re: Alternative to Excelwriter? (without COMate)

Post by zikitrake »

Hi, thank you for all your answers!!
mk-soft wrote:Alternatively, work via ODBC driver for Excel. The 32-bit drivers are installed by default.

You can also download the 64 bit drivers from MS.
I just tried it, but I have same issue with long strings :( ( Driver: "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
Fig wrote:...Here is my feed back: (sorry automatic translation...)
0- The .xlsx file
It's actually a .zip archive containing different files ...
...
Once you know all this, it's easier to add values ​​or create values ​​in cells.
Thank you, Fig. I don't need formula (fixed colums and always texts)

I just found what files I need to make it: sharedStrings.xml and sheet1.xml

so I unpacked those files, modified them and repackaged them again.
And the resulting xlsx opens without problem in excel/libreoffice with the modified data!

Again, really thank you to all for your answers!

PS: Sorry my bad english
Post Reply