ANNOUNCE ooxml (Excel im-/export) v1.6

[ALX]

ANNOUNCE ooxml (Excel im-/export) v1.6


What has been changed:

bug fix: docProps/app.xml sheet count was wrong
https://tcl.sowaswie.de/repos/fossil/ooxml/info/32d3627499855488

new feature: "named formats" for numberformats, fonts, fills, borders and styles "-tag TAGNAME"
TAGNAMES must be alphanumeric because the internal formats return numeric values and otherwise not kept apart.

new feature: "shared formula" in method cell
-formulaidx SHARE
SHARE must be integer and starts at 0. Make sure that indexing is correct, no plausibility checks are performed.
-formularef INDEX:INDEX
The formula is shared from INDEX to INDEX. A '-formulaidx' must be assigned.
see example: https://tcl.sowaswie.de/repos/fossil/ooxml/artifact/309b44ca85



See also:

https://tcl.sowaswie.de/repos/fossil/ooxml/wiki?name=man-page
https://tcl.sowaswie.de/repos/fossil/ooxml/timeline?t=version-1.6



ooxml ECMA-376 Office Open XML File Formats


SYSNOPSIS

Read and Write Office Open XML "XLSX" since Excel 2007

This package contains several commands to edit Excel files. The three most important
are the following three:

Importing Excel files into a Tcl array with ::ooxml::xl_read,
export Tcl data to an Excel file with ::ooxml::xl_write and
export Tcl tablelist to an Excel file with ::ooxml::tablelist_to_xl.

For information on how to use these commands, see the examples and the man-page
at https://tcl.sowaswie.de/repos/fossil/ooxml.

API

::ooxml::Default name value
::ooxml::RowColumnToString rowcol
::ooxml::StringToRowColumn name
::ooxml::CalcColumnWidth numberOfCharacters ?maximumDigitWidth? ?pixelPadding?
::ooxml::xl_sheets file
::ooxml::xl_read file args
::ooxml::xl_write args
method numberformat args
method defaultdatestyle STYLEID
method font args
method fill args
method border args
method style args
method worksheet name
method column sheet args
method row sheet args
method cell sheet {data {}} args
method autofilter sheet indexFrom indexTo
method freeze sheet index
method presetstyles
method presetsheets
method view args
method write filename
::ooxml::tablelist_to_xl lb args

DOWNLOAD

https://tcl.sowaswie.de/repos/fossil/ooxml/uv/download.html


DEPENDENCIES

Tcl >= 8.6.7
tclvfs::zip >= 1.4.2
tdom >= 0.9.0


GETTING THE CODE

The development repository is hosted at https://tcl.sowaswie.de/repos/fossil/ooxml
You are encouraged to use trunk.


INSTALLING

Simply extract the download ooxml-<version>.zip into your Tcl library path.

Since ooxml is TEA-compatible you should be able to do

../configure
make test
make install


REPORTING BUGS

Please head to https://tcl.sowaswie.de/repos/fossil/ooxml/ticket and
click on "New Ticket". Log in as anonymous and report your findings.
If you prefer to have an individual login write Alex a mail.


HISTORY

ooxml was started by Alexander Schoepe and improved with contributions by Rolf Ade.

[Harald Oehlmann]

Am 02.04.2021 um 14:27 schrieb ALX:
> ANNOUNCE ooxml (Excel im-/export) v1.6

Thank you, Alex !
Great package !

Take care,
Harald

[js]

On 4/2/21 1:28 PM, Harald Oehlmann wrote:
> Am 02.04.2021 um 14:27 schrieb ALX:
>> ANNOUNCE ooxml (Excel im-/export) v1.6
>
> Thank you, Alex !
> Great package !
>

Hello,

I sent a Alex Shoepe a direct email about a potential bug in ooxml but
it bounced. So I thought I would report it here briefly:

Line # 1293 of ooxml.tcl should be changed as follows:

"if {[dict exists $cellXfs($idx) nfi]} {"

change to

"if {[info exists cellXfs($idx)] && [dict exists $cellXfs($idx) nfi]} {"

Without this change, I was getting errors about unknown "cellXfs(4)"

[Harald Oehlmann]

Hi js,
great that you enjoy it. Alexander is normally well reachable and
responding. Strange, that the E-Mail bounces.

To file a bug for ooxml, you may consider to file a ticket in the fossil
ticket tracker here:

https://tcl.sowaswie.de/repos/fossil/ooxml/ticket

Thank you,
Harald

[js]

On 5/19/21 12:07 PM, Harald Oehlmann wrote:
>
> Hi js,
> great that you enjoy it. Alexander is normally well reachable and
> responding. Strange, that the E-Mail bounces.
>

I got this message:

"sfi-mx-2.v28.lw.sourceforge.com rejected your message to the following
email addresses:

sch...@users.sourceforge.net (sch...@users.sourceforge.net)
Your message couldn't be delivered. It appears that the email address
you sent your message to wasn't found at the destination domain, or the
recipient's mailbox is unavailable."

Thanks for the link; I will have to wait until the weekend to file a ticket.

[ALX]

Hello js,

thank you for the error report.
I will have a look at it today.
Why the sourceforge email is currently not working is unclear to me, but I have just reproduced the problem myself.
I will get back later.

Alex

[ALX]

Hi js,

e-mail sch...@users.sourceforge.net still not working...

Yes, I can confirm the error and have corrected it. I'll check the code again for more errors of this kind.

Could you provide your test Excel file?

Please check the fossil-repo and use the e-mail given there.
https://tcl.sowaswie.de/repos/fossil/ooxml/dir?ci=tip

Best
Alex

[ALX]

I found the following ticket in the fossil ticket system:

anonymous added on 2021-05-22 04:08:25:
Hello,
Is there a way to take an existing spreadsheet and do the following? 1) Add a new sheet 2) Rename a sheet 3) Delete an existing sheet
From my limited time so far with the documentation and the example scripts, I can see that some of these are possible with new sheets that are created from scratch. It would be more realistic for these methods to also work on existing spreadsheets.

On the page https://tcl.sowaswie.de/repos/fossil/ooxml/wiki?name=examples there are two examples that read an Excel file and write it again afterwards (sample7.tcl and sample8.tcl).
In sample8.tcl data is changed before writing. As shown in the example, two procedures are possible.

Possibility 1
array set workbook [ooxml::xl_read filename.xlsx]
-> manipulate array workbook
set spreadsheet [::ooxml::xl_write new]
$spreadsheet presetstyles workbook
$spreadsheet presetsheets workbook
$spreadsheet write filename.xlsx
$spreadsheet destroy

Possibility 2
array set workbook [ooxml::xl_read filename.xlsx]
set spreadsheet [::ooxml::xl_write new]
$spreadsheet presetstyles workbook
$spreadsheet presetsheets workbook
-> manipulate with the methods $spreadsheet cell ...
$spreadsheet write filename.xlsx
$spreadsheet destroy

To question 1 this should be possible with possibility 2.
To question 2 and 3 this should be possible with possibility 1, logical cleanup of the data is necessary.

All in all it has to be said that the reading and writing of the excel files has to be considered separately. In principle, these are two independent packages. The merging of reading and writing was already quite complex and does not take into account all aspects that would have to be considered.

[ALX]

I found the following ticket in the fossil ticket system:

anonymous added on 2021-05-28 22:41:57:
Hello,

I am following some of the examples to see how I can create a new spreadsheet. Here is one I am using:

set spreadsheet [::ooxml::xl_write new]

$spreadsheet worksheet Sheet1

$spreadsheet write filename.xlsx
$spreadsheet destroy

The file gets created but when you open it, Excel reports that it is broken and says that it needs to be fixed. I can create an empty spreadsheet (with one or many empty sheets) in Excel just fine. So there seems to be some difference somewhere. The funny thing is that it works if I add some data to it using "$spreadsheet row ..." or "$spreadsheet cell ..." commands before writing to the file. Excel only complains if the spreadsheet has no data.

Am I missing something in the script perhaps? What can I do to fix it?

*

Yes, that will be so. At least one cell must be set. Then the file can be written. What is the point of creating an Excel file if it does not contain any data?

Example empty Excel file:

set spreadsheet [::ooxml::xl_write new]

set sheet [$spreadsheet worksheet Sheet1]
$spreadsheet cell $sheet {} -index A1