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

                // 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();
            }
        }
Exemplo n.º 3
0
        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();
            }
        }
Exemplo n.º 4
0
        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();
            }
        }
Exemplo n.º 5
0
        /// <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;
            }
        }