xGrid (Spreadsheet / Grid Control) for PB4

[Xombie]

yes, huge work, congrats.

i noticed a little bug about formulas:

for example '=A4+C5' is ok but not with 'double letters column' like '=AB4+CE5'.

Thanks a lot That bug should be an easy fix so I'll get on it.

xombie,

Your new xGrid is GREAT!

I have one suggestion to make it easier to resize the column width.
Right now, I have to put my cursor exactly on the dividing line between two columns to be able to resize a column.
Since the line is only one pixel wide, this is very hard!
Please consider allowing a "margin" on either side of the divider, maybe 10 pixels or so.

This idea isn't my original; I "borrowed" it from Microsoft's Excel spreadsheet.
But there's no shame in sharing a good idea, is there?

Regards,
Eric

Thanks again

I had actually thought I did put a margin. I'll check it out and then increase it a bit.

Is there any other functionality that people would like added? Since I don't actually use the code myself, I only borrowed the basic ideas from Excel (see - so no problems, ebs ). If there's any nifty ideas y'all want added, let me know and I'll see what I can do with it.

[Flype]

maybe the ability to load open-office sheets.
they are xml based so it should not be too hard.

[Xombie]

I've updated to fix the formulas not recognizing cells from double letter columns. Also a few other minor things were tweaked/fixed.

I'm not finding any good OpenOffice Calc files to test. Do you have a simple one I could try?

http://www.seijin.net/Storage/Code/xGrid/xGrid.zip

[aszid]

Hiya,

I've been watching this project with a good deal of interest. I often deal with spreadsheets and tons of data at work, and some things, excel just doesn't do easily. I've started playing with your code a bit, nothing too extravagant as of yet, but i have added the ability to load csv files. I've moved these changes into the latest version of the code that you've posted, and i thought i would share. I didn't spend a huge amount of time on this, so there may be a few bugs, but from my testing so far, it seems to be working well.

Anyhow, you can find that here: http://www.aszid.com/xGrid.zip


It would be nice if you could merge these changes with your currect version, and perhaps keep them in the code for future releases, but it's your project so that is up to you

I haven't the time at the moment to make some of the other features i've been wanting to create, but when i do make them, i will be sure to share

[Xombie]

Neat, I'll take a look. However, you know you can always just request new features and I'll do my best to implement them.

[aszid]

well, i figured i *could* just request features... but what's the fun in that?

I've done my best to avoid mucking up any of your code when adding to it. for that change, i pretty much only modified one line of code (making the size variables for the table global), other than that it's just a few bits here and there that i've added to it. I've also for the most part, indented my bits differently so they'd be easy to find. (didn't really work with the loading procedure, but the rest is indented a bit less than the norm)


Anyhow... since you asked, these are a few of the things that i was planning on adding:

- easy Deduping of data - take 2 sheets, remove any duplicate rows
- easy merging - take 2+ sheets, and merge them up based on one or more data rows that match up (for example names, or email addresses)
- rows > 65536 - nothing is worse than importing a csv or other DB dump only to have excel be incapable of handling the full file.

There's prolly a few more things, but those are the few that pop up in my head.


I have already started coding a function to export .csv files, to go along with the loading code. Maybe if i share more changes, i'll just post the code with directions for where to insert it... i don't want to step on your feet, and i know version control without CVS can be a pain in the butt.

[Xombie]

#3 is easy - there is no hard coded limit to columns or rows. I tested it with 990,000 rows and it did okay. I mean, I didn't actually put data into that many rows but I did get it to display them. It's just a matter of changing the limit in GridGadget(). I will put in a constant so that can be easily changed for a project.

If you don't mind, I'll also rework the *.csv importing a little bit and add in an export function as well.

If you can give me more information on the first two, I can put those in. Give me a small example and then tell me what something like that would be called. And also let me know if there's an Excel equivalent that I can snoop at and try to imitate.

Thanks for all the suggestions. I've said it before - I don't really use the code for anything so it's not easy for me to come up with things to add.

[aszid]

Groovy.

It doesn't bother me any if you want to tweak the csv importing code, as i said i threw it together pretty quick, so i'm not too attached to it. I figure you can prolly tweak it to work better with your code anyhow, as i kinda just stumbled my way through it, figuring out your functions as i went along

unfortunatly, as far as i know there are no *good* excel equivilants of the other features i wanted. I'll try to explain them a bit clearer though:

- Deduping:
Basically, this is taking 2 spreadsheets with the same type of data (same columns), and merging the 2 of them, removing any duplicate rows. I'm actually surprised that excel cannot do this easily... but the closest excel has is filtering, which will let you *hide* duplicate data in one spreadsheet after manually combining them. Deduping is the best term i know for this process, however there may be something better.

- Merging:
The goal of this one, is to take 2 spreadsheet with different data columns and combine them based off of 1 or more key columns that contain equivilant data.

For example, I have 2 files, with these columns:

File 1: First Name, Last Name, Address
File 2: First Name, Last Name, Phone number

Both files contain information for *some* of the same people, but do not necassarily contain information for all of the people that are in the other file. Ideally this feature would take the 2 spreadsheets, and combine them into one spreadsheet with the following columns:

First Name, Last Name, Address, Phone number

while lining up all of the people that exist in both files, and just leaving empty the fields where data is not present in the files.

Once again, i'm not sure what this feature would be called aside from "merging"... maybe "Smart Merge"? Once again, this is something that i do not know of an *easy* way to do in excel, but it is something that i find myself having to do the hard way quite often.

Quite often i throw together a 1 time use PB program to do all of the work for me... but in those instances i'm generally lining up data by a numeric field, which makes it much much easier to do than text would be.
-----


Let me know if you have any questions or need more clarification. I could prolly put together a few example CSV files if you would like as well.


Thanks!

[Xombie]

I've added a couple of things to this release.

Two constants control the maximum number of rows (#xGrid_MaxRows) and columns (#xGrid_MaxColumns) allowed for all of the grids.

There is now a simple import/export *.csv routine that will need to be tested. Only works for the current sheet.

A new event is added - A 'percent complete' event. It's only added to the *.csv import routine and I'll probably end up changing it a lot more. It'll basically report the progress of a function so you can have your own routine that does something like display a progress bar or etc...

I'll try tackling your merging/removing duplicates stuff next to see how it looks.

[PB]

> Only problem is, no cursor/mouse pointer while over the grid area.
> Must be my setup, will play around some more.

Not your setup: I get no cursor/mouse either on my system.

[Xombie]

a tip for people using the PB IDE.
assuming that you created a folder "C:\Program Files\PureBasic\Examples\xGrid" and extracted xGrid 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

PB - on the first page of this post there are some tips on getting the cursors included at compiling time. I use japbe but jack has the tips for the PB editor. Do those work for you?

[PB]

I'll try it later Xombie (leaving for work now) but I can see that I have nothing
in the Examples folder that you mentioned... when I unzipped the archive there
is nothing with a path like that, or a mention of needing to put anything in the
Examples folder.

[Xombie]

Weird! Just in case - the latest should be at...

http://www.seijin.net/Storage/Code/xGrid/xGrid.zip

... let me know if it's missing those cursors in the zip file.

[aszid]

groovy. I've taken a quick look at the import CSV function.. and found a small bug that likely also existed in my code.

If i load a large file, that fills up a lot of cells, then load a small file that only has a few lines, all of the data from the first imported file is still there.

It would prolly be easiest to clear the entire sheet before loading the new data, and this would go away.

[Xombie]

Ah, well, it wasn't a bug exactly. Just not sure what the behavior should be. The code simply replaces existing cells with the new information. I should I have it clear all sheets and begin again? Like a "File->New" operation?