I am gonna just flat out ask how do I go about creating my own spreadsheet control, I do not want to use any of the ones offered such as by dev. express and so forth because they are limited to a small amount of cells before random cells are filled with their name, I would rather try to create my own, secondly how are these even created in the first place, and I don't have MS Office or excel so i can't use that either (Would rather not anyways) and I have found examples of free ones before but they don't work and cause so many exceptions and errors that
I can't fix it. Thanks.
That is what i was thinking well i have open office and would use that but not sure how to get the control into visual studio it won't add from open office, so idk if libre office has it where you can or not, I have limited internet (Live in middle of no where you may say and have satilite) so i don't want to download something that may not work. I will look into it more and see what i can find
You haven't defined what you want the control to do specifically.
Is it just to display columns and rows of data? Do you need to read native Excel documents or is comma or tab separated text files okay.
Do you need to make working formulas in cells?
If you want something that can do most of what excel can do you are talking about a really tall order.
Burn the land and boil the sea
You can't take the sky from me
Yes, I want something users can edit and save, but I want more of a shell than anything because all these companies limit a freebie version or don't offer it, quite honestly I want to know how they create the control, and how I can use a existing one or try to recreate one. Not sure, what I exactly want just yet.
If all you want is to display and edit text based files in a grid then as dday said the standard datagridview control in VB.NET is what you want. You will have to do some reading about it and write your own procedures for special functionallity.
Burn the land and boil the sea
You can't take the sky from me
Datagrids are good for row/column data. The DevExpress grid does something quite a bit weirder, which is good and bad. I've never seen the issue you reported happening when the number of columns gets too high. How many are you talking about? On the other hand, I guess I've always used their grids bound to datatables, so that may not be the same thing at all.
Shaggy, they only offer a free trial and it was buggy, I forget which one it was that said they did that trying to relocate it, I was wanting something more open source, I did find a spreadsheet control that was free open source etc. on codeproject I think it was but it didn't work and caused an error that made it not work right. Not sure, Was really just an idea not really needed, was just wondering if there was a way to get one of my own. The biggest probelm is they are free and have no free updates and are filled with bugs, and then you have to pay an overprice license fee if you want it to work withoiut bugs that is all i find If you find anythinglet me know
Found the free one that I had found it isn't the one that puts random stuff in but its the one i couldn't use and it is in c# so idk how to use it, and not sure if I can convert it. here is a link http://www.codeproject.com/Articles/...dsheet-Control any ideas from here to convert it to use in vb.net that wouldn't cause too many problems
oh and one last note on the one found on codeplex it is now a "For sale" control and is licensed with limited use so I am not sure if it will even work....
Now wait a minute! You say you don't really need this? In that case, it could be absolutely anything at all, right?
You have to figure out what features you want before you go looking. I use a DGV in one project as a very simple grid entry system. I didn't need much at all, though, so the raw DGV was sufficient. In a different project, I needed quite a bit more, so I made a grid of custom controls (a textbox and two buttons) that could be sized all over the place. Another option would be a TableLayoutPanel with textboxes in it, or even user controls. It all depends on what functionality you need.
Alright so I am gonna use a datagridview to get this working now the only thing is I want it to look and feel like excel so how can I make it go infinate like the columns and rows and so forth and how do i declare the formulas and will I be able to do this?
Even that is not a trivial task if you are writing it from scratch.
Years ago I worked with the Office Web Controls (OWC) from Microsoft.
I believe it was a free download or you got it by installing Office 2000.
It had a spreadsheet control that worked for the most part. I do not think it could load xls files directly, but there were work arounds. It had a lot of the functionality of Excel. You used the controls directly on your VB6 form. I do not know if it would work with VB.NET or the latest Windows OS's.
Burn the land and boil the sea
You can't take the sky from me
Alright so I am gonna use a datagridview to get this working now the only thing is I want it to look and feel like excel so how can I make it go infinate like the columns and rows and so forth and how do i declare the formulas and will I be able to do this?
Nothing about Excel is infinite. It has limits for sure.
Formulas will require a separate data structure of a class to hold formulas. You will have to have ways to link cell xy positions with the structure so the results can be displayed in the DGV.
I would imagine it would be a 1D list not a 2D array.
You will need a formula parser and evaluator engine.
There is a whole lot to it and I do not think you will find any simple examples.
Recreating Excel is not something I would want to attempt and I have been programming since the eighties.
Burn the land and boil the sea
You can't take the sky from me
Recreating Excel is not something I would want to attempt and I have been programming since the eighties.
Oh wow, and I didn't mean the whole nine yards I was just meaning something that could do formulas and could have (infinate or a lot ) of rows and columns. I haven't been programming for a year yet and well I see all these spreadsheet controls but no where is there a way to make one...
Keep in mind that spreadsheets took years to develop with teams of programmers. Even the restricted set of functionality that you mention isn't something that was written all that fast. However, when it comes to the DGV, you can add columns with ease, and add rows even easier. There is some limit to both of those, but it's probably high enough that you don't need to worry about it too much. Before you reach that limit, the performance of the grid would probably degrade to the point that you'd KNOW you were reaching a limit.
Functions in cells is a whole lot harder, if you want them to act something like Excel. You can do some equations with the DGV, though I'm not sure which ones are available. The problem is that they wouldn't work the way Excel works, where you can write an equation into a cell and copy/paste it to other cells. That functionality would require a function parser, which is a whole different matter. What would be easier, but not so much like Excel, would be having buttons for Sum, Avg, Mode, or whatever, such that you'd highlight a bunch of cells and press the button, which would perform the function on the selected cells. Of course, that would greatly limit the set of functions you'd have.
allright then lets go from a different idea lets say instead we use what you mentioned earlier a tablelayout with textboxes how would I make it look like excel and do the calculations for add subtract (for now just so i can get the idea)
Here is a very rough inherited datagridview control.
It has no error checking and the evaluation is very limited.
Perhaps you can build on it. (More follows in the next post)
Code:
Imports System.Text.RegularExpressions
Public Class SpreadSheet
Inherits DataGridView
Public FCells As New List(Of DataGridViewCell)
Public Property TotalRows As Integer = 100
Public Property TotalColumns As Integer = 26
Public Sub New()
With Me
.DefaultCellStyle.BackColor = Color.White
.DefaultCellStyle.ForeColor = Color.Black
.DefaultCellStyle.SelectionBackColor = Color.LightCyan
.DefaultCellStyle.SelectionForeColor = Color.Blue
.Columns.Add(Nothing, "")
.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
.Columns(0).DefaultCellStyle.BackColor = SystemColors.Control
.Columns(0).Width = 35
For x = 0 To TotalColumns - 1
.Columns.Add(Nothing, Chr(x + 65))
.Columns(x).SortMode = DataGridViewColumnSortMode.NotSortable
.Columns(x).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
Next
For y = 0 To TotalRows - 1
.Rows.Add()
.Rows(y).Cells(0).Value = (y + 1).ToString
.Rows(y).Cells(0).Style.Alignment = DataGridViewContentAlignment.MiddleRight
Next
.RowHeadersVisible = False
.AllowUserToDeleteRows = False
.AllowUserToAddRows = False
.AllowUserToResizeRows = False
.CurrentCell = .Rows(0).Cells(1)
.Refresh()
End With
End Sub
Public Sub AddFunction(Cell As DataGridViewCell, Txt As TextBox)
Txt.Text = Txt.Text.ToUpper
Txt.Text = Txt.Text.Replace(" ", "")
With Me
If Txt.Text.StartsWith("=") Then
Cell.Tag = Txt.Text
Cell.ReadOnly = True
FCells.Add(Cell)
ReCalc()
End If
End With
End Sub
Public Sub ClearFunction(Cell As DataGridViewCell)
With Me
If FCells.Contains(Cell) Then
FCells.Remove(Cell)
End If
Cell.Tag = Nothing
Cell.Value = ""
Cell.ReadOnly = False
End With
End Sub
Public Sub ReCalc()
For Each Cell As DataGridViewCell In FCells
Cell.Value = Eval(Cell.Tag.ToString)
Next
End Sub
Private Function Eval(Formula As String) As Double
' Parse and evaluate a simple expression
Formula = Formula.Replace("=", "")
Dim CellList() As String = Formula.Split(New Char() {"+"c, "-"c, "*"c, "/"c})
For Each sCell As String In CellList
Dim p As Point = ConvertAddress(sCell)
Dim sValue As String = "0"
If Me.Rows(p.Y).Cells(p.X).Value Is Nothing Then
Formula = Formula.Replace(sCell, "0")
Else
Formula = Formula.Replace(sCell, Me.Rows(p.Y).Cells(p.X).Value.ToString)
End If
Next
'MessageBox.Show(Formula)
Return Evaluate(Formula)
End Function
Private Function ConvertAddress(sCell As String) As Point
' CALCULATE row and ColUmn from Excel style address. A1 = 1,1. Ect...
Dim Alpha As String = Regex.Replace(sCell, "[0-9\-]", String.Empty)
Dim Col As Integer = Asc(Alpha) - 64
Dim Row As Integer = CInt(sCell.Replace(Alpha, "")) - 1
Return New Point(Col, Row)
End Function
Private Function Evaluate(expression As String) As Double
Dim DT As DataTable = New DataTable
Dim DTCol = New DataColumn("Eval", GetType(System.Double), expression)
DT.Columns.Add(DTCol)
DT.Rows.Add(0)
Return CDbl(DT.Rows(0).Item("Eval"))
End Function
End Class
Burn the land and boil the sea
You can't take the sky from me
Public Class Form1
Private Sub SpreadSheet1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles SpreadSheet1.CellClick
With SpreadSheet1
TextBox1.Text = ""
If .CurrentCell.Tag Is Nothing Then Exit Sub
TextBox1.Text = .CurrentCell.Tag.ToString
End With
End Sub
Private Sub cmdAddFunc_Click(sender As Object, e As EventArgs) Handles cmdAddFunc.Click
With SpreadSheet1
.AddFunction(.CurrentCell, TextBox1)
End With
End Sub
Private Sub cmdClearFunc_Click(sender As Object, e As EventArgs) Handles cmdClearFunc.Click
With SpreadSheet1
.ClearFunction(.CurrentCell)
End With
End Sub
End Class
Last edited by Gruff; Dec 23rd, 2015 at 07:22 PM.
Burn the land and boil the sea
You can't take the sky from me
so the first part of the code you post how does it work with the second part I get confused with classes is this suppose to be a control like it saves to my toolbox or what?
Okay I figured it out. It is a toolbox control and wow thanks for your help. I def. got work to do on it.
*Edit* Got a couple of questions. First I tried using a tool strip so it could be docked and so forth that caused an error dunno why but it did, fixed it with using regular textbox with a group box. Second does it recongnize all of the formulas etc.
Last edited by jdc20181; Jan 1st, 2016 at 11:35 AM.
Not sure what you mean by toolstrip error. As I am not sitting next to you I cannot say.
You said you only wanted the basics. + - * /. That is what this does.
Perhaps If I gave you an overview and you read through the code (There really isn't much.)
The datagridview does most of the visual house keeping for you.
Each cell is a mini textbox with full editing capability. It also keeps track of the currently selected cell.
There is a ton of method and properties you can manipulate. Again I suggest you start reading up on those features.
One key feature is the cell.tag property which can hold just about anything you like.
I use it to hold a string that contains the cell function formula.
the AddFunction() method copies the string from the textbox into the currently selected cell's tag.
When it does that it creates a reference to the selected cell and adds the reference to a list object.
it is this list we iterate through when recalculating the entire spreadsheet.
This much faster then checking all the cells in the spreadsheet every time recalc is called.
Key to a spreadsheet is the coordinate system. In the new sub routine we letter and number the headers.
This is really just cosmetics as the datagridview still uses number coordinates inside.
So to handle the formulas we first need to parse the text into fields we can handle.
We remove the leading "=" character and then split the forumla text by the operators + - * and /.
This gives us a string array of everything between the operators.
We try to find a text segment with an address A1..Z100 for example.
if found we convert it to number coordinates.
In this case I used a point object as it stores two integer values,
x and y which I use to represent Column and Row.
Using the address we look up that cell value and replace the address in the formula text with it.
So "=A1+A2" becomes perhaps "44+29"
Finally we feed the formula into an evaluator engine that converts it to real numbers and then an answer.
For this purpose I used a trick with the datatable class that can handle really simple formulas.
The answer is stamped into the cell's value property and so displayed.
And that as they say is that.
I have a new version of the spreadsheet control in the works.
It will give an example of handling inline functions like Average() ,Sum(), and Count().
It has better error handling and recalculates when any cell is edited.
It should be ready sometime next week as I am at home for the weekend and do not have a Windows PC here.
Realize that this is not the way Excel works. It is just one possible way of making something similar.
The bigger challenge will be to have all the functions with addresses change when Rows and Columns are added or removed.
A feature not in this control yet.
Last edited by Gruff; Jan 2nd, 2016 at 03:30 AM.
Burn the land and boil the sea
You can't take the sky from me
Yep one other thing I would like to do with this tho is add more functions, so if I wanted min or max avg or other misc functions how would I add these? I am using this more to learn than anything to be honest so really i am not too worried right now
Attached is the entire project for testing a spreadsheet control inherited from the datagridview class.
The more I played around with it the more I saw what needed to be done is several generations of complete rewrite.
Too much horse power is wasted on conversion of string to numbers and back.
A better class than string for holding formulas would do better
A list of dirty cells Datatable under the hood
A better evaluation method and much more.
That said this version has:
1) Toolstrip interface.
2) Accept new or updated function by enter keypress.
3) Deletion of a cell or range of cells contents whether they contains a function or not by delete keypress.
4) Open and save documents to from disk.
5) Pull down with available function names and a button to push the selected function into the formula toolstriptextbox.
6) Display of the address of the current cell.
7) RightMouse option to copy the address of a range of cells to windows clipboard.
8) SUM, AVG, MIN, MAX, and COUNT functions.
9) Add Subtract multiply or divide operators.
10 Substitution of Cell values for individual addresses within a formula.
For what it is worth here is the latest version of the control and the last I will work on.
Burn the land and boil the sea
You can't take the sky from me
I know this is my thread but if someone could put this in the code bank for Gruff's help that would be awesome.....I am sure someone else will need something like this eventually!
The only issue I found was the first time I started it up it gave a error,
An unhandled exception of type 'System.IndexOutOfRangeException' occurred in SpreadSheetControl.exe
But after I stopped debugging and restarted it worked fine
The forum rules state no binaries are to be up loaded. The uploaded project was stripped of them.
A build of the project would need to be done to generate them again before the first run.
Again, this project is not recommended for use as anything other than an exploration of the hurdles involved in creating a spreadsheet control. It needs to be completely redesigned for speed and flexibility.
The entire contents of the form interface should be moved to a usercontrol project if the goal is a stand alone control.
Burn the land and boil the sea
You can't take the sky from me
What is your reccomendations on making this more user friendly, as I had issues with the dialog popping up no data found or something with the program it self not a code end but when i entered a formula that i hadn't put info into certain cells for example i put in Cell A1 =sum(b1:c1) (I had to modify that sum to get it working exact) but it did work however until i added data in b1 or c1 it popped up a error, Flexibility isn't a problem exactly I don't want to make a microsoft excel or other related program I want a basic function with what I have said I don't need fancy stuff, it is made for simple calculations of data and use. I will be making some adjustments to it as I Want to add Bold and italics and underline as well as *Merging* cells, which I must ask, how to do.
This of course will be part of a open source project in case you was wondering it will be part of it anyways. BeffsBrowser is a webbrowser I have worked on since July I haven't focused on much of the browser but added more to it such as a word processor screen shot taker, calculator, games and other features. currently i am using the IE control I dont like it but it does run better than IE itself though, that may have to do with it being smaller as far as the program itself.
No, that isn't what i meant it won't be a project for a long time, I am using the code as a base as again i am just learning, it isn't gonna be anything until i can do more. I am not asking for anyone to do anything for me. Anytime i barrow code, i don't just copy and paste I read through it, right now I don't have the time to read through alll of the code as i am still working continuously on a ongoing project, so I have no plans to work on it until later on. With high school i barely have time to get online anymore really.
as far as debugging I don't understand how to step through exactly but i will get it. No need to write anything else, I think i can figure out the rest. The mechanics is what i didn't understand with the functions, and getting into a toolstrip so it isnt cluttered.
Breakpoints and code stepping are the most valuable tools you can have for coding. We all use them pretty much every day in pretty nearly every language we work in. They are so useful that similar functionality of breakpoints and code stepping has been added to every IDE that I am aware of. The first thing to do is to look up how to set breakpoints F9 will do it, but there's an easier way with the mouse in VS. Once code stops at a breakpoint, stepping is F10 or F11 (F8 on some rare layouts), with a significant difference between the two, but stepping is just the beginning, since you can also look at the contents of all variables and evaluate all expressions.
I had a few minutes today. (Rare thing.) I ran the spreadsheet testbed app and tested for what you said created an error. No error occurred.
The easiest way to use this testbed for range functions is to:
1) highlight the range you want to calculate.
2) right mouse and select 'copy cell address range'
3) select the cell where you want the function to reside.
4) select the function from the dropdown in the toolbar.
5) click the left arrow button to push the function into the function editor.
6) press Ctrl-V to paste the address range.
7) press enter.
Entering anything invalid into the function editor will result in an error.
Typically one forgets the '=' at the beginning. or a parentheses.
To test this enter =CAT and press enter. You will get an error.
=CAT(A2,A10).... Error as no function called CAT exists.
Last edited by Gruff; Jan 12th, 2016 at 12:40 PM.
Burn the land and boil the sea
You can't take the sky from me
As i said, it quickly disappeared, I just had to clean and rebuild a few times
I think the other error i said i got was my fault, not the code fault typo errors, and it was getting late when i tested it my fingers were hurting.
[RESOLVED] SpreadSheet Control
Reply With Quote
Originally Posted by jdc20181
