Monday, June 23, 2008

Write to excel file

Found an interesting peice of code to write to xls.
To write directly to an Excel-file (with formatting) or to a
simple csv-file (without formatting).

To write the result of a test to an Excel-File, you can do the
following:

excel = WIN32OLE::new('excel.Application')

workbook = excel.Workbooks.Add

worksheet = workbook.Worksheets(1)

worksheet.SaveAs("test.xls")

#the headline

worksheet.range("a1").value = "Action"

worksheet.range("b1").value = "Result"

#put a "try-catch" around the test, writing either "ok" to a green
cell or the error-text to a red cell:

begin

            …do something…

assert($ie.contains_text("Test"), "ERROR 1!")

            worksheet.range("a1").value = "Action 1"

worksheet.range("b1").value = "ok"

            worksheet.range("b1").Interior['ColorIndex'] = 43 #green

rescue => e

            worksheet.range("b1").value = e.to_s

            worksheet.range("b1").Interior['ColorIndex'] = 46 #red

end

worksheet.columns("a:b").AutoFit

workbook.save

workbook.close

excel.Quit

Writing a csv-File is simpler but lacks the ability to use formatting:

logfile = open("test.csv", "a")

logfile.puts "Action;Result"

begin

            …do something…

assert($ie.contains_text("Test"), "ERROR 1!")

            logRow = "Action 1;"

logRow += "ok"

logfile.puts logRow

rescue => e

            logRow = "Action 1;"

logRow += e

logfile.puts logRow

end

No comments: