The ImportXLStoTPSAbc and ImportAnXLSFile templates allow you to import files from Excel into you database.
 

2) ImportAnXLSFile: import without a Wizard:
Add the local Office Inside extension to your procedure to populate an Excel
object.
Add a button to the window to start the import (you can also use a menu item
etc.) Go to the embeds for the button and double click on Event:Accepted.
Select "ImportXLS- Import an XLS File" from the list (under Class
OfficeInside).

Once you have added the code template, double click on it to change the
settings - enter the name of the Excel object and the file to import etc.

Both ways are demonstrated in the offdemo.app example application (see the
Excel_ImportFromXlsToTps_NoWizard, Excel_ImportFromXlsToTps_Wizard and
oiExcelImportWizard procedures).

AutoFilter     ( string pFromColumn, <string pToColumn> ) ,byte,proc
MyExcel.AutoFilter ('A', 'E')

• Applies an AutoFilter to the range of columns identified by the pFromColumn and pToColumn parameters.  See the Microsoft Office documentation on 'AutoFilter' for more information on how AutoFiltering works.

Calculate ()

Forces the workbook to calculate the values of all cells and ensures they are up to date. When data is written to Excel by default it automatically checks whether the data is a formula and performs any needed calculation on the cell. This can slow down writing of data dramatically, although it ensures that all cells in the workbook are always up to date. In order to speed up writing data to Excel this method allow calculation to be set to manual by calling the SetCalculations() method, so data can be written, and then any calculations needed can be performed by calling the Calculate() method

Parameters

none

Return Value

Returns 1 if the function succeeds, and zero for failure. If the function fails the ErrorTrap() method is called with information relating to the error.

Examples

    ExcelDoc.SetCalculations(oix:CalculationManual)
    ExcelDoc.SetScreenUpdating(false)               ! Optionally turn off screen updating

    ! Write the required data to the worksheet here...

    ExcelDoc.Calculate()                            ! Manually calculate (if necessary) 

    ! Automatic calculation can be turned back on:
    ExcelDoc.SetCalculations(oix:CalculationAutomatic) 

ClearPrintArea ( ) ,byte,proc

MyExcel.ClearPrintArea ()

• Same as "File" --> "Print Area" --> "Clear Print Area" in Excel.
• Returns true (1) if no problems were experienced.

• Closes the currently open workbook.
• The pSaveChanges parameter can be one of the following values:
  • 1  - User will be prompted (if necessary) as to whether he wants to save changes
  • 2  - Changes will be discarded without warning the user
  • 3  - The "Save As" dialog window will be called
• Returns true (1) if no problems were experienced.

• Same effect as "Edit --> Copy" in MS Excel.
• You can either copy the current selection, or a single specified cell, or a specified range (as shown above). 

ColumnName (long columnNumber)

Returns a string that contains the Excel name of a column when passed the number of the column. Excel names columns alphabetically. The first column is A, the second B and so on. After the 26th column (Z), two characters are used to present the column. Column 27 is AA, 28 is AB and so on. This method actually returns the characters used for any column up to 450,000. Version of Excel prior to Excel 2007 only supported 255 columns (so the final column was named IV). In Excel 2007 this limit has been increased to 16K.

This method is particularly useful when processing columns in a loop.

Parameters

long columnNumber:

Return Value

Returns a string that contains the name of the column.

Examples

This example writes the name of each cell to that particular cell for 100 rows and 320 columns

columnName      cstring(6)
col             long
row             long
  code 
    MyExcel.SetCalculations(oix:CalculationManual)  ! Improve bulk insert performance 

    loop col = 1 to 320
        columnName = MyExcel.ColumnName(col)
        loop row = 1 to 100
            MyExcel.WriteToCell(row & columnName )
        end
    end

    MyExcel.SetCalculations(oix:CalculationAutomatic)

TempLong = MyExcel.CountOpenWorkbooks()

• Returns the number of currently open workbooks, or a negative value if an error occurred.
• NOTE: At this time certain methods in Office Inside only support having one Workbook open at a time. 
•  We don't recommend opening more than one at this stage, although this will change in future releases.

• Returns the bottom-most row number of all the used cells in the currently open worksheet, or a negative number if an error occurred.

• Returns the number of currently open worksheets, or a negative number if an error occurred.

• ( Not yet implemented... )

• Deletes the currently selected cells.  pShift can be one of the following equates:
  oix:Down
  oix:ToLeft
  oix:ToRight
  oix:Up

• Deletes the currently selected WorkSheet.

ErrorTrap (string pErrorString, string pFunctionName)

This method is called when an error occurs. Office Inside provides embed points for this method (before parent call, and after parent call) where you can put code to deal with any errors Office Inside experiences (see the example code above - note the grey text indicates code generated by the Office Inside template, the black text is what you would add).

By default any errors that Office Inside encounters will be dealt with as follows:

• First, this ErrorTrap method is called, where you can act on the error message as shown in the example code above.
• Second, Outlook might display a message of its own.  You can suppress all messages by ticking the Suppress Error Messages check box in the procedure extension template.

Parameters

string pErrorString: A string containing the error description.

string pFunctionName: A string that contains the name of the function that the error occured in.

Examples

MyExcel.ErrorTrap PROCEDURE (string pErrorString, string pFunctionName) 
CODE
PARENT.ErrorTrap (pErrorString, pFunctionName)

if pErrorString = 'Init Failed'
    HandleInitFailure()
end

Return Values

None

Notes

Note: This is a callback method. This means that unlike normal methods that you call in order to perform some function, the OfficeInside methods will call this method. This allows you to add your own code in this method to respond to to the particular event that triggered the method being called.

Note: Even if you suppress error messages the ErrorTrap method will still be called, however no message will be displayed to the user.

Note: The Suppress Error Messages checkbox simply generates a line of code that sets the SuppressErrorMessages property to true.  You can set this property manually if you prefer.

ExcelImport (string fileName, long progressControl=0, long columns, long startRow=1)

Imports a file from Excel and allows each row to be processed. Loads a large number of file formats supported by Excel and processes each row in the file. For each row the TakeImportRecord() method is called to allow you to then process the row (record). The TakeImportRecord method is passed a queue that contains all the cells in the current row.

Parameters

string fileName: The file name to import. If an empty string ('') is passed the method will prompt the user for a file name.

long progressControl: An optional parameter that allows the a progress control handle to be passed to the method. The progress control will be updated as the import is done. Optional.

long columns: The number of columns to import. Required.

long startRow: The row to start at. This value is optional and default to importing from the first row (1).

Return Values

Returns 1 for success and zero for failure, or if the user cancels the import (only applies if no file name was passed and the user is prompted for a file name).

Examples

The below example demonstrates how to use the Who() and What() method to dynamically assign each field from the queue to a any file, based purely on field order. You could also use Who() to find fields with matching names. Alternatively you could fetch specific columns using the Get() function and assign them to specific fields rather than using a loop to do this dynamically.

MyExcel.ExcelImport('expenses.xls', ?Progress, 32)

! This will call the TakeImportRecord() method for each record,
! which allows the records to be processed (see below):

MyExcel.TakeImportRecord Procedure(long recordNumber, excelImportQType cellsQ)
ReturnValue   long                                                           
destField     any
sourceField   any
i             long
  code                                                                       
    loop i = 1 to Records(cellsQ)
        Get(cellsQ, i)
        sourceField = Clip(cellsQ.cell)
        if Who(MyFile.Record, i) <> ''       ! If the field exists in the file assign to it.
            destField &= What(MyFile.Record, i)
            destField = sourceField
        end
    end
    if Access:MyFile.Insert()
        return level:fatal                   ! Error adding a record, cancel import
    end

    return level:benign                      ! continue importing
! Parent Call                                                                  
ReturnValue = parent.TakeImportRecord(recordNumber, cellsQ)                    
! [Priority 5000]                                                              

! End of "Class Method - Executable Code Section"                              
Return ReturnValue                                                             

ImportSetup ()

This method is called by the ExcelImport method once the Excel spreadsheet has been opened, but before any data is imported. This allow the sheet to be changed, or the document otherwise manipulated or modified before the actual importing of individual records (rows) begins.

Parameters

None

Return Values

None

Examples

ThisExcel.ImportSetup()
  code
    ! Select worksheet to import using the worksheet name
    self.SelectWorksheet('Sheet 2')  
    parent.ImportSetup()

ImportComplete ()

This method is called by the ExcelImport method once the Excel spreadsheet has been completely imported, but but before the spreadsheet is closed. You can use this to do any cleanup, or take any actions once the import is completed that might need to be done on the Excel document.

Parameters

None

Return Values

None

Examples

ThisExcel.ImportComplete()
  code
    ! Save a copy of the imported document as a CSV file in an 'Imported' folder
    self.SaveAs(LongPath() & '\' & 'Imported\' |
              & Format(Today(), @D6) & '.xls', oix:xlCSV)  
    parent.ImportComplete()

• Freezes or unfreezes panes, depending on the value you pass in the pFreeze parameter (as shown in the example code above).  See the Excel help for more information.

GetCellBackgroundColor (string fromCell, string toCell) , long

Returns the background color of a cell or range of cells. The value returned is the palette position of the color, use the GetPaletteColor() and SetPaletteColor() to get and set the actual colors used.

Parameters

string fromCell

The starting cell address. Both cell addresses can be passed as empty strings to use the currently selection.

string toCell

The ending cell address. This can be passed as an empty string if a specific cell, or the current selection is being used.

Return Value

The palette position of the background color for the specific cells, or the current selection if no cell addresses are specified. If an error occurs the method returns -1 (color:none). The retrieve the color corresponding the the palette entry returned use the GetPaletteColor method.

Examples

curColor = oiExcel.GetCellbackgroundColor('', '')    ! Fetch the color for the currently selected cells
curColor = oiExcel.GetCellbackgroundColor('A1', '')  ! Fetch the color for a specific cell
curColor = oiExcel.GetCellbackgroundColor('D1', 'E7')! Fetch the color for the range of cells specified

GetColumnAddressFromColumnNumber (long pColumnNumber)

Important: This method is deprecated and has been replaced by ColumnName(). The new method removes the limitation of 26 columns and supports up to 450,000 columns (Excel 2007 removes the 255 column limit of previous version and supports 65535 columns). The GetColumnAddressFromColumnNumber method now just calls ColumnName().

MyExcel.WriteToCell ('Cell A1', (MyExcel.GetColumnAddressFromColumnNumber(1) & '1') )
MyExcel.WriteToCell ('Cell B1', (MyExcel.GetColumnAddressFromColumnNumber(2) & '1') )
MyExcel.WriteToCell ('Cell C1', (MyExcel.GetColumnAddressFromColumnNumber(3) & '1') )

• Many methods in the Office Inside Excel object(s) take a cell address as one of the parameters.  An example of a cell address is "C5" for instance.  When coding (loops for instance), it is often inconvenient to have to enter a cell address using characters other than numbers, which is why we have this method.  The above example code shows how to write to cells A1, B1, and C1, using the numbers 1, 2, and 3 (in conjunction with this method) to make up valid cell addresses.  All this method does is takes a number (which is the column number, so column D would be column number 4 for instance), and returns the column "Address", as in "D".  You can use this method as shown above to convert numbers into valid cell addresses.

• Returns the width of the column that you pass as pColumn, or a negative number if an error occurred.

• This is a wrapper method which can be used to get various information, and read various properties, depending on the parameters which you pass.
• The pOption parameter can be any one of the Excel GetInfo / Update Equates.

GetFileName (), string

Returns the file name and path on disk for the currently open document.

Paremeters

None

Return Value

Returns a string containing the file name and path of the current document. If the document was not opened, and has not been saved, then it returns the current name of the document, such as 'Document1'.

Unlike MS Word, Excel stores colors in a palette - you can only use the colors contained in that palette.  If the default palette for a given worksheet does not contain the colors you want to use, you can change them using the SetPaletteColor method.

For two palette management functions for your applications see the Examples section below, as well as the section Color Management in Excel and Cell Formattting (recommended reading).

Parameters

byte pSwatch: The palette position (swatch) to return the color of.

Return Value

Returns a long value that contains the color of the swatch (palette entry) specified by the pSwatch parameter.

Notes (Important)

The color swatches are the "blocks" of color you see when you click the "Font Color" droplist on the Excel toolbar.  You will see there are 8 columns and 5 rows or swatches (totalling 40 swatches).  We have labelled these swatches as 1 being column 1 row 1, 2 being column 2 row 1, 8 being column 8 row 1, 9 being column 1 row 2, and so on.

By default, Office Inside supports using standard Clarion color equates, so long as you're using the default Excel palette, so you would simply pass COLOR:YELLOW to an Office Inside method, and it would then use the closest match to that color from the default palette.

If you would rather use the palette directly (which means that passing standard Clarion color equates would no longer work), so can do this by simply setting the InterpretClarionColorEquates property to false (after calling oiExcel.Init(), which will set it to true), as shown in the example code. If you do that, then any color parameter that you pass in needs to be the color swatch number.

Important: By default oiExcel.InterpretClarionColorEquates is set to true. This means that passing RGB color values, or colors stored in longs will not work. You must set this property to false (0) to pass colors as long value or as RBG colors (such as 0FF00FFh or a color value returned from the ColorDialog() function.

Examples

The first example below demonstrates how to create a local array of colors and a pair of procedures. This allows you to create your own color palette and synchronize it with that of Excel.

Example 1

AddPaletteColor(): This adds a palette color. Note that each time a color is added the variable numColors is incremented. In this case for versions of Excel prior to Excel 2007 you should limit this to 40 entries.

GetPaletteColor(): This locates a palette entry from the local palette by matching the passed color to each palette entry. You could expand on this to find the closest match. Unlike the oiExcel.GetPaletteColor method, this does not return a color at a position in a palette, it finds the color and returns the palette position.

! Local or module data
numColors          long           ! number of colors used in the custom excel palette
colorPalette       long, dim(40)  ! colors in the excel palette
curColor           long           ! the current color in the excel palette
palettePos         long           ! position of the current color in the palette
 

! Adds a color to the local and Excel palette
AddPaletteColor procedure(long pColor)
  code
    if NumColors > 39 and ExcelDoc.GetOfficeVersion < 12   ! Limit colors for old versions
        ! can do error handling here, or choose which color entry to replace etc.
        return
    end
    numColors += 1
    colorPalette[numColors] = pColor
    curColor = pColor
    palettePos = numColors

    ! add the color to the excel palette
    ExcelDoc.SetPaletteColor(numColors, pColor)     


! This locates a passed color in the palette and returns the position 
GetPaletteColor procedure(long pColor)
i              long
  code
    loop i = 1 to 40
        if colorPalette[i] = pColor
            curColor = colorPalette[i]
            palettePos = i
            return i
        end
    end

    return 0

Example 2

! Let the user pick a color (RGB)
if ColorDialog('Select a color', tempLong)
    ! Set InterpretClarionColorEquates to false and pass RGB colors,
    ! Not Clarion color equates.
    MyExcel.InterpretClarionColorEquates = false 

    ! Set swatch 33 to this RGB color (column1, row5 in palette)
    MyExcel.SetPaletteColor (33, tempLong)       
 
    ! Set cell B2 to the color in swatch 33, which we just set
    MyExcel.SetCellBackgroundColor (33, 'B2')    
end

Example 3

curColor2 = MyExcel.GetPaletteColor(2) ! Fetch and store the current color
MyExcel.SetPaletteColor(2, color:red)  ! Replace it with red (Clarion color equate)
curColor3 = MyExcel.GetPaletteColor(3)

MyExcel.InterpretClarionColorEquates = false
MyExcel.SetPaletteColor(3, 0FF00CCh)   ! Set to an RGB color value
! Set cell B2 to the color in swatch 33, which we just set
MyExcel.SetCellBackgroundColor(3, 'B2') 

• Returns the height of a row (see example above)

• Makes the Excel Formula Bar not visible.
• Returns true (1) if no problems were experienced

• Makes the Excel Status Bar not visible.
• Returns true (1) if no problems were experienced

• Hides the toolbar specified in the pToolbar parameter.  For a list of valid Toolbar equates click here.
• Returns true (1) if no problems were experienced.

ImportXLSFile (string pFileName, long pProgressControl=0, long pColums=10)

Important: This method is deprecated. Please use the new ExcelImport method.

This method provides an easy way to import an Excel spreadsheet.  You simply call this method, and then Office Inside will read the data out of the spreadsheet, one row at a time.  As it reads a row, the object's ImportXLSFile_TakeRecord method will be called, and the data for that row will be contained in the parameters for the ImportXLSFile_TakeRecord method.  There are actually two different ImportXLSFile_TakeRecord methods. One only passes in columns A through to J (default), the other passes in columns A through to Z.  Only one of these two ImportXLSFile_TakeRecord methods will be called. Which one called is determined by the value of the pColumns parameter:

• If pColumns is omitted or is passed as 10, then only 10 columns are read in (The ImportXLSFile_TakeRecord method that passes in A through to J is fired)
• If pColumns is omitted or is passed as 26, then 26 columns are read in (The ImportXLSFile_TakeRecord method that passes in A through to Z is fired)

Examples

Typically you would code as follows:

! Call ImportXLS file, to begin importing records
MyExcel.ImportXLSFile ('c:\temp.xls')

! Once you have called ImportXLSFile, the ImportXLSFile_TakeRecord method
! will fire once for every record that is read in, you have to put code
! into that method to actually write the record to your table, as follows:

MyExcel.ImportXLSFile_TakeRecord PROCEDURE (long pRowNumber, string pA, string pB, |
                                            string pC, string pD, string pE, string pF, |
                                            string pG, string pH, string pI, string pJ)
                                            
ReturnValue        byte
  CODE

    if pRowNumber > 10
        ! you don't have to do this, but you can return 
                ! record:OutOfRange to stop processing records
        return Record:OutOfRange
    end

    if access:TempExcelTable.tryinsert() = level:benign
        TMP1:ColumnA = pA
        TMP1:ColumnB = pB
        TMP1:ColumnC = pC
        TMP1:ColumnD = pD
        TMP1:ColumnE = pE
        TMP1:ColumnF = pF
        TMP1:ColumnG = pG
        Access:TempExcelTable.update()
    end

    ReturnValue = PARENT.ImportXLSFile_TakeRecord(pRowNumber, pA, pB, pC, pD, |
                                                  pE, pF, pG, pH, pI, pJ)

Init (byte startVisible=1, byte enableEvents=0)

Initialises the oiExcel object and the COM interface and performs all setup necessary to begin automating Excel, including starting Excel itself and controlling whether Excel starts as visible to the user and whether to enable event handling.

Parameters

byte startVisible: Determines whether Excel is visible at startup or not. Pass as 1 (the default) to make Excel visible, or pass zero to keep Excel hidden.

Note: You can also set whether Excel is visible after you have called the Init method by using the Update method, passing oix:ApplicationVisible as the property to set and 1 as the value to set it to.

byte enableEvents: Determines whether event handling is enabled. Defaults to zero (no event handling). Pass as 1 to enable event handling.

Return Values

Returns true (1) if successful and false (0) if the method fails.

Examples

! Initialise with the standard parameter values (Excel visible with no event handling)
MyExcel.Init()     

! Initialise with Excel hidden and event handling on:

• Inserts a column to the left of the cell specified in the pCellAddress parameter.  If pCellAddress is not passed, then a column is inserted to the left of the currently selected column.
• Returns true (1) if no problems were experienced.
•  More options to follow.  The ShiftOption parameter is not yet supported. 

• Inserts a row above the cell specified in the pCellAddress parameter.  If pCellAddress is not passed, then a row is inserted above the currently selected row
• Returns true (1) if no problems were experienced.
•  More options to follow.  The ShiftOption parameter is not yet supported. 

• Inserts a new worksheet into the currently loaded workbook.
• Returns true (1) if no problems were experienced
• More parameters to follow.

MyExcel.Kill()

• Kills the oiExcel object and handles any cleaning up
• Returns true (1) if no problems were experienced
• Please do not use the pUnloadCOM parameter yet.  If you are going to call this method do so as per the example code above.

MyExcel.MergeCells ('B2', 'C4')

Merges the range of cells identified by the pFromCell and pToCell parameters.

• Creates a new Workbook.
• Returns true (1) if no problems were experienced.

MyExcel.OpenWorkbook()

• Opens the workbook called pFileName.
• Returns true (1) if no problems were experienced.

Same effect as "Edit --> Paste" in MS Excel.

Example

MyExcel.Paste()

This method has not been implemetned in this realease.

Example

MyExcel.PasteSpecial()

Notes

Not yet implemented...

Prints the current document to the default system printer.

Return Value

Returns true (1) if no problems were experienced.

Examples

MyExcel.PrintMe()

Notes

More options to follow in future releases...

• Returns true (1) if no problems were experienced.
• More options to follow in future releases...

This is the equivalent of "Tools" --> "Protection" --> "Protect Workbook" in MS Excel.  If you are unfamiliar with protecting sheets refer to the MS Excel documentation (search for "Protect workbooks from changes").

• This is the equivalent of "Tools" --> "Protection" --> "Protect Sheet" in MS Excel.  If you are unfamiliar with protecting sheets refer to the MS Excel documentation (search for "Protect worksheets from changes").

Reads a value from a cell, which is returned as a string. There is a limit of 255 characters for the returned data.

• Parameters:
  • pCellAddress: The Excel address of the cell to be read using the standard Excel format such as 'B3', 'AC22', where the letter represents the column and the number represents the row.
  • pFormula: Optional parameter that allows you to read the formula for the cell rather than the actual data. For example if the cell contains
    'A3*B3', which gives a value of 140 then passing pFormula as FALSE will return the value '140', whereas setting pForumula to TRUE will return 'A3*B3'.
    
• Return Value:
  • Returns a string containing the cell contents, with a maximum of 255 characters.
    
• Example:

cellValue         string(255)
cellFormula       string(255)

code

    cellValue = MyExcel.ReadCell('B3') 
    cellFormula = MyExcel.ReadCell('B3', true) 

        

• Same as "Redo" in Excel.
• Returns true (1) if no problems were experienced.

• Renames the currently selected worksheet.  You can use the SelectWorksheet method to select a worksheet, and can then call this method to rename it.

• Searches for the text passed as pFindText, and replaces it with the text passed in pReplaceText

• Saves the current workbook, using its existing name.
• Returns true (1) if no problems were experienced.

Saves the current workbook using the name provided in the FileName parameter. Also optionally allows the File Type to be specified, which is particularly useful with Office 2007, which default to a new XLSX format, and displays a "Compatibility Warning" dialog box when saving a file that has the .XLS extension.

Return Values

Returns true (1) if no problems were experienced and zero (0) if the function failed. When an error occurs the ErrorTrap() method is called with error information, if you have debug logging enabled this will be output to the debug log and can be viewed using DebugView.

Parameters

string fileName (optional): Specified the name of the file that the document should be saved as, and should include the full path if the file is not saved in the current directory. If no file name is specified the user is prompted for a name.

long fileType (optional): A value that determines the type of file saved. This can be used to override the default Excel file type. For example in Excel 2007 the default file type is .XLSX, which cannot be opened by previous versions of Excel. This can be used to save as a standard XLS file, which is compatible with Excel 97, 2000, XP and 2003. The below table shows a list of all possible values, and the equates provided for the values, as well as a description of the file type.