Tags:
automation1Add my vote for this tag export3Remove my vote on this tag import5Remove my vote on this tag spreadsheet3Add my vote for this tag create new tag
, view all tags

ExcelImportExportPlugin

Renders an attached Excel spreadsheet as TWiki table

Overview

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:

  • Import from Excel
    • Upload an Excel 2003 spreadsheet to TWiki and generate a table such that each row of the resultant table corresponds to a row in the spreadsheet.
    • Upload an Excel 2003 spreadsheet to TWiki and generate a topic for each row in the spreadsheet such that each column corresponds to a form field.
    • Attach an Excel 2003 spreadsheat to a TWiki topic and render its content as a TWiki table whenever the topic is viewed.
  • Export to Excel
    • Generate an Excel 2003 spreadsheet from a TWiki table, such that each row in the table corresponds to a row in the resultant spreadsheet.
    • Generate an Excel 2003 spreadsheet from a table returned as the result of a FormattedSearch, such that each row in the table corresponds to a row in the resultant spreadsheet.
    • Generate an Excel 2003 spreadsheet from a table as the result TWiki variable expansion on a topic, such that each row in the table corresponds to a row in the resultant spreadsheet.
    • Generate an Excel 2003 spreadsheet from all topics that have an indicated form. Each topic results in a row of the spreadsheet, such that each form field is mapped to a column in the spreadsheet.

The code of this plugin is based on the TWiki:Plugins.ExcelImportExportAddOn by TWiki:Main.ClausLanghans.

General comments

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.

Syntax Rules

  • Syntax: %EXCEL2TABLE{...}%
  • Synopsis: Renders the first worksheet of an Excel 2003 spreadsheet attached to the indicated topic as a table, formatted per the definition of the form given in the template parameter.
  • Supported parameters:
    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

  • Syntax: %UPLOADEXCEL2TABLE{...}%
  • Synopsis: An Excel 2003 spreadsheet can be uploaded and a TWiki table will be generated such that each row of the table corresponds to a row in the uploaded spreadsheet. The generated table replaces the first line solely having |....|. But for such a line, the table is not inserted anywhere.
  • Supported parameters:
    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.  

  • Syntax: %TABLE2EXCEL{...}%
  • Synopsis: Generates an Excel 2003 spreadsheet file from the first table found in this topic, such that each row in the table will result in a row of the generated spreadsheet.
  • Supported parameters:
    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.
    From To
    <br/> \r\n
    <p/> \r\n\r\n
    &quote; "
    &#39; '
    &#124; |
    off
    scale="..." The print scale factor of the resulting spreadsheet. 100

  • Syntax: http://twiki.org/cgi-bin/topics2excel
  • Synopsis: Generates an Excel 2003 spreadsheet file from the topics which have the designated form, such that each topic will result in a row of the generated spreadsheet and where the fields of the form indicate the columns of the spreadsheet.
  • Supported parameters:
    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.  

  • Syntax: http://twiki.org/cgi-bin/excel2topics
  • Synopsis: An Excel spreadsheet can be uploaded and a topic is created for each row of the spreadsheet, such that each column of the spreadsheet corresponds to a formfield in the generated topic, as defined in the designated form. Only those fields will be written, that are present in an already existing topic (which will be updated with data from the spreadsheet) or in the identified topic creation template.
  • Supported parameters:
    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.

Mapping between Excel Spreadsheet and TWiki Table

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

Plugin settings are stored as preferences variables. To reference a plugin setting write %<plugin>_<setting>%, i.e. %SMARTEDITPLUGIN_SHORTDESCRIPTION%

  • One line description, is shown in the TextFormattingRules topic:
    • Set SHORTDESCRIPTION = Renders an attached Excel spreadsheet as TWiki table

  • Debug plugin: (See output in data/debug.txt)
    • Set DEBUG = 0

Note: Additional settings are available, but not yet documented.

Note: The parameters for the tags and scripts can also be defined in settings.

Plugin Installation Instructions

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.

  • Download the archive from the Plugin Home (see below)
  • Unpack the archive in your twiki installation directory. Content:
    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  

  • Make sure that all files are readable by the web server user, that the scripts are executable, and that the scripts are given the proper access rights in your .htaccess file, if applicable.

Look at the example in Sandbox...

Plugin Info

Plugin Author: TWiki:Main.ThomasWeigert, based on code by TWiki:Main.ClausLanghans
Copyright: © 2001-2016 TWikiContributor
© 2006 Motorola TWiki:Main.ThomasWeigert
© 2006 TWiki:Main.ClausLanghans
License: GPL (GNU General Public License)
Plugin Version: 2016-06-22
Change History:  
2016-06-22: TWikibug:Item7703: Fix bug in manifest (Config.spec missing) -- TWiki:Main.PeterThoeny
2015-05-29: TWikibug:Item7604: Switch from GPL v2 to v3
2014-02-12: TWikibug:Item7371: Typo fix -- thanks TWiki:Main.AnthonyRizzo
2012-10-17: TWikibug:Item6993: Disable uploading when current site mode is read-only or slave.
2012-10-17: TWikibug:Item6994: Bin scripts are out of date
2012-10-17: TWikibug:Item6995: Add "sheetnum" parameter for %EXCEL2TABLE{...}%
2012-10-17: TWikibug:Item6996: Add "dynamic", "html2text" and "scale" parameters to %TABLE2EXCEL%
2012-10-17: TWikibug:Item6997: Exported excel file doesn't support utf-8 charset.
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:
NameVersionDescription
Spreadsheet::ParseExcel>=0Required. Available from CPAN.
Spreadsheet::WriteExcel>=0Required. Available from CPAN.
Date::Manip>=0Required. Available from CPAN.
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

Topic attachments
I Attachment History Action Size Date Who Comment
Unknown file formatmd5 ExcelImportExportPlugin.md5 r10 r9 r8 r7 r6 manage 0.2 K 2016-06-22 - 17:19 PeterThoeny  
Compressed Zip archivetgz ExcelImportExportPlugin.tgz r10 r9 r8 r7 r6 manage 25.1 K 2016-06-22 - 17:19 PeterThoeny  
Compressed Zip archivezip ExcelImportExportPlugin.zip r11 r10 r9 r8 r7 manage 34.1 K 2016-06-22 - 17:19 PeterThoeny  
Unknown file formatEXT ExcelImportExportPlugin_installer r5 r4 r3 r2 r1 manage 4.3 K 2016-06-22 - 17:19 PeterThoeny  
Edit | Attach | Watch | Print version | History: r21 < r20 < r19 < r18 < r17 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r21 - 2016-06-22 - PeterThoeny
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2016 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.