public static void WriteDataInExcelFile(ResultTable results, Stream stream)
{
if (results == null)
{
throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartByName(ExcelMessage.Data.NiceToString());
CellBuilder cb = PlainExcelGenerator.CellBuilder;
SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().SingleEx();
List <ColumnData> columnEquivalences = GetColumnsEquivalences(document, sheetData, results);
UInt32Value headerStyleIndex = worksheetPart.Worksheet.FindCell("A1").StyleIndex;
//Clear sheetData from the template sample data
sheetData.InnerXml = "";
sheetData.Append(new Sequence <Row>()
{
(from columnData in columnEquivalences
select cb.Cell(columnData.Column.Column.DisplayName, headerStyleIndex)).ToRow(),
from r in results.Rows
select(from columnData in columnEquivalences
select cb.Cell(r[columnData.Column], cb.GetTemplateCell(columnData.Column.Column.Type), columnData.StyleIndex)).ToRow()
}.Cast <OpenXmlElement>());
var pivotTableParts = workbookPart.PivotTableCacheDefinitionParts
.Where(ptpart => ptpart.PivotCacheDefinition.Descendants <WorksheetSource>()
.Any(wss => wss.Sheet.Value == ExcelMessage.Data.NiceToString()));
foreach (PivotTableCacheDefinitionPart ptpart in pivotTableParts)
{
PivotCacheDefinition pcd = ptpart.PivotCacheDefinition;
WorksheetSource wss = pcd.Descendants <WorksheetSource>().FirstEx();
wss.Reference.Value = "A1:" + GetExcelColumn(columnEquivalences.Count(ce => !ce.IsNew) - 1) + (results.Rows.Count() + 1).ToString();
pcd.RefreshOnLoad = true;
pcd.SaveData = false;
pcd.Save();
}
workbookPart.Workbook.Save();
document.Close();
}
}
/// <summary>
/// Deletes the A work sheet.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="sheetToDelete">The sheet to delete.</param>
public void ClearWorkSheetData(string fileName, string sheetToClear)
{
string Sheetid = "";
//Open the workbook
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart wbPart = document.WorkbookPart;
// Get the pivot Table Parts
IEnumerable <PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary <PivotTableCacheDefinitionPart, string>();
foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
{
PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
//Check if this CacheSource is linked to SheetToDelete
var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToClear);
if (pvtCahce.Count() > 0)
{
pvtTableCacheDefinationPart.Add(Item, Item.ToString());
}
}
foreach (var Item in pvtTableCacheDefinationPart)
{
wbPart.DeletePart(Item.Key);
}
//Get the SheetToDelete from workbook.xml
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToClear).FirstOrDefault();
if (theSheet == null)
{
// The specified sheet doesn't exist.
}
//Store the SheetID for the reference
Sheetid = theSheet.SheetId;
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
for (int childIndex = 1; childIndex < sheetData.ChildElements.Count; childIndex++)
{
sheetData.RemoveChild(sheetData.ChildElements[childIndex]);
}
IEnumerable <Row> rows = sheetData.Descendants <Row>();
List <Row> rowsList = rows.ToList();
//rowsList.RemoveRange(1, rowsList.Count - 1);
// Save the workbook.
wbPart.Workbook.Save();
}
}
// Generates content of pivotTableCacheDefinitionPart1.
private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1)
{
PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition() { Id = "rId1", RefreshedBy = "Author", RefreshedDate = 40905.65532824074D, CreatedVersion = 4, RefreshedVersion = 4, MinRefreshableVersion = 3, RecordCount = (UInt32Value)3U };
CacheSource cacheSource1 = new CacheSource() { Type = SourceValues.Worksheet };
WorksheetSource worksheetSource1 = new WorksheetSource() { Reference = "A1:B4", Sheet = "Sheet2" };
cacheSource1.Append(worksheetSource1);
CacheFields cacheFields1 = new CacheFields() { Count = (UInt32Value)2U };
CacheField cacheField1 = new CacheField() { Name = "id", NumberFormatId = (UInt32Value)0U };
SharedItems sharedItems1 = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 1D, MaxValue = 3D, Count = (UInt32Value)3U };
NumberItem numberItem1 = new NumberItem() { Val = 1D };
NumberItem numberItem2 = new NumberItem() { Val = 2D };
NumberItem numberItem3 = new NumberItem() { Val = 3D };
sharedItems1.Append(numberItem1);
sharedItems1.Append(numberItem2);
sharedItems1.Append(numberItem3);
cacheField1.Append(sharedItems1);
CacheField cacheField2 = new CacheField() { Name = "score", NumberFormatId = (UInt32Value)0U };
SharedItems sharedItems2 = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 100D, MaxValue = 132D };
cacheField2.Append(sharedItems2);
cacheFields1.Append(cacheField1);
cacheFields1.Append(cacheField2);
PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList1 = new PivotCacheDefinitionExtensionList();
pivotCacheDefinition1.Append(cacheSource1);
pivotCacheDefinition1.Append(cacheFields1);
pivotCacheDefinition1.Append(pivotCacheDefinitionExtensionList1);
pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;
}
// Generates content of pivotTableCacheDefinitionPart
private static void GeneratePivotTableCacheDefinitionPartContent(
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt)
{
var source = pt.SourceRange;
var pivotCacheDefinition = new PivotCacheDefinition
{
Id = "rId1",
SaveData = pt.SaveSourceData,
RefreshOnLoad = true //pt.RefreshDataOnOpen
};
if (pt.ItemsToRetainPerField == XLItemsToRetain.None)
pivotCacheDefinition.MissingItemsLimit = 0U;
else if (pt.ItemsToRetainPerField == XLItemsToRetain.Max)
pivotCacheDefinition.MissingItemsLimit = XLHelper.MaxRowNumber;
pivotCacheDefinition.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var cacheSource = new CacheSource {Type = SourceValues.Worksheet};
cacheSource.AppendChild(new WorksheetSource {Name = source.ToString()});
var cacheFields = new CacheFields();
foreach (var c in source.Columns())
{
var columnNumber = c.ColumnNumber();
var columnName = c.FirstCell().Value.ToString();
var xlpf = pt.Fields.Add(columnName);
var field =
pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName);
if (field != null)
{
xlpf.CustomName = field.CustomName;
xlpf.Subtotals.AddRange(field.Subtotals);
}
var sharedItems = new SharedItems();
var onlyNumbers =
!source.Cells().Any(
cell =>
cell.Address.ColumnNumber == columnNumber &&
cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.DataType != XLCellValues.Number);
if (onlyNumbers)
{
sharedItems = new SharedItems
{ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true};
}
else
{
foreach (var cellValue in source.Cells().Where(cell =>
cell.Address.ColumnNumber == columnNumber &&
cell.Address.RowNumber >
source.FirstRow().RowNumber()).Select(
cell => cell.Value.ToString())
.Where(cellValue => !xlpf.SharedStrings.Contains(cellValue)))
{
xlpf.SharedStrings.Add(cellValue);
}
foreach (var li in xlpf.SharedStrings)
{
sharedItems.AppendChild(new StringItem {Val = li});
}
}
var cacheField = new CacheField {Name = xlpf.SourceName};
cacheField.AppendChild(sharedItems);
cacheFields.AppendChild(cacheField);
}
pivotCacheDefinition.AppendChild(cacheSource);
pivotCacheDefinition.AppendChild(cacheFields);
pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition;
var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart<PivotTableCacheRecordsPart>("rId1");
var pivotCacheRecords = new PivotCacheRecords();
pivotCacheRecords.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords;
}
internal PivotCacheDefinition ToPivotCacheDefinition()
{
PivotCacheDefinition pcd = new PivotCacheDefinition();
if (this.Id != null && this.Id.Length > 0) pcd.Id = this.Id;
if (this.Invalid != false) pcd.Invalid = this.Invalid;
if (this.SaveData != true) pcd.SaveData = this.SaveData;
if (this.RefreshOnLoad != false) pcd.RefreshOnLoad = this.RefreshOnLoad;
if (this.OptimizeMemory != false) pcd.OptimizeMemory = this.OptimizeMemory;
if (this.EnableRefresh != true) pcd.EnableRefresh = this.EnableRefresh;
if (this.RefreshedBy != null && this.RefreshedBy.Length > 0) pcd.RefreshedBy = this.RefreshedBy;
if (this.RefreshedDate != null) pcd.RefreshedDate = this.RefreshedDate.Value;
if (this.BackgroundQuery != false) pcd.BackgroundQuery = this.BackgroundQuery;
if (this.MissingItemsLimit != null) pcd.MissingItemsLimit = this.MissingItemsLimit.Value;
if (this.CreatedVersion != 0) pcd.CreatedVersion = this.CreatedVersion;
if (this.RefreshedVersion != 0) pcd.RefreshedVersion = this.RefreshedVersion;
if (this.MinRefreshableVersion != 0) pcd.MinRefreshableVersion = this.MinRefreshableVersion;
if (this.RecordCount != null) pcd.RecordCount = this.RecordCount.Value;
if (this.UpgradeOnRefresh != false) pcd.UpgradeOnRefresh = this.UpgradeOnRefresh;
if (this.IsTupleCache != false) pcd.IsTupleCache = this.IsTupleCache;
if (this.SupportSubquery != false) pcd.SupportSubquery = this.SupportSubquery;
if (this.SupportAdvancedDrill != false) pcd.SupportAdvancedDrill = this.SupportAdvancedDrill;
pcd.CacheSource = this.CacheSource.ToCacheSource();
pcd.CacheFields = new CacheFields() { Count = (uint)this.CacheFields.Count };
foreach (SLCacheField cf in this.CacheFields)
{
pcd.CacheFields.Append(cf.ToCacheField());
}
if (this.CacheHierarchies.Count > 0)
{
pcd.CacheHierarchies = new CacheHierarchies() { Count = (uint)this.CacheHierarchies.Count };
foreach (SLCacheHierarchy ch in this.CacheHierarchies)
{
pcd.CacheHierarchies.Append(ch.ToCacheHierarchy());
}
}
if (this.Kpis.Count > 0)
{
pcd.Kpis = new Kpis() { Count = (uint)this.Kpis.Count };
foreach (SLKpi k in this.Kpis)
{
pcd.Kpis.Append(k.ToKpi());
}
}
if (this.HasTupleCache) pcd.TupleCache = this.TupleCache.ToTupleCache();
if (this.CalculatedItems.Count > 0)
{
pcd.CalculatedItems = new CalculatedItems() { Count = (uint)this.CalculatedItems.Count };
foreach (SLCalculatedItem ci in this.CalculatedItems)
{
pcd.CalculatedItems.Append(ci.ToCalculatedItem());
}
}
if (this.CalculatedMembers.Count > 0)
{
pcd.CalculatedMembers = new CalculatedMembers() { Count = (uint)this.CalculatedMembers.Count };
foreach (SLCalculatedMember cm in this.CalculatedMembers)
{
pcd.CalculatedMembers.Append(cm.ToCalculatedMember());
}
}
if (this.Dimensions.Count > 0)
{
pcd.Dimensions = new Dimensions() { Count = (uint)this.Dimensions.Count };
foreach (SLDimension d in this.Dimensions)
{
pcd.Dimensions.Append(d.ToDimension());
}
}
if (this.MeasureGroups.Count > 0)
{
pcd.MeasureGroups = new MeasureGroups() { Count = (uint)this.MeasureGroups.Count };
foreach (SLMeasureGroup mg in this.MeasureGroups)
{
pcd.MeasureGroups.Append(mg.ToMeasureGroup());
}
}
if (this.Maps.Count > 0)
{
pcd.Maps = new Maps() { Count = (uint)this.Maps.Count };
foreach (SLMeasureDimensionMap mdm in this.Maps)
{
pcd.Maps.Append(mdm.ToMeasureDimensionMap());
}
}
return pcd;
}
internal void FromPivotCacheDefinition(PivotCacheDefinition pcd)
{
this.SetAllNull();
if (pcd.Id != null) this.Id = pcd.Id.Value;
if (pcd.Invalid != null) this.Invalid = pcd.Invalid.Value;
if (pcd.SaveData != null) this.SaveData = pcd.SaveData.Value;
if (pcd.RefreshOnLoad != null) this.RefreshOnLoad = pcd.RefreshOnLoad.Value;
if (pcd.OptimizeMemory != null) this.OptimizeMemory = pcd.OptimizeMemory.Value;
if (pcd.EnableRefresh != null) this.EnableRefresh = pcd.EnableRefresh.Value;
if (pcd.RefreshedBy != null) this.RefreshedBy = pcd.RefreshedBy.Value;
if (pcd.RefreshedDate != null) this.RefreshedDate = pcd.RefreshedDate.Value;
if (pcd.BackgroundQuery != null) this.BackgroundQuery = pcd.BackgroundQuery.Value;
if (pcd.MissingItemsLimit != null) this.MissingItemsLimit = pcd.MissingItemsLimit.Value;
if (pcd.CreatedVersion != null) this.CreatedVersion = pcd.CreatedVersion.Value;
if (pcd.RefreshedVersion != null) this.RefreshedVersion = pcd.RefreshedVersion.Value;
if (pcd.MinRefreshableVersion != null) this.MinRefreshableVersion = pcd.MinRefreshableVersion.Value;
if (pcd.RecordCount != null) this.RecordCount = pcd.RecordCount.Value;
if (pcd.UpgradeOnRefresh != null) this.UpgradeOnRefresh = pcd.UpgradeOnRefresh.Value;
if (pcd.IsTupleCache != null) this.IsTupleCache = pcd.IsTupleCache.Value;
if (pcd.SupportSubquery != null) this.SupportSubquery = pcd.SupportSubquery.Value;
if (pcd.SupportAdvancedDrill != null) this.SupportAdvancedDrill = pcd.SupportAdvancedDrill.Value;
if (pcd.CacheSource != null) this.CacheSource.FromCacheSource(pcd.CacheSource);
// doing one by one because it's bloody hindering awkward complicated.
if (pcd.CacheFields != null)
{
SLCacheField cf;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CacheFields))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CacheField))
{
cf = new SLCacheField();
cf.FromCacheField((CacheField)oxr.LoadCurrentElement());
this.CacheFields.Add(cf);
}
}
}
}
if (pcd.CacheHierarchies != null)
{
SLCacheHierarchy ch;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CacheHierarchies))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CacheHierarchy))
{
ch = new SLCacheHierarchy();
ch.FromCacheHierarchy((CacheHierarchy)oxr.LoadCurrentElement());
this.CacheHierarchies.Add(ch);
}
}
}
}
if (pcd.Kpis != null)
{
SLKpi k;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.Kpis))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Kpi))
{
k = new SLKpi();
k.FromKpi((Kpi)oxr.LoadCurrentElement());
this.Kpis.Add(k);
}
}
}
}
if (pcd.TupleCache != null)
{
this.TupleCache.FromTupleCache(pcd.TupleCache);
this.HasTupleCache = true;
}
if (pcd.CalculatedItems != null)
{
SLCalculatedItem ci;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CalculatedItems))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculatedItem))
{
ci = new SLCalculatedItem();
ci.FromCalculatedItem((CalculatedItem)oxr.LoadCurrentElement());
this.CalculatedItems.Add(ci);
}
}
}
}
if (pcd.CalculatedMembers != null)
{
SLCalculatedMember cm;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CalculatedMembers))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculatedMember))
{
cm = new SLCalculatedMember();
cm.FromCalculatedMember((CalculatedMember)oxr.LoadCurrentElement());
this.CalculatedMembers.Add(cm);
}
}
}
}
if (pcd.Dimensions != null)
{
SLDimension d;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.Dimensions))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Dimension))
{
d = new SLDimension();
d.FromDimension((Dimension)oxr.LoadCurrentElement());
this.Dimensions.Add(d);
}
}
}
}
if (pcd.MeasureGroups != null)
{
SLMeasureGroup mg;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.MeasureGroups))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(MeasureGroup))
{
mg = new SLMeasureGroup();
mg.FromMeasureGroup((MeasureGroup)oxr.LoadCurrentElement());
this.MeasureGroups.Add(mg);
}
}
}
}
if (pcd.Maps != null)
{
SLMeasureDimensionMap mdm;
using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.Maps))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(MeasureDimensionMap))
{
mdm = new SLMeasureDimensionMap();
mdm.FromMeasureDimensionMap((MeasureDimensionMap)oxr.LoadCurrentElement());
this.Maps.Add(mdm);
}
}
}
}
}
private static void DeleteAWorkSheet(SpreadsheetDocument document, string sheetToDelete)
{
WorkbookPart wbPart = document.WorkbookPart;
// Get the pivot Table Parts
IEnumerable <PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart =
new Dictionary <PivotTableCacheDefinitionPart, string>();
foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
{
PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
//Check if this CacheSource is linked to SheetToDelete
var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
if (pvtCahce.Count() > 0)
{
pvtTableCacheDefinationPart.Add(Item, Item.ToString());
}
}
foreach (var Item in pvtTableCacheDefinationPart)
{
wbPart.DeletePart(Item.Key);
}
//Get the SheetToDelete from workbook.xml
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
if (theSheet == null)
{
// The specified sheet doesn't exist.
}
//Store the SheetID for the reference
var Sheetid = theSheet.SheetId;
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
theSheet.Remove();
// Delete the worksheet part.
wbPart.DeletePart(worksheetPart);
//Get the DefinedNames
var definedNames = wbPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
if (definedNames != null)
{
foreach (DefinedName Item in definedNames)
{
// This condition checks to delete only those names which are part of Sheet in question
if (Item.Text.Contains(sheetToDelete + "!"))
{
Item.Remove();
}
}
}
// Get the CalculationChainPart
//Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
//workbook whose value is calculated from any formula
CalculationChainPart calChainPart;
calChainPart = wbPart.CalculationChainPart;
if (calChainPart != null)
{
var calChainEntries =
calChainPart.CalculationChain.Descendants <CalculationCell>().Where(c => c.SheetId.ToString() == Sheetid);
foreach (CalculationCell Item in calChainEntries)
{
Item.Remove();
}
if (calChainPart.CalculationChain.Count() == 0)
{
wbPart.DeletePart(calChainPart);
}
}
}
/// <summary>
/// Deletes worksheet and its dependencies.
/// </summary>
/// <param name="fileName">Workbook to delete sheet from.</param>
/// <param name="sheetToDelete">Worksheetto delete.</param>
/// <remarks>Code taken and modified from <a href="https://blogs.msdn.microsoft.com/vsod/2010/02/05/how-to-delete-a-worksheet-from-excel-using-open-xml-sdk-2-0/">Ankush Bhatia</a>.</remarks>
private void DeleteWorkSheet(string fileName, string sheetToDelete)
{
string Sheetid = string.Empty;
//Open the workbook
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart wbPart = document.WorkbookPart;
// Get the pivot Table Parts
IEnumerable <PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary <PivotTableCacheDefinitionPart, string>();
foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
{
PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
//Check if this CacheSource is linked to SheetToDelete
var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
if (pvtCahce.Count() > 0)
{
pvtTableCacheDefinationPart.Add(Item, Item.ToString());
}
}
foreach (var Item in pvtTableCacheDefinationPart)
{
wbPart.DeletePart(Item.Key);
}
//Get the SheetToDelete from workbook.xml
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
if (theSheet == null)
{
return;
}
//Store the SheetID for the reference
Sheetid = theSheet.SheetId;
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
theSheet.Remove();
// Delete the worksheet part.
wbPart.DeletePart(worksheetPart);
//Get the DefinedNames
var definedNames = wbPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
if (definedNames != null)
{
List <DefinedName> defNamesToDelete = new List <DefinedName>();
foreach (DefinedName Item in definedNames)
{
// This condition checks to delete only those names which are part of Sheet in question
if (Item.Text.Contains(sheetToDelete + "!"))
{
defNamesToDelete.Add(Item);
}
}
foreach (DefinedName Item in defNamesToDelete)
{
Item.Remove();
}
}
// Get the CalculationChainPart
//Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
//workbook whose value is calculated from any formula
CalculationChainPart calChainPart;
calChainPart = wbPart.CalculationChainPart;
if (calChainPart != null)
{
var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().Where(c => c.SheetId == Sheetid);
List <CalculationCell> calcsToDelete = new List <CalculationCell>();
foreach (CalculationCell Item in calChainEntries)
{
calcsToDelete.Add(Item);
}
foreach (CalculationCell Item in calcsToDelete)
{
Item.Remove();
}
if (calChainPart.CalculationChain.Count() == 0)
{
wbPart.DeletePart(calChainPart);
}
}
// force recalculation if any
var calcProps = document.WorkbookPart.Workbook.Elements <CalculationProperties>().FirstOrDefault();
if (calcProps != null)
{
document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
}
} // using autosaves
}
internal PivotCacheDefinition ToPivotCacheDefinition()
{
var pcd = new PivotCacheDefinition();
if ((Id != null) && (Id.Length > 0))
{
pcd.Id = Id;
}
if (Invalid)
{
pcd.Invalid = Invalid;
}
if (SaveData != true)
{
pcd.SaveData = SaveData;
}
if (RefreshOnLoad)
{
pcd.RefreshOnLoad = RefreshOnLoad;
}
if (OptimizeMemory)
{
pcd.OptimizeMemory = OptimizeMemory;
}
if (EnableRefresh != true)
{
pcd.EnableRefresh = EnableRefresh;
}
if ((RefreshedBy != null) && (RefreshedBy.Length > 0))
{
pcd.RefreshedBy = RefreshedBy;
}
if (RefreshedDate != null)
{
pcd.RefreshedDate = RefreshedDate.Value;
}
if (BackgroundQuery)
{
pcd.BackgroundQuery = BackgroundQuery;
}
if (MissingItemsLimit != null)
{
pcd.MissingItemsLimit = MissingItemsLimit.Value;
}
if (CreatedVersion != 0)
{
pcd.CreatedVersion = CreatedVersion;
}
if (RefreshedVersion != 0)
{
pcd.RefreshedVersion = RefreshedVersion;
}
if (MinRefreshableVersion != 0)
{
pcd.MinRefreshableVersion = MinRefreshableVersion;
}
if (RecordCount != null)
{
pcd.RecordCount = RecordCount.Value;
}
if (UpgradeOnRefresh)
{
pcd.UpgradeOnRefresh = UpgradeOnRefresh;
}
if (IsTupleCache)
{
pcd.IsTupleCache = IsTupleCache;
}
if (SupportSubquery)
{
pcd.SupportSubquery = SupportSubquery;
}
if (SupportAdvancedDrill)
{
pcd.SupportAdvancedDrill = SupportAdvancedDrill;
}
pcd.CacheSource = CacheSource.ToCacheSource();
pcd.CacheFields = new CacheFields {
Count = (uint)CacheFields.Count
};
foreach (var cf in CacheFields)
{
pcd.CacheFields.Append(cf.ToCacheField());
}
if (CacheHierarchies.Count > 0)
{
pcd.CacheHierarchies = new CacheHierarchies {
Count = (uint)CacheHierarchies.Count
};
foreach (var ch in CacheHierarchies)
{
pcd.CacheHierarchies.Append(ch.ToCacheHierarchy());
}
}
if (Kpis.Count > 0)
{
pcd.Kpis = new Kpis {
Count = (uint)Kpis.Count
};
foreach (var k in Kpis)
{
pcd.Kpis.Append(k.ToKpi());
}
}
if (HasTupleCache)
{
pcd.TupleCache = TupleCache.ToTupleCache();
}
if (CalculatedItems.Count > 0)
{
pcd.CalculatedItems = new CalculatedItems {
Count = (uint)CalculatedItems.Count
};
foreach (var ci in CalculatedItems)
{
pcd.CalculatedItems.Append(ci.ToCalculatedItem());
}
}
if (CalculatedMembers.Count > 0)
{
pcd.CalculatedMembers = new CalculatedMembers {
Count = (uint)CalculatedMembers.Count
};
foreach (var cm in CalculatedMembers)
{
pcd.CalculatedMembers.Append(cm.ToCalculatedMember());
}
}
if (Dimensions.Count > 0)
{
pcd.Dimensions = new Dimensions {
Count = (uint)Dimensions.Count
};
foreach (var d in Dimensions)
{
pcd.Dimensions.Append(d.ToDimension());
}
}
if (MeasureGroups.Count > 0)
{
pcd.MeasureGroups = new MeasureGroups {
Count = (uint)MeasureGroups.Count
};
foreach (var mg in MeasureGroups)
{
pcd.MeasureGroups.Append(mg.ToMeasureGroup());
}
}
if (Maps.Count > 0)
{
pcd.Maps = new Maps {
Count = (uint)Maps.Count
};
foreach (var mdm in Maps)
{
pcd.Maps.Append(mdm.ToMeasureDimensionMap());
}
}
return(pcd);
}
internal void FromPivotCacheDefinition(PivotCacheDefinition pcd)
{
SetAllNull();
if (pcd.Id != null)
{
Id = pcd.Id.Value;
}
if (pcd.Invalid != null)
{
Invalid = pcd.Invalid.Value;
}
if (pcd.SaveData != null)
{
SaveData = pcd.SaveData.Value;
}
if (pcd.RefreshOnLoad != null)
{
RefreshOnLoad = pcd.RefreshOnLoad.Value;
}
if (pcd.OptimizeMemory != null)
{
OptimizeMemory = pcd.OptimizeMemory.Value;
}
if (pcd.EnableRefresh != null)
{
EnableRefresh = pcd.EnableRefresh.Value;
}
if (pcd.RefreshedBy != null)
{
RefreshedBy = pcd.RefreshedBy.Value;
}
if (pcd.RefreshedDate != null)
{
RefreshedDate = pcd.RefreshedDate.Value;
}
if (pcd.BackgroundQuery != null)
{
BackgroundQuery = pcd.BackgroundQuery.Value;
}
if (pcd.MissingItemsLimit != null)
{
MissingItemsLimit = pcd.MissingItemsLimit.Value;
}
if (pcd.CreatedVersion != null)
{
CreatedVersion = pcd.CreatedVersion.Value;
}
if (pcd.RefreshedVersion != null)
{
RefreshedVersion = pcd.RefreshedVersion.Value;
}
if (pcd.MinRefreshableVersion != null)
{
MinRefreshableVersion = pcd.MinRefreshableVersion.Value;
}
if (pcd.RecordCount != null)
{
RecordCount = pcd.RecordCount.Value;
}
if (pcd.UpgradeOnRefresh != null)
{
UpgradeOnRefresh = pcd.UpgradeOnRefresh.Value;
}
if (pcd.IsTupleCache != null)
{
IsTupleCache = pcd.IsTupleCache.Value;
}
if (pcd.SupportSubquery != null)
{
SupportSubquery = pcd.SupportSubquery.Value;
}
if (pcd.SupportAdvancedDrill != null)
{
SupportAdvancedDrill = pcd.SupportAdvancedDrill.Value;
}
if (pcd.CacheSource != null)
{
CacheSource.FromCacheSource(pcd.CacheSource);
}
// doing one by one because it's bloody hindering awkward complicated.
if (pcd.CacheFields != null)
{
SLCacheField cf;
using (var oxr = OpenXmlReader.Create(pcd.CacheFields))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CacheField))
{
cf = new SLCacheField();
cf.FromCacheField((CacheField)oxr.LoadCurrentElement());
CacheFields.Add(cf);
}
}
}
}
if (pcd.CacheHierarchies != null)
{
SLCacheHierarchy ch;
using (var oxr = OpenXmlReader.Create(pcd.CacheHierarchies))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CacheHierarchy))
{
ch = new SLCacheHierarchy();
ch.FromCacheHierarchy((CacheHierarchy)oxr.LoadCurrentElement());
CacheHierarchies.Add(ch);
}
}
}
}
if (pcd.Kpis != null)
{
SLKpi k;
using (var oxr = OpenXmlReader.Create(pcd.Kpis))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Kpi))
{
k = new SLKpi();
k.FromKpi((Kpi)oxr.LoadCurrentElement());
Kpis.Add(k);
}
}
}
}
if (pcd.TupleCache != null)
{
TupleCache.FromTupleCache(pcd.TupleCache);
HasTupleCache = true;
}
if (pcd.CalculatedItems != null)
{
SLCalculatedItem ci;
using (var oxr = OpenXmlReader.Create(pcd.CalculatedItems))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculatedItem))
{
ci = new SLCalculatedItem();
ci.FromCalculatedItem((CalculatedItem)oxr.LoadCurrentElement());
CalculatedItems.Add(ci);
}
}
}
}
if (pcd.CalculatedMembers != null)
{
SLCalculatedMember cm;
using (var oxr = OpenXmlReader.Create(pcd.CalculatedMembers))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculatedMember))
{
cm = new SLCalculatedMember();
cm.FromCalculatedMember((CalculatedMember)oxr.LoadCurrentElement());
CalculatedMembers.Add(cm);
}
}
}
}
if (pcd.Dimensions != null)
{
SLDimension d;
using (var oxr = OpenXmlReader.Create(pcd.Dimensions))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Dimension))
{
d = new SLDimension();
d.FromDimension((Dimension)oxr.LoadCurrentElement());
Dimensions.Add(d);
}
}
}
}
if (pcd.MeasureGroups != null)
{
SLMeasureGroup mg;
using (var oxr = OpenXmlReader.Create(pcd.MeasureGroups))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(MeasureGroup))
{
mg = new SLMeasureGroup();
mg.FromMeasureGroup((MeasureGroup)oxr.LoadCurrentElement());
MeasureGroups.Add(mg);
}
}
}
}
if (pcd.Maps != null)
{
SLMeasureDimensionMap mdm;
using (var oxr = OpenXmlReader.Create(pcd.Maps))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(MeasureDimensionMap))
{
mdm = new SLMeasureDimensionMap();
mdm.FromMeasureDimensionMap((MeasureDimensionMap)oxr.LoadCurrentElement());
Maps.Add(mdm);
}
}
}
}
}
internal PivotCacheDefinition ToPivotCacheDefinition()
{
PivotCacheDefinition pcd = new PivotCacheDefinition();
if (this.Id != null && this.Id.Length > 0)
{
pcd.Id = this.Id;
}
if (this.Invalid != false)
{
pcd.Invalid = this.Invalid;
}
if (this.SaveData != true)
{
pcd.SaveData = this.SaveData;
}
if (this.RefreshOnLoad != false)
{
pcd.RefreshOnLoad = this.RefreshOnLoad;
}
if (this.OptimizeMemory != false)
{
pcd.OptimizeMemory = this.OptimizeMemory;
}
if (this.EnableRefresh != true)
{
pcd.EnableRefresh = this.EnableRefresh;
}
if (this.RefreshedBy != null && this.RefreshedBy.Length > 0)
{
pcd.RefreshedBy = this.RefreshedBy;
}
if (this.RefreshedDate != null)
{
pcd.RefreshedDate = this.RefreshedDate.Value;
}
if (this.BackgroundQuery != false)
{
pcd.BackgroundQuery = this.BackgroundQuery;
}
if (this.MissingItemsLimit != null)
{
pcd.MissingItemsLimit = this.MissingItemsLimit.Value;
}
if (this.CreatedVersion != 0)
{
pcd.CreatedVersion = this.CreatedVersion;
}
if (this.RefreshedVersion != 0)
{
pcd.RefreshedVersion = this.RefreshedVersion;
}
if (this.MinRefreshableVersion != 0)
{
pcd.MinRefreshableVersion = this.MinRefreshableVersion;
}
if (this.RecordCount != null)
{
pcd.RecordCount = this.RecordCount.Value;
}
if (this.UpgradeOnRefresh != false)
{
pcd.UpgradeOnRefresh = this.UpgradeOnRefresh;
}
if (this.IsTupleCache != false)
{
pcd.IsTupleCache = this.IsTupleCache;
}
if (this.SupportSubquery != false)
{
pcd.SupportSubquery = this.SupportSubquery;
}
if (this.SupportAdvancedDrill != false)
{
pcd.SupportAdvancedDrill = this.SupportAdvancedDrill;
}
pcd.CacheSource = this.CacheSource.ToCacheSource();
pcd.CacheFields = new CacheFields()
{
Count = (uint)this.CacheFields.Count
};
foreach (SLCacheField cf in this.CacheFields)
{
pcd.CacheFields.Append(cf.ToCacheField());
}
if (this.CacheHierarchies.Count > 0)
{
pcd.CacheHierarchies = new CacheHierarchies()
{
Count = (uint)this.CacheHierarchies.Count
};
foreach (SLCacheHierarchy ch in this.CacheHierarchies)
{
pcd.CacheHierarchies.Append(ch.ToCacheHierarchy());
}
}
if (this.Kpis.Count > 0)
{
pcd.Kpis = new Kpis()
{
Count = (uint)this.Kpis.Count
};
foreach (SLKpi k in this.Kpis)
{
pcd.Kpis.Append(k.ToKpi());
}
}
if (this.HasTupleCache)
{
pcd.TupleCache = this.TupleCache.ToTupleCache();
}
if (this.CalculatedItems.Count > 0)
{
pcd.CalculatedItems = new CalculatedItems()
{
Count = (uint)this.CalculatedItems.Count
};
foreach (SLCalculatedItem ci in this.CalculatedItems)
{
pcd.CalculatedItems.Append(ci.ToCalculatedItem());
}
}
if (this.CalculatedMembers.Count > 0)
{
pcd.CalculatedMembers = new CalculatedMembers()
{
Count = (uint)this.CalculatedMembers.Count
};
foreach (SLCalculatedMember cm in this.CalculatedMembers)
{
pcd.CalculatedMembers.Append(cm.ToCalculatedMember());
}
}
if (this.Dimensions.Count > 0)
{
pcd.Dimensions = new Dimensions()
{
Count = (uint)this.Dimensions.Count
};
foreach (SLDimension d in this.Dimensions)
{
pcd.Dimensions.Append(d.ToDimension());
}
}
if (this.MeasureGroups.Count > 0)
{
pcd.MeasureGroups = new MeasureGroups()
{
Count = (uint)this.MeasureGroups.Count
};
foreach (SLMeasureGroup mg in this.MeasureGroups)
{
pcd.MeasureGroups.Append(mg.ToMeasureGroup());
}
}
if (this.Maps.Count > 0)
{
pcd.Maps = new Maps()
{
Count = (uint)this.Maps.Count
};
foreach (SLMeasureDimensionMap mdm in this.Maps)
{
pcd.Maps.Append(mdm.ToMeasureDimensionMap());
}
}
return(pcd);
}
protected void Page_Load(object sender, EventArgs e)
{
//load url arguments in variables
string INSTANCE_NAME = @Request.QueryString["instance"];
string CATALOG_NAME = Request.QueryString["catalog"];
string CUBE_NAME = Request.QueryString["cube"];
//Create Excel file name
string ConnectionName = INSTANCE_NAME.Replace("\\", "_") + "_" + CATALOG_NAME + "_" + CUBE_NAME;
Response.Write(ConnectionName);
//Create Workbook
string filename = Server.MapPath(@"tmp/" + ConnectionName + ".xlsx");
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
//Add a connectionPart to the workbookpart
ConnectionsPart connectionsPart1 = workbookpart.AddNewPart <ConnectionsPart>();
Connections connections1 = new Connections();
Connection connection1 = new Connection()
{
Id = (UInt32Value)1U, KeepAlive = true, Name = ConnectionName, Type = (UInt32Value)5U, RefreshedVersion = 5, Background = true
};
DatabaseProperties databaseProperties1 = new DatabaseProperties()
{
Connection = "Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + CATALOG_NAME + ";Data Source=" + @INSTANCE_NAME + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error", Command = CUBE_NAME, CommandType = (UInt32Value)1U
};
OlapProperties olapProperties1 = new OlapProperties()
{
SendLocale = true, RowDrillCount = (UInt32Value)1000U
};
connection1.Append(databaseProperties1);
connection1.Append(olapProperties1);
connections1.Append(connection1);
connectionsPart1.Connections = connections1;
//Add a PivottableCache part
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = workbookpart.AddNewPart <PivotTableCacheDefinitionPart>();
PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition()
{
SaveData = false, BackgroundQuery = true, SupportSubquery = true, SupportAdvancedDrill = true
};
pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + @"D:\MyBI\SSAS\SSAS2012_MyBI\tmp\test.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.WriteFile(Server.MapPath(@"tmp/" + ConnectionName + ".xlsx"));
Response.End();
}
