class Basic_Steps_R1C1
    {
        // /This class creates a workbook with only one Worksheet.
        //    Declare ExcelApplication, Workbook, Worksheet, and Color
        //    objects at   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, @"..\..\OutputExcelOutputFiles\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 = @"..\..\imagesExcelImages\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();
        }
    }