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();
}
}
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
}