Class: Ruport::Formatter::XLSX

Inherits:
Ruport::Formatter show all
Defined in:
lib/ruport/util/xls.rb

Overview

Excel 2007 OpenXML

Constant Summary collapse

BLANK_XLSX =
File.join(Ruport::Util::BASEDIR, 'example', 'data', 'blank.xlsx')

Instance Method Summary collapse

Constructor Details

#initializeXLSX


86
87
# File 'lib/ruport/util/xls.rb', line 86

def initialize
end

Instance Method Details

#build_cells(values, style = '') ⇒ Object


141
142
143
144
145
146
147
148
149
150
151
152
# File 'lib/ruport/util/xls.rb', line 141

def build_cells(values, style = '')
  col = 0
  values.each do |value|
    value = CGI.escapeHTML(value.to_s)
    id = @strings.length
    @strings.push(value)
    output << %{<c r="#{get_cell_name(@xls_row + 1, col)}" t="s">
<v>#{id}</v>
   </c>}
    col += 1
  end     
end

#build_rowObject


124
125
126
# File 'lib/ruport/util/xls.rb', line 124

def build_row
  table_row{ build_cells(data.to_a) }
end

#build_strings_fileObject


154
155
156
157
158
159
160
161
162
# File 'lib/ruport/util/xls.rb', line 154

def build_strings_file
  out = ''
  out << %{<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" uniqueCount="#{@strings.length}">\n}
  @strings.each {|val|
    out << %{  <si><t>#{val}</t></si>\n} 
  }
  out << %{</sst>\n}
  out
end

#build_table_bodyObject


118
119
120
121
122
# File 'lib/ruport/util/xls.rb', line 118

def build_table_body
  data.each do |r|
      table_row { build_cells(r) }
  end
end

#build_table_headerObject


112
113
114
115
116
# File 'lib/ruport/util/xls.rb', line 112

def build_table_header
  if options.show_table_headers
    table_row { build_cells(data.column_names, 'Heading') }
  end
end

#finalize_tableObject


164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
# File 'lib/ruport/util/xls.rb', line 164

def finalize_table
  output << %{</sheetData>
 <sheetProtection sheet="false" objects="false" scenarios="false" formatCells="false" formatColumns="false" formatRows="false" insertColumns="false" insertRows="false" insertHyperlinks="false" deleteColumns="false" deleteRows="false" selectLockedCells="false" sort="false" autoFilter="false" pivotTables="false" selectUnlockedCells="false"/>
 <printOptions gridLines="false" gridLinesSet="true"/>
 <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>

 <pageSetup paperSize="1" orientation="default"/>
 <headerFooter differentOddEven="false" differentFirst="false" scaleWithDoc="true" alignWithMargins="true">
  <oddHeader></oddHeader>
  <oddFooter></oddFooter>
  <evenHeader></evenHeader>
  <evenFooter></evenFooter>
  <firstHeader></firstHeader>
  <firstFooter></firstFooter>
 </headerFooter>

</worksheet>}

  @tempfile = Tempfile.new('output.xlsx')

  File.open(BLANK_XLSX) { |bo| 
    @tempfile.print(bo.read(1024)) until bo.eof? 
  }
  @tempfile.close
  zip = Zip::ZipFile.open(@tempfile.path)
  zip.get_output_stream('xl/worksheets/sheet1.xml') do |cxml|
    cxml.write(output)
  end
  zip.get_output_stream('xl/sharedStrings.xml') do |cxml|
    cxml.write(build_strings_file)
  end
  workbook = %{<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xml:space="preserve"  xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>
<workbookPr codeName="ThisWorkbook"/>
  <bookViews>
<workbookView activeTab="0" autoFilterDateGrouping="1" firstSheet="0" minimized="0" showHorizontalScroll="1" showSheetTabs="1" showVerticalScroll="1" tabRatio="600" visibility="visible"/>
</bookViews>
  <sheets>
<sheet name="#{options.worksheet_name || 'Ruport'}" sheetId="1" r:id="rId4"/>
</sheets>
<definedNames/>
<calcPr calcId="124519" calcMode="auto" fullCalcOnLoad="1"/>
</workbook>}
  zip.get_output_stream('xl/workbook.xml') do |cxml|
    cxml.write(workbook)
  end
  zip.close
  options.io =
    if options.tempfile
      @tempfile
    else
      File.read(@tempfile.path)
    end
end

#get_cell_name(row, col) ⇒ Object


135
136
137
138
139
# File 'lib/ruport/util/xls.rb', line 135

def get_cell_name(row, col)
  name = ((col % 26) + 65).chr + row.to_s
  name = ((col / 26) + 65).chr + name if (col / 26 != 0)
  name
end

#prepare_tableObject


91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
# File 'lib/ruport/util/xls.rb', line 91

def prepare_table
  @xls_row = 0
  output << %{<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
 <sheetPr codeName="#{options.worksheet_name || 'Ruport'}"/>

 <sheetViews>
  <sheetView tabSelected="1" workbookViewId="0">
   <selection/>
  </sheetView>
 </sheetViews>
 <sheetFormatPr defaultRowHeight="12.75"/>
<cols>
  }
  data.column_names.size.times {
    output << %{ <col min="1" max="1" width="10" customWidth="true"/>}
  }
  output << %{</cols><sheetData>}
  @strings = []
end

#table_rowObject


128
129
130
131
132
133
# File 'lib/ruport/util/xls.rb', line 128

def table_row
  output << %{        <row r="#{@xls_row + 1}">\n}
  yield
  output << %{        </row>\n}
  @xls_row += 1
end