The Spreadsheet Control
The control is on its face identical to the worksheet object in Excel and the programming object model shows the same similarity. However the name is (to som extend) misleading as it’s a container for workbooks and the workbooks are containers of worksheets. The control can only handle one open workbook at the time. Unfortunately it can’t handle Excel’s files.
Some information about the control:
Each worksheet can contain up to 702 columns and 262144 rows.
If the control is populated with data from a data source then only the columns and rows in use are viewed.
Can only support one active worksheet, which limit the possibility to have interaction between
worksheets through functions and references. We can’t make a lookup in one worksheet and place the result
in another worksheet through formulas.
Support nearly all functions that Excel provides including functions in Analysis Toolpak.
Support relative and absolute cell references as well as range names.
Does not support Data Validation.
Support autofiltering and sorting although on a primitive level.
Have the same worksheet’s events as its sibling and several others events like StartEdit, EndEdit and
ViewChange. In total it has over 40 events.
Allow us to create customized column headings.
Has its own built-in source control.
Lack the possibility to print from the control (as with the other OWC).
If we plan to create a model that involves functions / formulas including a less sophisticated design then it’s highly recommended to do all the work in Excel and then simple copy and paste it into the control at design time as the control does not provide any user friendly interface.
The below code example shows how we can manipulate central properties of the control, populate the control with data from a Recordset, and apply an autofilter. When the form is loaded we get the following output:
Function Set_Up_Worksheet()
Const stCon As String = _
“Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _
“Persist Security Info=False;Initial Catalog=Northwind;” & _
“Data Source=IBM”
Const stSQL As String = _
“SELECT Year(ShippedDate) AS Period, “ & _
“ShipCountry AS Country, “ & _
“ShipCity AS City, “ & _
“Freight “ & _
“FROM Orders “ & _
“WHERE Year(ShippedDate)IS NOT NULL;”
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim owcSpread As OWC11.Spreadsheet
Dim owcWbook As OWC11.Workbook
Dim owcWSheet As OWC11.Worksheet
Dim owcAutoFilter As OWC11.AutoFilter
Dim iCounter As Integer
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
Set owcSpread = frmData.Spreadsheet1
Set owcWbook = owcSpread.ActiveWorkbook
Set owcWSheet = owcWbook.ActiveSheet
‘Get the recordset.
With cnt
.CursorLocation = adUseClient
.Open stCon
Set rst = .Execute(stSQL)
Set rst.ActiveConnection = Nothing
.Close
End With
‘Setup the Spreadsheet control.
With owcSpread
.AllowPropertyToolbox = False
.DisplayOfficeLogo = False
.DisplayPropertyToolbox = False
.DisplayTitleBar = False
.DisplayToolbar = False
With .ActiveWindow
.DisplayGridlines = False
.DisplayHorizontalScrollBar = False
.DisplayColumnHeadings = False
.DisplayRowHeadings = False
.DisplayWorkbookTabs = False
.EnableResize = False
End With
End With
With owcWSheet
‘Populate the worksheet with the recordset.
.Cells.CopyFromRecordset rst
‘Turn on the AutoFilter for the range.
.UsedRange.AutoFilter
‘Set the variable to the worksheet AutoFilter which is not equal
‘to the Range AutoFilter.
Set owcAutoFilter = .AutoFilter
‘Create the filters.
With owcAutoFilter
‘Criteria for Year, i e exclude 1996 records.
‘Excluded is the default setting for Criteria.
.Filters(1).Criteria.Add “1996”
‘Criterias for Shipping Country,
‘i e include only records from France and Germany.
With .Filters(2).Criteria
.FilterFunction = ssFilterFunctionInclude
.Add “France”
.Add “Germany”
End With
‘Apply the filters.
.Apply
End With
‘Sort the filtered list.
With .UsedRange
For iCounter = 4 To 1 Step -1
.Sort ColumnKey:=iCounter, Order:=xlAscending, Header:=xlYes
Next iCounter
End With
‘Adjust the columns.
.Cells.Columns.AutoFit
‘Disallow the users to manipulate the data and the worksheet.
With .Protection
.AllowDeletingColumns = False
.AllowDeletingRows = False
.AllowInsertingColumns = False
.AllowInsertingRows = False
.AllowSorting = False
.Enabled = True
End With
End With
With owcSpread.ActiveWindow
‘The range that have been populated with data which is
‘also visible for the users.
frmData.lblViewable.Caption = .ViewableRange
End With
‘Release objects from memory.
rst.Close
Set rst = Nothing
Set cnt = Nothing
Set owcAutoFilter = Nothing
Set owcWSheet = Nothing
Set owcWbook = Nothing
Set owcSpread = Nothing
End Function
The freeze Panes is set manually as it seems to be impossible (at least for me) to do it via code, i e freeze the first row. Other then that it works as expected via code.
To export the filtered data to Excel we use the command plCommandExport as the following snippet code shows:
Set ptCommand = owcSpread.Commands(plCommandExport)
‘Execute the Export to Excel command.
ptCommand.Execute
Please note that all the settings are also exported, including any worksheet protection.
The control has a property named Dirty that is intended to control if data have been changed or not. In theory it’s a nice feature however due to a bug it does not work. For a workaround please see the following article at my English site: Use the OWC Spreadsheet in MS VB 6.0
In the next part the OWC Chart control will be presented.
Kind regards,
Dennis
Very interesting. What’s the difference between OWC.Worksheet and OWC.Spreadsheet, and why do you set up one of each?
Dennis
Thanks for taking the time to post the last 3 items regarding the OWC. Better take more time to smell the roses Okay?
Thanks for the feedback :)
Unfortunately I hit the wrong (!) button so I’ve made some additional amendments in the post.
The control is a container for one open workbook and unlike Excel it can’t handle several worksheets at the same time.
The OWC11.Spreadsheet refer to the control itself while OWC11.Worksheet refer to the active worksheet.
Some properties can only be handle via the control itself.
I have limit experience with the control (only two shipped customs solutions includes the control in hidden mode) and I believe it would be a mistake to compair it with Excel and try to use it in similar way.
Jim, for personal reason I have a time schedule to pay attention to…
Kind regards,
Dennis
This is a great clooection of articles, Dennis. Thanks for sharing !
Best wishes Harald
Sorry. I need an article on the spleeckecher control…
You’re welcome :)
Kind regards,
Dennis
Hi there,
Anyone know how to paste a range from a spreedsheet to a spreadsheet control in another sheet from the same workbook?
Many thanks
julie
hello…
please help me…
i am using vb.net and asp.net… and i am using owc spreadsheet..
i simply want to load an existing excel file to owc spreadsheet “programmatically” in code behind page of vb.net by the click of a button.
please help me. i cant find any documentation anywhere in the web.
god bless,
jb
Any idea if I can use a combo box in spreadsheet control??
me too say…
hello…
please help me…
i am using vb.net and asp.net… and i am using owc spreadsheet..
i simply want to load an existing excel file to owc spreadsheet “programmatically” in code behind page of vb.net by the click of a button.
please help me. i cant find any documentation anywhere in the web.
JB, do you have any answer related with your question?
Jose Luis
Hai all
Please help me…
I am very new to this owc control..Can anybody send me a code to populate a data in the spreadsheet control.I tryed a lot in that.I cant able to make a the object created in the class to communicate with the ID of the control in the asmx page..Can anybody send it plz…
Its very urgent for me to do some small application using this owc control..can anybody send a code plz..this is my mail ID divyadhivi@yahoo.co.in
hi dhivya,
me 2 wrkin 4 dat ;-)
JB,
Did you find an answer? If so, please email me – I am trying to do the same thing, and can’t find info anywhere.
thanks,
Michelle
Hi Michelle & JB (if still around)
I’d been doing like this by load xls file in (hidden) Excel application object.
Then copy entire sheet and paste them in OWC sheet. then close Excel.
A bit nasty and inefficient but it works.
Panya
How can I add a dropdownlist on my excelsheet as we do with datavalidation in normal excel?
I am new to this OWC control. How can I format activated the wrap text/ AutoFit property in a cell as we do in normal excel??
How can I add a dropdown list on any OWC spreedsheet cell??
With thanks in advance…
Subhrajit
Hello,
the example above is great. But… i have defined several queries which I’d like to use in the Excel. I don’t want to define the queries by hand and link em to the spreadsheet control. Is there anyway to link the contents of the Spreadsheet control to a query?
Kind regards,
Barry
How can you freeze planes in MS OWC 7? If you can’t, can you change the Column Headings?
ANy guess on how to place a combobox in spreadsheet control?Or data validation for a cell??
it is 2012 and the old OWC11 Spreadsheet ActiveX is
STILL THE FASTEST GRID VIEW CONTROL out there
(usable in all kind of Microsoft applications with user forms).
Remember that if you intend to build ANY reports or ERP like
applications for accountants (that will display tons of table rows),
the FIRST good “thing” you need is a FAST Grid View with lots of
events and with an intelligent invisible-pagination system
(tuned for fast displaying, like this OWV11.Spreadheest) adapted for
the surface that the user can actually see.
1) How to place a combobox/Dropdown in spreadsheet control?
2) On selection of one cell, other cell should get filled, is this possible in this control?
3) How to see the sum of selected column cells in bottom toolbar?
Hi, do you know if there is a way to auto populate a cell (say A2) in the Spreadsheet when another cell (say B2) has text added to it? The Cell will be auto populated with a Ref which is in a Textbox on the same form.
Any help would be much appreciated
Thanks