RubyXL - Copying cells between the sheets

Question

How can I copy a cell from one sheet (or worksheet) with all styles (not only value) and paste to another? Thanks for answer.

Answer 1

Can't help you with RubyXL but here is a tested sript that does just that. You will need to be on Windows though.

require 'win32ole'

xl=WIN32OLE::new('Excel.Application')
xl.Visible=0 #hide excel, 1 = show

xl.Workbooks.Open('c:\\temp\\Map1.xlsx') #will open a file
sheet = xl.sheets(1)
sheet.activate #activate the sheet you want
xlPasteValues = -4163 #(&HFFFFEFBD)
xlPasteSpecialOperationNone = -4142 #(&HFFFFEFD2)
xlPasteFormats = -4122 # Copied source format is pasted
xlPasteValuesAndNumberFormats = 12
sheet.Range("A1:A2").Copy
sheet.Range("B1:B2").PasteSpecial(xlPasteValuesAndNumberFormats)
sheet.Range("B1:B2").PasteSpecial(xlPasteFormats)
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close()
xl.Quit

Answer 2

Took the time to spit this out because I use RubyXL on a regular base. Seems there is a much better gem axlsx to work with spreadsheets, more logical and much more things you can do. Here a simple example of setting and copying styles. I keep my other answer for reference because it works but has obvious drawnbacks.

require 'axlsx'

p = Axlsx::Package.new
wb = p.workbook

wb.add_worksheet(:name => "Basic Worksheet") do |sheet|
  sheet.add_row ["First Column", "Second", "Third"]
  sheet.add_row [1, 2, 3]
  sheet.add_row ['     preserving whitespace']
  thin_border = Axlsx::STYLE_THIN_BORDER
  sheet['A1:A1'].first.style = thin_border
  sheet['A1:C1'].each{|cell|cell.style = sheet['A1'].style}
end

p.serialize('./simple.xlsx')