Ruby使用Axlsx保存格式化本地表格时报错求助

错误原因 #

触发该错误的核心是路径字符串的转义处理错误：

• Ruby双引号字符串中，\是转义符，比如\n会被解析为换行符、\s会被解析为空白符正则元字符。
• 代码中local_file_path = "new\spreadsheet\output.xlsx"里的\s和\n被错误转义，导致Axlsx处理路径时触发正则语法错误。

同时代码存在两个次要问题：

1. send_email方法定义接收local_file_path参数，但调用时传入的是file_path，且方法内部错误引用未定义的file_path变量。
2. 先将数据转为制表符分隔的纯文本再拆分生成Excel，属于冗余步骤，容易引入格式错误。

修复方案 #

1. 修正路径字符串 #

以下三种写法任选其一：

• 使用单引号字符串（单引号中\不会被转义）：

  local_file_path = 'new/spreadsheet/output.xlsx'
  

• 使用正斜杠作为路径分隔符（Ruby跨平台支持正斜杠）：

  local_file_path = "new/spreadsheet/output.xlsx"
  

• 转义反斜杠（用\\表示单个\）：

  local_file_path = "new\\spreadsheet\\output.xlsx"
  

2. 修正send_email方法的参数问题 #

调整方法调用和内部逻辑，确保参数传递正确：

def process_order(file_path)
  order_details = read_spreadsheet(file_path)
  local_file_path = 'new/spreadsheet/output.xlsx'
  save_to_excel(order_details, local_file_path)
  send_email(local_file_path, order_details) # 传递正确参数
end

def send_email(local_file_path, order_details)
  # 省略邮件配置代码...
  
  # 直接使用传入的order_details，无需重复读取表格
  formatted_data = format_data_plain_text(order_details)

  mail = Mail.new do
    # 省略邮件内容配置...
    add_file local_file_path
  end
  mail.deliver!
end

3. 优化Excel生成逻辑（可选） #

跳过纯文本中间步骤，直接用原始数据生成Excel，减少出错概率：

def save_to_excel(data, local_file_path)
  Axlsx::Package.new do |p|
    p.workbook.add_worksheet(name: 'Reorder Details') do |sheet|
      sheet.add_row(['SKU', 'QUANTITY'])
      data.each do |row|
        unless row['REORDER QTY'].to_i.zero? || row['AC SKU'].to_s.empty?
          sheet.add_row([row['AC SKU'], row['REORDER QTY']])
        end
      end
    end
    p.serialize(local_file_path)
  end
end

完整修正后的代码 #

require 'bundler'
require 'roo'
require 'mail'
require 'date'
require 'axlsx'
Bundler.require

begin
def read_spreadsheet(file_path)
  spreadsheet = Roo::Excelx.new(file_path)
  header = spreadsheet.row(2)
  data = []
  (3..spreadsheet.last_row).each do |i|
    row = Hash[[header, spreadsheet.row(i)].transpose]
    data << row
  end
  data
end

def format_data_plain_text(data)
  formatted_data = ""
  data.each do |row|
    unless row['REORDER QTY'].to_i.zero? || row['AC SKU'].to_s.empty?
      sku = row['AC SKU'].to_s
      quantity = row['REORDER QTY'].to_s
      formatted_data += "#{sku}\t#{quantity}\n"
    end
  end
  formatted_data.to_s
end

def save_to_excel(data, local_file_path)
  Axlsx::Package.new do |p|
    p.workbook.add_worksheet(name: 'Reorder Details') do |sheet|
      sheet.add_row(['SKU', 'QUANTITY'])
      data.each do |row|
        unless row['REORDER QTY'].to_i.zero? || row['AC SKU'].to_s.empty?
          sheet.add_row([row['AC SKU'], row['REORDER QTY']])
        end
      end
    end
    p.serialize(local_file_path)
  end
end

def process_order(file_path)
  order_details = read_spreadsheet(file_path)
  local_file_path = 'new/spreadsheet/output.xlsx'
  save_to_excel(order_details, local_file_path)
  send_email(local_file_path, order_details)
end

def send_email(local_file_path, order_details)
  options = {
    address: 'smtp.gmail.com',
    port: 587,
    user_name: 'email@email.com',
    password: 'password',
    domain: 'domain.com',
    authentication: 'plain',
    enable_starttls_auto: true
  }
  Mail.defaults do
    delivery_method :smtp, options
  end

  formatted_data = format_data_plain_text(order_details)

  mail = Mail.new do
    from    'email@email.com'
    to      'email@email.com'
    subject "Order Details:"
    body    "Hey Tim. Hope you're doing well! I just need to place an order for the following items. This will be using Net Terms.\n\n#{formatted_data}\nPlease let me know if you need anything else from me! Have a great week!"
    add_file local_file_path
  end
  mail.deliver!
end

file_path = 'path/to/original/spreadsheet/012224 COUNT.xlsx'
process_order(file_path)

end

内容的提问来源于stack exchange，提问作者Zach S