Class: JExcelFile

Inherits:
Object
  • Object
show all
Extended by:
RubyPoiTranslations
Includes:
Enumerable, RubyPoiTranslations
Defined in:
lib/datashift/applications/jexcel_file.rb

Constant Summary collapse

@@maxrows =

NOTE: this is the POI 3.7 HSSF maximum rows

65535

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from RubyPoiTranslations

cell_value, poi_cell_type, poi_cell_value

Constructor Details

#initialize ⇒ JExcelFile

NOTES :

The HSSFWorkbook uses 0 based indexes



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

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 = {}

  @date_style = nil
end

Instance Attribute Details

#current_sheet_index ⇒ Object (readonly)

Returns the value of attribute current_sheet_index.



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

def current_sheet_index
  @current_sheet_index
end

#date_style ⇒ Object

Returns the value of attribute date_style.



36
37
38
 
# File 'lib/datashift/applications/jexcel_file.rb', line 36

def date_style
  @date_style
end

#row(index) ⇒ Object

Returns the value of attribute row.



36
37
38
 
# File 'lib/datashift/applications/jexcel_file.rb', line 36

def row
  @row
end

#sheet ⇒ Object (readonly)

Returns the value of attribute sheet.



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

def sheet
  @sheet
end

#workbook ⇒ Object

Returns the value of attribute workbook.



36
37
38
 
# File 'lib/datashift/applications/jexcel_file.rb', line 36

def workbook
  @workbook
end

Class Method Details

.date_format ⇒ Object 



46
47
48
 
# File 'lib/datashift/applications/jexcel_file.rb', line 46

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

.maxrows ⇒ Object 



42
43
44
 
# File 'lib/datashift/applications/jexcel_file.rb', line 42

def self.maxrows
  @@maxrows
end

.open(file_name) ⇒ Object 



50
51
52
 
# File 'lib/datashift/applications/jexcel_file.rb', line 50

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

Instance Method Details

#[](row, column) ⇒ Object

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



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

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

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



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

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

#activate_sheet(term) ⇒ Object

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



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

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

#cell(row, column) ⇒ Object

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



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

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

#create_row(index) ⇒ Object

Create new row (indexing in line with POI usage, start 0)



139
140
141
142
143
144
 
# File 'lib/datashift/applications/jexcel_file.rb', line 139

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



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

def create_worksheet( options = {} )
  sheet_name = options[:name]

  @workbook = HSSFWorkbook.new if @workbook.nil?

  if sheet_name

    name = sanitize_sheet_name( sheet_name )

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

    activate_sheet(name)

  else
    i = 0
    # there is no hard limit to no of sheets in Excel but at some point you will run out of memory!
    begin
      sheet_name = "Worksheet#{i += 1}"
    end while(@workbook.getSheetIndex(sheet_name) >= 0)

    return create_sheet_and_set_styles( sheet_name )
  end
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/datashift/applications/jexcel_file.rb', line 159

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

#num_rows ⇒ Object 



146
147
148
 
# File 'lib/datashift/applications/jexcel_file.rb', line 146

def num_rows
  @sheet.getPhysicalNumberOfRows
end

#open(file_name) ⇒ Object 



70
71
72
73
74
75
76
77
78
 
# File 'lib/datashift/applications/jexcel_file.rb', line 70

def open(file_name)
  @workbook = JExcelFile.open(file_name)

  @date_style = @workbook.createCellStyle
  @date_style.setDataFormat( JExcelFile.date_format )

  activate_sheet(0)
  @workbook
end

#sanitize_sheet_name(name) ⇒ Object 



188
189
190
 
# File 'lib/datashift/applications/jexcel_file.rb', line 188

def sanitize_sheet_name( name )
  name.gsub(/[\[\]:\*\/\\\?]/, '')
end

#save_to_text(file_name) ⇒ Object 



201
202
203
 
# File 'lib/datashift/applications/jexcel_file.rb', line 201

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

#set_cell(row, column, value) ⇒ Object 



182
183
184
185
186
 
# File 'lib/datashift/applications/jexcel_file.rb', line 182

def set_cell(row, column, value)
  @row = row(row)

  @row[column] = value
end

#to_s ⇒ Object 



205
206
207
208
209
210
 
# File 'lib/datashift/applications/jexcel_file.rb', line 205

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



126
127
128
129
130
131
132
 
# File 'lib/datashift/applications/jexcel_file.rb', line 126

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 



134
135
136
 
# File 'lib/datashift/applications/jexcel_file.rb', line 134

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

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



192
193
194
195
196
197
 
# File 'lib/datashift/applications/jexcel_file.rb', line 192

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