# Copyright:: Autotelik Media Ltd # Author :: Tom Statter # Date :: July 2010 # License:: # # A wrapper around creating and directly manipulating Excel files. # # i.e Create and populate XSL files # # jar added to class path in manifest - 'poi-3.5-beta4-20081128.jar' # if DataShift::Guards.jruby? require 'java' require 'poi-3.7-20101029.jar' require_relative 'ruby_poi_translations' class JExcelFile include RubyPoiTranslations extend RubyPoiTranslations include Enumerable java_import 'org.apache.poi.hssf.util.HSSFColor' java_import 'org.apache.poi.poifs.filesystem.POIFSFileSystem' java_import 'org.apache.poi.hssf.usermodel.HSSFCell' java_import 'org.apache.poi.hssf.usermodel.HSSFWorkbook' java_import 'org.apache.poi.hssf.usermodel.HSSFCellStyle' java_import 'org.apache.poi.hssf.usermodel.HSSFDataFormat' java_import 'org.apache.poi.hssf.usermodel.HSSFClientAnchor' java_import 'org.apache.poi.hssf.usermodel.HSSFRichTextString' attr_accessor :workbook, :row, :date_style attr_reader :sheet, :current_sheet_index # NOTE: this is the POI 3.7 HSSF maximum rows @@maxrows = 65535 def self.maxrows @@maxrows end def self.date_format HSSFDataFormat.getBuiltinFormat('m/d/yy h:mm') end def self.open(file_name) HSSFWorkbook.new(FileInputStream.new(file_name)) end # NOTES : # The HSSFWorkbook uses 0 based indexes 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 def open(file_name) @workbook = JExcelFile.open(file_name) @date_style = @workbook.createCellStyle @date_style.setDataFormat( JExcelFile.date_format ) activate_sheet(0) @workbook end # Create and return a new worksheet. # Not set to the active worksheet 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 # Set the supplied sheet index or name, as the active sheet and return it. # If no such sheet return current sheet 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 # Return a sheet by index 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 def worksheets (0...@workbook.getNumberOfSheets).collect { |i| @workbook.getSheetAt(i) } end # Create new row (indexing in line with POI usage, start 0) 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 def num_rows @sheet.getPhysicalNumberOfRows end # Process each row. Row type is org.apache.poi.hssf.usermodel.HSSFRow # 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. def each(_skip = nil, &block) @sheet.rowIterator.each(&block) end def row( index ) @sheet.getRow(index) || create_row(index) end # Get the enriched value of the Cell at row, column. def cell(row, column) row = row(row) cell_value( row.get_or_create_cell( column ) ) end # Get the enriched value of the Cell at row, column. def [](row, column) cell(row, column) end def []=(row, column, value) set_cell(row, column, value) end def set_cell(row, column, value) @row = row(row) @row[column] = value end def sanitize_sheet_name( name ) name.gsub(/[\[\]:\*\/\\\?]/, '') end 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 alias save write def save_to_text( file_name ) File.open( file_name, 'w') { |f| f.write(to_s) } end def to_s outs = ByteArrayOutputStream.new @workbook.write(outs) outs.close String.from_java_bytes(outs.toByteArray) end private def create_sheet_and_set_styles( sheet_name ) name = sanitize_sheet_name(sheet_name) @sheet = @workbook.createSheet( name ) @patriarchs.store(name, @sheet.createDrawingPatriarch) @date_style = @workbook.createCellStyle @date_style.setDataFormat( JExcelFile.date_format ) @sheet end end require 'jexcel_file_extensions' require 'apache_poi_extensions' end