release prep for 1.0.14.

5 files changed, 248 insertions, 147 deletions

diff --git a/README.md b/README.md
index ebe2e10a..c6ebff7a 100644
--- a/README.md
+++ b/README.md
@@ -7,20 +7,23 @@ Axlsx: Office Open XML Spreadsheet Generation
 **Author**:       Randy Morgan   
 **Copyright**:    2011      
 **License**:      MIT License      
-**Latest Version**: 1.0.13   
+**Latest Version**: 1.0.14   
 **Ruby Version**: 1.8.7, 1.9.2, 1.9.3 
 
-**Release Date**: December 8th 2011     
+**Release Date**: December 14th 2011     
 
 Synopsis
 --------
 
 Axlsx is an Office Open XML Spreadsheet generator for the Ruby programming language.
-With Axlsx you can create worksheets with charts, images, automated column width, customizable styles and full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification. Check out the README for some examples of how easy it is. Best of all, you can validate your xlsx file before serialization so you know for sure that anything generated is going to load on your client's machine.
+With Axlsx you can create excel worksheets with charts, images, automated column width, customizable styles, functins, merged cells and auto filters as well as full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification.
 
 If you are working in rails, or with active record see:
 http://github.com/randym/acts_as_xlsx 
 
+There are guides for using axlsx and acts_as_xlsx here:
+[http://axlsx.blogspot.com](http://axlsx.blogspot.com)
+
 Help Wanted
 -----------
 
@@ -49,6 +52,10 @@ Feature List
 
 **10. Support for formulas
 
+**11. Support for cell merging via worksheet.merged_cells
+
+**12. Auto filtering tables with worksheet.auto_filter
+
 Installing
 ----------
 
@@ -56,213 +63,213 @@ To install Axlsx, use the following command:
 
     $ gem install axlsx
     
-Usage
------
+#Usage
+------
 
-###Examples
-
-     require 'rubygems'
      require 'axlsx'
 
-
-Validation
-
      p = Axlsx::Package.new
-     p.workbook.add_worksheet do |sheet|
-       sheet.add_row ["First", "Second", "Third"]
-       sheet.add_row [1, 2, 3]
-     end
-
-     p.validate.each do |error|
-       puts error.inspect
-     end
+     wb = p.workbook
 
-A Simple Workbook
+##A Simple Workbook
 
-      p = Axlsx::Package.new
-      p.workbook.add_worksheet do |sheet|
-        sheet.add_row ["First Column", "Second Column", "Third Column"]
-        sheet.add_row [1, 2, Time.now]
+      wb.add_worksheet(:name => "Basic Worksheet") do |sheet|
+        sheet.add_row ["First Column", "Second", "Third"]
+        sheet.add_row [1, 2, 3]
       end
-      p.serialize("example1.xlsx")
 
-Generating A Bar Chart
+##Using Custom Styles
 
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet 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", :title=>sheet["A1"]) do |chart|
-         chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"]
+     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  
-     p.serialize("example2.xlsx")
+     end
 
-Generating A Pie Chart
+##Using Custom Formatting
 
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet 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, 25], :title=>"example 3: Pie Chart") do |chart|
-         chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"]
+     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.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 [Time.now, 0.2, 32], :style => [date, percent, padded]
        end
-     end  
-     p.serialize("example3.xlsx")
+     end
 
-Generating A Line Chart
+##Styling Columns
 
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet 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") 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"]
+     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  
-     p.serialize("example4.xlsx")
-
-Using Custom Styles
-
-     p = Axlsx::Package.new
-     wb = p.workbook
-     black_cell = wb.styles.add_style :bg_color => "00", :fg_color => "FF", :sz=>14, :alignment => { :horizontal=> :center }
-     blue_cell = wb.styles.add_style  :bg_color => "0000FF", :fg_color => "FF", :sz=>14, :alignment => { :horizontal=> :center }
-     wb.add_worksheet 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
-     p.serialize("example5.xlsx")
-
-Using Custom Formatting and date1904
 
-     p = Axlsx::Package.new
-     wb = p.workbook
-     date = wb.styles.add_style :format_code=>"yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER
-     padded = wb.styles.add_style :format_code=>"00#", :border => Axlsx::STYLE_THIN_BORDER
-     percent = wb.styles.add_style :format_code=>"0%", :border => Axlsx::STYLE_THIN_BORDER
-     wb.date1904 = true # required for generation on mac
-     wb.add_worksheet do |sheet|
-       sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER
-       sheet.add_row [Time.now, 0.2, 32], :style => [date, percent, padded]
+##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
-     p.serialize("example6.xlsx")
 
+##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     
 
-Add an Image
+##Add an Image
 
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet do |sheet|
+     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|
+       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  
-     p.serialize("example8.xlsx")
 
-Asian Language Support
+##Asian Language Support
 
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet do |sheet|
+     wb.add_worksheet(:name => "Unicode Support") do |sheet|
        sheet.add_row ["日本語"]
        sheet.add_row ["华语/華語"]
        sheet.add_row ["한국어/조선말"]
      end  
-     p.serialize("example9.xlsx")
 
-Styling Columns
 
-     p = Axlsx::Package.new
-     percent = p.workbook.styles.add_style :num_fmt => 9
-     p.workbook.add_worksheet 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]
-     end
-     p.workbook.worksheets.first.col_style 2, percent, :row_offset=>1
-     p.serialize("example10.xlsx")
-
-Styling Rows
-
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet 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]
-     end
-     head = p.workbook.styles.add_style :bg_color => "00", :fg_color=>"FF"
-     percent = p.workbook.styles.add_style :num_fmt => 9
-     p.workbook.worksheets.first.col_style 2, percent, :row_offset=>1
-     p.workbook.worksheets.first.row_style 0, head
-     p.serialize("example11.xlsx")
+##Using formula
 
-Using formula
-
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet do |sheet|
+     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
-     p.serialize("example12.xlsx")
 
-Using cell specific styling and range / name based access
 
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet(:name=>'My Worksheet') 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"}
-     end     
-     p.workbook['My Worksheet!A1:D2'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER }
-     p.serialize("example13.xlsx")
+##Merging Cells
 
-#Merging Cells.
-     p = Axlsx::Package.new
-     p.workbook.add_worksheet(:name=>'My Worksheet') do |sheet|
+     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 ["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')
-        # cell level style overrides via sheet range
+         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     
-     p.workbook['My Worksheet!A1:D4'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER }
-     p.serialize("example14.xlsx")
 
-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  
 
+##Generating A Pie Chart
 
-###Documentation
+     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     
+
+##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  
+
+##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  
+
+##Validate and Serialize
+
+     p.validate.each { |e| puts e.message }
+     p.serialize("example.xlsx")
+
+
+#Documentation
+--------------
 This gem is 100% documented with YARD, an exceptional documentation library. To see documentation for this, and all the gems installed on your system use:
 
       gem install yard
       yard server -g
 
 
-###Specs
-
+#Specs
+------
 This gem has 100% test coverage using test/unit. To execute tests for this gem, simply run rake in the gem directory.
  
-Changelog
+#Changelog
 ---------
-- **December.?.11**: 1.0.14 release
+- **December.14.11**: 1.0.14 release
    - Added support for mergin cells
+   - Added support for auto filters
+   - Improved auto width calculations
+   - Improved charts
+   - Updated examples to output to a single workbook with multiple sheets
+   - Added access to app and core package objects so you can set the creator and other properties of the package
+   - The beginning of password protected xlsx files - roadmapped for January release.
 	
 - **December.8.11**: 1.0.13 release
    -  Fixing .gemspec errors that caused gem to miss the lib directory. Sorry about that.
@@ -282,8 +289,8 @@ Changelog
 Please see the {file:CHANGELOG.md} document for past release information.
 
 
-Copyright
----------
+#Copyright and License
+----------
 
 Axlsx © 2011 by [Randy Morgan](mailto:[email protected]). Axlsx is 
 licensed under the MIT license. Please see the {file:LICENSE} document for more information.
diff --git a/axlsx.gemspec b/axlsx.gemspec
index 76a05953..44c4141f 100644
--- a/axlsx.gemspec
+++ b/axlsx.gemspec
@@ -25,6 +25,9 @@ Gem::Specification.new do |s|
   
   s.add_development_dependency 'rake', "0.8.7"  if RUBY_VERSION == "1.9.2"
   s.add_development_dependency 'rake', "~> 0.9" if ["1.9.3", "1.8.7"].include?(RUBY_VERSION)
+  s.add_development_dependency 'yard'
+  s.add_development_dependency 'yard'
+  s.add_development_dependency 'rdiscount'
 
   s.required_ruby_version = '>= 1.8.7'
   s.require_path = 'lib'
diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index daa7ea11..2f347d49 100644
--- a/lib/axlsx.rb
+++ b/lib/axlsx.rb
@@ -7,6 +7,8 @@ require 'axlsx/util/simple_typed_list.rb'
 require 'axlsx/util/constants.rb'
 require 'axlsx/util/validators.rb'
 
+require 'axlsx/util/ms_off_crypto.rb'
+
 
 # to be included with parsable intitites.
 #require 'axlsx/util/parser.rb'
diff --git a/lib/axlsx/util/ms_off_crypto.rb b/lib/axlsx/util/ms_off_crypto.rb
new file mode 100644
index 00000000..7f20ac28
--- /dev/null
+++ b/lib/axlsx/util/ms_off_crypto.rb
@@ -0,0 +1,88 @@
+require 'digest'
+require 'base64'
+require 'openssl'
+module Axlsx
+  class MsOffCrypto
+
+    attr_reader :verifier
+    attr_reader :key
+
+    def initialize(password = "passowrd")
+      @password = password
+      @salt_size = 0x10
+      @key_size = 0x100
+      @verifier = rand(16**16).to_s 
+
+      #fixed salt for testing
+      @salt = [0x90,0xAC,0x68,0x0E,0x76,0xF9,0x43,0x2B,0x8D,0x13,0xB7,0x1D,0xB7,0xC0,0xFC,0x0D].join
+      # @salt =Digest::SHA1.digest(rand(16**16).to_s)
+    end
+
+    def encryption_info
+      #         v.major v.minor flags           header length   flags           size            # AES 128 bit
+      header = [3, 0, 2, 0, 0x24, 0, 0, 0, 0xA4, 0, 0, 0, 0x24, 0, 0, 0, 0, 0, 0, 0, 0x0E, 0x66, 0, 0]
+      header.concat [0x04, 0x80, 0, 0, 0x80, 0, 0, 0, 0x18, 0, 0, 0, 0xA0, 0xC7, 0xDC, 0x2, 0, 0, 0, 0]
+      header.concat "Microsoft Enhanced RSA and AES Cryptographic Provider (Prototype)".bytes.to_a.pack('s*').bytes.to_a
+      header.concat [0, 0]
+      header.concat [0x10, 0, 0, 0]
+      header.concat [0x90,0xAC,0x68,0x0E,0x76,0xF9,0x43,0x2B,0x8D,0x13,0xB7,0x1D,0xB7,0xC0,0xFC,0x0D]
+      header.concat encrypted_verifier.bytes.to_a.pack('c*').bytes.to_a
+      header.concat [20, 0,0,0]
+      header.concat encrypted_verifier_hash.bytes.to_a.pack('c*').bytes.to_a
+      header.flatten!
+      header.pack('c*')
+    end
+
+    def encryption_verifier
+      {:salt_size => @salt_size, 
+       :salt => @salt, 
+       :encrypted_verifier => encrypted_verifier,
+       :varifier_hash_size => 0x14,
+        :encrypted_verifier_hash =>  encrypted_verifier_hash}
+    end
+
+    # 2.3.3
+    def encrypted_verifier
+      @encrypted_verifier ||= encrypt(@verifier)
+    end
+
+    # 2.3.3
+    def encrypted_verifier_hash
+      verifier_hash = Digest::SHA1.digest(@verifier)
+      verifier_hash << Array.new(32 - verifier_hash.size, 0).join('')
+      @encrypted_verifier_hash ||= encrypt(verifier_hash)
+    end
+
+    # 2.3.4.7 ECMA-376 Document Encryption Key Generation (Standard Encryption)
+    def key
+      sha = Digest::SHA1.new() << (@salt + @password)
+      (0..49999).each { |i| sha.update(i.to_s+sha.to_s) }
+      key = sha.update(sha.to_s+'0').digest
+      a = key.bytes.each_with_index.map { |item, i|  0x36 ^ item }
+      x1 = Digest::SHA1.digest((a.concat Array.new(64 - key.size, 0x36)).to_s)
+      a = key.bytes.each_with_index.map { |item, i| 0x5C ^ item }
+      x2 = Digest::SHA1.digest( (a.concat Array.new(64 - key.size, 0x5C) ).to_s)
+      x3 = x1 + x2
+      @key ||= x3.bytes.to_a[(0..31)].pack('c*')
+    end
+
+    def verify_password
+      puts decrypt(@encrypted_verifier)
+    end
+
+    def encrypt(data)
+      aes = OpenSSL::Cipher.new("AES-128-ECB")     
+      aes.encrypt
+      aes.key = key
+      aes.update(data)
+    end
+
+    def decrypt(data)
+      aes = OpenSSL::Cipher.new("AES-128-ECB")     
+      aes.decrypt
+      aes.key = key
+      aes.update(data)
+    end
+
+  end
+end
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 4d55ad1e..60468870 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -147,6 +147,7 @@ require 'axlsx/workbook/worksheet/worksheet.rb'
       builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
         xml.workbook(:xmlns => XML_NS, :'xmlns:r' => XML_NS_R) {
           xml.workbookPr(:date1904=>@@date1904)
+          #<x:workbookProtection workbookPassword="xsd:hexBinary data" lockStructure="1" lockWindows="1" />
           xml.sheets {
             @worksheets.each_with_index do |sheet, index|              
               xml.sheet(:name=>sheet.name, :sheetId=>index+1, :"r:id"=>sheet.rId)