 |
Office Inside Excel Import and Export |
The oiExcelImpEx class makes importing and exporting data to and from Excel a breeze. If you are at all familiar with CapeSoft xFiles, you will feel right at home, as we have kept the interface consistent with xFiles. If you are not, never fear, it really couldn't be any easier.
Let's dive right in with an example of saving a File (a Table) to an Excel workbook. This file can use any driver, and have any structure. Each field will be output to a seperate column (you can map fields to specific columns and customise the output as needed, but for the moment we'll stick with the most basic scenario):
ExcelImpEx
oiExcelImpEx
! Instance of the oiExcelImpEx
class
code
ExcelImpEx.Save(MyTable, 'MyTable')
The above line of code will create an Excel document called 'MyTable' (the correct file extension will automatically be appended, either .xls or .xlsx, depending on the version of Excel being used).
See the HowTos and Examples section for a wide variety of different usage scenarios and code examples.
The Two Basic Approaches to Exporting and Importing (recommended reading)
The oiExcelImpEx class provides two approaches to importing and exporting. The first approach imports or exports fields and columns in the order that they occur in, and allows specified fields or columns to be ignored by calling the IgnoreField method.
The second approach is using column and field mapping to import or export any fields and columns in any order. This also allows fields to be exported to multiple columns, and column values to be imported into multiple fields. This approach also allows the user to be given control over the field to column mapping. This is done using the CreateMap, GetMap, SetMap, AddMap and ClearMap methods.
This is perhaps best illustrated by examples of each approach. Let's start with the default of saving fields in the order in which they occur:
ImpEx.numFields = 7
ImpEx.IgnoreField(2)
ImpEx.IgnoreField(4)
ImpEx.IgnoreField(6)
if not ImpEx.Save(BrowseCustomersQ, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
In the above example the fields in the queue are exported in the order in which they occur. The IgnoreField method is used to skip fields that are not required in the export. In this case the Queue is populating a listingbox and contains fields for the styles, which are skipping in the export. The above example also uses the .numFields property to limit the number of fields exported, so all fields after the seventh are ignored.
The second approach uses field to column mapping to determine which fields and columns are mapped to one another. This approach provides the following advantages:
- Any column can be mapped to any field in any order
- Columns and fields can be mapped multiple times (a single field can be saved to multiple columns, a single column can be loaded into multiple fields)
- As few or as many columns and fields can be mapped as needed
- Column and field mapping can be done a runtime
- Mapping uses a queue, so the end user can be presented with an interface to control field to column mapping
colMap queue(oiColumnMap)
end
code
ImpEx.CreateMap() ! Create the default map
ImpEx.GetMap(colMap) ! Get the default map
! Modify the map
Get(colMap, 1)
Clear(colMap)
colMap.fieldPos = 2
colMap.col = 1
Put(colMap)
ImpEx.SetMap(colMap)
if not ImpEx.Save(BrowseCustomersQ, excelFileName)
Message('Cannot Save the data. ' & Clip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
Using the oiExcelImpEx Class (recommended reading)
The oiExcelImp class makes importing and exporting data between Clarion and Excels simple. The core methods provided are the Load method (to import data from Excel into your application) and the Save method (to export data from your application to Excel). These methods support Files, Queus, Groups and Views (Views are read only, and hence are only supported by the Save method).
Save a File to an Excel Workbook.
Save all fields in the File to the spreadsheet, in the order that they occur in the File. All records are saved.
if not ImpEx.Save(Customers, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
endLoad from an Excel Workbook into a Queue
This example loads from an Excel spreadsheet directly into the queue. Each column is placed into the queue field in the order in which they occur.
ImpEx.freeBeforeLoad = True ! Clears the structure of all data/records before loading
if not ImpEx.Load(ExpensesQ, LongPath() & '\expenses.xls')
Message('Cannot import the data, loading failed. ' & CLip(ImpEx.errorMessage), 'Import Error', Icon:Exclamation)
endSee the HowTos and Examples section for a wide variety of different usage scenarios and code examples.
Import and Export Templates
Coming soon!
Class Methods
This section lists all the methods in the oiExcelImp class, along with their use, parameters, return values, expected use, and code samples.
| oiExcelImpEx Method Reference | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
General Use Methods |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Frequently used methods
for importing and exporting, as well as controlling and modifying the import
and export process. These methods are marked in the docs with the
icon throughout the documentation for quick reference.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
 Callback MethodsThe callback methods are provided to allow the default behaviour to be overridden. They provide a convenient location for adding addition code to the various stages of the process. These methods marked with the
icon througout documentation for easy reference. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
All Methods | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| All methods of the
class, including those used primarily internally and those methods that are
not frequently used. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Constructor. Calls the Init method to initialise the object. Should not be called directly.
Parameters
None
Return Value
None
Notes
This method should not be called directly.
See Also
Destruct Procedure ()
Destructor, calls the Kill method to perform cleanup. This is called automatically when the object goes out of scope, and should not be called directly. The Kill method can be used to clean up the object before it goes out of scope (generally this is not needed, as the standard Load and Save methods handle this).
Parameters
None
Return Value
None
See Also
Init Procedure (), virtual
Initialises the object, allocates memory, New's the internal references. This method does not generally need to called manually, as it is handled by the Load and Save methods.
Parameters
None
Return Value
None
See Also
InitExcel Procedure (), long, virtual
Initialises the COM interface and creates an instance of Excel for the object to us. This is called automatically by the Load and Save methods if Excel has not already been intialised. This method does not generally need to be called directly.
Parameters
None
Return Value
None
See Also
Disposes allocated memory and releases the COM interface and instance of Excel created. Called by the Load and Save methods on completion. This method does not generally need to be called directly.
Parameters
None
Return Value
None
See Also
Open Procedure (string p_FileName), long, virtual
Initialise Excel and loads the Workbook. Contains generic code regardless of the structure being imported into.
Parameters
p_FileName
A string that contains the file name. If this does not
contain a path, then the current directory is assumed to be the path.
If the passed string is empty (blank), then the user is prompted for a file to
open.
Return Value
Returns True (1) if successful and False (0) if it fails.
See Also
NewWorkbook Procedure (string p_FileName), long, virtual
Create a new workbook. Checks for the existance of the passed file name, and if the file exist either removed it, or opens it to append to append to it (depending on the where the .appendToFile property is set to True or not). If Excel! has not been intialised, then this method initialises it.
Parameters
p_FileName
A string that contains the file name. If this does not contain a path, then the current directory is assumed to be the path.
Return Value
Returns True (1) if successful and False (0) if it fails.
See Also
SaveFile Procedure (), long, proc, virtual
Save the workbook as the current file name. Calls the parent SaveAs method to save the Excel document. If the self.excelFile property is blank, the user is prompted for a file name. This is called by the Save method, and is not typically called directly.
Parameters
p_FileName
A string that contains the file name. If this does not
contain a path, then the current directory is assumed to be the path.
If the passed string is empty (blank), then the user is prompted for a file to
open.
Return Value
Returns True (1) if successful and False (0) if it fails.
See Also
OpenFile Procedure (), long, virtual
Opens the File or View associated with the object. Returns False if no File or View is associated, or if opening the file fails. Returns True if the file is already open, or if it was successfully opened.
For files, if it is opened, then the .filesOpen[1] entry is set to 1.
For Views, all Files used in the view are opened if they are not already open. For each file opened the .filesOpened[x] entry is set to 1 to indicate that the file has been opened. In addition the .ViewOpened property is set to 1, and the .bindPushed property is Set to 1, to indicate that PopBind needs to be called in CloseFile (this is all handled automatically).
Parameters
None
Return Value
Returns False (0) if no File or View is associated, or if
opening the file fails.
Returns True (1) if the file is already open, or if it was successfully opened.
See Also
CloseFile Procedure (), virtual
Close the file/view if it was opened. For views all files associated with the View that were opened are closed.
Parameters
p_FileName
A string that contains the file name. If this does not
contain a path, then the current directory is assumed to be the path.
If the passed string is empty (blank), then the user is prompted for a file to
open.
Return Value
Returns True (1) if successful and False (0) if it fails.
See Also
With Procedure (*File f), virtual
With Procedure (*Queue q), virtual
With Procedure (*Group g), virtual
With Procedure (*View v), virtual
Selects the current data structure to use. This method is called by the Load and Save methods, and does not generally need to be calle directly.
Parameters
*File f or *Queue q or *Group g or *View v
The data structure to associated with the object. This is the data structure that will be exported from, or import into.
Return Value
None
See Also
Load Procedure (*File p_File, string p_FileName),
long, proc, virtual
Load Procedure (*Group p_Group, string p_FileName), long, proc, virtual
Load Procedure (*Queue p_Queue, string p_FileName), long, proc ,virtual
Load Procedure (string p_FileName), long, proc ,virtual
Load (import) from a Excel document into the selected data structure.
Parameters
p_File or p_Group or p_Queue
The data structure to load into. If this is not provided (if only a file name is passed), then the With method must have already been called to associate a data structure with the object.
p_FileName
The name of the Excel file to import from. If the file name does not contain the path to the file, then the path is assumed to be the current application path.
Return Value
Returns True (1) if successful and False (0) if it fails.
Examples
This example loads from an Excel spreadsheet directly into the queue. Each column is placed into the queue field in the order in which they occur. See the HowTos and Examples examples section for more.
ImpEx.freeBeforeLoad = True ! Clears the structure of all data/records before loading
if not ImpEx.Load(ExpensesQ, LongPath() & '\expenses.xls')
Message('Cannot import the data, loading failed. ' & CLip(ImpEx.errorMessage), 'Import Error', Icon:Exclamation)
end
See Also
SetupLoad Procedure (), virtual
Setup the property defaults for importing (loading) data. Called by the Load method. This method is not typically called directly.
Parameters
None
Return Value
None
See Also
Save Procedure (*Group p_Group, string
p_FileName), long, proc, virtual
Save Procedure (*File p_File, string p_FileName), long, proc, virtual
Save Procedure (*Queue p_Queue, string p_FileName),long, proc ,virtual
Save Procedure (*View p_View, string p_FileName), long, proc ,virtual
Save Procedure (*string p_FileName), long, virtual
Save (export) from the specified structure to an Excel workbook.
Parameters
p_File or p_Group or p_Queue
The data structure to save from. If this is not provided (if only a file name is passed), then the With method must have already been called to associate a data structure with the object
p_FileName
The name of the Excel file to import from. If the file name does not contain the path to the file, then the path is assumed to be the current application path.
Return Value
Returns True (1) if successful and False (0) if it fails.
Examples
See the HowTos and Examples section for further information and example.
Example1 - Save a File
Save all fields in the File to the spreadsheet, in the order that they occur in the File. All records are saved.
if not ImpEx.Save(Customers, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
endExample 2 - Save a Queue
Limit the number of fields saved (7 fields in this case), and skip every second field (the Style or Icon field for example) by calling the IgnoreField method.
ImpEx.numFields = 7
ImpEx.IgnoreField(2)
ImpEx.IgnoreField(4)
ImpEx.IgnoreField(6)
if not ImpEx.Save(BrowseCustomersQ, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
endExample 3 - Save a View
Save all fields in the view to the Excel workbook in the order that they occur.
if not ImpEx.Save(BRW1::View:Browse, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
See Also
SetupSave Procedure (), long, virtual
Setup the property defaults for saving (exporting) data. Called by the Save method. This method is not typically called directly.
Parameters
None
Return Value
None
See Also
SaveData Procedure (), long, virtual
Generic Save method, handles the saving of data from the the data structure
selected to the cellsQ, which is then Written to the Excel document. Writes the
data in the structure to the current Workbook. A workbook must be available, and
the data structure must be open (for Files etc.).
This method can also be called to recursively saves groups with Files or Queues,
or queues within queues to the document simply by setting the startRow and
startCol before calling it.
This method is not typically called directly. Call the Load() or Save() methods to load or save data, and use the provided callbacks (TakeInit, TakeSetup, TakeComplete, ValidateRecord, InsertRecord and AssignField) to modify the behaviour.
Parameters
None
Return Value
Returns True (1) if successful, and False (0) for failure.
See Also
Load, Save, TakeInit, TakeSetup, TakeComplete, ValidateRecord, InsertRecord and AssignField
IgnoreField Procedure (long fieldNum), virtual
Add the field to the list of ignored fields. Fields that are ignored will not be included in the export, and will be skipped when calling the Save method. The field is skipped as if it did not exist (no blank column is created in the Excel document).
Note that the Ignored list is not used when using field to column mapping. See the The Two Basic Approachs to Importing and Exporting section for more information.
Parameters
fieldNum
The number (position) of the field in the data structure.
Return Value
Returns True (1) if successful, and False (0) for failure.
Examples
ImpEx.numFields = 7 ! ignore all fields after field 7
ImpEx.IgnoreField(2) ! ignore (skip) field 2
ImpEx.IgnoreField(4) ! ignore field 3
ImpEx.IgnoreField(6) ! ignored field 6
if not ImpEx.Save(BrowseCustomersQ, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
See Also
IsIgnored Procedure (long
pos), long, virtual
Checks whether a column is in the ignored list. Returns True (1) is the column is ignored, False (0) if it is not.
Parameters
pos
The number (position) of the column to check.
Return Value
Returns True (1) is the column is ignored, False (0) if it is not.
Examples
if ImpEx.IsIgnored(7) ! Check whether column 7 is ignored
ImpEx.IgnoreField(8) ! Also ignored column 8
end
See Also
Load, Save, ClearIgnored, IgnoreField
ClearIgnored Procedure (), virtual
Clears the list of ignored fields.
Note that the Ignored list is not used when using field to column mapping. See the The Two Basic Approachs to Importing and Exporting section for more information.
Parameters
None
Return Value
None
Examples
ImpEx.ClearIgnored() See Also
Close Procedure (), virtual
Closes all structures and documents that were opened when by a Load or Save. This method is called by the Load and Save methods and does not generally need to be called directly.
Parameters
None
Return Value
None
Examples
ImpEx.Close() ! Called automatically by the Load and Save methodsSee Also
CloseAndKill Procedure (), virtual
Closes all opened structures (calls the Close method), and if closes and cleans up any instance of Excel that was created. This method is typically called by the Load and Save methods and does not need to be called directly.
Parameters
None
Return Value
None
Examples
ImpEx.CloseAndKill() ! Called automatically by Load and Save See Also
AddMap Procedure (long fieldPos, long colPos), long, proc, virtual
Maps a field to a column for exporting or importing. When column mapping is used the colMap property is populated with a list of fields to import or export and the columns that they are associated with, allowing columns and fields to be imported or exported in any order and from and to any position.
The CreateMap method can be called to create a default mapping from the fields to the columns in the order that the occur. GetMap can then be called to retrieve the queue for customisation, and SetMap allows the modified queue to be used.
When saving (exporting) data, a single field value can be written to multiple columns by adding a mapping from that field to multiple columns. For example:
ExcelImpEx.AddMap(1, 1) ! Field 1 to Column A
ExcelImpEx.AddMap(1, 5) ! Field 1 to Column E
ExcelImpEx.Save(Customers, 'Customers')Similarly when loading data from Excel, values can be saved into multiple fields in example the same way:
ExcelImpEx.AddMap(1, 1) ! Import from Column A to field 1
ExcelImpEx.AddMap(2, 1) ! Import from Column A to field 2
ExcelImpEx.AddMap(5, 1) ! Import from Column A to field 5
ExcelImpEx.Save(Customers, 'Customers')In the above example the same value from Column A would be written to the field at position 1, 2 and 5 in the data structure.
Parameters
fieldPos
The number (position) of the field in the structure to map to a particular column in Excel.
colPos
The number of the colum to map from or to. This is the numeric position (ordinal) of the column, not the Excel name.
Return Value
None
Example
ExcelImpEx.AddMap(1, 1) ! Field 1 to Column A
ExcelImpEx.AddMap(3, 2) ! Field 3 to Column B
ExcelImpEx.AddMap(4, 3) ! Field 4 to Column C
ExcelImpEx.AddMap(5, 4) ! Field 5 to Column D
ExcelImpEx.AddMap(1, 5) ! Field 1 to Column E
! fields can be written to as many columns as required, and
! columns can be imported to as many fields as required).
ExcelImpEx.Save(Customers, 'Customers')See Also
SetMap, ClearMap, GetMap, FieldName, CountFields, Mapping Fields To Columns
SetMap Procedure (*oiColumnMap qMap), long, proc, virtual
Sets the colum/field mapping from a queue. Any existing mapping is replaced by the mapping specified by the passed queue. This functionality is most often used to provide a user configurable column to field mapping. The CreateMap method is called to create the default mapping, which can then be customise (to remove fields or columns not required). The GetMap method can be called to populate a queue with the current mapping. The queue can be modified and displayed, and SetMap can then be called with the modified queue.
qMap
A queue of the type oiColumnMap, which contains the fields to column mapping to use for import, or export.
Return Value
None
Types
The oiColumnMap queue type is used to map fields to columns, and contains four fields. This can be displayed in the user to allow them to adjust the field to column mapping by setting the fieldPos and col fields of the queue to map specific columns to fields. The queue allows "duplicate" entries, in that a single column can be mapped to as many fields as required and vice versa.
oiColumnMap queue, type
fieldPos long ! The field to map
col long ! The number of the column to map to
fieldName string(256) ! Friendly name, purely for display purposes
colName string(256) ! Column "name", for display purposes
end
Examples
colMap queue(oiColumnMap) end codeExcelImpEx.CreateMap() ! Create the default mapping ExcelImpEx.GetMap(colMap) ! Get the queue of fields to columns ! The queue can be displayed to the user in a listbox, modified, ! Added to, filtered and otherwise manipulated Get(colMap, 1) ! Get the first entry if not ErrorCode() Delete(colMap) ! Remove it (this column/field will be ignored) end ExcelImpEx.SetMap(colMap) ! Update the mapping with our modified queue ! fields can be written to as many columns as required, and ! columns can be imported to as many fields as required). ExcelImpEx.Save(Customers, 'Customers')
See Also
AddMap, ClearMap, GetMap, FieldName, CountFields
GetMap Procedure (*oiColumnMap qMap), long, proc, virtual
Clears the passed queue and populates it with the current field to column mapping. Note that by default no field to column mapping exists, and the fields and columns are read in order. Call the CreateMap method to create the initial column to field mapping for all fields, or call AddMap to manually add entries to the map.
CreateMap Procedure (), long, proc, virtual
ClearMap Procedure (), virtual
FieldName Procedure (long fieldPos), string, virtual
CountFields Procedure(), long, virtual
SetHeader Procedure (string headerText, long colPos), virtual
RunFile Procedure
(string fileName), long, proc, virtual
FreeData Procedure (), virtual
FreeFileData Procedure (), virtual
FreeQueueData Procedure (), virtual
FreeGroupData Procedure (), virtual
SetProgressControl Procedure (long progressControl), virtual
ProgressSetup Procedure (long rangeLow, long rangeHigh), virtual
ProgressSet Procedure (long progress), virtual
ProgressIncrement Procedure (), virtual
Callback Methods
The callback methods provide by the oiExcelmpEx class allow you to customise and override the behaviour of the object, including formatting the Workbook being created, modifying or filtering fields imported or exported and changing the object properties.
If the provided procedure templates are used to add an oiExcelImpEx object to the procedure, then these callback methods will be populated by the template to allow embed code to be added to them.
CreateFooter Procedure (),
virtual
Called when the export is complete to allow a footer to be added to the document (or otherwise customise the document). This method should be overridden to add any additional data to the exported document. The oiExcelImpEx class inherits from the oiExcel class, and allows all functionality exposed by the parent class to be leveraged to manipulate the Excel document.
Parameters
None
Return Value
None
Examples
ExcelImpEx.CreateFooter Procedure()
code
! Code to create a footer for the document is added here
self.WriteToCell('Date: ' & Format(Today(), @D6), 'A' & self.curRow + 1)
self.WriteToCell('Time: ' & Format(Clock(), @T4), 'B' & self.curRow + 1)
self.WriteToCell('Report Title', 'C' & self.curRow + 1)
See Also
CreateHeader Procedure
(), virtual
This is a callback method that allows you to modify the document being created. Called when the export begins, after the document has been created, but before any data is added to allow a header to be added to the document (or otherwise customise the document). This method should be overridden to add any additional data to the exported document. The oiExcelImpEx class inherits from the oiExcel class, and allows all functionality exposed by the parent class to be leveraged to manipulate the Excel document.
If data is added to the Excel workbook the curRow property of the class must be adjusted so that the imported data does not overwrite any data added. Setting curRow causes imported data to be added to the workbook starting with the row that curRow specifies.
Parameters
None
Return Value
None
Examples
ExcelImpEx.CreateHeader Procedure()
code
! Add code here to customise the header
self.WriteToCell('Exported Document ' & Format(Today(), @D6), 'A1')
self.SetCellFontName('Tahoma', 'A1')
self.SetCellFontSize(24, 'A1')
! Increment the curRow property to the new start position below the header
self.curRow = 2
See Also
TakeInit Procedure (), virtual
Called by the Load and Save methods when the object is initialised. This method is called directly after the object has initialised, and the Workbook has been opened (when loading). It can be used to perform any actions required before the object calls SetupLoad or SetupSave to populate the default setting.
Parameters
None
Return Value
None
Examples
ExcelImpEx.TakeInit Procedure()
code
! The object has initialised and the import or export is about to take place
self.With(MyFile) ! Override the data structure associated with the object
See Also
Load, Save, TakeSetup, TakeComplete, ValidateRecord, InsertRecord, AssignField
TakeSetup Procedure (), virtual
Called directly after the object has called SetupLoad or SetupSave, but before the Progress control and variables are setup. At this point the object has initialised, the default values have been populated and the data structure has been cleared by calling FreeData if the freeBeforeLoad property is set to True (1).
Parameters
None
Return Value
None
See Also
Load, Save, TakeInit, TakeComplete, ValidateRecord, InsertRecord, AssignField
TakeComplete Procedure (), virtual
Called when the import or export process has completed.
Parameters
None
Return Value
None
Examples
ExcelImpEx.TakeComplete Procedure()
code
! Loading or saving is complete
if self.action = oix:save
Message('Saving the data to Excel completed successfully', 'Save to Excel Complete', Icon:Exclamation)
else
Message('Loading the data from Excel completed successfully', 'Load from Excel Complete', Icon:Exclamation)
end
See Also
Load, Save, TakeSetup, TakeInit, ValidateRecord, InsertRecord, AssignField
ValidateRecord Procedure
(), long, virtual
Called when the import or export process has completed.
Parameters
None
Return Value
None
Examples
! Local Data - overide procedures ImpEx Class(oiExcelImpEx) TakeRecord Procedure (*excelImportQType cellsQ), long, derived InsertRecord Procedure (), long, derived ValidateRecord Procedure (), long, derived End ! Local Procedures - define overridden procedures ExcelImpEx.ValidateRecord Procedure() codeself.cellsQ.col = 3 ! Find the value retrieved from the third column Get(self.cellsQ, self.cellsQ.col) if ErrorCode() ! No cell from col 3, take some action: return level:notify ! Stop importing for example else if self.cellsQ.cell = 'some value' ! Check the value and filter the record return record:filtered else return record:OK ! Continue importing this record end end
See Also
Load, Save, TakeSetup, TakeInit, ValidateRecord, InsertRecord, AssignField
TakeRecord Procedure (*excelImportQType cellsQ), long, proc, virtual
Performs the actual assignment of the data between the Excel values and the Clarion data structure. The passed queue contains the values of each fields or cell, as strings. The passed queue is typically the .cellsQ property of the class, however it is passed as parameter rather than references directly to allow this to be overridden easily.
When saving, the data is the queue is written to each cell in the current Excel workbook row (each queue entry contains the value of a cell, and the queue represents a row).
When loading, the queue contains the values of each of the cells for a row in the Excel workbook, and they are written to each of the fields in the table. The InsertRecord method will be called to write the record to the File, Queue or View.
Parameters
cellsQ
A queue of the type excelImportQType, which contains the values of the the of cells to process for the current row. By default TakeRecord is passed the .cellsQ property, which contains these values. The excelImportQType contains a .cell field that is a string containing the value for that cell.
excelImportQType
queue, type
cell sstring(_oit:LargeStringSize)
! 32KB string
end
Return Value
Returns level:benign (0) if successful. If the call fails the error code of the failure is returned, or -1 if the method is called without associating a valid data type with the object.
Examples
! For loading, populate the autonumber ID field
ImpEx.TakeRecord Procedure (*excelImportQType cellsQ)
code
if self.dataType = oi:file and self.action <> oix:Save
Access:Customers.PrimeRecord(true) ! Prime the record, do not clear fields
end
return parent.TakeRecord(cellsQ)
See Also
Load, Save, TakeSetup, TakeInit, ValidateRecord, InsertRecord, AssignField
InsertRecord Procedure (), long, virtual
The InsertRecord method performs the actual record insertion for Files, Queues and Views. By default it uses the Clarion Add function, however this can be overridden to add the record using the FileManager method (this is demonstrated in the shipped examples, and in the code example below).
Parameters
None
Return Value
None
Examples
ImpEx.InsertRecord Procedure()
code
if self.dataType = oi:file and self.action <> oix:Save
! For loading into the file, populate the autonumber ID field
! See the TakeRecord code example above.
return Access:Customers.Insert()
else
! Just use the default behaviour
parent.InsertRecord()
end
See Also
Load, Save, TakeSetup, TakeInit, ValidateRecord, TakeRecord, AssignField
AssignField Procedure
(), virtual
Handles reading of data from Excel into the cellsQ (loading), or from the current field (saving). Allows the value for each cell to be modified by overriding this method. For each field/column the AssignField method is called to actually assign the value between the Excel cell value (stored in the current .cellsQ.cell queue record property) and the Clarion field (store in the .currentField property).
Once the field value has been assigned, it is added to the .cellsQ, which contains a queue of all cell/field values.
Parameters
None
Return Value
None
Examples
oiExcelImpEx.AssignField Procedure()
code
if self.action = oix:Save ! Saving
if self.currentFieldNumber = 10 ! Field at position 10
self.cellsQ.cell = Upper(self.currentField)
else
self.cellsQ.cell = self.currentField
end
else
self.cellsQ.cell = self.ReadCell(self.ColumnName(self.curCol) & self.curRow)
end
Add(self.cellsQ) ! No need to call the parent if we do the Add
See Also
Load, Save, TakeSetup, TakeInit, ValidateRecord, InsertRecord
Internal Methods
Internal methods are those used internally by the object, and not typically called directly in general use. They are listed here for completeness, and for advanced use in overriding the default functionality.
_ClearReferences Procedure (), virtual
Clears the internal references when the object is killed or destructs. The internal references points to the data stuctures associated with the object. Does not affect the data pointed to, only the references themselves. This is not expected to be used in general use. Use the Load and Save methods to load or save data, and the With method to switch the object to a different data structure manually.
Parameters
None
Return Value
None
_ClearAutos Procedure (), virtual
Clears all automatically calculated values, such as the number of rows and columns being processed, the starting row and field etc. Only clears automatically populated settings. This methods is called once Load or Save is complete and clears settings that the method will have modified or populated. When these settings are specified manually, they must be done before each call to Load or Save (or after SetupLoad or SetupSave have been called in the TakeSetup callback method) - they are NOT persistent between calls.
Properties cleared are: .startRow,
.startCol,
.numRows,
.numCols,
.startField,
.numFields
Parameters
None
Return Value
None
_GetRecord Procedure (), long, virtual
Retrieves the the record for the current data type. For Files and Queues this is the record, for Groups it is the group itself, and for Views this method fails and returns -1 (_GetViewRecord should be called for Views). This method assigns the record for the current structure to the .rec property, allowing for generic access to the record, rather than having to code for each specific structure.
Parameters
None
Return Value
True (1) for success, False (0) for failure, and -1 if the current structure is a view.
_GetViewRecord Procedure (long fieldPos), long, virtual
For Views the File record is used in order to retrieve field values. _GetViewRecord assigns file record for the current field.
Parameters
None
Return Value
Returns True (1) for success and False (0) for failure.
_ClearRecord Procedure (), virtual
Clears the record buffer for the current File or Queue, or in the case of Groups, the group itself is cleared. Does not support views (if the current structure is a View, then calling _ClearRecord will have no effect).
Parameters
None
Return Value
None
Debugging and Error Handling
DbgMsg Procedure (string pDbgMessage)
Sends a string to the Windows Debug output. This output can be trapped by using the free DebugView tool (See http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx for more information and to download DebugView). If the .debug property is set to False (0), then calls to this method are ignored. The ErrorTrap method calls this method to output error and debug information.
Parameters
pDbgMessage
A string that is output to the Windows debug output.
Return Value
None
ErrorTrap Procedure (string errorMessage, string functionName)
The ErrorTrap method is called when an error occurs in another method. Information about the error is provided, and optionally sent to the the system debug output (if the .debug property is set to True (1)). This output can be trapped by using the free DebugView tool (See http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx for more information and to download DebugView).
The ErrorTrap method provides a central location for error trapping and handling. The method name and error message are stored in the .errorMessage property of the class by the ErrorTrap method, and the error code is stored in the .lastError property.
Parameters
errorMessage
The error message. Contains a description of the error that occured.
functionName
A string that contains the name of the method that the error occured in.
Return Value
None
Class Properties
This section lists the properties of the class and their usage.
Private
- Treat these properties as private (or at least Read-Only) unless you have a
good reason to change them. Typically the provided methods should be used to
access these properties.
Read-Only
- treat these properties as read-only, unless you have a specific reason not to
do so.
Public -
Properties that can be set directly as needed.
f &File
q &queue
g &group
v &View
Pointers to the data structure associated with the object. Call the With method to associated the object with a data structure manually, or pass the data structure to the Load or Save methods. These properties should not be directly accessed in general use.
A reference used to manipulate the data structures in a generic manner. This properties should not be directly accessed in general use.
The data type being import or exported, set to oi:File, oi:Queue, oi:Group or oi:View to indicate the type of data structure currently associated with the object. Set by the With() method (which is in turn called by Load and Save).
Set to True (1) to enable debug output, or False (0) to disable it. Debug output is written to the system debug ouput, and can be viewed by running DebugView (a free 286KB tool provided by Microsoft - download it from Microsoft™ TechNet here).
Debug information is output when the ErrorTrap method is called, and additional debug information can be written to the system debug output by calling DbgMsg.
Properties used for both import and export
Maps columns to fields. See the CreateMap, SetMap, AddMap, GetMap and ClearMap methods.
oiColumnMap
queue,
type
fieldPos
long
col
long
fieldName
string(256)
! Friendly name, purely for display purposes
colName
string(256)
! For display purposes
end
A queue of ignored fields or columns. The queue contains a col property, and each entry represents a column to ignore (when loading) or a field to ignore (when saving). Ignored fields and columns are treated as if they did not exist (no blank entry is loaded or saved).
! Fields (by position) or columns to
be ignored during import/export
oiIgnored queue,
type
col
long
end
See the IgnoreField and ClearIgnored methods
Set when Excel is initialised by the object. If this is set, then when the object is destroyed, then the instance of Excel created will be destroyed.
Name of the Excel document being saved to. This workbook will be created if it does not exist, and will be overwritten if it does exist (unless the .appendToFile property is set to True).
Processing Properties
Properties that modify the position to process from, and the number of records to process. These are cleared at the end of each load/save call.
Row to start at in the Excel document
Column to start at in the Excel document
The current row being read/written, used primarily in the callback methods to determine which rows is being processed. The curRow can also be adjusted to skip out rows in the spreadsheet, for example when adding custom data between rows.
The current column being read/written to.
Total number of rows to process. Set automatically to all rows in the spreadsheet, and can be overridden to limit the number of rows processed.
Number of columns to process, see numRows.
Field to start at in the data structure when importing or exporting. Defaults to 1 (the first field).
The number of fields to export
Position of the record being processed.
The current field in the data structure being processed.
Position of the current field being processed in the data structure.
Optional handle to a progress control. See SetProgressControl.
Used to automatically unhide a progress control during the process, and then hide it again when complete. This does not need to be set manually - if the progress control is hidden, this will be set, and it will be unhidden during processing. When processing is complete, the progress control will then be hidden.
action
long
If this is oix:Save, then a save (export) is being performed, otherwise a Load (import) is being performed.
The error code for the last error that occured.
Stores the error message associated with the last error that occured. Set by the ErrorTrap method.
Export properties
Properties that effect the way in which data is saved to the Excel workbook.
If set the file is opened once the save is complete
commitFileRecords long(1024)
How many file records to process when loading before committing (when using a transaction).
headers
&oiHeaders
Queue of custom headers for each column. See the HeadersFromFields and SetHeader methods.
oiHeaders
queue,
type
col
long
text
string(oi:maxHeaderLen)
end
appendToFile
lolong
If this is set, then the Excel workbook is appended to rather than overwritten. The data is added to the end of the first worksheet.
Import properties
freeBeforeLoad lolong
If set the data structure is cleared, and all records are deleted before loading from the Excel workbook.
cellsQ
&excelImportQType
Queue of cell data for the current row. Allows filtering and modification in the callback methods such as ValidateRecord and TakeRecord.
excelImportQType
queue, type
cell
string(_oit:LargeStringSize)
enend
View specific properties
fileOpened
long, dim(100)
Set if Files have been opened by the object. When a File is associated with the object, then the first entry in the array is set to True, and the File is closed when the Kill method is called. When a View is associated with the object, each of the Files that need to be opened are opened by the object and the entry in the fileOpened array is set to True (1) to indicate that the file at that position in the View was opened. A maximum of 100 tables in a View are supported.
viviewOpened
long
Set to True (1) when the View associated with the object was opened by the object. If this is set, then the View is closed when the Kill method is called.
bindPushed
long
Set to True (1) when a View is associated with the object and PushBind has been called. When the Kill is called PopBind will be called if this is set.
See the ImportExport example for more information and the code in context.
Save a File to an Excel Workbook.
Save all fields in the File to the spreadsheet, in the order that they occur in the File. All records are saved.
if not ImpEx.Save(Customers, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
Save a Browse Queue to an Excel Workbook
Limit the number of fields saved (7 fields in this case), and skip every second field (the Style or Icon field for example) by calling the IgnoreField method.
ImpEx.numFields = 7
ImpEx.IgnoreField(2)
ImpEx.IgnoreField(4)
ImpEx.IgnoreField(6)
if not ImpEx.Save(BrowseCustomersQ, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
Save a View to an Excel workbook
Save all fields in the view to the Excel workbook in the order that they occur.
if not ImpEx.Save(BRW1::View:Browse, excelFileName)
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
Load from an Excel SpreadSheet into a Queue
This example loads from an Excel spreadsheet directly into the queue. Each column is placed into the queue field in the order in which they occur.
ImpEx.freeBeforeLoad = True ! Clears the structure of all data/records before loading
if not ImpEx.Load(ExpensesQ, LongPath() & '\expenses.xls')
Message('Cannot import the data, loading failed. ' & CLip(ImpEx.errorMessage), 'Import Error', Icon:Exclamation)
end
Load from an Excel Spreadsheet into a File (Table)
This example loads the contents of an Excel spreadsheet into a File. The first field is skipped (in this case because it contains an ID that should be AutoNumber). See the Code in the section below on how to override the TakeRecord and InsertRecord methods to provide auto-numbering and to use the FileManager Insert() method to insert the records.
ImpEx.startField = 2 ! Skip the record ID field
if not ImpEx.Load(Customers, excelFileName)
Message('Cannot import the data, loading failed. ' & CLip(ImpEx.errorMessage), 'Import Error', Icon:Exclamation)
end
BrowseCustomers.ResetFromFile()
Override the TakeRecord and InsertRecord methods
The oiExcelImpEx class provides a number of callback methods to allow the behavior of the class to be customized.
The TakeRecord method is provided to allow any additional code to be added that should be called each time a new record is processed.
The InsertRecord method is provided to allow the default record insertion to be overridden (for example to call the FileManager Insert method rather than using Add).
The example below customizes the behavior when loading files (handle auto numbering and call the FileManager method to insert the record).
! When loading from a File, populate the autonumber ID field
ImpEx.TakeRecord Procedure (*excelImportQType cellsQ)
code
if self.dataType = oi:file and self.action <> oix:Save
Access:Customers.PrimeRecord(true) ! Prime the record, do not clear fields
end
return parent.TakeRecord(cellsQ) ! Call the parent to continue processing
! When loading from a File, call the FileManager Insert method and return.
! For all other cases, call the parent to before the default action.
ImpEx.InsertRecord Procedure()
code
if self.dataType = oi:file and self.action <> oix:Save
return Access:Customers.Insert()
else ! Use the default behaviour when saving
parent.InsertRecord()
end
Export data to an existing Excel file (appending to or overwriting part of a worksheet)
In addition to exporting to a new file the Save method can be used to export to an Excel workbook which already exists. The new records can either be appended after the rows which already contain data, or they can be written from a specific row (overwriting any data which those rows might already contain).
1. Appending to an existing sheet:
ImpEx.appendToFile = true
if not ImpEx.Save(Customers, 'Customers') ! Note: If not file extension is specified, Excel will add one.
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
2. Add the data starting at a specific row (for example to preserve a header). This uses the TakeSetup() callback method.
ImpEx.appendToFile = true
if not ImpEx.Save(Customers, 'Customers') ! Note: If not file extension is specified, Excel will add one.
Message('Cannot Save the data. ' & CLip(ImpEx.errorMessage), 'Export Error', Icon:Exclamation)
end
In ImpEx.TakeSetup():
ImpEx.TakeSetup Procedure()
code
self.startRow = 3 ! Set the desired start row.
FAQ (Frequently Asked Questions)
Coming soon.