spreadsheet_alignments

main.rb

require 'spreadsheet'

class DailyOrdersXlsFactory

  #formats:
  @@simple_format       = Spreadsheet::Format.new :color => :black, :weight => :normal, :size => 10
  @@title_format        = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 14, :vertical_align => :center, :horizontal_align => :center
  @@total_format        = Spreadsheet::Format.new :color => :black, :weight => :bold, :vertical_align => :center, :horizontal_align => :right
  @@price_format        = Spreadsheet::Format.new :color => :black, :vertical_align => :center, :horizontal_align => :center
  @@price_title_format  = Spreadsheet::Format.new :color => :black, :vertical_align => :center, :horizontal_align => :right, :weight => :bold

  @@normal_format         = Spreadsheet::Format.new :weight => :normal, :size => 10
  @@normal_center_format  = Spreadsheet::Format.new :weight => :normal, :size => 10, :color => :black, :horizontal_align => :center, :vertical_align => :center, :border => :thin
  @@normal_left_format    = Spreadsheet::Format.new :weight => :normal, :size => 10, :color => :black, :horizontal_align => :left,   :vertical_align => :center, :border => :thin, :text_wrap => true
  @@normal_right_format   = Spreadsheet::Format.new :weight => :normal, :size => 10, :color => :black, :horizontal_align => :right,  :vertical_align => :center, :border => :thin
  @@normal_right_format_not_border = Spreadsheet::Format.new :color => :black, :size => 10, :vertical_align => :center, :horizontal_align => :right

  @@bold_format                   = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10
  @@bold_center_format            = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :center
  @@bold_center_format_wbordered  = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :center, :border => :medium
  @@bold_center_format_tbordered  = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :center, :border => :thin
  @@bold_left_format              = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :left
  @@bold_left_format_wbordered    = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :left,   :border => :medium
  @@bold_left_format_tbordered    = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :left,   :border => :thin
  @@bold_left_format_tbordered_nright = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :left, :border => :thin, :right => false
  @@bold_left_format_tbordered_nleft  = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10, :vertical_align => :center, :horizontal_align => :left, :border => :thin, :left => false

  @@merged_simple_format      = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :normal, :size => 10, :vertical_align => :center, :border => :thin
  @@merged_heading_format     = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :bold, :size => 14
  @@merged_bold_left_format   = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :bold, :size => 10, :horizontal_align => :left
  @@merged_bold_center_format = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :bold, :size => 10
  @@merged_bold_center_tbordered_format = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :bold, :size => 10, :border => :thin
  #there is a point:
  @@merged_bold_left_tbordered_format   = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :bold, :size => 10, :border => :thin, :horizontal_align => :left
  @@merged_left_format        = Spreadsheet::Format.new :align => :merge, :color => :black, :weight => :normal, :size => 10, :vertical_align => :center, :text_wrap => true, :border => :thin

  def initialize(name)
    @reportbook = Spreadsheet::Workbook.new
    @sheet = @reportbook.create_worksheet(:name => name)
    self
  end
  def get_last_row_index
    @sheet.rows.count - 1
  end
  def get_document_height
    @sheet.rows.count
  end
  def set_row_height(row, height)
    @sheet.row(row).height = height
    self
  end
  def add_column(width)
    column_index = @sheet.columns.count
    @sheet.column(column_index).width = width
    @sheet.column(column_index).default_format = @@price_format
    self
  end
  def add_row(values, row_height = nil)
    write_row_values(@sheet.rows.count, values)
    @sheet.row(@sheet.rows.count-1).height = row_height unless row_height.nil?
    self
  end
  def set_row_height(row, height)
    @sheet.row(row).height = height
    self
  end
  def write_row_values(row, values)
    values.each_with_index { |value, i|
      write(row, i, value)
    }
    self
  end
  def write(row, column, text, height = nil)
    @sheet.row(row).set_format(column, @current_format) unless @current_format.nil?
    @sheet[row, column] = text
    @sheet.row(row).height = height unless height.nil?
    self
  end

  # Format Cursor Setters:
  #
  def set_title_format;                @current_format = @@title_format;                self; end
  def set_bold_format;                 @current_format = @@bold_format;                 self; end
  def set_price_format;                @current_format = @@price_format;                self; end
  def set_total_format;                @current_format = @@total_format;                self; end
  def set_price_title_format;          @current_format = @@price_title_format;          self; end
  def set_normal_left_format;          @current_format = @@normal_left_format;          self; end
  def set_normal_center_format;        @current_format = @@normal_center_format;        self; end
  def set_bold_center_format;          @current_format = @@bold_center_format;          self; end
  def set_bold_left_format;            @current_format = @@bold_left_format;            self; end  
  def set_bold_left_format_tbordered;  @current_format = @@bold_left_format_tbordered;  self; end
  def set_bold_left_format_wbordered;  @current_format = @@bold_left_format_wbordered;  self; end
  def set_bold_center_format_tbordered;@current_format = @@bold_center_format_tbordered;self; end
  def set_bold_center_format_wbordered;@current_format = @@bold_center_format_wbordered;self; end

  # Apply formats to cells
  #
  def apply_normal_right_format_not_border(row, column)
    @sheet.row(row).set_format(column, @@normal_right_format_not_border); self
  end
  def apply_normal_right_format(row, column)
    @sheet.row(row).set_format(column, @@normal_right_format); self
  end
  def apply_normal_left_format(row, column)
    @sheet.row(row).set_format(column, @@normal_left_format); self
  end
  def apply_bold_left_format_tbordered_nright(row, column)
    @sheet.row(row).set_format(column, @@bold_left_format_tbordered_nright); self
  end
  def apply_bold_left_format_tbordered_nleft(row, column)
    @sheet.row(row).set_format(column, @@bold_left_format_tbordered_nleft); self
  end
  def apply_bold_center_format_wbordered(row, column)
    @sheet.row(row).set_format(column, @@bold_center_format_wbordered); self
  end
  def apply_bold_center_format_tbordered(row, column)
    @sheet.row(row).set_format(column, @@bold_center_format_tbordered); self
  end
  def apply_normal_center_format(row, column)
    @sheet.row(row).set_format(column, @@normal_center_format); self
  end
  def apply_bold_center_format(row, column)
    @sheet.row(row).set_format(column, @@bold_center_format); self
  end
  def apply_title_format(row, column)
    @sheet.row(row).set_format(column, @@title_format); self
  end

  #Cells Merging:
  #
  def apply_range_format_for_last_row(range, format)
    joined_row = @sheet.row(get_last_row_index)
    range.each do |i|
      joined_row.set_format(i, format)
    end
    self
  end
  def join_last_row_heading(range)
    apply_range_format_for_last_row range, @@merged_heading_format
  end
  def join_last_row_bold_left(range)
    apply_range_format_for_last_row range, @@merged_bold_left_format
  end
  def join_last_row_bold_center(range)
    apply_range_format_for_last_row range, @@merged_bold_center_format
  end
  def join_last_row_bold_center_tbordered(range)
    apply_range_format_for_last_row range, @@merged_bold_center_tbordered_format
  end  
  def join_last_row_simple(range)
    apply_range_format_for_last_row range, @@merged_left_format
  end
  
  #NOT WORKED!>>
  def join_last_row_bold_left_tbordered(range)
    apply_range_format_for_last_row range, @@merged_bold_left_tbordered_format
  end

  #Post functions
  #
  def save_to_file fname
    f = File.new(fname, "w+")
    @reportbook.write(f.path)
    f.close
    f.path
  end
  def get_report
    @reportbook
  end
  def get_sheet
    @sheet
  end
end


@report = DailyOrdersXlsFactory.new("simple report")
@report.add_column(10).add_column(20).add_column(30)
@report.add_row(["Test row"], 30).join_last_row_heading(0..2)
  #if add - merging not working:
        @report.join_last_row_bold_left_tbordered(0..2)
        
@report.save_to_file("test.xls")

Dear Vitaly

Thanks for the script.

Running the script does not create test.xls on my machine. Why? Also what Ruby Version are you using? I am on 1.9.3.

Best
Zeno

On 07/30/2012 03:48 PM, Zeno R.R. Davatz wrote: Dear Vitaly Thanks for the script. Running the script does not create test.xls on my machine. Why? Also what Ruby Version are you using? I am on 1.9.3. Best Zeno --- Reply to this email directly or view it on GitHub: https://gist.github.com/3206637

Dear Zeno Thanks for reply. I use ruby 1.9.2p320 (2012-04-20 revision 35421) [i686-linux] Version of ruby. Also, I use spreadsheet (0.7.3) gem - I think this is newest version. Please, try specify other destination for save_to_file() method. Maybe, @report.save_to_file("./report.xls")

Ok, I got the test.xls file now. Can you upload an expected outcome file to github an link it here. This way I can see what your expected outcome should be. Are you using Microsoft Office or LibreOffice to view the file?

Is this not your expected outcome?

https://twitter.com/zdavatz/status/229925439597076480/photo/1/large

Best
Zeno

No, I expected to get three merged cells with text that left-aligned . If need screenshot, I think I can upload somewhere. But, it is very simple: merged left-aligned cells.
I use OpenOffice for Linux (Ubuntu)

Yes, please upload the screenshot with your expected outcome somewhere, i.e. Twitter and link it here.

https://twitter.com/AriekDev/status/229929112653930499/photo/1

So basically "very simple cell merging" does not work for you. Correct?

Have you seen this: https://groups.google.com/forum/?fromgroups#!searchin/rubyspreadsheet/merge/rubyspreadsheet/usuQkDCXDHc/AD6uGBAr7ZUJ and have you tried it?

I seems that your example sets cells formats, and then inputs text there..
Hmm.. I'll try and then say you what is going on. Thanks!

Yes, please let me know here of your outcome.

Also, have a look at the spreadsheet code:

lib/spreadsheet/worksheet.rb

on line

285

it says: https://github.com/zdavatz/spreadsheet/blob/master/lib/spreadsheet/worksheet.rb#L285

So it should work.

Best
Zeno

Also Timon added this feature in 2011:

zdavatz/spreadsheet@e5906f3

Best
Zeno

Oki, I'll check this feature and add to my library if this will be useful.
Many thanks for now,

Best
Vitaly

Dear Vitaly

Also see this on how the functionality got added.

voraz/spreadsheet#1

If it works for you, please supply us with a sample code of yours how you merged your cells. That would be great.

Best
Zeno

Hello, I don' tknow if anyone is still reading these. I am new to programming and I am trying to make one line of my code autofit into a cell. Is that possible? I didn't see what I was looking for up top. Thanks.
Cam

Hello, I don' tknow if anyone is still reading these. I am new to programming and I am trying to make one line of my code autofit into a cell. Is that possible? I didn't see what I was looking for up top. Thanks.
Cam

Hi Cam,
is your question still actual? :)
@camdbel