/// <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(); } }