public void EnsurePowerPivotDataIsLoaded2010()
{
var wb = _app.ActiveWorkbook;
PivotCaches pvtcaches = wb.PivotCaches();
var olapPivotCaches = from PivotCache pvtc in pvtcaches
let conn = pvtc.Connection.ToString()
where pvtc.OLAP &&
pvtc.CommandType == XlCmdType.xlCmdCube &&
((string)conn).Contains("Data Source=$Embedded$") &&
!pvtc.IsConnected
select pvtc;
if (olapPivotCaches.Count() == 0)
{
var pc = CreateHiddenPivotTable(wb); // automatically generate a hidden pivot table
var cache = new List <PivotCache>
{
pc
};
olapPivotCaches = cache;
}
foreach (PivotCache pvtc in olapPivotCaches)
{
pvtc.Refresh();
}
}
public ADOTabularConnection GetPowerPivotConnection()
{
PivotCache pc = null;
string connStr = "";
PivotCaches pvtcaches = _app.ActiveWorkbook.PivotCaches();
if (float.Parse(_app.Version) >= 15)
{
pc = (from PivotCache pvtc in pvtcaches
let conn = pvtc.Connection.ToString()
where pvtc.OLAP &&
pvtc.CommandType == XlCmdType.xlCmdCube &&
(int)pvtc.WorkbookConnection.Type == 7 // xl15Model
select pvtc).First();
connStr = (string)((dynamic)pc.WorkbookConnection).ModelConnection.ADOConnection.ConnectionString;
connStr = string.Format("{0};location={1}", connStr, _app.ActiveWorkbook.FullName);
// for connections to Excel 2013 or later we need to use the Excel version of ADOMDClient
return(new ADOTabularConnection(connStr, AdomdType.Excel));
}
else
{
pc = (from PivotCache pvtc in pvtcaches
let conn = pvtc.Connection.ToString()
where pvtc.OLAP &&
pvtc.CommandType == XlCmdType.xlCmdCube
//&& (int)pvtc.WorkbookConnection.Type == 7
select pvtc).First();
connStr = ((dynamic)pc.WorkbookConnection).OLEDBConnection.Connection.Replace("OLEDB;", "");
connStr = string.Format("{0};location={1}", connStr, _app.ActiveWorkbook.FullName);
// for connections to Excel 2010 we need to use the AnalysisServices version of ADOMDClient
return(new ADOTabularConnection(connStr, AdomdType.AnalysisServices));
}
}
// TODO
//public bool HasPowerPivotData()
//{
// return _xlHelper.HasPowerPivotData();
// }
public void EnsurePowerPivotDataIsLoaded()
{
PivotCaches pvtcaches = _app.ActiveWorkbook.PivotCaches();
if (pvtcaches.Count == 0)
{
return;
}
foreach (PivotCache pvtc in from PivotCache pvtc in pvtcaches
let conn = pvtc.Connection.ToString()
where pvtc.OLAP &&
pvtc.CommandType == XlCmdType.xlCmdCube &&
((string)conn).Contains("Data Source=$Embedded$") &&
!pvtc.IsConnected
select pvtc)
{
pvtc.Refresh();
}
}
//private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, XLWorksheet worksheet)
//{
// if (drawingsPart.WorksheetDrawing == null)
// drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
// var worksheetDrawing = drawingsPart.WorksheetDrawing;
// if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing")))
// worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
// if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")))
// worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
// foreach (var chart in worksheet.Charts.OrderBy(c => c.ZOrder).Select(c => c))
// {
// Xdr.TwoCellAnchor twoCellAnchor = new Xdr.TwoCellAnchor();
// worksheetDrawing.AppendChild(twoCellAnchor);
// if (chart.Anchor == XLDrawingAnchor.MoveAndSizeWithCells)
// twoCellAnchor.EditAs = Xdr.EditAsValues.TwoCell;
// else if (chart.Anchor == XLDrawingAnchor.MoveWithCells)
// twoCellAnchor.EditAs = Xdr.EditAsValues.OneCell;
// else
// twoCellAnchor.EditAs = Xdr.EditAsValues.Absolute;
// if (twoCellAnchor.FromMarker == null)
// twoCellAnchor.FromMarker = new Xdr.FromMarker();
// twoCellAnchor.FromMarker.RowId = new Xdr.RowId((chart.FirstRow - 1).ToString());
// twoCellAnchor.FromMarker.RowOffset = new Xdr.RowOffset(chart.FirstRowOffset.ToString());
// twoCellAnchor.FromMarker.ColumnId = new Xdr.ColumnId((chart.FirstColumn - 1).ToString());
// twoCellAnchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(chart.FirstColumnOffset.ToString());
// if (twoCellAnchor.ToMarker == null)
// twoCellAnchor.ToMarker = new Xdr.ToMarker();
// twoCellAnchor.ToMarker.RowId = new Xdr.RowId((chart.LastRow - 1).ToString());
// twoCellAnchor.ToMarker.RowOffset = new Xdr.RowOffset(chart.LastRowOffset.ToString());
// twoCellAnchor.ToMarker.ColumnId = new Xdr.ColumnId((chart.LastColumn - 1).ToString());
// twoCellAnchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(chart.LastColumnOffset.ToString());
// Xdr.GraphicFrame graphicFrame = new Xdr.GraphicFrame();
// twoCellAnchor.AppendChild(graphicFrame);
// if (graphicFrame.NonVisualGraphicFrameProperties == null)
// graphicFrame.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();
// if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties == null)
// graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = (UInt32)chart.Id, Name = chart.Name, Description = chart.Description, Hidden = chart.Hidden };
// if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties == null)
// graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties();
// if (graphicFrame.Transform == null)
// graphicFrame.Transform = new Xdr.Transform();
// if (chart.HorizontalFlip)
// graphicFrame.Transform.HorizontalFlip = true;
// else
// graphicFrame.Transform.HorizontalFlip = null;
// if (chart.VerticalFlip)
// graphicFrame.Transform.VerticalFlip = true;
// else
// graphicFrame.Transform.VerticalFlip = null;
// if (chart.Rotation != 0)
// graphicFrame.Transform.Rotation = chart.Rotation;
// else
// graphicFrame.Transform.Rotation = null;
// if (graphicFrame.Transform.Offset == null)
// graphicFrame.Transform.Offset = new A.Offset();
// graphicFrame.Transform.Offset.X = chart.OffsetX;
// graphicFrame.Transform.Offset.Y = chart.OffsetY;
// if (graphicFrame.Transform.Extents == null)
// graphicFrame.Transform.Extents = new A.Extents();
// graphicFrame.Transform.Extents.Cx = chart.ExtentLength;
// graphicFrame.Transform.Extents.Cy = chart.ExtentWidth;
// if (graphicFrame.Graphic == null)
// graphicFrame.Graphic = new A.Graphic();
// if (graphicFrame.Graphic.GraphicData == null)
// graphicFrame.Graphic.GraphicData = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" };
// if (!graphicFrame.Graphic.GraphicData.Elements<C.ChartReference>().Any())
// {
// C.ChartReference chartReference = new C.ChartReference() { Id = "rId" + chart.Id.ToStringLookup() };
// chartReference.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
// chartReference.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// graphicFrame.Graphic.GraphicData.AppendChild(chartReference);
// }
// if (!twoCellAnchor.Elements<Xdr.ClientData>().Any())
// twoCellAnchor.AppendChild(new Xdr.ClientData());
// }
//}
//private void GenerateChartPartContent(ChartPart chartPart, XLChart xlChart)
//{
// if (chartPart.ChartSpace == null)
// chartPart.ChartSpace = new C.ChartSpace();
// C.ChartSpace chartSpace = chartPart.ChartSpace;
// if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")))
// chartSpace.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
// if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")))
// chartSpace.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
// if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
// chartSpace.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// if (chartSpace.EditingLanguage == null)
// chartSpace.EditingLanguage = new C.EditingLanguage() { Val = CultureInfo.CurrentCulture.Name };
// else
// chartSpace.EditingLanguage.Val = CultureInfo.CurrentCulture.Name;
// C.Chart chart = new C.Chart();
// chartSpace.AppendChild(chart);
// if (chart.Title == null)
// chart.Title = new C.Title();
// if (chart.Title.Layout == null)
// chart.Title.Layout = new C.Layout();
// if (chart.View3D == null)
// chart.View3D = new C.View3D();
// if (chart.View3D.RightAngleAxes == null)
// chart.View3D.RightAngleAxes = new C.RightAngleAxes();
// chart.View3D.RightAngleAxes.Val = xlChart.RightAngleAxes;
// if (chart.PlotArea == null)
// chart.PlotArea = new C.PlotArea();
// if (chart.PlotArea.Layout == null)
// chart.PlotArea.Layout = new C.Layout();
// OpenXmlElement chartElement = GetChartElement(xlChart);
// chart.PlotArea.AppendChild(chartElement);
// C.CategoryAxis categoryAxis1 = new C.CategoryAxis();
// C.AxisId axisId4 = new C.AxisId() { Val = (UInt32Value)71429120U };
// C.Scaling scaling1 = new C.Scaling();
// C.Orientation orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax };
// scaling1.AppendChild(orientation1);
// C.AxisPosition axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom };
// C.TickLabelPosition tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
// C.CrossingAxis crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)71432064U };
// C.Crosses crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
// C.AutoLabeled autoLabeled1 = new C.AutoLabeled() { Val = true };
// C.LabelAlignment labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center };
// C.LabelOffset labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U };
// categoryAxis1.AppendChild(axisId4);
// categoryAxis1.AppendChild(scaling1);
// categoryAxis1.AppendChild(axisPosition1);
// categoryAxis1.AppendChild(tickLabelPosition1);
// categoryAxis1.AppendChild(crossingAxis1);
// categoryAxis1.AppendChild(crosses1);
// categoryAxis1.AppendChild(autoLabeled1);
// categoryAxis1.AppendChild(labelAlignment1);
// categoryAxis1.AppendChild(labelOffset1);
// C.ValueAxis valueAxis1 = new C.ValueAxis();
// C.AxisId axisId5 = new C.AxisId() { Val = (UInt32Value)71432064U };
// C.Scaling scaling2 = new C.Scaling();
// C.Orientation orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax };
// scaling2.AppendChild(orientation2);
// C.AxisPosition axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Left };
// C.MajorGridlines majorGridlines1 = new C.MajorGridlines();
// C.NumberingFormat numberingFormat1 = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true };
// C.TickLabelPosition tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
// C.CrossingAxis crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)71429120U };
// C.Crosses crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
// C.CrossBetween crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.Between };
// valueAxis1.AppendChild(axisId5);
// valueAxis1.AppendChild(scaling2);
// valueAxis1.AppendChild(axisPosition2);
// valueAxis1.AppendChild(majorGridlines1);
// valueAxis1.AppendChild(numberingFormat1);
// valueAxis1.AppendChild(tickLabelPosition2);
// valueAxis1.AppendChild(crossingAxis2);
// valueAxis1.AppendChild(crosses2);
// valueAxis1.AppendChild(crossBetween1);
// plotArea.AppendChild(bar3DChart1);
// plotArea.AppendChild(categoryAxis1);
// plotArea.AppendChild(valueAxis1);
// C.Legend legend1 = new C.Legend();
// C.LegendPosition legendPosition1 = new C.LegendPosition() { Val = C.LegendPositionValues.Right };
// C.Layout layout3 = new C.Layout();
// legend1.AppendChild(legendPosition1);
// legend1.AppendChild(layout3);
// C.PlotVisibleOnly plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true };
// chart.AppendChild(legend1);
// chart.AppendChild(plotVisibleOnly1);
// C.PrintSettings printSettings1 = new C.PrintSettings();
// C.HeaderFooter headerFooter1 = new C.HeaderFooter();
// C.PageMargins pageMargins4 = new C.PageMargins() { Left = 0.70000000000000018D, Right = 0.70000000000000018D, Top = 0.75000000000000022D, Bottom = 0.75000000000000022D, Header = 0.3000000000000001D, Footer = 0.3000000000000001D };
// C.PageSetup pageSetup1 = new C.PageSetup();
// printSettings1.AppendChild(headerFooter1);
// printSettings1.AppendChild(pageMargins4);
// printSettings1.AppendChild(pageSetup1);
// chartSpace.AppendChild(printSettings1);
//}
//private OpenXmlElement GetChartElement(XLChart xlChart)
//{
// if (xlChart.ChartTypeCategory == XLChartTypeCategory.Bar3D)
// return GetBar3DChart(xlChart);
// else
// return null;
//}
//private OpenXmlElement GetBar3DChart(XLChart xlChart)
//{
// C.Bar3DChart bar3DChart = new C.Bar3DChart();
// bar3DChart.BarDirection = new C.BarDirection() { Val = GetBarDirection(xlChart) };
// bar3DChart.BarGrouping = new C.BarGrouping() { Val = GetBarGrouping(xlChart) };
// C.BarChartSeries barChartSeries = new C.BarChartSeries();
// barChartSeries.Index = new C.Index() { Val = (UInt32Value)0U };
// barChartSeries.Order = new C.Order() { Val = (UInt32Value)0U };
// C.SeriesText seriesText1 = new C.SeriesText();
// C.StringReference stringReference1 = new C.StringReference();
// C.Formula formula1 = new C.Formula();
// formula1.Text = "Sheet1!$B$1";
// stringReference1.AppendChild(formula1);
// seriesText1.AppendChild(stringReference1);
// C.CategoryAxisData categoryAxisData1 = new C.CategoryAxisData();
// C.StringReference stringReference2 = new C.StringReference();
// C.Formula formula2 = new C.Formula();
// formula2.Text = "Sheet1!$A$2:$A$3";
// C.StringCache stringCache2 = new C.StringCache();
// C.PointCount pointCount2 = new C.PointCount() { Val = (UInt32Value)2U };
// C.StringPoint stringPoint2 = new C.StringPoint() { Index = (UInt32Value)0U };
// C.NumericValue numericValue2 = new C.NumericValue();
// numericValue2.Text = "A";
// stringPoint2.AppendChild(numericValue2);
// C.StringPoint stringPoint3 = new C.StringPoint() { Index = (UInt32Value)1U };
// C.NumericValue numericValue3 = new C.NumericValue();
// numericValue3.Text = "B";
// stringPoint3.AppendChild(numericValue3);
// stringCache2.AppendChild(pointCount2);
// stringCache2.AppendChild(stringPoint2);
// stringCache2.AppendChild(stringPoint3);
// stringReference2.AppendChild(formula2);
// stringReference2.AppendChild(stringCache2);
// categoryAxisData1.AppendChild(stringReference2);
// C.Values values1 = new C.Values();
// C.NumberReference numberReference1 = new C.NumberReference();
// C.Formula formula3 = new C.Formula();
// formula3.Text = "Sheet1!$B$2:$B$3";
// C.NumberingCache numberingCache1 = new C.NumberingCache();
// C.FormatCode formatCode1 = new C.FormatCode();
// formatCode1.Text = "General";
// C.PointCount pointCount3 = new C.PointCount() { Val = (UInt32Value)2U };
// C.NumericPoint numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)0U };
// C.NumericValue numericValue4 = new C.NumericValue();
// numericValue4.Text = "5";
// numericPoint1.AppendChild(numericValue4);
// C.NumericPoint numericPoint2 = new C.NumericPoint() { Index = (UInt32Value)1U };
// C.NumericValue numericValue5 = new C.NumericValue();
// numericValue5.Text = "10";
// numericPoint2.AppendChild(numericValue5);
// numberingCache1.AppendChild(formatCode1);
// numberingCache1.AppendChild(pointCount3);
// numberingCache1.AppendChild(numericPoint1);
// numberingCache1.AppendChild(numericPoint2);
// numberReference1.AppendChild(formula3);
// numberReference1.AppendChild(numberingCache1);
// values1.AppendChild(numberReference1);
// barChartSeries.AppendChild(index1);
// barChartSeries.AppendChild(order1);
// barChartSeries.AppendChild(seriesText1);
// barChartSeries.AppendChild(categoryAxisData1);
// barChartSeries.AppendChild(values1);
// C.Shape shape1 = new C.Shape() { Val = C.ShapeValues.Box };
// C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)71429120U };
// C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)71432064U };
// C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)0U };
// bar3DChart.AppendChild(barChartSeries);
// bar3DChart.AppendChild(shape1);
// bar3DChart.AppendChild(axisId1);
// bar3DChart.AppendChild(axisId2);
// bar3DChart.AppendChild(axisId3);
// return bar3DChart;
//}
//private C.BarGroupingValues GetBarGrouping(XLChart xlChart)
//{
// if (xlChart.BarGrouping == XLBarGrouping.Clustered)
// return C.BarGroupingValues.Clustered;
// else if (xlChart.BarGrouping == XLBarGrouping.Percent)
// return C.BarGroupingValues.PercentStacked;
// else if (xlChart.BarGrouping == XLBarGrouping.Stacked)
// return C.BarGroupingValues.Stacked;
// else
// return C.BarGroupingValues.Standard;
//}
//private C.BarDirectionValues GetBarDirection(XLChart xlChart)
//{
// if (xlChart.BarOrientation == XLBarOrientation.Vertical)
// return C.BarDirectionValues.Column;
// else
// return C.BarDirectionValues.Bar;
//}
//--
private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart,
XLWorksheet xlWorksheet,
SaveContext context)
{
foreach (var pt in xlWorksheet.PivotTables)
{
var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);
var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp);
GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);
var pivotCaches = new PivotCaches();
var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp};
pivotCaches.AppendChild(pivotCache);
workbookPart.Workbook.AppendChild(pivotCaches);
var pivotTablePart =
worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GeneratePivotTablePartContent(pivotTablePart, pt);
pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
}
public bool HasPowerPivotData()
{
Log.Debug("{Class} {method} {event}", "ExcelHelper", "HasPowerPivotData", "Start");
try
{
var wb = _app.ActiveWorkbook;
if (_app.ActiveWorkbook == null)
{
return(false);
}
if (IsExcel2013OrLater)
{
var conns = wb.Connections;
foreach (Microsoft.Office.Interop.Excel.WorkbookConnection c in conns)
{
if (c.Name == "ThisWorkbookDataModel")
{
Log.Debug("{Class} {method} {event}", "ExcelHelper", "HasPowerPivotData:true", "End (2013)");
return(true);
}
}
Log.Debug("{Class} {method} {event}", "ExcelHelper", "HasPowerPivotData:false", "End (2013)");
return(false);
}
// if Excel 2010
PivotCaches pvtcaches = wb.PivotCaches();
if (pvtcaches.Count == 0)
{
CreateHiddenPivotTable(wb); // create a hidden pivottable so we can "wake up" the data model
}
var ptc = (from PivotCache pvtc in pvtcaches
let conn = pvtc.Connection.ToString()
where pvtc.OLAP &&
pvtc.CommandType == XlCmdType.xlCmdCube &&
(((string)conn).IndexOf("Data Source=$Embedded$", StringComparison.InvariantCultureIgnoreCase) >= 0)
select pvtc).First();// Any();
/*
* //TODO - try creating a pivot cache or connection
* if (ptc == null)
* {
*
* ptc = pvtcaches.Create(XlPivotTableSourceType.xlExternal);
* ptc.CommandType = XlCmdType.xlCmdCube;
*
* ptc.Connection = new AdomdConnection( )
* }
*/
if (ptc != null)
{
ptc.Refresh();
Log.Debug("{Class} {method} {event}", "ExcelHelper", "HasPowerPivotData", "End (2010) - true");
return(true);
}
Log.Debug("{Class} {method} {event}", "ExcelHelper", "HasPowerPivotData", "End (2010) - false");
return(false);
}
catch (Exception ex)
{
Log.Error("{Class} {method} {exception} {stacktrace}", "ExcelHelper", "HasPowerPivotData", ex.Message, ex.StackTrace);
throw;
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache() { CacheId = (UInt32Value)2U, Id = "rId4" };
pivotCaches1.Append(pivotCache1);
workbook1.Append(sheets1);
workbook1.Append(pivotCaches1);
workbookPart1.Workbook = workbook1;
}
static void Main(string[] args)
{
string
currentDirectory = System.IO.Directory.GetCurrentDirectory();
currentDirectory = currentDirectory.Substring(0, currentDirectory.LastIndexOf("bin", currentDirectory.Length - 1));
Application excel = null;
Workbooks workbooks = null;
Workbook workbook = null;
PivotCaches pivotCaches = null;
try
{
excel = new Application();
excel.Visible = true;
excel.DisplayAlerts = false;
excel.UserControl = false;
Console.WriteLine(GetExcelVersion(excel));
Console.WriteLine(_GetExcelVersion_(excel));
workbooks = excel.Workbooks;
workbook = workbooks.Add(currentDirectory + "SaleBuyPoints.xls");
pivotCaches = workbook.PivotCaches();
/*
* foreach (WorkbookConnection connection in workbook.Connections)
* {
* if (connection.Type == XlConnectionType.xlConnectionTypeODBC)
* connection.ODBCConnection.Connection = "ODBC;DRIVER=SQL Server;SERVER=(local);UID=chicago_reports;PWD=poiUYT56;APP=Microsoft Office 2003;WSID=R-EXEL;DATABASE=chicago_2_11_0";
* else if (connection.Type == XlConnectionType.xlConnectionTypeOLEDB)
* connection.OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Password=poiUYT56;Persist Security Info=True;User ID=chicago_reports;Data Source=i-nozhenko;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=I-NOZHENKO;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=chicago_2_11_0";
* }
*/
foreach (PivotCache pivotCache in pivotCaches)
{
try
{
pivotCache.SavePassword = true;
//pivotCache.Connection = "ODBC;DRIVER=SQL Server;SERVER=(local);UID=chicago_reports;PWD=poiUYT56;APP=Microsoft Office 2003;WSID=R-EXEL;DATABASE=chicago_2_11_0";
pivotCache.Connection = "OLEDB;Provider=SQLOLEDB.1;Password=poiUYT56;Persist Security Info=True;User ID=chicago_reports;Data Source=i-nozhenko;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=I-NOZHENKO;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=chicago_2_11_0";
pivotCache.CommandText = "EXEC Reports.report_xls_SalesBuyPoints @paramStart = '20111101', @paramEnd = '20111130', @enDistributors = '', @idPosition = '', @Goods_Type = 'True', @Unit_Type = 'False', @idPhysicalPerson = '0', @guid = '7f2c17c072a04b3492966e5896722590', @need_empty=0";
//pivotCache.CommandText = new string[] {"EXEC Reports.report_xls_SalesBuyPoints @paramStart = '20111101', @paramEnd = '20111130', @enDistributors = '', @idPosition = '', @Goods_Type = 'True', @Unit_Type = 'False', @id", "PhysicalPerson = '0', @guid = '7f2c17c072a04b3492966e5896722590', @need_empty=0" };
pivotCache.SavePassword = false;
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
finally
{
if (pivotCache != null)
{
Marshal.ReleaseComObject(pivotCache);
}
}
}
}
finally
{
if (pivotCaches != null)
{
Marshal.ReleaseComObject(pivotCaches);
pivotCaches = null;
}
if (workbook != null)
{
Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
excel = null;
}
GC.GetTotalMemory(true);
}
}
public override string Export(string json)
{
string fileName = "";
try
{
if (string.IsNullOrEmpty(json))
{
throw new KMJXCException("没有销售数据，不能导出");
}
JArray jObject = JArray.Parse(json);
if (jObject == null || jObject.Count() == 0)
{
throw new KMJXCException("没有销售数据，不能导出");
}
Worksheet sheet = (Worksheet)this.WorkBook.ActiveSheet;
sheet.Name = "销售报表";
int startRow = 2;
object[,] os = new object[jObject.Count, 6];
for (int i = 0; i < jObject.Count(); i++)
{
JObject obj = (JObject)jObject[i];
string productName = obj["ProductName"].ToString();
string propName = obj["PropName"].ToString();
string shopName = obj["ShopName"].ToString();
string month = obj["Month"].ToString();
string quantity = obj["Quantity"].ToString();
string amount = obj["Amount"].ToString();
os[i, 0] = productName;
os[i, 1] = propName;
os[i, 2] = shopName;
os[i, 3] = month;
os[i, 4] = quantity;
os[i, 5] = amount;
}
Range range1 = sheet.Cells[startRow, 1];
Range range2 = sheet.Cells[startRow + jObject.Count - 1, 6];
Range range = sheet.get_Range(range1, range2);
range.Value2 = os;
Worksheet pivotTableSheet = (Worksheet)this.WorkBook.Worksheets[2];
pivotTableSheet.Name = "销售透视表";
PivotCaches pch = WorkBook.PivotCaches();
sheet.Activate();
pch.Add(XlPivotTableSourceType.xlDatabase, "'" + sheet.Name + "'!A1:'" + sheet.Name + "'!F" + (jObject.Count() + 1)).CreatePivotTable(pivotTableSheet.Cells[4, 1], "PivTbl_1", Type.Missing, Type.Missing);
PivotTable pvt = pivotTableSheet.PivotTables("PivTbl_1") as PivotTable;
pvt.Format(XlPivotFormatType.xlTable1);
pvt.TableStyle2 = "PivotStyleLight16";
pvt.InGridDropZones = true;
foreach (PivotField pf in pvt.PivotFields() as PivotFields)
{
pf.ShowDetail = false;
}
PivotField productField = (PivotField)pvt.PivotFields("产品");
productField.Orientation = XlPivotFieldOrientation.xlRowField;
productField.set_Subtotals(1, false);
PivotField propField = (PivotField)pvt.PivotFields("属性");
propField.Orientation = XlPivotFieldOrientation.xlRowField;
propField.set_Subtotals(1, false);
PivotField shopField = (PivotField)pvt.PivotFields("店铺");
shopField.Orientation = XlPivotFieldOrientation.xlRowField;
shopField.set_Subtotals(1, false);
PivotField monthField = (PivotField)pvt.PivotFields("年月");
monthField.Orientation = XlPivotFieldOrientation.xlRowField;
monthField.set_Subtotals(1, false);
pvt.AddDataField(pvt.PivotFields(5), "销量", XlConsolidationFunction.xlSum);
pvt.AddDataField(pvt.PivotFields(6), "销售额", XlConsolidationFunction.xlSum);
((PivotField)pvt.DataFields["销量"]).NumberFormat = "#,##0";
((PivotField)pvt.DataFields["销售额"]).NumberFormat = "#,##0";
pivotTableSheet.Activate();
this.WorkBook.Saved = true;
this.WorkBook.SaveCopyAs(this.ReportFilePath);
this.WorkBook.Close();
}
catch (Exception ex)
{
throw ex;
}
return(fileName);
}
static void makeGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.Worksheet chartSheet = null;
Excel.Range range = null;
Excel.Range dataR = null;
int rowC = 0;
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "table";
chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
chartSheet.Name = "graph";
Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing);
dataR = dataSheet.UsedRange;
rowC = dataR.Rows.Count;
range = newSheet.get_Range("A1");
PivotCaches pCs = workbook.PivotCaches();
PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing);
PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1");
PivotField fA = pT.PivotFields("Time");
PivotField fB = pT.PivotFields("Command");
fA.Orientation = XlPivotFieldOrientation.xlRowField;
fA.Position = 1;
fB.Orientation = XlPivotFieldOrientation.xlColumnField;
fB.Position = 1;
pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum);
ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450);
Chart chartP = pChart.Chart;
chartP.SetSourceData(pT.TableRange1, Type.Missing);
chartP.ChartType = XlChartType.xlLine;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
using (StreamWriter outfile = File.AppendText("output.txt")) {
outfile.WriteLine("Did have issues interacting with Excel on " + file);
}
} finally {
/*Excel.Application excelApp = null;
* Excel.Workbook workbook = null;
* Excel.Sheets sheets = null;
* Excel.Worksheet dataSheet = null;
* Excel.Worksheet newSheet = null;
* Excel.Worksheet chartSheet = null;
* Excel.Range range = null;
* Excel.Range dataR = null;*/
releaseObject(dataR);
releaseObject(range);
releaseObject(chartSheet);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" };
Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" };
Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" };
Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" };
Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" };
Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" };
Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" };
Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" };
Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" };
Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
sheets1.Append(sheet3);
sheets1.Append(sheet4);
sheets1.Append(sheet5);
sheets1.Append(sheet6);
sheets1.Append(sheet7);
sheets1.Append(sheet8);
sheets1.Append(sheet9);
sheets1.Append(sheet10);
sheets1.Append(sheet11);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" };
definedName3.Text = "#N/A";
DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" };
definedName4.Text = "#N/A";
DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" };
definedName5.Text = "#N/A";
DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" };
definedName6.Text = "#N/A";
DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" };
definedName7.Text = "#N/A";
DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" };
definedName8.Text = "#N/A";
DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" };
definedName9.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
definedNames1.Append(definedName4);
definedNames1.Append(definedName5);
definedNames1.Append(definedName6);
definedNames1.Append(definedName7);
definedNames1.Append(definedName8);
definedNames1.Append(definedName9);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" };
PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" };
pivotCaches1.Append(pivotCache1);
pivotCaches1.Append(pivotCache2);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineCacheReferences timelineCacheReferences1 = new X15.TimelineCacheReferences();
X15.TimelineCacheReference timelineCacheReference1 = new X15.TimelineCacheReference(){ Id = "rId14" };
X15.TimelineCacheReference timelineCacheReference2 = new X15.TimelineCacheReference(){ Id = "rId15" };
X15.TimelineCacheReference timelineCacheReference3 = new X15.TimelineCacheReference(){ Id = "rId16" };
X15.TimelineCacheReference timelineCacheReference4 = new X15.TimelineCacheReference(){ Id = "rId17" };
X15.TimelineCacheReference timelineCacheReference5 = new X15.TimelineCacheReference(){ Id = "rId18" };
X15.TimelineCacheReference timelineCacheReference6 = new X15.TimelineCacheReference(){ Id = "rId19" };
X15.TimelineCacheReference timelineCacheReference7 = new X15.TimelineCacheReference(){ Id = "rId20" };
X15.TimelineCacheReference timelineCacheReference8 = new X15.TimelineCacheReference(){ Id = "rId21" };
X15.TimelineCacheReference timelineCacheReference9 = new X15.TimelineCacheReference(){ Id = "rId22" };
timelineCacheReferences1.Append(timelineCacheReference1);
timelineCacheReferences1.Append(timelineCacheReference2);
timelineCacheReferences1.Append(timelineCacheReference3);
timelineCacheReferences1.Append(timelineCacheReference4);
timelineCacheReferences1.Append(timelineCacheReference5);
timelineCacheReferences1.Append(timelineCacheReference6);
timelineCacheReferences1.Append(timelineCacheReference7);
timelineCacheReferences1.Append(timelineCacheReference8);
timelineCacheReferences1.Append(timelineCacheReference9);
workbookExtension2.Append(timelineCacheReferences1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName1.Text = "Sheet1!$B$2:$I$13";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" };
pivotCaches1.Append(pivotCache1);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.DataModel dataModel1 = new X15.DataModel();
X15.ModelTables modelTables1 = new X15.ModelTables();
X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" };
modelTables1.Append(modelTable1);
dataModel1.Append(modelTables1);
workbookExtension1.Append(dataModel1);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" };
OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
workbookExtension2.Append(openXmlUnknownElement1);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
