Hello Team,
I am putting together a simple program that basically reads some dated
data
and creates a speadsheet. I started using WriteExcel to create the
spreadsheet and I have couple questions that perhaps someone out there
can
help me with.
I am copying the code here but please note that the code ONLY generates
the
spreadsheet, I would like to be able to generate the graph also.
I wanted to know:
- How do I select a different color for the first row, not just for
a
single cell? - How do I set a larger font for the first row, a different size for
the third row and “normal” for the rest of the sheet? - How can I generate the bar graph that corresponds to the
spreadsheet,
like the one shown below, which I manually created?
If someone can point me to some documentation or sample that will be
great.
Thank you
==========================================
require ‘rubygems’
require ‘writeexcel’
require ‘pp’
fn = “/home/rb/MyData/lpr_time.log.*”
msg_types = [‘DATE’, ‘read’, “entry”, “delete”, “alarm”, “hotlist”,
“site”]
fnls = Dir["#{fn}"].sort # Create fnls = file names list in
chronological order
puts fnls
r = fnls.size # Number of files to be processed = number
of
rows
msg_count = Array.new(r+1) {Array.new(7) {0}} # r+1 = 1 additional
row
for the headings
r = 0; c = 0;
msg_types.each do |t|
msg_count[r][c] = t.upcase # For spreadsheet heading, make it
uppercase
c += 1
end
pp msg_count
r += 1 # Point to row 1
fnls.each do |f| # Process each file
lp,lg,dt = f.split(’.’) # Get date part from fn
6.times do |c| # Process each column
if c == 0 # When in r=0,c=0 Write date
fdate = Date.parse(dt).strftime("%a%b%d%Y") # Will go
from:
20130611 to 2013-06-11 to TueJun112013
msg_count[r][c] = fdate # Write date to column 0
else
mtc = grep -c #{msg_types[c]} #{f} # Get Message Type
Count
msg_count[r][c] = mtc.to_i
end # if
#pp msg_count
end # 6.times
r += 1
end # each
#pp msg_count
workbook = WriteExcel.new(“lpr_wb01.xls”)
worksheet1 = workbook.add_worksheet(‘Sheet 1’)
format = workbook.add_format(:color => ‘red’, :bold => 1)
format_cmd = workbook.add_format(:color => ‘blue’, :bold => 1)
worksheet1.write(‘A1’, “WebSphere MQ Weekly Report”, format_cmd)
worksheet1.write(‘A3’, [ msg_count ], format_cmd )
workbook.close