From: Author <author@...>
Date: 2010-09-03T14:24:01+09:00
Subject: Failed to write unicode string to excel

--0016e6441efe4dd4f0048f5423c2
Content-Type: text/plain; charset=UTF-8

Hi,
I am trying to do some converting from xml data and write the reports to
Excel. it works very well for English, however, the Unicode data will be
messing in the excel, how should I do it.
the class I am using to write Excel:

require 'win32ole'
require "iconv"
###
#
class ExcelConst
 end

class ExcelHelper

  def initialize()
    @excel_app = WIN32OLE.new("Excel.Application")
    puts "Excel failed to start" unless @excel_app
    @excel_app.Visible = false
    @workbook = @excel_app.Workbooks.Add
    @sheet = @workbook.Worksheets(1)
    puts "Excel failed to get wowksheet" unless @sheet
    @row =1

    #WIN32OLE.const_load(@excel_app, ExcelConst)
  end

  def write_header(header_data)
    @row = 1
    col = 0
    header_data.each{ |cell| begin
          col_name = (?A + col).chr
          range = @sheet.Range("#{col_name}#{@row}")
          range.value = cell
          col = col+1
        end
        }
     @sheet.Rows(1).Font.Bold = true
     @sheet.Rows(1).Font.Size = 14
     #@sheet.Rows(1).Font.ColorIndex = 5 #set to blue
     @sheet.Rows(1).Interior.ColorIndex = 37 #set to light blue
     @sheet.Rows(1).RowHeight = 25

  end

  def set_column_width(width_list)
    col =0
    width_list.each{ |width| begin
          col_name = (?A + col).chr
          @sheet.Columns(col_name).ColumnWidth = width
          col = col+1
        end
        }
  end

  def write_row(data)
    @row =2 if @row <2

    p data
    col = 0
    data.each{ |cell| begin
      col_name = (?A + col).chr
      range = @sheet.Range("#{col_name}#{@row}")
      range.value = cell
      p cell
      col = col+1
    end
    }
    @row = @row+1
  end

  def save(filename)
    #@sheet.Columns.AutoFit()
    excel97_2003_format = -4143
    #pwd =  Dir.pwd.gsub('/','\\') << '\\'
    #otherwise, it sticks it in default save directory-
C:\Users\Sam\Documents on my system
    @workbook.SaveAs(filename, excel97_2003_format)
  end

  def quit()
    if(@excel_app != nil) then
      @excel_app.Quit
      @excel_app = nil
    end
  end
end

thank you

-- 

Best regards,
Liang

--0016e6441efe4dd4f0048f5423c2--