Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
EXCEL CLASS VERSION 1.0.1

The class contains three protected properties, these are:

1: oExcel = This is the Excel.Application object
2: oWorkBook = This is the WorkBook Object, this can only contain one object.
3: aSheets = Array of Worksheet objects, each element is one sheet.
4: aExcelRow = Internal Array used by ConvertStringToArray and BuildLineFromArray.

To load the DLL:
oReport = CREATEOBJECT("ExcelClass.clsExcel")

Methods contained in the class are used to manipulate the Excel object and work sheet objects, these methods are:


MethodBuildExcelBookParameterstnNumberOfWorkSheets

Example Call
oReport.BuildExcelBook(5) && Creates Excel workbook with 5 sheets


MethodRenameSheetParameterstnWorkSheettcCaption

Example Call
oReport.RenameSheet(1,”This is Sheet 1”) && Renames the Tab
oReport.RenameSheet(2,”This is Sheet 2”)
* etc

If you emit the Worksheet, the routine will default to the first work sheet.

MethodGetSelectionParameterstnWorkSheettcRangeReturnsSelection Object

Example Call
loSelection = oReport.GetSelection(1,”A1:H1”)
loSelection.HorizontalAlignment = xlHAlignLeft && Sets all Cells in range to Left Alignment.

MethodShowExcel

Example Call
oReport.ShowExcel && This method makes Excel Visible.

MethodSetFontParameterstnWorkSheettcRangetcFonttnColor

Example Call
This method allows you to set the font and colour of a specific cell or range of cells. The Default Font is set at: Arial - 10.
oReport.SetFont(1,”A1”,”Arial:10:B”)
oReport.SetFont(1,”A1”,””,RGB(0,0,255)) && Set Colour to Blue leave at default font.

FontStyle ParametersBBoldUUnderlineIItalicNNormal - Reset to defaultBU or UBBold and UnderlinedBI or IBBold and ItalicUI or IUUnderlined and Italic

MethodAddSheetParameterstcWheretnWorkSheet

Example Call
The font Parameter is in the format = ontName:FontSize:FontStyle
oReport.AddSheet(“Before”,1) && Add a new sheet before worksheet 1
oReport.AddSheet(“After”,2) && Add a new sheet after worksheet 2

tcWhere can be either Before or After, when you use this method, you will have to remember that the internal array ‘aSheets’ is re-mapped, so if you have 3 worksheets to start with then add a new sheet after 1 the array will look like this:

aSheets array beforeaSheets array after[1] = WorkSheet 1[1] = WorkSheet 1[2] = WorkSheet 2[2] = New Inserted WorkSheet[3] = WorkSheet 3[3] = WorkSheet 2 [4] = WorkSheet 3

MethodRemoveSheet

This method has not been implemented yet!

MethodCellAlignmentParameterstnWorkSheettcRangetcAlignment

Example Call
oReport.CellAlignment(1,”A2”,”HCenter”)

The default Alignment is HLeft and VBottom. tcAlignment can be one of the following:

HCenterHorizontal CenterHLeftHorizontal LeftHRightHorizontal RightHAcrossHorizontal Center Across SelectionHDistribHorizontal DistributedHFillHorizontal FillHGeneralHorizontal GeneralHJustifyHorizontal JustifyVCenterVertical CenterVBottomVertical BottomVDistribVertical DistributedVJustifyVertical JustifyVTopVertical Top

MethodSaveWorkBookParameterstcFileName

Example Call
oReport.SaveWorkBook( “MyExcelFile” ) &&Saves the excel spreadsheet to MyExcelFile.xls

MethodOpenFileParameterstcFileName

Example Call
This method requires the full path to the document.
oReport.OpenFile( “C:Documents.xls” )

Once the file has been opened, the following properties are set:
oWorkBook = Contains the WorkBook Object
aSheets[x] = Contains all the WorkSheet objects.

MethodBuildLineFromArrayParameterstnWorkSheettcStartCelltcFont

This method will populate an entire line with the contents of an array.

Example Call
lcHeaders = “a:.T.,b:10,c:15,d,e:5,f:.T.”
oReport.ConvertStringToArray(lcHeaders)
oReport.BuildLineFromArray(1,”A5”,”Arial:10:B”)

Note the string is in the format of:
Element 1 = Name/Description
Element 2 = Column Width or .T. for AutoFit

MethodBuildSheetParameterstnWorkSheettcRangetcValuetcFonttuWidthtcAlignment

This method allows you to manipulate an individual worksheet.
Example Call
WITH oReport
    .BuildSheet(1,”A1”,”Header”,”Arial:10:B”,.T.)
    .BuildSheet(1,”A3”,10)
    .BuildSheet(1,”A4”,20)
    .BuildSheet(1,”A6”,”=SUM(A3:A4)”,”Arial:10:B”,”HRight”)
ENDWITH

MethodCloseExcel

This method will close the excel application.

MethodMergeCellsParameterstnWorkSheettcRangetlMerge

This method allows you to merge or un-merge a range of cells. The default action of this method is merge.
Example Call
oReport.MergeCells(1,”A1:D1”) && Merge Cells A1 to D1
oReport.MergeCells(1,”A1:D1”,.F.) && Un-Merge the Cells A1 to D1

MethodFindSheetParameterstuWhatReturnsSheet Name or Number depending on tuWhat

This Method will find a specific sheet either by name or number. If you pass a number as the parameter, the function will return the tab name of the selected sheet. If you pass the Name you want to find, the function will return the sheet number.

Example Call
lcName = oReport.FindSheet(2) && Return the Tab Name of Worksheet 2
lnSheet = oReport.FindSheet(lcName) && Return the Sheet Number for lcName

MethodConvertStringToArrayParameterstcString

This method will take a comma delimited string and convert this into a two dimensional array which can be used by the BuildLineFromArray method.

Example Call
LOCAL lcHeadString
*-- You can specify the column width in the string by using a :width, .T. = AutoFit.
lcHeadString = “a:.T.,b:10,c,d,e,f:10,g”
oReport.ConvertStringToArray(lcHeadString)
oReport.BuildLineFromArray(1,”A5”)

MethodSetCellParameterstnWorkSheettcRangetcPropertytuValue

This method allows the user to manipulate any cell or range of cells properties.

Example Call
oReport.SetCell(1,”A1”,”Orientation”,90) && Set the Cells Orientation property
oReport.SetCell(1,”A1”,”ShrinkToFit”,.T.) && Shrinks the Text to Fit in a Cell.
*-- To Change Font Properties
oReport.SetCell(1,”A1”,”Font.Color”,RGB(255,0,0)) && Sets Colour to Red.

MethodSetWorkSheetParameterstnWorkSheettcPropertytuValue

This method allows the user to manipulate any of the worksheet properties.

Example Call
oReport.SetWorkSheetl(1,”PageSetup.Orientation”,2) && Set page to Landscape

MethodDrawBorderParameterstnWorkSheettcRangetcStyletnColortlGrid

This method allows you to draw lines around a single cell or a range of cells, you can also specify the colour and thickness of the line to be drawn. tcStyle is in the following format: LineType:Thickness.

LineTypeThicknessxlContinuousxlHairLinexlDashxlThin (Default)xlDashDotxlMediumxlDashDotDotxlThickxlDouble xlSlantDashDot xlLineStyleNone 

Example Call


oReport.Drawborderl(1,”A1”,”xlContinuous”) && Draw continuous border around Cell A1
*-- This draws a hairline dashed box around cells A1-C3 with the colour of red and draws lines
*-- Inside the box as well.
oReport.DrawBorder(1,”A1:C3”,”xlDash:xlHairline”,RGB(255,0,0),.T.)

MethodGetActiveSheetParametersNoneReturnsActiveSheet Object

This method is used to return an ActiveSheet Object.

Example Call
oSheet = oReport.GetActiveSheet

Version Changes

1.0.0New Class1.0.1New Method - GetActiveSheet - Returns Active sheet Object

Planned Modifications
To allow multiple orkBook objects, also method to allow selection of appropriate workbook either by Name or number. Options to include adding graphs, also other options for opening different types of files from .txt to .csv etc… Also allow for saving to different file formats. Increase the options in the ConvertStringToArray method; including Cell Formatting, Alignment and colours, these can be done through other methods at the moment.

Update (November 20, 2001)
Simon has updated his Excel Wrapper Class to version 1.0.5. All Excel Examples have been tested and are fully functional after having cleared a few bugs found in some earlier versions. Simon is still working on the Beta release of the Help file, but in the interim has included documents as well as the beta release of the Help file. The classes for Word, Outlook and PowerPoint which are also planned for inclusion are still in development. You can download the new library here. The file size is 192,978 bytes.

ABOUT THE AUTHOR: SIMON ARNOLD

Simon Arnold Simon has worked with FoxPro for over 16 years. He currently works for a company based in Harrogate, North Yorkshire (UK), which specialises in FoxPro, Web and Unix development.
He has a weblog at http://weblogs.foxite.com/simonarnold.

FEEDBACK

Mohammad Irfan @ 6/18/2012 12:16:54 PM
File not found error :(



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: