Class: JExcelFile

Inherits:
Object show all
Defined in:
lib/helpers/jruby/jexcel_file.rb

Constant Summary collapse

MAX_COLUMNS = 
256.freeze
MAX_ROWS = 
65536.freeze

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ JExcelFile

The HSSFWorkbook uses 0 based indexes



51
52
53
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 51

def initialize()
  @book = nil
end

Instance Attribute Details

#book ⇒ Object

Returns the value of attribute book.



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

def book
  @book
end

#current_sheet ⇒ Object

Returns the value of attribute current_sheet.



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

def current_sheet
  @current_sheet
end

#row ⇒ Object

Returns the value of attribute row.



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

def row
  @row
end

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

Return the current or specified HSSFSheet



74
75
76
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 74

def sheet
  @sheet
end

Class Method Details

.date_format ⇒ Object 



45
46
47
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 45

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

Instance Method Details

#cell_value(cell) ⇒ Object 



108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 108

def cell_value(cell)
  return nil unless cell
  #puts "DEBUG CELL TYPE : #{cell} => #{cell.getCellType().inspect}"
  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_row(index) ⇒ Object

Create new row



92
93
94
95
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 92

def create_row(index)
  @row = @sheet.createRow(index)
  @row
end

#create_sheet(sheet_name) ⇒ Object

TOFIX - how do we know which sheet we are creating so we can set index @current_sheet



64
65
66
67
68
69
70
71
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 64

def create_sheet(sheet_name)
  @current_sheet = 0
  @book = HSSFWorkbook.new()
  @sheet = @book.createSheet(sheet_name.gsub(" ", ''))
  date_style = @book.createCellStyle()
  date_style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))
  @sheet
end

#each_row ⇒ Object

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



86
87
88
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 86

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

#num_rows ⇒ Object 



80
81
82
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 80

def num_rows
  @sheet.getPhysicalNumberOfRows
end

#open(filename) ⇒ Object 



55
56
57
58
59
60
61
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 55

def open(filename)
  inp = FileInputStream.new(filename)

  @book = HSSFWorkbook.new(inp)

  sheet(0)  # also sets @current_sheet
end

#save(filename) ⇒ Object 



120
121
122
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 120

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

#set_cell(row, column, data) ⇒ Object 



97
98
99
100
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 97

def set_cell(row, column, data)
  @row = @sheet.getRow(row) || create_row(row)
  @row.createCell(column).setCellValue(data)
end

#set_headers(headers) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 125

def set_headers(headers)

  return if headers.empty?

  create_sheet( headers.first.class.name ) unless(@sheet)

  row = sheet.createRow(0)

  headers.each_with_index.each do |column, i|
    row.createCell(i).setCellValue(column.name.to_s)
  end
end

#to_s ⇒ Object

The internal representation of a Excel File



181
182
183
184
185
186
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 181

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

#to_xls(items) ⇒ Object 



139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 139

def to_xls(items)
  return if items.empty?

  create_sheet( items.first.class.name ) unless(@sheet)

  set_headers( items.first.class.columns )

  date_style = @book.createCellStyle
  date_style.setDataFormat( JExcelFile::date_format )

  # value rows
  row_index = 1
  items.each do |item|
    row = sheet.createRow(row_index);

    cell_index = 0
    item.class.columns.each do |column|
      cell = row.createCell(cell_index)
      
      if column.sql_type =~ /date/ then

        millis = item.send(column.name).to_f * 1000
        cell.setCellValue(Date.new(millis))

        cell.setCellStyle( date_style );
      elsif column.sql_type =~ /int/ then
        cell.setCellValue(item.send(column.name).to_i)

      else
        value = item.send(column.name)
        cell.setCellValue( value.to_s ) unless value.nil?
      end

      cell_index += 1
    end
    row_index += 1
  end
  @excel.to_s
end

#value(row, column) ⇒ Object

Raises:

  • (TypeError) 


102
103
104
105
106
 
# File 'lib/helpers/jruby/jexcel_file.rb', line 102

def value(row, column)
  raise TypeError, "Expect row argument of type HSSFRow" unless row.is_a?(Java::OrgApachePoiHssfUsermodel::HSSFRow)
  #puts "DEBUG - CELL VALUE : #{column} => #{ cell_value( row.getCell(column) ).inspect}"
  cell_value( row.getCell(column) )
end