How to Write a Sales Report - DOC by vkv75576

VIEWS: 204 PAGES: 15

How to Write a Sales Report document sample

More Info
									Module 03: Basic WordprocessingML                                                         Lab 03




Lab 03: Basic WordprocessingML
Generating Sales Report Summary Documents
              Imagine you work for AdventureWorks, a sales company that sells outdoor
              equipment online and through an internal sales force. At the end of each year,
              management needs an overview of the performance of each sales person. You’ve
              been given the task of creating a document that contains each sales person’s total
              sales for the year as well as a breakdown of sales by company.

              You could manually generate this document by totalling up each person’s sales or
              you could take advantage of the Office Open XML specifications and write code
              to generate the data.

              For this lab your goal will be to generate a rough outline of the document. For
              input, you’ll have an XML file that is generated from your sales database and the
              Packaging API that ships with .NET 3.0. Use these tools to generate the document
              shown below.




Office Open XML                                                                                1
Lab 03                                                        Module 03: Basic WordprocessingML



Exercise 0: Getting Started

          In this exercise, you will open the OfficeOpenXML solution, which contains the
          starting points for all of the labs.


               To open the Office Open XML solution:

          1.    In Visual Studio 2005, on the File menu, select to Open, and then click
                Project/Solution.
                      If Visual Studio 2005 is not open, click Start, point to All
                      Programs, point to Microsoft Visual Studio 2005, and then click
                  Microsoft Visual Studio 2005.
          2.    In the Open Project dialog box, navigate to the
                C:\OfficeOpenXML\Labs\Starter folder, select Starter.sln, and then click
                Open.
          3.    Locate the Lab03 project in the Solution Explorer. All of the following
                exercises will be done using this project.
                       If the Solution Explorer is not visible, click View and select
                       Solution Explorer to open it.
          4.    Right click Lab03 in the Solution Explorer and select Set as StartUp
                Project. This will cause all commands such as Build and Debug to act on
                this project.

Exercise 1: Opening an Office Open XML Package

          An Office Open XML document appears to be a single file. This is convenient
          for those who want to store it on a file server or email it to a colleague, but it adds
          complexity to software that reads it. For example it’s more difficult to access an
          image in a document if that image is embedded inside a larger, non standardized
          format.

          To solve this problem, an Office Open XML document is both a single file and a
          collection of files. This arrangement is known as a Package; essentially Zip file
          containing Package Parts that are used to make up a complete document.




2                                                                                Office Open XML
Module 03: Basic WordprocessingML                                                          Lab 03




              In this first exercise you’ll be creating a class called PackageHelper to
              encapsulate access to the Packaging APIs. This class will aid in loading a package
              from an array of bytes, allowing System.Xml based access to the Package Parts
              contained within the package, and finally allow you to save the in-memory
              package into a file.

              To do this the class will contain a reference to a MemoryStream object which
              represents the in-memory package and the Package class from the Packaging API
              that will control access to the in-memory package.


                   Add a reference to the Packaging API:

              1.    Right click the Lab03 project in the Solution Explorer and click Add
                    Reference.
              2.    In the Add Reference dialog, select the Browse tab and navigate to the
                    C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0
                    folder.




              3.    Select the WindowsBase.dll and click OK to add the reference.


Office Open XML                                                                                3
Lab 03                                                 Module 03: Basic WordprocessingML



             To open a Office Open XML package:

         1.   In the Solution Explorer locate the PackageHelper.cs class.
         2.   Declare two member fields in the PackageHelper class.
              private MemoryStream m_packageData;
              private Package m_package;

         3.   Locate the constructor in the PackageHelper class and implement it. Start
              by creating and loading the memory stream from the data parameter. Then
              create the new System.IO.Packaging.Package object using the new stream.
              // load the memory stream
              m_packageData = new MemoryStream ();
              m_packageData.Write(data, 0, data.Length);

              // open the package
              m_package =
                  Package.Open(m_packageData,
                      FileMode.Open,
                      FileAccess.ReadWrite); // use read write access

                    When using the Package.Open method, make sure you use a file
                    access mode containing write permissions if you want to save your
              changes.
         4.   Locate the Dispose method in PackageHelper and add some cleanup code.
              First close the Package object, and then close the MemoryStream.
              m_package.Close();
              m_packageData.Close();


             To read data from a Package Part:

         1.   Locate the GetWritablePart method in the PackageHelper class.
         2.   Access the PackagePart within the Package using the GetPart method and
              the partUri parameter.
              // get the part
              PackagePart writablePart = m_package.GetPart(partUri);

         3.   Create a new XmlDocument object and load it using the stream returned by
              the part’s GetStream method.
              // load the part into a XmlDocument
              XmlDocument partXml = new XmlDocument();
              using (Stream partStream =
                  writablePart.GetStream(FileMode.Open, FileAccess.Read))
              {
                  partXml.Load(partStream);
              }


4                                                                           Office Open XML
Module 03: Basic WordprocessingML                                                           Lab 03



                           Notice that the GetStream method only asks for Read access. At
                           this point you only need to read data. You’ll write the data back to a
                    stream later.
              4.    Finally return the XmlDocument you’ve just loaded.
                    // return the document
                    return partXml;


                   To write data to a Package Part:

              1.    Locate the SavePart method in the PackageHelper class.
              2.    Once again, access the PackagePart within the Package using the GetPart
                    method and the partUri parameter.
                    // get the part
                    PackagePart writablePart = m_package.GetPart(partUri);

              3.    Access the part’s stream using the GetStream method and write the contents
                    of the XmlDocument to the part’s stream.
                    // load the part into a XmlDocument
                    using (Stream partStream =
                        writablePart.GetStream(FileMode.Open, FileAccess.Write))
                    {
                        partStream.SetLength(0);
                        partXml.Save(partStream);
                    }

                          Make sure you set the length of the part stream to zero before writing
                          the data to the part stream. If you forget this step and write a smaller
                    amount of data to the stream, you could end up with a corrupt package part.


                   Saving the Package to the file system:

              1.    Locate the Save method in the PackageHelper class.
              2.    Call the package’s Close method to flush all of the data to the memory
                    stream.
                    // flush and data in the package buffers to the stream
                    m_package.Flush();
                    m_package.Close();

                           If you forget to close the package before writing the contents of the
                           memory stream to a file, problems could occur. The Package object
                    uses internal buffers that won’t be properly flushed to the MemoryStream if
                    Flush and Close are not called.



Office Open XML                                                                                  5
Lab 03                                                       Module 03: Basic WordprocessingML



          3.   Write the MemoryStream to a file using a FileStream object and then close
               the MemoryStream.
               // write the stream to the output file
               using (FileStream outputStream = File.Create(filename))
                   m_packageData.WriteTo(outputStream);

               // close the stream
               m_packageData.Close();

                      In the interest of simplicity, the PackageHelper class is not as robust
                      as needed for real world systems. No validation is done to restrict
               access to the Package object after it is closed.
                      Be aware that the lab will not build at this point. In the next exercise
                      you will be adding a reference to another project. At that point, the
               project will build.

Exercise 2: Writing data using WordprocessingML

          Now that you know how to manipulate an Office Open XML package and modify
          its parts, it’s time to use that knowledge to create a WordprocessingML
          document. In this exercise you’ll be using the sales person summary XML
          documents to generate a simple sales report.

          To allow you to focus on WordprocessingML instead of the incoming XML data,
          a set of classes has been created for you to provide quick access to the data. These
          classes are located in the SalesReportData project you’ll see in the solution.
          There are also a few classes added to Lab03 named SalesReportDocument and
          SalesReportDocumentPage that process the sales report data and call methods to
          render the page using WordprocessingML. These methods are the ones you will
          implement to generate the sales report.

          Next you’ll need to know a little about WordprocessingML. For our purposes
          you’ll need to learn about three primary XML elements in the
          WordprocessingML namespace:

               http://schemas.openxmlformats.org/wordprocessingml/2006/main
               p         Represents a paragraph
               r         Represents a run of text. A paragraph can contain one or more of these.
               t         Represents text within a run.

          All of these elements must be located within the document/body element within
          the /word/document.xml part.
               <?xml version="1.0" encoding="utf-8"?>
               <w:document ...>
                 <w:body>
                   <w:p>



6                                                                                Office Open XML
Module 03: Basic WordprocessingML                                                          Lab 03



                          <w:r>
                            <w:t>Stephen Jiang</w:t>
                          </w:r>
                        </w:p>
                      </w:body>
                    </w:document>


                   Add a reference to the SalesReportData project:

              1.    Right click the Lab03 project in the Solution Explorer and click Add
                    Reference.




              2.    In the Add Reference dialog, select the Projects tab and select the
                    SalesReportData project and click OK.


                   Generate the Title WordprocessingML:

              1.    Locate the WriteTitle method of the SalesReportDocumentPage class.
              2.    Write the value in the m_salesPersonName field to the writer parameter as
                    a WordprocessingML paragraph. The code below will generate the XML
                    fragment shown
                    // create the title
                    // <w:p>
                    //   <w:r>
                    //     <w:t>Stephen Jiang</w:t>
                    //   </w:r>
                    // </w:p>
                    writer.WriteStartElement(Prefixes.WordprocessingML, "p",
                        Namespaces.WordprocessingML);
                    writer.WriteStartElement(Prefixes.WordprocessingML, "r",
                        Namespaces.WordprocessingML);



Office Open XML                                                                                7
Lab 03                                                   Module 03: Basic WordprocessingML



              writer.WriteElementString(Prefixes.WordprocessingML, "t",
                  Namespaces.WordprocessingML, this.m_salesPersonName);
              writer.WriteEndElement();
              writer.WriteEndElement();

                       Notice that the code above references a Prefixes class and a
                       Namespaces class. These are helper classes that contain constants
              definint the prefixes and uris of the various Office Open XML namespaces
              used. To better understand these namespaces examine the Namespaces.cs
              file in the project.
         3.   Write the date ranges for the report in the format “Sales from 1/1/2003 to
              12/1/2003” based on the values in the m_reportBeginDate and
              m_reportEndDate fields. The XML to generate is shown below; use the
              code above as a reference when writing this paragraph.
              // create the date ranges
              // <w:p>
              //   <w:r>
              //     <w:t>Sales from 1/1/2003 to 12/1/2003</w:t>
              //   </w:r>
              // </w:p>

                    Use the format string “Sales from {0:d} to {1:d}” and the
                    string.Format method to format the string to display in the text (w:t)
              element.


             Load the WordprocessingML Package:

         1.   Locate the Main method in the Program.cs file.
         2.   Read the sales report data using the SalesReportFactory class in the
              SalesReportData project.
              // read the sales report
              SalesReportData.SalesReport salesReport =
                  SalesReportData.SalesReportFactory.GetSalesReport();

         3.   Create the new Package object based on the BaseWordDocument resource.
              // create the package
              using (PackageHelper package =
                  new PackageHelper(Properties.Resources.BaseWordDocument))
              {

         4.   Inside the using statement, request an XmlDocument object from the
              /word/document.xml part using the PackageHelper object.
              // load the document package part into an XmlDocument
              Uri documentUri =
                  new Uri(@"/word/document.xml", UriKind.Relative);



8                                                                          Office Open XML
Module 03: Basic WordprocessingML                                                          Lab 03



                    XmlDocument documentXml =
                        package.GetWritablePart(documentUri);

                           When creating Uri objects for use with the Packaging API, make
                           sure you specify a UriKind of Relative. If you don’t do this, you
                    will encounter an error retrieving a part within a package.


                   Generate and save the Sales Report:

              1.    Following the call to GetWritableObject, find the paragraph node within
                    the BaseWordDocument resource document using an XPath query.
                    XPathNavigator documentNav = documentXml.CreateNavigator()
                        .SelectSingleNode("w:document/w:body/w:p",
                            Namespaces.NamespaceManager);

                           Notice the code above uses a Namespaces.NamespaceManager
                           property. This is needed since all of the nodes in an Office Open
                    XML document are namespace qualified. The SelectSingleNode method
                    needs a way to map the w: prefix in the XPath query to the appropriate
                    namespace.

                    Instead of creating a new namespace manager every time it is used, the
                    Namespaces class creates on and stores it for all calls to use. For more
                    information look at the Namespaces class in the Namespaces.cs file.
              2.    Replace the existing w:p element by calling the ReplaceRange method on
                    the XPathNavigator.
                    using (XmlWriter writer =
                        documentNav.ReplaceRange(documentNav))
                    {

                           The XPathNavigator.ReplaceRange method allows you to use an
                           XmlWriter to insert XML into an existing document. It even allows
                    you to replace a single node with multiple nodes.
              3.    Inside the previous using statement, create a new instance of
                    SalesReportDocument based on the sales report data and write its contents
                    to the XmlWriter.
                         // create the instance of the sales report document
                         SalesReportDocument report =
                             new SalesReportDocument(salesReport);

                         // write the document to the XmlWriter
                         report.WriteTo(writer);
                    }




Office Open XML                                                                                9
Lab 03                                                    Module 03: Basic WordprocessingML



                     The WriteTo method in the SalesReportDocument calls the
                     methods you created in the SalesReportDocumentPage class once
              for each sales person in the data. The details of its implementation are
              provided for you since they are not directly related to WordprocessingML.
              To view its implementation see the SalesReportDocument.cs file.
         4.   Save the updated XmlDocument to the /word/document.xml package part.
              // write the document into the package part
              package.SavePart(documentUri, documentXml);

         5.   Save the complete package to the file system as Lab03Solution.docx.
                   // write the package to the a file
                   package.Save("Lab03Solution.docx");
              }


             Run and verify the results

         1.   Build the project by opening the Build menu and clicking Build Lab03.
         2.   Run the project by opening the Debug menu and clicking Start Without
              Debugging.
                    If you try to run the application while the output file is currently in
                    use, the sample will fail. Simply close the generated file and run the
              sample again.
         3.   Open Windows Explorer and navigate to
              C:\OfficeOpenXML\Labs\Starter\Lab03\bin\Debug and open
              Lab03Solution.docx. You should see a document similar to the one below.
                     If Windows Explorer is not open, click Start, right click My
                     Computer and click Explore.




10                                                                          Office Open XML
Module 03: Basic WordprocessingML                                                             Lab 03



                           What you should see is a list of all the sales people followed by a
                           header defining the date ranges. In the next exercise you’ll add a
                    page break between each sales person and also add more information to each
                    page.

Exercise 3: Expanding the Sales Report

              The previous exercise you created a simple sales report. In this exercise you’ll
              expand the sales report. Along with the sales person’s name, you’ll need to add
              their email address to the document. Next you’ll add some sales summary
              information followed by a breakdown of their sales by territory.

              To add these features you’ll need to use a few more WordprocessingML elements.
              First you’ll need to learn how to place a page break in the application. Next you’ll
              need to learn how to add a table into a document.

                    http://schemas.openxmlformats.org/wordprocessingml/2006/main
                    br        Represents a break. This can be a page break or a line break.
                    tbl       Represents a table. Contains table row (tr) elements
                    tr        Represents a table row. Contains table column elements (tc)
                    tc        Represents a table column. Can contain paragraph elements (p)

              This is a simple example of a table containing a single row. Notice how table cells
              contain other elements such as paragraphs and even other tables.
                    <w:tbl>
                      <w:tr>
                        <w:tc>
                          <w:p>
                             ...
                          </w:p>
                        </w:tc>
                        <w:tc>
                          <w:p>
                             ...
                          </w:p>
                        </w:tc>
                      </w:tr>
                    </w:tbl>


                   Add a page break between sales people:

              1.    Locate the WritePageBreak method in the SalesReportDocument class.
              2.    Write a page break to the document using the XmlWriter.
                    // <w:br w:type="page" />
                    writer.WriteStartElement(Prefixes.WordprocessingML, "br",
                        Namespaces.WordprocessingML);



Office Open XML                                                                                  11
Lab 03                                                     Module 03: Basic WordprocessingML



              writer.WriteAttributeString(Prefixes.WordprocessingML,
                  "type", Namespaces.WordprocessingML, "page");
              writer.WriteEndElement();


             Add more data to each page:

         1.   Locate the WriteContact method of the SalesReportDocumentPage class.
         2.   Write the code to generate the contact information section header XML.
              // create the contact information section
              // <w:p>
              //   <w:r>
              //     <w:t>Contact</w:t>
              //   </w:r>
              // </w:p>
              writer.WriteStartElement(Prefixes.WordprocessingML, "p",
                  Namespaces.WordprocessingML);
              writer.WriteStartElement(Prefixes.WordprocessingML, "r",
                  Namespaces.WordprocessingML);
              writer.WriteElementString(Prefixes.WordprocessingML, "t",
                  Namespaces.WordprocessingML, "Contact");
              writer.WriteEndElement();
              writer.WriteEndElement();

         3.   Write the code to generate the contact information data based on the
              following XML. Use the m_salesPersonName and the
              m_salesPersonEmail fields to provide the name and email address.
              // fill in the contact information section
              // <w:p>
              //   <w:r>
              //     <w:t>Stephen Jiang</w:t>
              //     <w:br />
              //     <w:t>stephen0@adventure-works.com</w:t>
              //   </w:r>
              // </w:p>

                     The break (w:br) element indicates a line break. This can only be
                     used within a run (w:r) element. Different types of breaks can be
              defined such as page breaks. For a complete list see the ECMA Office Open
              XML specification.
         4.   Repeat steps 2 and 3 for the WriteSummary method. The XML that you’ll
              need to generate is contained within the comments.
                       Notice that this paragraph (w:p) contains two run (w:r) elements in
                       it. This is often used to apply different formatting to each run while
              still retaining the same paragraph formatting. Ex. This BOLD word will be
              contained within a separate run element in the WordprocessingML.


             Add a table containing sales by territory:


12                                                                            Office Open XML
Module 03: Basic WordprocessingML                                                           Lab 03



              1.    Locate the WriteTerritories method of the SalesReportDocumentPage
                    class.
              2.    Write the code to generate the territory section header XML.
                    // create the territory section header
                    // <w:p>
                    //   <w:r>
                    //     <w:t>Sales by Territory</w:t>
                    //   </w:r>
                    // </w:p>
                    writer.WriteStartElement(Prefixes.WordprocessingML, "p",
                        Namespaces.WordprocessingML);
                    writer.WriteStartElement(Prefixes.WordprocessingML, "r",
                        Namespaces.WordprocessingML);
                    writer.WriteElementString(Prefixes.WordprocessingML, "t",
                        Namespaces.WordprocessingML, "Sales by Territory");
                    writer.WriteEndElement();
                    writer.WriteEndElement();

              3.    Create the open table element (w:tbl).
                    // open the table element
                    writer.WriteStartElement(Prefixes.WordprocessingML, "tbl",
                        Namespaces.WordprocessingML);

              4.    Create an open table row element (w:tr) for each territory in the sales person
                    data record.
                    // write a row for each territory
                    foreach (KeyValuePair<SalesReportData.Territories, decimal>
                        salesByTerritory in m_salesByTerritory)
                    {
                        // write the table row
                        writer.WriteStartElement(Prefixes.WordprocessingML, "tr",
                            Namespaces.WordprocessingML);

              5.    Create a table cell element (w:tc) containing a paragraph for the territory
                    name. Get the territory name from the Key of the salesByTerritory
                    KeyValuePair.
                    // write the territory column
                    // <w:tc>
                    //   <w:p>
                    //     <w:r>
                    //        <w:t>Northwest</w:tr>
                    //     </w:r>
                    //   </w:p>
                    // </w:tc>
                    writer.WriteStartElement(Prefixes.WordprocessingML, "tc",
                        Namespaces.WordprocessingML);
                    writer.WriteStartElement(Prefixes.WordprocessingML, "p",
                        Namespaces.WordprocessingML);
                    writer.WriteStartElement(Prefixes.WordprocessingML, "r",
                        Namespaces.WordprocessingML);



Office Open XML                                                                                   13
Lab 03                                                    Module 03: Basic WordprocessingML



              writer.WriteElementString(Prefixes.WordprocessingML, "t",
                  Namespaces.WordprocessingML,
                  salesByTerritory.Key.ToString());
              writer.WriteEndElement();
              writer.WriteEndElement();
              writer.WriteEndElement();

         6.   Repeat step 5 to create a second table cell element (w:tc) containing the total
              sales from the territory. Get the total sales from the Value of the
              salesByTerritory KeyValuePair.
              // write the total sales column
              // <w:tc>
              //   <w:p>
              //     <w:r>
              //        <w:t>$10,000.00</w:tr>
              //     </w:r>
              //   </w:p>
              // </w:tc>

                     To get the date formatted correctly, call the ToString method on the
                     Value with a parameter of “C”. This will format the value as
              currency.

              Ex. salesByTerritory.ToString(“C”)
         7.   Close the table row element (w:tr) and the foreach loop.
                   // write the close row
                   writer.WriteEndElement();
              }

         8.   Close the table element (w:tbl).
              // end the table element
              writer.WriteEndElement();


             Run and verify the results

         1.   Build the project by opening the Build menu and clicking Build Lab03.
         2.   Run the project by opening the Debug menu and clicking Start Without
              Debugging.
                    If you try to run the application while the output file is currently in
                    use, the sample will fail. Simply close the generated file and run the
              sample again.
         3.   Open Windows Explorer and navigate to
              C:\OfficeOpenXML\Labs\Starter\Lab03\bin\Debug and open
              Lab03Solution.docx. You should see a document similar to the one below.



14                                                                           Office Open XML
Module 03: Basic WordprocessingML                                                         Lab 03



                           If Windows Explorer is not open, click Start, right click My
                           Computer and click Explore.




                   To finish up:

              1. On the File menu, click Save All.
              2. On the File menu, click Close Solution.




Office Open XML                                                                              15

								
To top