Friday, September 14, 2007

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|
# try to insert the entire array into the row
worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row
# if exception, then insert each cell individually
row.each_with_index do |field, c|
worksheet.Cells(r + 2, c + 1).Value = field

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.

1 comment:

Tej said...

Well I have been trying to query from a SQL server directly to a Excel spreadsheet where i can configure Ruby to directly give me a Chart as an output.
require 'win32ole'
require 'oci8'

ChartTypeVal = -4100;

conn ='scott', 'tiger')
cursor = conn.exec('SELECT OMR FROM tworkorder')

excel ="excel.application")
excel.DisplayAlerts = FALSE;
excel['Visible'] = false;
workbook ='Book1.xls') # `method_missing': Open (WIN32OLERuntimeError)OLE error code:800A03EC in Microsoft Office Excel 'Book1.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

while r = cursor.fetch()
#excel.Range("a1:a5").Select(); This Is where the Error is!!
excelchart = workbook.Charts.Add();
excelchart['Type'] = ChartTypeVal;

30.step(180, 10) do |rot|
excelchart['Rotation'] = rot

excelchart2 = workbook.Charts.Add();
excelchart3 = workbook.Charts.Add();

charts = workbook.Charts
charts.each { |i| puts i }


Would be Glad if you could help me out here.