Class: Excel::Workbook

Inherits:
Object
  • Object
show all
Defined in:
lib/rexcel/workbook.rb

Overview

This Workbook will become an Excel-File.

Workbooks contains one or more Worksheet.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(logger = nil) ⇒ Workbook

Create a workbook.

Container for

  • Style #styles

  • Worksheets

Raises:

  • (ArgumentError) 


20
21
22
23
24
25
26
27
28
29
30
31
 
# File 'lib/rexcel/workbook.rb', line 20

def initialize( logger = nil)
  
  @log = logger || LOGGER
  raise ArgumentError, "Workbook: No logger" unless @log.is_a?(Log4r::Logger)
  @log.info("Create Workbook")
  
  @styles = {}
  @worksheets = []
  @active_worksheet = nil
  #~ @header = []

  #~ @content = []

end

Instance Attribute Details

#active_worksheet ⇒ Object (readonly)

Active worksheet



37
38
39
 
# File 'lib/rexcel/workbook.rb', line 37

def active_worksheet
  @active_worksheet
end

#styles ⇒ Object (readonly)

Predefined styles.



35
36
37
 
# File 'lib/rexcel/workbook.rb', line 35

def styles
  @styles
end

#worksheets ⇒ Object (readonly)

Worksheets



33
34
35
 
# File 'lib/rexcel/workbook.rb', line 33

def worksheets
  @worksheets
end

Instance Method Details

#<<(insertion) ⇒ Object

Add content to the workbook.

  • Excel#Worksheet

  • Array: Added to the actual worksheet.

  • Hash: Added to the actual worksheet.

  • Row: Added the actual worksheet.

If no actual worksheet is available, a new worksheet i created.



47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'lib/rexcel/workbook.rb', line 47

def << (insertion)
  case insertion
    when Style  #Excel::Style      

      if @styles[insertion.name]
        @log.warn("Duplicate insertion #{@name} for Style")
      else
        @styles[insertion.name] = insertion
        insertion.style_id = @styles.size
      end
    when Worksheet  
      @worksheets << insertion
      @active_worksheet = insertion
    when Array, Hash, Row
      self << Worksheet.new("Sheet#{@worksheets.size}", :log => @log) unless @active_worksheet
      @active_worksheet << insertion
    else
      raise ArgumentError, "#{Workbook}: Wrong insertion type #{insertion.inspect}"
  end
  self
end

#build_excel_csv(options = {}) ⇒ Object

Build the csv for Excel.

Details on options see Rexcel::Worksheet#to_csv and Rexcel::Cell#to_csv.

Raises:

  • (ArgumentError) 


311
312
313
314
315
316
317
318
319
320
321
322
323
324
 
# File 'lib/rexcel/workbook.rb', line 311

def build_excel_csv(options = {})
  
  csv = []
  count = 0 #counter for filled worksheets

  @log.debug("Prepare csv")
  @worksheets.each{|worksheet|
      next if worksheet.rows.empty?
      csv << worksheet.to_csv(options)
      count += 1
    } #Worksheets

  
  raise ArgumentError, "csv not possible for multiple worksheets" if count > 1
  csv.join()
end

#build_excel_xml(namespace) ⇒ Object

Build the XML for Excel (“Microsoft Office Word 2003 XML Format”)

Requires a namespace for the xml.

Special rules:

  • The last CR must be deleted. Else Excel doesn’t accept the xml

  • There must be a name space.

The namespace must be used before its definition (Tag Workbook)

Example:

XSD:

Descriptions and examples:

http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats



273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
 
# File 'lib/rexcel/workbook.rb', line 273

def build_excel_xml(namespace)
  
  #Define method ns

  self.class.class_eval(%{
    def ns(attr=nil)
      attr ?  "%s:%s" % ['#{namespace}', attr] : '#{namespace}'
    end
  })
  
  @log.debug("Prepare XML")
  builder = Nokogiri::XML::Builder.new() { |xmlbuilder|
    #~ xmlbuilder.Workbook( 'xmlns'=>"urn:schemas-microsoft-com:office:spreadsheet"){

    xmlbuilder.Workbook( "xmlns:#{namespace}"  =>"urn:schemas-microsoft-com:office:spreadsheet"){
      #Add Styles

      xmlbuilder[ns].Styles{
        @styles.each{|name,style|
          style.to_xml(xmlbuilder, method(:ns))
        }
      } unless @styles.empty?
      #Add worksheets

      @worksheets.each{|worksheet|
        worksheet.to_xml(xmlbuilder, method(:ns))
      }
    } #Workbook

  } #@builder

  
  builder.to_xml(
              #~ :encoding => 'utf-8',

              :indent => 4,
              :save_with => Nokogiri::XML::Node::SaveOptions::FORMAT
            ).strip.gsub(/<(\/?)Workbook/, '<\1%s:Workbook' % namespace )
            
end

#prepare_xls(source_reference = nil) ⇒ Object

Build the workbook via OLE.

The excel may be build in two ways.

  • from internal data (previous with Workbook#<< added data)

  • from a xml file

Internal data

If no xml-source is available, the internal data are taken to build the xls.

This may take some time…

Source-reference

If a reference to a source is available, this reference is used to build the Excel Workbook.

This source reference may be

  • xml

  • csv

You may first save the data as xml and then use the xml to build a xls(x).

For big files, it is recommended to use the way via xml.



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/rexcel/workbook.rb', line 96

def prepare_xls(source_reference = nil)

  wb = nil
  case source_reference
    when /xml$/
      if File.exist?(source_reference)
        @log.info("Use existing #{source_reference}")
        wb = Excel.instance.xl.Workbooks.OpenXML(File.expand_path(source_reference))
      else
        @log.fatal("#{__method__} #{source_reference} missing")
        raise ArgumentError, 'Source data missing'
      end
    when /csv$/
      if File.exist?(source_reference)
        @log.info("Use existing #{source_reference}")
        wb = Excel.instance.xl.Workbooks.Open(File.expand_path(source_reference))
      else
        @log.fatal("#{__method__} #{source_reference} missing")
        raise ArgumentError, 'Source data missing'
      end
      
    when nil
      @log.info("Create xls-Workbook")
      wb = Excel.instance.xl.Workbooks.Add #Includes 3 worksheets

      #Delete unused sheets.

      wb.ActiveSheet.delete
      wb.ActiveSheet.delete

      @log.info("Add styles to document") unless @styles.empty?
      @styles.each{|name, style|
        @log.debug("Add style #{name} to document")
        style.to_xls(wb)
      }

      first = true
      #wb.Worksheets.Add appends new worksheets at begin -> reverse for creation to get right sequence.

      @worksheets.reverse.each{|worksheet|
        if first
          worksheet.to_xls(wb.ActiveSheet)
          first = false
        else
          worksheet.to_xls(wb.Worksheets.Add)
        end
      }
  end #case source_reference

  wb
end

#save(path, source_reference = nil) ⇒ Object

Save the workbook.

The filename must end with

  • .xls

  • .xlsx

  • .xlm (Microsoft Office Word 2003 XML Format)

  • .csv

Examples:

WB = Excel::Workbook.new()
# fill WB
WB.save('testfile.xls')
WB.save('testfile.xlsx')
WB.save('testfile.xml')

Save with reference to xml file

You may create the Excel file based on a xml file.

The way Ruby Workbook -> xml -> xls is faster then the direct xls generation.

Example:

WB = Excel::Workbook.new()
# fill WB
WB.save('testfile.xml')
WB.save('testfile_xml.xls', 'testfile.xml')

Save with reference to csv file

You may create the Excel file based on a csv file.

  • This method is only possible for workbooks with only one worksheets.

  • Attention! You may get problems with field conversion.

  • Therefor the way Ruby Workbook -> xml -> xls is recommended.

Example:

WB = Excel::Workbook.new()
# fill WB
WB.save('testfile.csv')
WB.save('testfile_xml.xls', 'testfile.csv')

Save with implicit reference files

Instead the explicit usage of xml/csv-file, the save command can do it implicit

Example:

WB = Excel::Workbook.new()
# fill WB
WB.save('testfile_xml.xls', :via_xml)
WB.save('testfile_xml.xls', :via_csv)



198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/rexcel/workbook.rb', line 198

def save(path, source_reference = nil)
  
  case source_reference
    when :via_xml
      source_reference = path.sub(/\.[^\.]+/, '.xml')
      save(source_reference)
    when :via_csv
      source_reference = path.sub(/\.[^\.]+/, '.csv')
      save(source_reference)
  end
    
  @log.info("Save #{path}")
  expath = File.expand_path(path) #Excel needs absolute path

  expath.gsub!(/\//, '\\')# Save the workbook. / must be \

  begin
    #different formats, see http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

    case path
      when /xlsx$/
        wb = prepare_xls(source_reference)
        @log.info("Save #{path}")
        wb.SaveAs(expath, 51)
        wb.Close
      when /xls$/
        wb = prepare_xls(source_reference)
        @log.info("Save #{path}")
        wb.SaveAs(expath, -4143 ) #excel97_2003_format

        wb.Close
      when /xml$/
Save "Microsoft Office Word 2003 XML Format". 

Special rules:
* The last CR must be deleted. Else Excel doesn't accept the xml
* There must be a name space.
The namespace must be used before its definition (Tag Workbook)

        ns = 'ss' #namespace

        File.open(path, 'w'){|f| 
            f << build_excel_xml(ns)
        }          
      when /csv$/
        File.open(path, 'w'){|f| 
            f << build_excel_csv()
        }                  
      else
        @log.fatal("Wrong filename, no xls/xlsx (#{path})")
        raise ArgumentError, "Wrong filename, no xls/xlsx (#{path})"
    end
  rescue WIN32OLERuntimeError => err
    @log.error("Error #{path} (Opened in Excel?) #{err}")
  end
  # Close the workbook

  
end