title: Axlsx, building dynamic columns
body: <p>I have been commissioned to make a dynamic report building plugin for one of our systems here at work. the thing is that these reports are quite complex with 19 worksheets per report each sheet contains multiple tables, that are going to be populated with various amounts and types of data including data validations(drop-downs).</p>
  
  <p>My initial thought was lets find a ruby gem. since its a rails app. there isn't much to pick from, but i did find <a href="https://github.com/randym/axlsx" rel="nofollow">Axlsx</a> which looks like is my last hope. </p>
  
  <p>One of the tables has 6 columns, of which 4 need data validation.</p>
  
  <p>Now i have seen that you can style each column and even change formats through the <code>.add_style</code> method, </p>
  
  <pre><code>p.workbook do |wb|
    # define your regular styles
    styles = wb.styles
    title = styles.add_style :sz =&gt; 15, :b =&gt; true, :u =&gt; true
    default = styles.add_style :border =&gt; Axlsx::STYLE_THIN_BORDER
    header = styles.add_style :bg_color =&gt; '00', :fg_color =&gt; 'FF', :b =&gt; true
    profit = styles.add_style :border =&gt; Axlsx::STYLE_THIN_BORDER
    percent = styles.add_style :num_fmt =&gt; Axlsx::NUM_FMT_PERCENT, :border =&gt; Axlsx::STYLE_THIN_BORDER
  </code></pre>
  
  <p>and apply those styles when creating rows.</p>
  
  <pre><code>ws.add_row ['Quarter', 'Profit', '% of Total'], :style =&gt; header
  ws.add_row ['Q1-2010', 'yes', '=B4/SUM(B4:B7)'], :style =&gt; [default, profit, percent]
  </code></pre>
  
  <p>and i have seen that there is a way of validating data.</p>
  
  <pre><code>p.workbook.add_worksheet(name: "dropdown") do |ws|
    ws.add_row ["rank_type"]
    ws.add_data_validation("A2:A1000", {
      :type =&gt; :list,
      :formula1 =&gt; 'lists!A2:A4',
      :showDropDown =&gt; false,
      :showErrorMessage =&gt; true,
      :errorTitle =&gt; '',
      :error =&gt; 'Please use the dropdown selector to choose a valid rank type',
      :errorStyle =&gt; :stop,
      :showInputMessage =&gt; true,
      :promptTitle =&gt; 'Rank type',
      :prompt =&gt; 'Please select a valid rank type'})
  end
  </code></pre>
  
  <p>but is there a way to specify the cells that need validation when adding rows. or to add them into tables of variying sizes at all?</p>
  
  <p>i was thinking it would be cool if you could specify data validations the same way you could styles. something like this.</p>
  
  <pre><code> boolean_validation = format.add_data_validation :type =&gt; list, :formula =&gt; 'lists!A2:A4'... (rest of the options)
  </code></pre>
  
  <p>then when creating the rows </p>
  
  <pre><code> ws.add_row ['Q1-2010', 'yes', '=B4/SUM(B4:B7)'], :style =&gt; [default, profit, percent], :format =&gt; [nil, boolean_validation, nil]
  </code></pre>
  
  <p>Has any one attempted something like this is this even possible? or any one got an idea on how to build such </p>