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
Here are some of the features shown in the sample:
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
Output: Bas= icStepsR1C1_output.xlsx
Image: OWlogo.gif