GSpread.NET - Google Spreadsheets .NET APIGSpread.NET

Tutorial

Our internal project grew up into a GSpreadCOM library that allows accessing (read/write) Google Spreadsheets as normal Microsoft Excel documents.

Example 1 (VBScript)

This example demonstrates the basics:

Set objExcel = CreateObject("GSpreadCOM.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"

objExcel.ActiveWorkbook.Save "test.xls"
objExcel.ActiveWorkbook.Close

To make this script work with Google Spreadsheet, you need to change Excel.Application to GSpreadCOM.Application. The name test.xls will be title of the new spreadsheet document.

All the rest code remains the same.

Example 2 (VBScript)

This is a bit more complicated example: cells access

Set objExcel = CreateObject("GSoreadCOM.Application")
Set objWorkbook = objExcel.Workbooks.Open("test.xls")

intRow = 1

Do Until objExcel.Cells(intRow,1).Value = ""
	WScript.Echo objExcel.Cells(intRow, 1).Value
	intRow = intRow + 1
Loop

Again, by using GSpreadCOM.Application instead of Excel.Application and spreadsheet title instead of test.xls, you open the existing spreadsheet and read content from it.

Example 3 (C#)

A nice side-effect of our library is that you can easily use it even in a new code. For example, you can use .NET interop to work with Excel.Application interface. The only change you need is:

Type excelAppType = Type.GetTypeFromProgID("GSpreadCOM.Application");
var app = Activator.CreateInstance(excelAppType) as Microsoft.Office.Interop.Excel.Application;

All the rest code remains the same.

Example 4

Let's create a simple table with formulas and styles:

GSpread.NET

Set app = CreateObject("GSpreadCOM.Application")
app.Workbooks.Item("Example2").Worksheets.Item("Sheet1").Activate()

Set cells = app.Cells

' --- Data

cells(2, 2).Cells.Value = "Product"
For i = 1 To 4
    cells(2, 2 + i).Cells.Value = "Quarter " & i
Next
cells(2, 7).Cells.Value = "Year"

cells(4, 2).Cells.Value = "Apples"
cells(5, 2).Cells.Value = "Pears"
cells(6, 2).Cells.Value = "Bananas"
cells(7, 2).Cells.Value = "Oranges"
cells(8, 2).Cells.Value = "Totals"

randomize
For row = 4 To 7
    For col = 3 To 6
   	 cells(row, col).Cells.Value = (int(rnd * 100) + 1) * 100
    Next
Next

cells.Range("C8:G8").FormulaR1C1 = "=SUM(R[-4]C[0]:R[-1]C[0])"
cells.Range("G4:G7").Formula = "=SUM(C4:F4)"

' --- Styling

Set tableRange = cells.Range("B2:G8")
tableRange.Borders.Color = "white"
tableRange.NumberFormat = "$0,000" ' currency number format

cells.Range("B2:B3").Merge()
cells.Range("C2:C3").Merge()
cells.Range("D2:D3").Merge()
cells.Range("E2:E3").Merge()
cells.Range("F2:F3").Merge()
cells.Range("G2:G3").Merge()

Set headerRange = cells.Range("B2:G3")
Set footerRange = cells.Range("B8:G8")

' color #0066CC has index 23 in excel color palette
headerRange.Interior.ColorIndex = 23
footerRange.Interior.Color = "#0066CC"

' white color has index 2 in excel color palette
headerRange.Font.ColorIndex = 2
footerRange.Font.Color = "white"

headerRange.Font.Bold = True
footerRange.Font.Bold = True

footerRange.RowHeight = 30

oddRowColor = 14994616  ' RGB(184, 204, 228)
evenRowColor = 15853019 ' RGB(219, 229, 241)

cells.Range("B4:G4").Interior.Color = oddRowColor
cells.Range("B5:G5").Interior.Color = evenRowColor
cells.Range("B6:G6").Interior.Color = oddRowColor
cells.Range("B7:G7").Interior.Color = evenRowColor

WScript.Echo "Done"

By using GSpreadCOM.Application instead of Excel.Application and spreadsheet title instead of test.xls, you open the existing spreadsheet and read content from it.