private void AddToolbar()
        {
            string barName            = Properties.Resources.OrdersToolbar;
            string weeklyCaption      = Properties.Resources.WeeklyMenu;
            string unscheduledCaption = Properties.Resources.UnscheduledMenu;

            // Toolbars are unique for the whole application.
            // In case there are two workbooks running this code,
            // the second workbook should check first if the
            // toolbar is already there.
            // If it is not, it has to be created.
            // In any case, a handler must be added.
            Office.CommandBar commandBar = null;

            Office.CommandBarButton weeklyButton;
            Office.CommandBarButton unscheduledButton;

            for (int i = 1; i <= ThisApplication.CommandBars.Count; ++i)
            {
                if (ThisApplication.CommandBars[i].Name == barName)
                {
                    commandBar = ThisApplication.CommandBars[i];
                    break;
                }
            }

            if (commandBar == null)
            {
                commandBar = this.Application.CommandBars.Add(barName, Office.MsoBarPosition.msoBarTop, missing, true);
            }


            if (commandBar.FindControl(missing, missing, weeklyCaption, missing, false) == null)
            {
                weeklyButton = (Office.CommandBarButton)commandBar.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, missing);

                weeklyButton.Caption = weeklyCaption;

                weeklyButton.Picture = ExcelHelpers.Convert(toolBarImages.Images["CreateWeeklyOrder"]);
                weeklyButton.Mask    = ExcelHelpers.Convert(toolBarImages.Images["CreateWeeklyOrderMask"]);
                weeklyButton.Tag     = weeklyCaption;
            }
            else
            {
                weeklyButton = (Office.CommandBarButton)commandBar.Controls[weeklyCaption];
            }

            if (commandBar.FindControl(missing, missing, unscheduledCaption, missing, false) == null)
            {
                unscheduledButton = (Office.CommandBarButton)commandBar.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, missing);

                unscheduledButton.Caption = unscheduledCaption;

                unscheduledButton.Picture = ExcelHelpers.Convert(this.toolBarImages.Images["CreateUnscheduledOrder"]);
                unscheduledButton.Mask    = ExcelHelpers.Convert(this.toolBarImages.Images["CreateUnscheduledOrderMask"]);
                unscheduledButton.Tag     = unscheduledCaption;
            }
            else
            {
                unscheduledButton = (Office.CommandBarButton)commandBar.Controls[unscheduledCaption];
            }

            commandBar.Visible = true;

            this.weeklyToolbarButton      = weeklyButton;
            this.unscheduledToolbarButton = unscheduledButton;

            this.toolBar = commandBar;
        }
        private void PopulateStatColumn(int column, Excel.Range start, Excel.Range end)
        {
            try
            {
                // Determines the range that needs to get filled with data.
                Excel.Range twoLines = start.get_Resize(2, 1);

                twoLines.Merge(System.Type.Missing);

                Excel.Range fillRange = this.worksheet.get_Range(start, end);
                end.Select();

                switch (column)
                {
                case (int)StatHeadings.DailySales:
                    // Fills in the daily sales column.
                    // Gets the addresses of the cells containing the
                    // standard deviation and average.
                    Excel.Range average            = start.Previous;
                    string      averageAddress     = average.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                    Excel.Range standardDev        = average.get_Offset(1, 0);
                    string      standardDevAddress = standardDev.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // Sets the formulas for the column.
                    start.Formula = "=" + averageAddress + "+ (2*" + standardDevAddress + ")";

                    // Format "0.00" - two decimal places.
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                case (int)StatHeadings.Required:
                    // Fills in the required column.
                    // Determines the address for the cell containing
                    // the expected sales.
                    Excel.Range expectedSales = start.Previous;
                    string      salesAddress  = expectedSales.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // Determines how much inventory is required
                    // until delivery.
                    // Determines the number of days until delivery.
                    int waitDays = this.GetDaysToDelivery();

                    start.Formula = "=" + waitDays + "*" + salesAddress;

                    // Format "0.00" - two decimal places.
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                case (int)StatHeadings.CurrentInventory:
                    // Fills in the current inventory column.
                    // Gets the range for the last day from the journal.
                    int         count       = (end.Row - start.Row + 1) / 2;
                    Excel.Range currentCell = start;

                    for (int row = 0; row < count; row += 1)
                    {
                        Excel.Range flavorCell = currentCell.get_Offset(0, 0 - 5);


                        string flavor    = ExcelHelpers.GetValueAsString(flavorCell);
                        int    inventory = Globals.DataSet.Sales.FindByDateFlavor(Globals.DataSet.MaxDate, flavor).Inventory;

                        currentCell.Value2 = inventory;

                        if (row != 0)
                        {
                            Excel.Range twoCells = currentCell.get_Resize(2, 1);

                            twoCells.Merge(System.Type.Missing);
                            currentCell = twoCells;
                        }

                        currentCell = currentCell.get_Offset(1, 0);
                    }

                    break;

                case (int)StatHeadings.ProjectInventory:

                    // Gets the addresses for the projected sales and
                    // current inventory.
                    Excel.Range currentInventory        = start.Previous;
                    Excel.Range required                = currentInventory.Previous;
                    string      currentInventoryAddress = currentInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                    string      requiredAddress         = required.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // Determines the inventory expected on the
                    // delivery date.
                    start.Formula = "=MAX(0," + currentInventoryAddress + "-" + requiredAddress + ")";

                    // Format "0.00" - two decimal places.
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                case (int)StatHeadings.OrderQuanity:
                    // Determines the addresses for the projected inventory
                    // and the required amounts.
                    Excel.Range projectedInventory        = start.Previous;
                    Excel.Range needed                    = projectedInventory.Previous.Previous;
                    string      projectedInventoryAddress = projectedInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                    string      neededAddress             = needed.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // Determines the order size needed for each item.
                    start.Formula = "=" + neededAddress + "-" + projectedInventoryAddress;

                    // Format "0.00" - two decimal places.
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                default:
                    break;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
                throw;
            }
        }
Example #3
0
        private void PopulateStatColumn(int column, Excel.Range start, Excel.Range end)
        {
            try
            {
                // 确定需要用数据填充的范围。
                Excel.Range twoLines = start.get_Resize(2, 1);

                twoLines.Merge(System.Type.Missing);

                Excel.Range fillRange = this.worksheet.get_Range(start, end);
                end.Select();

                switch (column)
                {
                case (int)StatHeadings.DailySales:
                    // 填充日销售额列。
                    // 获取包含标准偏差和
                    // 平均值的单元格的地址。
                    Excel.Range average            = start.Previous;
                    string      averageAddress     = average.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                    Excel.Range standardDev        = average.get_Offset(1, 0);
                    string      standardDevAddress = standardDev.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // 设置该列的公式。
                    start.Formula = "=" + averageAddress + "+ (2*" + standardDevAddress + ")";

                    // 格式“0.00”- 两个小数位。
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                case (int)StatHeadings.Required:
                    // 填充所需的列。
                    // 确定包含预期销售额的
                    // 单元格的地址。
                    Excel.Range expectedSales = start.Previous;
                    string      salesAddress  = expectedSales.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // 确定交货前需要多少
                    // 库存。
                    // 确定交货前的天数。
                    int waitDays = this.GetDaysToDelivery();

                    start.Formula = "=" + waitDays + "*" + salesAddress;

                    // 格式“0.00”- 两个小数位。
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                case (int)StatHeadings.CurrentInventory:
                    // 填充当前库存列。
                    // 从日记中获取上一天的范围。
                    int         count       = (end.Row - start.Row + 1) / 2;
                    Excel.Range currentCell = start;

                    for (int row = 0; row < count; row += 1)
                    {
                        Excel.Range flavorCell = currentCell.get_Offset(0, 0 - 5);


                        string flavor    = ExcelHelpers.GetValueAsString(flavorCell);
                        int    inventory = Globals.DataSet.Sales.FindByDateFlavor(Globals.DataSet.MaxDate, flavor).Inventory;

                        currentCell.Value2 = inventory;

                        if (row != 0)
                        {
                            Excel.Range twoCells = currentCell.get_Resize(2, 1);

                            twoCells.Merge(System.Type.Missing);
                            currentCell = twoCells;
                        }

                        currentCell = currentCell.get_Offset(1, 0);
                    }

                    break;

                case (int)StatHeadings.ProjectInventory:

                    // 获取预计销售额和
                    // 当前库存的地址。
                    Excel.Range currentInventory        = start.Previous;
                    Excel.Range required                = currentInventory.Previous;
                    string      currentInventoryAddress = currentInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                    string      requiredAddress         = required.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // 确定交货日期的
                    // 预计库存。
                    start.Formula = "=MAX(0," + currentInventoryAddress + "-" + requiredAddress + ")";

                    // 格式“0.00”- 两个小数位。
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                case (int)StatHeadings.OrderQuanity:
                    // 确定预计库存和
                    // 所需库存量的地址。
                    Excel.Range projectedInventory        = start.Previous;
                    Excel.Range needed                    = projectedInventory.Previous.Previous;
                    string      projectedInventoryAddress = projectedInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
                    string      neededAddress             = needed.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);

                    // 确定每一项所需的订货量。
                    start.Formula = "=" + neededAddress + "-" + projectedInventoryAddress;

                    // 格式“0.00”- 两个小数位。
                    start.NumberFormat = "0.00";
                    twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
                    break;

                default:
                    break;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
                throw;
            }
        }
Example #4
0
        private void AddToolbar()
        {
            string barName            = Properties.Resources.OrdersToolbar;
            string weeklyCaption      = Properties.Resources.WeeklyMenu;
            string unscheduledCaption = Properties.Resources.UnscheduledMenu;

            // 工具栏对于整个应用程序是唯一的。
            // 如果有两个工作簿运行此代码,
            // 第二个工作簿应先检查工具栏
            // 是否已存在。
            // 如果不存在,则必须创建。
            // 在任何情况下,都必须添加一个处理程序。
            Office.CommandBar commandBar = null;

            Office.CommandBarButton weeklyButton;
            Office.CommandBarButton unscheduledButton;

            for (int i = 1; i <= ThisApplication.CommandBars.Count; ++i)
            {
                if (ThisApplication.CommandBars[i].Name == barName)
                {
                    commandBar = ThisApplication.CommandBars[i];
                    break;
                }
            }

            if (commandBar == null)
            {
                commandBar = this.Application.CommandBars.Add(barName, Office.MsoBarPosition.msoBarTop, missing, true);
            }


            if (commandBar.FindControl(missing, missing, weeklyCaption, missing, false) == null)
            {
                weeklyButton = (Office.CommandBarButton)commandBar.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, missing);

                weeklyButton.Caption = weeklyCaption;

                weeklyButton.Picture = ExcelHelpers.Convert(toolBarImages.Images["CreateWeeklyOrder"]);
                weeklyButton.Mask    = ExcelHelpers.Convert(toolBarImages.Images["CreateWeeklyOrderMask"]);
                weeklyButton.Tag     = weeklyCaption;
            }
            else
            {
                weeklyButton = (Office.CommandBarButton)commandBar.Controls[weeklyCaption];
            }

            if (commandBar.FindControl(missing, missing, unscheduledCaption, missing, false) == null)
            {
                unscheduledButton = (Office.CommandBarButton)commandBar.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, missing);

                unscheduledButton.Caption = unscheduledCaption;

                unscheduledButton.Picture = ExcelHelpers.Convert(this.toolBarImages.Images["CreateUnscheduledOrder"]);
                unscheduledButton.Mask    = ExcelHelpers.Convert(this.toolBarImages.Images["CreateUnscheduledOrderMask"]);
                unscheduledButton.Tag     = unscheduledCaption;
            }
            else
            {
                unscheduledButton = (Office.CommandBarButton)commandBar.Controls[unscheduledCaption];
            }

            commandBar.Visible = true;

            this.weeklyToolbarButton      = weeklyButton;
            this.unscheduledToolbarButton = unscheduledButton;

            this.toolBar = commandBar;
        }