Ruby生成CSV文件后₹、€等货币符号在Microsoft Excel中无法正常显示及编码转换错误的解决方案咨询

I've run into this exact frustrating issue before—Excel's quirky encoding handling for CSVs can throw off non-ASCII characters like currency symbols. Let's break down why this happens and the best fixes:

The core problems here are:

1. Excel doesn't automatically detect UTF-8 CSVs unless they include a Byte Order Mark (BOM).
2. ISO-8859-9 (Latin-5) doesn't support the Indian Rupee symbol (U+20B9), which is why you hit that conversion error.

Here are the most reliable solutions to get your symbols showing up correctly in Excel:

Solution 1: Add UTF-8 BOM to Your CSV #

Modern Excel versions recognize UTF-8 CSVs if they start with the UTF-8 BOM (\uFEFF). This is the simplest, most straightforward fix:

# Generate your CSV content as you did before
csv_content = CSV.generate(headers: true) do |csv| 
  csv << ['Date', 'Transaction', 'Order Total', 'Wallet Amount', 'Wallet Balance'] 
  fetch_data.each do |data_response| 
    data_response.wallet_amount = "₹40" 
    csv_data = { date: data_response.display_date}.merge(data_response.to_h.except(*skip_attributes)) 
    csv << csv_data.values 
  end 
end

# Prepend the UTF-8 BOM to trigger Excel's correct encoding detection
csv_with_bom = "\uFEFF" + csv_content

# Write the file with explicit UTF-8 encoding to avoid any surprises
File.write('transactions.csv', csv_with_bom, encoding: 'UTF-8')

When you open this CSV in Excel, it should now correctly parse the UTF-8 content and display ₹, €, and other non-ASCII characters without garbling them.

Solution 2: Use UTF-16 Encoding (For Older Excel Versions) #

If you need to support older Excel builds that still struggle with UTF-8 BOM, UTF-16 is a foolproof alternative. Excel natively supports UTF-16, and we'll use the LE (Little Endian) variant with its own BOM:

# Generate CSV with tab separators (more reliable for UTF-16 than commas)
csv_content = CSV.generate(headers: true, col_sep: "\t") do |csv| 
  csv << ['Date', 'Transaction', 'Order Total', 'Wallet Amount', 'Wallet Balance'] 
  fetch_data.each do |data_response| 
    data_response.wallet_amount = "₹40" 
    csv_data = { date: data_response.display_date}.merge(data_response.to_h.except(*skip_attributes)) 
    csv << csv_data.values 
  end 
end

# Convert to UTF-16LE and add its specific BOM (\xFF\xFE)
csv_utf16 = csv_content.encode('UTF-16LE', 'UTF-8')
csv_utf16_with_bom = "\xFF\xFE" + csv_utf16

File.write('transactions.csv', csv_utf16_with_bom)

This method guarantees compatibility with nearly all Excel versions, and using tabs instead of commas helps avoid parsing quirks that can come with UTF-16.

Solution 3: Fallback to Currency Codes (If All Else Fails) #

If for some reason the above methods don't work, you can replace symbols with standard currency codes (e.g., INR 40 instead of ₹40, EUR 100 instead of €100). Users can then format the Excel cells to display the corresponding symbol via Excel's built-in currency formatting options. It's a compromise, but it's reliable across all systems.

内容的提问来源于stack exchange，提问作者Humayun Naseer