Data Analysis Sample
Note
This sample runs only in Microsoft Office Excel 2007.
This sample demonstrates data analysis tasks executed using toolbar buttons and menus in Microsoft Office Excel 2007. The data is stored in XML files.
In addition, the sample is completely localizable; it demonstrates how to take advantage of System.Resources.ResourceManager and managed resource (RESX) files. The code was written to be globalized using culture-switching techniques that were necessary in earlier versions of Visual Studio Tools for Office. Those techniques are no longer necessary because of a change in how Visual Studio Tools for Office works with Excel. For more information, see Globalization and Localization of Office Solutions, Formatting Data in Excel with Various Regional Settings, and How to: Make String Literals Region-safe in Excel Using Reflection.
Note
This sample does not run if you have an English language version of Office running on Windows with regional settings other than English (United States).
The sample is a simple inventory management tool for a fictitious ice cream store. The workbook has systems for tracking sales, inventory in storage, and the products sold. Each of these systems stores data in XML files.
The store has the following constraints on inventory:
Storage capacity is limited to 300 units of ice cream.
Ice cream orders are delivered once per week, in the morning.
Orders must be placed a minimum of two days before scheduled delivery date. The schedule is to place the order on Tuesday, and receive delivery on Thursday.
If additional stock is needed outside of the normal delivery schedule, there is an additional charge of 25 to have the order delivered.
.gif "Security note") Security Note: |
|---|
This sample code is intended to illustrate a concept, and it shows only the code that is relevant to that concept. It may not meet the security requirements for a specific environment, and it should not be used exactly as shown. We recommend that you add security and error-handling code to make your projects more secure and robust. Microsoft provides this sample code "AS IS" with no warranties. |
To run this sample
Press F5.
The workbook opens to the Inventory worksheet, which displays a pivot table of average daily sales and average daily profits per flavor and a ListObject control that shows the previous day’s sales data. Notice that a group named Menu Commands and a group named Custom Toolbars have been added to the Add-Ins tab of the Ribbon. An Orders menu has been added to the Menu Commands group, and two buttons that correspond to the two menu items have been added to the Custom Toolbars group.
View historical sales data by selecting a different date in the Calendar control. If you select the last day of data in the data source, two additional columns (Estimated Inventory and Recommendation) are displayed.
Click Add New Date to add data for a new day.
The ListObject control is cleared so you can enter the end-of-day inventory values for each flavor. As you enter the current inventory for each ice cream flavor, the Estimated Inventory column shows expected end-of-week shortfalls or overages. The Recommendation column shows whether it makes sense to create an unscheduled order. The actions pane shows a list of high inventory items and low inventory items.
Click Save Data to save your changes.
Click an ice cream flavor in one of the lists on the actions pane.
Historical sales data and a trend chart on that flavor appear in the Details worksheet.
If an unscheduled order is recommended, click Create to determine which flavors and how much to order.
A new worksheet named Unscheduled Order_<Date> is added to the workbook. The worksheet estimates the quantity of each ice cream flavor that should be ordered to cover any shortage for the rest of the week.
Click Create Weekly Order on the Orders menu to create the weekly order.
Sales information is read from the XML files for the previous two weeks, the average of the daily sales is calculated, and a standard deviation of the distribution is determined. An estimate of future sales is calculated based on average daily sales plus two standard deviations, multiplied by seven days. This gives a 95.4% probability that the amount ordered will cover the week’s expected sales.
A new worksheet named Weekly Order_<Date> is added to the workbook. <Date> is the projected order date.
Demonstrates
The sample demonstrates the following tasks:
Reading data from XML files.
Customizing menus and toolbars.
Using Excel built-in functions to analyze data.
Creating pivot tables that are linked to data in XML files.
Binding XML data to Excel list controls.
Creating charts.
Preparing user interface layout and strings for localization.
See Also
Concepts
Globalization and Localization of Office Solutions
Host Items and Host Controls Overview