Notes Import Export Library
Have you ever wanted to import the newest
Excel file types directly into Lotus Notes? Look no further! Voith's
CODE Notes Import Export Library
(IEL) can do that! You can export too!
With easy user interface and intuitive field mapping, you can easily import and export the newest Microsoft Excel 2007 and 2010 files (with file type xlsx) in addition to the older 93-2003 file type xls.
Above you see how some spreadsheet columns have been mapped to their corresponding fields in the Document-form.
Why was IEL created?
Lotus Notes itself have never had direct support for import- or export to Excel files. However, this could be circumvented since earlier versions of Excel you could save the content as a Lotus 1-2-3 workbooK. Lotus Notes can import 1-2-3 files. Starting from Excel 2007 this ability disappeared, and Notes users wanting to import- or export to Excel was left in a void.
Programmers could of course address this by creating LotusScript-solutions controlling Excel via COM. and several good solutions is out there. Try to search for CreateObject Excel Application LotusScript, and you will find them. A slight disadvantage with the COM-approach is that you actually need Excel installed, and funny things could happen if you tried to launch multiple import- or export tasks at the same time. In other words, easy at first sight, but kind of hard to control in a production environment.
Another programmatic approach can be via Lotus Symphony APIs (or even via the original Open Office APIs), since these APIs do a pretty good job reading and writing Office formats.
IEL was created because I wanted something up front able to import and export Excel without having to resolve to programming all the time.
Where do you find IEL when installed?
After IEL is installed, you will find new import- and export options in your standard Notes File -> Import/Export dialog boxes.
Below you see the two new import options Excel 2007+ for the newer xlsx files and Excel 97-2003 for the older xls files;
The same two new options exists when you exportfrom Notes.
In other words, IEL is a standard import/export library to Lotus Notes.
Features:
With easy user interface and intuitive field mapping, you can easily import and export the newest Microsoft Excel 2007 and 2010 files (with file type xlsx) in addition to the older 93-2003 file type xls.
Above you see how some spreadsheet columns have been mapped to their corresponding fields in the Document-form.
Why was IEL created?
Lotus Notes itself have never had direct support for import- or export to Excel files. However, this could be circumvented since earlier versions of Excel you could save the content as a Lotus 1-2-3 workbooK. Lotus Notes can import 1-2-3 files. Starting from Excel 2007 this ability disappeared, and Notes users wanting to import- or export to Excel was left in a void.
Programmers could of course address this by creating LotusScript-solutions controlling Excel via COM. and several good solutions is out there. Try to search for CreateObject Excel Application LotusScript, and you will find them. A slight disadvantage with the COM-approach is that you actually need Excel installed, and funny things could happen if you tried to launch multiple import- or export tasks at the same time. In other words, easy at first sight, but kind of hard to control in a production environment.
Another programmatic approach can be via Lotus Symphony APIs (or even via the original Open Office APIs), since these APIs do a pretty good job reading and writing Office formats.
IEL was created because I wanted something up front able to import and export Excel without having to resolve to programming all the time.
Where do you find IEL when installed?
After IEL is installed, you will find new import- and export options in your standard Notes File -> Import/Export dialog boxes.
Below you see the two new import options Excel 2007+ for the newer xlsx files and Excel 97-2003 for the older xls files;
The same two new options exists when you exportfrom Notes.
In other words, IEL is a standard import/export library to Lotus Notes.
Features:
- No need have Excel installed at all.
That's right. IEL can both import and export Excel files without having Excel installed on the computer. If you do have Excel installed, IEL don't mind either.
- Easy, visual field mapping. Visually
easy to see what fields maps together. Automatic conversion of field types
if possible.
Simply by double clicking on a field, you enable it for mapping, as shown with the red field above. By double clicking on a target field you map the two fields as show below;
You may off course break any mapping by double clicking on either field in a existing mapping. You may also click on the toolbar button shown below;
- Change field names in target with ease.
Dont' like the target field name? Simply click once on the field name and rename it to whatever you would like.
- Add new target fields, not found
in Notes or Excel.
Right click anywhere in the target field list to get this context menu;
The topmost menu let you add new fields. A new field dialog box pops up;
You may specify a field type as well.
When the field has been added it turns up in the target field list, like this;
The new field can be mapped like any other field in the target field list.
- Any target list field can have a formula!
Use standard Notes @-formulas together with special @VC-formulas. The formula will be evaluated on the document, so you can reference other fields etc. The current value is always treated as text and can be referenced as @VCThisValue. By the way, other special @VC-formulas exist too. Wonder what @VCLinkToDocument do? :-)
Right-click on the target field you want to edit the formula for;
A super-simple formula window pops up (yes, I will improve the editor in the future, but right now I figure that its important to just have an editor ...);
Enter your formula.
After a formula is edited, you can easily see which fields have a formula or not;
- Save you settings and reload them later.
IEL has the ability to save your settings to file, and reload them back at any time. Use the menu File -> Save Settings -> Save to file ...
The settings file contain all your current settings, including the file name you work with. This may be overridden when you reload the settings file.
- Full Unicode support. Filenames,
field names, any content. Full conversion between Lotus' internal LMBCS
and Unicode.
You speak Greek? No problem, file names and content shouldn't be any problem at all. Field names in Notes adhere to the file name rules in Notes.
- Multithreaded design, meaning that
you quickly can return to Notes and continue to work with other stuff,
while IEL does its work. Any task can of course be aborted at any time.
You can even have multiple import- or export tasks running at the same time.
- Preview before import- or export.
Sometimes it can be hard to know what will be imported- or exported.
Why not preview the result before commencing a long job?
General import features:
- Choose which form you want to import
to. IEL tries to choose the default form for you;
When you choose a form, the fields of the form are instantly displayed as target fields for mapping. Note that some fields, such as computed and computed for display type fields are omitted from the list since you can't write to them anyway.
- Compute with form support!
You may choose to perform "Compute with form" on each imported row, meaning that any computed field in the form is evaluated for each document.
Note that this slows down the import somewhat.
Excel export features:
You have all the field mapping capabilities, combined with the ability to add new fields, rename target fields, and even use formulas.
Below you see the export dialog box of IEL. Pretty similar to the import dialog box regarding mapping, but now the Excel fields are the target fields while Lotus Notes is the source fields.
- Export All documents, or Selected
documents only.
You can choose to export selected documents or all documents from the current view.
- Export columns from the selected
view -or- fields from the first selected document.
This gives you the ability to dump the view as you see it in Notes, -or- you can dump fields at will.
The absolutely fastest way to export data, is to select All documents and columns from the selected view. IEL will then read view summary info as fast as possible.
- Quickly control the new column names
in Excel.
The default setting is Equal fields as Lotus Notes, which means that the Excel column headers will be the same as the field names choosen for Lotus Notes. The idea here is to quickly let you define the column headers in Excel without needing to add your own fields.
- Notes view format can be exported.
Please note that IEL will try to export your format. Sometimes it looks good, and sometimes not. The more special columns you have (display as icons, display colors etc), the more twisted the Excel-result may look.
Note that you can only select to export view format when you have chosen All documents in the view and Columns from the selected view. The options are grayed out if you have any other combination.
- Group the rows in Excel.
A cool feature in Excel is that you can group rows and columns. By chosing this option in IEL, it will automatically group the Excel rows for you. Look at the Notes view below;
When exported to Excel, it looks like this;
Note how the data rows are grouped!
You may only group rows if you export the All documents in the view, choose Columns from the selected view and Export Notes view format to Excel, like this;
The reason for this is of course that IEL needs to see the whole view structure in order to be able to group your rows.
No active manual found