Formatting a cell as Text using the axlsx spreadsheet ruby gem?

Question

I'm using the axlsx ruby gem to create Excel-compatible .xlsx files. I can't figure out how to override the cell type that is generated by it's automatic type detection. For Active Record model attributes of type string the gem is setting the Excel cell format to General, but I want it to use Text explicitly. That way I can avoid stripping leading zeros off of zip codes, etc.

Anybody know how to accomplish this?

Bracer

Answer 1

You can override the type of data using the types option on add row.

Something like:

worksheet.add_row ['0012342'], :types => [:string]

Grab me on irc (JST) if you need any help getting that to work.

Best

randym

edit --

I've added an example for this to examples/example.rb in the repo.

wb.add_worksheet(:name => "Override Data Type") do |sheet|
  sheet.add_row ['dont eat my zeros!', '0088'] , :types => [nil, :string]
end

https://github.com/randym/axlsx/blob/master/examples/example.rb#L349

Technique

I don't quite understand how it works, I change a cell type to integer and when I see results in the document (ctrl + 1) it still has general type (LibreOfficce) and when it's string it just preserves leading zeros but general type is still there. – Rochet

@Technique in my use-case the user may change the data by typing in the excel sheet or add new rows later. I need to set type to string to the entire column. Can this be done with Axlsx? Kindly help – Beige

Any idea why type "currency" is not supported in cell types? I generated an excel using axlsx; I explicitly added commas to identify thousandth separator and also prefixed it with a '$' symbol; now when I selected multiple cells in a row, I don't see the total displayed in the bottom. If I use integer or float datatype, then I cannot have thousandth separator commas and the $ symbol prefix. What is the solution for this? pls help!! – Voltz

Answer 2

format_code: '@' will work for you. Please find below code for reference.

def default_data_type_as_string
    @xlsx_package = Axlsx::Package.new
    @workbook = @xlsx_package.workbook
    @worksheet = @workbook.add_worksheet(:name => "Introduction")
    default_style = @workbook.styles.add_style({ format_code: '@' })
    row_data_array = ['1', '2%', '3$']
    @worksheet.add_row row_data_array, :style => [nil, default_style, nil]
    @xlsx_package.serialize('default_data_type_as_string.xlsx')
end

Electrodeposit

Answer 3

For gem versions gem 'axlsx', '2.1.0.pre', gem 'axlsx_rails' in order to have the file columns in text type should specify both style and type

default_style = worksheet.styles.add_style({ format_code: '@' })
worksheet.add_row ['0012687'], :types => [:string], :style => [default_style]

Siena