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