Any Standard Guide for Ruby Win32Ole API

Any standard guide for Ruby WIN32OLE API?

I fear you may not find one. David Mullet is working on a book but that rather implies that it's still some considerable time away from publication.

I need a complete, systematic standard
tutorial for WIN32OLE ruby APIs.

How "complete", "systematic" and "standard" do you really need it to be? You have to consider that someone needs to have the motivation to produce such a tutorial, which would be a substantial piece of work to undertake for no reward; that's why a book is in preparation - then we can pay David for his expertise!

So we may need to modify your need to be more satisfiable by current reality: are there particular problem(s) you are trying to solve? Could you put some more specific questions here?

VSS automation with ruby win32ole

Check out http://msdn.microsoft.com/en-US/library/microsoft.visualstudio.sourcesafe.interop(v=VS.80).aspx

When experimenting with Win32OLE I tend to open up an irb session and just experiment seeing what objects I can get hold of.

You need to read the API documentation though as it's hard to work out what types to pass and how many parameters methods have, otherwise.

What's the easiest way to send a message through Outlook with Ruby?

Assuming that the Outlook credentials are stored and you are set to autologin to Outlook, WIN32OLE does the trick quite nicely:

require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')
message = outlook.CreateItem(0)
message.Subject = "Hey look a subject!"
message.Body = "Yes this is dog"
message.Recipients.Add 'dog@dog.com'
message.Recipients.Add 'cat@dog.com'
message.Attachments.Add('C:\Path\To\File.txt')
#Want to save as a draft?
message.Save
#Want to send instead?
message.Send

This is in fact quite well documented in "Automating Outlook with Ruby: Saving Mail Messages To Files", as is automating the rest of windows with Ruby.

You may have an authorization issue, which, if it appears, can be solved using "Advanced Security for Outlook".

Can Ruby import a .NET dll?

While IronRuby will make short work of talking to your .NET dll (it'll be literally no code at all), it was abandoned by microsoft, and it never got a large enough open source community to keep it going after that event. I wouldn't recommend it these days

Regarding the COM solution, this may actually be a good way to go.

You don't need the RubyCOM library - that lets other COM objects call into ruby code. To load COM objects from ruby, you just need the win32ole library, which comes as part of the standard library on windows ruby.

Whether or not you can load the dll from COM will depend if the .NET dll was built to be 'Com Visible'.
The .NET framework defines a ComVisibleAttribute, which can be applied to either an entire assembly, or specific classes within an assembly. If it is set to true for either the whole assembly, or any classes, then the dll will already be callable from COM without any wrapper code.

Here's a test I did.

Create a new .NET dll project (class library). Here's an example class I used:

using System;
using System.IO;

namespace ComLib
{
    public class LogWriter
    {
        public void WriteLine( string line )
        {
            using( var log = new StreamWriter( File.OpenWrite( @"c:\log.file" ) ) )
            {
                log.WriteLine( line );
            }
        }
    }
}

Now, under the visual studio project, there is a directory called Properties which contains AssemblyInfo.cs. In this file, there will be the following

[assembly: ComVisible( false )]

Change the false to true. If you don't want every class in the assembly exposed to COM, then you can leave it set to false in AssemblyInfo.cs and instead put it above each class you want to expose, like this:

[ComVisible( true )]
public class LogWriter ....

Now right click on the dll project itself, and from the popup menu, select 'properties'. In the list of sections, choose Build

Scroll down, and tick the 'Register for COM interop' checkbox. Now when you compile this DLL, visual studio will do the neccessary stuff to load the COM information into the registry. Note if you're on vista you need to run VS as an administrator for this to work.

Now that this is done, recompile your dll, and then create a new ruby file.

In this ruby file, do this:

require 'win32ole'

lib = WIN32OLE.new('[Solution name].ComLib.LogWriter')
lib.WriteLine('calling .net from ruby via COM, hooray!')

Where [Solution name] is to be replaced by the name of the solution you just created (default: "ClassLibrary1")

Ruby that ruby file, and presto! you should see that the text gets written to c:\log.file.

One problem with this solution is that it requires that the .NET dll is already Com Visible, or if it's not, you have the ability to recompile it. If neither of these things are true, then you may have to look at other options.

Good luck!

Creating Microsoft Word (.docx) documents in Ruby

As has been noted, there don't appear to be any libraries to manipulate Open XML documents in Ruby, but OpenXML Developer has complete documentation on the format of Open XML documents.

If what you want is to send a copy of a standard document (like a form letter) customized for each user, it should be fairly simple given that a DOCX is a ZIP file that contains various parts in a directory hierarchy. Have a DOCX "template" that contains all the parts and tree structure that you want to send to all users (with no real content), then simply create new (or modify existing) pieces that contain the user-specific content you want and inject it into the ZIP (DOCX file) before sending it to the user.

For example: You could have document-template.xml that contains Dear [USER-PLACEHOLDER]:. When a user requests the document, you replace [USER-PLACEHOLDER] with the user's name, then add the resulting document.xml to the your-template.docx ZIP file (which would contain all the images and other parts you want in the Word document) and send that resulting document to the user.

Note that if you rename a .docx file to .zip it is trivial to explore the structure and format of the parts inside. You can remove or replace images or other parts very easily with any ZIP manipulation tools or programmatically with code.

Generating a brand new Word document with completely custom content from raw XML would be very difficult without access to an API to make the job easier. If you really need to do that, you might consider installing Mono, then use VB.NET, C# or IronRuby to create your Open XML documents using the Open XML Format SDK 1.0. Since you would just be using the Microsoft.Office.DocumentFormat.OpenXml.Packaging Namespace to manipulate Open XML documents, it should work okay in Mono, which seems to support everything the SDK requires.

How to convert CSV to Excel?

According to this post, the spreadsheet gem is a possibility. It looks like this is a very popular gem. Check it out. Example:

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet

header_format = Spreadsheet::Format.new(
  :weight => :bold,
  :horizontal_align => :center,
  :bottom => true,
  :locked => true
)

sheet1.row(0).default_format = header_format

FasterCSV.open(input_path, 'r') do |csv|
  csv.each_with_index do |row, i|
    sheet1.row(i).replace(row)
  end
end

book.write(output_path)

According to this post, write_xlsx is a possibility.

I've used the Apache POI library with JRuby to export xls files. Here's a quick example.

require 'java'
require 'poi.jar'
# require 'poi-ooxml.jar'
require 'rubygems'
require 'fastercsv'

java_import org.apache.poi.hssf.usermodel.HSSFWorkbook;

wb = HSSFWorkbook.new # OR XSSFWorkbook, for xlsx
sheet = wb.create_sheet('Sheet 1')

FasterCSV.open(ARGV.first) do |csv|
  csv.each_with_index do |csv_row, line_no|
    row = sheet.createRow(line_no)
    csv_row.each_with_index do |csv_value, col_no|
      cell = row.createCell(col_no)
      cell.setCellValue(csv_value) unless csv_value.nil? # can't pass nil.
    end
  end
end

f = java.io.FileOutputStream.new("workbook.xls")
wb.write(f)
f.close

Some useful methods for formatting POI spreadsheets are

• sheet.createFreezePane(0,1,0,1)
• wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 1)
• sheet.setColumnWidth(i, 100 *256)
• sheet.autoSizeColumn(i), but beware, if you're running in headless mode, you have to call java.lang.System.setProperty("java.awt.headless", "true")

You can also use Win32ole on Windows, if you have Excel installed

require 'win32ole'
require 'rubygems'
require 'fastercsv'

xl = WIN32OLE.new('Excel.Application')
xl.Visible = 0
wb = xl.Workbooks.Add
ws = wb.Worksheets(1)

FasterCSV.open(ARGV.first) do |csv|
  csv.each_with_index do |csv_row, line_no|
    csv_row.each_with_index do |value, col|
      ws.Cells(line_no + 1, col + 1).Value = value
    end
  end
end

wb.SaveAs("workbook.xls", 56) # 56 = xlExcel8 aka Excel 97-2003. i.e. xls
wb.SaveAs("workbook.xlsx", 51) # 51 = xlOpenXMLWorkbook
wb.SaveAs("workbook.xlsb", 50) # 50 = xlExcel12

wb.Close(2) #xlDoNotSaveChanges
xl.Quit

Some useful methods for formatting with Excel are

• xl.Rows(1).Font.Bold = true
• ws.Cells.EntireColumn.AutoFit

Yet another option is to write directly to Microsoft's XML Spreadsheet format, as Ryan Bates at Railscasts.com does at the end of his Exporting CSV and Excel episode.

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
    <% @products.each do |product| %>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

This gem looks promising, too.

How do I properly clean up Excel interop objects?

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

---

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

---

Related Topics