This plugin renders an attached Excel 2003 spreadsheet as a TWiki table, and vice versa. It also provides scripts to generate a set of topics from an Excel spreadsheet, and vice versa. In detail, the plugin supports the following use cases:
The code of this plugin is based on the TWiki:Plugins.ExcelImportExportAddOn by TWiki:Main.ClausLanghans
.
This plugin establishes a correspondence between a spreadsheet and a TWiki table, or a set of TWiki topics. The spreadsheet must have a header row which defines the schema of the database represented by the spreadsheet.
The columns of the spreadsheet and the columns of the TWiki table are correlated via a TWiki form and/or a more detailed mapping table, see #ColumnMapping.
%EXCEL2TABLE{...}%
template
parameter.
Parameter: | Description: | Default: |
---|---|---|
topic="..." | The topic at which the spreadsheet is attached | Current topic |
file="..." | Default parameter: The name of the attached spreadsheet file (without the extension) | Current topic |
template="..." | A form definition (see TWikiForms); it defines the columns of the table to be generated. Must match column headers of the attached spreadsheet. | |
sheetnum="..." | The sheet number to be retrieved. | 1 |
%UPLOADEXCEL2TABLE{...}%
Parameter: | Description: | Default: |
---|---|---|
topic="..." | Default Parameter: The topic where the resultant table will be inserted. The first table found in this topic will be replaced by the generated table. | Current topic |
template="..." | A form definition (see TWikiForms); it defines the columns of the table to be generated. Must match column headers of the attached spreadsheet. |
%TABLE2EXCEL{...}%
Parameter: | Description: | Default: | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
topic="..." | Default parameter: The topic at which the spreadsheet is attached | Current topic | ||||||||||||
file="..." | The name of the generated spreadsheet file (without the extension) | Current topic | ||||||||||||
template="..." | A form definition (see TWikiForms); it defines the columns of the table to be generated. Must match column headers of the attached spreadsheet. There is one special field type recognized by this plug-in - url , which is undefined in TWikiForms. A table column of this type is supposed to have either [[URL][label]] or URLs. And it's put as hyperlinks in the resulting spreadsheet. | |||||||||||||
map="..." | The topic which contains additional mapping information between the columns of the table and the columns in the generated Excel file. | |||||||||||||
dynamic="on" | Usually, the table to be converted into Excel needs to follow %TABLE2EXCEL{...}% . And only one line %SEARCH{...}% is expanded. But if you specify this parameter, 1) there is no need for %TABLE2EXCEL{...}% and the entire topic has its variables expanded before the table to Excel conversion takes place. | off | ||||||||||||
html2text="on" | The following replacements are caused.
| off | ||||||||||||
scale="..." | The print scale factor of the resulting spreadsheet. | 100 |
http://dayawane.ihep.ac.cn/twiki/bin/topics2excel
Parameter: | Description: | Default: |
---|---|---|
template="..." | Default parameter: A form definition (see TWikiForms); it defines the columns of the table to be generated. Must match column headers of the attached spreadsheet. | |
map="..." | The topic which contains additional mapping information between the columns of the table and the columns in the generated Excel file. |
http://dayawane.ihep.ac.cn/twiki/bin/excel2topics
Parameter: | Description: | Default: |
---|---|---|
topic="..." | The topic at which the spreadsheet is attached | Current topic |
file="..." | Default parameter: The name of the attached spreadsheet file (without the extension) | Current topic |
template="..." | A form definition (see TWikiForms); it defines the columns of the table to be generated. Must match column headers of the attached spreadsheet. | |
topiccolumn="..." | The column that defines the name of the generated topic. If not present, the name is constructed from ExcelRow and a sequence number. | TOPIC |
topictext="..." | This column defines the text of the topic. As a safety precaution, it will only generate topic text, if the existing text is not empty. | TEXT |
newtopictemplate="..." | The topic template defining a newly created topic derived from a spreadsheet row. |
Please see the example in IssueExcelExportImport for more detail.
In its simplest form, the correspondence between the Excel spreadsheet and the TWiki table is established through a TWikiForms template. This form corresponds to the header row in the spreadsheet: each field in the form is the same as a column field in the header row. All fields except date fields are assumed to be of type text.
Note that the order of the fields need not be the same, nor do all the column fields have to be present. Missing fields will be omitted in the import or export operations.
More details of the generated Excel spreadsheet can be defined in a mapping table, which is a table with the following layout:
Field | Meaning | Default |
---|---|---|
Name | Name of the form field | |
Short Name | Excel column header. The Short Name and Name need to be the same if you want to be able to import the exported files without changing the column headers | |
Width | Width of the Excel column | |
Orientation | Vertical orientation of the Excel column, either h (horizontal) or v (vertical) |
h |
Data Type | empty, text , or date |
text |
The mapping table, if present, overrides the form definition. If neither form nor mapping table are given, the column headings are inferred from the header line in table or spreadsheet. (A header line must be present, if a form or mapping table is not given.)
Plugin settings are stored as preferences variables. To reference a plugin setting write %<plugin>_<setting>%
, i.e. %SMARTEDITPLUGIN_SHORTDESCRIPTION%
data/debug.txt
) Note: Additional settings are available, but not yet documented.
Note: The parameters for the tags and scripts can also be defined in settings.
Note: You do not need to install anything on the browser to use this add-on. The following instructions are for the administrator who installs the add-on on the server where TWiki is running.
File: | Description: |
---|---|
lib/TWiki/Plugins/ExcelImportExportPlugin.pm | |
lib/TWiki/Plugins/ExcelImportExportPlugin/Import.pm | |
lib/TWiki/Plugins/ExcelImportExportPlugin/Export.pm | |
data/TWiki/ExcelImportExportPlugin.txt | |
bin/excel2topics | |
bin/topics2excel | |
bin/table2excel | |
bin/uploadexcel | |
data/Sandbox/ExcelExport1.txt | |
data/Sandbox/ExcelImport1.txt | |
data/Sandbox/IssueExcelExportImport.txt | |
data/Sandbox/IssueTrackingForm.txt | |
pub/Sandbox/IssueExcelExportImport/IssueExcelExportImport.xls |
.htaccess
file, if applicable.
Look at the example in Sandbox...
Plugin Author: | TWiki:Main.ThomasWeigert![]() ![]() |
||||||||||||
Copyright: | © 2001-2014 TWikiContributor © 2006 Motorola TWiki:Main.ThomasWeigert ![]() © 2006 TWiki:Main.ClausLanghans ![]() |
||||||||||||
License: | GPL (GNU General Public License![]() ![]() |
||||||||||||
Plugin Version: | 2014-02-12 | ||||||||||||
Change History: | |||||||||||||
2014-02-12: | TWikibug:Item7371![]() ![]() |
||||||||||||
2012-10-17: | TWikibug:Item6993![]() |
||||||||||||
2012-10-17: | TWikibug:Item6994![]() |
||||||||||||
2012-10-17: | TWikibug:Item6995![]() |
||||||||||||
2012-10-17: | TWikibug:Item6996![]() |
||||||||||||
2012-10-17: | TWikibug:Item6997![]() |
||||||||||||
July 2008: | 4.2.x compatibility, Update Topic data from changed Form definition. - TWiki:Main.SvenDowideit![]() |
||||||||||||
04 Nov 2006: | Added abilty to upload spreadsheet. Rewritten for consistency and made more resilient. Enhanced documentation. | ||||||||||||
13 Aug 2006: | Initial version | ||||||||||||
Dependencies: |
|
||||||||||||
Other Dependencies: | none | ||||||||||||
Perl Version: | 5.005 | ||||||||||||
TWiki:Plugins/Benchmark![]() |
GoodStyle nn%, FormattedSearch nn%, ExcelImportExportPlugin nn% | ||||||||||||
Plugin Home: | http://TWiki.org/cgi-bin/view/Plugins/ExcelImportExportPlugin![]() |
||||||||||||
Feedback: | http://TWiki.org/cgi-bin/view/Plugins/ExcelImportExportPluginDev![]() |
||||||||||||
Appraisal: | http://TWiki.org/cgi-bin/view/Plugins/ExcelImportExportPluginAppraisal![]() |
Related Topics: TWikiPlugins, DeveloperDocumentationCategory, AdminDocumentationCategory, TWikiPreferences, TWiki:Plugins.ExcelImportExportAddOn
|
Copyright © 1999-2025 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Daya Bay? Send feedback Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.ExcelImportExportPlugin. |