如何使用Axlsx将数组写入具有不同样式的列的Excel [英] How to write array to Excel with differing styles per column using Axlsx

问题描述

我想使用Axlsx宝石写出不同样式应用于不同列的Excel电子表格。

我正在写一个哈希数组整个行，因此，我似乎不能按需要格式化不同的列。

我不知道怎么说，列AD使用默认样式，但对于列E和F，使用水平中心对齐。

  require'axlsx'
 
p = Axlsx :: Package.new 
 wb = p.workbook 
 
＃所有单元格的默认样式
 standard_text = wb.styles.add_style（：alignment => ; {：vertical =>：center，：wrap_text => true}）
 
＃仅适用于行E和F中的单元格的自定义样式
 custom_text = wb.styles。 add_style（：alignment => {：horizo​​ntal =>：center}）
 
 assessment_technology_hashes.each do | rows | 
 sheet.add_row（rows.values，：height => 35，：style => standard_text）
 end 
  

下面是使用 add_row 行的散列数组的结构$ c>：

  {：vendor_name =>vendor，：link =>Link\\\
 ：important =>低优先级，：score =>5，：overall_score =>4.5，：match =>是，：access =>Anywhere，：title => ;Full Title} 
 {：vendor_name =>vendor2，：link ="Link2\\\
，：important =>中等优先级，：score =>7 ，：overall_score =>8.5，：match =>是，：access =>典型，：title =>全标题} 
 ... 
。 .. 
  

在这种情况下，正确的方法是将所有数据写入主要格式 standard_text 是所需的，然后在所有数据写入后应用自定义格式？

我认为这可能在循环中工作，只有当条件为真时才写出行，但总是返回false：

$ b $如果rows.key？（：overall_score）|| b（$）$（$）$ rows.key？（：match）

有人可以指出我正确的方向吗？ / p>

解决方案

这是我如何做的。

首先，我生成我的样式如下：

  p = Axlsx :: Package.new 
 wb = p.workbook 
 ＃style 
 style_1 = wb.styles.add_style bg_c​​olor：'3492ff'，font_name：'Calibri'
 style_2 = wb.styles.add_style bg_c​​olor：'ff8800'，font_name：'Arial'
  

阅读完我的样式后，我创建了一个工作表：

  wb.add_worksheet（name：'Example'）do | sheet | 
  

创建工作表后，我可以通过调用 add_row 即可。作为最后一个参数，我传递了一系列样式。示例：

  sheet.add_row ['列1'，'Column2']，样式：[style_1，style_2] 
  

以这种方式，我可以对单个列的单元格进行样式。如果您不想应用style_2，只需键入 [style_1，nil] 。

这是连接代码

  p = Axlsx :: Package.new 
 wb = p.workbook 
＃styles 
 style_1 = wb.styles.add_style bg_c​​olor：'3492ff'，font_name：'Calibri'
 style_2 = wb.styles.add_style bg_c​​olor：'ff8800'，font_name：'Arial'
＃工作表
 wb.add_worksheet（name：'Example'）do | sheet | 
 sheet.add_row ['Column 1'，'Column2']，style：[style_1，style_2] 
 end 
 end 
  

I would like to be able to use the Axlsx gem to write out an Excel spreadsheet with different styles applied to different columns.

I am writing an Array of Hashes an entire row at a time and, as a result, I cannot seem to format the columns differently as needed.

I don't know how to say, "for columns A-D use default styling, however for column E and F, use horizontal center alignment".

require 'axlsx'

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

# Default Style for all cells
standard_text = wb.styles.add_style( :alignment => {:vertical=>:center, :wrap_text=>true} )

# Custom styling to be applied to cells in rows E and F only
custom_text = wb.styles.add_style( :alignment => {:horizontal=>:center} )

assessment_technology_hashes.each do |rows|
  sheet.add_row(rows.values, :height => 35, :style => standard_text)
end

Here is the structure of the Array of hashes for rows that is being written with add_row:

{:vendor_name=>"vendor", :link=>"Link\n", :importance=>"Low Priority", :score=>"5", :overall_score=>"4.5", :match=>"Yes", :access=>"Anywhere", :title=>"Full Title"}
{:vendor_name=>"vendor2", :link=>"Link2\n", :importance=>"Medium Priority", :score=>"7", :overall_score=>"8.5", :match=>"Yes", :access=>"Typical", :title=>"Full Title"}
...
...

In this scenario, is the correct approach to write all of the data with the primary formatting standard_text that is desired, and then apply the custom formatting AFTER all the data has been written?

I thought this might work in the loop to only write out rows when the condition is true but it always returns false:

sheet.add_row(rows.values, :height => 35, :style => custom_text) if rows.key?(:overall_score) || rows.key?(:match)

Can someone point me in the correct direction to do this?

解决方案

Here is how I do it.

First, I generate my styles like this:

p  = Axlsx::Package.new
wb = p.workbook
  # styles
  style_1 = wb.styles.add_style bg_color: '3492ff', font_name: 'Calibri'
  style_2 = wb.styles.add_style bg_color: 'ff8800', font_name: 'Arial'

After my styles are read, I create a worksheet:

wb.add_worksheet(name: 'Example') do |sheet|

After worksheet has been created I am able to add data by calling add_row. And as a last argument, I pass array of styles. Example:

sheet.add_row ['Column 1', 'Column2'], style: [style_1, style_2]

In this way I am able to style individual column's cell. In case you don't want to apply style_2, just type [style_1, nil].

Here is the concatenated code

p  = Axlsx::Package.new
wb = p.workbook
  # styles
  style_1 = wb.styles.add_style bg_color: '3492ff', font_name: 'Calibri'
  style_2 = wb.styles.add_style bg_color: 'ff8800', font_name: 'Arial'
  # worksheet
  wb.add_worksheet(name: 'Example') do |sheet|
    sheet.add_row ['Column 1', 'Column2'], style: [style_1, style_2]
  end
end

这篇关于如何使用Axlsx将数组写入具有不同样式的列的Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！