| Excel2Html | Version: 2.2.1.0 |
| Last update: v2.2.1.0, 2012-05-10 | 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.
v2.x: Requires Java 6 (JRE) to be installed, Excel is not 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:
| -? | 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 all data 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..4 = 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 --> | |
| 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 | ||
- Version
- Downloads 480
- File Size 15.63 MB
- File Count 1
- Create Date May 10, 2012
- Last update 2012-05-10 20:33:07
- Last Updated January 29, 2018