Class: JExcelFile

Inherits:
Object show all
Extended by:
RubyPoiTranslations
Includes:
Enumerable, ExcelBase, RubyPoiTranslations
Defined in:
lib/applications/jruby/old_pre_proxy_jexcel_file.rb,
lib/applications/jexcel_file.rb,
lib/applications/jruby/old_pre_proxy_jexcel_file.rb

Overview

END JExcelFile

Constant Summary collapse

MAX_COLUMNS = 
256.freeze
@@maxrows =

NOTE: this is the POI 3.7 HSSF maximum rows

65535

Instance Attribute Summary collapse

Attributes included from ExcelBase

#excel_headers, #header_row_index

Class Method Summary collapse

Instance Method Summary collapse

Methods included from RubyPoiTranslations

poi_cell_type, poi_cell_value

Methods included from ExcelBase

#parse_headers, #sanitize_sheet_name

Constructor Details

#initialize ⇒ JExcelFile

The HSSFWorkbook uses 0 based indexes,

Raises:



59
60
61
62
63
64
65
66
67
68
69
70
 
# File 'lib/applications/jexcel_file.rb', line 59

def initialize()
  @workbook = HSSFWorkbook.new
  
  @sheet = nil
  @current_sheet_index = 0
  
  # The @patriarchs hash is a workaround because HSSFSheet.getDrawingPatriarch()
  # causes a lot of issues (if it doesn't throw an exception!)
  @patriarchs = Hash.new
  
  @date_style = nil
end

Instance Attribute Details

#current_sheet_index ⇒ Object (readonly)

Returns the value of attribute current_sheet_index.



39
40
41
 
# File 'lib/applications/jexcel_file.rb', line 39

def current_sheet_index
  @current_sheet_index
end

#date_style ⇒ Object

Returns the value of attribute date_style.



38
39
40
 
# File 'lib/applications/jexcel_file.rb', line 38

def date_style
  @date_style
end

#row(index) ⇒ Object

Returns the value of attribute row.



38
39
40
 
# File 'lib/applications/jexcel_file.rb', line 38

def row
  @row
end

#sheet(i = nil) ⇒ Object (readonly)

Return the current or specified HSSFSheet



99
100
101
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 99

def sheet
  @sheet
end

#workbook ⇒ Object

Returns the value of attribute workbook.



38
39
40
 
# File 'lib/applications/jexcel_file.rb', line 38

def workbook
  @workbook
end

Class Method Details

.date_format ⇒ Object 



48
49
50
 
# File 'lib/applications/jexcel_file.rb', line 48

def self.date_format
  HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")
end

.maxrows ⇒ Object

NOTE: this is the POI 3.7 HSSF maximum rows



46
47
48
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 46

def self.maxrows
  return @@maxrows
end

.open(filename) ⇒ Object 



52
53
54
 
# File 'lib/applications/jexcel_file.rb', line 52

def self.open(filename)
  HSSFWorkbook.new(FileInputStream.new(filename))
end

Instance Method Details

#[](row, column) ⇒ Object

Get the enriched value of the Cell at row, column.



174
175
176
 
# File 'lib/applications/jexcel_file.rb', line 174

def [](row, column) 
  cell(row, column) 
end

#[]=(row, column, value) ⇒ Object 



178
179
180
 
# File 'lib/applications/jexcel_file.rb', line 178

def []=(row, column, value)
  set_cell(row, column, value)
end

#activate_sheet(sheet) ⇒ Object

Set the supplied sheet index or name, as the active sheet and return it. If no such sheet return current sheet



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/applications/jexcel_file.rb', line 111

def activate_sheet(term)

  if(@workbook)
    x = term.is_a?(String) ? @workbook.getSheetIndex(term.to_java(java.lang.String)) : term
    @sheet = worksheet(x)
    
    if( @sheet )
      @current_sheet_index = x
      @workbook.setActiveSheet(@current_sheet_index)
      @sheet = @workbook.getSheetAt(@current_sheet_index)
      @sheet.setActive(true)
    end
  end
  @sheet
end

#add_comment(cell, text) ⇒ Object 



321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 321

def add_comment( cell, text )
  raise "Please supply valid HSSFCell" unless cell.respond_to?('setCellComment')
  return if @sheet.nil?

  patriarch = @patriarchs[@sheet.getSheetName()]

  anchor = HSSFClientAnchor.new(100, 50, 100, 50, cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex()+3, cell.getRowIndex()+4)
  comment = patriarch.createCellComment(anchor)

  comment_text = HSSFRichTextString.new(text)
  comment.setString(comment_text)
  comment.setAuthor("Mapping")

  cell.setCellComment(comment)
end

#ar_to_headers(records) ⇒ Object

TODO - Move into an ActiveRecord helper module of it’s own



216
217
218
219
220
221
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 216

def ar_to_headers( records )
  return if( !records.first.is_a?(ActiveRecord::Base) || records.empty?)
  
  headers = records.first.class.columns.collect( &:name )    
  set_headers( headers )
end

#ar_to_xls(records, options = {}) ⇒ Object

Pass a set of AR records



225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 225

def ar_to_xls(records, options = {})
  return if( ! records.first.is_a?(ActiveRecord::Base) || records.empty?)
  
  row_index = 
    if(options[:no_headers])
    0
  else
    ar_to_headers( records )
    1
  end
  
  records.each do |record|
    create_row(row_index)
 
    ar_to_xls_row(0, record)
    
    row_index += 1
  end
end

#ar_to_xls_cell(column, record, connection_column) ⇒ Object 



258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 258

def ar_to_xls_cell(column, record, connection_column)  
  begin
    datum = record.send(connection_column.name)

    if(connection_column.sql_type =~ /date/) 
      @row.createCell(column, HSSFCell::CELL_TYPE_STRING).setCellValue(datum.to_s) 
      
    elsif(connection_column.type == :boolean || connection_column.sql_type =~ /tinyint/) 
      @row.createCell(column, HSSFCell::CELL_TYPE_BOOLEAN).setCellValue(datum) 
      
    elsif(connection_column.sql_type =~ /int/) 
      @row.createCell(column, HSSFCell::CELL_TYPE_NUMERIC).setCellValue(datum.to_i)
    else
      @row.createCell(column, HSSFCell::CELL_TYPE_STRING).setCellValue( datum.to_s ) 
    end
    
  rescue => e
    puts "Failed to export #{datum} from #{connection_column.inspect} to column #{column}"
    puts e
  end
end

#ar_to_xls_row(start_column, record) ⇒ Object

Save data from an AR record to the current row, based on the record’s columns [c1,c2,c3] Returns the number of the final column written to



247
248
249
250
251
252
253
254
255
256
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 247

def ar_to_xls_row(start_column, record)
  return unless( record.is_a?(ActiveRecord::Base))
    
  column = start_column
  record.class.columns.each do |connection_column|    
    ar_to_xls_cell(column, record, connection_column)
    column += 1
  end
  column
end

#autosize(column = nil) ⇒ Object

Auto size either the given column index or all columns



206
207
208
209
210
211
212
213
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 206

def autosize(column = nil)
  return if @sheet.nil?
  if (column.kind_of? Integer)
    @sheet.autoSizeColumn(column)
  else
    @sheet.getRow(0).cellIterator.each{|c| @sheet.autoSizeColumn(c.getColumnIndex)}
  end
end

#cell(row, column) ⇒ Object

Get the enriched value of the Cell at row, column.



168
169
170
171
 
# File 'lib/applications/jexcel_file.rb', line 168

def cell(row, column) 
  row = row(row)
  cell_value( row.get_or_create_cell( column )  )
end

#cell_value(cell) ⇒ Object

Return the raw data of an HSSFCell



291
292
293
294
295
296
297
298
299
300
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 291

def cell_value(cell)
  return unless cell
  case (cell.getCellType())
  when HSSFCell::CELL_TYPE_FORMULA  then return cell.getCellFormula()
  when HSSFCell::CELL_TYPE_NUMERIC  then return cell.getNumericCellValue()
  when HSSFCell::CELL_TYPE_STRING   then return cell.getStringCellValue()
  when HSSFCell::CELL_TYPE_BOOLEAN  then return cell.getBooleanCellValue()
  when HSSFCell::CELL_TYPE_BLANK    then return ""
  end
end

#create(sheet_name) ⇒ Object Also known as: create_sheet

EXCEL ITEMS



77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 77

def create(sheet_name)
  @workbook = HSSFWorkbook.new() if @workbook.nil?

  acceptable_name = sheet_name.gsub(':', '').gsub(" ", '')
  
  # Double check sheet doesn't already exist
  if(@workbook.getSheetIndex(acceptable_name) < 0)
    sheet = @workbook.createSheet(acceptable_name.gsub(" ", ''))

    @patriarchs.store(acceptable_name, sheet.createDrawingPatriarch())
  end
  @current_sheet = @workbook.getSheetIndex(acceptable_name)
  
  @date_style = @workbook.createCellStyle
  @date_style.setDataFormat( JExcelFile::date_format )
  
  self.sheet()
end

#create_row(index) ⇒ Object

Create new row, index with POI usage starts at 0



141
142
143
144
145
146
 
# File 'lib/applications/jexcel_file.rb', line 141

def create_row(index)
  return nil if @sheet.nil?
  raise "BAD INDEX: Row indexing starts at 0" if(index < 0)
  @row = @sheet.createRow(index)
  @row
end

#create_worksheet(options = {}) ⇒ Object

Create and return a new worksheet. Not set to the active worksheet



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/applications/jexcel_file.rb', line 85

def create_worksheet( options = {} )
  sheet_name = options[:name]
  
  @workbook = HSSFWorkbook.new() if @workbook.nil?
        
  unless(sheet_name)
    i = 0
    begin
      sheet_name = "Worksheet#{i += 1}"
    end while(@workbook.getSheetIndex(sheet_name) >= 0) # there is no hard limit to no of sheets in Excel but at some point you will run out of memory!
    
    return create_sheet_and_set_styles( sheet_name )
  else 
    
    name = sanitize_sheet_name( sheet_name )

    if (@workbook.getSheetIndex(name) < 0)  #Check sheet doesn't already exist
      return create_sheet_and_set_styles( name )
    else
      activate_sheet(name)
    end
  end
end

#createFreezePane(row = 1, column = 0) ⇒ Object 



358
359
360
361
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 358

def createFreezePane(row=1, column=0)
  return if @sheet.nil?
  @sheet.createFreezePane(row, column)
end

#each(skip = nil, &block) ⇒ Object

Currently ignores skip argument - TODO - this is how spreadsheet gem works #each iterates over all used Rows (from the first used Row until but omitting the first unused Row, see also #dimensions) If the argument skip is given, #each iterates from that row until but omitting the first unused Row, effectively skipping the first skip Rows from the top of the Worksheet.



159
160
161
 
# File 'lib/applications/jexcel_file.rb', line 159

def each(skip = nil, &block) 
  @sheet.rowIterator.each(&block)
end

#each_row ⇒ Object

Process each row. (type is org.apache.poi.hssf.usermodel.HSSFRow)



125
126
127
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 125

def each_row
  @sheet.rowIterator.each { |row| @row = row; yield row }
end

#excel_cell_type(data) ⇒ Object

Return the suitable type for a HSSFCell from a Ruby data type



190
191
192
193
194
195
196
197
198
199
200
201
202
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 190

def excel_cell_type(data)
    
  if(data.kind_of?(Numeric))
    HSSFCell::CELL_TYPE_NUMERIC
  elsif(data.nil?)
    HSSFCell::CELL_TYPE_BLANK
  elsif(data.is_a?(TrueClass) || data.is_a?(FalseClass))
    HSSFCell::CELL_TYPE_BOOLEAN
  else
    HSSFCell::CELL_TYPE_STRING
  end
  # HSSFCell::CELL_TYPE_FORMULA
end

#get_cell_value(row, column) ⇒ Object

Return the raw data of the requested cell by row/column

Raises:

  • (TypeError) 


285
286
287
288
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 285

def get_cell_value(row, column)
  raise TypeError, "Expect row argument of type HSSFRow" unless row.is_a?(Java::OrgApachePoiHssfUsermodel::HSSFRow)
  cell_value( row.getCell(column) )
end

#getPercentStyle ⇒ Object

Get a percentage style



340
341
342
343
344
345
346
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 340

def getPercentStyle()
  if (@percentCellStyle.nil? && @workbook)
    @percentCellStyle = @workbook.createCellStyle();
    @percentCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
  end
  return @percentCellStyle
end

#header_style ⇒ Object 



162
163
164
165
166
167
168
169
170
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 162

def header_style
  return @header_style if @header_style
  @header_style = @workbook.createCellStyle();
  @header_style.setBorderTop(6) # double lines border
  @header_style.setBorderBottom(1) # single line border
  @header_style.setFillBackgroundColor(HSSFColor::GREY_25_PERCENT.index)
  
  @header_style
end

#num_rows ⇒ Object 



148
149
150
 
# File 'lib/applications/jexcel_file.rb', line 148

def num_rows
  @sheet.getPhysicalNumberOfRows
end

#open(filename) ⇒ Object 



72
73
74
75
76
77
78
79
80
 
# File 'lib/applications/jexcel_file.rb', line 72

def open(filename)
  @workbook = JExcelFile.open(filename)
  
  @date_style = @workbook.createCellStyle
  @date_style.setDataFormat( JExcelFile::date_format )
  
  activate_sheet(0)
  @workbook
end

#results_to_sheet(results, sheet, mappings = nil, header = true) ⇒ Object

Use execute to run sql query provided and write to a csv file (path required) header row is optional but default is on Auto mapping of specified columns is optional



368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 368

def results_to_sheet( results, sheet, mappings=nil, header=true)
  numrows = results.length
  sheet_name = sheet

  if (numrows == 0)
    log :info, "WARNING - results are empty nothing written to sheet: #{sheet}"
    return
  end

  #Check if we need to split the results into seperate sheets
  if (numrows > @@maxrows )
    startrow = 0
    while (numrows > 0)
      # Split the results and write to a new sheet
      next_results = results.slice(startrow, @@maxrows > numrows ? numrows : @@maxrows)
      self.results_to_sheet(next_results, "#{sheet_name}", mappings, header) if next_results

      # Increase counters
      numrows -= next_results.length
      startrow += next_results.length
      sheet_name += 'I'
    end
  else
    # Create required sheet
    self.create(sheet)

    row_index = self.num_rows
    # write header line
    if (header && row_index==0 )
      header_row = @sheet.createRow(row_index)
      cell_index = 0
      results[0].keys.each{ |h|
        header_row.createCell(cell_index).setCellValue("#{h}")
        @sheet.setDefaultColumnStyle(cell_index, self.getPercentStyle) if "#{h}".include? '%'
        cell_index += 1
      }
      # Freeze the header row
      @sheet.createFreezePane( 0, 1, 0, 1 )
      row_index += 1
    end

    # write_results
    results.each{ |row|
      sheet_row = @sheet.createRow(row_index)
      cell_index = 0
      row.each{|k,v|
        celltype = v.kind_of?(Numeric) ? HSSFCell::CELL_TYPE_NUMERIC : HSSFCell::CELL_TYPE_STRING
        cell = sheet_row.createCell(cell_index, celltype)

        v.nil? ? value = "<NIL>" : value = v

        cell.setCellValue(value)

        cell_index +=1
      }
      #puts "#{sheet}: written row #{row_index}"
      row_index +=1
    }
  end 

end

#save_to_text(filename) ⇒ Object 



197
198
199
 
# File 'lib/applications/jexcel_file.rb', line 197

def save_to_text( filename )
  File.open( filename, 'w') {|f| f.write(to_s) }
end

#set_cell(row, column, datum) ⇒ Object

Populate a single cell with data



143
144
145
146
147
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 143

def set_cell(row, column, value)
  @row = row(row)
  
  @row[column] = value
end

#set_headers(headers, apply_style = nil) ⇒ Object

Convert array into a header row



149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 149

def set_headers(headers, apply_style = nil)
  create_row(0)
  return if headers.empty?

  style = apply_style || header_style()

  headers.each_with_index do |datum, i|
    c = @row.createCell(i, excel_cell_type(datum))
    c.setCellValue(datum)
    c.setCellStyle(style)
  end
end

#set_row(row, col, data, sheet_num = nil) ⇒ Object

Populate a row of cells with data in an array

where the co-ordinates relate to row/column start position



175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/applications/jruby/old_pre_proxy_jexcel_file.rb', line 175

def set_row( row, col, data, sheet_num = nil)

  sheet(sheet_num)
  
  create_row(row)
  
  column = col
  data.each do |datum|
    set_cell(row, column, datum)
    column += 1
  end
end

#to_s ⇒ Object 



201
202
203
204
205
206
 
# File 'lib/applications/jexcel_file.rb', line 201

def to_s
  outs = ByteArrayOutputStream.new
  @workbook.write(outs);
  outs.close();
  String.from_java_bytes(outs.toByteArray)
end

#worksheet(index) ⇒ Object

Return a sheet by index



128
129
130
131
132
133
134
 
# File 'lib/applications/jexcel_file.rb', line 128

def worksheet( index )  
  if(@workbook)
    x = index.is_a?(String) ? @workbook.getSheetIndex(index.to_java(java.lang.String)) : index
    return @workbook.getSheetAt(x) 
  end
  nil
end

#worksheets ⇒ Object 



136
137
138
 
# File 'lib/applications/jexcel_file.rb', line 136

def worksheets
  (0...@workbook.getNumberOfSheets).collect { |i| @workbook.getSheetAt(i) }
end

#write(filename = nil) ⇒ Object Also known as: save 



188
189
190
191
192
193
 
# File 'lib/applications/jexcel_file.rb', line 188

def write( filename = nil )
  filename.nil? ? file = @filepath : file = filename
  out = FileOutputStream.new(file)
  @workbook.write(out) unless @workbook.nil?
  out.close
end