Intro
Excerpt |
---|
This sample covers a wide range of basic ExcelApplication functionality. Excellent for getting started or brushing up on basic skills. |
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 { // 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; /// <summary> /// Build the report with ExcelApplication /// </summary> public void GenerateReport() { // Create an instance of ExcelApplication, // a new Workbook, and create one WorksheetWorksheet xlw = new ExcelApplication(); wb = xlw.Create(ExcelApplication.FileFormat.Xlsx); ws = wb.Worksheets[0]; // Demonstrate setting cell valuesvalues this.PopulateWorksheet(); // Demonstrate setting formulasformulas this.AddCellFormulas(); // Set up colors that will be used in // the workbook this.SetupColors(); // Add styles to the worksheetworksheet this.AddStyles(); // Set the Page Setup propertiesproperties this.DoPageSetup(); // Set Document PropertiesProperties this.AddDocumentProperties(); // Add a chart to the worksheetworksheet this.AddChart(); // Add some comments to the worksheetworksheet this.AddCellComment(); // Add a JPEG picture to the worksheetworksheet this.AddPicture(); // Add a hyperlinkhyperlink this.AddHyperlink(); // Use the CharacterRun object to // stylize individual characters // in a cell. // this.StylizeHeaderCharacters(); // Add Conditional Formatting to the // Worksheet // this.AddConditionalFormating(); // Turn off gridlines displaydisplay ws.ShowGridlines = false; // Save the report by streaming itit xlw.Save(wb, @"..\..\OutputExcelOutputFiles\BasicSteps_output.xlsx"); } /// <summary> 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. /// </summary> private void StylizeHeaderCharacters() { // Get a reference to cell A1A1 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 cellcell cellHeader.GetCharacters(11, 12).Font.Color = wb.Palette.GetClosestColor(25, 116, 210); cellHeader.GetCharacters(24, 12).Font.Color = wb.Palette.GetClosestColor(77, 82, 81); } /// <summary> Add protection to workbooks and worksheets /// with various security-related properties. /// </summary> 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; } /// <summary> Create hyperlinks in cells with the /// Cell.CreateHyperlink method. /// It returns a Hyperlink object /// </summary> private void AddHyperlink() { // Write bold-faced text into a cell to describe the hyperlinkhyperlink ws[30, 0].Value = "Click here to visit SoftArtisans Support:"; ws[30, 0].Style.Font.Bold = true; // Create the hyperlinkhyperlink 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 objectobject ConditionalFormat cf1 = wb.CreateConditionalFormat(); //Create a range of the value columnscolumns Area valuesArea = ws.CreateArea("A4:C24"); //Create a set of conditions, and set the fonts to warmer colors as the values go upup 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 valuesvalues valuesArea.SetConditionalFormat(cf1); } /// <summary> Insert external picture files into worksheets /// with the Pictures.CreatePicture method. /// Specify its location with an Anchor object. /// </summary> private void AddPicture() { // The path to the picture that will be importedimported string picturePath = @"..\..\imagesExcelImages\OWlogo.gif"; // Create an Anchor on which the picture will setset 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); } /// <summary> Comment items can be added, /// edited, or removed from cells. /// </summary> private void AddCellComment() { // Add a comment to cell C3C3 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; } /// <summary> The DocumentProperties object is used for setting /// workbook metadata such as Author, Comments, Company, /// and also user-defined custom properties. /// </summary> private void AddDocumentProperties() { // Get the DocumentProperties interface from WorkbookWorkbook DocumentProperties docprops = wb.DocumentProperties; // Set built-in DocumentProperties valuesvalues 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 pairspairs docprops.SetCustomProperty("GeneratedBy", "SoftArtisans OfficeWriter for Excel"); } /// <summary> 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. /// </summary> private void AddChart() { // Create an Anchor on which the Chart will be placedplaced 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 datadata SeriesCollection seriesCol = columnChart.SeriesCollection; // Set the month header row as the Primary axis's category datadata seriesCol.CategoryData = "Sheet1!A3:C3"; // Add the totals row as a series and name it "Sales" Series chartSeries = seriesCol.CreateSeries("Sheet1!A25:C25"); chartSeries.Name = "Sales"; // Change the chart's titletitle columnChart.Title.Text = "Basic Column Chart"; } /// <summary> 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. /// </summary> private void DoPageSetup() { // Get the PageSetup object from the WorksheetWorksheet 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 gridlinesgridlines ps.PrintGridlines = false; // Change printing orientation to Landscape, and. paper size to LegalLegal // ps.Orientation = PageSetup.PageOrientation.Landscape; ps.PaperSize = PageSetup.PagePaperSize.Legal; } /// <summary> 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. /// </summary> 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 cellscells 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 cellcell ws[0, 0].Style = styleTitle; // Make the month header row boldbold ws.CreateArea(2, 0, 1, 3).SetStyle(styleHeader); } /// <summary> 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. /// </summary> private void PopulateWorksheet() { // Cells can be referenced by Excel name (eg, A1, B2) or by 0-based row number/column numbernumber // 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 cellscells for (int iRow = 3; iRow <= 23; iRow++) for (int iCol = 0; iCol <= 2; iCol++) ws[iRow, iCol].Value = iRow + iCol; } /// <summary> 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. /// </summary> private void AddCellFormulas() { // Apply forumlas to cells with the Cell.setFormula() methodmethod // Display the formula in cell A25A25 ws[24, 0].Formula = "=SUM(A4:A24)"; // To build formula strings, the Cell.Name method is useful to get the Excel address (eg, A1, B2) for a given row/column indexindex // string formula = String.Format("=SUM({0}:{1})", ws[3, 1].Name, ws[23, 1].Name); // Display that formula in cell B25 using row/column ordinalordinal ws[24, 1].Formula = formula; // Insert a formula that uses a Named Range as its operandoperand Range rngMarch = ws.CreateNamedRange("C4:C24", "MarchRange"); ws[24, 2].Formula = "=SUM(MarchRange)"; } /// <summary> 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. /// </summary> private void SetupColors() { // Note: clrDarkBlue, clrDarkGreen, and clrComment // are declared above as private class members. // // Workbook.getPalette returns the Palette objectobject 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, 25, 149); clrTitleCell = pal.GetClosestColor(100, 100, 255); 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. // int c = clrDarkBlue.PaletteIndex; pal.SetColorAt(0, 0, 35, 149); // The Color object has built-in colors. // These variables are declared here for demonstration, // but they are not used // Color clrSysGreen = Color.SystemColor.Green; Color clrSysBlack = Color.SystemColor.Black; } /// <summary> 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) /// </summary> 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: BasicSteps_output.xlsx
Image: OWlogo.gif