Intro
Excerpt |
---|
Same as Basic Steps except all the formulas and references are in R1C1 notation. |
Here are some of the features shown in the sample:
- Setting cell values and formulas
- Adding a basic chart
- Using the color palette
- Applying styles and formatting
- Page setup, including headers and footers
- Setting "document properties" metadata
- Adding external pictures to a worksheet
- Defining clickable hyperlinks
- Using cell comments
- Setting Conditional Formatting
- Defining workbook and worksheet protection
Code
Code Block |
---|
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(); } } |
Downloads
Output: BasicStepsR1C1_output.xlsx
Image: OWlogo.gif