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;
}
}
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;
}
}
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;
}
