Message-ID: <1548758252.9617.1711696722664.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_9616_1946460829.1711696722664" ------=_Part_9616_1946460829.1711696722664 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 { // 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, clrCom= ment, clrTitleCell; /// <summary> /// Build the report with ExcelApplication /// </summary> public void GenerateReport() { // Create an instance of ExcelApp= lication, a new Workbook, and create one Wo= rksheet xlw =3D new ExcelApplication(); wb =3D xlw.Create(ExcelApplicatio= n.FileFormat.Xlsx); 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 to = the //Worksheet this.AddConditionalFormating(); // Turn off gridlines display&nbs= p; ws.ShowGridlines =3D false; // Save the report by streaming i= t xlw.Save(wb, @"..\..\ExcelOu= tputFiles\BasicSteps_output.xlsx"); } /// <summary> The Cell's getCharacters me= thod returns 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. /// </summary> 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); } /// <summary> Add protection to workbooks= and worksheets /// with various security-related properties. /// </summary> 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; } /// <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 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= ("A4:C24"); //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); } /// <summary> Insert external picture fil= es into worksheets /// with the Pictures.CreatePicture method. &nb= sp; /// Specify its location with an Anchor object.= /// </summary> 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); } /// <summary> Comment items can be added, /// edited, or removed from cells. /// </summary> 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; } /// <summary> The DocumentProperties obje= ct is used for setting /// workbook metadata such as Author, Comments,= Company, /// and also user-defined custom properties. /// </summary> 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"); } /// <summary> Adds a simple clustered col= umn 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; /// </summary> 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!A3:C3"; // Add the totals row as a series= and name it "Sales" Series chartSeries =3D seriesCol.= CreateSeries("Sheet1!A25:C25"); chartSeries.Name =3D "Sales&= quot;; // Change the chart's title columnChart.Title.Text =3D "= Basic Column Chart"; } /// <summary> Every Worksheet has a PageS= etup object that can be /// used to set print behavior. Commonly = used PageSetup /// capabilities are header/footers, and printe= r paper setup. /// </summary> 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( "Pa= ge &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; } /// <summary> Apply some basic stylizatio= n 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&qu= ot; or "layer" two Styles in one region. /// </summary> 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); } /// <summary> Add some data to the workbo= ok 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 valu= es to cells. /// </summary> 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; } /// <summary> The Cell.setFormula() metho= d 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 t= he Cell.setFormula() method // Display the formula in cell A2= 5 ws[24, 0].Formula =3D "=3DSU= M(A4:A24)"; // To build formula strings, the = Cell.Name method is useful to get the Excel addres= s (eg, A1, B2) for a given row/column index string formula =3D String.Format(= "=3DSUM({0}:{1})", ws[3, 1].Name, ws[23, 1].Name); // Display that formula in cell B= 25 using row/column ordinal ws[24, 1].Formula =3D formula; // Insert a formula that uses a N= amed Range as its operand Range rngMarch =3D ws.CreateNamed= Range("C4:C24", "MarchRange"); ws[24, 2].Formula =3D "=3DSU= M(MarchRange)"; } /// <summary> Colors are managed by the P= alette 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, 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, 25, 149); clrTitleCell =3D pal.GetClosestCo= lor(100, 100, 255); 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. int c =3D clrDarkBlue.PaletteInde= x; pal.SetColorAt(0, 0, 35, 149); // The Color object has built-in = colors. //These variables are decla= red here for demonstration, //but they are not used Color clrSysGreen =3D Color.Syste= mColor.Green; Color clrSysBlack =3D Color.Syste= mColor.Black; } /// <summary> This method is here for you= r convenience. 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) /// </summary> 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: BasicSt= eps_output.xlsx
Image: OWlogo.gif