CapeSoft.Com
Clarion Accessories
Office Inside
Documentation
Excel
CapeSoft Logo

CapeSoft Office Inside
Excel Documentation

Download Latest Version JumpStart FAQ History
Installed Version Latest Version

JumpStart for adding Excel functionality into your application

Data Import into/Export from your application.

There are 2 ways of doing this. You can either provide a wizard for your users to map the spreadsheet's columns to the fields in your database, or you can programmatically assign the fields to the spreadsheet columns (in which case no wizard is needed).

These are both demonstrated in the offdemo.app example shipped with OfficeInside.

Tip: You can also use SendTo to export browses and lists to Excel, Word, PDF, Email, etc. with all the correct formatting. SendTo uses Office Inside to export to Excel and Word, so it is a good example, and actually contains some very useful Office Inside code (SendTo ships are pure Clarion source code). The Data Export in OfficeInside is simply that - a data export. There is no formatting included.

To implement  Non-wizarded data export/import see Importing/Exporting data programmatically from your application

Implementing a wizard to allow the user to map the data fields to the excel columns (Only available in ABC applications at this stage)

  • Open your dictionary and import the oiSavedColumnMaps.dctx (or txd) file. In C7 and up, this is located in your clarion\accessory\libsrc\win directory.

    Clarion 7/8:


    You will see a new oiSavedImportMaps table added to your dct:
  • Save and close your dictionary and open your application where you want the ExcelWizard to reside.
  • If you have not done so already, add the OfficeInside global extension to your application
  • From the Application menu in the Clarion IDE select "Template Utility" and select "ImportXlsToTpsABC" from the Class OfficeInside section. This will add the import Wizard procedure.


  • You will see a new procedure added called oiExcelImportWizard.
  • On the properties of the Excel Import Wizard Controls extension, you can add the tables that you would like to allow the user to import to:



    If you would like to re-import into a table, then you can use a unique key to require the fields that are in the import in order to update existing records' specific fields in the table.

    The wizard has a number of screen controls. If you do not require some of the options, hide the controls (rather than deleting them).
  • From the procedure where you want to add the wizard, place a button (or other actionable item) and call the Excel_ImportFromXlsToTps_Wizard procedure from the button (the procedure does not take any parameters by default) in the normal manner. For more options (like How to use the abc filemanager to insert (and handle duplicate entries) ) take a look at the Data Import/Export HowTos and Examples section of this document.
  • Importing/Exporting data programmatically from your application

    This will require some handcode, but it's really simple, so read on.

  • If you have not done so already, add the OfficeInside global extension to your application
  • Go to the local extensions in the procedure where you want to create the import, and add the "Add_MSExcel_Object" template to the templates there. In the template prompts, select "Basic" from the Template Type and give your Object Name something meaningful (like MyExcel).



    If you have a FileExplorer extension added to this procedure, then check the "Activate compatibility with file Explorer" checkbox.



  • In the source of your procedure, you simply call the following to save your data tables to (or load data into your tables from) an Excel spreadsheet:
  • MyExcel.Save(MyDctTable, 'MyTable')
    !Where MyDctTable is a table structure in your dct, and MyTable is the name
    	of the excel file to export to (don't worry about the .xls or .xlsx
    	extension - OfficeInside will figure out which version your user is using
    	and assign the extension accordingly)
    		
    MyExcel.Load(MyDctTable, 'MyTable')   
    !as above, but loads the data from the Excel spreadsheet into your table.
    
  • You can also Load from/save to a queue (or view), and do other fancy stuff (like begin export from a certain column, insert rows into an existing spreadsheet, ignore certain fields). Go to the Data Import/Export HowTos and Examples.
  • Exporting your reports to Excel

  • If you have not done so already, add the OfficeInside global extension to your application
  • If this is a Legacy application, then you must check the "Enable the use of ABC classes" checkbox in the Global Properties of your Application on the Classes tab.



  • In the extension templates of the Report procedure that you want to export to Excel, add the  "Add_MSExcel_Object" template to the templates there. In the template prompts, select "Reports" from the Template Type.



    If you have a FileExplorer extension added to this procedure, then check the "Activate compatibility with file Explorer" checkbox, and ensure the "Base Class" is set to "oiExcel".



    On the Reporting tab, there are a number of options you can set:



    File Name indicates the name of the file to store the output. You can either use an expression, or use a constant. You should not include the 'xls' or 'xlsx' extension though, as OfficeInside will populate this correctly depending on which version of Excel is installed on your user's PC.

    Write into Existing File allows the object to write into an existing Excel file if it exists. If you use this option then it's strongly recommended you set the Worksheet Name option as well. If you do not set the worksheet name then the report will always go into worksheet number 1.

    Worksheet Name tells the object which worksheet in the file to write into. If the worksheet does not exist then it will be created.

    Open Excel spreadsheet once generated if checked will run excel once the report has been generated (displaying the report).

    On the Report properties, make sure that you set the report to select the output at runtime:

  • Use the oiExcel object in your application.

  • If you have not done so already, add the OfficeInside global extension to your application
  • Go to the local extensions in the procedure where you want to create the import, and add the "Add_MSExcel_Object" template to the templates there. In the template prompts, select "Basic" from the Template Type and give your Object Name something meaningful (like MyExcel).



    If you have a FileExplorer extension added to this procedure, then check the "Activate compatibility with file Explorer" checkbox, and ensure the "Base Class" is set to "oiExcel".



  • Now you can use the oiExcel class Methods and Properties in your application to control Excel.
  • Converting an Excel Macro into your application

    Microsoft have developed a scripting language that allows you to automatically perform a sequence of tasks in Excel. The sequence of commands is called a macro. Excel has the more powerful (as of writing this doc using Exc 2010) macro recorder (as apposed to Word), and you're pretty much able to do everything you can do in excel to generate a script. First thing is to record a macro in Excel and then we'll work through converting the generated VB script code in the following manner:

    In your word doc, from the View menu, select the Record Macro in the Macros drop down:



    It's advisable to pick the "This Workbook" to Store the macro in (for the purposes of editing the macro with the current excel spreadsheet open):



    The Record Macro item now changes to a Stop Recording item. When you have finished the sequence of instructions that you're wanting to perform, click the Stop Recording item, and then use the View Macros to show the macro that you've just recorded. Highlight the macro you just recorded, and click the Edit button



    Now follow the steps in  Converting macro code to Clarion code to convert your macro code to Clarion code.

    How To's and Code Examples

    1. Data Import/Export HowTos and Examples

      NOTE: Fields = elements in your file/Queue/View. Columns = columns in the Excel spreadsheet.

      1. Save to a File, Queue, View
      2. Load a Queue or File
      3. How to change values during import or export (or override the default add/put methods)
      4. How to append to an existing Excel spreadsheet (including overwriting existing data if necessary)
      5. How to ignore certain fields
      6. How to map columns to fields
      7. How to use the abc filemanager to insert (and handle duplicate entries)
      8. How to start at a particular row, and only export/import a certain number of rows
      9. How to add additional information (like a total) at the end of the export.
    2. For more info on the class's methods and properties take a look at The oiExcelImpEx Class section of the doc
    3. Controlling Excel from your application.

      1. Setting the Cell color using the Excel Palette
      2. How to manage the Palette
      3. How to write to and format cells
      4. Conditionally Deleting Rows from a Workbook

    Data Import/Export HowTos and Examples

    This section will show you how to save a File, Queue, View to Excel. Or Load a Queue or File from an Excel spreadsheet. See below.

    Also see: 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 MyExcel.Save(Customers, excelFileName)
         Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
    end

    Save a Browse Queue to an Excel Workbook

    Save all fields in a Queue to the spreadsheet, in the order that they occur. All records are saved.

    if not MyExcel.Save(BrowseCustomersQ, excelFileName)
        Message('Cannot Save the data. ' & CLip(MyExcel.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 MyExcel.Save(BRW1::View:Browse, excelFileName)
        Message('Cannot Save the data. ' & CLip(MyExcel.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.

    MyExcel.freeBeforeLoad = True                               ! Clears the structure of all data/records before loading
    if not MyExcel.Load(ExpensesQ, LongPath() & '\expenses.xls')
        Message('Cannot import the data, loading failed. ' & CLip(MyExcel.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. 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.
          if not MyExcel.Load(Customers, excelFileName)
              Message('Cannot import the data, loading failed. ' & CLip(MyExcel.errorMessage), 'Import Error', Icon:Exclamation)
          end
          BrowseCustomers.ResetFromFile()
    

    How to change values during import or export (by overriding 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 (like changing a field's text before it gets written to Excel).

    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 - or to change a field's text before it is written to the file).

    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
    MyExcel.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.
    MyExcel.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).

    Appending to an existing sheet:

         MyExcel.appendToFile = true
        if not MyExcel.Save(Customers, 'Customers')  ! Note: If not file extension is specified, Excel will add one.
            Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
        end

    To start at a particular row, add the following code:

    In MyExcel.TakeSetup():

    MyExcel.TakeSetup Procedure() code self.startRow = 3 ! Set the desired start row.

    How to ignore certain fields

    Note: You can also map fields to different columns. You should not use both mapping of columns and ignoring of fields. You need to adopt one approach to limiting which data columns you would like to import/export.

    The advantage of this approach is that it is very simple to implement. Essentially you call the IgnoreField method for each file/queue/group field that you want to ignore from the import/export.

    Before calling the save (or load) command, you need to tell the class which columns to ignore:

      MyExcel.numFields = 7         !If you're only importing from the first 7 fields, then indicate this using the NumFields property.
        MyExcel.IgnoreField(2)      !Ignore the second field of the file/queue/group.
        MyExcel.IgnoreField(4)
        MyExcel.IgnoreField(6)
        if not MyExcel.Save(BrowseCustomersQ, excelFileName)
            Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
        end

    How to map columns to fields

    Note: You can also Ignore certain fields to limit column export (or import). You should not use both mapping of columns and ignoring of fields. You need to adopt one approach to limiting which data columns you would like to import/export.

    The map fields method uses file/queue/group field to excel 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
    • As few or as many columns and fields can be mapped as needed
    • Column and field mapping can be done at runtime
        MyExcel.MapFieldToColumn(5,1)       !Map table/queue/group field number 5 to excel spreadsheet column 1.
        MyExcel.MapFieldToColumn(2,2)
        MyExcel.MapFieldToColumn(3,3)
        MyExcel.MapFieldToColumn(4,3)       !This will override the previous line, so that field 4 will be mapped to col 3.
        MyExcel.MapFieldToColumn(5,5)       !This will clear the first mapping, in that Field 5 will now be mapped to Col 5.
    	
        if not MyExcel.Save(BrowseCustomersQ, excelFileName)
            Message('Cannot Save the data. ' & Clip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
        end
    	

    How to use the abc filemanager to insert (and handle duplicate entries)

    The import template utility uses the load method to import data from excel into your database. The load method uses the generic add() command to perform the record addition. Often, though, it's preferable to use the FileManager to implement the add, which handles error correction, auto-inc, etc. You need to derive the InsertRecord method to put the call to the ABC manager there.

    If you are using the Import Wizard (out the box) then you can simply use the filemanager handle declared in the local class:



    If you are handcoding the import using the load method, then you need to know which FileManager you are using. Most likely the import is programmed for a single file, in which case you can use the file's Filemanager:

    Access:MyFile.tryinsert()
    return errorcode()

    If your user has an option of files, then you need to determine which file is being inserted into, and use the corresponding filemanager to perform the insert:

    if self.f &= MyFile
       Access:MyFile.tryinsert()
      return errorcode()
    elsif self.f &= AnotherFile
      Access:MyFile.tryinsert()
      return errorcode()
    end


    To handle duplicates (and perform an update rather than an insert if a duplicate occurs):

    if self.f &= MyFile
      if Access:MyFile.tryinsert()
        if access:MyFile.tryfetch(UseAppropriateKey)
          self.PrimeRecord()    !Prime the fields that need to be changed.
          access:MyFile.tryupdate()
        end
      end
      return errorcode()
    end

    How to start at a particular row, and only export/import a certain number of rows

    Before calling the load/save method, you can set at which row you want the Import/Export to occur from/to in the Excel spreadsheet. You do this using the StartAtRow property. You can also limit the number of rows to import/export from/to the Excel spreadsheet using the numRows property:

    MyExcel.StartAtRow = 3    !Start Importing/Exporting from the excel spreadsheet in row 3.
    MyExcel.numRows = 48     !Import/Export 48 rows from/to the excel spreadsheet.

    How to add additional information (like a total) at the end of the export

    The Save method is designed to be completely containerized, so it initializes, opens, does the export, closes and kills the Excel com object to be self-contained in one method. If you are wanting to perform additional functions, the full range of oiExcel functionality is available to you, which you can use in the derived TakeComplete method.

    MyExcel.Takeomplete procedure
      code
        parent.TakeComplete()
           !Write your code in hear using the various oiExcel methods and properties.

    Implementing Translation in the Excel Import Wizard

    All items that are translatable from within the class, are called with the translate method. The translate method simply receives a line of text and returns that same line of text. This means that you are able to derive the translate method and insert your code to implement translation on that text, and return the translated text string.

    Examples of strings that are translatable:

    'Select an Excel file to import', 'Excel Files|*.xls*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xlt|Web Pages|*.htm;*.mht;*.html;*.mhtml|XML Files|*.xml|Text Files|*.csv;*.txt;*.prn|dBase Files|*.dbf|Data Interchange Format|*.dif|All Files|*.*', 'Remove map to ', ' selected for the Table', column headings, and other error messages.

    MyExcel.translate procedure (string pText)
      code
        case pText
        of 'PRO:ProductName'   !The header name of the ProductName
          return 'Product'
        end 
     

    How Tos and Code Examples / Controlling Excel from your application

    This section provides code for common task using Office inside. You can also check out the Classes section section for details on other methods.

    Setting the Cell color using the Excel Palette (required reading)

    One of the most frequent problems that people have when formatting cells in Excel is setting the cell colour, only to have it end up black, rather than the color selected. This is because the default behaviour is to translate Clarion color equates to Excel colors. The first property that you have to make sure is set correctly when using color in Excel isI. This property defaults to 1 (enabled) and allows you to pass Clarion color equates to the various methods. To use the Excel equates or RGB colors you must set this property to 0. Also note that you do not have to use the Excel default palette, you can create your own palette, which avoids the rather hideous Excel default colors.

    Not setting interpretClarionColorEquates is the most frequent mistake when using colors to format excel spreadsheets.
    Always remember to set oiExcel. interpretClarionColorEquates! It is recommended that you store your own palette and use the functions provided below to manage the colors in the palette. This creates a "palette", which is an array of longs and is used to store the colors used, and the functions to get/set the colors ensure that the Excel palette remains sychronised. In versions of Excel prior to Excel 2007 this was limited to 40 colors (which is handled in the docs below).

    The oiExcel object in this example is named ExcelDoc.

    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
    
    cellFontColor     long           ! The color to set the cell to
    curCell           string(5)      ! The current cell
    columnNumber      long           ! The current column number
    rowNumber         long           ! The current row number
      code
    
    ExcelDoc.intepretClarionColorEquates = 0  ! Important: Treat passed colors as RGB
    numColors = 1                             ! First color in the Excel palette is always black
    palettePos = 1                            ! The current position in our palette
    colorPalette[1] = color:black             ! An array of colors (256)
    curColor = color:black                    ! the current color
    ! Each time I need to use a specific color I check whether
    ! it needs to be added to the palette:    
    if not ExcelDoc.GetPaletteColor(cellFontColor)
           ExcelDoc.SetPaletteColor(cellFontColor)
     end
     ExcelDoc.SetCellFontColor(palettePos, currentCell)   
    Methods and Properties for color managment:

    Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

    Properties: interpretClarionColorEquates

    How to manage the Palette

    The oiExcel Class provides two palette management methods. These allow a color to be retrieve from the palette by calling oiExcel.SetPaletteColor(). In addition GetPaletteColor() can be used to check if the color exists or not. The oiExcel.SetPaletteColor() method adds a color to the palette and keeps the objects's palette synchronised with Excel's. Note that the exception is color:black, which is always the first entry in the palette and can always be used.
    ! Adds a color to the local and Excel paletteAddPaletteColor 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

    How to write to and format cells

    ! Store the cell name (Note that the new method is called ColumnName()
    ! instead of GetColumnAddressFromColumnNumber())
    currentCell = ExcelDoc.ColumnName(columnNumber) & rowNumber
    
    ! Add the data:  
    ExcelDoc.WriteToCell(cellData, currentCell) 
    
    ! Format the cell:
    ExcelDoc.SetCellFontName(cellFontName, currentCell)
    ExcelDoc.SetCellFontSize(cellFontSize, currentCell)
    
    !Set the picture:
    ExcelDoc.SetCellTextFormat('@n03')             !You can also use the Excel format pictures directly if you know the excel picture to use 					  !using SetCellNumberFormat
    if Band(cellFontStyle, font:weight) >= font:bold
        ExcelDoc.SetCellFontStyle('Bold', currentCell)
    end
    if Band(cellFontStyle, font:underline)
        ExcelDoc.SetCellFontUnderline(oix:UnderlineStyleSingle, currentCell)
    end
    if cellFontColor > 0
        if not ExcelDoc.GetPaletteColor(cellFontColor)
            ExcelDoc.SetPaletteColor(cellFontColor)
        end
        ExcelDoc.SetCellFontColor(palettePos, currentCell)
    end
    
    if backgroundColor <> color:none
        if not ExcelDoc.GetPaletteColor(backgroundColor)
            ExcelDoc.SetPaletteColor(backgroundColor)
        end
        ExcelDoc.SetCellBackgroundColor(palettePos , currentCell)
    end

    Conditionally Deleting Rows from a Workbook

    This code example loads a workbook, processes each row, and conditionally deletes the row. The workbook is saved on completion.
    
    excel           oiExcel
    row             long
    tot             long
      code
        excel.OpenWorkbook(myWorkbookFile)          ! Open the workbook
    
        tot = excel.CountUseRows()                  ! Get the number of used rows
    
        loop row = 1 to tot
            idNumber = excel.ReadCell('A' & row)    ! Get the ID from a particular cell
            if idNumber = someValue
               excel.SelectRows (row)
               excel.DeleteSelection(oix:up)
            end
        end
    
        excel.Save()
    

    Classes

    This section describes the various classes which make up "Office Inside". Each class contains methods and properties, which are documented below. Each method is documented by giving the method name, the parameters, an example of how to code the method, a list describing what the method does, and additional comments below.

    The oiExcel Class - Introduction

    The oiExcel class is a "wrapper" class, which is used by the templates and by other classes as the "communication" layer between Office Inside and MS Excel. Should you wish to write code which communicates "directly" with MS Excel, this is the class you're looking for.

    The oiExcel Class - Methods Listed by Usage

    oiExcel Methods
    Workbook Methods
    NewWorkbook Creates a new workbook
    OpenWorkbook Opens an existing workbook
    CloseWorkbook Closes the currently open workbook
    CountOpenWorkbooks Returns the number of workbooks opened in this instance of Excel
    GetFileName Returns the current file name and path on disk.
    ProtectWorkbook Turns on protection for the current workbook
    UnprotectWorkbook Turns off protection for the current workbook
    Save Saves changes to the current workbook
    SaveAs Saves the workbook as a file on disk.
    Worksheet Methods
    InsertWorksheet Inserts a new worksheet
    DeleteWorksheet Deletes a worksheet
    RenameWorksheet Renames a worksheet
    CountWorksheets Returns the number of worksheets in the workbook
    ProtectWorksheet Turns on protection for the worksheet
    UnprotectWorksheet Turns off protection for the worksheet
    SelectWorksheet Selects a particular worksheet in the current workbook
    Rows and Column Methods
    InsertColumn Inserts a new column
    InsertRow Inserts a new row
    CountUsedRows Returns the number of rows that have been used (contain data)
    CountUsedColumns Returns the number of columns that have been used (contain data)
    ColumnName Converts a column number into the Excel name for that column.
    GetColumnAddressFromColumnNumber Converts a number for the column into the Excel name for that column. Deprecated: replaced by ColumnName()
    GetColumnWidth Returns the width of a column
    GetRowHeight Returns the height of a row
    SelectRows Selects one or more rows
    SelectColumns Selects one or more columns
    SetRowHeight Sets the height of a row
    SetColumnWidth Sets the width of a column
    Printing Methods
    SetPrintArea Sets the area to print (limits printing to the set area)
    ClearPrintArea Clears the are that was set using SetPrintArea() method
    PrintMe Prints the document (see the PrintWorkbook and PrintWorksheet for the newer, more extensive methods)
    PrintPreview Prints the document and displays a preview before printing to allow the user to choose whether to print or not and to check the print settings.
    PrintSheet Print out the current sheet in the current workbook
    PrintWorkbook Print the current workbook
    GetActivePrinter This method returns the currently selected active printer.
    SetActivePrinter This method sets the currently selected active printer.
    Editing Methods
    Copy Copies the current selection to the clipboard
    Find Searches for a string in the document
    Paste Pastes from the clipboard to the current position
    Undo Undoes the last action
    Redo Redoes an action that was undone
    DeleteSelection Deletes the current selection
    ReplaceText Replaces one or all occurrences of a string with the string specified.
    AddImage Add an image at the currently selected cell.
    View and Application Methods
    GetInfo Retrieves a variety of information related to Excel, the user, and the current document. This information can be updated using the Update() method.
    Update Sets a variety of information related to Excel, the user, and the current document. This information can be retrieved using the Get Info() method.
    GetPaletteColor Returns the colour at the passed palette position.
    SetPaletteColor Sets a colour in the Excel palette
    RunFile Opens the Excel file in the parameter
    View Management
    SetPageView Sets the way the page is displayed
    ShowFormulaBar Displays the Formula toolbar
    HideFormulaBar Hides the Formula toolbar
    ShowToolbar Shows the main Toolbar
    HideToolbar Hides the main Toolbar
    ShowStatusBar Shows the Status bar at the bottom of the window
    HideStatusBar Hides the Status bar
    FreezePanes
    Utility Methods
    ErrorTrap Called with error and warning messages. Provides centralised error handling and reporting.
    GetObjects Called to initialize the various oiObjects in the Excel class.
    Init Initialises the object and the COM interface if needed
    Kill Disposes of the object and the COM interface if it is no longer being used by any objects.
    TakeEvent Event handling, called when an event occurred in Word
    ExcelImport Deprecated: Use the oiExcelImpEx class for importing and exporting data.

    Imports a file from Excel into Clarion and calls the TakeImportRecord method for each row (record) imported.
    TakeImportRecord Called by ExcelImport to allow each row (record) to be processed.
    ImportSetup ExcelImport() Callback method - the spreadsheet has been opened, before records are imported.
    ImportComplete ExcelImport() Callback method - import is complete, before the spreadsheet is closed.
    ImportXLSFile (deprecated) Deprecated, replaced by ExcelImport
    AutoFilter Enables Excel's auto filtering on a range of cells.
    Cell Methods
    ReadCell Gets the value of the specified cell
    WriteToCell Sets the value of the specified cell
    WriteFormulaToCell Writes a formula to a specified cell
    MergeCells Merges a range of cells into a single large cell
    UnmergeCells Unmerges a merged range of cells back into the original cells
    SelectCells Selects a range of cells
    Cell Formatting
    GetCellBackgroundColor Gets the background color for a cell or a range of cells.
    SetCellAlignment Sets the alignment for the contents of the selected cells
    SetCellBackgroundColor Sets the background colour of the selected cells.
    SetCellBorders Sets the borders for the selected cells
    SetCellFontColor Sets the cell font colour
    SetCellFontName Sets the font for the contents of the selected cells.
    SetCellFontSize Sets the size of the font used for the selected cells
    SetCellFontStyle Sets the style of the font (bold, italic etc.)
    SetCellFontUnderline Underlines the contents of the selected cells
    SetCellNumberFormat Sets the format (picture) used to display the contents of the selected cells.
    SetCellTextFormat Sets the format (picture) used to display the contents of the selected cells using a clarion picture.
    SetCellProtection Turns on or off cell protection
    Performance and Optimization
    SetCalculations Enables or disables automatic calculation each time a cell is modified. Turning this off has a massive performance impact when doing bulk insertion.
    Calculate Forces a manual recalculation when SetCalculations() has been used to turn it off.
    SetScreenUpdating Turns screen updating on or off. For use when Excel is hidden and a batch update is done. Has a small performance impact.

    AutoFilter

    AutoFilter (<string pFromColumn>, <string pToColumn> ) ,byte,proc

    Description

    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.

    If pFromColumn is omitted, then the first column is assumed.
    if pToColumn is omitted, then the last column (with data) is assumed.

    Example

    MyExcel.AutoFilter ('A', 'E')

    Calculate

    Calculate ()

    Description

    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

    Example
        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

    ClearPrintArea ( ) ,byte,proc

    Description Example

    MyExcel.ClearPrintArea ()

    CloseWorkBook

    CloseWorkbook ( byte pSaveChanges=1 ) ,byte,proc

    Description Example

    MyExcel.CloseWorkbook() ! user is prompted to save

    Copy

    Copy    ( <string pFromCell>, <string pToCell> ) ,byte,proc

    Description Example
    Example
    MyExcel.Copy () ! copies current selection
    MyExcel.Copy ('A1') ! copies cell A1
    MyExcel.Copy ('A1', 'B3') ! copy range A1:B3

    Find

    Find    (string pFind, <string pAfter>, long lookIn=oix:Values, long lookAt=oix:Whole, long searchOrder=1, long searchDirection=1, long matchCase=0, long matchByte=0)

    Description

    Search the current workbook for the passed string. All values other than the string to search for are optional.

    Parameters
    ParameterDescription
    pFind The string to search for.
    pAfter [Optional] The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you don't specify this argument, the search starts after the cell in the upper-left corner of the range.
    lookIn [Optional]The search location - can be one of the following values: oix:Comments (-4144), oix:Formulas (-4123), oix:Values (-4163)
    lookAt The type of matching to do. Can be oix:Part (2) or oix:Whole (1). oix:Whole means that the text search for and the text searched in are an exact match. oix:Part means that the text searched for exists in the text searched.
    searchOrder Can be either oix:ByRows (1) or oix:ByColumns (2).
    searchDirection The direction to search in, either oix:Next (1) or oix:Previous (2)
    matchcaseWhether the search is case sensitive or not. oi:False for case insensitive, oi:True for case sensitive search
    matchByteOnly applies for double byte languages (Unicode). Setting this to true force double byte chars to only match double byte chars (which is not desired behaviour with Clarion strings)
    Return Value

    Returns a string containing the location of the range found if successful. Returns an empty string for failure or if there are no matches. If the method fails, ErrorTrap is called.

    ColumnName

    ColumnName (long columnNumber)

    Description

    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
    Parameter Description
    longcolumnNumber:
    Return Value

    Returns a string that contains the name of the column.

    Example

    This example writes the name of each cell to that particular cell for 100 rows and 320 columns
    Example
    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(columnName & row , columnName & row)
            end
        end

        MyExcel.SetCalculations(oix:CalculationAutomatic)

    CountOpenWorkBooks

    CountOpenWorkbooks( ) ,long,proc

    Description Example

    TempLong = MyExcel.CountOpenWorkbooks()

    CountUsedRows

    CountUsedRows     ( ) ,long,proc

    Description

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

    Example

    TempLong = MyExcel.CountUsedRows()

    CountUsedColumns

    CountUsedColumns     ( ) ,long,proc

    Description

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

    Example

    TempLong = MyExcel.CountUsedColumns()

    CountWorksheets

    CountWorksheets     ( ) ,long,proc

    Description

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

    Example

    TempLong = MyExcel.CountSheets()

    DeleteSelection

    DeleteSelection ( long pShift ) ,byte,proc

    Description Example

    MyExcel.DeleteSelection (oix:Up)

    DeleteWorksheet

    DeleteWorksheet ( ) ,byte,proc

    Description

    Deletes the currently selected WorkSheet.

    Example

    MyExcel.DeleteWorksheet()

    ErrorTrap

    ErrorTrap (string pErrorString, string pFunctionName)

    Description

    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:
    Parameters
    Parameter Description
    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
    Examples
    MyExcel.ErrorTrap PROCEDURE (string pErrorString, string pFunctionName)
      code
        PARENT.ErrorTrap (pErrorString, pFunctionName)
    
        if pErrorString = 'Init Failed'
    HandleInitFailure()
    end
    Return Values

    None

    Notes

    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

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

    Description

    Note: For importing and exporting to and from Excel, we recommend using the OfficeInside oiExcelImpEx class, which handles importing to and from queues, files, groups and views.

    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.
    Parameter Description
    string fileName The file name to import. If an empty string ('') is passed the method will prompt the user for a file name.
    long progressControlAn 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.
    Examples
    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                                                             

    GetActivePrinter

    GetActivePrinter     () ,string

    Description Example
    Example
    CurrentPrinter = MyExcel.GetActivePrinter ()

    ImportSetup

    ImportSetup()

    Description

    Note: For importing and exporting to and from Excel, we recommend using the OfficeInside oiExcelImpEx class, which handles importing and exporting to and from queues, files, groups and views.

    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
    Examples
    ThisExcel.ImportSetup()
      code

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

    GetObjects

    GetObjects()

    Description

    This method is to set the internal oiObject handles to point to their respective COM counterparts. Currently, the handles that are set are: ActiveWorkbook, ActiveWorkSheet, WorkSheets, Workbooks, Names and Cells. These are all child classes (or sub classes) of the Application class.

    Parameters

    None

    Return Values

    None

    Examples
    Examples
    ThisExcel.GetObjects()

    ImportComplete

    ImportComplete()

    Description

    Note: For importing and exporting to and from Excel, we recommend using the OfficeInside oiExcelImpEx class, which handles importing and exporting to and from queues, files, groups and views.

    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
    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()

    FreezePanes

    FreezePanes    ( byte pFreeze=true ) ,byte

    Description

    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.

    Example
    Example
    MyExcel.FreezePanes ()
    MyExcel.FreezePanes (false)

    GetCellBackgroundColor

    GetCellBackgroundColor    (string fromCell, string toCell) , long

    Description

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

    Parameters
    Parameter Description
    string fromCell The starting cell address. Both cell addresses can be passed as empty strings to use the currently selection.
    string toCellThe 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.

    Example
    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
    See Also

    Color Management in Excel and Cell Formatting

    Methods:GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

    Properties: interpretClarionColorEquates

    GetColumnAddressFromColumnNumber

    GetColumnAddressFromColumnNumber (long pColumnNumber)

    Description

    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().

    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.

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

    GetColumnWidth

    GetColumnWidth ( string pColumn ) ,string

    Description

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

    Example

    loc:ColumnWidth = MyExcel.GetColumnWidth('B')

    GetInfo

    GetInfo ( byte pOption ) ,string,proc

    Description Example

    TempString = MyExcel.GetInfo (oix:WindowState)

    GetFileName

    GetFileName (), string

    Description

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

    Parameters

    None

    Return Value

    Returns a string containing the file name and path of the current document. If the document was not opened (in other words, it is a new document rather than one loaded from disk), and has not been saved, then it returns the current name of the document, such as 'Document1'.

    GetPaletteColor

    GetPaletteColor (byte pSwatch), long

    Description

    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
    Parameter DDescription
    byte pSwatchThe 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.InterpretClarionColorEquatesis 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.

    Example

    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') 
    See Also

    Color Management in Excel and Cell Formatting

    Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

    Properties: interpretClarionColorEquates

    GetRowHeight

    GetRowHeight ( string ) ,string

    Description

    Returns the height of a row (see example above)

    Example

    X = MyExcel.GetRowHeight (5) ! get height for row 5

    HideFormulaBar

    HideFormulaBar ( ) ,byte,proc

    Description Example

    MyExcel.HideFormulaBar()

    HideStatusBar

    HideStatusBar ( ) ,byte,proc

    Description Example

    MyExcel.HideStatusBar()

    HideToolbar

    HideToolbar (long pToolbar) ,byte,proc

    Description Example

    MyExcel.HideToolbar(oix:ToolbarStandard)

    ImportXLSFile

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

    Description

    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: Example

    Examples Typically you would code as follows:
    Example
    ! 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

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

    Description

    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.

    Example

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

    ! Initialise with Excel hidden and event handling on:

    InsertColumn

    InsertColumn ( <string pCellAddress>, byte pShiftOption=0 ) ,byte,proc

    Description Example

    MyExcel.InsertColumn ()
    MyExcel.InsertColumn ('C3')

    InsertRow

    InsertRow ( <string pCellAddress>, byte pShiftOption=0 ) ,byte,proc

    Description Example

    MyExcel.InsertRow ()
    MyExcel.InsertRow ('C3')

    InsertWorksheet

    InsertWorksheet ( string pBefore, string pAfter, long pCount=0, long pType=0) ,byte,proc

    Description Example

    MyExcel.InsertWorksheet()

    Kill

    Kill ( byte pUnloadCOM=1 ) ,byte,proc

    Description Example

    MyExcel.Kill()

    MergeCells

    MergeCells ( string pFromCell, string pToCell ) , byte, proc

    Description

    Merges the range of cells identified by the pFromCell andpToCell parameters.

    Example

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

    NewWorkbook

    NewWorkbook ( ) ,byte,proc

    Description Example

    MyExcel.NewWorkbook()

    OpenWorkbook

    OpenWorkbook ( <string pFileName> ) , long, proc

    Description

    Opens a Workbook. The passed parameter is optional and if it omitted (or an empty string is passed), the user is prompted to select a Workbook to open. The file can be any type that is supported by the version of Excel installed. You can use the pfileName parameter to open a new workbook based on a template (and then SaveAs to save the workbook).

    Parameters
    Parameter Description
    pFileNameThe name of the file to open. Can be any format supported by the version of Excel installed. If pFileName is empty or omitted, then the user is prompted to select a file to open.
    Return Value

    Returns true (1) if no problems were experienced, zero if an error was encountered. If the method fails, then the ErrorTrap method is called with further information.

    Example
    Examples
    MyExcel.OpenWorkbook()       ! Prompts the user for the file name
    MyExcel.OpenWorkbook(LongPath() & '\data.xlsx') ! Opens the specified file

    Paste

    Paste ( ) ,byte,proc

    Description

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

    Example

    MyExcel.Paste()

    PrintWorksheetProcedure

    PrintWorksheetProcedure   (*oixPrintProperties printSettings)

    Example
    Example
    ! Excel print settings
    oixPrintProperties            group, type
    pFrom                           long                        ! The number of the page at which to start printing. If this argument is omitted, printing starts at the beginning.
    pTo                             long                        ! The number of the last page to print. If this argument is omitted, printing ends with the last page.
    copies                          long                        ! The number of copies to print. If this argument is omitted, one copy is printed.
    pPreview                        long                        ! True to have Microsoft Excel invoke print preview before printing the object. False (or omitted) to print the object immediately.
    activePrinter                   cstring(200)                ! Sets the name of the active printer.
    printToFile                     long                        ! True to print to a file. If PrToFileName is not specified, Microsoft Excel prompts the user to enter the name of the output file.
    collate                         long                        ! True to collate multiple copies.
    ! These two properties are not supported by the _PrintOut() method, which is being used, as the PrintOut() method always fails.
    prToFileName                    cstring(260)                ! If PrintToFile is set to True, this argument specifies the name of the file you want to print to.
    ignorePrintAreas                long                        ! True to ignore print areas and print the entire object.
                                  end    

    PrintMe

    PrintMe ( ) ,byte,proc

    Description

    Prints the current document to the default system printer.

    This method has been deprecated, see the PrintWorkbook and PrintWorksheet for the newer, more extensive methods).


    Return Value

    Returns true (1) if no problems were experienced and False (0) if an error occurs.

    Example


    MyExcel.PrintMe()

    PrintPreview

    PrintPreview ( )  ,byte, proc

    Description Example

    MyExcel.PrintPreview()

    ProtectWorkbook

    ProtectWorkbook ( string pPassword, byte pStructure, byte pWindows ) , byte, proc

    Parameters: pStructure and pWindows are reserved for later use. Pass 0,0 in the interim.

    Description

    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").

    ProtectWorksheet

    ProtectWorksheet( string pPassword, byte pDrawingObjects, byte pContents, byte pScenarios, byte pUserInterfaceOnly ),byte ,proc

    Description

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

    ReadCell

    ReadCell (<string pCellAddress>, byte pFormula=false) , string

    Description

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

    Parameters Return Value

    Returns a string containing the cell contents, with a maximum of 255 characters.

    Example
    Example
    cellValue         string(255)
    cellFormula       string(255)
    
    code   cellValue = MyExcel.ReadCell('B3')   cellFormula = MyExcel.ReadCell('B3', true)

    Redo

    Redo( ) ,byte,proc

    Description Example

    MyExcel.Redo()

    RenameWorksheet

    RenameWorksheet ( string pName ) ,byte,proc

    Description

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

    Example

    MyExcel.RenameWorksheet ('Sales')

    ReplaceText

    ReplaceText ( string pFindText, string pReplaceText, byte pReplaceAll=false ) ,byte,proc

    Description

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

    Example

    MyExcel.ReplaceText ('Cape', 'Soft', true)

    AddImage

    AddImage ( string pImageName) ,byte,proc

    Description

    Adds an image to the currently selected cell. You must first call select so that a cell is selected prior to calling this function

    Example

    MyExcel.AddImage ('C:\MyImages\MyImage.png')  !You must include the path to the image

    Save

    Save     ( ) ,byte,proc

    Description Example

    MyExcel.Save()

    SaveAs

    SaveAs (<string pFileName>, <long fileType>,<String pOpenPassword>, <string pEditPassword>)

    Description

    Saves the current workbook using the name provided in theFileName 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
    Parameter Description
    string pFileName (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.
    string pOpenPassword (optional)If this is set then this sets an Open password for the file. The user will need to enter this password in order to open the file.
    string pEditPassword (optional)If this is set then this sets an Edit password for the file. The user will need to enter this password in order to edit the file.
    NameValue Description
    oix:xlAddIn18Microsoft Excel 2007 Add-In
    oix:xlAddIn818Microsoft Excel 97-2003 Add-In
    oix:xlCSV6CSV
    oix:xlCSVMac22Macintosh CSV
    oix:xlCSVMSDOS24MSDOS CSV
    oix:xlCSVWindows23Windows CSV
    oix:xlCurrentPlatformText-4158Current Platform Text
    oix:xlDBF27DBF2
    oix:xlDBF38DBF3
    oix:xlDBF411DBF4
    oix:xlDIF9DIF
    oix:xlExcel1250Excel12
    oix:xlExcel216Excel2
    oix:xlExcel2FarEast27Excel2 FarEast
    oix:xlExcel329Excel3
    oix:xlExcel433Excel4
    oix:xlExcel4Workbook35Excel4 Workbook
    oix:xlExcel539Excel5
    oix:xlExcel739Excel7
    oix:xlExcel856Excel8
    oix:xlExcel979543Excel9795
    oix:xlHtml44HTML format
    oix:xlIntlAddIn26International Add-In
    oix:xlIntlMacro25International Macro
    oix:xlOpenXMLAddIn55Open XML Add-In
    oix:xlOpenXMLTemplate54Open XML Template
    oix:xlOpenXMLTemplateMacroEnabled53Open XML Template Macro Enabled
    oix:xlOpenXMLWorkbook51Open XML Workbook
    oix:xlOpenXMLWorkbookMacroEnabled52Open XML Workbook Macro Enabled
    oix:xlPDF57Save As PDF File
    oix:xlSYLK2SYLK
    oix:xlTemplate17Template
    oix:xlTemplate817Template 8
    oix:xlTextMac19Macintosh Text
    oix:xlTextMSDOS21MSDOS Text
    oix:xlTextPrinter36Printer Text
    oix:xlTextWindows20Windows Text
    oix:xlUnicodeText42Unicode Text
    oix:xlWebArchive45Web Archive
    oix:xlWJ2WD114WJ2WD1
    oix:xlWJ340WJ3
    oix:xlWJ3FJ341WJ3FJ3
    oix:xlWK15WK1
    oix:xlWK1ALL31WK1ALL
    oix:xlWK1FMT30WK1FMT
    oix:xlWK315WK3
    oix:xlWK3FM332WK3FM3
    oix:xlWK438WK4
    oix:xlWKS4Worksheet
    oix:xlWorkbookDefault51Workbook default
    oix:xlWorkbookNormal-4143Workbook normal
    oix:xlWorks2FarEast28Works2 FarEast
    oix:xlWQ134WQ1
    oix:xlXMLSpreadsheet46Spreadsheet
    Remarks

    Excel 2007 has a new default file type which is .XLSX. This format cannot be opened by previous versions of Excel. The fileFormat parameter can be used to save as a standard XLS file, which is compatible with Excel 97, 2000, XP and 2003 by passing oix:xlWorkbookNormal. Use oix:xlWorkbookDefault to allow Excel to save in the default format specified, which in Excel 2007 is always the new XLSX. The changes made to Excel 2007 result in unexpected behaviour if you assume a certain format when saving. If the incorrect file extension is specified the file will be saved in the default, or the specified format, but when opened it will display an error warning the user that the file extension is incorrect and that the file may be dangerous. If you choose to save the document as an Excel 97-2003 compatible XLS document by setting the fileFormat parameter to oix:xlWorkbookNormal, then depending on the content of the document Excel may display a compatibility warning dialog box (which may or may not be displayed in front of your application).

    Important:
    Excel 2007 allows the user to set the default format for saving documents (such as the new XLSX or old XLS format). This setting has no effect on the default format saved when calling OfficeInside via the COM interface, oix:xlWorkbookDefault will always save as a Excel 2007 .xlsx document!

    Important:
    If the document already exists then Excel will prompt the user to overwrite the existing document.

    Example
    Example
    ! Dangerous in Excel 2007 this may save as an xlsx file with an .xls extension
    ! which will display a warning when the document is opened, and may display
    ! a compatibility dialog when the document is saved in Excel 2007
    MyExcel1.SaveAs(' test.xls ')

    ! Save in the default format and allow Excel to set the correct extension
    MyExcel1.SaveAs('
    test', oix:xlWorkbookDefault)   

    ! Prompt the user
    MyExcel1.SaveAs()    

    ! Save as a "normal" workbook (.xls), may display a compatibility dialog in Excel 2007
    MyExcel1.SaveAs(LongPath() & '\' & Clip(excelFileName), oix:xlWorkbookNormal) 

    SelectCells

    SelectCells     ( string <pFromCell>, <string pToCell> ) ,byte,proc

    Description Example
    Example
    MyExcel.SelectCells ('B3')
    MyExcel.SelectCells ('B3', 'D12')
    MyExcel.SelectCells () ! Ctrl-A

    SelectColumns

    SelectColumns     ( string pFromColumn, <string pToColumn> ) ,byte,proc

    Description Example
    Example
    MyExcel.SelectColumns ('B')
    MyExcel.SelectColumns ('B', 'D')

    SelectRows

    SelectRows     ( string pFromRow, <string pToRow> ) ,byte,proc

    Description Example
    Example
    MyExcel.SelectRows (3)
    MyExcel.SelectRows (3, 8)

    SelectWorksheet

    SelectWorksheet     ( <string pSheetName>, long pSheetNumber=0 ) ,byte,proc

    Description Example
    Example
    MyExcel.SelectWorksheet ('Sheet2')
    MyExcel.SelectWorksheet ('My Sheet')
    MyExcel.SelectWorksheet ('', 2)

    SetCalculations

    SetCalculations (long calcType)

    Description

    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, so data can be written, and then any calculations needed can be performed by calling the Calculate method

    Parameters
    Parameter Description
    long calcType determines how Excel will handle inserted data. Can be one of three values:
    • oix:CalculationAutomatic: The default, performs calculations to ensure all cells are up to date every time data is inserted. Only recommended if the user is interacting with the data and calculations need to be done in real time.
    • oix:CalculationManual: The recommended option when doing bulk inserts or building Excel worksheets without user interaction. Provides a dramatic performance increase.
    • oix:CalculationSemiautomatic: Turns off most calculation.
    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.

    Example
    Example
    ExcelDoc.SetCalculations(oix:CalculationManual)

    ! document being updated

    ExcelDoc.SetScreenUpdating(
    false)
    ! Write the required data to the worksheet here...

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

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

    SetActivePrinter

    SetActivePrinter     ( string pPrinter ) ,byte,proc

    Description Example
    Example
    MyExcel.SetActivePrinter ('CutePDF Writer on CPW2:')

    SetScreenUpdating

    SetScreenUpdating (long updateScreen)

    Description

    This method allows screen updating (redrawing) to be turned off and on. Turn screen updating off can provide a small performance improvement for batch inserts etc. It is only recommended in cases where the user does not have to see or interact with the document. The same method can be used with the oiWord object to slightly improve performance. For a large performance increase when doing bulk updates or insert see the SetCalculations method.

    Parameters
    Parameter Description
    long updateScreen Set this to 1(true) to turn screen updating on, and to zero (false) to turn screen updating off.
    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.

    Example
    Example
    ExcelDoc.SetCalculations(oix:CalculationManual)
    ! Screen updating can be turned off to provide a small performance boost

    ! in cases where the window is hidden or the user does not need to see the
    ! document being updated

    ExcelDoc.SetScreenUpdating(false)

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


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

    ! Automatic calculation can be optionally turned back on

    ExcelDoc.SetCalculations(oix:CalculationAutomatic)

    SetCellAlignment

    SetCellAlignment (long option, string value, <string fromCell>, <string toCell>), long, proc

    Description

    Sets the alignment in the cells identified by the pFromCell and pToCell parameters. If these parameters are omitted then this applies to the current selection (so you can select a range of cells and then set the alignment without passing the fromCell and toCell parameters. This method allows the horizontal and vertical alignment to be set, as well as text wrapping and text orientation (the angle that the text in the cell is drawn at). The option pparameter specifies which property to set and the value parameter specifies what to see it to. See below for a list of equates and values that may be passed.

    Parameters
    Parameter Description
    long optionSpecifies which option is being set. This parameter determines what the method actually does. Can have one of the following values:
    • oix:WrapText - Sets whether text wrapping is enabled for the cells. If text wrapping is on then text that would not fit in the cell is wrapped onto the next line. The value parameter determines whether text wrapping is turned on (1) or off (0) for the cells.
    • oix:HorizontalAlignment - set the horizontal alignment for the currently selected cells, or the range of select of cells if fromCell and toCell are passed.
      Supported values for the value parameters are:
      • oix:xlCenter - aligns the contents of the cell in the centre of the cell
      • oix:xlLeft - aligns the cell contents left
      • oix:xlRight - aligns the cell contents right
      • oix:xlDistributed - distributes (spreads) the contents across the cell
      • oix:xlJustify - justifies the contents of the cell (each line is spread to the width of the cell)
    • oix:VerticalAlignment - sets the vertical alignment for the cell. By default, Excel aligns text vertically on the bottom of a cell.
      The value parameter may be one of the following:
      • oix:xlTop - aligns the cell contents with the top of the cell
      • oix:xlBottom - aligns the cell contents with the top of the cell
      •  oix:xlCenter - aligns the cell contents in the centre of the cell
      • oix:xlDistributed - distributes the cell contents within the cell
      • oix:xlJustify - similar to Distributed, spreads the contents out from the top to the bottom of the cell
    • oix:Indent - Indents cell contents from any edge of the cell, the value passed is the amount to indent by. The unit used is the width of a single character. So an indent to 3 would indent by the width of 3 characters.
    • oix:ShrinkToFit -Reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically if you change the column width. The applied font size is not changed.
    • oix:Orientation - Change the orientation of text in selected cells. Rotation options may not be available if other alignment options are selected. This rotates the text in the selected cells.
      The value passed can be an integer from -90 to 90 degrees, or one of the following values:
      • oix:Downward
      • oix:Horizontal
      • oix:Upward
      • oix:Vertical
    • oix:TextDirection - sets the reading order of the cell. Can be used for languages such as Arabic and Hebrew that are read from right to left.
      The value parameter may be one of the following:
      • oix:Context - allow Excel to use the default based on the context
      • oix:LTR - use Left to Right reading order, the standard for most languages
      • oix:RTL - use Right to Left reading order for Hebrew, Arabic etc.
    string valueThe value to set the specified option to. The values for each option are listed under that option above.
    string fromCell An optional parameter specifying the cell or start of a range of cells to apply the option to. The fromCell and toCell parameters are optional, and if they are not passed then the current selection in the document is used. If a single cell is passed in the fromCell parameter and toCell is not passed then only that single cell is affected. If both fromCell and toCell are specified they are used to determine the rectangular region of cells that are affected. Both fromCell and toCell must specify an Excel cell name. For example A1, B22, BA2 etc. You can use the ColumnName() method to convert a column number into the Excel name for that column.
    string toCellAn optional parameter specifying the cell or start of a range of cells to apply the option to. See the description for the fromCell parameter above for more information.
    Return Values

    The method returns 1 if successful and zero if it failed. If an error occurs the ErrorTrap() method is called with the description of the error. If debug output is turned on (for example using a command line parameter) then ErrorTrap() will log the error to the system debug log (viewable using DebugView from www.sysinternals.com). See the debugging section for more information.

    Example
    Example
    ! Align the current selection right
    MyExcel.SetCellAlignment(oix:HorizontalAlignment, oix:xlRight)
    ! Turn text wrapping on for a range of cells
    MyExcel.SetCellAlignment(oix:WrapText,
    true, 'B2', 'C4 ')
    ! Turn text wrapping off for a range of cells
    MyExcel.SetCellAlignment(oix:WrapText,
    false, 'B2', 'C4')
    ! Turn text wrapping off for a range of cells
    MyExcel.SetCellAlignment(oix:WrapText,
    false, 'B2', 'C4')

    SetCellBackgroundColor

    SetCellBackgroundColor     ( long olor, <string pFromCell>, <string pToCell> ) ,byte,proc

    Description

    Changes the background color for a cell, or a range of cells.

    Parameters Return Value

    Returns True (1) if no problems were experienced and False (0) if the method fails.

    Example
    Example
    MyExcel.SetCellBackgroundColor (oix:ColorYellow)
    MyExcel.SetCellBackgroundColor (oix:ColorYellow, '
    D5')
    MyExcel.SetCellBackgroundColor (oix:ColorRed, '
    B2', ' C5')
    MyExcel.SetCellBackgroundColor (oix:Color55, '
    B2', 'C5')
    Remarks

    Important: 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). If you do that, then any color parameter that you pass in needs to be the color swatch number.

    See Also

    Color Management in Excel and Cell Formatting

    Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

    Properties: interpretClarionColorEquates

    SetCellBorders

    SetCellBorders     ( string pFromCell, string pToCell, long pBorder, long pLineStyle, <long pWeight>, <long pColorIndex> ) ,byte,proc

    Description Example
    Example
    MyExcel.SetCellBorders ('D5', '', oix:BorderEdgeBottom, oix:LineStyleDouble)
    MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeBottom, oix:LineStyleContinuous)
    MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeTop, oix:LineStyleContinuous)
    MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeLeft, oix:LineStyleContinuous)
    MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeRight, oix:LineStyleContinuous)
    MyExcel.SetCellBorders ('B2', 'C6', oix:BorderInsideHorizontal, oix:LineStyleContinuous)
    MyExcel.SetCellBorders ('B2', 'C6', oix:BorderInsideVertical, oix:LineStyleContinuous)
    MyExcel.SetCellBorders ('D5', '', oix:BorderEdgeBottom, oix:LineStyleNone, oix:BorderWeightThick, color:red)

    SetCellFontColor

    SetCellFontColor     ( long pFontColor, <string pFromCell>, <string pToCell> ) ,byte,proc

    Description

    Sets the Font Color inside a cell / range of cells.

    Parameters
    Parameter Description
    long pFontColor The font color for the cells. The type of the value passed is dependant on the .interpretClarionColorEquates property. If this is set to True, then the passed value is a Clarion color equate, such as Color:Yellow or Color:Red. If this is set to False (0), then the value passed is a position in the Excel palette. See the Remarks and See Also sections for palette and color management information.
    string pFromCell (optional) If specified this is the starting cell of the selection to set the font color for. If this is not passed the current selection is used. If pFromCell is passed and pToCell is omitted, then the font color is set for the single cell.
    string pToCell (optional) If specified this determines the end of the selection to set the font color for (all cells from pFromCell to pToCell will be modified). If both pFromCell and pToCell are omitted, then the currently selected cells are modified. If only pFromCell is passed, then just that cell is modified.
    Return Value

    Returns true (1) if no problems were experienced.

    Remarks

    Important
    : 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). If you do that, then any color parameter that you pass in needs to be the color swatch number.

    See Also


    Color Management in Excel and Cell Formatting

    Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

    Properties: interpretClarionColorEquates

    Example
    Example
    MyExcel.SetCellFontColor (color:red) ! currently selected cell/range
    MyExcel.SetCellFontColor (
    color:red, 'A1') ! cell A1
    MyExcel.SetCellFontColor (
    color:red, 'A1', 'C3') ! range A1:C3

    SetCellFontName

    SetCellFontName     ( string pFontName, <string pFromCell>, <string pToCell> ) ,byte,proc

    Description

    Example
    Example
    MyExcel.SetCellFontName ('Verdana') ! currently selected cell
    MyExcel.SetCellFontName (
    'Arial', 'A1') ! cell A1
    MyExcel.SetCellFontName (
    'Verdana', 'A1', 'C3') ! cell range A1:C3

    SetCellFontSize

    SetCellFontSize     ( long pFontSize, <string pFromCell>, <string pToCell> ) ,byte,proc

    Description Example
    Example
    MyExcel.SetCellFontSize (16) ! currently selected cell / range
    MyExcel.SetCellFontSize (20, 'A1') ! cell A1
    MyExcel.SetCellFontSize (16, 'A1', 'C3') ! range A1:C3

    SetCellFontStyle

    SetCellFontStyle     ( string pFontStyle, <string pFromCell>, <string pToCell> ) ,byte,proc

    Description Example
    Example
    MyExcel.SetCellFontStyle ('Bold') ! currently selected cell/range
    MyExcel.SetCellFontStyle ('Bold Italic', 'A1') ! cell A1
    MyExcel.SetCellFontStyle ('Bold', 'A1', 'C3') ! range A1:C3

    SetCellFontUnderline

    SetCellFontUnderline     ( long pUnderlineStyle, <string pFromCell>, <string pToCell> ),byte,proc

    Description Example
    Example
    MyExcel.SetCellFontUnderline (oix:UnderlineStyleDouble)
    MyExcel.SetCellFontUnderline (oix:UnderlineStyleNone, 'A1')
    MyExcel.SetCellFontUnderline (oix:UnderlineStyleDouble, 'A1', 'C3')

    SetCellTextFormat

    SetCellTextFormat (string pPicture, <string pFromCell>, <string pToCell>)

    Description

    Sets the format of the text for the currently selected cells, or optionally for the cell range specified by the pFromCell and pToCell parameters.

    Parameters
    Parameter Description
    string pPicture Must be a clarion picture token (see the Clarion docs for picture tokens): Currently supported (at the time of writing) Time, Date, Numeric and Currency and String pictures.

    Special Overrides:
    Currency - uses the predefined Excel Currency picture.
    string pFromCell and string pToCell used to specify the range which is going to be formatted. If you only want to format a single cell either pass the same address in both parameters, or pass the second parameter as nothing ( ' ' ). e.g. ('A1', ' A1'...) or ('A1', '', ...). To format the currently selected cell, omit these parameters

    SetCellNumberFormat

    (Rather see SetCellTextFormat for formatting excel cells using Clarion picture tokens)

    SetCellNumberFormat (long pFormatType, long pSymbol=-1, long pDecimalPlaces=-1, long pSubType=-1, <string pFromCell>, <string pToCell>)

    Description

    Sets the format for the currently selected cells, or optionally for the cell range specified by the pFromCell and pToCell parameters. This method is equivalent to right clicking on the cells and choosing the Format option in Excel.

    Parameters
    Parameter Description
    string pFromCell and string pToCell used to specify the range which is going to be formatted. If you only want to format a single cell either pass the same address in both parameters, or pass the second parameter as nothing ( ' ' ) e.g. ('A1', 'A1'...) or ('A1', '', ...)
    long pFormatType Must be one of the following equates:
    • oix:NumberFormatGeneral
    • oix:NumberFormatNumber
    • oix:NumberFormatCurrency
    • oix:NumberFormatDate
    • oix:NumberFormatTime
    • oix:NumberFormatPercentage
    • oix:NumberFormatScientific
    • oix:NumberFormatText
    long pSymbol Only applies if yourFormatType is Currency or Accounting (see list above). This is used to set the currency symbol displayed with your numbers. pSymbol can be one of the following: ( if your currency is not listed here please see the Note section below for using any format supported by Excel)
    • oix:CurrencySymbolNone
    • oix:CurrencySymbolDefault
    • oix:CurrencySymbolUKPound
    • oix:CurrencySymbolUSDollar
    • oix:CurrencySymbolSARand
    long pDecimalPlaces Only applies if your formatType is Number, Currency, Accounting, Percentage or Scientific. As the name implies, it is used to set the number of decimal places.
    long pSubType Only applies if your FormatType is Date, Time, Fraction, Special or Custom. Implementation is as follows:
    If FormatType is Date, then pSubType can be 1 through to 13 - examples of these date formats are:
    1. 9/7
    2. 9/7/03
    3. 09/07/03
    4. 7-Sep
    5. 7-Sep-03
    6. 07-Sep-03
    7. Sep-03
    8. September-03
    9. September 7, 2003
    10. S
    11. S-03
    12. 9/7/2003
    13. 7-Sep-2003
    If FormatType is Time, then pSubType can be 1 through 6 - examples of these time formats are:
    1. 13:30
    2. 1:30 PM
    3. 13:30:55
    4. 1:30:55 PM
    5. 30:55.7
    6. 37:30:55
    Example
    Example
    MyExcel.WriteToCell ('5.98', 'A3')
    MyExcel.SetCellNumberFormat(oix:NumberFormatCurrency, oix:CurrencySymbolUnitedKingdom, 2, , '
    A3')

    Notes: Additional Formats, Fractions, Custom Pictures and Currencies.
    If formatType is Fraction, Special or Custom, then this method does not support it, however you can call an internal method to set the value to any value supported by Excel. You need to call the following method:

        oiExcel._WrapperRange (16, '', '', formatString, '', '', '')

    The formatString parameter contains a picture for the string and the number of decimal places. This is a standard Excel format string, and exactly the
    same string that works in Excel will work in this string:

        formatString = '#,##0.00'            ! no currency, 2 decimal places
        formatString = '
    [$�-809]#,##0.000'   ! UK Pound, 3 decimal places
        formatString = '
    [$$-409]#,##0.00'    ! US Dollar, 3 decimal places
        formatString = '
    [$$-2409]# ##0.00'   ! Caribbean dollar, 2 decimal spaces

    You can even use formats such as:

      formatString = '$#,##0;[Red]$#,##0'

    Which uses the dollar sign and comma separator for thousands, and no decimal places. The second part after the semi colon will cause negative numbers to
    be displayed in red (without a minus sign, sign it isn't explicitly stated in this case).

    In order to get the format strings for all the currencies (or anything else) that you need, open Excel, right click on a cell, and choose Format from the popup menu. In the dialog box displayed select the currency format that you want to use. Once you have the currency format select, the number of decimal places etc. change from Currency to Custom in the Category list on the left. It will display the picture used for this format. For example the following option is selected from the dropdown:

    $ English (Caribbean)

    Then and enter 2 decimal spaces, and switch from Currency to Custom in the Category list. The following picture is display: [$$-2409]# ##0.00

    The value can be passed as the formatString parameter to the _WrapperRange method.
    See Also

    SetCellTextFormat

    SetCellProtection

    SetCellProtection( byte pLocked, byte pFormulaHidden, <string pFromCell>, <string pToCell> ) , byte, proc

    Description Example
    Example
    MyExcel.SetCellProtection (true, false) ! currently selected cell / range
    MyExcel.SetCellProtection (true, true, 'A1') ! cell A1 MyExcel.SetCellProtection (false, false, 'A1', 'C4') ! range A1:C4

    SetColumnWidth

    SetColumnWidth ( string FromColumn, <string ToColumn>, <string pWidth> ) ,byte, proc

    Description Example
    Example
    MyExcel.SetColumnWidth ('A', 'C', '15.71')
    MyExcel.SetColumnWidth (
    'A', , '18.45')

    SetPageView

    SetPageView ( byte pPageView ) ,byte,proc

    Description Example

    MyExcel.SetPageView (oix:PageBreakPreview)

    SetPaletteColor

    SetPaletteColor (byte pSwatch, long pColor ), byte, proc

    Description

    The colors in Excel are stored in a palette. Each block (or swatch) in the palette stores a particular color, and colors are referenced by their position in the palette, from 1 to 40. The color swatches are the "blocks" of color you see when you click the "Font Color" droplist on the Excel toolbar. This method sets the color for a particular position in the palette (swatch). Only colors in the palette can be used in the Excel worksheet, and changing a palette entry will change the color of anything that swatch was used for.

    Please see the section Color Management in Excel and Cell Formattting (recommended reading).

    Parameter
    Parameter Description
    byte pSwatch The position in the palette to set
    long pColor The color to set the specified color entry to. There are two ways of passing colors that are supported of OfficeInside. You can pass clarion color equates such as color:red, or you can pass the actual color values using a variable, or a numeric value such as 0FF0C0Ah. By default only Clarion color equates may be used. If you wish to pass the actual color values you must set the oiExcel.InterpretClarionColorEquates property to zero. It is set to 1 (true) by default.
    Return Values

    Returns 1 if the palette was successfully set and zero if the method failed. If an error occurs the ErrorTrap() method will be called with a string containing the error description.

    Notes

    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.

    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.

    Example

    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 find 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 
        code! 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 3, which we just set
    MyExcel.SetCellBackgroundColor(3, 'B2') 

    See Also

    Color Management in Excel and Cell Formatting

    Methods:GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

    Properties: interpretClarionColorEquates

    SetPrintArea

    SetPrintArea ( string fromCell, string ToCell ) ,byte,proc

    Description Example

    MyExcel.SetPrintArea('A3', 'B5')

    SetRowHeight

    SetRowHeight ( string pFromRow, <string pToRow>, <string pHeight> ) , byte, proc

    Description Example

    Example
    MyExcel.SetRowHeight(5,8,'20.75')
    MyExcel.SetRowHeight(9,,'20.75')

    ShowFormulaBar

    ShowFormulaBar ( ) , byte, proc

    Description Example

    MyExcel.ShowFormulaBar()

    ShowStatusBar

    ShowStatusBar ( ) , byte, proc

    Description Example

    MyExcel.ShowStatusBar()

    ShowToolbar

    ShowToolbar ( long pToolbar ) , byte, proc

    Description Example

    MyExcel.ShowToolbar(oix:ToolbarStandard)

    TakeEvent

    TakeEvent ( string pEventString1, string pEventString2, long pEventNumber=0, byte pEventType=0, byte pEventStatus=0 )

    Description

    The TakeEvent method is not a method that you (typically) would call yourself. It is a virtual method that fires, which you can write code into in order to deal with certain events. See the offdemo.app (Excel Automation Example) example to see how we make use of this method.

    TakeImportRecord

    TakeImportRecord (long recordNumber, excelImportQType cellsQ)

    Note: We recommend using the oiExcelImpEx class for import and export to and from Excel. This class provides fully automated import and export to and from queues, groups, files (tables) and views. It allows columns and fields to be mapped in any order.

    This callback method is called for each row (record) when importing a file from Excel using the ExcelImport method. This allows each row to be processed. The queue contains the value of each cell in the row. The maximum length of the data for any cell is 1024 characters. Each cell is stored in the cellsQ.cell queue property passed. You need to add code to this method in order to process imported record. See the Return Values section below for the values that you should return.

    Parameter
    Parameter Description
    long recordNumberThe record number being processed.
    excelImportQType cellsQ A queue that contains the values of all cells (fields) in the current row (record). The queue has the following structure:excelImportQType queue cell string(_oit:LargeStringSize) end
    The _oit:LargeStringSize equate defaults to 1024, which means that each cell may only contain up to 1024 characters.
    Return Values

    Return level:benign (zero) to indicate that processing should continue, and level:fatal to indicate that processing should halt (such as if the record is out of range).

    Example

    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 and assign them to specific fields rather than using a loop to do this dynamically.

    This method is normally generated for you when you add an Excel object to the procedure. You then add code to process each record as demonstrated in the example below.
    Example
    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)
            sourceField &= What(cellsQ, i)
            if Who(MyFile.Record, i) <> ''           ! If the field exists in the file assign to it.
                destField &= What(MyFile.Record, i)
                destField = sourceField
                if Access.MyFile.Insert()
                    return level:fatal               ! Error adding a record, cancel import
                end
            end
        end
        return level:benign                          ! continue importing
    ! Parent Call                                                                  
    ReturnValue = parent.TakeImportRecord(recordNumber, cellsQ)                    
    ! [Priority 5000]                                                              


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

    Undo

    Undo ( ) , byte, proc

    Description Example

    MyExcel.Undo()

    UnmergeCells

    UnmergeCells ( string pFromCell, string pToCell ) , byte, proc

    Description

    Use the Use the MergeCells method to merge cells, and use this method to unmerge them.

    Example

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

    UnprotectWorkbook

    UnprotectWorkbook ( string pPassword ) , byte, proc

    Description

    See the ProtectWorkbook method.

    Example

    MyExcel.UnprotectWorkbook('MyPassword')

    UnprotectWorksheet

    UnprotectWorksheet ( string pPassword ) , byte, proc

    Description

    See the ProtectWorksheet method.

    Example

    MyExcel.UnprotectWorksheet('MyPassword')

    Update

    Update ( byte pOption, <string pValue> ) , byte, proc

    Description Example

    TempByte = MyExcel.Update (oix:WindowState, oix:MinimizeWindow)

    WriteToCell

    WriteToCell ( string pText, <string pCellAddress> ) , byte, proc

    Description Example
    Example
    MyExcel.WriteToCell('Test')
    MyExcel.WriteToCell('Test', 'B3')
    This Example loops through a table and writes each row in the table to a row in Excel. The example only writes the first two fields, but each additional field is done in the same manner.

    currentRow         long
      code
        MyExcel.NewWorkbook()
        currentRow = 1

        Set(MyFile)
        loop until Access:MyFile.Next()
            MyExcel.WriteToCell(MyFile.Field1, clip(MyExcel.ColumnName(1)) & currentRow)
            MyExcel.WriteToCell(MyFile.Field2, clip(MyExcel.ColumnName(2)) & currentRow)
            MyExcel.WriteToCell(day(LC:LC_Date)&'.'&month(LC:LC_Date)&'.'&year(LC:LC_Date), clip(MyExcel.ColumnName(3)) & currentRow)  !This will enter the date in the format that excel understands.
            currentRow += 1
        end
        MyExcel.SaveAs(fileName)
    The GetColumnAddressFromColumnNumber () method called above converts a number to the column name in Excel, which are named alphabetically.

    To format the text in the cell, use the SetCellTextFormat method.

    You could get fancier and use Who(), What() and Where() to make it this process generic, regardless of the table being exported. This approach will be added to a new Export class and template in the near future.

    Hint: You can also use SendTo to export browses and lists to Excel, Word, PDF, Email etc. with all their formatting etc. SendTo uses Office Inside to export to Excel and Word, so it is a good example, and actually contains some very useful Office Inside code (SendTo ships are pure Clarion source code).

    WriteFormulaToCell

    WriteFormulaToCell ( string pFormula, <string pCellAddress> ) , byte, proc

    Description Example
    Example
    MyExcel.WriteFormulaToCell('=SUM(A6:A12)')
    MyExcel.WriteFormulaToCell('=SUM(A6:A12)', 'B3')
    The GetColumnAddressFromColumnNumber () method called above converts a number to the column name in Excel, which are named alphabetically.

    To format the text in the cell, use the SetCellTextFormat method.

    Hint: You can also use SendTo to export browses and lists to Excel, Word, PDF, Email etc. with all their formatting etc. SendTo uses Office Inside to export to Excel and Word, so it is a good example, and actually contains some very useful Office Inside code (SendTo ships are pure Clarion source code).

    Properties

    This section lists the properties of the Excel class and their use. This section is a work in Progress and will be fleshed out in the next few releases.
    oiExcel Class Properties
    New Properties
    (also listed below)
    dontCloseOnKill Leaves Excel open when the Kill method is called.
    oiExcel Propeties
    dontCloseOnKill
    interpretClarionColorEquates

    dontCloseOnKill

    dontCloseOnKill long

    Setting dontCloseOnKill to a non zero value will cause the instance of Excel to remain open when the Kill method is called. Any documents that have been created or opened will be left open etc. The state of the Excel window will be set to the default state for a newly opened Excel window (this behaviour is determined by Office). This property is common to all the Office objects, and be used with Word etc.

    The property should be set after the call to the Init() method, and before the call to Kill().

    Example
        ! Clean up the object and COM interface, but leave Excel open:
        myExcel.dontCloseOnKill = 1
        myExcel.Kill()

    interpretClarionColorEquates

    interpretClarionColorEquates long

    This property is set to 1 by default and will automatically convert Clarion color equates (such as color:red, color:black and so on) to Excel palette colors. In order to use RGB colors and Excel palette colors (swatches) you should set this property to zero. See the GetPaletteColor method for more information on colors and Excel. The Color Equates section shows the default Excel colors and a set of equates that can be used for them.

    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.

    Important
    : 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 theInterpretClarionColorEquates property to false (after calling oiExcel.Init, which will set it to true). If you do that, then any color parameter that you pass in needs to be the color swatch number, as explained in the next point.

    Note: 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.

    Example
    Example 1
    curColor         long
      code
        ! Let the user pick a color (RGB)
        if ColorDialog('Select a color', curColor)
       
            MyExcel.SetPaletteColor(33, curColor)
          ! Set swatch 33 to this RGB color

          MyExcel.InterpretClarionColorEquates = false
            MyExcel.SetCellBackgroundColor(33, 'B2')
        end
    Example 2
         TempLong = 0FFFF00h                       ! Use an RGB color (yellow)
          MyExcel.SetPaletteColor(33, curColor)
          MyExcel.InterpretClarionColorEquates = false
          ! Set cell B2 to the color in swatch 33, which we just set
          MyExcel.SetCellBackgroundColor (33, 'B2')
    See also

    SetPaletteColor, GetPaletteColor, SetCellBackgroundColor, Excel Color Equates.

    The oiExcel Class - Equates

    Toolbar Equates     ( used in the ShowToolbar and HideToolbar methods )
    oix:ToolbarWorksheetMenuBar
    oix:ToolbarWorksheetMenuBar
    oix:ToolbarChartMenuBar
    oix:ToolbarStandard
    oix:ToolbarFormatting
    oix:ToolbarPivotTable
    oix:ToolbarChart
    oix:ToolbarReviewing
    oix:ToolbarForms
    oix:ToolbarStopRecording
    oix:ToolbarExternalData
    oix:ToolbarAuditing
    oix:ToolbarFullScreen
    oix:ToolbarCircularReference
    oix:ToolbarVisualBasic
    oix:ToolbarWeb
    oix:ToolbarControlToolbox
    oix:ToolbarExitDesignMode
    oix:ToolbarRefresh
    oix:ToolbarDrawing
    oix:ToolbarQueryAndPivot
    oix:ToolbarPivotChartMenu
    oix:ToolbarWorkbookTabs
    oix:ToolbarCell
    oix:ToolbarColumn
    oix:ToolbarRow
    oix:ToolbarCell2
    oix:ToolbarColumn2
    oix:ToolbarRow2
    oix:ToolbarPly
    oix:ToolbarXLMCell
    oix:ToolbarDocument
    oix:ToolbarDesktop
    oix:ToolbarNondefaultDragAndDrop
    oix:ToolbarAutoFill
    oix:ToolbarButton
    oix:ToolbarDialog
    oix:ToolbarSeries
    oix:ToolbarPlotArea
    oix:ToolbarFloorAndWalls
    oix:ToolbarTrendline
    oix:ToolbarChart2
    oix:ToolbarFormatDataSeries
    oix:ToolbarFormatAxis
    oix:ToolbarFormatLegendEntry
    oix:ToolbarFormulaBar
    oix:ToolbarPivotTableContextMenu
    oix:ToolbarQuery
    oix:ToolbarQueryLayout
    oix:ToolbarAutoCalculate
    oix:ToolbarObjectPlot
    oix:ToolbarTitleBarCharting
    oix:ToolbarLayout
    oix:ToolbarPivotChartPopup
    oix:ToolbarPhoneticInformation
    oix:ToolbarWordArt
    oix:ToolbarPicture
    oix:ToolbarShadowSettings
    oix:Toolbar3DSettings
    oix:ToolbarBorders
    oix:ToolbarChartType
    oix:ToolbarPattern
    oix:ToolbarFontColor
    oix:ToolbarFillColor
    oix:ToolbarLineColor
    oix:ToolbarOrder
    oix:ToolbarNudge
    oix:ToolbarAlignOrDistribute
    oix:ToolbarRotateOrFlip
    oix:ToolbarLines
    oix:ToolbarConnectors
    oix:ToolbarAutoShapes
    oix:ToolbarCallouts
    oix:ToolbarFlowchart
    oix:ToolbarBlockArrows
    oix:ToolbarStarsAndBanners
    oix:ToolbarBasicShapes
    oix:ToolbarShapes
    oix:ToolbarInactiveChart
    oix:ToolbarExcelControl
    oix:ToolbarCurve
    oix:ToolbarCurveNode
    oix:ToolbarCurveSegment
    oix:ToolbarPicturesContextMenu
    oix:ToolbarOLEObject
    oix:ToolbarActiveXControl
    oix:ToolbarWordArtContextMenu
    oix:ToolbarRotateMode
    oix:ToolbarConnector
    oix:ToolbarScriptAnchorPopup
    oix:ToolbarAddCommand
    oix:ToolbarBuiltinMenus
    oix:ToolbarSystem

    Color Equates

    Used in the SetCellBackgroundColor methods

    Note: It appears that MS have changed some of the equates. The below equates should be tested first prior to utilizing to get the actual colour that is displayed.

    Below is a picture of the 56 default colors. Office Inside has 56 equates representing these colors, which are named as follows:

    oix:Color1, oix:Color2, oix:Color3, ..., oix:Color55, oix:Color56

    We also have synonyms for the first 8 colors (equates), as follows:

    oix:ColorBlack ( same as oix:Color1)
    oix:ColorWhite ( same as oix:Color2)
    oix:ColorRed ( same as oix:Color3 )
    oix:ColorGreen ( same as oix:Color4)
    oix:ColorBlue ( same as oix:Color5 )
    oix:ColorYellow ( same as oix:Color6)
    oix:ColorMagenta ( same as oix:Color7)
    oix:ColorCyan ( same as oix:Color8 )

    Cell Border Equates

    Used in the SetCellBorders method

    oix:BorderInsideHorizontal
    oix:BorderInsideVertical
    oix:BorderDiagonalDown
    oix:BorderDiagonalUp
    oix:BorderEdgeBottom
    oix:BorderEdgeLeft
    oix:BorderEdgeRight
    oix:BorderEdgeTop

    Line Styles

    Used in the SetCellBorders method

    oix:LineStyleContinuous
    oix:LineStyleDash
    oix:LineStyleDashDot
    oix:LineStyleDashDotDot
    oix:LineStyleDot
    oix:LineStyleDouble
    oix:LineStyleSlantDashDot
    oix:LineStyleNone

    Border Weights

    Used in the SetCellBorders method oix:BorderWeightHairline
    oix:BorderWeightMedium
    oix:BorderWeightThick
    oix:BorderWeightThin

    Underline Styles

    Used in the SetCellFontUnderline method oix:UnderlineStyleDouble
    oix:UnderlineStyleDoubleAccounting
    oix:UnderlineStyleNone
    oix:UnderlineStyleSingle
    oix:UnderlineStyleSingleAccounting

    GetInfo / Update Equates

    Used in the GetInfo and Update methods
    Equate:
     GetInfo? Update? Description (To Do)
    oix:Left  
    oix:Top  
    oix:Width  
    oix:WindowState  
    oix:ApplicationVisible  

    oiExcelImpEx Class

    This class is used to Import from and Export to Excel in a generic way. There are a number of useful Methods and Properties that will assist you in doing a number of things that are reasonably consistent with the xFiles architecture. This class is derived from the oiExcel class, so all Methods and Properties that are applicable to the oiExcel class, are also applicable to the oiExcelImpEx class.

    Useful Properties

    This is not an exhaustive list, but rather a list of properties that are most likely to be used.
    oiExcelImpEx Class Properties
    startRow The row number of the excel spreadsheet to start at for the import/export.
    StartCol The Column number from which to start importing/exporting (not applicable when column mapping is used)
    ColumnHeadersFromFields When exporting, if set to one of the equates below, exports the StartRow with the field names in the columns
    oix:LowerCase - creates the field names in lower case
    oix:UpperCase - creates the field names in upper case
    oix:CaseAsIs - creates the field names as is
    ProgressControl The progress control on the clarion window to update periodically
    numRows The number of rows to import/export from/to the excel file.
    numCols The number of columns to import/export from/to the excel file.
    commitRecords The number of records to insert before committing (file driver dependent)
    openAfterSave Open the Excel file after exporting
    RowsDone Set after the import/export. Indicates the number of rows imported/exported
    ColsDone Set after the import/export. Indicates the number of columns imported/exported
    KillWhenDone If you would like to only use the import/export once in this procedure, you can set the KillWhenDone flag. By default this is off so that you can re-do the load/save multiple times.
    Example:

    MyExcel.StartRow = 2
    MyExcel.ProgressControl = ?ProgressControl

    Useful Methods

    This is not an exhaustive list, but rather a list of methods that are most likely to be used.
    oiExcelImpEx Class Methods
    Save Exports the data to the excel file.
    Load Imports the data from an excel file into the data structure.
    ValidateRecord Useful if you want to filter out records immediately before importing/exporting that record (during the import process).
    IgnoreField Adds a field to the internal ignored queue, so that the particular data field is omitted from the exporting/import.
    ClearIgnored Clear the internal IgnoreFields queue.
    IsIgnored Check if a field is going to be ignored or not.
    MapFieldToColumn Creates an entry in the ColMap internal queue to map a field to a particular column.
    PrimeRecord This method primes the fields immediately prior to inserting the record. You can override field values here.

    Save

    Save(*File p_File, string p_FileName), long
    Save(*Group p_Group, string p_FileName), long
    Save(*Queue p_Queue, string p_FileName), long

    Description Example

       !Derived code
    MyExcel.Save(MyFile,'MyExcelFile.xlsx')

    Load

    Load(*File p_File, string p_FileName), long
    Load(*Group p_Group, string p_FileName), long
    Load(*Queue p_Queue, string p_FileName), long

    Description Example

       !Derived code
    MyExcel.Load(MyFile,'MyExcelFile.xlsx')

    ValidateRecord

    ValidateRecord(), long

    Description Example

       !Derived code
    MyExcel.ValidateRecord  procedure ()
      code
        if SomeCondition() then
          return Record:filtered
        else
          return Record:OK
        end

    IgnoreField

    IgnoreField(long fieldNum)

    Description Parameters Example

    MyExcel.IgnoredField(1)    !Ignores field number 1.

    ClearIgnored

    ClearIgnored()

    Description Example

    MyExcel.ClearIgnored()

    IsIgnored

    IsIgnored(long fieldpos), long

    Description Parameters Example

    TempByte = MyExcel.IsIgnored(1)

    MapFieldToColumn

    MapFieldToColumn(long fieldPos, long colPos), long, proc

    Description Example

    TempByte = MyExcel.MapFieldToColumn(1,2)    !Map Field 1 to Column 2
    TempByte = MyExcel.MapFieldToColumn(2,1) !We must map field 2 now as well as all other fields that must be used.

    PrimeRecord

    PrimeRecord()

    Description Example

    MyAutoIncVal            long

      code
       set(FIL:MyAutoIncKey)
       previous(MyFile)
       if not errorcode()
         MyAutoIncVal            = MyPre:AutoInc + 1
       else
         MyAutoIncVal            = 1
       end
       MyExcelImport.load(MyFile,'MyExcelFile.xlsx')

    MyExcelImport.PrimeRecord        procedure
      code
         parent.PrimeRecord()
         MyPre:AutoInc = MyAutoIncVal           
         MyAutoIncVal            += 1
      

    Useful References

    Limitations in Excel, Workarounds and Version Differences.

    For versions of Office prior to 2007 there is a limit of 256 columns and 64K rows. For Office 2007 the limit is 1,048,576 rows by 16,384 columns. A full list of limit changes is below.

    The rows and columns limit is not per worksheet, but rather a per workbook limit. In versions prior to Excel 2007, in order to support more than 64K rows split the data into multiple workbooks rather than create new sheets, which is clunky, but workable.

    Of course in Excel 2007 this limit isn't much of an issue, as it supports 1 million rows by 16K columns. Excel 2007 also contains optimisations to speed up calculations when using new dual and multi core CPUs.

    The total number of available columns in Excel
    Old Limit: 256  (2^8)
    New Limit: 16k  (2^14)

    The total number of available rows in Excel
    Old Limit: 64k  (2^16)
    New Limit: 1M  (2^20)

    Total amount of PC memory that Excel can use
    Old Limit: 1GB
    New Limit: Maximum allowed by Windows

    Number of unique colours allowed a single workbook
    Old Limit: 56 (indexed colour)
    New Limit: 4.3 billion (32-bit colour)

    Number of conditional format conditions on a cell
    Old Limit: 3 conditions
    New Limit: Limited by available memory

    Number of levels of sorting on a range or table
    Old Limit: 3
    New Limit: 64

    Number of items shown in the Auto-Filter dropdown
    Old Limit: 1,000
    New Limit: 10,000

    The total number of characters that can display in a cell
    Old Limit: 1k (when the text is formatted)
    New Limit: 32k or as many as will fit in the cell (regardless of formatting)

    The number of characters per cell that Excel can print
    Old Limit: 1k
    New Limit: 32k

    The total number of unique cell styles in a workbook (combinations of all cell formatting)
    Old Limit: 4000
    New Limit: 64kThe maximum length of formulas (in characters)
    Old Limit: 1k characters
    New Limit: 8k characters

    The number of levels of nesting that Excel allows in formulas
    Old Limit: 7
    New Limit: 64

    Maximum number of arguments to a function
    Old Limit: 30
    New Limit: 255

    Maximum number of items found by �Find All�
    Old Limit: ~64k (65472)
    New Limit: ~2 BillionNumber of rows allowed in a Pivot Table
    Old Limit: 64k
    New Limit: 1M

    Number of columns allowed in a Pivot Table
    Old Limit: 255
    New Limit: 16k

    Maximum number of unique items within a single Pivot Field
    Old Limit: 32k
    New Limit: 1M

    Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table
    Old Limit: 255 characters
    New Limit: 32k

    The length at which fields� labels are truncated when added to PivotTable; this also includes caption length limitations
    Old Limit: 255
    New Limit: 32k

    The number of fields (as seen in the field list) that a single PivotTable can have
    Old Limit: 255
    New Limit: 16k

    The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
    Old Limit: 8k
    New Limit: Limited by available memory

    The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
    Old Limit: 64k
    New Limit: Limited by available memory The number of array formulas in a worksheet that can refer to another (given) worksheet
    Old Limit: 65k
    New Limit: Limited by available memory The number of categories that custom functions can be bucketed into
    Old Limit: 32
    New Limit: 255

    The number of characters that may be updated in a non-resident external workbook reference
    Old Limit: 255
    New Limit: 32k

    Number of rows of a column or columns that can be referred to in an array formula
    Old Limit: 65,335
    New Limit: Limitation removed (full-column references allowed)

    The number of characters that can be stored and displayed in a cell formatted as Text
    Old Limit: 255
    New Limit: 32k