/// <summary>
/// 创建一个数据透视表，其中包含的数据来自用制表符分隔的文本文件。
/// </summary>
/// <param name="filePath">文本文件所在位置。</param>
/// <returns>已创建的数据透视表。</returns>
private Excel.PivotTable CreatePivotTable(string filePath)
{
// 如果该表已存在，
// 则返回现有的表。
string tableName = Properties.Resources.AveragesPivotTableName;
Excel.PivotTables tables = (Excel.PivotTables) this.PivotTables(missing);
System.Collections.Generic.Queue <double> savedWidths = new System.Collections.Generic.Queue <double>();
if (tables != null)
{
int count = tables.Count;
for (int i = 1; i <= count; i++)
{
Excel.PivotTable table = tables.Item(i);
if (table.Name == tableName)
{
return(table);
}
}
}
try
{
// AddField 将调整列的大小。
// 保存列宽，以便在添加数据透视字段之后还原
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
savedWidths.Enqueue((double)column.ColumnWidth);
}
// 创建数据透视表需要关闭保护功能。
Globals.ThisWorkbook.MakeReadWrite();
Excel.PivotTable table = Globals.ThisWorkbook.CreateSalesPivotTable(this.get_Range(pivotTableAddress, missing), filePath);
table.Name = tableName;
// 在数据透视表中，添加所需的
// 行和列。
table.AddFields("Flavor", missing, missing, missing);
Excel.PivotField soldField = table.AddDataField(table.PivotFields("Sold"), Properties.Resources.AverageSold, Excel.XlConsolidationFunction.xlAverage);
// 在数据透视表中设置所需数据的视图。
// 格式“0.0”- 一个小数位。
soldField.NumberFormat = "0.0";
Excel.PivotField profitField = table.AddDataField(table.PivotFields("Profit"), Properties.Resources.AverageProfit, Excel.XlConsolidationFunction.xlAverage);
// 在数据透视表中设置所需数据的视图。
// 格式“0.00”- 两个小数位。
profitField.NumberFormat = "0.00";
// 隐藏创建数据透视表时添加的两个浮动栏。
Globals.ThisWorkbook.ShowPivotTableFieldList = false;
Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false;
return(table);
}
finally
{
// AddField 将调整列的大小。还原列宽。
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
column.ColumnWidth = savedWidths.Dequeue();
}
Globals.ThisWorkbook.MakeReadOnly();
}
}
/// <summary>
/// Create a PivotTable with data from a tab-delimiter text file.
/// </summary>
/// <param name="filePath">Text file location.</param>
/// <returns>Created PivotTable.</returns>
private Excel.PivotTable CreatePivotTable(string filePath)
{
// If the table is already there,
// return the existing table.
string tableName = Properties.Resources.AveragesPivotTableName;
Excel.PivotTables tables = (Excel.PivotTables) this.PivotTables(missing);
System.Collections.Generic.Queue <double> savedWidths = new System.Collections.Generic.Queue <double>();
if (tables != null)
{
int count = tables.Count;
for (int i = 1; i <= count; i++)
{
Excel.PivotTable table = tables.Item(i);
if (table.Name == tableName)
{
return(table);
}
}
}
try
{
// AddFields will resize the columns. Save the columns' widths
// for restoring them after pivot fields are added
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
savedWidths.Enqueue((double)column.ColumnWidth);
}
// PivotTable creation requires that protection be off.
Globals.ThisWorkbook.MakeReadWrite();
Excel.PivotTable table = Globals.ThisWorkbook.CreateSalesPivotTable(this.get_Range(pivotTableAddress, missing), filePath);
table.Name = tableName;
// Adds the desired rows and columns within
// the PivotTable.
table.AddFields("Flavor", missing, missing, missing);
Excel.PivotField soldField = table.AddDataField(table.PivotFields("Sold"), Properties.Resources.AverageSold, Excel.XlConsolidationFunction.xlAverage);
// Sets the view of data desired within the PivotTable.
// Format "0.0" - one decimal place.
soldField.NumberFormat = "0.0";
Excel.PivotField profitField = table.AddDataField(table.PivotFields("Profit"), Properties.Resources.AverageProfit, Excel.XlConsolidationFunction.xlAverage);
// Sets the view of data desired within the PivotTable.
// Format "0.0" - two decimal places.
profitField.NumberFormat = "0.00";
// Hiding the two floating bars that get added when a PivotTable is created.
Globals.ThisWorkbook.ShowPivotTableFieldList = false;
Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false;
return(table);
}
finally
{
// AddFields will have resized the columns. Restore the columns' widths.
foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells)
{
column.ColumnWidth = savedWidths.Dequeue();
}
Globals.ThisWorkbook.MakeReadOnly();
}
}
