[RESOLVED] SpreadSheet Control

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.

A .NET alternative would be to use a DataGridView control.

A free 3rd party software alternative would be to use LibreOffice.

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

I found this site http://10tec.com/ but not sure on credibility or if it is "Safe"

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.

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.

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

and i found the one that does the random thing here http://www.gemboxsoftware.com/spread...mitReached.htm

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.

Well probally basic calculations for now like average divide multiply add subtract max min etc. and simple as well

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.

Quote:

---

Originally Posted by jdc20181

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.

Quote:

---

Originally Posted by Gruff

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

Just doing your own calculations for a datagridview is far from a trivial task.

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)

The difficult part is not the visual interface. DataGridView does all that for you.

Okay,

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

---

Form code for the spreadsheet control.

Code:

---

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

---

Attachment 133673

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.

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.

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

I would imaging it would start with those segments of the formula between the operators again.

We would check if each segment started with one of the new function names.
AVG(, MIN(, MAX(, TOTAL(, ...

Send the segment to sub routines by the appropriate name and replace that section of the formula just as we did before.

Excel Function parameters are separated by commas so we split the string by commas to get each parameter

Since many Excel functions operate on ranges we will need to convert the Alphanumeric to two points.

then iterate like so.

Code:

---

For y as integer = p1.y to p2.y
  For x a integer = p1.x to p2.x
    'Do stuff with cell value.
  Next
Next

---

Just spit balling here.

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.

Thanks! I honestly am still a beginner so I appreciate your help!

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.

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.

I think you are being purposefully obtuse. Re-read the last line of post #30.

You stated you wanted to learn.
It is beginning to appear as you simply want someone to do your work for you.

If you really want to learn about the mechanics of how it works you would be stepping through the code in debug mode and asking questions to clarify.

If it is breaking figure out why.

No. I will not write code to tack on more features you want.

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.

Here is a quick you tube on break points.

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.

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.