SoftArtisans OfficeWriter

Cells can be addressed in two ways: by Name or by Number. Accessing by name is easier to read, more intuitive and matches the cell references seen in Microsoft Excel. Accessing by number is significantly faster, and is easier to program if it is necessary to walk a number of cells.

Example 1: Addressing Cells by Name and Number

When referencing a cell by number, the format is cells(row, column). It is easy to mix and match different means of referencing a cell within your application.

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim oXLW
Dim oCells

'--- Create an instance of ExcelApplication and get a reference
'--- to the first worksheet's cells.
Set oXLW = Server.CreateObject("SoftArtisans.ExcelWriter")
Set oCells = oXLW.Worksheets(1).Cells

'--- Reference a cell by its Excel address.
oCells("B3").value = 123

'--- Reference a cell using row/column addressing.
oCells(7,1).value = 123

oXLW.Save "address1.xls", saOpenInPlace

Set oXLW = Nothing
%>

Example 2: Iterating over Cells

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim xlw, cells
Dim row, column, letterA, cellname

set xlw = Server.CreateObject("SoftArtisans.ExcelWriter")
set cells = xlw.worksheets(1).cells

'--- Address cells by number
'---
for row = 1 to 5
  for column = 1 to 5
    cells(row, column).value = CStr(row) & ", " & CStr(column)
  next ' column
next ' row

for row = 1 to 5
  for column = 27 to 31
    cells(row, column).value = CStr(row) & ", " & CStr(column)
  next ' column
next ' row

'--- Address cells by name.
'---
'--- This works only up to column Z. It will not work for column AA.
'---
letterA = Asc("A") ' ASCII numerical value of the letter 'A'
for row = 7 to 12
  for column = 1 to 5
    cellname = Chr(column + letterA - 1) & CStr(row)
    cells(CStr(cellname)).value = cellname
  next ' column
next ' row

'--- This is more complicated but works for any column
'---
for row = 7 to 12
  for column = 27 to 31 
    cellname = ""
    if (column mod 26) <> 0 then
      if (column \ 26) > 0 then cellname = Chr((column \ 26) + letterA - 1)
      cellname = cellname & Chr((column Mod 26) + letterA - 1) & CStr(row)
    else
      cellname = chr(column + letterA - 1) & cStr(row)
    end if  
    cells(CStr(cellname)).value = cellname
  next ' column
next ' row

for row = 14 to 18
  for column = 1 to 5
    cellname = ""
    if (column mod 26) <> 0 then
      if (column \ 26) > 0 then cellname = Chr((column \ 26) + letterA - 1)
      cellname = cellname & Chr((column Mod 26) + letterA - 1) & CStr(row)
    else
      cellname = chr(column + letterA - 1) & cStr(row)
    end if
    cells(CStr(cellname)).value = cellname
  next ' column
next ' row

'--- For a specific cell, addressing by name is easier
'---
cells("G1").Value = "Please scroll to cell AA1->>>"
cells("G1").Format.Font.Bold = true

xlw.Save "address2.xls", saOpenInPlace

Set xlw = nothing
Response.end
%>

Addressing Cells

How to Address Cells

Example 1: Addressing Cells by Name and Number

Example 2: Iterating over Cells

Section Summary