PureBasic Forums - English

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.

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.

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.

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.

I am using LIBXL for Excel Files.

http://www.libxl.com/

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.

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.

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.

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:
= 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.

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