Tuesday, July 8, 2014

Golang, working with Microsoft Excel

You can actually do alot using "github.com/mattn/go-ole".
Here we use it "mattn/go-ole" for Excel.

There are 5 go source codes here, you only need to understand 1 file: the "range_test.go".

Excel wrappers:
- xlx.go
- workbooks.go
- workbook.go
- range.go

Test file:
- range_test.go

- xlx.go

 package xlsx  
 import (  
      "github.com/mattn/go-ole"  
      "github.com/mattn/go-ole/oleutil"  
 )  
 type Xlsx ole.IDispatch  
 type Workbook ole.IDispatch  
 func CreateObject() (*Xlsx, error) {  
      unknown, err := oleutil.CreateObject("Excel.Application")  
      if nil != err {  
           return nil, err  
      }  
      excel, err := unknown.QueryInterface(ole.IID_IDispatch)  
      if nil != err {  
           return nil, err  
      }  
      return (*Xlsx)(excel), nil  
 }  
 func (this *Xlsx) Release() {  
      (*ole.IDispatch)(this).Release()  
 }  
 func (this *Xlsx) Workbooks() *Workbooks {  
      if v := oleutil.MustGetProperty((*ole.IDispatch)(this), "Workbooks"); nil != v {  
           return (*Workbooks)(v.ToIDispatch())  
      }  
      return nil  
 }  

- workbooks.go

 package xlsx  
 import (  
      "github.com/mattn/go-ole"  
      "github.com/mattn/go-ole/oleutil"  
 )  
 type Workbooks ole.IDispatch  
 func (this *Workbooks) Open(filepath string) *Workbook {  
      if v := oleutil.MustCallMethod((*ole.IDispatch)(this), "Open", filepath); nil != v {  
           return (*Workbook)(v.ToIDispatch())  
      }  
      return nil  
 }  

- workbook.go

 package xlsx  
 import (  
      "github.com/mattn/go-ole"  
      "github.com/mattn/go-ole/oleutil"  
 )  
 func (this *Workbook) Close() {  
      oleutil.MustCallMethod((*ole.IDispatch)(this), "Close")  
 }  
 func (this *Workbook) Worksheets(i int) *Worksheet {  
      if v := oleutil.MustGetProperty((*ole.IDispatch)(this), "Worksheets", i); nil != v {  
           return (*Worksheet)(v.ToIDispatch())  
      }  
      return nil  
 }  

- range.go

 package xlsx  
 import (  
      "github.com/mattn/go-ole"  
      "github.com/mattn/go-ole/oleutil"  
 )  
 type Range ole.IDispatch  
 func (this *Range) ToString() string {  
      if val := oleutil.MustGetProperty((*ole.IDispatch)(this), "Value"); nil != val {  
           return val.ToString()  
      }  
      return ""  
 }  

- range_test.go

this will test the value at "A1"
 package xlsx  
 import (  
      "fmt"  
      "testing"  
      "github.com/mattn/go-ole"  
 )  
 func TestRangeToString(t *testing.T) {  
      ole.CoInitialize(0)  
      defer ole.CoUninitialize()  
      var err error  
      defer func() {  
           if nil != err {  
                fmt.Println("err=", err)  
           }  
      }()  
      excel, err := CreateObject()  
      if nil != err {  
           return  
      }  
      defer excel.Release()  
      filepath := "D:\\dev\\gopath\\src\\xlsx\\dummy.xlsx"  
      workbooks := excel.Workbooks()  
      workbook := workbooks.Open(filepath)  
      if nil == workbook {  
           t.Fatal("workbook is nil")  
           return  
      }  
      defer workbook.Close()  
      //  
      sheet1 := workbook.Worksheets(1)  
      r := sheet1.Range("a1")  
      a1Val := r.ToString()  
      if "a1" != a1Val {  
           t.Fatal("a1Val=", a1Val)  
      }  
 }