Publishing on the Web and Using XML

Advanced Training for Microsoft Office Excel 2003

Converting an Excel Workbook to XML

1	To begin saving an Excel workbook as an XML spreadsheet, click the File menu, and click Save As.
2	To indicate the folder in which to save the Daily Sales document, in the Save As dialog box, double-click the Garden Company folder, and then double-click the Reports folder.
3	To select the format in which the DailySales workbook will be saved, click the Save as type arrow, and then in the list, click XML Spreadsheet.
4	To continue saving the workbook as an XML spreadsheet, click Save.
5	To finish saving as an XML spreadsheet, in the message box warning that Microsoft Visual Basic project and header or footer images will not be saved, click Yes.
6	To open a task pane that enables you to map the data in this document to an XML source and modify other XML options, click the View menu, and click Task Pane.
7	To switch to the XML Source task pane, in the Task Pane, click the arrow to the right of the words Getting Started, and click XML Source.
8	To open this XML file in Notepad, so that you can view the underlying XML code, click the Windows Start menu, and click My Documents.
9	To locate the Daily Sales XML file, in the My Documents folder, double-click the Garden Company folder, and then double-click the Reports folder.
10	Notice that the Daily Sales file on the right is labeled as an XML document. To display the XML code for this file, click the DailySales XML document, and then click the File menu, point to Open with, and click Notepad.

Using XML Data Capabilities and Retrieving Web Data

1	To begin creating a Web query, in Internet Explorer, click the File menu, and then click Open.
2	To open the target Web page, in the Open dialog box, click Browse, double-click the Sales folder, click Financial, and then click Open.
3	To display the financial data in Internet Explorer, in the Open dialog box, click OK.
4	To select the table data, click the Edit menu, and then click Select All.
5	To copy the data to the clipboard, click the Edit menu, and then click Copy.
6	To paste the data into Excel, on the taskbar, click the Microsoft Excel taskbar button, click cell A1, and then on the Standard toolbar, click the Paste button.
7	Notice that the Paste Options button is displayed next to the data. To begin creating a Web query, click the Paste Options button, and then click Create Refreshable Web Query.
8	Notice that the New Web Query dialog box displays the data you copied from the Web page. To select the table to be imported into Excel, click the table icon next to Company Name, as indicated, and then click Import.
9	Notice that the HTML table data appears in the worksheet, with its original HTML formatting. To save your changes, on the Standard toolbar, click the Save button.
10	To create a new workbook, on the Standard toolbar, click the New button.
11	To begin adding the structure of a schema to the worksheet, click the Data menu, point to XML, and then click XML Source.
12	To begin establishing a data map in the worksheet, near the bottom of the XML Source task pane, click XML Maps, and then click Add.
13	To locate the schema to add, in the Select XML Source dialog box, double-click the Products folder, click Product.xsd, and then click Open.
14	To display the structure of the selected XML map in the XML Source task pane, in the XML Maps dialog box, click OK.
15	Notice that the structure of product_Map consists of three elements. To assign the productId element to the worksheet, in the XML Source task pane, click ns1 :productId and drag to cell B2.
16	Notice that the Header Options button is displayed to the right of cell B2. To display the name of the element in cell A2, click the Header Options button, and then click Place XML Heading to the Left.
17	To assign the productName element to the worksheet, in the XML Source task pane, click ns1:productName and drag to cell B3.
18	To display the name of the element in cell A3, click the Header Options button, and then click Place XML Heading to the Left.
19	To assign the priceEach element to the worksheet, in the XML Source task pane, click ns1:priceEach and drag to cell B4.
20	To display the name of the element in cell A4, click the Header Options button, and then click Place XML Heading to the Left.
21	To begin saving the file, on the Standard toolbar, click the Save button.
22	To name the file, for the purposes of this exercise, press SPACEBAR to have the name of the file typed for you, and then click Save.

Publishing Worksheets and PivotTables on the Web

1	To display the March worksheet, click the March sheet tab.
2	To begin saving the worksheet as an HTML document, click the File menu, and then click Save as Web Page.
3	To assign a file name, for the purposes of this exercise, press SPACEBAR to have the file name typed for you in the File name box.
4	To publish only the March worksheet on the Web, in the Save area, click Selection: Sheet.
5	To allow users to interact with the worksheet, select the Add interactivity check box, and then click Publish.
6	To begin setting a title for the worksheet, in the Publish as Web Page dialog box, in the Publish as area, click Change.
7	To assign a title to the Web page, press SPACEBAR to have the title typed for you in the Title box, and then click OK.
8	To update the Web version of the file whenever the original document is saved, select the AutoRepublish every time this workbook is saved check box.
9	To display the worksheet in Internet Explorer, select the Open published web page in browser check box, and then click Publish.
10	To interact with the worksheet by formatting cells, in the Internet Explorer window, click cell B5, and then on the Interactivity toolbar, click the Commands and Options button, as indicated.
11	In the Commands and Options dialog box, verify that the Format tab is displayed. To align the contents of cell B5 with the left edge of the cell, in the Text format area, click the Align Left button, as indicated.
12	To finish accessing the formatting tools, in the Commands and Options dialog box, click the Close button.
13	To switch back to Microsoft Excel, on the taskbar, click the Microsoft Excel taskbar button.
14	To open another workbook, on the Standard toolbar, click the Open button, click HourlySales, and then click Open.
15	To display the PivotTable worksheet, click the PivotTable sheet tab.
16	To begin publishing the PivotTable on the Web, click the File menu, and then click Save as Web Page.
17	To assign a file name, press SPACEBAR to have the file name typed for you in the File name box.
18	To publish only the worksheet with the PivotTable, in the Save area, click Selection: Sheet.
19	To allow users to interact with the worksheet, select the Add interactivity check box, and then click Publish.
20	Verify that Items on PivotTable is displayed in the Choose box, and that items from the worksheet are displayed in the Choose list. To select the PivotTable, in the Choose list, click PivotTable.
21	To publish the PivotTable on the Web, in the bottom section of the dialog box, verify that the Open published web page in browser check box is selected, and then click Publish.
22	Notice that the PivotTable is displayed in Internet Explorer. To expand the PivotTable to display every cell, on the Interactivity toolbar, click the Show Details button, as indicated.
23	To display only the sales for Monday, click the Weekday list arrow, as indicated, clear the Tue, Wed, Thu, and Fri check boxes, and then click OK.
24	To remove the filter, click the Weekday list arrow, select the (All) check box, and then click OK.
25	To close Internet Explorer, click the Close button.