Class: Axlsx::Worksheet

Inherits:

Object

• Object
• Axlsx::Worksheet

show all

Defined in:

lib/axlsx/workbook/worksheet/worksheet.rb

Overview

Instance Attribute Summary (collapse)

• - (Array) auto_fit_data readonly

  An array of content based calculated column widths.

• - (Drawing) drawing readonly

  The drawing associated with this worksheet.

• - (Integer) index readonly

  The index of this worksheet in the owning Workbook’s worksheets list.

• - (String) name

  The name of the worksheet.

• - (String) pn readonly

  The part name of this worksheet.

• - (Object) relationships readonly

  The worksheet’s relationships.

• - (String) rels_pn readonly

  The relationship part name of this worksheet.

• - (String) rId readonly

  The relationship Id of thiw worksheet.

• - (SimpleTypedList) rows readonly

  The rows in this worksheet.

• - (Workbook) workbook

  The workbook that owns this worksheet.

Instance Method Summary (collapse)

• - (Object) add_chart(chart_type, options = {}) {|chart| ... }

  Adds a chart to this worksheets drawing.

• - (Object) add_image(options = {}) {|image| ... }

  Adds a media item to the worksheets drawing.

• - (Row) add_row(values = [], options = {}) {|@rows.last| ... }

  Adds a row to the worksheet and updates auto fit data.

• - (Float) auto_width(col)

  Determines the proper width for a column based on content.

• - (Worksheet) initialize(wb, options = {}) constructor

  Creates a new worksheet.

• - (String) to_xml

  Serializes the worksheet document.

• - (Array) update_auto_fit_data(cells)

  Updates auto fit data.

59
60
61
62
63
64
65
66

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 59

def initialize(wb, options={})
  @rows = SimpleTypedList.new Row
  self.workbook = wb
  @workbook.worksheets << self
  @auto_fit_data = []
  self.name = options[:name] || "Sheet" + (index+1).to_s
  @magick_draw = Magick::Draw.new
end

34
35
36

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 34

def auto_fit_data
  @auto_fit_data
end

29
30
31

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 29

def drawing
  @drawing || @drawing = Axlsx::Drawing.new(self)
end

50
51
52

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 50

def index
  @workbook.worksheets.index(self)
end

9
10
11

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 9

def name
  @name
end

38
39
40

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 38

def pn
  "#{WORKSHEET_PN % (index+1)}"
end

17
18
19
20
21

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 17

def relationships
    r = Relationships.new
    r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing
    r
end

42
43
44

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 42

def rels_pn
  "#{WORKSHEET_RELS_PN % (index+1)}"
end

46
47
48

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 46

def rId
  "rId#{index+1}"
end

23
24
25

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 23

def rows
  @rows
end

13
14
15

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 13

def workbook
  @workbook
end

116
117
118
119
120

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 116

def add_chart(chart_type, options={})
  chart = drawing.add_chart(chart_type, options)
  yield chart if block_given?
  chart
end

125
126
127
128
129

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 125

def add_image(options={})
  image = drawing.add_image(options)
  yield image if block_given?
  image
end

96
97
98
99
100
101

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 96

def add_row(values=[], options={})
  Row.new(self, values, options)
  update_auto_fit_data @rows.last.cells
  yield @rows.last if block_given?
  @rows.last
end

 Column width measured as the number of characters of the maximum digit width of the numbers 0 .. 9 as
 rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.
 width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

199
200
201
202
203
204
205
206
207
208

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 199

def auto_width(col)
  mdw = 6.0 # maximum digit with is always 6.0 with RMagick's default font
  mdw_count = 0 
  best_guess = 1.5  #direct testing shows the results of the documented formula to be a bit too small. This is a best guess scaling
  font_scale = col[:sz].to_f / (self.workbook.styles.fonts[0].sz.to_f || 11.0)
  col[:longest].scan(/./mu).each do |i|
    mdw_count +=1 if @magick_draw.get_type_metrics(i).width >= mdw 
  end
  ((mdw_count * mdw + 5) / mdw * 256) / 256.0 * best_guess * font_scale      
end

133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 133

def to_xml
  builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
    xml.worksheet(:xmlns => XML_NS, :'xmlns:r' => XML_NS_R) {
      if @auto_fit_data.size > 0
        xml.cols {
          @auto_fit_data.each_with_index do |col, index|
            min_max = index+1
            xml.col(:min=>min_max, :max=>min_max, :width => auto_width(col), :customWidth=>"true")
          end
        }
      end
      xml.sheetData {
        @rows.each do |row|
          row.to_xml(xml)
        end
      }
      xml.drawing :"r:id"=>"rId1" if @drawing          
    }
  end
  builder.to_xml(:indent=>0, :save_with=>0)
end

171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189

# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 171

def update_auto_fit_data(cells)
  styles = self.workbook.styles
  cellXfs, fonts = styles.cellXfs, styles.fonts
  sz = fonts[0].sz

  cells.each_with_index do |item, index|
    col = @auto_fit_data[index] || {:longest=>"", :sz=>sz} 
    cell_xf = cellXfs[item.style]
    font = fonts[cell_xf.fontId || 0]
    sz = font.sz || sz

    if (col[:longest].scan(/./mu).size * col[:sz]) < (item.value.to_s.scan(/./mu).size * sz)
      col[:sz] =  sz
      col[:longest] = item.value.to_s
    end
    @auto_fit_data[index] = col
  end
  cells
end