Read and write the cell contents of Excel tables.

Read and write the cell contents of Excel tables.

Both Excel file formats are supported:

The functions are used to read and write text contents of Excel sheets.

Table formats are not evaluated by the priint:comet plugins.

To see it positively: Excel's font handling is very creative. Depending on where a font was selected and which font is used, there are seven (!) different spellings for the combination of font family and font face. In general, the dialog Cell Format->Font and the List of fonts in the toolbar displays (and uses!) different font names.

The table shows the different spellings of the (non-existent) font Howgrotesque.

Fontname Description
Howgrotesque Bd Dialog Cell Format->Font Family
Howgrotesque Bold List of fonts in the toolbar Toolbar, and nly the family name Howgrotesque is displayed.
Howgrotesque-Bold In Some fonts the postscript name of the font is used.
HowgrotesqueBold Sometimes Excel finds a font name that not even the Mac Font Collection can find.
Howgrotesque Fett And sometimes the style name is also used in local translation.
Howgrotesque Kursiv, Fett in case of several settings, the style name can also be separated by comma.
Howgrotesque Extrabreit, Schrä Font names are generally truncated at at 31 characters. The information about the bold cut is lost here (and is also no longer applied in Excel).

The additional settings for bold and italic can also be used to change the display of a font. If no corresponding font is installed or available, the bold or italic font will be calculated on the fly - a so-called fake bold or italic. This behavior is not supported by InDesign®, Illustrator® or PDF generators.

When using bold, italic and/or bolditallic by additional settings, always check that the selected font has a corresponding font face at all.

To make the misery complete, Excel also comes with its own fonts. For example, the Mac bundle of Excel contains more than 200 fonts in the folder Microsoft Excel.app/Contents/Resources/DFonts. These fonts are loaded on demand and can only be used by Excel (at least as long as are only in this bundle).

That's all not very pretty right now. To solve these problems we have set up a so-called FontDB, which is permanently installed and is searched for the valid font names if necessary. The FontDB currently contains almost 2,000 font styles. Additionally we have checked almost 500 fonts we found in Excel in the dialog and and in the toolbar list and adjusted the FontDB accordingly.

In all probability, the fonts you use will already be able to be used correctly.

If a font name is still not found in the FontDB, all the TrueType, TrueTypeContainer and OpenType fonts (ttf, ttc, otf) installed on your system are searched:

Searching through the installed fonts can take some time (about 2-4) seconds (but is done at most once per program start)! Also incorrect or missing information in fonts can lead to errors in finding a font - and errors in fonts are more common as expected. During the creation of the FontFB we had to realize that approximately 20-25% of all fonts have errors, especially in the information about font weight, slant and stretch.

For performance reasons and to avoid errors we strongly recommend to insert unknown fonts into your local font database. Here you can find more information:

To resolve the various font names used by Excel, the Excel font name can be appended to the font family name separated by #. To find the information required for a FontDB entry please follow these steps:

Attribute Description Example
font Name of font family use in in InDesign®. font="Meta Pro"
font mit # Complete name of the font used in Excel. The specification is given separated by # after the name of the font family. You will find the used name in the log file: 
# Search definition for Excel font 'MetaPro-CondBook'
font="Meta Pro#MetaPro−CondBook"
face Name of font face like used in in InDesign®. face="Condensed Book"
style, weight, stretch Information on the characteristics of the font. The values are obtained by comparison with the other members of the font family. But note that there must be exactly one variant with the values n/400/0! style="n"
weight="400"
stretch="-2"

Here is an example of FontDB extensions: 

<variant
 	font="Meta Pro#MetaPro-CondBook"
 	face="Condensed Book"
 	style="n"
 	weight="400"
 	stretch="-2"
/>
<variant
 	font="Meta Pro#MetaPro-CondBold"
 	face="Condensed Bold"
 	style="n"
 	weight="700"
 	stretch="-2"
/>

Dates and times can be displayed by Excel in about 175 different languages. All time separators, month and day names used for this are part of Excel's program resources. From the priint:comet plugins and from comet_pdf this information is calculated with the help of the installed locales of the operating system. The locale required in each case is written into the log file: 

# Set locale 'hy_AM' for Excel datetime format '[$-42B]dd/mmm/yyyy;@'

Hardly any operating system contains all locales used by Excel. Since the installation of additional locales heavily depends on the operating system and the locales already installed we cannot help you here unfortunately. However, you can specify a (close as possible) substition locale for each locale that will be used instead. See here for more information about locale substituations.

After translation into the target language, time separators, month and day names may contain characters that are not defined in the cell font. For example, the standard font Calibri font does not contain Japanese, Hebrew or Armenian characters. Excel will automatically select a suitable font for these languages then. Which font is used cannot be selected. The priint:comet plugins and comet_pdf do not do font substitutions by default. However, you can specify suitable fonts for each locale. See here for more information about locale fonts.

If the current language of the operating system is used for an Excel date, Excel will automatically show the date on another computer according to this language. So with a German operating system you will see a German date, with an English operating system an English date. If you want to set your own language, you have to set it explicitly in a user defined format!

The priint:comet plug-ins will use the plug-ins language (de_DE, en_US, fr_FR, ja_JP) in this case. Using comet_pdf you can set the language by the program option --lang [DE|EN|FR|JP].

Surprisingly, Excel automatically replaces the characters '/' and ':' in date and time specifications with the date and time separators of the operating system (and not in the locale of the cell format). We have followed this rule in the priint:comet plugins and in comet_pdf, but (at least) using the language of the plug-ins (de_DE, en_US, fr_FR, ja_JP). Using comet_pdf you can set the language by the program option --lang [DE|EN|FR|JP].

To define locales Substitions and Font Mappings, use the datetime_locales.xml configuration file. This file is searched at the following locations (in the given order). Later specifications overwrite earlier ones:

  1. Folder \span[path]{werkii of your system preferences
  2. Folder Plug-Ins of InDesign® resp. Illustrator® (not used by comet_pdf)
  3. Folder of the priint:comet plug-ins itself, resp. the program folder of comet_pdf
  4. Current data connection (not used in database connections)

There are two types of definitions:

Here is an example: 

<datetime_locales>
 
 	<!-- Map Afrikaans to English (US) - the default on Mac -->
 	<datetime_locale name="af_ZA">
 		<substitution>en_US</substitution>
 		<font/>
 		<face/>
 		<size/>
 	</datetime_locale>
 
 	<!-- Change font for Armenian datetimes -->
 	<datetime_locale name="hy_AM">
 		<substitution/>
 		<font>Arial Unicode MS</font>
 		<face/>
 		<size>120%</size>
 	</datetime_locale>
 
 	<!-- Change font for Japanese datetimes -->
 	<datetime_locale name="ja_JP">
 		<substitution/>
 		<font>HGPGothicE</font>
 		<face/>
 		<size>75%</size>
 	</datetime_locale>
 
</datetime_locales>

To simplify the query of cell contents in placeholders, the 'language' excelquery can be used.

Please note that the excelquery language is defined exclusively in placeholders and for determining the content of single cells only. cScript does not support the language; here the queries can be made easily with the commands of the module excel.

The definition of the language is very simple:

[functionVariables]

"excelFile"
select "formatString"
  with cell
  { ( and | , ) cell }

As an introduction, here is a small example that displays a price in the same font as in the Excel file with superscripted cent amount. For example, the Excel value 123.56789 then becomes 123.57.

The value is given in the cell B1 of sheet ttt of file aaa.xlsx of your desktop. The obtained number is first truncated to an integer using the int function. After this the cell is read again, but we are only interested in the font setting of the cell now and using the fix content '.' instead (so that the point will get the same font as the price). Finally, we take the price a third time again. But now we are only interested in the cent amount, i.e. the (rounded) first two decimal places of the value from Excel. So in total we get three strings. These three results replace the three ? of the result string. 

"$DESKTOP/aaa.xlsx"
select "Unser Preis %s%s<cPosition:Superscript>%s<cPosition:>" with
    tt ttt!B1 int and
    tt ttt!B1 value "." and
    tt ttt!B1 cent

As in all other placeholders, function variables are available of course. As usual, the definitions are given directly at the beginning of the placeholder statement.

Here is a simple example. 

#pragma var "cell//Zelle"
#pragma var "fmt//Format" "plain//Unformatiert" "tt//TaggedText" "html//HTML"

In addition to the <tags> defined by function variables, all placeholder environment tags like <ID>, <ID2>, etc are available.

The decision whether a statement is an Excel or a XML query is determined by the file extension. Excel files must have the extensions xlsx or xls. Incomplete paths are resolved relative to the current XML folder. $ALIASSE names at the beginning of the path are allowed.

The file is specified in quotation marks.

Here is an example. 

"$DESKTOP/aaa.xlsx"

In the format string, you specify how the following cell contents are formatted and which (additional) texts are also to be returned. The syntax corresponds to the generally used syntax for formatted text output in cScript. As values of the % markers the content of the next cell definition of the statement is taken. The contents of the cells must be able to be converted into the corresponding target format. A %f therefore expects a floating number and so on. Any number of % markers are allowed, but missing cell definitions can lead to errors in execution!

The format string must be enclosed in quotation marks!

Here is an example in TaggedText notation 

"Ozr price : %s%s<cPosition:Superscript>%s<cPosition:>"

The cells define the contents of the % markers in the format string. For each %-marker exactly one cell definition with the appropriate data type is expected. The first cell definition is separated from the format string by the keyword with. Cell definitions are separated by and or comma (,) from eachother.

Here is the general EBNF syntax of a cell definition: 

cell			:= numtype address numfunctions
 				|  stringtype address functions

numtype		:= int
 				|  float | double
stringtype		:= string | plain
 				|  tt
 				|  html

address		:= [ sheet '!' ] ( cellName | cellAddress )
sheet			:= ( '"' Name_of_Sheet '"' ) | Name_of_Sheet | Index_of_Sheet
cellName		:= [A-Za-z]+[0-9]+
cellAddress	:= row ',' col [onebased]

numfunctions	:= { numfunction }
numfunction	:= int
 				|  cent
 				|  round fractions
 				|  script action_id

functions		:= { function | numfunction }
function		:= upper | lower
 				|  reverse
 				|  folder | file | shortfile | extender
 				|  substr startpos length
 				|  rsubstr startpos length
 				|  trim '"' letter '"'
 				|  token '"' letter '"' tokennumber
 				|  value '"' any text '"'

That looks more complicated than it is: Each cell definition starts with a type definition. This is followed by the address of the cell in the Excel document. After that cell contents that contain text (string, plain, may be customized byany number of of built-in or self-implemented functions.

Cell contents can be fetched as integers, floats or as text. Please note that numbers can also be fetched as text. Vice versa, however, texts can usually not be fetched as numbers!.

The following data types are supported:

Key Type Description
int Integers Returns the content of the cell as an integer. Floats in the Excel table are reduced to the integer part. (123.89 will return 123). Text contents can usually not be converted to integers!. In the format string, these values can be written by %d.
float, double Floats Returns the content of the cell as a floating number (with up to 20 fractional digits). Text contents can usually not be converted into floats!. In the format string these values can be written by %f or %.2f.
plain, string Unformatted text

Deliver the contents of the cell as unformatted text. Numbers are displayed as decimal numbers with up to 20 digits.

Image cells provide the full path of the loaded image file.

In the format string, these values can be written by %s.

tt TaggedText formatted Text

Returns the content of the cell as TaggedTtext formatted text. For texts that are not post-processed by functions, the RichText settings of the text are applied, if available. Otherwise the font setting of the cell is used. In the format string, these values can be written by %s.

Image cells provide a valid <w2inline>-definition to create an embedded inline of the image.

If the format string does not begin with a valid TT-Trailer, a %!TT is automatically placed in front of the text. In one statement, tagged text and HTML must not be mixed.

The so-called Design Colors of Excel and their tints are currently not supported. Font using these colors is displayed in black.

html HTML-formatierter Text

Deliver the content of the cell as HTML formatted text. For texts that are not post-processed by functions, the RichText settings of the text are applied, if available. Otherwise the font setting of the cell is used. In the format string, these values can be written by %s.

Image cells provide a valid <img>definition to the loaded image file.

If the format string does not begin with a valid HTML-Trailer, a %!TT_html_ is automatically placed in front of the text. In one statement, tagged text and HTML must not be mixed.

The so-called Design Colors of Excel and their tints are currently not supported. Font using these colors is displayed in black.

Cells are addressed by the name of the worksheet (sheet) and the cell name or the cell index.Placeholders typically use 1-based cell indexes to address cells. Using the (optional) post operator onebased the indexes are reduced by 1 accordingly. 

address		:= [ sheet '!' ] ( cellName | cellAddress)
sheet			:= ( '"' Name_of_Sheet '"' ) | Name_of_Sheet | Index_of_Sheet
cellName		:= [A-Za-z]+[0-9]+
cellAddress	:= row ',' col [onebased]

The following specifications are supported:

Information Value Description
Work sheet empty The first worksheet of the file is used
Zahl ! 0-based index of the worksheet. After the specification an exclamation mark (!) must follow!
MyTable ! Name of the worksheet. After the specification an exclamation mark (!) must follow!
"Name with Blanks" !
'Name with Blanks' ! 		Name of the worksheet in quotation marks. After the specification an exclamation mark (!) must follow!
Cell A1, A2, ... The names are the usual Excel specifications A1, A2, ..., B1, B2, ..., AA1, ... supported.
row, col 0-based row,column index of the cell. As 0.0 index the first non-empty cell is used. Attention : The first non-empty cell need not necessarily be the cell A1, see first_col and first_row.

Here are some valid addresses for cells: 

A1              // Cell A1 of the first worksheet
0,0             // First non-empty cell of the first worksheet
2!A1            // Cell A1 of the third worksheet
2!0,0           // First non-empty cell of the third worksheet
Data!A1         // Cell A1 of worksheet Data
Data!0, 0       // First non-empty cell of the worksheet Data
"Data M"!A1     // Cell A1 of worksheet Data M
"Data M"!0, 0   // First non-empty cell of the worksheet Data M

After a value has been retrieved from the Excel file, it can be customized by using built-in or self-implemented functions. Please note that the string functions like upper, substr etc. can only be used for string values! The processing of the functions is strictly sequential: The first function receives the original text from the Excel file, the second function receives the output of the first function, and so on.

Possible function parameters are separated by at least one whitespace (not comma).

RichText information of the Excel cell cannot be applied in this case (same like in Excel). For the formatted output always uses the font setting of the cell.

The following functions are defined:

Function Parameter Description Example
int nothing

Remove the decimal places of a number. The integer part of the number is not rounded!

Attention: Values of type float or double retain their data types. To get the result in the output string without decimal places, you may use %.0f.

123.789 will change to 123
round int - Precision Rounding an integer to the specified precision. (e.g. 100).

Rounding a floating point number to the specified number of decimal places (e.g. 2) 		round 100 will change to 1187 1200

round 0 will change to 123.789 124
cent keine

Convert the first two decimal places to a two-digit and rounded integer between 00-99.

Attention: Values of type float or double retain their data types. To get the result in the output string without decimal places, you may use %.0f.

Aus 123.1004 becomes 10
Aus 123.789 becomes 79
Aus 123.001 becomes 00
upper, lower nothing Convert the text to upper or lower cases Converts Hallo Köln to HALLO KÖLN
Converts PräHistorisch to prähistorisch
reverse nothing Reversing a text. The German boy Hänschen doesn't become (the man) Hans but nehcsnäH
folder, file, shortfile, extender nothing Determining parts of a file path.
substr int  - Start
int  - Length		 Partial string of a text. Position and length are letters (and not bytes). substr 1 1 of Hänschen is ä (although das UTF-8 ä consists of two bytes).
substr int - Start
int - Length		 Partial string from the end of a text. Position and length are letters (and not bytes). rsubstr 2 1 of Köln is ö (although das UTF-8 ö consists of two bytes).
trim str - Token Remove all occurrences of the token str from the beginning and end of the string. trim "-" of ---Info--- is Info.
token str - Delimiter
int - Index 		Get the nth token of a string separated by str. token " " 1 of Hello World is World.
value str - Any Text The determined cell text is replaced by str. This function is useful if the formatted text output should have some additional text formatted in the same way as the cell text (see example).
script int - Action ID Execute a defined action of the table/file actions of the current data pool. The script has the following global variables:
  • The following variables contain the current value of the cell before the script call and may be changed in the script. If the script terminates without errors (e.g. by return 0;), the new value will be applied for further use. Which variable is used depends on the data type of the cell definition in the excelquery call:
    • int * gResultInt; Cell definitions of type int
    • float * gResultFloat; Cell definitions of type float oder double
    • String gResultString; Cell definitions of type plain, string, tt, ...
  • The following additional global variables are defined. These variables must not be changedn!
    • String gExcelPath; Full path of the current Excel file.
    • ExcelBook gExcel; The currently opened Excel file. The file must not be closed by the script!
    • The following general global variables are also defined: gRecordID, gRecordID2, gRecordID3, gRecordStringID, gRecordStringID1, gRecordStringID2, gRecordStringID3 and gDocumentID.
Here is a very simple script: 
int main ()
{
   string::append (
       gResultString,
       "from script");
   return 0;
}

 

static ExcelBook open(
  char* path,
  int autosave = 0,
  int useGlobal = 1)

Open an Excel file.

Name Type Default Description
Return ExcelBook   0 : Fehler
otherwise : Opened Excel file for further use in the excel functions, see the descriptions of the parameter useGlobal.
path char* oder String - Full path of the Excel file. The path may begin with a $ALIAS name.
autoSave int 0 Should the file be saved automatically before closing?

0 : No
1 : Yes
useGlobal int 1 Open file globally or only for the current script?

0 : Open file only for the current script. In this case the received result of the function must be closed again with close!

1 : Open file globally. The Excel file remains open until thr next idle time or a call to close. If the file has already been opened in a previous script, this already opened file is returned. Since you probably want to use the file in multiple scripts, this is the recommended procedure.

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

close
comet.excel.open

static int count_sheets(ExcelBook ebook)

Number of sheets in the Excel file.

Name Type Default Description
Return int   Number of sheets in the Excel file.

0 : Error
ebook ExcelBook - With excel::open opened Excel file

v4.1.7 R26846, 22. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
comet.CExcelBook.getSheetCount

static char* nth_sheet(ExcelBook ebook, int nth)

Name of the n-th sheet of an Excel file

Name Type Default Description
Return char*   Name of the n-th sheet of an Excel file
"" : error

The return value is read only, do NOT CHANGE. The result will be overridden on following calls to the function. See here to learn more about functions returning r/o char* values.
ebook ExcelBook - With excel::open opened Excel file
int nth - 0-based index of the sheet 
#include "internal/types.h"
#include "internal/text.h"

int main ()
{
    ExcelBook	ebook;
    String 		str 	= string::alloc ();
    int 		n;

    // Open the Excel file
    //
    ebook = excel::open ("$DESKTOP/aaa.xlsx");
    if (!ebook)
    {
        showmessage ("Cannot open Excel file");
        string::release (str);

        return 0;
    }

    // Collect all sheet names
    //
    for (n = 0; n < excel::count_sheets (ebook); n++)
    {
        string::append (str, "%s\n", excel::nth_sheet (ebook, n));
    }
    frame::replace (gFrame, str);

    // Clean up
    //
    excel::close (ebook);
    string::release (str);

    return 0;
}

v4.1.7 R26846, 22. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
count_sheets
comet.CExcelBook.getSheetName

static int first_row(ExcelBook ebook, char* sheet)

Get the index of the first used row of an Excel spreadsheet.

Name Type Default Description
Return int   >= 0 : 0-based index of the first row in the sheet that contains a used cell

-1 : Error
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String "" Name of data sheet
"" : First data sheet

v4.1.7 R26846, 22. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
last_row
first_col
last_col
comet.CExcelBook.getFirstRow

static int last_row(ExcelBook ebook, char* sheet)

Get the index of the first unused row under an Excel sheet.

Name Type Default Description
Return int   >= 0 : 0-based index of the row after the last row in the sheet that contains a used cell.

-1 : Error
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String "" Name of data sheet
"" : First data sheet

v4.1.7 R26846, 22. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
first_row
first_col
last_col
comet.CExcelBook.getLastRow

static int first_col(ExcelBook ebook, char* sheet)

Get the index of the first used column of an Excel sheet.

Name Type Default Description
Return int   >= 0 : 0-based index of the first column in the sheet that contains a used cell

-1 : Error
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String "" Name of data sheet
"" : First data sheet

v4.1.7 R26846, 22. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
first_row
last_row
last_col
comet.CExcelBook.getFirstColumn

static int last_col(ExcelBook ebook, char* sheet)

Get the index of the first not used column right behind an Excel sheet.

Name Type Default Description
Return int   >= 0 : 0-based index of the column after the last column in the sheet that contains a used cell.

-1 : Error
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String "" Name of data sheet
"" : First data sheet

v4.1.7 R26846, 22. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
first_row
last_row
first_col
comet.CExcelBook.getLastColumn

static float get_rowheight(
  ExcelBook ebook,
  char* sheet,
  int row)

Height of a row in a table

Name Type Default Description
Return float   > 0.0 : Height of the row in points
0.0 : Error
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table

v4.1.7 R26848, 24. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

get_colwidth
comet.CExcelBook.getRowHeight

static float get_colwidth(
  ExcelBook ebook,
  char* sheet,
  int col,
  float* excelV)

Width of a column of a table relative to the total width of the table.

Name Type Default Description
Return float   > 0.0 : > 0.0 : Percentage of the column of the total width of the table in the range [0.0 - 1.0)
0.0 : Error

Microsoft defines the width of columns in a very strange way:

The column width is measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font.

Whatever that means, this value is always given as column width in the Excel documents. We don't say anything about this here 😷 but with this value you probably won't be able to do too much 😧.

As a result of the column width, we return the percentage of the column of the total width of the table.

ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
col int - 0-based column of the table
excelV float* 0 Column width stored in the Excel file, see above.

0 : Ignore value

v4.1.7 R26848, 24. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

get_rowheight
comet.CExcelBook.getColumnWidth

static float get_colwidth_pt(
  ExcelBook ebook,
  char* sheet,
  int col)

Width of a column of a table.

Name Type Default Description
Return float   With of the column in pt

0.0 : Error
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
col int - 0-based column of the table


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

get_rowheight

static float get_merge(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int* oRowStart,
  int* oRowEnd,
  int* oColStart,
  int* oColEnd)

Determine the merged area of a cell.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet String or char* - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
oRowStart int * - [Return] 0-based row of the anchor cell
0 : Ignore value
oRowEnd int * - [Return] 0-based first row which does not belong to the area
0 : Ignore value
oColStart int * - [Return] 0-based column of the anchor cell
0 : Ignore value
oColEnd int * - [Return] 0-based first column which does not belong to the area
0 : Ignore value


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.getCellArea

static int get_color(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int which,
  int* oRed,
  int* oGreen,
  int* oBlue)

Determine the configured colors of a cell.
When fetching the outer border colors for merged cells, you have to fetch them from the actual indices and not the anchorcell.
e.g. When trying to determine the right border of a cell merged from column 0-1, you have to query the right border color at column 1.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet String or char* - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
which int - Which color should be determined?

0: Border left
1: Border top
2: Border right
3: Border bottom
4: Border diagonal
5: Pattern foreground
6: Pattern background
oRed int * - [Return] Red part of the color in range [0-255]
When the value is negative, it is not set.
oGreen int * - [Return] Green part of the color in range [0-255]
When the value is negative, it is not set.
oBlue int * - [Return] Blue part of the color in range [0-255]
When the value is negative, it is not set.


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.getColor

static int get_border(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int which,
  int* oStyle)

Determine the configured border style of a cell.
When fetching the outer border for merged cells, you have to fetch them from the actual indices and not the anchorcell.
e.g. When trying to determine the right border of a cell merged from column 0-1, you have to query the right border color at column 1.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet String or char* - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
which int - Which border style should be determined?

0: Border style left
1: Border style top
2: Border style right
3: Border style bottom
4: Border style diagonal
oStyle int * - [Return] Set border style:
-1 - Not set or error
0 - BORDERSTYLE_NONE
1 - BORDERSTYLE_THIN
2 - BORDERSTYLE_MEDIUM
3 - BORDERSTYLE_DASHED
4 - BORDERSTYLE_DOTTED
5 - BORDERSTYLE_THICK
6 - BORDERSTYLE_DOUBLE
7 - BORDERSTYLE_HAIR
8 - BORDERSTYLE_MEDIUMDASHED
9 - BORDERSTYLE_DASHDOT
10 - BORDERSTYLE_MEDIUMDASHDOT
11 - BORDERSTYLE_DASHDOTDOT
12 - BORDERSTYLE_MEDIUMDASHDOTDOT
13 - BORDERSTYLE_SLANTDASHDOT


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.getBorderStyle

static int read(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int type,
  char* value,
  int maxlen = 0,
  int format = 0,
  int dtFormat = -1)

Read a value from an Excel sheet.

Please see also the notes to Fontnames. Missing font assignments can be corrected locally with local extensions of the font table (FontDB).

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
type int - Data type of the output variable value.

1 - kInt : Integer
2 - kFloat : Floating point number
3 - kString : Text, datetime or image. For datetime formats see dtFormat

kIntand kFloat can only be ised in number cells. In other cells the function generates an error!
value int*, float* char*, String - Output variable, the type depends on parameter type. After successful execution of the function, the variable contains the value of the cell. When trying to read numbers from text or date cells, the function returns an error!

1 - kInt : &int_var
2 - kFloat : &float_var
3 - kString : char* oder String
maxlen int 0 Only if type = kString and char* : Maximum length of result in bytes

0 : Any
format int 0 For type = kString only: Format of the output

0 - kExportPlain : Unformatted text
1 - kExportTagged : TaggedText
12 - kExportHTML : HTML

-1 - kUndef : Direct value. The output is useful if you want to get the cell content without Excel formatting. Decimal numbers are given with (up to) six decimal places. Datetimes are given in the format dtFormat. If dtFormat = -1, yyymmddhhmmss is used.

For the output of images see here.
dtFormat int -1 Date and time formatting. The specification is only applied to user-defined cell formats!

-1 : Apply cell format, see Date & Time

otherwise : Valid built-in DateTime format, see system::now
#include "internal/types.h"

Open an Excel file and write the contents of the first table into the current frame. If the file is used only in this script, it should be closed with excel::close at the end of the script. 

#pragma plain

#include "internal/types.h"
#include "internal/text.h"

int main ()
{
    ExcelBook	ebook;
    String 		str 	= string::alloc ();
    String 		vs		= string::alloc ();
    String 		ipath	= string::alloc ();
    ItemRef		fr 		= item::alloc ();
    int 		r, c, hostApp;
    char 		sheet[256];
    int 		result;
    int 		w, h, x, y;

    system::version(&hostApp);
    if (hostApp < 2)	item::define (fr, 0, item::getint (gFrame));
    else				item::define (fr, 0, 221);		// comet_pdf

    // Open the Excel file
    //
    ebook = excel::open ("$DESKTOP/Formats.xlsx");
    if (!ebook)
    {
        showmessage ("Cannot open Excel file");
        string::release (str);

        return 0;
    }
    strcpy (sheet, "Table 2");

    // Read data and build the output string *str*
    //
    string::append (str, "%!TT"); 	// Init the output

    for (r = excel::first_row (ebook, sheet); r < excel::last_row (ebook, sheet); r++)
    {
        for (c = excel::first_col (ebook, sheet); c < excel::last_col (ebook, sheet); c++)
        {
            wlog ("", "\n##### CELL [%c%d]\n", c+0x41, r+1);

            string::append (str, "<ParaStyle:aaa>[%c%d] Type = %d : ",
             			c+0x41,
             			r+1,
             			excel::get_type (ebook, sheet, r, c));

            if (excel::get_type (ebook, sheet, r, c) == 8)
            {
                // It'a an image.
                // Add the image path and geometry to the output.
                //
                excel::read_image (ebook, sheet, r, c, ipath, &w, &h, &x, &y);
                string::append (str, "('%s', %d x %d with offset %d x %d) ",
                 		ipath,
                 		w, h,
                 		x, y);
            }

            // Read cell data as TaggedText.
            // Numbers are formatted as close as possible to Excel.
            // Images are created as embedded inlies automatically.
            //
            result = excel::read (ebook, sheet, r, c, kString, vs, 0, kExportTagged);
            if (result == 0)
            {
                string::append (str, "%s", vs);
            }
            else
            {
                string::append (str, "Error while reading\n");
            }
        }
        string::append (str, "<ParaStyle:aaa>");
    }

    // Finally insert the complete text
    //
    frame::replace (fr, str);

    // There is no need to close the book again.
    // This is done automatically at the next idle time.
    //

    return 0;
}

For HTML output, simply replace the prefix %!TT by %!TT_html_ and change the format in excel::read tokExportHTML. 

 

v4.1.7 R26836, 21. Apr 2020 Parameter dtFormat and decimals since v4.2 R32100

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
get_type
get_format
get_font
read_image
comet.CExcelBook.read

static int read_image(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  String path = 0,
  int* width = 0,
  int* height = 0,
  int* xoffset = 0,
  int* yoffset = 0)

Read an image from an Excel sheet. The image is automatically loaded into an image file.

Excel cells are recognized as 'image cells' only if the cell does not contain any text! The cell of an image is the cell of the table where the upper left corner of the image is located. Whether a cell contains an image can be determined with the help of excel::get_type (=8).

Name Type Default Description
Return int   0 or ErrorCode

noImageErr (101) : Not an image cell
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
path String oder char* 0 On successful execution, the full path of the loaded image file. Otherwise the value remains unchanged. Please note that String and char* are already pointers, so the usual & for output variables must not be used here!

0 : Ignore value
width int* 0 If successful, the width of the image in pixels. Otherwise the value remains unchanged. Please note that the call is an address to a number wich usually is written as &var!

0 : Ignore value
height int* 0 If successful, the height of the image in pixels. Otherwise the value remains unchanged. Please note that the call is an address to a number wich usually is written as &var!

0 : Ignore value
xoffset int* 0 If successful, the distance of the image in pixels from the left side of the cell. Otherwise the value remains unchanged. Please note that the call is an address to a number wich usually is written as &var!

0 : Ignore value
yoffset int* 0 If successful, the distance of the image in pixels from the top edge of the cell. Otherwise the value remains unchanged. Please note that the call is an address to a number wich usually is written as &var!

0 : Ignore value

v4.2 R32245, 1. Feb 2023

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
get_type
read

static int get_font(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int fmt,
  String tagsOpen,
  String tagsClose)

Font settings of a given cell. This function is necessary if you want to output cells containing numbers (for example, a currency specification). With read you only get the number value without using the formatting (e.g. 12,99 €). You can apply this formatting in the script. But if you want to output this result in the same font as in Excel, you need the font settings of the cell.

Depending on the number of function parameters the cell style is returned either as an HTML/CSS or tagged text string or as individual values of the font properties:

Please note that the return values weight and italic of the function are combinations from the selected (fake) setting of Excel and the used font face!

The so-called Design Colors of Excel and their tinted versions are currently not supported. Font in these colors is displayed in black.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
⇨ Output as format string, exactly 7 parameters required
fmt int - Output format

1 - kExportTagged : TaggedText
12 - kExportHTML : HTML
tagsOpen char* oder String - Allocated string for the format-specific output of the tags for setting the font of the cell.
tagsClose char* oder String - Allocated string for the format-specific output of the tags for resetting the font of the cell.
⇨ Individual values, at least 8 parameters required
fontname char* oder String 0 Name of font family
fontface char* oder String 0 Name of the font face with taking bold and italic into account . The font face is usefull for the <cTypeface:...> tag of TaggedText. For HTML output the font face is not needed.
size int* 0 Font size in pt.
red int* 0 Red portion of the font color in the range 0-255
green int* 0 Green portion of the font color in the range 0-255
blue int* 0 Blue portion of the font color in the range 0-255
weight int* 0 Weight of font, 100, 200, ..., 900
italic int* 0 Italic font used??

0 : No
1 : Yes

he value is needed for HTML output. For TaggedText the value is not needed, the slant is determined by the font face here.
strikeThru int* 0 Is the text striked thru?

0 : No
1 : Yes
underline int* 0 Is the text underlined?

0 : No
1 : Single underline above _
2 : Double underline above _
3 : Single underline below _
4 : Double underline below _
position int* 0 Position of text

0 : Normal
1 : Super
2 : Sub
stretch int* 0 Character spacing

-4 : ultra-condensed
-3 : extr-condensed
-2 : condensed
-1 : semi-condensed
0 : normal
1 : semi-expanded
2 : expanded
3 : extrs-expanded
4 : ultra-expanded
#include "internal/types.h"

Write the contents of the first table in the same text formatting as in Excel. Local changes in the cells are ignored (but can be made by read.) 

#include "internal/types.h"
#include "internal/text.h"

int main ()
{
    ExcelBook	ebook;
    String 		str 		= string::alloc ();
    String 		openTags 	= string::alloc ();
    String 		closeTags 	= string::alloc ();
    String 		vs			= string::alloc ();
    int 		r, c;

    // Open the Excel file
    //
    ebook = excel::open ("$DESKTOP/aaa.xlsx");
    if (!ebook)
    {
        showmessage ("Cannot open Excel file");
        string::release (str);

        return 0;
    }

    frame::replace (gFrame, "");

    // Load data
    //
    for (r = excel::first_row (ebook); r < excel::last_row (ebook); r++)
    {
        for (c = excel::first_col (ebook); c < excel::last_col (ebook); c++)
        {
            // Get the formated styles
            //
            excel::get_font (ebook, "", r, c, kExportTagged, openTags, closeTags);

            string::set (str, "%%!TT[%c%d] : ", c+0x41, r+1);

            if (excel::read (ebook, "", r, c, kString, vs, 0) == 0)
            {
                string::append (str, "%s", openTags);
                string::append (str, "%s", vs);
                string::append (str, "%s", closeTags);
            }
            else
            {
                string::append (str, "Error while reading\n");
            }

            frame::append (gFrame, str);
            frame::append (gFrame, "\n");
        }
        frame::append (gFrame, "\n");
    }

    // Clean up
    //
    excel::close (ebook);
    string::release (str);

    return 0;
}

Here is a suggestion for converting the Excel font properties to HTML/CSS 

#include "internal/types.h"
#include "internal/text.h"

int get_style (String sopen, String sclose, ExcelBook ebook, int r, int c, char * sheet)
{
    String 	fontname	= string::alloc ();
    String 	fontface	= string::alloc ();
    int 	size;
    int		red, green, blue;
    int		weight, italic, strikeThru, underline, position, stretch;
    int 	res;

    // Init
    //
    string::set (sopen, "");
    string::set (sclose, "");

    // Read font settings of cell
    //
    res = excel::get_font (ebook, sheet, r, c,
     		fontname, fontface,
     		&size,
     		&red, &green, &blue,
     		&weight,
     		&italic,
     		&strikeThru,
     		&underline,
     		&position,
     		&stretch);
    if (res != 0)
    {
        return 1;
    }

    // Create and fill a new HTML span
    //
    string::append (sopen, "<span style='");

    string::append (sopen, "font-family:\"%s\"; ", fontname);
    string::append (sopen, "font-size:%dpt; ", size);
    string::append (sopen, "font-weight:%dpt; ", weight);
    string::append (sopen, "color:rgb (%d, %d, %d); ", red, green, blue);

    if (strikeThru)	string::append (sopen, "text-decoration:line-through; ");
    if (underline)	string::append (sopen, "text-decoration:underline; ");

    if (position == 1)	string::append (sopen, "vertical-align:super; ");
    if (position == 2)	string::append (sopen, "vertical-align:sub; ");

    if (stretch == -4)      string::append (sopen, "font-stretch:ultra-condensed; ");
    else if (stretch == -3) string::append (sopen, "font-stretch:extra-condensed; ");
    else if (stretch == -2) string::append (sopen, "font-stretch:condensed; ");
    else if (stretch == -1) string::append (sopen, "font-stretch:semi-condensed; ");
    else if (stretch == 0)  string::append (sopen, "font-stretch:normal; ");
    else if (stretch == 1)  string::append (sopen, "font-stretch:semi-expanded; ");
    else if (stretch == 2)  string::append (sopen, "font-stretch:expanded; ");
    else if (stretch == 3)  string::append (sopen, "font-stretch:extra-expanded; ");
    else if (stretch == 4)  string::append (sopen, "font-stretch:ultra-expanded; ");

    string::append (sopen, "'>");

    // Add italic if needed
    //
    if (italic)	string::append (sopen, "<i>");

    // Create the close tags in reverse order
    //
    if (italic)	string::append (sclose, "</i>");
    string::append (sclose, "</span>");

    // Clean up
    //
    string::release (fontname);
    string::release (fontface);

    return 0;
}

int main ()
{
    String 		str 	= string::alloc ();
    ExcelBook	ebook;
    int 		vi;
    float		vf;
    String 		vs	= string::alloc ();
    String 		format	= string::alloc ();
    String 		formula	= string::alloc ();
    String 		sopen	= string::alloc ();
    String 		sclose	= string::alloc ();
    int 		r, c;
    int 		formatID;
    int 		convqu	= prefs::get_convert_quotas ();
    int			dtY, dtM, dtD, dth, dtm, dts;

    // Open the Excel file
    //
    ebook = excel::open ("$DESKTOP/aaa.xlsx");
    if (!ebook)
    {
        showmessage ("Cannot open Excel file");
        string::release (str);

        return 0;
    }

    prefs::set_convert_quotas (kOff);

    // Load some data here
    //
    frame::replace (gFrame, "");
    for (r = excel::first_row (ebook); r < excel::last_row (ebook); r++)
    {
        for (c = excel::first_col (ebook); c < excel::last_col (ebook); c++)
        {
            get_style (sopen, sclose, ebook, r, c, "");

            string::set (str, "%%!TT_html_[%c%d] : %s", c+0x41, r+1, sopen);

            if (excel::read (ebook, "", r, c, kInt, &vi) == 0)
            {
                string::append (str, "%d", vi);
            }
            else if (excel::read (ebook, "", r, c, kFloat, &vf) == 0)
            {
                string::append (str, "%.6f", vf);
            }
            else if (excel::read (ebook, "", r, c, kString, vs) == 0)
            {
                if (excel::get_type (ebook, "", r, c) == 5)
                {
                    system::time_scan (vs, yyyymmddhhmmss, &dtY, &dtM, &dtD, &dth, &dtm, &dts);
                    string::append (str, "%02d.%02d.%d, %02d:%02d:%02d", dtD, dtM, dtY, dth, dtm, dts);
                }
                else
                {
                    string::append (str, "%s", vs);
                }
            }

            string::append (str, sclose);

            formatID = 0;
            string::set (format, "");
            excel::get_format (ebook, "", r, c, &formatID, format);
            if (formatID > 0) 			string::append (str, " / Format %d", formatID);
            if (string::length (format))	string::append (str, " <span class=\"format\">%s</span>", format);

            if (excel::get_formula (ebook, "", r, c, formula) == 0)
            {
                string::append (str, " / <span class=\"formula\">%s</span>", formula);
            }

            string::append (str, " ");

            frame::append (gFrame, str);
            frame::append (gFrame, "\n");
        }
        frame::append (gFrame, "\n");
    }

    // Clean up
    //
    string::release (str);
    string::release (format);
    string::release (formula);
    string::release (sopen);
    string::release (sclose);
    excel::close (ebook);

    prefs::set_convert_quotas (convqu);

    return 0;
}

v4.1.7 R26855, 23. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
read
get_format
comet.CExcelBook.getFontFamily
comet.CExcelBook.getFontFace
comet.CExcelBook.getFontSize
comet.CExcelBook.getFontColor
comet.CExcelBook.getFontStrikeThrough
comet.CExcelBook.getFontUnderline
comet.CExcelBook.getFontPosition
comet.CExcelBook.getFontWeight
comet.CExcelBook.getFontItalic
comet.CExcelBook.getCellFormatTags

static int get_type(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col)

Type of cell of an Excel sheet.

Name Type Default Description
Return int   Typ der Tabellenzelle

0 : Error
1 : Number (int or float)
3 : Text
4 : Boolean
5 : Date, Time
6 : Blank, the cell contains only format information
7 : Empty, the cell doesn't exist
8 : Image (since v4.2 R32250) The cell of an image is the cell where the upper left corner of the image is located. Only cells without text can be recognized as image cells.
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.getCellType

static int get_formula(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  char* formula,
  int maxlen = 0)

Formula of cell of an Excel sheet.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
formula char* oder String - Formula to calculate the content of the cell
maxlen int 0 Only at char* : Maximum length of the result.

0 : Any

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.getCellFormula

static int get_format(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int* formatID,
  char* format,
  int maxlen = 0)

Get the formula of cell of an Excel sheet.

The following table contains a list of the standard formats. The result string format of the standard formats is empty. User-defined formats have IDs >= 164. The definition of these formats is returned in the result string format. The IDs 36-163 are unused.

IDDescriptionExample
general format
general number1000
number with decimal point1000.00
number with thousands separator100,000
number with decimal point and thousands separator100,000.00
monetary value, negative in brackets(1000$)
monetary value, negative is red in brackets(1000$)
monetary value with decimal point, negative in brackets($1000.00)
monetary value with decimal point, negative is red in brackets($1000.00)
percent value, multiply the cell value by 10075%
percent value with decimal point, multiply the cell value by 10075.00%
scientific value with E character and decimal point10.00E+1
fraction value, one digit10 1/2
fraction value, two digits10 23/95
date value, depends on OS settings3/11/2009
custom date value11-Mar-09
custom date value11-Mar
custom date valueMar-09
custom date value8:30 AM
custom date value8:30:00 AM
custom date value8:30
custom date value8:30:00
custom datetime value3/11/2009 8:30
number with thousands separator, negative in brackets(4,000)
number with thousands separator, negative is red in brackets(4,000)
number with thousands separator and decimal point, negative in brackets(4,000.00)
number with thousands separator and decimal point, negative is red in brackets(4,000.00)
account value5,000
account value with currency symbol$ 5,000
account value with decimal point5,000.00
account value with currency symbol and decimal point$ 5,000.00
custom time value30:55
custom time value20:30:55
custom time value30:55.0
custom value15.2E+3
text valueany text

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
formatID int* - ID dof cell format

< 164 : Excel Standard Format
>= 164 : User defined format. The format statement is specified in the result string format.
format char* oder String - User-defined format statement for the cell contents
maxlen int 0 Only at char* : Maximum length of the result.

0 : Any
#include "internal/types.h"

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.getCellFormat

static int write(
  ExcelBook ebook,
  char* sheet,
  int row,
  int col,
  int type,
  char* value,
  int maxlen = 0)

Write a value into a cell of an Excel sheet.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
row int - 0-based row of the table
col int - 0-based column of the table
type int - Data type of cell in Excel table?

1 - kInt : Integer
2 - kFloat : Floating
3 - kString : Text
4 - kBool : Bool
value int, float char*, String - The data type of the variable depends on the value of the parameter type:

1 - kInt : int
2 - kFloat : float
3 - kString : char* or String
4 - kBool : int
#include "internal/types.h"

Writing Excel content 

#include "internal/types.h"
#include "internal/text.h"

int main ()
{
    ExcelBook	ebook;
    int 		r;

    // Open the Excel file
    //
    ebook = excel::open ("$DESKTOP/bbb.xlsx");
    if (!ebook)
    {
        showmessage ("Cannot open Excel file");
        string::release (str);
        return 0;
    }

    // Write some data here
    //
    for (r = 1; r < 11; r++)
    {
        excel::write (ebook, "", r, 0, kString, "28.10.2018, 12:34");
    }

    // Clean up
    //
    excel::save (ebook);
    excel::close (ebook);

    return 0;
}

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.write

static int save(ExcelBook* ebook)

Save an Excel file.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
close
comet.CExcelBook.save

static int to_xml(
  ExcelBook* ebook,
  char* sheet,
  int destType,
  char* dest,
  int maxLen = 0)

Export an Excel sheet into XML. The function generates the following XML format: 

<?xml version="1.0" encoding="utf-8"?><rows>
 	<row num="1" id="0">
 		<col name="A" num="1" id="0">
 			<format>Zellformat</format>
 			<value>Direkter Zellinhalt</value>
 			<text>Zellinhalt mit angewendetem Zellformat</text>
 		</col>
 		...
 	</row>
 	...
</rows>

We would like to point out explicitly that the XML export is a data export. For a complete XML with cell sizes, colors, lines etc. the export is not suitable! But you can easily implement this yourself as a script using the functions in this module.

Here are some valid xmlquery queries. 

// All cells with number and content
select ...num, num, value node rows.row.cols.col

// All cells with name (A1...) and content
select name||...num, value node rows.row.cols.col

// All cells with the content 'a
select ...num, num, value node rows.row.cols.col where value='a'

// All rows whose first two columns contain 'a'.
select ..num, col.num node rows.row.cols where child_0.value='a' and child_1.value='a'

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file
sheet char* oder String - Name of data sheet
"" : First data sheet
destType int - What does the following parameter dest contain?

-1 : Allocated result string
0 : Full file path for the result
1 : Full file path of a file that can be overwritten
dest char* oder String - Result string or file path depending on the parameter destType.
maxlen int 0 Maximum length of the result in bytes. The parameter has meaning only if destType = -1 and dest are of type char* / char[nnn].

0 : Arbitrarily long (but then you should be sure that your target variable is large enough!)

v4.2 R32320, 9. Feb 2023

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

open
close

static int close(ExcelBook* ebook)

Close an Excel file and remove the object from memory.

Name Type Default Description
Return int   0 or ErrorCode
ebook ExcelBook - With excel::open opened Excel file

v4.1.7 R26836, 21. Apr 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

comet.CExcelBook.close

static int col2i(char* colname)

Convert an Excel column name of the form A, B, ..., Z, AA, AB, ... ...into a number.

Name Type Default Description
Return int   0-based numerical value of the input string
colname char* oder String - Character string consisting of the letters A-Z and a-z. Lower case letters are automatically evaluated as upper case letters.

Please note that Excel supports a maximum of 214 = 16,384 columns only. This corresponds to the letter string XFD.

v4.1.7 R27182, 9. Jun 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

i2col
comet.excel.columnToIndex

static char* i2col(int colnum)

Conversion of a column number into the form A, B, ..., Z, AA, AB, ..., which is commonly used in Excel.

Name Type Default Description
Return char*   Excel name of the given 0-based column index
"" : error

The return value is read only, do NOT CHANGE. The result will be overridden on following calls to the function. See here to learn more about functions returning r/o char* values.
colnum int - 0-based column index

Please note that Excel supports a maximum of 214 = 16,384 columns only. This corresponds to the letter string XFD.

v4.1.7 R27182, 9. Jun 2020

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

col2i
comet.excel.indexToColumn

Since
v4.1.7 R26836, 21. Apr 2020

Alphabetic index HTML hierarchy of classes or Java