Excel Import Export Add-On

Description

This add-on extends the functionality of TWiki to ex- and import topics with forms to Excel. The export is currently limited to topics with a form, however the import can be done even to topics without any attached form. Best is to look at this example page: TWiki:Sandbox.IssueExcelExportImport

Export

To export topics a table needs to be defined which and how the topics are exported. A couple of additional settings need to be configured such as which topics are exported.

Importing

• The Excel file is attached to a topic
• Only the data on the first sheet is considered for importing
• The first row contains the column headers. They must exactly match with the names of the form fields. At the current stage this is not checked against the form definition.
• Two special columns exist:
  • TEXT contains the raw text of the topic, i.e. everything except the meta data
  • TOPIC contain the name of topic that will be imported.

Help Needed

I need some help to finish this add-on. I know how to write Perl code, but I am not very familar with the internals of TWiki. I would much appriciate feedback. Please review the two files topics2excel and excel2topics for these aspects:

• Security
  • Will topics2excel allow users to read a topic to which they should not have access? If yes, how can I avoid this?
  • Will excel2topics overwrite topics it should not have write access to? How to avoid?
• Functionality
  • The criteria to select the topics for export is limited. How can this improved? Ideally I would like to use the same syntax as the %SEARCH{}% TWikiVariables
  • Is this add-on useful to anyone?
  • Which features are missing?
    • Import: Allow different parent topics. Currently all topics have the same parent
    • Export: different date/time formats for different columns
    • Export: A configurable sort order (currently sorted by topic names)
• Compatibility
  • Is the code compatible with future or other current versions of TWiki? I developed against TWiki3 and did a quick test with TWiki4 using the VMWare image from TWiki:Codev.TWikiVMDebianStable.

Usage

• See the attached example in Sandbox.IssueExcelExportImport

Add-On 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 ZIP file from the Add-on Home (see below)
• Unzip ExcelImportExportAddOn.zip in your twiki installation directory. Content:

  File:	Description:
  bin/excel2topics	program to import topics
  bin/topics2excel	program to export topics
  data/Sandbox/IssueExcelExportImport.txt	working example
  pub/Sandbox/IssueExcelExportImport/IssueExcelExportImport.xls
  data/Sandbox/IssueTemplate.txt	Template topic for new topics
  data/Sandbox/IssueTracker.txt	List of all topics and to create a new topic
  data/Sandbox/IssueTracking.txt	Main page
  data/Sandbox/IssueTrackingForm.txt	Form for the example
  data/Sandbox/IssueUserHasNoClueHowToUseTheSystem1.txt	Example topic with form data
  data/Sandbox/IssueTheBarProblem2.txt	Example topic with form data
  data/Sandbox/IssueKeyboardChairError3.txt	Example topic with form data
  data/Sandbox/IssueUserCanNotFindTheOnButton4.txt	Example topic with form data
  data/TWiki/ExcelImportExportAddOn.txt	Add-on topic
  data/TWiki/ExcelImportExportAddOn.txt,v	Add-on topic repository
  bin/foobar	Add-on script

• Add the IssueTrackingForm to the forms for the Sandbox web in WebPreferences
• Test if the installation was successful:
• Go to IssueExcelExportImport and carry out the steps in this topic

Add-On Info

• Set SHORTDESCRIPTION = Export and import topics to/from an Excel sheet

Add-on Author:	TWiki:Main/ClausLanghans
Add-on Version:	13 Aug 2006 (v1.000)
Change History:
13 Aug 2006:	Initial version
CPAN Dependencies:	Spreadsheet::ParseExcel, Spreadsheet::WriteExcel
Other Dependencies:	none
Perl Version:	5.005
License:	GPL
Add-on Home:	http://TWiki.org/cgi-bin/view/Plugins/ExcelImportExportAddOn
Feedback:	http://TWiki.org/cgi-bin/view/Plugins/ExcelImportExportAddOnDev
Appraisal:	http://TWiki.org/cgi-bin/view/Plugins/ExcelImportExportAddOnAppraisal

Related Topic: TWikiAddOns

-- TWiki:Main/ClausLanghans - 13 Aug 2006