private void DeleteCellFormula(Cell cell, WorksheetPart wsPart)
{
try
{
CalculationChainPart calculationChainPart = wbPart.CalculationChainPart;
CalculationChain calculationChain = calculationChainPart.CalculationChain;
var calculationCells = calculationChain.Elements <CalculationCell>().ToList();
if (cell.CellFormula != null && cell.CellValue != null)
{
string cellRef = cell.CellReference;
CalculationCell calculationCell = calculationCells.Where(c => c.CellReference == cellRef).FirstOrDefault();
cell.CellFormula.Remove();
if (calculationCell != null)
{
calculationCell.Remove();
calculationCells.Remove(calculationCell);
}
else
{
System.Console.WriteLine("Unable to delete cell formula, no further details.");
}
}
if (calculationCells.Count == 0)
{
wbPart.DeletePart(calculationChainPart);
}
}
catch (Exception e)
{
throw new ApplicationException("Unable to delete cell formula.", e);
}
}
//BLP(A2 & " " & B2;C1)
public static void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart, string cellRef, int sheetId)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell()
{
CellReference = cellRef, SheetId = sheetId
};
calculationChain1.Append(calculationCell1);
calculationChainPart.CalculationChain = calculationChain1;
}
public static void bloom(string col, int li, string formule)
{
//Copie du template et ouverture du fichier
System.IO.File.Copy("TemplateBloom.xlsx", "BloomGenerated.xlsx", true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("BloomGenerated.xlsx", true);
//myWorkbook.WorkbookPart.Workbook.NamespaceDeclarations
//myWorkbook.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
//myWorkbook.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
//Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt <WorksheetPart>(2);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
sheetData.RemoveNamespaceDeclaration("x");
//CalculationChainPart calculationChainPart1 = workbookPart.AddNewPart<CalculationChainPart>("rId7");
//GenerateCalculationChainPart1Content(calculationChainPart1,"C2",1);
CalculationChain CC = workbookPart.GetPartsOfType <CalculationChainPart>().First().CalculationChain;
CalculationCell calculationCell = new CalculationCell()
{
CellReference = col + li
};
CC.Append(calculationCell);
//GenerateCalculationCell(CC, "C3", 1);
Row r = (Row)sheetData.ChildElements.GetItem(2);
Cell cell = GenerateCell(col + li, formule);
//Cell cell = (Cell) r.ChildElements.GetItem(2);
//cell.DataType = CellValues.Error;
//cell.CellValue.Text="#NAME?";
//cell.CellFormula.Text = formule;
r.RemoveNamespaceDeclaration("x");
cell.RemoveNamespaceDeclaration("x");
r.AppendChild(cell);
CalculationChainPart ccp = workbookPart.CalculationChainPart;
workbookPart.DeletePart(ccp);
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
public bool InsertFormulaChain(string formula, string fromCell, string toCell, WorksheetPart wsPart = null, bool force = false)
{
if (this.workbook == null || this.workbook.Workbook == null)
{
Console.WriteLine("Error: This spreadsheet has no workbook!");
return(false);
}
if (wsPart == null)
{
if (force == true)
{
if (this.workbook.GetPartsOfType <WorksheetPart>().Count() <= 0)
{
Console.WriteLine("Error: This spreadsheet has no sheets");
}
else
{
wsPart = this.workbook.GetPartsOfType <WorksheetPart>().FirstOrDefault();
if (wsPart == null)
{
Console.WriteLine("Error: Internal error while getting sheet!");
return(false);
}
}
}
else
{
Console.WriteLine("Error: This speardsheet has no according worksheet.");
return(false);
}
}
if (this.calcChain == null)
{
Console.WriteLine("InsertFormulaChain [WARNING]: This spreadsheet does not have calcChainPart, auto create new one!");
this.calcChain = this.workbook.AddNewPart <CalculationChainPart>();
this.calcChain.CalculationChain = new CalculationChain();
}
string fromCol, toCol, tmpFromRow, tmpToRow;
uint fromRow, toRow;
fromCol = this.GetPartFromAddress(fromCell, false);
if (fromCol == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Invalid fromCell, got {0}", fromCell);
return(false);
}
toCol = this.GetPartFromAddress(toCell, false);
if (toCol == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Invalid fromCell, got {0}", toCell);
return(false);
}
if (!fromCol.Equals(toCol))
{
Console.WriteLine("InsertFormulaChain [ERROR]: Cannot add calcChain in different column, got {0}, {1}", fromCell, toCell);
return(false);
}
tmpFromRow = this.GetPartFromAddress(fromCell, true);
if (tmpFromRow == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Invalid fromCell, got {0}", fromCell);
return(false);
}
tmpToRow = this.GetPartFromAddress(toCell, true);
if (tmpToRow == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Invalid fromCell, got {0}", toCell);
return(false);
}
fromRow = uint.Parse(tmpFromRow);
toRow = uint.Parse(tmpToRow);
// Get the corresponding sheet:
string wsPartID = this.workbook.GetIdOfPart(wsPart);
if (wsPartID == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Internal error!");
return(false);
}
Sheets sheets = this.workbook.Workbook.GetFirstChild <Sheets>();
Sheet correspondingSheet = sheets.Elements <Sheet>()
.Where(e => string.Compare(e.Id, wsPartID, true) == 0).FirstOrDefault();
if (correspondingSheet == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Internal error!");
return(false);
}
int sheetID;
bool res = int.TryParse(correspondingSheet.SheetId.ToString(), out sheetID);
if (res == false)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Internal error!");
return(false);
}
Cell baseCell = this.InsertCellInWorksheet(fromCol, fromRow, wsPart);
if (baseCell == null)
{
Console.WriteLine("InsertFormulaChain [ERROR]: Cannot insert cell at {0}!", fromCell);
return(false);
}
string baseCellRef = fromCell + ":" + toCell;
uint baseCellSi = 0;
IEnumerable <CellFormula> listFomula = wsPart.Worksheet.Descendants <CellFormula>()
.Where(f => {
if (f.FormulaType != null)
{
return(f.FormulaType == CellFormulaValues.Shared);
}
return(false);
});
if (listFomula.Count() > 0)
{
baseCellSi = listFomula.Select(f => uint.Parse(f.SharedIndex.ToString())).Max() + 1;
}
baseCell.CellFormula = new CellFormula(formula)
{
FormulaType = new EnumValue <CellFormulaValues>(CellFormulaValues.Shared),
Reference = baseCellRef, SharedIndex = baseCellSi
};
baseCell.CellValue = new CellValue("0");
CalculationCell baseCellCal;
baseCellCal = this.calcChain.CalculationChain.Elements <CalculationCell>()
.Where(c => c.CellReference == baseCell.CellReference && c.SheetId == sheetID)
.FirstOrDefault();
if (baseCellCal == null)
{
baseCellCal = new CalculationCell()
{
CellReference = baseCell.CellReference, SheetId = sheetID
};
this.calcChain.CalculationChain.Append(baseCellCal);
}
bool trailAddRet = true;
for (uint i = fromRow + 1; i <= toRow; i++)
{
Cell trailCell = this.InsertCellInWorksheet(fromCol, i, wsPart);
if (trailCell != null)
{
trailCell.CellFormula = new CellFormula()
{
FormulaType = new EnumValue <CellFormulaValues>(CellFormulaValues.Shared),
SharedIndex = baseCellSi
};
trailCell.CellValue = new CellValue("0");
CalculationCell trailCellCal;
trailCellCal = this.calcChain.CalculationChain.Elements <CalculationCell>()
.Where(c => c.CellReference == trailCell.CellReference && c.SheetId == sheetID)
.FirstOrDefault();
if (trailCellCal == null)
{
trailCellCal = new CalculationCell()
{
CellReference = trailCell.CellReference, SheetId = sheetID
};
this.calcChain.CalculationChain.Append(trailCellCal);
}
else
{
if (trailCellCal.NewLevel != null)
{
trailCellCal.NewLevel = null;
}
}
}
else
{
Console.WriteLine("InsertFormularChain [WARNING]: Cannot add formula at trailing cell {0}", fromCol + i);
trailAddRet = false;
}
}
this.workbook.Workbook.CalculationProperties.ForceFullCalculation = true;
this.workbook.Workbook.CalculationProperties.FullCalculationOnLoad = true;
this.workbook.Workbook.Save();
return(trailAddRet);
}
public Cell InsertFormula(string formula, string columnName, uint rowIndex, WorksheetPart wsPart, bool force = false)
{
if (this.workbook == null || this.workbook.Workbook == null)
{
Console.WriteLine("Error: This spreadsheet has no workbook!");
return(null);
}
if (wsPart == null)
{
if (force == true)
{
if (this.workbook.GetPartsOfType <WorksheetPart>().Count() <= 0)
{
Console.WriteLine("Error: This spreadsheet has no sheets");
}
else
{
wsPart = this.workbook.GetPartsOfType <WorksheetPart>().FirstOrDefault();
if (wsPart == null)
{
Console.WriteLine("Error: Internal error while getting sheet!");
return(null);
}
}
}
else
{
Console.WriteLine("Error: This speardsheet has no according worksheet.");
return(null);
}
}
if (this.calcChain == null)
{
Console.WriteLine("InsertFormula [WARNING]: This spreadsheet does not have calcChainPart, auto create new one!");
this.calcChain = this.workbook.AddNewPart <CalculationChainPart>();
this.calcChain.CalculationChain = new CalculationChain();
}
Cell cell = this.InsertCellInWorksheet(columnName, rowIndex, wsPart);
if (cell == null)
{
Console.WriteLine("InsertFormula [ERROR]: Cannot insert cell at {0}!", columnName + rowIndex);
return(null);
}
cell.CellFormula = new CellFormula(formula);
cell.CellValue = new CellValue("0");
string wsPartID = this.workbook.GetIdOfPart(wsPart);
if (wsPartID == null)
{
Console.WriteLine("InsertFormula [ERROR]: Internal error!");
return(null);
}
Sheets sheets = this.workbook.Workbook.GetFirstChild <Sheets>();
Sheet correspondingSheet = sheets.Elements <Sheet>()
.Where(e => string.Compare(e.Id, wsPartID, true) == 0).FirstOrDefault();
if (correspondingSheet == null)
{
Console.WriteLine("InsertFormula [ERROR]: Internal error!");
return(null);
}
int sheetID;
bool res = int.TryParse(correspondingSheet.SheetId.ToString(), out sheetID);
if (res == false)
{
Console.WriteLine("InsertFormula [ERROR]: Internal error!");
return(null);
}
CalculationCell calCel;
// If there is already cal cell at CellReference in SheetID, no need to add new:
calCel = this.calcChain.CalculationChain.Elements <CalculationCell>()
.Where(c => c.CellReference == cell.CellReference && c.SheetId == sheetID)
.FirstOrDefault();
if (calCel == null)
{
calCel = new CalculationCell()
{
CellReference = cell.CellReference, NewLevel = true, SheetId = sheetID
};
this.calcChain.CalculationChain.Append(calCel);
}
this.workbook.Workbook.CalculationProperties.ForceFullCalculation = true;
this.workbook.Workbook.CalculationProperties.FullCalculationOnLoad = true;
this.workbook.Workbook.Save();
return(cell);
}
//private WorkbookPart
public CustomSpreadsheet(string path)
{
if (!System.IO.File.Exists(path))
{
//this.spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook);
try
{
this.spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook);
}
catch (Exception ex)
{
this.spreadsheet = null;
return;
}
WorkbookPart workbookPart = this.spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
this.workbook = workbookPart;
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = this.spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = this.spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
//this.Save();
this.Save();
//this.Close();
}
else
{
OpenSettings openSettings = new OpenSettings();
openSettings.MarkupCompatibilityProcessSettings =
new MarkupCompatibilityProcessSettings(
MarkupCompatibilityProcessMode.ProcessAllParts,
FileFormatVersions.Office2013
);
try
{
this.spreadsheet = SpreadsheetDocument.Open(path, true, openSettings);
}
catch (Exception ex)
{
this.spreadsheet = null;
}
if (this.spreadsheet.GetPartsCountOfType <WorkbookPart>() > 0)
{
this.workbook = this.spreadsheet.WorkbookPart;
}
else
{
this.workbook = this.spreadsheet.AddWorkbookPart();
}
if (this.workbook == null)
{
this.workbook = this.spreadsheet.AddWorkbookPart();
this.workbook.Workbook = new Workbook();
this.InsertWorksheet();
}
if (this.workbook.GetPartsCountOfType <SharedStringTablePart>() > 0)
{
this.sharedStrings = this.workbook.GetPartsOfType <SharedStringTablePart>().First();
}
if (this.workbook.GetPartsCountOfType <CalculationChainPart>() > 0)
{
this.calcChain = this.workbook.GetPartsOfType <CalculationChainPart>().First();
}
}
}
internal void ReplaceFormulaWithValue(string sheetName)
{
var formulaDict = new Dictionary <string, string>();
StringBuilder logStringBuilder = new StringBuilder();
var worksheetPart = GetWorksheetPartByName(_spreadSheet, sheetName);
_worksheetPart = worksheetPart;
CalculationChainPart calculationChainPart = _spreadSheet.WorkbookPart.CalculationChainPart;
if (calculationChainPart == null)
{
return;
}
CalculationChain calculationChain = calculationChainPart.CalculationChain;
var calculationCells = calculationChain.Elements <CalculationCell>().ToList();
foreach (Row row in worksheetPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>())
{
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.CellValue != null)
{
Console.WriteLine(cell.CellReference);
}
if (cell.CellFormula != null &&
cell.CellValue != null)
{
string cellRef = cell.CellReference;
string formula = cell.CellFormula.InnerText;
if (!string.IsNullOrEmpty(cell.CellFormula.SharedIndex))
{
if (!formulaDict.ContainsKey(cell.CellFormula.SharedIndex.InnerText))
{
formulaDict.Add(cell.CellFormula.SharedIndex.InnerText, cell.CellFormula.InnerText);
}
}
//
if (formula == "" && cell.CellFormula.SharedIndex != null)
{
string tmp;
if (formulaDict.TryGetValue(cell.CellFormula.SharedIndex.InnerText, out tmp))
{
formula = "Shared " + tmp;
}
}
CalculationCell calculationCell =
calculationCells.Where(c => c.CellReference == cellRef).FirstOrDefault();
//CalculationCell calculationCell = calculationChain.Elements<CalculationCell>().Where(c => c.CellReference == cell.CellReference).FirstOrDefault();
string value = cell.CellValue.InnerText;
UpdateCell(cell, DataTypes.String, value);
cell.CellFormula.Remove();
calculationCell.Remove();
//Try
calculationCells.Remove(calculationCell);
//Log
if (Log)
{
string log = string.Format("Cell: {0} | Sheet: {1} | Formula: {2} | Value: {3}", cellRef,
sheetName, formula, value);
logStringBuilder.Append(log);
logStringBuilder.Append(Environment.NewLine);
}
}
if (calculationCells.Count == 0)
{
//delete calcCalutions.xml
_spreadSheet.WorkbookPart.DeletePart(calculationChainPart);
}
}
}
//Log
if (Log)
{
File.AppendAllText("Log.log", logStringBuilder.ToString());
}
//SaveChanges();
}
/// <summary>
/// Deletes the supplied sheet instance from the workbook part
/// </summary>
/// <param name="workbookPart">The workbook part to delete the sheet from</param>
/// <param name="sheet">The sheet to delete</param>
/// <exception cref="ArgumentNullException">
/// workbookPart
/// or
/// sheet
/// </exception>
public static void DeleteSheet(this WorkbookPart workbookPart, DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
if (workbookPart == null)
{
throw new ArgumentNullException("workbookPart");
}
if (sheet == null)
{
throw new ArgumentNullException("sheet");
}
// get the id of the sheet for deletion
Int32Value sheetId = Int32Value.FromInt32((int)sheet.SheetId.Value);
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
SheetViews views = worksheetPart.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
worksheetPart.Worksheet.Save();
}
sheet.Remove();
// Delete the worksheet part.
workbookPart.DeletePart(worksheetPart);
// 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 = workbookPart.CalculationChainPart;
if (calChainPart != null)
{
List <CalculationCell> forRemoval = new List <CalculationCell>();
var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().ToList();
foreach (CalculationCell item in calChainEntries)
{
if (item.SheetId == null)
{
item.Remove();
}
else if (item.SheetId.HasValue && item.SheetId.Value.Equals(sheetId))
{
item.Remove();
}
}
if (calChainPart.CalculationChain.Count() == 0)
{
workbookPart.DeletePart(calChainPart);
}
}
workbookPart.Workbook.Save();
}
