/// <summary> /// Exports data from the list object into a text file and updates the PivotTable /// using that data. /// </summary> internal void UpdateSalesPivotTable(Excel.PivotTable pivotTable) { bool screenUpdating = Globals.ThisWorkbook.Application.ScreenUpdating; Application.ScreenUpdating = false; TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { string fileDirectory = System.IO.Path.GetDirectoryName(generator.FullPath); string fileName = System.IO.Path.GetFileName(generator.FullPath); this.ShowPivotTableFieldList = false; pivotTable.SourceData = new string[] { String.Format( CultureInfo.CurrentUICulture, salesPivotTableConnectionTemplate, fileDirectory), String.Format( CultureInfo.CurrentUICulture, salesPivotTableQueryTemplate, fileName) }; this.Application.CommandBars["PivotTable"].Visible = false; } finally { generator.Dispose(); this.Application.ScreenUpdating = screenUpdating; } }
Excel.PivotTable CreatePivotTable() { TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { Excel.Range destination = this.worksheet.get_Range(pivotTableAddress, defaultParameter); Excel.PivotTable pivotTable; pivotTable = Globals.ThisWorkbook.CreateSalesPivotTable(destination, generator.FullPath); // 调整新的数据透视表的属性 pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; // 在数据透视表中,添加所需的 // 行和列。 pivotTable.AddFields("Flavor", defaultParameter, defaultParameter, defaultParameter); Excel.PivotField soldField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.AverageOfUnitsSold, Excel.XlConsolidationFunction.xlAverage); // 在数据透视表中设置所需数据的视图。 // 格式“0.0”- 一个小数位。 soldField.NumberFormat = "0.0"; Excel.PivotField profitField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.StdDevOfUnitsSold, Excel.XlConsolidationFunction.xlStDev); // 在数据透视表中设置所需数据的视图。 // 格式“0.00”- 两个小数位。 profitField.NumberFormat = "0.00"; // 隐藏创建数据透视表时添加的两个浮动栏。 Globals.ThisWorkbook.ShowPivotTableFieldList = false; Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false; return(pivotTable); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; } finally { generator.Dispose(); } }
Excel.PivotTable CreatePivotTable() { TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { Excel.Range destination = this.worksheet.get_Range(pivotTableAddress, defaultParameter); Excel.PivotTable pivotTable; pivotTable = Globals.ThisWorkbook.CreateSalesPivotTable(destination, generator.FullPath); // Adjusts the properties of the new PivotTable pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; // Adds the desired rows and columns within // the PivotTable. pivotTable.AddFields("Flavor", defaultParameter, defaultParameter, defaultParameter); Excel.PivotField soldField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.AverageOfUnitsSold, 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 = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.StdDevOfUnitsSold, Excel.XlConsolidationFunction.xlStDev); // Sets the view of data desired within the PivotTable. // Format "0.00" - 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(pivotTable); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; } finally { generator.Dispose(); } }
private void Sheet1_Startup(object sender, System.EventArgs e) { try { this.Sheet1_TitleLabel.Value2 = Properties.Resources.Sheet1Title; this.Name = Properties.Resources.Sheet1Name; this.newDateButton.Text = Properties.Resources.AddNewDateButton; this.saveButton.Text = Properties.Resources.SaveDataButton; this.dayView = Globals.DataSet.CreateView(); if (Globals.DataSet.Sales.Count != 0) { this.DateSelector.MinDate = Globals.DataSet.MinDate; this.DateSelector.MaxDate = Globals.DataSet.MaxDate; this.DateSelector.Value = this.DateSelector.MaxDate; } using (TextFileGenerator textFile = new TextFileGenerator(Globals.DataSet.Sales)) { this.pivotTable = CreatePivotTable(textFile.FullPath); } Globals.DataSet.Sales.SalesRowChanged += new OperationsBaseData.SalesRowChangeEventHandler(Sales_SalesRowChanged); UnscheduledOrderControl smartPaneControl = new UnscheduledOrderControl(); smartPaneControl.Dock = DockStyle.Fill; smartPaneControl.View = this.dayView; Globals.ThisWorkbook.ActionsPane.Controls.Add(smartPaneControl); this.Activate(); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); MessageBox.Show(ex.Message); } }
Excel.PivotTable CreatePivotTable() { TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { Excel.Range destination = this.worksheet.get_Range(pivotTableAddress, defaultParameter); Excel.PivotTable pivotTable; pivotTable = Globals.ThisWorkbook.CreateSalesPivotTable(destination, generator.FullPath); // 调整新的数据透视表的属性 pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; // 在数据透视表中,添加所需的 // 行和列。 pivotTable.AddFields("Flavor", defaultParameter, defaultParameter, defaultParameter); Excel.PivotField soldField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.AverageOfUnitsSold, Excel.XlConsolidationFunction.xlAverage); // 在数据透视表中设置所需数据的视图。 // 格式“0.0”- 一个小数位。 soldField.NumberFormat = "0.0"; Excel.PivotField profitField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.StdDevOfUnitsSold, Excel.XlConsolidationFunction.xlStDev); // 在数据透视表中设置所需数据的视图。 // 格式“0.00”- 两个小数位。 profitField.NumberFormat = "0.00"; // 隐藏创建数据透视表时添加的两个浮动栏。 Globals.ThisWorkbook.ShowPivotTableFieldList = false; Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false; return pivotTable; } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; } finally { generator.Dispose(); } }
/// <summary> /// 将列表对象中的数据导出到一个文本文件中,并使用这些数据更新 /// 数据透视表。 /// </summary> internal void UpdateSalesPivotTable(Excel.PivotTable pivotTable) { bool screenUpdating = Globals.ThisWorkbook.Application.ScreenUpdating; Application.ScreenUpdating = false; TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { string fileDirectory = System.IO.Path.GetDirectoryName(generator.FullPath); string fileName = System.IO.Path.GetFileName(generator.FullPath); this.ShowPivotTableFieldList = false; pivotTable.SourceData = new string[] { String.Format( CultureInfo.CurrentUICulture, salesPivotTableConnectionTemplate, fileDirectory), String.Format( CultureInfo.CurrentUICulture, salesPivotTableQueryTemplate, fileName) }; this.Application.CommandBars["PivotTable"].Visible = false; } finally { generator.Dispose(); this.Application.ScreenUpdating = screenUpdating; } }