CODEWITHSUNDEEP
mysqlrubyexcelrubygems
Mike
Mike

Reputation: 1310

Number Stored as Text when Writing Query to a Worksheet

I am creating a report using the following gems:

require "mysql2"
require "watir"
require "io/console"
require "writeexcel"

After I query a database with mysql2 and convert the query into a multidimensional array like so:

Mysql2::Client.default_query_options.merge!(:as => :array)

mysql = Mysql2::Client.new(:host => "01.02.03.405", :username => "user", :password => "pass123", :database => "db")

report = mysql.query("SELECT ... ASC;")

arr = []
report.each {|row| arr << row}

and then finally write the data to an Excel spreadsheet like so:

workbook = WriteExcel.new("File.xls")
worksheet = workbook.add_worksheet(sheetname = "Report")

header = ["Column A Title", ... , "Column N Title"]

worksheet.write_row("A1", header)
worksheet.write_col("A2", arr)

workbook.close

when I open the file in the latest edition of Excel for OSX (Office 365) I get the following error for every cell containing mostly numerals:

Excel error.

This report has a target audience that may become distracted with such an error.

I have attempted all the .set_num_format enumerable methods found in the documentation for writeexcel here.

How can I create a report with columns that contain special characters and numerals, such as currency, with write excel?

Should I look into utilizing another gem entirely?

Upvotes: 0

Views: 139

Answers (1)

B540Glenn
B540Glenn

Reputation: 409

Define the format after you create the worksheet.

format01 = workbook.add_format  
format01.set_num_format('#,##0.00')

then write the column with the format.

worksheet.write_col("A2", arr, format01)

Since I'm not a Ruby user, this is just a S.W.A.G.

Upvotes: 1

Related Questions