Forum: Ruby RubyExcel class. Useful?

I've managed to create a (relatively) stable data-processing class which
behaves a bit like Excel, but with some of Ruby's awesome syntax styles.
I've used it on a few projects now so I'm happy with how it works,
although I'm always trying to improve it.

The 2 files are intended to be placed in the same directory. I might
break it down further in a later build.

This was written to be documented with "yard" if that makes the random
layout easier on the eyes.

The intention is to help simplify scripts which have to sift through
table data, by allowing the user to work with a variety of ways to
access
and loop though the data. The class is built around the assumption that
it is dealing with a 2D array of data with headers in the first row,
which it generally tries to avoid touching while looping through the
data. The
indexing is 1-based to mimic Excel's API.

There are multiple ways to reference the data. You can do this by Row,
Column, Cell indices, Ranges, and individual addresses using [].

I've found this a happy medium between the slowness of Excel's clunky
VBA, and the frustration of having to constantly move Ruby's Array
indices around to avoid affecting the headers.

If I can get rid of the bugs in this and add enough useful functionality
then I might publish it as a gem for anyone who regularly has to deal
with tables (HTML in my case) and wants a simple tool to do it with.

Any comments, advice, requests or constructive criticism are welcome!

Special thanks to Robert Klemme for providing the impetus to get this
started.

Oops, I had a broken line in there.

This:
ruby_XL.send( self.class.to_sym, self.index ,ruby_XL )

Doesn't cause an error anymore if changed to this:
ruby_XL.send( self.class.name[/row|column/i].downcase.to_sym, self.index 
)

Are there tests that demonstrate the use of this?

Good point Andrew. Writing a thorough demo is a good way to 
systematically test every method as well.
I just tried to demo some of the newer stuff I added and I've already 
found a couple of bugs where I have class methods referenced rather than 
the mix-in methods I changed to recently. I'll run a thorough series of 
tests, build up some examples, and then try again.

This covers some of the basics anyway:

irb(main):002:0> re = RubyExcel.new.test
=> RubyExcel => columns: 4, rows: 6, values: 24
irb(main):003:0> puts re
a       b       c       d
e       f       g       h
i       j       k       l
m       n       o       p
q       r       s       t
u       v       w       x
=> nil
irb(main):004:0> re['B2'] = 'z'
=> "z"
irb(main):005:0> re.column_by_header('c').map! { |el| el.next }
=> Class: RubyExcel::Column, Index: C
irb(main):006:0> puts re
a       b       d       d
e       z       h       h
i       j       l       l
m       n       p       p
q       r       t       t
u       v       x       x
=> nil
irb(main):007:0> re.delete_column('D')
=> RubyExcel => columns: 3, rows: 6, values: 18
irb(main):008:0> re.cell(2,3).value = 'z'
=> "z"
irb(main):009:0> puts re
a       b       d
e       z       z
i       j       l
m       n       p
q       r       t
u       v       x
=> nil
irb(main):010:0> re += [ %w(1 2 3) ]
=> RubyExcel => columns: 3, rows: 7, values: 21
irb(main):011:0> puts re
a       b       d
e       z       z
i       j       l
m       n       p
q       r       t
u       v       x
1       2       3
=> nil
irb(main):012:0> re.filter!('b') { |el| el =~ /[a-r]/ }
=> RubyExcel => columns: 3, rows: 4, values: 12
irb(main):013:0> puts re
a       b       d
i       j       l
m       n       p
q       r       t
=> nil
irb(main):014:0> re.row(2)['A'] =  'q'
=> "q"
irb(main):015:0> re.summarise 'a'
=> {"q"=>2, "m"=>1}
irb(main):016:0> puts re
a       b       d
q       j       l
m       n       p
q       r       t
=> nil
irb(main):017:0> puts re.uniq! 'a'
a       b       d
q       j       l
m       n       p
=> nil

Thanks. One thing that strikes me, looking through the code, is that 
Column
and Row are very similar. It wouldn't surprise me if you could pull a 
lot
more logic up into the base class. I also have the vague feeling that 
this
could generally be done with a lot less code. But talk is cheap, so take
that with a grain of salt. What are you using this for? It's certainly 
an
interesting exercise, if nothing else.

Looks nice! Could you toss it up on github?

Also I'd recommend calling it something more generic like
RubySpreadsheet or Tabular, since it is neither interfacing with excel
nor reading excel files.

martin

Thanks for the feedback. I'd certainly like to make the code concise and 
efficient. I'm trying to refactor regularly but I also add new options 
and variations whenever I think of them, so it's a bit of a struggle in 
both directions.

I've tried to use the "Write Once / DRY" approach, which is why I've 
started to put certain shared methods into the Helper module, but I'm 
still a n00b at this so I'm sure I'm making parts needlessly 
complicated.

Thanks for the name suggestions. I picked the name "RubyExcel" out of 
the air so I'm open to changing it. The reason I went with that name was 
because I'm trying to make something with the best of Excel's and Ruby's 
APIs together.

I'll have to learn how to use github now :) I'll weed out the bugs that 
I've noticed and then figure out how to upload there.

I wrote this because I work with browser-based reports which appear in 
HTML tables. I then have to extract the data into excel. This is a tool 
to handle the data in the interim, and to make all of the changes to the 
data in the memory, using an API which allows me to easily. read back 
what it's doing.
I'll add more detail on usage cases later.

Thanks for the advice, guys. I'll act on this and come back with some 
updates.

On Fri, Mar 8, 2013 at 4:49 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> I've managed to create a (relatively) stable data-processing class which
> behaves a bit like Excel, but with some of Ruby's awesome syntax styles.
> I've used it on a few projects now so I'm happy with how it works,
> although I'm always trying to improve it.

Good, I see you're making progress!

One thing I noticed on quick glancing is that you are overusing string
interpolation.

self =~ "#{s}" -> self =~ s.to_s

if "#{ val }".empty? -> if val.to_s.empty?

In []= you are evaluating the same condition twice.

I also share Andrew's feeling that you might get away with less code.
Somehow it feels like you put too much into the class.  For example,
methods #strip! and #upcase! deal with individual cell manipulations.
They may make sense as convenience methods but would first try to
focus on the core functionality of the spreadsheet: inserting and
removing rows and columns. And method #test does not belong there this
should go into a test class.

Another thing that stroke me odd is that you assume there will always
be one header row.  IMHO that is a too specific assumption.  If any I
would only put this in a subclass or a wrapper class.

Finally I believe you are working too much with Arrays.  For example
in method #+ instances of Row, Column and RubyExcel are converted to
Arrays before they are appended.  Since these classes belong to this
library you know how their internals look and you should have a better
way to transfer their data over to this sheet.  Same for #- and
probably others as well.

Kind regards

robert

Thanks for your input, Robert.

I did wonder whether I should convert the underlying dataset into an 
Array rather than using a Hash, since spreadsheets are "structured" and 
I find the easiest way to manipulate the structure is with Array 
methods. Enumerable seems to be Array-based as well, and I'm still 
rather hazy on when to override methods like "map", or when to rely on 
the methods already available through "each".
Still, I've learned a lot about Hashes while writing this code, so even 
if I do abandon their use for the main data storage I'll still find good 
use for them elsewhere.

My reasoning behind the prevalence of headers is simply that if you 
wanted data without headers you'd just use arrays rather than this 
class. One of the big things I find helpful with this is that code is 
much more readable if I can reference a header rather than an index.
I'm not sure what a test class or wrapper class is. I'll look them up.

I see your point about to_s. I suppose I should differentiate between 
using interpolation for multiple variables and to_s for single cases.

The +, -, and << methods are recent additions; mostly because I only 
just learned that you can define these. I'm sure there are multiple ways 
to write these; my first attempt was very poor in performance... and my 
thinking was to avoid re-inventing the wheel by using the Array methods 
written by someone much smarter than me :)

I hadn't realised that I was evaluating "empty?" twice in "[]=". That 
was a performance-increasing experiment I'd tried to avoid recalculating 
the dimensions after every write operation. It made quite a difference 
at the time and I hadn't looked into simplifying it yet.

All in all, this still needs a lot of work to make it useful; but now 
that I have a better feel of what the weaknesses and strengths are, I 
hope to improve on this starting point and eventually build something 
genuinely useful to others as well as myself.

I'd be particularly interested on the question of Array vs Hash for the 
internals. Hash is great because of the simplicity of addresses and its 
efficient way of coping with blank space, but Arrays can keep their 
"form" much more effectively and already support things like sorting, 
rows, and columns.

On Sat, Mar 9, 2013 at 4:31 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Thanks for your input, Robert.

You're welcome!

> I did wonder whether I should convert the underlying dataset into an
> Array rather than using a Hash, since spreadsheets are "structured" and
> I find the easiest way to manipulate the structure is with Array
> methods.

It does not really matter what you do.  You could even use a hybrid
approach where you start with an Array based storage and exchange it
with a Hash based storage once sparseness is too large (for your
particular measure of "too large").  If you follow the layered
approach (see at end) you could have two implementations of the plain
data store which have the exact same API but one uses a Hash
internally and the other on an Array...

> Enumerable seems to be Array-based as well, and I'm still

No, not at all.  Enumerable is just a module which relies solely on
the existence of a method #each.  It's as simple as

irb(main):001:0> class X
irb(main):002:1> def each; yield 1; self end
irb(main):003:1> include Enumerable
irb(main):004:1> end
=> X
irb(main):005:0> x = X.new
=> #<X:0x802ec310>
irb(main):006:0> x.to_a
=> [1]
irb(main):007:0> x.select {|n| n.odd?}
=> [1]
irb(main):008:0> x.select {|n| n.even?}
=> []
irb(main):009:0> x.find {|n| n > 0}
=> 1

Or a simplistic integer range:

irb(main):014:0> class IntRange
irb(main):015:1> include Enumerable
irb(main):016:1> def initialize(a, b)
irb(main):017:2> @low, @high = [Integer(a), Integer(b)].sort
irb(main):018:2> end
irb(main):019:1> def each
irb(main):020:2> n = @low
irb(main):021:2> while n < @high
irb(main):022:3> yield n
irb(main):023:3> n += 1
irb(main):024:3> end
irb(main):025:2> self
irb(main):026:2> end
irb(main):027:1> end
=> nil
irb(main):028:0> ir = IntRange.new 3, 8
=> #<IntRange:0x80280048 @low=3, @high=8>
irb(main):029:0> ir.to_a
=> [3, 4, 5, 6, 7]
irb(main):030:0> ir.each {|x| p x}
3
4
5
6
7
=> #<IntRange:0x80280048 @low=3, @high=8>

No Arrays around. :-)

> rather hazy on when to override methods like "map", or when to rely on
> the methods already available through "each".

You should normally not override Array methods.  You generally
shouldn't inherit from Array either.  Those core classes are best used
through delegation.

> Still, I've learned a lot about Hashes while writing this code, so even
> if I do abandon their use for the main data storage I'll still find good
> use for them elsewhere.

That's good!  I am glad you see it that way.  Others might view these
exercises as useless detours - but they underestimate the learning
effects which come from that.  You certainly learned a lot more than
by placing questions for every detailed question that occurred to you
as a few other members of the community seem to have chosen to do
recently.

> My reasoning behind the prevalence of headers is simply that if you
> wanted data without headers you'd just use arrays rather than this
> class. One of the big things I find helpful with this is that code is
> much more readable if I can reference a header rather than an index.

Then I would at least make the number of header rows and header
columns a property of the individual instance - and not as a constant
in the implementation.  Still, I believe that with introduction of the
concept of "headers" in this class you may make things too complex too
fast.

> I'm not sure what a test class or wrapper class is. I'll look them up.

A test class would be a class implementing unit tests.  A wrapper
class simply wraps around your class RubyExcel in much the same way
that RubyExcel wraps a Hash.  In other words: it presents a different
abstraction.  It's a general approach in software engineering to
create several layers of abstractions which makes it easier to deal
with only few aspects on one layer.  The ISO 7 layer model is a famous
representative of that approach.
http://www.technology-training.co.uk/understanding...

> I see your point about to_s. I suppose I should differentiate between
> using interpolation for multiple variables and to_s for single cases.

+1

> The +, -, and << methods are recent additions; mostly because I only
> just learned that you can define these. I'm sure there are multiple ways
> to write these;

I wrote about numeric operators in Ruby a while back:
http://blog.rubybestpractices.com/posts/rklemme/01...

> my first attempt was very poor in performance... and my
> thinking was to avoid re-inventing the wheel by using the Array methods
> written by someone much smarter than me :)

... which is perfectly understandable and OK.  In this case the
conversion to an Array based structure might burn a lot of memory
though.

> All in all, this still needs a lot of work to make it useful; but now
> that I have a better feel of what the weaknesses and strengths are, I
> hope to improve on this starting point and eventually build something
> genuinely useful to others as well as myself.

That's a good approach.

> I'd be particularly interested on the question of Array vs Hash for the
> internals. Hash is great because of the simplicity of addresses and its
> efficient way of coping with blank space, but Arrays can keep their
> "form" much more effectively and already support things like sorting,
> rows, and columns.

In the end it does not matter that much what you use internally for
representation.  The important bit is to use the proper API to your
storage to allow for a consistent view of the model and easy of use.
For the moment I'd stick with Hash but it may make sense to use a
layered approach: split class RubyExcel into (at least) two where one
is only responsible for providing a consistent API to your data with
the minimal operations needed to make it work.  Use that internally as
storage and put everything else like header handling, those
convenience methods mentioned or reading from file and writing to file
in the wrapper class.  That way you get a clean separation of
concerns: you have a proper abstraction of the storage and you get a
second layer which adds all the whistles and bells you need to work
efficiently with it (like treading header rows and columns
differently).

Kind regards

robert

Wow! Lots of good advice, as usual :) It'll take me a while to process 
all this so I'll just get on with it.

Incidentally, I hadn't seen this before: 
http://ruby-doc.org/stdlib-1.9.3/libdoc/csv/rdoc/C...
It looks similar in many ways to what I'm trying to build. I'll have to 
see what I can learn from it as well.

On Sat, Mar 9, 2013 at 10:05 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Wow! Lots of good advice, as usual :) It'll take me a while to process
> all this so I'll just get on with it.

I have attached the skeleton of a class for spreadsheet storage to
illustrate what I was saying earlier.  Note for example that this
class does not have textual cell indexes.  I thought it might be
easier to use int ranges when focusing on the basic manipulations
which will move cells.  Depending on storage chosen that may be tricky
enough already.

A next step could be to create a subclass which allows for
CellReferences to register with the class and have them automatically
update on successful move operations.  I do not know whether you need
that but when trying to recreate a spreadsheet application in Ruby
that might be an interesting feature to have.

As step towards the user your spreadsheet class could wrap the storage
class and translate indexes back and forth so the user of your class
would only see addresses like "A4" instead of [0, 3].

> Incidentally, I hadn't seen this before:
> http://ruby-doc.org/stdlib-1.9.3/libdoc/csv/rdoc/C...
> It looks similar in many ways to what I'm trying to build. I'll have to
> see what I can learn from it as well.

Yes, but keep in mind that CSV has a different mental model: in CSV a
row is typically a record, so all the data in a row belongs together.
In a spread sheet both dimensions are much more equal.  With CSV you
won't do operations like inserting a rectangular area somewhere in
between while that could be quite common with spread sheets.

Kind regards

robert

Ok... having looked over what I want to accomplish with this I've 
decided to go with the following:

Completely rewrite using Arrays as primary internal storage (there's 
always the option to swap with or include Hash later). I rarely deal 
with large gaps in my data so this seems the best approach for now.

Rebuild the internal classes for the different functions (data storage, 
sections, element handling, accessing, etc.) within a class which 
controls the API. I want to try and separate the different types of 
logic and make this easier to work on.

Plan the layout before writing any code this time, and rewrite as 
idiomatically as possible rather than copy-pasting the older code.

This is going to take some time, due to my other work. Still, once it's 
complete it should improve the speed with which I can can write related 
code.
Apologies to anyone who wanted to play around with this anytime soon, 
but I'd rather do it right than do it quickly.

Aha! I have learned how to use Github.

My hideous-looking, still under-construction code is here:
https://github.com/VirtuosoJoel/RubyExcel

I've split it into something more resembling an Excel workbook, and 
tried to reduce the redundancy of address-based code by adding a module 
called Address.

I'll keep hammering bits onto it, cutting away the dross, and testing it 
until it turns into a reasonably serviceable tool.

As always, any advice is welcome!

On Wed, Mar 13, 2013 at 4:45 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Aha! I have learned how to use Github.

+1

> As always, any advice is welcome!
This doesn't look good:
def range=( first_cell, last_cell=nil, val )

Calls will look like this

sheet.range= 1, 2, 3

There is no visual separation between coordinates and the value like
there is with []=.

I always find constructs like this suspicious:

def cell( row, col )
  Element.new( self, indices_to_address( row, col ) )
end

The purpose of a constructor is to construct something and return it
so the caller can do something with it.  If you do not do that
everybody is left wondering what happens to the newly created
instance.  If you need it only temporary then another method name
would be better.  Or change it so that not Element stores itself
somewhere in "self" but rather "self" itself.

Kind regards

robert

Robert Klemme wrote in post #1101459:
> This doesn't look good:
> def range=( first_cell, last_cell=nil, val )
> There is no visual separation between coordinates and the value like
> there is with []=.

Thanks for spotting that, I wasn't sure how to use it. I've completely 
removed that method; range now returns an object and range.value can get 
and set its content.

>
> I always find constructs like this suspicious:
>
> def cell( row, col )
>   Element.new( self, indices_to_address( row, col ) )
> end
>
> The purpose of a constructor is to construct something and return it
> so the caller can do something with it.  If you do not do that
> everybody is left wondering what happens to the newly created
> instance.  If you need it only temporary then another method name
> would be better.  Or change it so that not Element stores itself
> somewhere in "self" but rather "self" itself.

I'm not really sure what you mean by this. My intention is that cell and 
cells are like alises for range, but you can select by 1-based indices 
(again, copying excel). Range is also capable of referring to more than 
1 cell, and can take cells as arguments. This allow for purely numerical 
selection of any area, I've allowed columns to be selected numerically 
as well.

irb(main):053:0> s.cell(1,1)
=> RubyExcel::Sheet::Element: A1
irb(main):054:0> s.cell(1,1).value
=> "A1"

On Wed, Mar 13, 2013 at 6:18 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Robert Klemme wrote in post #1101459:

>> would be better.  Or change it so that not Element stores itself
>> somewhere in "self" but rather "self" itself.
>
> I'm not really sure what you mean by this. My intention is that cell and
> cells are like alises for range, but you can select by 1-based indices
> (again, copying excel). Range is also capable of referring to more than
> 1 cell, and can take cells as arguments. This allow for purely numerical
> selection of any area, I've allowed columns to be selected numerically
> as well.

It's about the constructor (Element.new) - not addressing methods.
Bottom line: don't abuse constructors for doing algorithmic things.
Constructors are for constructing something so it is in a consistent
state and can be worked with. Placing the complete logic in a
constructor will prevent flexible use of classes.

Cheers

robert

Maybe I'm just being stupid, but I'm having trouble understanding your 
meaning. Do you mean that I shouldn't always use an "Element" class, and 
just work directly with the data?

So, for instance, "cell" and "[]" could reference the data directly, 
whereas "range" could be an object to be used in different ways?

On Wed, Mar 13, 2013 at 8:50 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Maybe I'm just being stupid, but I'm having trouble understanding your
> meaning. Do you mean that I shouldn't always use an "Element" class, and
> just work directly with the data?

No, I was being stupid.  Somehow I overlooked that the whole point of
the method was to return the Element instance.  Just forget it.  Sorry
for the noise.

Cheers

robert

Rare honesty, admirable :)

Took a quick look at the code; this bit looks wrong:

def delete(ref)
...
@sheets.delete_at( ref + 1 )

I think it should be (ref - 1). Which brings me to my main point, that
1-indexing is tricky and should be bolstered by a lot of tests in case
you inadvertently slip up. Also @sheets.delete_at changes the array
whereas @sheets.reject does not; it should be reject! for consistency.
(Again, tests would have caught this.)

martin

Thanks for that, I hadn't gotten around to doing much testing with 
sheets yet.

The 1-based indexing can be tricky to keep working as a standard, but 
one thing I thought this might help with is transitioning between VBA 
and Ruby. If the addressing and objects look similar, you don't need to 
change much of the individual project detail.
I have some VBA code which I think would be much simpler in Ruby, and if 
I can set this up I might even be able to write something to translate 
VBA into Ruby working with this class. That's more of a vague idea for a 
future project though.

I do need to add more checks, particularly validating input. What I want 
to do is to try and have most validation pass through the Address 
module, so I can make any changes there rather than running around the 
entire project looking for duplicate code.

This is still very much under construction, but it's good practice for 
me to use Github; and it's amazingly useful to have advice from more 
experienced programmers while I struggle to build a viable tool. Thanks 
again for your time :)

Uh oh, I think I've backed myself into a corner...

I assume that I'll need some sort of "dup" method in order to easily 
differentiate between modifying an object in place and returning a copy. 
However, in my attempts to interlink all the elements, I think I've 
created a situation where I'll end up in a loop.

RubyExcel holds "child" Sheets
Sheet links to "parent" RubyExcel
I know they aren't really parent and child, but I wasn't sure of the 
best way to link them together.

In order to dup RubyExcel I need to dup each sheet.
In order to dup a Sheet I need to dup RubyExcel.

I've ended up with a circular reference and I'm not sure of the best way 
to proceed from here.

Also, is it sensible to have the namespaces stacked the way I've done 
it? Like RubyExcel::Sheet::Element.

On Thu, Mar 14, 2013 at 11:04 AM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Uh oh, I think I've backed myself into a corner...

:-)

> In order to dup RubyExcel I need to dup each sheet.
> In order to dup a Sheet I need to dup RubyExcel.
>
> I've ended up with a circular reference and I'm not sure of the best way
> to proceed from here.

It may be an indication that you got your design wrong.  Why did you
think you need the circular references in the first place?

> Also, is it sensible to have the namespaces stacked the way I've done
> it? Like RubyExcel::Sheet::Element.

I'd probably only have one level of namespaces for this, e.g. module
RubyExcel and put all classes in there.  You won't have too many
classes to distribute across a multitude of namespaces.

Kind regards

robert

Robert Klemme wrote in post #1101581:
>> I've ended up with a circular reference and I'm not sure of the best way
>> to proceed from here.
>
> It may be an indication that you got your design wrong.  Why did you
> think you need the circular references in the first place?

I need a Sheet to belong to a workbook, but also be able to be copied or 
moved from one workbook to another. I need to be able to find the sheet 
from the workbook, and find the workbook from the sheet.
Originally I thought that would be some sort of inheritance chain, but I 
can't use inheritance because Sheet is not a type of Workbook.

> I'd probably only have one level of namespaces for this, e.g. module
> RubyExcel and put all classes in there.  You won't have too many
> classes to distribute across a multitude of namespaces.

Perhaps a RubyExcel Module with the first class called Workbook would be 
more suitable.

Martin DeMello wrote in post #1101514:
> Took a quick look at the code; this bit looks wrong:
>
> def delete(ref)
> ...
> @sheets.delete_at( ref + 1 )
>
> I think it should be (ref - 1). Which brings me to my main point, that
> 1-indexing is tricky and should be bolstered by a lot of tests in case
> you inadvertently slip up. Also @sheets.delete_at changes the array
> whereas @sheets.reject does not; it should be reject! for consistency.
> (Again, tests would have caught this.)
>
> martin

When I "delete" a sheet, do I just need to stop referring to it as an 
object? As I understand it; once it's removed from "@sheets", it should 
be garbage collected.

On Thu, Mar 14, 2013 at 1:37 PM, Joel Pearson <lists@ruby-forum.com> 
wrote:
> Robert Klemme wrote in post #1101581:
>>> I've ended up with a circular reference and I'm not sure of the best way
>>> to proceed from here.
>>
>> It may be an indication that you got your design wrong.  Why did you
>> think you need the circular references in the first place?
>
> I need a Sheet to belong to a workbook, but also be able to be copied or
> moved from one workbook to another. I need to be able to find the sheet
> from the workbook,

OK, that sounds reasonable.

> and find the workbook from the sheet.

What functionality for?

> Originally I thought that would be some sort of inheritance chain, but I
> can't use inheritance because Sheet is not a type of Workbook.

This is certainly not a case for inheritance.

Cheers

robert

Robert Klemme wrote in post #1101592:
>> and find the workbook from the sheet.
>
> What functionality for?

It's quite useful in case I lose track of the object which is the 
"parent" of the sheet. Consider this example of the current code:

#Shortcut to avoid having to go through the workbook every time
sheet = RubyExcel.new.load data
#But now I want the workbook:
myRubyExcel = sheet.ruby_excel

I wanted something akin to Excel VBA's approach
ActiveSheet.parent
=> workbook

I think I've found a way around this.

I've made Sheet's "workbook" an accessor rather than read-only. When I 
dup a workbook, I dup each sheet, and then overwrite the "workbook" 
attribute with the new one.

When I "dup" a Sheet, it has the same parent as the original, so I could 
overwrite the original if required (like +=).

The RubyExcel Module is now bashed into something resembling operational 
status. I still have plenty of ideas to implement with it, but it's 
finally something I can use (and have used) to handle data and output it 
into a neat excel format.
https://github.com/VirtuosoJoel/RubyExcel/

I think the next thing I need to do is try and add documentation to the 
code and upload it (YARD-style, I think) to github as well. Poorly 
documented APIs are the bane of the earnest programmer :)

Hopefully I've learned something along the way and not made a complete 
mess of it; but if any intrepid code explorers spot any glaring 
mistakes, correction would be appreciated.

Thanks,

Joel

I've now learned how to publish this as a gem. Still lacking proper 
documentation at present, but it'll let me work on it and test it out 
from multiple locations.
It's currently living on RubyGems here: 
https://rubygems.org/gems/rubyexcel
Minutes after I uploaded it the downloads went to 4. Not sure if that's 
genuine downloads or some automatic thing.
Anyway, feel free to have a play with it and see if it does anything 
unusual or lacks any features which would be handy. I'll add any 
requests onto my to-do list... or to my list of things to learn how to 
do :)

Hi all, just an update for anyone who's interested.

I've now managed to get this gem working sufficiently well to use it in 
a few of my projects.

I've incorporated some WIN32OLE tools for things that I do a lot, also 
lending credence to the name RubyExcel.

I think I've weeded out most of the bugs, and I've written a more 
comprehensive guide which is the ReadMe at Github.

I've tried to take on board all the suggestions so far, although I've 
dropped the header columns idea as tricky to implement alongside header 
rows; and currently of no use to me. Maybe I'll be able to add them 
later.

I've now managed to implement all the basics to the point that I'm just 
adding new bits as and when I need them. The core sections all seem to 
be working as intended, although of course it's still possible to cause 
problems by feeding it invalid input. I might add more extensive 
validation if that becomes enough of an issue.

I've even managed to get a wobbly looking bit of recursive code to 
import a nested Hash into a table array; which is something I'm having 
to do at work with increasing regularity. I spent long hours into the 
night trying to build it and I'm still not entirely sure how it works. 
It was more of a half-understood trial and error thing.
Actually if anyone can come up with a better way to do this I'd be 
fascinated, since I always have trouble visualising recursive methods. 
I'll attach the relevant code to this post.

The gem still lives here: https://rubygems.org/gems/rubyexcel
The code still lives here: https://github.com/VirtuosoJoel/RubyExcel

I still need to work out what (if any) documentation-style comments to 
add to the code itself. What do people use the most? The rubygems 
documentation link says it's a "YARD documentation server", but the 
local install only dropped some ".ri" files, and I've only just learned 
how to use those.