Update of "examples"

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.


Artifact ID: d9d86477abb3522119e40cecb91020955c5b615aa784c972e0803b2f461839ae
Page Name:examples
Date: 2019-08-16 20:30:27
Original User: alex
Parent: 84b0be497973b7dfd5415f99cd34f5598c8a1bc2fa6415044f9a7b571297529b (diff)
Next 8cabee4ba745a77d1b15f3640b6ce4e764c7dfd57582216ba10a81733c57536e


Generating XLSX with direct addressing with -index:

package require ooxml

set spreadsheet [::ooxml::xl_write new -creator {Creator Name}]
if {[set sheet [$spreadsheet worksheet {Sheet 1}]] > -1} {
  $spreadsheet cell $sheet 12.34 -index 0,0
  $spreadsheet cell $sheet 5.6 -index 0,1
  $spreadsheet cell $sheet {} -index C1 -formula {A1+B1}

  $spreadsheet cell $sheet {My Text} -index A2 -string

  $spreadsheet write my-first.xlsx
$spreadsheet destroy

You can use numeric index row,col (0,0) or alfanumeric index like Excel does rowcol (A1).

An other way is using autoincrement:

package require ooxml

set spreadsheet [::ooxml::xl_write new -creator {Creator Name}]
if {[set sheet [$spreadsheet worksheet {Sheet 1}]] > -1} {
  $spreadsheet row $sheet
  $spreadsheet cell $sheet 12.34
  $spreadsheet cell $sheet 5.6
  $spreadsheet cell $sheet {} -formula {A1+B1}

  $spreadsheet row $sheet
  $spreadsheet cell $sheet {My Text} -string

  $spreadsheet write my-first.xlsx
$spreadsheet destroy

Rows and coulmns are auto incremented and can be skipped by -index like this:

$spreadsheet row
$spreadsheet cell $sheet Value1
$spreadsheet cell $sheet Value2 -index 4
$spreadsheet cell $sheet Value3
$spreadsheet row -index 3
$spreadsheet cell $sheet Value4

The result will be Value1 in 0,0 (A1), Value2 in 0,4 (E1), Value3 in 0,5 (F1) and Value4 in 3,0 (A4).

There are two helper to convert row,col to string and the other way around.

::ooxml::StringToRowColumn A1

Result = 0,0

::ooxml::RowColumnToString 0,0

Result = A1

An other helper can calculate the column width of a cell:

::ooxml::CalcColumnWidth 16

Result = 16.7109375

Example Files


Write an Excel file method one: direct addressing with -index.
Output: export1.xlsx


Write an Excel file method two: with row auto increment.
Output: export2.xlsx


Exporting a Tablelist to an Excel file.
Output: export3.xlsx


Write an Excel file with a formula A1+B1.
Output: export4.xlsx


Write an Excel file with defaultdatestyle set.
Output: export5.xlsx


Write a complex Excel file with a lot of formats.
Output: export6.xlsx


Read a complex Excel file (generated by Excel) interpret all known formats and write it back.
Input: original_excel.xlsx
Output: export7.xlsx


Read in an Excel file search and replace some cells and write a new file.
Input: form8.xlsx
Output: export8.xlsx


Write an Excel file testing new options like wrap.
Output: export9.xlsx