xGrid - Grid/Spreadsheet Control

Developed or developing a new product in PureBasic? Tell the world about it.
Xombie
Addict
Addict
Posts: 898
Joined: Thu Jul 01, 2004 2:51 am
Location: Tacoma, WA
Contact:

xGrid - Grid/Spreadsheet Control

Post by Xombie »

It's come to my attention that some people missed the latest post in the xGrid topic that links to the code for the control. So, I'm making a new topic in the hopes that this will grab interested people's attention.

Yes, I am now releasing the code for xGrid. See the link at the bottom of this post for the download. It is currently incomplete and I'm basically releasing it so you can see how it might like in your own project. The little demo is very much incomplete (I was planning on updating it later today so check back this evening for a new update) and so doesn't work extremely well at the moment.

However, the code itself for xGrid should be fine and you can at least put it in your project to see how it behaves.

Please read my last post here for some more information:

viewtopic.php?t=16852&start=30

You can download the latest release of the code here:

http://www.seijin.net/Storage/xGrid-Code.rar

It is still a work in progrocess and I'll be adding to it. For example, soon I will be adding in my new expression solver so you can have more of a spreadsheet feel to the code. After that, copy/paste, column/row insertion/deletion, etc... Still a lot to go.

Please give credit where credit is due as this was quite a complex project for me - and it's not even my main project. It came out of a simple thought... "How hard would it be to make a grid control?" and this is the result. I don't even need it ^_^; It was just a side challenge, a distraction from my main project that kind of took on a life of it's own.

I was thinking of making it donationware or charging for the control but ultimately decided against it. I still have hopes of making a simple, lightweight spreadsheet control and then releasing that as donationware but with the code release, others might decide to try and beat me to it. I kind of hope not but I don't own the Internet nor am I stupid enough to think I'm smarter or a better coder than anyone else here (in fact, I know I'm not :) ) But I will still probably try to make xGrid the control into xGrid the lightweight, single executable spreadsheet for people to freely use. We'll see.

In the meantime, try out the code and try to wait patiently as I add to it and enhance the features.

There are a few neat tricks that I use in the code so even if you don't need a grid control, you may browse through the code to see what I'm doing.

For example, I don't use linked lists or arrays and, instead, use allocated memory as a kind of array. This allows for me to grow or shrink it as needed. I also do some neat things with callbacks and whatnot. I've tried to put in comments and keep the code structured and clean but it grew out of the beginning so some comments are not relevant (and, indeed, were copied from other sections).

Check out the older xGrid post for some code examples and let me know what you think.

As always, have fun ^_^
dell_jockey
Enthusiast
Enthusiast
Posts: 767
Joined: Sat Jan 24, 2004 6:56 pm

Post by dell_jockey »

You're my man, awesome, thanks a lot!

p.s. I admit to missing your prior releasing the code.... :oops:
cheers,
dell_jockey
________
http://blog.forex-trading-ideas.com
Xombie
Addict
Addict
Posts: 898
Joined: Thu Jul 01, 2004 2:51 am
Location: Tacoma, WA
Contact:

Post by Xombie »

Made an update to the code release. I've begun the implementation of adding expression evaluations to xGrid. Right now it will not handle any cell or range references but I hope to work on that some more today. I've started work on cell references and if you run it in debug mode, you can see how.

Well, first things first. To see how calculations work, type in "=2 + 2" in a cell and hit enter. It should show 4. Double-click on the cell to bring up the edit box and you should see the original '=2+2' formula that you can edit. Note - I have not hooked this up to the formatting yet so if you put in '= 2 + 2' you will return a ' 4' with a leading space. Eventually, numeric formatting will return a proper '4' with not spaces.

To see the very very beginning of cell value processing put in '=H10 + 2' and in the debug window you will see a message saying 'Column: 8, Row 10' to let you know it's located the column and row number for 'H10'. It should also handle absolute references (eg, '$H$10' or '$H10', etc...). HOWEVER, it's only for the left hand value and it will return a bad operator error because I'm definitely not finished with cell references.

And that's about it. Let me know if it works for y'all and how it's looking. ^_^

Download Here: http://www.seijin.net/Storage/xGrid-Code.rar

EDIT: Also, I noticed two things right now. It seems to not like calculating functions in xGrid (should be an issue with mistaking cell references) AND there's a bug when selecting columns/rows that shows up sometimes to crash. I should be able to take a look at both of these later today.
Xombie
Addict
Addict
Posts: 898
Joined: Thu Jul 01, 2004 2:51 am
Location: Tacoma, WA
Contact:

Post by Xombie »

Fairly Major update here. If all things work as they should, you should now have cell references available. Not range references yet.

You must use an '=' sign as the first character in the cell. So '=2+2' without the single quote ('). Or '=2 + Round(2.23562, 2)' or '=A3 + 332 + D10' or '=Round(H3)' or '=If(A10, True, False)' like that. You can use $A$3 or $A3 or A$3 to reference cells. The absolute/relative part will come into play later. Has no effect yet.

ALSO, you should be able to reference cells from other sheets. Use '=SheetName!A3 + 3' where 'SheetName' is the name of your sheet. An exclamation mark MUST follow the sheet name.

I also played around with a few other bits of code and such. I probably broke more than I fixed :) And I don't have the resources to do heavy testing so tell me if you spot bugs.

Now - It's been brought to my attention that I didn't explain how to use the cursors very well (mouse cursors). I took a screenshot for you to see how I did it in Japbe. You have to use resources to store the cursors in the executable.

Image

Let me know if that still doesn't help. The cursors should be included in the rar file.

Also, I updated the little demo part so right-clicking on a cell/column/row and clicking 'Format Cell..." should now let you change the various options like font name, size, colors, alignment... I haven't finished it all but it's more usable now.

It's shaping up, right? What do y'all think so far? Useful? Interesting?

Also, @straker, I have made a decision to use xGrid as my reintroduction into Linux. I ordered some parts to put together a new cheap Linux box (using an older cpu/mb I had lying around) and I plan to use it to get back into Linux (been years...) and start messing with PB Linux :D So, I will use this as my Hello World, Linux :D

And I think that's it for the moment.

Grab the latest here: http://www.seijin.net/Storage/xGrid-Code.rar

Let me know if you spot bugs. I hope I didn't make any new ones but I don't have the ability to test as much as I'd like.
jack
Addict
Addict
Posts: 1358
Joined: Fri Apr 25, 2003 11:10 pm

Post by jack »

looking good Xombie, thanks :)
jack
Addict
Addict
Posts: 1358
Joined: Fri Apr 25, 2003 11:10 pm

Post by jack »

small bug, when entering a number or expression in a cell and then pressing tab or arrow, it ignores the tab or arrow keys, clicking on another cell leaves the cell blank, pressing the return key seems to work.
btw, a tip for people using the PB IDE.
assuming that you created a folder "C:\Program Files\PureBasic\Examples\xGrid" and extracted the xGrid.rar to that folder.
save the following as "cursors.rc" in same folder

Code: Select all

2	cursor	discardable "C:\\Program Files\\PureBasic\\Examples\\xGrid\\old_CellSelect.cur"
3	cursor	discardable "C:\\Program Files\\PureBasic\\Examples\\xGrid\\RowSelect.cur"
4	cursor	discardable "C:\\Program Files\\PureBasic\\Examples\\xGrid\\ColumnSelect.cur"
5	cursor	discardable "C:\\Program Files\\PureBasic\\Examples\\xGrid\\SizeV.cur"
6	cursor	discardable "C:\\Program Files\\PureBasic\\Examples\\xGrid\\SizeH.cur"
now goto compilers/options/resources and add cursors.rc
:)
Xombie
Addict
Addict
Posts: 898
Joined: Thu Jul 01, 2004 2:51 am
Location: Tacoma, WA
Contact:

Post by Xombie »

Updated the code so that pressing tab while editing a formula/cell will save the cell contents and then move to the next cell. Should also now work when left or right clicking in another cell.

However, I've never had a problem using the arrow keys while editing a cell. In the last update I changed the usage to an Excel approach. If you double-click the cell to edit it, the arrow keys will move within the edit box itself. If you just started typing in a cell to display the edit box, the arrow keys will move around the cells. I tested it again on my side and it's working. In the second case, it also updates the cell with the new formula when changing cells.

PLEASE NOTE - I am not doing circular reference checking at this point. So please don't do this - (A3: "=A3 + 2"). You've been warned. Until I update code to check for and send an error for circular references you'll get caught up in a loop and crash the program.

So @jack and others - how is it running otherwise? Memory usage, speed, useability, etc...? Is the code easy to read and understand? Do you like the way the project is developing? This is my first released PB 'project' and first large code release so I'm like a mother watching her kid go off to school. I'd appreciate any thoughts or feedback on the project. Is my coding style bad or am I making stupid mistakes? I'm self taught on programming :oops: Also, is this something that should be listed on PureArea or otherwise?

Also - I'd like to hear feedback on whether I should drop the arrow keys and tabs and make that an end user coding issue. This was asked for previously and if enough people like the idea, I'll do it. I'd remove the scroll and tab so you'd just have the grid itself. I'd then implement some 'event' callbacks to return current scrolled position, etc... so you could easily update your own scrollbars as needed. For example, using the mouse wheel would check if you've set a 'new position' event and if so, would call the event with the current x & y offset so you can update your own scrollbars as needed. Thoughts?

Download Latest: http://www.seijin.net/Storage/xGrid-Code.rar
jack
Addict
Addict
Posts: 1358
Joined: Fri Apr 25, 2003 11:10 pm

Post by jack »

Xombie xGrid is looking really good, as for me I would like to keep the tab and arrow key functionallity as it is now, as for speed or memory usage, have not tested it extensibly yet but it is plenty fast enough for me.
with a little more work, I think you have a commercial product and should start thinking about charging some $$ :)
dell_jockey
Enthusiast
Enthusiast
Posts: 767
Joined: Sat Jan 24, 2004 6:56 pm

Post by dell_jockey »

I second Jack, please keep the tab and arrow key functionallity.

As to further ideas for xGrid:

- being able to 'lock' the grid (or parts of the grid), ie. prevent users from changing any formulae (or values that is).
- being able to make formulae 'invisible' for end users, this of course makes sense only in combinationwith lockable grids.

Both points above would be very nice to use xGrid as a closed application delivery platform, where you'd want to prevent that users tamper with the application proper.
cheers,
dell_jockey
________
http://blog.forex-trading-ideas.com
RichardL
Enthusiast
Enthusiast
Posts: 532
Joined: Sat Sep 11, 2004 11:54 am
Location: UK

Post by RichardL »

Xombie,
This is becoming a most interesting project.

I have found a crash mode that you may not be aware of:

Move the cursor to a cell, type the '=' symbol and then use the cursor keys. On my XP Pro installation it causes a crash.

Also, I can compile to an exe and run it, but normal compile or compile+debug flashes the grid and it then disappears.

All the best,
Richard
User avatar
netmaestro
PureBasic Bullfrog
PureBasic Bullfrog
Posts: 8451
Joined: Wed Jul 06, 2005 5:42 am
Location: Fort Nelson, BC, Canada

Post by netmaestro »

Looking very good Xombie! Excellent work, thanks for this.
Last edited by netmaestro on Wed Feb 22, 2006 7:28 am, edited 2 times in total.
BERESHEIT
Xombie
Addict
Addict
Posts: 898
Joined: Thu Jul 01, 2004 2:51 am
Location: Tacoma, WA
Contact:

Post by Xombie »

New version uploaded. While this one may look like it doesn't have very many features, it has a significant rewrite of the internal logic on handling cells and their formulas. I found a bug in how I was handling cells from different sheets and had to redo a lot. Hopefully I didn't add some bugs while I was at it.

So what's new? I added a feature similar to Excel in the demo. A string field above the grid will now display the formula for the selected cell. You should be able to edit the cell directly from this field and have it update the cell. However, if you switch to a different sheet, it will not update the field correctly based on what's selected for the sheet. Sorry. I'll have to add some code to store current cell selection per sheet in order to make that happen so that's a "to be added..." thing.

I've also reworked some of the cell calculation code. Since I was fixing things (see first paragraph), I made it so any cell that gets calculated because of a different cell's formula will not need to be recalculated again until the next time a cell's value is changed. This speeds up cell recalculations as we only need to calculate a cell once.

Copy and paste is working but only the values at the moment. So you can copy and paste from Excel or some other document. Column delimiter is the tab character (chr 9). You should be able to use the ctrl-v and ctrl-c keys to accomplish this as well as right-clicking in the cell/column/row and selecting from the pop-up menu.

I think that's it but it's been a little while. Let me know if something else is broken or just drop by and say howdy ^_^

http://www.seijin.net/Storage/xGrid-code.rar

And, as always, have fun :D

EDIT: Uploaded a new version yet again to fix a copy/paste error. Download again at the same spot, please ^_^ And sorry :oops:
Last edited by Xombie on Fri Nov 11, 2005 7:05 pm, edited 1 time in total.
rsts
Addict
Addict
Posts: 2736
Joined: Wed Aug 24, 2005 8:39 am
Location: Southwest OH - USA

Post by rsts »

Just keeps getting better n better.

Thanks again
jack
Addict
Addict
Posts: 1358
Joined: Fri Apr 25, 2003 11:10 pm

Post by jack »

Xombie this looking really good, how hard would it be to create tables like you can in a wordprocessor ?
tables are very usefull for creating fillable office forms.
thanks for your hard work :D
Xombie
Addict
Addict
Posts: 898
Joined: Thu Jul 01, 2004 2:51 am
Location: Tacoma, WA
Contact:

Post by Xombie »

Hmmm... you'd have to give me a more clear example of what you mean - maybe with pictures? I'm a little slow ^_^ Not really the brightest bulb on the tree (since it's getting closer to Christmas :D )

I think next I will add in your F64 library to xGrid after all. If people don't like that idea, they better speak now or hold their peace. This will help with precision and some extra functions as well as get ready for the (hopefully soon?) PB4 release. Easier to convert existing doubles code over to native code.

And I also need to add in an ex.... ohhh.... just figured out an easy way to store last selected cell per sheets :D That was causing me headaches because when you select cells/columns/rows whatever, I know what the last selected cell was. But if you change sheets, I lose that info. Wouldn't be that important except for the string field up top and notifications. I think I just figured out a really easy and nice way to do it :) We shall see.
Post Reply