Message-ID: <1517025153.8527.1711648509345.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8526_1715572825.1711648509345" ------=_Part_8526_1715572825.1711648509345 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Basic Steps R1C1

Basic Steps R1C1

Intro

Same as Basic Steps excep= t all the formulas and references are in R1C1 notation.

Here are some of the features shown in the sample:

  1. Setting cell values and formulas
  2. Adding a basic chart
  3. Using the color palette
  4. Applying styles and formatting
  5. Page setup, including headers and footers
  6. Setting "document properties" metadata
  7. Adding external pictures to a worksheet
  8. Defining clickable hyperlinks
  9. Using cell comments
  10. Setting Conditional Formatting
  11. Defining workbook and worksheet protection

Code

=20
class Basic_Steps_R1C1
    {
        ///This class creates a workbook with only one =
Worksheet.
            Declare ExcelApplication, Workboo=
k, Worksheet, and Color
            objects    at the class=
-level. 
        private ExcelApplication xlw;
        private Workbook wb;
        private Worksheet ws;
        private Color clrDarkBlue, clrDarkGreen, clrCom=
ment, clrTitleCell;

        /// 
        /// Build the report with ExcelApplication
        /// 
        public void GenerateReport()
        {

            ///Create an instance of ExcelApp=
lication,
            a new Workbook, and create one Wo=
rksheet.
            Switch the workbook to RC notatio=
n. 
            xlw =3D new ExcelApplication();
            wb =3D xlw.Create(ExcelApplicatio=
n.FileFormat.Xlsx);

            ///Set this property to turn or o=
ff the R1C1 formula
             //parser.  When set to=
 true, all properties and methods
             //that accept or return for=
mula string will use R1C1 notation.
             
            wb.UseRCFormulaNotation =3D true;

            ws =3D wb.Worksheets[0];

            ///Demonstrate setting cell value=
s 
            this.PopulateWorksheet();

            ///Demonstrate setting formulas&n=
bsp;
            this.AddCellFormulas();

            ///Set up colors that will be use=
d in
            //the workbook
            
            this.SetupColors();

            ///Add styles to the worksheet&nb=
sp;
            this.AddStyles();

            ///Set the Page Setup properties&=
nbsp;
            this.DoPageSetup();

            ///Set Document Properties 
            this.AddDocumentProperties();

            ///Add a chart to the worksheet&n=
bsp;
            this.AddChart();

            ///Add some comments to the works=
heet 
            this.AddCellComment();

            ///Add a JPEG picture to the work=
sheet 
            this.AddPicture();

            ///Add a hyperlink 
            this.AddHyperlink();

            ///Use the CharacterRun object to=
 
            //stylize individual characters
            //in a cell.
            
            this.StylizeHeaderCharacters();

            ///Add Conditional Formatting rul=
es to the Worksheet 
            this.AddConditionalFormating();

            ///Turn off gridlines display&nbs=
p;
            ws.ShowGridlines =3D false;

            ///Save the report by streaming i=
t
             //to the client's browser&n=
bsp;
            xlw.Save(wb, @"..\..\ExcelOu=
tputFiles\BasicStepsR1C1_output.xlsx");
        }

        ///  The Cell's getCharacters method retur=
ns a 
        /// CharacterRun object that can be used to app=
ly
        /// formatting to specific characters in the ce=
ll.
        /// Cell A1 is populated with "Welcome to =
SoftArtisans
        /// OfficeWriter" in the populateWorksheet=
() method. 
        /// This stylizeHeaderCharacters() method forma=
ts
        /// the individual words in the cell.
        /// 
        private void StylizeHeaderCharacters()
        {
            ///Get a reference to cell A1&nbs=
p;
            Cell cellHeader =3D ws[0, 0];

            ///Stylize the first word, starti=
ng from the 
            //first character, ending at the =
10th
            
            cellHeader.GetCharacters(0, 10).F=
ont.Size =3D 12;

            ///Format the font for the next t=
wo words in the cell 
            cellHeader.GetCharacters(11, 12).=
Font.Color =3D wb.Palette.GetClosestColor(25, 116, 210);
            cellHeader.GetCharacters(24, 12).=
Font.Color =3D wb.Palette.GetClosestColor(77, 82, 81);
        }

        ///  Add protection to workbooks and works=
heets
        /// with various security-related properties.
        /// 
        private void SetProtection()
        {
            ///The Workbook.setPasswordToModi=
fy property
            //will cause the user to be promp=
ted
            //for a password when the workboo=
k opens
            //in Excel. Users without the pas=
sword
            //will get Read-Only access. =
;
            //
            
            wb.PasswordToModify =3D "Som=
ePassword";

            ///Worksheet.protect turns on Wor=
ksheet Protection.
            //This can be used to "lock&=
quot; cells to prevent them
            //from being edited.
            
            ws.Protect("");

            ///setReadOnlyRecommended advises=
 the user
            //that the workbook should be ope=
ned as Read-Only
            //when it's opened in Excel. &nbs=
p;This is advisory only
            //and can be ignored by the user =
even if they don't have
            //a password.
            
            wb.ReadOnlyRecommended =3D true;
        }

        ///  Create hyperlinks in cells with the
        /// Cell.CreateHyperlink method.
        /// It returns a Hyperlink object
        /// 
        private void AddHyperlink()
        {
            ///Write bold-faced text into a c=
ell to describe the hyperlink 
            ws[30, 0].Value =3D "Click h=
ere to visit SoftArtisans Support:";
            ws[30, 0].Style.Font.Bold =3D tru=
e;

            ///Create the hyperlink 
            Hyperlink hl =3D ws[31, 0].Create=
Hyperlink("http://www.officewriter.com/support");

            ///Merge the cells containing the=
 hyperlink together.
            //This is optional, but improves =
the way it looks.
            
            ws.CreateArea(31, 0, 1, 4).MergeC=
ells();
        }

        /// <summary>Add some conditional formatt=
ing to the data in the worksheet.
        /// Use the wb.CreateConditionalFormat() method=
 to create a conditional formatting object
        /// that can be applied to a Cell, Area, or Ran=
ge.
        /// Use the Cell, Area or Range SetConditionalF=
ormat() method to apply a conditinal format.
        /// </summary>
        private void AddConditionalFormating()
        {
            //Create a conditional formatting=
 object 
            ConditionalFormat cf1 =3D wb.Crea=
teConditionalFormat();

            //Create a range of the value col=
umns 
            Area valuesArea =3D ws.CreateArea=
("R4C1:R24C3");

            //Create a set of conditions, and=
 set the fonts to warmer colors as the values go up 
            Condition condition1 =3D cf1.Crea=
teCondition(Condition.Comparison.CellValueBetween, "=3D0", "=
=3D10");
            condition1.Style.Font.Color =3D C=
olor.SystemColor.Red;

            Condition condition2 =3D cf1.Crea=
teCondition(Condition.Comparison.CellValueBetween, "=3D11", "=
;=3D20");
            condition2.Style.Font.Color =3D w=
b.Palette.GetClosestColor(0, 25, 149);

            Condition condition3 =3D cf1.Crea=
teCondition(Condition.Comparison.CellValueGreaterThan, "=3D20");
            condition3.Style.Font.Color =3D w=
b.Palette.GetClosestColor(36, 122, 51);

            //Apply the conditional formattin=
g object to the range of values 
            valuesArea.SetConditionalFormat(c=
f1);
        }

        ///  Insert external picture files into wo=
rksheets
        /// with the Pictures.CreatePicture method. &nb=
sp;
        /// Specify its location with an Anchor object.=
 
        /// 
        private void AddPicture()
        {

            ///The path to the picture that w=
ill be imported 
            string picturePath =3D @"..\=
..\ExcelImages\OWlogo.gif";

            ///Create an Anchor on which the =
picture will set 
            Anchor anch =3D ws.CreateAnchor(0=
, 7, 0, 0);

            ///Insert the picture into the wo=
rkeheet.
            //Its top left corner will be on =
the Anchor.
            
            Picture pic =3D ws.Pictures.Creat=
ePicture(picturePath, anch);
        }

        ///  Comment items can be added,
        /// edited, or removed from cells.
        /// 
        private void AddCellComment()
        {
            ///Add a comment to cell C3 
            Cell c =3D ws[2, 2];
            Comment cmnt =3D c.Comment;

            ///Set the comment text (body of =
comment note),
            //author (shown in status bar),&n=
bsp;
            //and visibility (false by defaul=
t). 
            
            cmnt.Author =3D "John Doe&qu=
ot;;
            cmnt.Text =3D "Figures for M=
arch are projected.";
            cmnt.Visible =3D true;

            ///The Comment's Shape object
            //can be used to stylize the
            //comment tag itself.
            //Set the fill color and FitToTex=
t
            //properties.
            
            Shape shp =3D cmnt.Shape;

            shp.SetCustomFillColor(0, 204, 25=
5);
            shp.FitToText =3D true;
        }

        ///  The DocumentProperties object is used=
 for setting 
        /// workbook metadata such as Author, Comments,=
 Company,
        /// and also user-defined custom properties.
        /// 
        private void AddDocumentProperties()
        {
            ///Get the DocumentProperties int=
erface from Workbook 
            DocumentProperties docprops =3D w=
b.DocumentProperties;

            ///Set built-in DocumentPropertie=
s values 
            docprops.Author =3D "John Do=
e";
            docprops.Comments =3D "A bas=
ic demonstration of OfficeWriter for Excel";
            docprops.Company =3D "SoftAr=
tisans, Inc.";
            docprops.Title =3D "Basic Re=
port w/ Chart";

            ///Set custom DocumentProperties =
key/value pairs 
            docprops.SetCustomProperty("=
GeneratedBy", "SoftArtisans OfficeWriter for Excel");
        }

        ///  Adds a simple clustered column chart =
to a worksheet
        /// Category data is what appears on the X axis=
 of the chart
        /// Series items (there can be more than one) a=
re the data that
        /// the chart is displaying in the plot area.&n=
bsp;
        /// 
        private void AddChart()
        {
            ///Create an Anchor on which the =
Chart will be placed 
            Anchor anch =3D ws.CreateAnchor(7=
, 4, 0, 50);

            ///Create a Column.Clustered char=
t on the Worksheet
            Chart columnChart =3D ws.Charts.C=
reateChart(ChartType.Column.Clustered, anch);

            ///Use the chart's SeriesCollecti=
on to add Series items and set dategory data 
            SeriesCollection seriesCol =3D co=
lumnChart.SeriesCollection;

            ///Set the month header row as th=
e Primary axis's category data 
            seriesCol.CategoryData =3D "=
Sheet1!R3C1:R3C3";

            ///Add the totals row as a series=
 and name it "Sales" 
            Series chartSeries =3D seriesCol.=
CreateSeries("Sheet1!R25C1:R25C3");
            chartSeries.Name =3D "Sales&=
quot;;

            ///Change the chart's title 
            columnChart.Title.Text =3D "=
Basic Column Chart";
        }

        ///  Every Worksheet has a PageSetup objec=
t that can be 
        /// used to set print behavior.  Commonly =
used PageSetup
        /// capabilities are header/footers, and printe=
r paper setup. 
        /// 
        private void DoPageSetup()
        {
            ///Get the PageSetup object from =
the Worksheet 
            PageSetup ps =3D ws.PageSetup;

            ///Set the worksheet headers and =
footers.
            Note use of special header/footer=
 variables. 
            // Updated to new syntax
            HeaderFooterSection leftHeader =
=3D ps.GetHeader(HeaderFooterSection.Section.Left);
            leftHeader.SetContent("Docum=
ent name: &F");
            HeaderFooterSection centerHeader =
=3D ps.GetHeader(HeaderFooterSection.Section.Center);
            centerHeader.SetContent("Bas=
ic Report");
            HeaderFooterSection rightHeader =
=3D ps.GetHeader(HeaderFooterSection.Section.Right);
            rightHeader.SetContent("Gene=
rated by OfficeWriter");
            HeaderFooterSection leftFooter =
=3D ps.GetFooter(HeaderFooterSection.Section.Left);
            leftFooter.SetContent("Creat=
ed on date &D");
            HeaderFooterSection centerFooter =
=3D ps.GetFooter(HeaderFooterSection.Section.Center);
            centerFooter.SetContent("Pag=
e &P of &N");
            HeaderFooterSection rightFooter =
=3D ps.GetFooter(HeaderFooterSection.Section.Right);
            rightFooter.SetContent("Crea=
te at time &T");

            ///Turn off the printing of gridl=
ines 
            ps.PrintGridlines =3D false;

            ///Change printing orientation to=
 Landscape,
            and. paper size to Legal 
            ps.Orientation =3D PageSetup.Page=
Orientation.Landscape;
            ps.PaperSize =3D PageSetup.PagePa=
perSize.Legal;
        }

        ///  Apply some basic stylization to the d=
ata in the worksheet.
        /// Use the setStyle method to set a base style=
 for a Cell, Area, or Range.
        /// Use the ApplyStyle method to "merge&qu=
ot; or "layer" two Styles in one region.
        /// 
        private void AddStyles()
        {
            ///Create a GlobalStyle to styliz=
e the data region.
            //Set number formatting, and a su=
rrounding border.
            
            GlobalStyle styleData =3D wb.Crea=
teStyle();
            styleData.NumberFormat =3D "=
$#.##00";

            ///Style for the header row.
            //Add a surrounding border, and s=
et 
            //bold-faced font.
            
            GlobalStyle styleHeader =3D wb.Cr=
eateStyle();
            styleHeader.Font.Bold =3D true;

            ///Style for the title cell. &nbs=
p;Enlarge the font
            //and change the color.
            
            GlobalStyle styleTitle =3D wb.Cre=
ateStyle();
            styleTitle.Font.Size =3D 14;
            styleTitle.Font.Color =3D clrTitl=
eCell;

            ///The total row will have number=
 formatting,
            //bold, and italicized text.
            
            GlobalStyle styleTotalRow =3D wb.=
CreateStyle();
            styleTotalRow.NumberFormat =3D &q=
uot;$#.##00";
            styleTotalRow.Font.Italic =3D tru=
e;
            styleTotalRow.Font.Bold =3D true;

            ///Create an Area that encompases=
 the data 
            and apply money formatting to the=
 cells 
            Area areaData =3D ws.CreateArea(3=
, 0, 21, 3);
            areaData.SetStyle(styleData);

            ///Create an area that encompasse=
s the totals row
            Use the setStyle method to set th=
e base style
            Use the ApplyStyle to layer other=
 styles on 
            the base style. 
            Area areaTotalRow =3D ws.CreateAr=
ea(24, 0, 1, 3);
            areaTotalRow.SetStyle(styleTotalR=
ow);

            ///Stylize the title cell 
            ws[0, 0].Style =3D styleTitle;

            ///Make the month header row bold=
 
            ws.CreateArea(2, 0, 1, 3).SetStyl=
e(styleHeader);
        }

        ///  Add some data to the workbook by usin=
g the Cell.setValue() method.
        /// Cells can be addressed by Excel address (eg=
, A1, B2), or by row/column
        /// ordinal for iteration.
        /// Cell.setValue() is just one way to add valu=
es to cells.
        /// 
        private void PopulateWorksheet()
        {
            ///Cells can be referenced by Exc=
el name (eg, A1, B2)
            or by 0-based row number/column n=
umber 

            ///Refers to cell A1
            Could also write as ws["A1&q=
uot;).setValue(...) 
            ws.Cells[0, 0].Value =3D "We=
lcome to SoftArtisans OfficeWriter";

            ws[2, 0].Value =3D "Jan"=
;;
            ws[2, 1].Value =3D "Feb"=
;;
            ws[2, 2].Value =3D "Mar"=
;;

            ///Use row/column indices to easi=
ly iterate over cells 
            for (int iRow =3D 3; iRow <=3D=
 23; iRow++)
                for (int iCol =3D 0=
; iCol <=3D 2; iCol++)
                    ws[iR=
ow, iCol].Value =3D iRow + iCol;
        }

        ///  The Cell.setFormula() method is for a=
dding Excel formulas
        /// to cells.  The result of the formulas =
will be calculated when
        /// the workbook is opened in Excel.
        /// 
        private void AddCellFormulas()
        {
            ///Apply forumlas to cells with t=
he Cell.setFormula() method using 
            a relative formula created with R=
C notation. 
            string formula =3D "=3DSUM(R=
4C:R[-1]C)";

            ///Display the formula in cell A2=
5 
            ws[24, 0].Formula =3D formula;

            ///Display that formula in cell B=
25 
            ws[24, 1].Formula =3D formula;

            ///Create a Range to sum using RC=
 notation. 
            Range rngMarch =3D ws.CreateNamed=
Range("R4C3:R24C3", "MarchRange");

            ///Display a formula that sums th=
e range in cell C25. 
            ws[24, 2].Formula =3D "=3DSU=
M(MarchRange)";
        }

        ///  Colors are managed by the Palette obj=
ect.
        /// Palette has several methods for retrieving =
colors
        /// from the workbook's internal palette.
        /// Also, there are build-in colors.
        /// 
        private void SetupColors()
        {
            ///Note: clrDarkBlue, clrDarkGree=
n, and clrComment
            //are declared above as private c=
lass members.
            

            ///Workbook.getPalette returns th=
e Palette object 
            Palette pal =3D wb.Palette;

            ///GetClosestColor finds the colo=
r in the palette
            //that most closely matches the s=
pecified Red,
            //Green, and Blue values.
            
            clrDarkBlue =3D pal.GetClosestCol=
or(0, 35, 149);
            clrTitleCell =3D pal.GetClosestCo=
lor(0, 35, 149);

            try
            {
                ///getColor will fi=
nd the exact color you request.
                //If the color does=
 not exist in the palette
                //it will throw an =
Exception.
                
                clrDarkGreen =3D pa=
l.GetColor(51, 153, 102);
            }
            catch
            {
                // The color you re=
quested was not found in the palette
            }

            ///getColorAt gets the color from=
 the palette at
            //the specified index.  This=
 retrieves the second
            //color from the palette.
            
            clrComment =3D pal.GetColorAt(1);

            ///setColor and setColorAt are fo=
r changing
            //the colors in the palette. Spec=
ify a Color to
            //change for setColor, the index =
of the color for
            //setColorAt, and then the RGB va=
lues for the desired
            //appearance.
            
            
            
            pal.SetColorAt(0, 0, 255, 0);

            ///The Color object has built-in =
colors 
            Color clrSysGreen =3D Color.Syste=
mColor.Green;
            Color clrSysBlack =3D Color.Syste=
mColor.Black; ;
        }

        ///  This method is here for your convenie=
nce.  You might find it useful
        /// in your project.  It simplifies buildi=
ng formulas in this basic form only:
        /// =3D[formulaName]([lBound]:[uBound])
        /// For example, =3DSUM(A1:C3) or =3DAVERAGE(D2=
:C20)
        /// 
        private string BuildFormula(string formulaName,=
 Cell lBound, Cell uBound)
        {
            System.Text.StringBuilder sb =3D =
new System.Text.StringBuilder();
            sb.Append("=3D");
            sb.Append(formulaName);
            sb.Append("(");
            sb.Append(lBound.Name);
            sb.Append(":");
            sb.Append(uBound.Name);
            sb.Append(")");
            return sb.ToString();
        }
    }
=20

Downloads

Output: Bas= icStepsR1C1_output.xlsx

Image: OWlogo.gif

------=_Part_8526_1715572825.1711648509345--