Ath's software collection

Excel2Html

Excel2Html

Version: 0.5.0.0

Download (353 kB) (deprecated)

Version: 2.2.1.0

Download (initial/full) (15.63 MB)

Download (no lib, app only) (68.3 kB)

Download (lib only, no app!) (15.16 MB)

 Last update: v2.2.1.0, 2012-05-10

Category v0.x: AutoIt3

Category v2.x: Java 6

  Support: DonationCoder thread

Convert rows of Excel data into a html table or a set of html files, using a template, for use as a Mail Merge tool.

Written as a response to this DonationCoder Coding Snack request

A command-line tool to transform an Excel file into 1, or several, html files, using all the data in the sheet. The output html is created based on a template specified.
v0.x: Requires Excel to be installed locally, based on an AutoIt3 script snippet from rarst.net

v2.x: Requires Java 6 (JRE) to be installed, but Excel is no longer needed. Using Apache POI for reading MS Excel .xls and .xlsx files.
Most important changes going from the AutoIt3 (v0.x) to the Java (v2.x) version:

  • The application is not compiled into an .exe, but delivered as a .jar, and requires at least Java runtime environment (JRE) version 6 installed.
  • The lib directory contains the required Apache POI and ini4j .jar files, and should remain in the same relative location of Excel2Html.jar
  • Error messages are displayed to the console, and no longer displayed as messageboxes. The -et parameter is no longer supported.
  • Excel does not need to be installed on the computer that runs the conversion.
  • Format strings should be specified for Java's String.format()
  • Logging can be more extensively supplied, with log detail levels from 1 (-d/-1) up to 4 (-4)
  • The source is not (yet) included

What's new:
v2.2.1.0: Replaced POI 3.8-beta3 by POI 3.8 Final release
v2.2.0.0: Added conditional filtering (not finished yet)
v2.1.0.0: Added a loop feature by tags in the template
v2.0.1.2: Added a linux .sh script like the Windows .cmd script, after some testing on Ubuntu Linux
v2.0.1.1: Added 2 command-line only parameters -sf (ShowFormula) and -dt (DisplayTags)
v2.0.1.0: Fixed issue with not displaying/evaluating formulas as expected/intended.

Command-line parameters: (based on v2.x)

-? Help display, show a message with all parameters shortly explained.
-d Debug log mode enabled, writes extra information to Excel2Html.log during processing. Setting in inifile: debug=1
Most useful if placed as first on the commandline, then also logs command line parsing. Detail level = 1
-1 .. -4 Set Debug log mode on, with detail level 1..4. Setting in inifile: debug=<n>
-d- Disable Debug log mode (default), to override debug=1 in the inifile
-0 Disable Debug log mode (like -d parameter)
-f filename The excel file to read the data from. See also -fs parameter. Setting in inifile: excelfile=filename
Example: -f testaddresses.xlsx
-fs sheet The sheet number or name to read to data from. By default sheet 1 is read. Setting in inifile: sheet=1
-t templatefile The html template file to read as source for the output. Default: Excel2Html-template.html. Setting in inifile: templatefile=Excel2Html-template.html
-o outputfile The file to write the output to. If a valid filename is specified, than alldata will be written to that file. Setting in inifile: outputfile=dump.html
  If  the specified name contains ? or * or [ then multifile mode is enabled, and a file for each datarow will be written. ? and * are replaced by the rownumber, [col] is replaced by the data of the column specified
  Examples:
-o dump.html : All output is written to dump.html
-o dump-*.html : Data is written to dump-1.html, dump-2.html, etc.
-o dump-[1]_[2].html : Data (testaddresses.xlsx is assumed) is written to dump-Fred_Flintstone.html, dump-Barney_Rubble.html, etc.
  These parameters can be combined, so -o dump-*-[2].html would give dump-1-Flintstone.html, etc.
-rf "format" Specify the format-string (C sprintf parameters) to use for rownumber formatting as used in filenames. Default: %d  Setting in inifile: rowformat=%d
  Examples:
-rf "%04d" : assuming -o dump-*.html was also specified, the filenames would be dump-0001.html, dump-0002.html, etc.
-df "format" Set the format used to convert date fields, default = "dd-MM-yyyy". Setting in inifile: dateformat=dd-MM-yyyy
Because the formatting can not be extracted correctly from the excel sheet (yet), this has to be set.
-hr Use row 1 as table headers. Setting in inifile: tableheadrow=1
-hr- Disable the use of row 1 as table headers, to override inifile setting. Setting in inifile: tableheadrow=0
-th "tableheaders" Set headers for each column in the data. Setting in inifile: tableheader=Firstname,Lastname,Address,Postal code,City,Telephone,E-mail
A header can span 2 or more columns by specifying a vertical bar, and the no. of columns to span (using colspan)
  Example:
-th "Name|2,Address,Postal code,City,Telephone,E-mail" : gives 1 header 'Name' over the Firstname and Lastname columns, and the rest of the headers over their corresponding datacolumns.
-tf "tablefooters" Set footers for each column in the data. Setting in inifile: tablefooter=Firstname,Lastname,Address,Postal code,City,Telephone,E-mail
The same extra colspan parameter can be given to a footer.
  Example:
-tf "End of data|7" : A wide footer displaying "End of data" across 7 columns
-r count Set the repeatcount of rows after which the footer (if defined) and header are inserted into the table. Only applicable with the TableRows marker (see below). Default: 0. Setting in inifile: repeatcount=0
-s [row] Startrow setting, the row to start the export of data from. Default if not specified: 1, default if no row specified: 2. Setting in inifile: startrow=1
-m maxrow Maxrow setting, the maximum row that is to be output to file, not the number of rows! Setting in inifile: maxrow=
-p "Page header" Set the text for the page header using PageHeader marker (see below). Setting in inifile: header=
-pf "Page footer" Set the text for the page footer using PageFooter marker (see below). Setting in inifile: footer=
-sf ShowFormula produces the formula text with the formula results. Only available from the command-line.
-dt DisplayTags displays the available template tags. Only available from the command-line.
-q Quiet mode, disable error messageboxes. Setting in inifile: quiet=1
-q- Quiet mode disabled, re-enable any error messageboxes. Setting in inifile: quiet=0
 
Commandline parameters override any setting made in the inifile.

Inifile settings:

debug= Debug log mode, 0 = off, 1 = on
excelfile= The excel file to read, specify including path and extension.
sheet= The sheet number or name of the excel file to read. If specified, should be > 0 and < no. of sheets, or an existing sheetname.
templatefile= The html template to use. Should be prepared with markers where the data should go. (See below)
outputfile= The html output file(s) to produce, see -o command-line parameter for details.
tableheadrow= Enable or disable the use of the first datarow as columnheaders. 0 = off, 1 = on
repeatcount= The no. of rows after which a tablefooter (if defined) and a tableheader is inserted. Either -hr or tableheadrow=1 should be specified to enable the use of columnheaders.
tableheader= The headers to display above each column. Only applicable with TableRows marker (see below), see also -th command-line parameter.
tablefooter= The footers to display below each column. Only applicable with TableRows marker (see below), see also -tf command-line parameter.
startrow= The row to start reading data, see -s command-line parameter.
maxrow= The last row to export to html.
header= The text to use for the PageHeader marker (see below)
footer= The text to use for the PageFooter marker (see below)
quiet= Enable or disable Quiet mode, 0 = off, 1 = on, set to 1 to suppress any error messageboxes.

Markers:

Markers should be inserted into the html to place the data from Excel into the output. They are embeded as html comments, so if no data for a marker is available nothing is displayed.
These markers ARE case-sensitive!

Name Insert
PageHeader <!-- EXCEL2HTML - PAGE-HEADER -->
data: The text specified with -p or header=
PageFooter <!-- EXCEL2HTML - PAGE-FOOTER -->
data: The text specified with -pf or footer=
TableHeader <!-- EXCEL2HTML - TABLE-HEADER -->
data: The complete <tr><th>header</th>...</tr> sequence is generated based on row 1, or -th or tableheader= parameters
TableRows <!-- EXCEL2HTML - TABLE-ROWS -->
data: For use in Single File mode. All data is put in <tr><td>content</td>...</tr> tags, and ended with a NewLine. The majority of data will go here.
See Excel2Html-all-template.html
TableFooter <!-- EXCEL2HTML - TABLE-FOOTER -->
data: The complete <tr><th>footer</th>...</tr> sequence is generated based on -tf or tablefooter= parameters
TableColumn <!-- EXCEL2HTML - COLUMN <colnr> -->
data: Data from column colnr is put where this tag is. The <tr><td></td></tr> has to be provided in the template, for maximum flexibility.
See Excel2Html-1-template.html
  The <colnr> can be specified with or without spaces, so these are all valid:
  <!-- EXCEL2HTML - COLUMN1 -->
  <!-- EXCEL2HTML - COLUMN 2-->
  <!-- EXCEL2HTML - COLUMN3-->
  <!-- EXCEL2HTML - COLUMN 4 -->
LoopStart <!-- EXCEL2HTML - LOOP-START -->
control: The tag to start looping over all rows in the excel sheet, same as TableRows, but now only the tags between LoopStart and LoopStop will be replaced with the data. Formatting is fully up to the user.
  Tag is replaced by lower-case tag during processing
LoopStop <!-- EXCEL2HTML - LOOP-STOP -->
control: End of loop definition, executes the replacement of values for the area between LoopStart and LoopEnd. These tags are placed by their lower-case counterparts, to avoid endless looping.
  Multiple loops can be defined, but not nested loops!
  Headers can be included both inside and outside a loop.
  Tag is replaced by lower-case tag during processing