From: Ruby Student Date: 2013-06-25T02:51:37+09:00 Subject: Need help adding formatting while using "WriteExcel" for spreadsheet --089e01160c66474c5204dfea106b Content-Type: text/plain; charset=ISO-8859-1 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: 1. How do I select a different color for the first row, not just for a single cell? 2. 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? 3. 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 -- Ruby Student --089e01160c66474c5204dfea106b Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hello Team,

I am putting toget= her a simple program that basically reads some dated data and creates a spe= adsheet. 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 co= de ONLY generates the spreadsheet, I would like to be able to generate the = graph also.

I wanted to know:
  1. How do I s= elect a different color for the first row, not just for a single cell?
  2. 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?
  3. 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

=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D
require 'rubygems'
require 'writeexcel'
require '= pp'

fn =3D "/home/rb/MyData/lpr_time.log.*"

msg= _types =3D ['DATE', 'read', "entry", "delete= ", "alarm", "hotlist", "site"]

fnls =3D Dir["#{fn}"].sort=A0=A0=A0 =A0=A0=A0 # Create fnls = =3D file names list in chronological order
=A0
puts fnls

r =3D= fnls.size=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 # Number of files to be p= rocessed =3D number of rows
msg_count =3D Array.new(r+1) {Array.new(7) {= 0}}=A0=A0=A0 # r+1 =3D 1 additional row for the headings

r =3D 0; c =3D 0;
msg_types.each do |t|
=A0=A0=A0 msg_count[r][c]= =3D t.upcase=A0=A0=A0 # For spreadsheet heading, make it uppercase
=A0= =A0=A0 c +=3D 1
end
# pp msg_count

r +=3D 1=A0=A0=A0 =A0=A0=A0= =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 # Point to row 1
fnls.each do |f| =A0=A0= =A0 =A0=A0=A0 =A0=A0=A0 # Process each file
=A0=A0 lp,lg,dt =3D f.split('.')=A0=A0=A0 =A0=A0=A0 # Get date part= from fn
=A0=A0 6.times do |c|=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 # Process ea= ch column
=A0=A0=A0=A0=A0 if c =3D=3D 0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 # W= hen in r=3D0,c=3D0 Write date
=A0=A0=A0=A0=A0=A0=A0=A0=A0 fdate =3D Date= .parse(dt).strftime("%a%b%d%Y") =A0=A0=A0 # Will go from: 2013061= 1 to 2013-06-11 to TueJun112013
=A0=A0=A0 =A0 msg_count[r][c] =3D fdate=A0=A0 =A0=A0=A0 # Write date to col= umn 0
=A0=A0=A0=A0=A0 else
=A0=A0=A0=A0=A0=A0=A0=A0=A0 mtc =3D `grep = -c #{msg_types[c]} #{f}`=A0=A0=A0 =A0=A0=A0 # Get Message Type Count
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 msg_count[r][c] =3D mtc.to_i
=A0=A0=A0=A0=A0 en= d # if
#pp msg_count
=A0=A0 end # 6.times
=A0=A0 r +=3D 1
end # each
#pp msg_count
<= br>workbook=A0=A0 =3D WriteExcel.new("lpr_wb01.xls")
worksheet= 1 =3D workbook.add_worksheet('Sheet 1')
format=A0=A0=A0=A0 =3D w= orkbook.add_format(:color =3D> 'red', :bold =3D> 1)
format_cmd =3D workbook.add_format(:color =3D> 'blue', :bold =3D= > 1)

worksheet1.write('A1', "WebSphere MQ Weekly Rep= ort", format_cmd)
worksheet1.write('A3', [ msg_count ], for= mat_cmd )
workbook.close


--
Ruby Student
--089e01160c66474c5204dfea106b--