Ruby & Excel: 911 Characters Only, Please

I was writing some code this week to query an SQL Server database and write the results to an Excel workbook.

I was defining a range in an Excel worksheet and inserting data using code something like this:

worksheet.Range("A2").Resize(data.size, data[0].size).Value = data

The above code defines a range starting at cell A2 and extending the width and length of my 2-dimensional array, then inserts my array into that range of cells.

This worked great -- about 95 percent of the time. But occasionally it would bomb. My debugging uncovered the fact that it would always and only bomb if a string exceeding 911 characters was inserted into a cell.

Some quick googling revealed that this is a known Excel 2003 bug, acknowledged by Microsoft. Microsoft's suggested workaround is "don't do that"; don't try to insert more than 911 characters into a cell as part of an array. Gee, thanks.

I was using the method above because it is significantly faster than inserting data one cell at a time. This isn't a big deal for 100 lines x 10 columns of data, but when you're inserting a million cells of data, there's a huge time difference. I could successfully insert the data one cell at a time, but my users might have to take extended coffee breaks.

I could write the data to a tab-delimited, comma-delimited, or XML text file; then open it in Excel and tweak it if necessary. But I hoped to avoid that if possible.

In case you're wondering, I resolved it with a compromise. Rather than insert the data all at once or cell-by-cell, I inserted it row-by-row:

worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row

This will still raise an exception when attempting to insert more than 911 characters into a cell. But we'll handle those exceptions by inserting that row's data cell-by-cell.

The revised code looks something like this:

data.each_with_index do |row, r|
    begin
        # try to insert the entire array into the row
        worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row
    rescue
        # if exception, then insert each cell individually
        row.each_with_index do |field, c|
            worksheet.Cells(r + 2, c + 1).Value = field
        end
    end
end

It's not as fast as an all-at-once insert, but much faster than a cell-by-cell insert.

By the way, the '+1' offset for column number is because Ruby arrays have zero-based indexes, while Excel's column indexes are 1-based. And the '+2' offset for row number is for the same reason, plus 1 to skip the first row of the worksheet, which contains the field names.

So, insert data into worksheets as arrays when you can. But don't let the 911-Characters Bug bite.