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