Message-ID: <1108316016.10229.1711718352490.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10228_1805500818.1711718352490" ------=_Part_10228_1805500818.1711718352490 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
=
A1-style references are easy to read, intuitive, and match the default r= eference style seen in Microsoft Excel.
ExcelApplication xla =3D new ExcelApplication(); Workbook wb =3D xla.Create(); Worksheet ws =3D wb.Worksheets[0]; ws.Cells["A3"].Value =3D "Jan"; ws.Cells["B3"].Value =3D "Feb"; ws.Cells["C3"].Value =3D "Mar"; Area a =3D ws.CreateArea("=3DA1:G10");=20
A1 cell references are relative by default. Therefore, if you enter &quo= t;=3DB1" in cell A1 and drag A1 down, the formula in cell Ax will be &= quot;=3DBx". To make an A1 row or column reference absolute, prepend a= dollar sign to the row or column. For example, if you enter "=3DB$1&q= uot; in cell A1 and drag A1 down, the formula in cell Ax will be "=3DB= $1".
The reference style used in the generated workbook (when it is opened in= Excel) is determined by the value of the property Workbook.UseRCFormulaNot= ation. If it is false (the default value), Excel will use A1 references. If= UseRCFormulaNotation is set to true, Excel will use R1C1-style references.
In the R1C1 style, the location of a cell is specified with an "R&q= uot; followed by a row number and a "C" followed by a column numb= er. R1C1 references can be either absolute or relative. To create an absolu= te reference, specify the row and column numbers without brackets. For exam= ple, "=3DR1C2" equates to row 1, column 2. To create a relative r= eference for a cell, specify the row and column numbers in brackets; and en= ter the difference between that cell's row and column numbers and those of = the cell it references. Use negative numbers to reference cells above or to= the left of a cell. Use positive numbers to reference cells below or to th= e right of a cell. For example, entering "=3DR[-2]C[-1]" in cell = R3C2 causes it to refence the value of cell R1C1.
Relative R1C1 references enable you to reuse a single formula in multipl= e rows or columns. The following example copies one formula to three differ= ent columns to calculate the sum of those columns.
ExcelApplication xla =3D new ExcelApplication(); Workbook wb =3D xla.Create(); wb.UseRCFormulaNotation =3D true; Worksheet ws =3D wb.Worksheets[0]; String formulaString =3D "=3DSUM(R[-12]C:R[-1]C)"; Cell columnTotal; for (int i =3D 0;i < 3; i++) { columnTotal =3D ws.Cells[13, i + 1]; columnTotal.Formula =3D formulaString; }=20
The reference style used in the generated workbook (when it is opened in=
Excel) is determined by the value of the property Workbook.UseRCFormulaNot=
ation. If it is false (the default value), Excel will use A1 references. If=
UseRCFormulaNotation is set to true, Excel will use R1C1-style references.=
Note: Workbooks created with ExcelApplication default to =
False
.
There are several valid ways of addressing a cell or an area using RC no= tation:
Single Cell, Absolute |
RxCy |
---|---|
Single Cell, Row Relative |
R[x]Cy |
Single Cell, Column Relative |
RxC[y] |
Single Cell, Both Relative |
R[x]C[y] |
Single Cell, Same Relative Row, Absolute Colu= mn |
RCy |
Single Cell, Same Relative Row, Relative Colu= mn |
RC[y] |
Single Cell, Absolute Row, Same Relative Colu= mn |
RxC |
Single Cell, Relative Row, Same Relative Colu= mn |
R[x]C |
Entire Row, Absolute |
Rx |
Entire Row, Relative |
R[x] |
Entire Row, Same, Relative |
R |
Entire Column, Absolute |
Cy |
Entire Column, Relative |
C[y] |
Entire Column, Same, Relative |
C |
Addressing cells by number allows you to iterate over cells in your Exce= lWriter code. Row and Column number references are also processed faster th= an Excel-style references.
The following specifies cell A1 by 0-based row and column numbers: The f= irst parameter of the Cell property is the row index, the second is the col= umn index.
sheet1.Cells[2, 0].Value =3D "SoftArtisans OfficeWriter&quo= t;;=20
Using row and column numbers makes it easy to iterate over cells:for(int= iRow =3D 3; iRow <=3D 23; iRow++)
for(int iCol =3D 0; iCol <=3D 2; iCol++) sheet1.Cells[iRow, iCol].Value =3D iRow + iCol;=20