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

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

            ///Create an instance of ExcelApplication,
            a new Workbook, and create one Worksheet.
            Switch the workbook to RC notation. 
            xlw = new ExcelApplication();
            wb = xlw.Create(ExcelApplication.FileFormat.Xlsx);

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

            ws = wb.Worksheets[0];

            ///Demonstrate setting cell values 
            this.PopulateWorksheet();

            ///Demonstrate setting formulas 
            this.AddCellFormulas();

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

            ///Add styles to the worksheet 
            this.AddStyles();

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

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

            ///Add a chart to the worksheet 
            this.AddChart();

            ///Add some comments to the worksheet 
            this.AddCellComment();

            ///Add a JPEG picture to the worksheet 
            this.AddPicture();

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

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

            ///Add Conditional Formatting rules to the Worksheet 
            this.AddConditionalFormating();

            ///Turn off gridlines display 
            ws.ShowGridlines = false;

            ///Save the report by streaming it
             //to the client's browser 
            xlw.Save(wb, @"..\..\ExcelOutputFiles\BasicStepsR1C1_output.xlsx");
        }

        ///  The Cell's getCharacters method returns a 
        /// CharacterRun object that can be used to apply
        /// formatting to specific characters in the cell.
        /// Cell A1 is populated with "Welcome to SoftArtisans
        /// OfficeWriter" in the populateWorksheet() method. 
        /// This stylizeHeaderCharacters() method formats
        /// the individual words in the cell.
        /// 
        private void StylizeHeaderCharacters()
        {
            ///Get a reference to cell A1 
            Cell cellHeader = ws[0, 0];

            ///Stylize the first word, starting from the 
            //first character, ending at the 10th
            
            cellHeader.GetCharacters(0, 10).Font.Size = 12;

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

        ///  Add protection to workbooks and worksheets
        /// with various security-related properties.
        /// 
        private void SetProtection()
        {
            ///The Workbook.setPasswordToModify property
            //will cause the user to be prompted
            //for a password when the workbook opens
            //in Excel. Users without the password
            //will get Read-Only access. 
            //
            
            wb.PasswordToModify = "SomePassword";

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

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

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

            ///Create the hyperlink 
            Hyperlink hl = ws[31, 0].CreateHyperlink("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).MergeCells();
        }

        /// <summary>Add some conditional formatting 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 Range.
        /// Use the Cell, Area or Range SetConditionalFormat() method to apply a conditinal format.
        /// </summary>
        private void AddConditionalFormating()
        {
            //Create a conditional formatting object 
            ConditionalFormat cf1 = wb.CreateConditionalFormat();

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

            //Create a set of conditions, and set the fonts to warmer colors as the values go up 
            Condition condition1 = cf1.CreateCondition(Condition.Comparison.CellValueBetween, "=0", "=10");
            condition1.Style.Font.Color = Color.SystemColor.Red;

            Condition condition2 = cf1.CreateCondition(Condition.Comparison.CellValueBetween, "=11", "=20");
            condition2.Style.Font.Color = wb.Palette.GetClosestColor(0, 25, 149);

            Condition condition3 = cf1.CreateCondition(Condition.Comparison.CellValueGreaterThan, "=20");
            condition3.Style.Font.Color = wb.Palette.GetClosestColor(36, 122, 51);

            //Apply the conditional formatting object to the range of values 
            valuesArea.SetConditionalFormat(cf1);
        }

        ///  Insert external picture files into worksheets
        /// with the Pictures.CreatePicture method.  
        /// Specify its location with an Anchor object. 
        /// 
        private void AddPicture()
        {

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

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

            ///Insert the picture into the workeheet.
            //Its top left corner will be on the Anchor.
            
            Picture pic = ws.Pictures.CreatePicture(picturePath, anch);
        }

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

            ///Set the comment text (body of comment note),
            //author (shown in status bar), 
            //and visibility (false by default). 
            
            cmnt.Author = "John Doe";
            cmnt.Text = "Figures for March are projected.";
            cmnt.Visible = true;

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

            shp.SetCustomFillColor(0, 204, 255);
            shp.FitToText = 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 interface from Workbook 
            DocumentProperties docprops = wb.DocumentProperties;

            ///Set built-in DocumentProperties values 
            docprops.Author = "John Doe";
            docprops.Comments = "A basic demonstration of OfficeWriter for Excel";
            docprops.Company = "SoftArtisans, Inc.";
            docprops.Title = "Basic Report 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) are the data that
        /// the chart is displaying in the plot area. 
        /// 
        private void AddChart()
        {
            ///Create an Anchor on which the Chart will be placed 
            Anchor anch = ws.CreateAnchor(7, 4, 0, 50);

            ///Create a Column.Clustered chart on the Worksheet
            Chart columnChart = ws.Charts.CreateChart(ChartType.Column.Clustered, anch);

            ///Use the chart's SeriesCollection to add Series items and set dategory data 
            SeriesCollection seriesCol = columnChart.SeriesCollection;

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

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

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

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

            ///Set the worksheet headers and footers.
            Note use of special header/footer variables. 
            // Updated to new syntax
            HeaderFooterSection leftHeader = ps.GetHeader(HeaderFooterSection.Section.Left);
            leftHeader.SetContent("Document name: &F");
            HeaderFooterSection centerHeader = ps.GetHeader(HeaderFooterSection.Section.Center);
            centerHeader.SetContent("Basic Report");
            HeaderFooterSection rightHeader = ps.GetHeader(HeaderFooterSection.Section.Right);
            rightHeader.SetContent("Generated by OfficeWriter");
            HeaderFooterSection leftFooter = ps.GetFooter(HeaderFooterSection.Section.Left);
            leftFooter.SetContent("Created on date &D");
            HeaderFooterSection centerFooter = ps.GetFooter(HeaderFooterSection.Section.Center);
            centerFooter.SetContent("Page &P of &N");
            HeaderFooterSection rightFooter = ps.GetFooter(HeaderFooterSection.Section.Right);
            rightFooter.SetContent("Create at time &T");

            ///Turn off the printing of gridlines 
            ps.PrintGridlines = false;

            ///Change printing orientation to Landscape,
            and. paper size to Legal 
            ps.Orientation = PageSetup.PageOrientation.Landscape;
            ps.PaperSize = PageSetup.PagePaperSize.Legal;
        }

        ///  Apply some basic stylization to the data in the worksheet.
        /// Use the setStyle method to set a base style for a Cell, Area, or Range.
        /// Use the ApplyStyle method to "merge" or "layer" two Styles in one region.
        /// 
        private void AddStyles()
        {
            ///Create a GlobalStyle to stylize the data region.
            //Set number formatting, and a surrounding border.
            
            GlobalStyle styleData = wb.CreateStyle();
            styleData.NumberFormat = "$#.##00";

            ///Style for the header row.
            //Add a surrounding border, and set 
            //bold-faced font.
            
            GlobalStyle styleHeader = wb.CreateStyle();
            styleHeader.Font.Bold = true;

            ///Style for the title cell.  Enlarge the font
            //and change the color.
            
            GlobalStyle styleTitle = wb.CreateStyle();
            styleTitle.Font.Size = 14;
            styleTitle.Font.Color = clrTitleCell;

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

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

            ///Create an area that encompasses the totals row
            Use the setStyle method to set the base style
            Use the ApplyStyle to layer other styles on 
            the base style. 
            Area areaTotalRow = ws.CreateArea(24, 0, 1, 3);
            areaTotalRow.SetStyle(styleTotalRow);

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

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

        ///  Add some data to the workbook by using 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 values to cells.
        /// 
        private void PopulateWorksheet()
        {
            ///Cells can be referenced by Excel name (eg, A1, B2)
            or by 0-based row number/column number 

            ///Refers to cell A1
            Could also write as ws["A1").setValue(...) 
            ws.Cells[0, 0].Value = "Welcome to SoftArtisans OfficeWriter";

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

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

        ///  The Cell.setFormula() method is for adding 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 the Cell.setFormula() method using 
            a relative formula created with RC notation. 
            string formula = "=SUM(R4C:R[-1]C)";

            ///Display the formula in cell A25 
            ws[24, 0].Formula = formula;

            ///Display that formula in cell B25 
            ws[24, 1].Formula = formula;

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

            ///Display a formula that sums the range in cell C25. 
            ws[24, 2].Formula = "=SUM(MarchRange)";
        }

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

            ///Workbook.getPalette returns the Palette object 
            Palette pal = wb.Palette;

            ///GetClosestColor finds the color in the palette
            //that most closely matches the specified Red,
            //Green, and Blue values.
            
            clrDarkBlue = pal.GetClosestColor(0, 35, 149);
            clrTitleCell = pal.GetClosestColor(0, 35, 149);

            try
            {
                ///getColor will find the exact color you request.
                //If the color does not exist in the palette
                //it will throw an Exception.
                
                clrDarkGreen = pal.GetColor(51, 153, 102);
            }
            catch
            {
                // The color you requested 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 = pal.GetColorAt(1);

            ///setColor and setColorAt are for changing
            //the colors in the palette. Specify a Color to
            //change for setColor, the index of the color for
            //setColorAt, and then the RGB values for the desired
            //appearance.
            
            
            
            pal.SetColorAt(0, 0, 255, 0);

            ///The Color object has built-in colors 
            Color clrSysGreen = Color.SystemColor.Green;
            Color clrSysBlack = Color.SystemColor.Black; ;
        }

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