From 223d4baee0f9aeb3485321d6f4128eb34e0dfce3 Mon Sep 17 00:00:00 2001 From: Jurriaan Pruis Date: Fri, 2 Mar 2012 10:19:31 +0100 Subject: Default to 1900 date system Office 2011 for Mac uses the 1900 system by default see http://www.officeformachelp.com/2010/10/excel-2011-defaults-to-1900-date-system/ for more info --- lib/axlsx/workbook/workbook.rb | 12 ++---------- 1 file changed, 2 insertions(+), 10 deletions(-) diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index b3b10fac..cbb85349 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -99,25 +99,17 @@ require 'axlsx/workbook/shared_strings_table.rb' # Creates a new Workbook # The recomended way to work with workbooks is via Package#workbook - # @option options [Boolean] date1904. If this is not specified, we try to determine if the platform is bsd/darwin and set date1904 to true automatically. + # @option options [Boolean] date1904. If this is not specified, date1904 is set to false. Office 2011 for Mac defaults to false. def initialize(options={}) @styles = Styles.new @worksheets = SimpleTypedList.new Worksheet @drawings = SimpleTypedList.new Drawing @charts = SimpleTypedList.new Chart @images = SimpleTypedList.new Pic - self.date1904= options[:date1904].nil? ? is_bsd? : options[:date1904] + self.date1904= !options[:date1904].nil? && options[:date1904] yield self if block_given? end - # Uses RUBY_PLATFORM constant to determine if the OS is freebsd or darwin - # based on this value we attempt to set date1904. - # @return [Boolean] - def is_bsd? - platform = RUBY_PLATFORM.downcase - platform.include?('freebsd') || platform.include?('darwin') - end - # Instance level access to the class variable 1904 # @return [Boolean] def date1904() @@date1904; end -- cgit v1.2.3 From 99e0fd6d70b48df1e09b6f234b2512893eae9ff6 Mon Sep 17 00:00:00 2001 From: Jurriaan Pruis Date: Fri, 2 Mar 2012 10:21:01 +0100 Subject: Updated example.rb Fixed indenting and commented date1904 --- examples/example.rb | 313 ++++++++++++++++++++++++++-------------------------- 1 file changed, 156 insertions(+), 157 deletions(-) diff --git a/examples/example.rb b/examples/example.rb index caafd274..8058b50b 100644 --- a/examples/example.rb +++ b/examples/example.rb @@ -1,225 +1,224 @@ #!/usr/bin/env ruby -w -s # -*- coding: utf-8 -*- - require 'axlsx.rb' +require 'axlsx.rb' - p = Axlsx::Package.new - wb = p.workbook +p = Axlsx::Package.new +wb = p.workbook #A Simple Workbook - wb.add_worksheet(:name => "Basic Worksheet") do |sheet| - sheet.add_row ["First Column", "Second", "Third"] - sheet.add_row [1, 2, 3] - end +wb.add_worksheet(:name => "Basic Worksheet") do |sheet| + sheet.add_row ["First Column", "Second", "Third"] + sheet.add_row [1, 2, 3] +end #Using Custom Styles - wb.styles do |s| - black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center } - blue_cell = s.add_style :bg_color => "0000FF", :fg_color => "FF", :sz => 20, :alignment => { :horizontal=> :center } - wb.add_worksheet(:name => "Custom Styles") do |sheet| - sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell] - sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER - end - end +wb.styles do |s| + black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center } + blue_cell = s.add_style :bg_color => "0000FF", :fg_color => "FF", :sz => 20, :alignment => { :horizontal=> :center } + wb.add_worksheet(:name => "Custom Styles") do |sheet| + sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell] + sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER + end +end ##Using Custom Formatting and date1904 - require 'date' - wb.styles do |s| - date = s.add_style(:format_code => "yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER) - padded = s.add_style(:format_code => "00#", :border => Axlsx::STYLE_THIN_BORDER) - percent = s.add_style(:format_code => "0000%", :border => Axlsx::STYLE_THIN_BORDER) - wb.date1904 = true # required for generation on mac - wb.add_worksheet(:name => "Formatting Data") do |sheet| - sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER - sheet.add_row [Date::strptime('2012-01-19','%Y-%m-%d'), 0.2, 32], :style => [date, percent, padded] - end - end +require 'date' +wb.styles do |s| + date = s.add_style(:format_code => "yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER) + padded = s.add_style(:format_code => "00#", :border => Axlsx::STYLE_THIN_BORDER) + percent = s.add_style(:format_code => "0000%", :border => Axlsx::STYLE_THIN_BORDER) + # wb.date1904 = true # Use the 1904 date system (Used by Excel for Mac < 2011) + wb.add_worksheet(:name => "Formatting Data") do |sheet| + sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER + sheet.add_row [Date::strptime('2012-01-19','%Y-%m-%d'), 0.2, 32], :style => [date, percent, padded] + end +end ##Add an Image - wb.add_worksheet(:name => "Images") do |sheet| - img = File.expand_path('examples/image1.jpeg') - sheet.add_image(:image_src => img, :noSelect => true, :noMove => true) do |image| - image.width=720 - image.height=666 - image.start_at 2, 2 - end - end +wb.add_worksheet(:name => "Images") do |sheet| + img = File.expand_path('examples/image1.jpeg') + sheet.add_image(:image_src => img, :noSelect => true, :noMove => true) do |image| + image.width=720 + image.height=666 + image.start_at 2, 2 + end +end ##Add an Image with a hyperlink - wb.add_worksheet(:name => "Image with Hyperlink") do |sheet| - img = File.expand_path('examples/image1.jpeg') - sheet.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| - image.width=720 - image.height=666 - image.hyperlink.tooltip = "Labeled Link" - image.start_at 2, 2 - end - end +wb.add_worksheet(:name => "Image with Hyperlink") do |sheet| + img = File.expand_path('examples/image1.jpeg') + sheet.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| + image.width=720 + image.height=666 + image.hyperlink.tooltip = "Labeled Link" + image.start_at 2, 2 + end +end ##Asian Language Support - wb.add_worksheet(:name => "日本語でのシート名") do |sheet| - sheet.add_row ["日本語"] - sheet.add_row ["华语/華語"] - sheet.add_row ["한국어/조선말"] - end +wb.add_worksheet(:name => "日本語でのシート名") do |sheet| + sheet.add_row ["日本語"] + sheet.add_row ["华语/華語"] + sheet.add_row ["한국어/조선말"] +end ##Styling Columns - wb.styles do |s| - percent = s.add_style :num_fmt => 9 - wb.add_worksheet(:name => "Styling Columns") do |sheet| - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] - sheet.add_row [1, 2, 0.3, 4] - sheet.add_row [1, 2, 0.2, 4] - sheet.add_row [1, 2, 0.1, 4] - sheet.col_style 2, percent, :row_offset => 1 - end - end +wb.styles do |s| + percent = s.add_style :num_fmt => 9 + wb.add_worksheet(:name => "Styling Columns") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] + sheet.add_row [1, 2, 0.3, 4] + sheet.add_row [1, 2, 0.2, 4] + sheet.add_row [1, 2, 0.1, 4] + sheet.col_style 2, percent, :row_offset => 1 + end +end ##Styling Rows - wb.styles do |s| - head = s.add_style :bg_color => "00", :fg_color => "FF" - percent = s.add_style :num_fmt => 9 - wb.add_worksheet(:name => "Styling Rows") do |sheet| - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] - sheet.add_row [1, 2, 0.3, 4] - sheet.add_row [1, 2, 0.2, 4] - sheet.add_row [1, 2, 0.1, 4] - sheet.col_style 2, percent, :row_offset => 1 - sheet.row_style 0, head - end - end +wb.styles do |s| + head = s.add_style :bg_color => "00", :fg_color => "FF" + percent = s.add_style :num_fmt => 9 + wb.add_worksheet(:name => "Styling Rows") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] + sheet.add_row [1, 2, 0.3, 4] + sheet.add_row [1, 2, 0.2, 4] + sheet.add_row [1, 2, 0.1, 4] + sheet.col_style 2, percent, :row_offset => 1 + sheet.row_style 0, head + end +end ##Styling Cell Overrides - wb.add_worksheet(:name => "Cell Level Style Overrides") do |sheet| - # cell level style overides when adding cells - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'], :sz => 16 - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - # cell level style overrides via sheet range - sheet["A1:D1"].each { |c| c.color = "FF0000"} - sheet['A1:D2'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } - end +wb.add_worksheet(:name => "Cell Level Style Overrides") do |sheet| + # cell level style overides when adding cells + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'], :sz => 16 + sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] + # cell level style overrides via sheet range + sheet["A1:D1"].each { |c| c.color = "FF0000"} + sheet['A1:D2'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } +end ##Using formula - wb.add_worksheet(:name => "Using Formulas") do |sheet| - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - end +wb.add_worksheet(:name => "Using Formulas") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] + sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] +end ##Automatic cell types - wb.add_worksheet(:name => "Automatic cell types") do |sheet| - sheet.add_row ["Date", "Time", "String", "Boolean", "Float", "Integer"] - sheet.add_row [Date.today, Time.now, "value", true, 0.1, 1] - end +wb.add_worksheet(:name => "Automatic cell types") do |sheet| + sheet.add_row ["Date", "Time", "String", "Boolean", "Float", "Integer"] + sheet.add_row [Date.today, Time.now, "value", true, 0.1, 1] +end ##Merging Cells. - wb.add_worksheet(:name => 'Merging Cells') do |sheet| - # cell level style overides when adding cells - sheet.add_row ["col 1", "col 2", "col 3", "col 4"], :sz => 16 - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - sheet.add_row [2, 3, 4, "=SUM(A3:C3)"] - sheet.add_row ["total", "", "", "=SUM(D2:D3)"] - sheet.merge_cells("A4:C4") - sheet["A1:D1"].each { |c| c.color = "FF0000"} - sheet["A1:D4"].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } - end +wb.add_worksheet(:name => 'Merging Cells') do |sheet| + # cell level style overides when adding cells + sheet.add_row ["col 1", "col 2", "col 3", "col 4"], :sz => 16 + sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] + sheet.add_row [2, 3, 4, "=SUM(A3:C3)"] + sheet.add_row ["total", "", "", "=SUM(D2:D3)"] + sheet.merge_cells("A4:C4") + sheet["A1:D1"].each { |c| c.color = "FF0000"} + sheet["A1:D4"].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } +end ##Generating A Bar Chart - wb.add_worksheet(:name => "Bar Chart") do |sheet| - sheet.add_row ["A Simple Bar Chart"] - sheet.add_row ["First", "Second", "Third"] - sheet.add_row [1, 2, 3] - sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "F17") do |chart| - chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"], :title => sheet["A1"] - end - end +wb.add_worksheet(:name => "Bar Chart") do |sheet| + sheet.add_row ["A Simple Bar Chart"] + sheet.add_row ["First", "Second", "Third"] + sheet.add_row [1, 2, 3] + sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "F17") do |chart| + chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"], :title => sheet["A1"] + end +end ##Generating A Pie Chart - wb.add_worksheet(:name => "Pie Chart") do |sheet| - sheet.add_row ["First", "Second", "Third", "Fourth"] - sheet.add_row [1, 2, 3, "=PRODUCT(A2:C2)"] - sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "example 3: Pie Chart") do |chart| - chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"] - end - end +wb.add_worksheet(:name => "Pie Chart") do |sheet| + sheet.add_row ["First", "Second", "Third", "Fourth"] + sheet.add_row [1, 2, 3, "=PRODUCT(A2:C2)"] + sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "example 3: Pie Chart") do |chart| + chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"] + end +end ##Data over time - wb.add_worksheet(:name=>'Charting Dates') do |sheet| - # cell level style overides when adding cells - sheet.add_row ['Date', 'Value'], :sz => 16 - sheet.add_row [Time.now - (7*60*60*24), 3] - sheet.add_row [Time.now - (6*60*60*24), 7] - sheet.add_row [Time.now - (5*60*60*24), 18] - sheet.add_row [Time.now - (4*60*60*24), 1] - sheet.add_chart(Axlsx::Bar3DChart) do |chart| - chart.start_at "B7" - chart.end_at "H27" - chart.add_series(:data => sheet["B2:B5"], :labels => sheet["A2:A5"], :title => sheet["B1"]) - end - end +wb.add_worksheet(:name=>'Charting Dates') do |sheet| + # cell level style overides when adding cells + sheet.add_row ['Date', 'Value'], :sz => 16 + sheet.add_row [Time.now - (7*60*60*24), 3] + sheet.add_row [Time.now - (6*60*60*24), 7] + sheet.add_row [Time.now - (5*60*60*24), 18] + sheet.add_row [Time.now - (4*60*60*24), 1] + sheet.add_chart(Axlsx::Bar3DChart) do |chart| + chart.start_at "B7" + chart.end_at "H27" + chart.add_series(:data => sheet["B2:B5"], :labels => sheet["A2:A5"], :title => sheet["B1"]) + end +end ##Generating A Line Chart - wb.add_worksheet(:name => "Line Chart") do |sheet| - sheet.add_row ["First", 1, 5, 7, 9] - sheet.add_row ["Second", 5, 2, 14, 9] - sheet.add_chart(Axlsx::Line3DChart, :title => "example 6: Line Chart", :rotX => 30, :rotY => 20) do |chart| - chart.start_at 0, 2 - chart.end_at 10, 15 - chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"] - chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"] - end - end +wb.add_worksheet(:name => "Line Chart") do |sheet| + sheet.add_row ["First", 1, 5, 7, 9] + sheet.add_row ["Second", 5, 2, 14, 9] + sheet.add_chart(Axlsx::Line3DChart, :title => "example 6: Line Chart", :rotX => 30, :rotY => 20) do |chart| + chart.start_at 0, 2 + chart.end_at 10, 15 + chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"] + chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"] + end +end ##Auto Filter - wb.add_worksheet(:name => "Auto Filter") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.auto_filter = "A2:D5" - end +wb.add_worksheet(:name => "Auto Filter") do |sheet| + sheet.add_row ["Build Matrix"] + sheet.add_row ["Build", "Duration", "Finished", "Rvm"] + sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] + sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] + sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] + sheet.auto_filter = "A2:D5" +end ##Specifying Column Widths - wb.add_worksheet(:name => "custom column widths") do |sheet| - sheet.add_row ["I use autowidth and am very wide", "I use a custom width and am narrow"] - sheet.column_widths nil, 3 - end +wb.add_worksheet(:name => "custom column widths") do |sheet| + sheet.add_row ["I use autowidth and am very wide", "I use a custom width and am narrow"] + sheet.column_widths nil, 3 +end ##Specify Page Margins for printing - margins = {:left => 3, :right => 3, :top => 1.2, :bottom => 1.2, :header => 0.7, :footer => 0.7} - wb.add_worksheet(:name => "print margins", :page_margins => margins) do |sheet| - sheet.add_row ["this sheet uses customized page margins for printing"] - end +margins = {:left => 3, :right => 3, :top => 1.2, :bottom => 1.2, :header => 0.7, :footer => 0.7} +wb.add_worksheet(:name => "print margins", :page_margins => margins) do |sheet| + sheet.add_row ["this sheet uses customized page margins for printing"] +end ##Validate and Serialize - p.validate.each { |e| puts e.message } - p.serialize("example.xlsx") +p.validate.each { |e| puts e.message } +p.serialize("example.xlsx") - s = p.to_stream() - File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) } +s = p.to_stream() +File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) } ##Using Shared Strings - - p.use_shared_strings = true - p.serialize("shared_strings_example.xlsx") +p.use_shared_strings = true +p.serialize("shared_strings_example.xlsx") -- cgit v1.2.3 From ca7248353d295c74f3dff7e8e003044849c026e7 Mon Sep 17 00:00:00 2001 From: Jurriaan Pruis Date: Fri, 2 Mar 2012 10:23:03 +0100 Subject: epoc => epoch --- README.md | 2 +- lib/axlsx/workbook/worksheet/date_time_converter.rb | 14 +++++++------- test/workbook/worksheet/tc_date_time_converter.rb | 2 +- 3 files changed, 9 insertions(+), 9 deletions(-) diff --git a/README.md b/README.md index 37a09854..f23bcde7 100644 --- a/README.md +++ b/README.md @@ -42,7 +42,7 @@ Feature List **5. Automatic and fixed column widths: Axlsx will automatically determine the appropriate width for your columns based on the content in the worksheet, or use any value you specify for the really funky stuff. -**6. Support for automatically formatted 1904 and 1900 epocs configurable in the workbook. +**6. Support for automatically formatted 1904 and 1900 epochs configurable in the workbook. **7. Add jpg, gif and png images to worksheets with hyperlinks diff --git a/lib/axlsx/workbook/worksheet/date_time_converter.rb b/lib/axlsx/workbook/worksheet/date_time_converter.rb index 5a572781..d2d9a014 100644 --- a/lib/axlsx/workbook/worksheet/date_time_converter.rb +++ b/lib/axlsx/workbook/worksheet/date_time_converter.rb @@ -9,8 +9,8 @@ module Axlsx # @param [Date] date the date to be serialized # @return [Numeric] def self.date_to_serial(date) - epoc = Axlsx::Workbook::date1904 ? Date.new(1904) : Date.new(1899, 12, 30) - (date-epoc).to_f + epoch = Axlsx::Workbook::date1904 ? Date.new(1904) : Date.new(1899, 12, 30) + (date-epoch).to_f end # The time_to_serial methond converts a Time object its excel serialized form. @@ -18,12 +18,12 @@ module Axlsx # @return [Numeric] def self.time_to_serial(time) # Using hardcoded offsets here as some operating systems will not except - # a 'negative' offset from the ruby epoc. - epoc1900 = -2209161600 # Time.utc(1899, 12, 30).to_i - epoc1904 = -2082844800 # Time.utc(1904, 1, 1).to_i + # a 'negative' offset from the ruby epoch. + epoch1900 = -2209161600 # Time.utc(1899, 12, 30).to_i + epoch1904 = -2082844800 # Time.utc(1904, 1, 1).to_i seconds_per_day = 86400 # 60*60*24 - epoc = Axlsx::Workbook::date1904 ? epoc1904 : epoc1900 - (time.to_f - epoc)/seconds_per_day + epoch = Axlsx::Workbook::date1904 ? epoch1904 : epoch1900 + (time.to_f - epoch)/seconds_per_day end end end diff --git a/test/workbook/worksheet/tc_date_time_converter.rb b/test/workbook/worksheet/tc_date_time_converter.rb index 529da917..c78e51eb 100644 --- a/test/workbook/worksheet/tc_date_time_converter.rb +++ b/test/workbook/worksheet/tc_date_time_converter.rb @@ -88,7 +88,7 @@ class TestDateTimeConverter < Test::Unit::TestCase def test_time_to_serial_1904 Axlsx::Workbook.date1904 = true - # ruby 1.8.7 cannot parse dates prior to epoc. see http://ruby-doc.org/core-1.8.7/Time.html + # ruby 1.8.7 cannot parse dates prior to epoch. see http://ruby-doc.org/core-1.8.7/Time.html tests = if @extended_time_range { # examples taken straight from the spec -- cgit v1.2.3