/// <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();
}
}
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);
// 调整新的数据透视表的属性
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;
}
}