Ruby: Parse Excel 95-2003 Files

Ruby: Parse Excel 95-2003 files?

I'm using spreadsheet, give it a shot.

export excel 2003(xls) file in ruby with date column(cell formatting = date)

I had this same problem. Axlsx_rails helped me. It has various examples.

To format a cell to a Date cell, you have to do the next,

wb = xlsx_package.workbook
wb.styles do |s|
  date = s.add_style(:format_code => "dd/mm/yyyy")
end

And then you pass the style you want to the cell, i.e:

wb = xlsx_package.workbook
wb.styles do |s|
  date = s.add_style(:format_code => "dd/mm/yyyy")
  wb.add_worksheet(name: "Example") do |sheet|
     sheet.add_row['Date Column']
     sheet.add_row[Date.today], :style => [date]
  end
end

As for the format, you could change the name on save, like so:

    respond_to do |format|
      format.html
      format.xlsx do
        response.headers['Content-Disposition'] = 'attachment; filename="excel_sheet.xls"'
      end
    end

Reading and writing Excel files using Ruby on a server without Excel installed

I agree with Gonzih, and I use roo fairly regularly. It allows me to read, write, and write using a template file.
The project is fairly well documented on their site.

I always use something like:

input = Excel.new(path)
output = Array.new
input.default_sheet = input.sheets[sheet]
start.upto(input.last_row) do |row|
  output << input.row(row)
end

p output
=> a nested array representing the spreadsheat.

p output[0]
=> [row1_column_a, row1_column_b...]

to read a spreadsheet. note that the roo gem requires you to use Excelx.new instead of Excel.new if your file is a .xlsx.

to write you can:

book = Spreadsheet::Workbook.new
write_sheet = book.create_worksheet
row_num = 0
input.each do |row|
  write_sheet.row(row_num).replace row
  row_num +=1
end
book.write "/path/to/save/to.xls"

where input is an array structured just like output was

where can I find a good ruby excel parser that also had good documentation?

Roo is a good alternative with documentation.

How can I parse an excel file within a zip file?

According to the documentation of Archive::Zip, it's possible to get the contents of a compressed file member as a string:

$xls_content = $zipFile->contents($file);

And according to the documentation of Spreadsheet::ParseExcel, it's possible to parse a string containg the contents of an Excel file by passing the string as a reference:

my $workbook = $parser->parse(\$xls_content);

So you should be able to combine both together.

Another possibility is to extract the zip file member into a temporary file.

What's the best way to extract Excel cell comments using Perl or Ruby?

One option is to use Ruby's win32ole library.

The following (somewhat verbose) example connects to an open Excel worksheet and gets the comment text from cell B2.

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet
cell = ws.Range('B2')
comment = cell.Comment
text = comment.Text

More info and examples of using Ruby's win32ole library to automate Excel can be found here:

http://rubyonwindows.blogspot.com/search/label/excel

Perl: Parse Excel with Search and Replace

There are a couple of problems with your code:

1. You're not writing to the output file, $worksheet_W

2. You're not writing out the contents of the cells that don't have Apples in them.

   for my $row ( $row_min .. $row_max ) {
     for my $col ( $col_min .. $col_max ) {
   
       my $cell = $worksheet_R->get_cell( $row, $col );
       // if the cell contains Apples, write 'Peaches' instead
       if($cell->value() =~ /Apples/) {
         $worksheet_W->write($row, $col,"Peaches");
       }
       else {
         // print the existing cell contents
         $worksheet_W->write($row, $col, $cell);
       }
     }
   }
   

Is it possible to read a local Excel 97-2003 file (.xls) in a Flash application?

One avenue you can explore is to wrap the Flex classes into a swc that you can use in Flash. The basic idea can be found here: http://flashauthoring.blogspot.com/2009/02/using-class-stringutil-in-flash.html

I have not tried it yet myself but it seems possible.

---

Related Topics