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 CalculationCell ToCalculationCell()
{
CalculationCell cc = new CalculationCell();
cc.CellReference = SLTool.ToCellReference(this.RowIndex, this.ColumnIndex);
cc.SheetId = this.SheetId;
if (this.InChildChain != null && this.InChildChain.Value)
{
cc.InChildChain = this.InChildChain.Value;
}
if (this.NewLevel != null && this.NewLevel.Value)
{
cc.NewLevel = this.NewLevel.Value;
}
if (this.NewThread != null && this.NewThread.Value)
{
cc.NewThread = this.NewThread.Value;
}
if (this.Array != null && this.Array.Value)
{
cc.Array = this.Array.Value;
}
return(cc);
}
internal CalculationCell ToCalculationCell()
{
var cc = new CalculationCell();
cc.CellReference = SLTool.ToCellReference(RowIndex, ColumnIndex);
cc.SheetId = SheetId;
if ((InChildChain != null) && InChildChain.Value)
{
cc.InChildChain = InChildChain.Value;
}
if ((NewLevel != null) && NewLevel.Value)
{
cc.NewLevel = NewLevel.Value;
}
if ((NewThread != null) && NewThread.Value)
{
cc.NewThread = NewThread.Value;
}
if ((Array != null) && Array.Value)
{
cc.Array = Array.Value;
}
return(cc);
}
internal void FromCalculationCell(CalculationCell cc)
{
this.SetAllNull();
int iRowIndex = -1;
int iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(cc.CellReference.Value, out iRowIndex, out iColumnIndex))
{
this.RowIndex = iRowIndex;
this.ColumnIndex = iColumnIndex;
}
this.SheetId = cc.SheetId ?? 0;
if (cc.InChildChain != null)
{
this.InChildChain = cc.InChildChain.Value;
}
if (cc.NewLevel != null)
{
this.NewLevel = cc.NewLevel.Value;
}
if (cc.NewThread != null)
{
this.NewThread = cc.NewThread.Value;
}
if (cc.Array != null)
{
this.Array = cc.Array.Value;
}
}
public static void GenerateCalculationCell(CalculationChain calculationChain, string cellRef, int sheetId)
{
CalculationCell calculationCell1 = new CalculationCell()
{
CellReference = cellRef, SheetId = sheetId
};
calculationChain.Append(calculationCell1);
}
//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;
}
internal CalculationCell ToCalculationCell()
{
CalculationCell cc = new CalculationCell();
cc.CellReference = SLTool.ToCellReference(this.RowIndex, this.ColumnIndex);
cc.SheetId = this.SheetId;
if (this.InChildChain != null && this.InChildChain.Value) cc.InChildChain = this.InChildChain.Value;
if (this.NewLevel != null && this.NewLevel.Value) cc.NewLevel = this.NewLevel.Value;
if (this.NewThread != null && this.NewThread.Value) cc.NewThread = this.NewThread.Value;
if (this.Array != null && this.Array.Value) cc.Array = this.Array.Value;
return cc;
}
public override bool OnCircular(IEnumerator circularCellsData)
{
CalculationCell cc = null;
ArrayList cur = new ArrayList();
while (circularCellsData.MoveNext())
{
cc = (CalculationCell)circularCellsData.Current;
cur.Add(cc.Worksheet.Name + "!" + CellsHelper.CellIndexToName(cc.CellRow, cc.CellColumn));
}
circulars.Add(cur);
return(true);
}
internal void FromCalculationCell(CalculationCell cc)
{
this.SetAllNull();
int iRowIndex = -1;
int iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(cc.CellReference.Value, out iRowIndex, out iColumnIndex))
{
this.RowIndex = iRowIndex;
this.ColumnIndex = iColumnIndex;
}
this.SheetId = cc.SheetId ?? 0;
if (cc.InChildChain != null) this.InChildChain = cc.InChildChain.Value;
if (cc.NewLevel != null) this.NewLevel = cc.NewLevel.Value;
if (cc.NewThread != null) this.NewThread = cc.NewThread.Value;
if (cc.Array != null) this.Array = cc.Array.Value;
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell() { CellReference = "J42", SheetId = 1 };
CalculationCell calculationCell2 = new CalculationCell() { CellReference = "J37" };
CalculationCell calculationCell3 = new CalculationCell() { CellReference = "J34" };
CalculationCell calculationCell4 = new CalculationCell() { CellReference = "J32" };
CalculationCell calculationCell5 = new CalculationCell() { CellReference = "H27" };
CalculationCell calculationCell6 = new CalculationCell() { CellReference = "G27" };
CalculationCell calculationCell7 = new CalculationCell() { CellReference = "F27" };
CalculationCell calculationCell8 = new CalculationCell() { CellReference = "E27" };
CalculationCell calculationCell9 = new CalculationCell() { CellReference = "D27" };
CalculationCell calculationCell10 = new CalculationCell() { CellReference = "C27" };
CalculationCell calculationCell11 = new CalculationCell() { CellReference = "B27" };
CalculationCell calculationCell12 = new CalculationCell() { CellReference = "J27", InChildChain = true };
CalculationCell calculationCell13 = new CalculationCell() { CellReference = "H22" };
CalculationCell calculationCell14 = new CalculationCell() { CellReference = "G22" };
CalculationCell calculationCell15 = new CalculationCell() { CellReference = "F22" };
CalculationCell calculationCell16 = new CalculationCell() { CellReference = "E22" };
CalculationCell calculationCell17 = new CalculationCell() { CellReference = "D22" };
CalculationCell calculationCell18 = new CalculationCell() { CellReference = "C22" };
CalculationCell calculationCell19 = new CalculationCell() { CellReference = "B22" };
CalculationCell calculationCell20 = new CalculationCell() { CellReference = "J22", InChildChain = true };
CalculationCell calculationCell21 = new CalculationCell() { CellReference = "H47", InChildChain = true };
CalculationCell calculationCell22 = new CalculationCell() { CellReference = "H49", InChildChain = true };
CalculationCell calculationCell23 = new CalculationCell() { CellReference = "H50", InChildChain = true };
CalculationCell calculationCell24 = new CalculationCell() { CellReference = "H51", InChildChain = true };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChain1.Append(calculationCell3);
calculationChain1.Append(calculationCell4);
calculationChain1.Append(calculationCell5);
calculationChain1.Append(calculationCell6);
calculationChain1.Append(calculationCell7);
calculationChain1.Append(calculationCell8);
calculationChain1.Append(calculationCell9);
calculationChain1.Append(calculationCell10);
calculationChain1.Append(calculationCell11);
calculationChain1.Append(calculationCell12);
calculationChain1.Append(calculationCell13);
calculationChain1.Append(calculationCell14);
calculationChain1.Append(calculationCell15);
calculationChain1.Append(calculationCell16);
calculationChain1.Append(calculationCell17);
calculationChain1.Append(calculationCell18);
calculationChain1.Append(calculationCell19);
calculationChain1.Append(calculationCell20);
calculationChain1.Append(calculationCell21);
calculationChain1.Append(calculationCell22);
calculationChain1.Append(calculationCell23);
calculationChain1.Append(calculationCell24);
calculationChainPart1.CalculationChain = calculationChain1;
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell() { CellReference = "A6", SheetId = 1, NewLevel = true };
calculationChain1.Append(calculationCell1);
calculationChainPart1.CalculationChain = calculationChain1;
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell() { CellReference = "AG21", SheetId = 1 };
CalculationCell calculationCell2 = new CalculationCell() { CellReference = "AG23", InChildChain = true };
CalculationCell calculationCell3 = new CalculationCell() { CellReference = "AF21" };
CalculationCell calculationCell4 = new CalculationCell() { CellReference = "AF23", InChildChain = true };
CalculationCell calculationCell5 = new CalculationCell() { CellReference = "AI21" };
CalculationCell calculationCell6 = new CalculationCell() { CellReference = "AJ21" };
CalculationCell calculationCell7 = new CalculationCell() { CellReference = "AH20" };
CalculationCell calculationCell8 = new CalculationCell() { CellReference = "AH21", InChildChain = true };
CalculationCell calculationCell9 = new CalculationCell() { CellReference = "AH23", InChildChain = true };
CalculationCell calculationCell10 = new CalculationCell() { CellReference = "AJ23" };
CalculationCell calculationCell11 = new CalculationCell() { CellReference = "C21" };
CalculationCell calculationCell12 = new CalculationCell() { CellReference = "C23", InChildChain = true };
CalculationCell calculationCell13 = new CalculationCell() { CellReference = "D21" };
CalculationCell calculationCell14 = new CalculationCell() { CellReference = "D23", InChildChain = true };
CalculationCell calculationCell15 = new CalculationCell() { CellReference = "E21" };
CalculationCell calculationCell16 = new CalculationCell() { CellReference = "E23", InChildChain = true };
CalculationCell calculationCell17 = new CalculationCell() { CellReference = "F21" };
CalculationCell calculationCell18 = new CalculationCell() { CellReference = "F23", InChildChain = true };
CalculationCell calculationCell19 = new CalculationCell() { CellReference = "G21" };
CalculationCell calculationCell20 = new CalculationCell() { CellReference = "G23", InChildChain = true };
CalculationCell calculationCell21 = new CalculationCell() { CellReference = "H21" };
CalculationCell calculationCell22 = new CalculationCell() { CellReference = "H23", InChildChain = true };
CalculationCell calculationCell23 = new CalculationCell() { CellReference = "I21" };
CalculationCell calculationCell24 = new CalculationCell() { CellReference = "I23", InChildChain = true };
CalculationCell calculationCell25 = new CalculationCell() { CellReference = "J21" };
CalculationCell calculationCell26 = new CalculationCell() { CellReference = "J23", InChildChain = true };
CalculationCell calculationCell27 = new CalculationCell() { CellReference = "K21" };
CalculationCell calculationCell28 = new CalculationCell() { CellReference = "K23", InChildChain = true };
CalculationCell calculationCell29 = new CalculationCell() { CellReference = "L21" };
CalculationCell calculationCell30 = new CalculationCell() { CellReference = "L23", InChildChain = true };
CalculationCell calculationCell31 = new CalculationCell() { CellReference = "M21" };
CalculationCell calculationCell32 = new CalculationCell() { CellReference = "M23", InChildChain = true };
CalculationCell calculationCell33 = new CalculationCell() { CellReference = "N21" };
CalculationCell calculationCell34 = new CalculationCell() { CellReference = "N23", InChildChain = true };
CalculationCell calculationCell35 = new CalculationCell() { CellReference = "O21" };
CalculationCell calculationCell36 = new CalculationCell() { CellReference = "O23", InChildChain = true };
CalculationCell calculationCell37 = new CalculationCell() { CellReference = "P21" };
CalculationCell calculationCell38 = new CalculationCell() { CellReference = "P23", InChildChain = true };
CalculationCell calculationCell39 = new CalculationCell() { CellReference = "Q21" };
CalculationCell calculationCell40 = new CalculationCell() { CellReference = "Q23", InChildChain = true };
CalculationCell calculationCell41 = new CalculationCell() { CellReference = "R21" };
CalculationCell calculationCell42 = new CalculationCell() { CellReference = "R23", InChildChain = true };
CalculationCell calculationCell43 = new CalculationCell() { CellReference = "S21" };
CalculationCell calculationCell44 = new CalculationCell() { CellReference = "S23", InChildChain = true };
CalculationCell calculationCell45 = new CalculationCell() { CellReference = "T21" };
CalculationCell calculationCell46 = new CalculationCell() { CellReference = "T23", InChildChain = true };
CalculationCell calculationCell47 = new CalculationCell() { CellReference = "U21" };
CalculationCell calculationCell48 = new CalculationCell() { CellReference = "U23", InChildChain = true };
CalculationCell calculationCell49 = new CalculationCell() { CellReference = "V21" };
CalculationCell calculationCell50 = new CalculationCell() { CellReference = "V23", InChildChain = true };
CalculationCell calculationCell51 = new CalculationCell() { CellReference = "W21" };
CalculationCell calculationCell52 = new CalculationCell() { CellReference = "W23", InChildChain = true };
CalculationCell calculationCell53 = new CalculationCell() { CellReference = "X21" };
CalculationCell calculationCell54 = new CalculationCell() { CellReference = "X23", InChildChain = true };
CalculationCell calculationCell55 = new CalculationCell() { CellReference = "Y21" };
CalculationCell calculationCell56 = new CalculationCell() { CellReference = "Y23", InChildChain = true };
CalculationCell calculationCell57 = new CalculationCell() { CellReference = "Z21" };
CalculationCell calculationCell58 = new CalculationCell() { CellReference = "Z23", InChildChain = true };
CalculationCell calculationCell59 = new CalculationCell() { CellReference = "AA21" };
CalculationCell calculationCell60 = new CalculationCell() { CellReference = "AA23", InChildChain = true };
CalculationCell calculationCell61 = new CalculationCell() { CellReference = "AB21" };
CalculationCell calculationCell62 = new CalculationCell() { CellReference = "AB23", InChildChain = true };
CalculationCell calculationCell63 = new CalculationCell() { CellReference = "AC21" };
CalculationCell calculationCell64 = new CalculationCell() { CellReference = "AC23", InChildChain = true };
CalculationCell calculationCell65 = new CalculationCell() { CellReference = "AD21" };
CalculationCell calculationCell66 = new CalculationCell() { CellReference = "AD23", InChildChain = true };
CalculationCell calculationCell67 = new CalculationCell() { CellReference = "AE21" };
CalculationCell calculationCell68 = new CalculationCell() { CellReference = "AE23", InChildChain = true };
CalculationCell calculationCell69 = new CalculationCell() { CellReference = "AK20", NewLevel = true };
CalculationCell calculationCell70 = new CalculationCell() { CellReference = "AK21", InChildChain = true };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChain1.Append(calculationCell3);
calculationChain1.Append(calculationCell4);
calculationChain1.Append(calculationCell5);
calculationChain1.Append(calculationCell6);
calculationChain1.Append(calculationCell7);
calculationChain1.Append(calculationCell8);
calculationChain1.Append(calculationCell9);
calculationChain1.Append(calculationCell10);
calculationChain1.Append(calculationCell11);
calculationChain1.Append(calculationCell12);
calculationChain1.Append(calculationCell13);
calculationChain1.Append(calculationCell14);
calculationChain1.Append(calculationCell15);
calculationChain1.Append(calculationCell16);
calculationChain1.Append(calculationCell17);
calculationChain1.Append(calculationCell18);
calculationChain1.Append(calculationCell19);
calculationChain1.Append(calculationCell20);
calculationChain1.Append(calculationCell21);
calculationChain1.Append(calculationCell22);
calculationChain1.Append(calculationCell23);
calculationChain1.Append(calculationCell24);
calculationChain1.Append(calculationCell25);
calculationChain1.Append(calculationCell26);
calculationChain1.Append(calculationCell27);
calculationChain1.Append(calculationCell28);
calculationChain1.Append(calculationCell29);
calculationChain1.Append(calculationCell30);
calculationChain1.Append(calculationCell31);
calculationChain1.Append(calculationCell32);
calculationChain1.Append(calculationCell33);
calculationChain1.Append(calculationCell34);
calculationChain1.Append(calculationCell35);
calculationChain1.Append(calculationCell36);
calculationChain1.Append(calculationCell37);
calculationChain1.Append(calculationCell38);
calculationChain1.Append(calculationCell39);
calculationChain1.Append(calculationCell40);
calculationChain1.Append(calculationCell41);
calculationChain1.Append(calculationCell42);
calculationChain1.Append(calculationCell43);
calculationChain1.Append(calculationCell44);
calculationChain1.Append(calculationCell45);
calculationChain1.Append(calculationCell46);
calculationChain1.Append(calculationCell47);
calculationChain1.Append(calculationCell48);
calculationChain1.Append(calculationCell49);
calculationChain1.Append(calculationCell50);
calculationChain1.Append(calculationCell51);
calculationChain1.Append(calculationCell52);
calculationChain1.Append(calculationCell53);
calculationChain1.Append(calculationCell54);
calculationChain1.Append(calculationCell55);
calculationChain1.Append(calculationCell56);
calculationChain1.Append(calculationCell57);
calculationChain1.Append(calculationCell58);
calculationChain1.Append(calculationCell59);
calculationChain1.Append(calculationCell60);
calculationChain1.Append(calculationCell61);
calculationChain1.Append(calculationCell62);
calculationChain1.Append(calculationCell63);
calculationChain1.Append(calculationCell64);
calculationChain1.Append(calculationCell65);
calculationChain1.Append(calculationCell66);
calculationChain1.Append(calculationCell67);
calculationChain1.Append(calculationCell68);
calculationChain1.Append(calculationCell69);
calculationChain1.Append(calculationCell70);
calculationChainPart1.CalculationChain = calculationChain1;
}
private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context)
{
var thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
if (workbookPart.CalculationChainPart == null)
workbookPart.AddNewPart<CalculationChainPart>(thisRelId);
if (workbookPart.CalculationChainPart.CalculationChain == null)
workbookPart.CalculationChainPart.CalculationChain = new CalculationChain();
var calculationChain = workbookPart.CalculationChainPart.CalculationChain;
calculationChain.RemoveAllChildren<CalculationCell>();
foreach (var worksheet in WorksheetsInternal)
{
var cellsWithoutFormulas = new HashSet<String>();
foreach (var c in worksheet.Internals.CellsCollection.GetCells())
{
if (XLHelper.IsNullOrWhiteSpace(c.FormulaA1))
cellsWithoutFormulas.Add(c.Address.ToStringRelative());
else
{
if (c.FormulaA1.StartsWith("{"))
{
var cc = new CalculationCell
{
CellReference = c.Address.ToString(),
SheetId = worksheet.SheetId
};
if (c.FormulaReference.FirstAddress.Equals(c.Address))
{
cc.Array = true;
calculationChain.AppendChild(cc);
calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true});
}
else
{
calculationChain.AppendChild(cc);
}
}
else
{
calculationChain.AppendChild(new CalculationCell
{
CellReference = c.Address.ToString(),
SheetId = worksheet.SheetId
});
}
}
}
//var cCellsToRemove = new List<CalculationCell>();
var m = from cc in calculationChain.Elements<CalculationCell>()
where !(cc.SheetId != null || cc.InChildChain != null)
&& calculationChain.Elements<CalculationCell>()
.Where(c1 => c1.SheetId != null)
.Select(c1 => c1.CellReference.Value)
.Contains(cc.CellReference.Value)
|| cellsWithoutFormulas.Contains(cc.CellReference.Value)
select cc;
//m.ToList().ForEach(cc => cCellsToRemove.Add(cc));
m.ToList().ForEach(cc => calculationChain.RemoveChild(cc));
}
if (!calculationChain.Any())
workbookPart.DeletePart(workbookPart.CalculationChainPart);
}
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();
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell() { CellReference = "G20", SheetId = 2 };
CalculationCell calculationCell2 = new CalculationCell() { CellReference = "G21" };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChainPart1.CalculationChain = calculationChain1;
}
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();
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell() { CellReference = "AN29", SheetId = 26 };
CalculationCell calculationCell2 = new CalculationCell() { CellReference = "AN26" };
CalculationCell calculationCell3 = new CalculationCell() { CellReference = "AM26" };
CalculationCell calculationCell4 = new CalculationCell() { CellReference = "G40" };
CalculationCell calculationCell5 = new CalculationCell() { CellReference = "AF26" };
CalculationCell calculationCell6 = new CalculationCell() { CellReference = "AL25" };
CalculationCell calculationCell7 = new CalculationCell() { CellReference = "AL24" };
CalculationCell calculationCell8 = new CalculationCell() { CellReference = "AL23" };
CalculationCell calculationCell9 = new CalculationCell() { CellReference = "AL22" };
CalculationCell calculationCell10 = new CalculationCell() { CellReference = "AL21" };
CalculationCell calculationCell11 = new CalculationCell() { CellReference = "G39" };
CalculationCell calculationCell12 = new CalculationCell() { CellReference = "G38" };
CalculationCell calculationCell13 = new CalculationCell() { CellReference = "G37" };
CalculationCell calculationCell14 = new CalculationCell() { CellReference = "G36" };
CalculationCell calculationCell15 = new CalculationCell() { CellReference = "G35" };
CalculationCell calculationCell16 = new CalculationCell() { CellReference = "AN25" };
CalculationCell calculationCell17 = new CalculationCell() { CellReference = "AN24" };
CalculationCell calculationCell18 = new CalculationCell() { CellReference = "AN23" };
CalculationCell calculationCell19 = new CalculationCell() { CellReference = "G18" };
CalculationCell calculationCell20 = new CalculationCell() { CellReference = "AN22" };
CalculationCell calculationCell21 = new CalculationCell() { CellReference = "G26" };
CalculationCell calculationCell22 = new CalculationCell() { CellReference = "H26" };
CalculationCell calculationCell23 = new CalculationCell() { CellReference = "I26" };
CalculationCell calculationCell24 = new CalculationCell() { CellReference = "J26" };
CalculationCell calculationCell25 = new CalculationCell() { CellReference = "K26" };
CalculationCell calculationCell26 = new CalculationCell() { CellReference = "L26" };
CalculationCell calculationCell27 = new CalculationCell() { CellReference = "M26" };
CalculationCell calculationCell28 = new CalculationCell() { CellReference = "N26" };
CalculationCell calculationCell29 = new CalculationCell() { CellReference = "O26" };
CalculationCell calculationCell30 = new CalculationCell() { CellReference = "P26" };
CalculationCell calculationCell31 = new CalculationCell() { CellReference = "Q26" };
CalculationCell calculationCell32 = new CalculationCell() { CellReference = "R26" };
CalculationCell calculationCell33 = new CalculationCell() { CellReference = "S26" };
CalculationCell calculationCell34 = new CalculationCell() { CellReference = "T26" };
CalculationCell calculationCell35 = new CalculationCell() { CellReference = "U26" };
CalculationCell calculationCell36 = new CalculationCell() { CellReference = "V26" };
CalculationCell calculationCell37 = new CalculationCell() { CellReference = "W26" };
CalculationCell calculationCell38 = new CalculationCell() { CellReference = "X26" };
CalculationCell calculationCell39 = new CalculationCell() { CellReference = "Y26" };
CalculationCell calculationCell40 = new CalculationCell() { CellReference = "Z26" };
CalculationCell calculationCell41 = new CalculationCell() { CellReference = "AA26" };
CalculationCell calculationCell42 = new CalculationCell() { CellReference = "AB26" };
CalculationCell calculationCell43 = new CalculationCell() { CellReference = "AC26" };
CalculationCell calculationCell44 = new CalculationCell() { CellReference = "AD26" };
CalculationCell calculationCell45 = new CalculationCell() { CellReference = "AE26" };
CalculationCell calculationCell46 = new CalculationCell() { CellReference = "AG26" };
CalculationCell calculationCell47 = new CalculationCell() { CellReference = "AH26" };
CalculationCell calculationCell48 = new CalculationCell() { CellReference = "AI26" };
CalculationCell calculationCell49 = new CalculationCell() { CellReference = "AJ26" };
CalculationCell calculationCell50 = new CalculationCell() { CellReference = "AK26" };
CalculationCell calculationCell51 = new CalculationCell() { CellReference = "AL26", NewLevel = true };
CalculationCell calculationCell52 = new CalculationCell() { CellReference = "AN21" };
CalculationCell calculationCell53 = new CalculationCell() { CellReference = "AN30", InChildChain = true };
CalculationCell calculationCell54 = new CalculationCell() { CellReference = "AN31", InChildChain = true };
CalculationCell calculationCell55 = new CalculationCell() { CellReference = "AN32", NewLevel = true };
CalculationCell calculationCell56 = new CalculationCell() { CellReference = "AN33" };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChain1.Append(calculationCell3);
calculationChain1.Append(calculationCell4);
calculationChain1.Append(calculationCell5);
calculationChain1.Append(calculationCell6);
calculationChain1.Append(calculationCell7);
calculationChain1.Append(calculationCell8);
calculationChain1.Append(calculationCell9);
calculationChain1.Append(calculationCell10);
calculationChain1.Append(calculationCell11);
calculationChain1.Append(calculationCell12);
calculationChain1.Append(calculationCell13);
calculationChain1.Append(calculationCell14);
calculationChain1.Append(calculationCell15);
calculationChain1.Append(calculationCell16);
calculationChain1.Append(calculationCell17);
calculationChain1.Append(calculationCell18);
calculationChain1.Append(calculationCell19);
calculationChain1.Append(calculationCell20);
calculationChain1.Append(calculationCell21);
calculationChain1.Append(calculationCell22);
calculationChain1.Append(calculationCell23);
calculationChain1.Append(calculationCell24);
calculationChain1.Append(calculationCell25);
calculationChain1.Append(calculationCell26);
calculationChain1.Append(calculationCell27);
calculationChain1.Append(calculationCell28);
calculationChain1.Append(calculationCell29);
calculationChain1.Append(calculationCell30);
calculationChain1.Append(calculationCell31);
calculationChain1.Append(calculationCell32);
calculationChain1.Append(calculationCell33);
calculationChain1.Append(calculationCell34);
calculationChain1.Append(calculationCell35);
calculationChain1.Append(calculationCell36);
calculationChain1.Append(calculationCell37);
calculationChain1.Append(calculationCell38);
calculationChain1.Append(calculationCell39);
calculationChain1.Append(calculationCell40);
calculationChain1.Append(calculationCell41);
calculationChain1.Append(calculationCell42);
calculationChain1.Append(calculationCell43);
calculationChain1.Append(calculationCell44);
calculationChain1.Append(calculationCell45);
calculationChain1.Append(calculationCell46);
calculationChain1.Append(calculationCell47);
calculationChain1.Append(calculationCell48);
calculationChain1.Append(calculationCell49);
calculationChain1.Append(calculationCell50);
calculationChain1.Append(calculationCell51);
calculationChain1.Append(calculationCell52);
calculationChain1.Append(calculationCell53);
calculationChain1.Append(calculationCell54);
calculationChain1.Append(calculationCell55);
calculationChain1.Append(calculationCell56);
calculationChainPart1.CalculationChain = calculationChain1;
}
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);
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell(){ CellReference = "C22", SheetId = 27 };
CalculationCell calculationCell2 = new CalculationCell(){ CellReference = "D22" };
CalculationCell calculationCell3 = new CalculationCell(){ CellReference = "E22" };
CalculationCell calculationCell4 = new CalculationCell(){ CellReference = "F22" };
CalculationCell calculationCell5 = new CalculationCell(){ CellReference = "G22" };
CalculationCell calculationCell6 = new CalculationCell(){ CellReference = "H22" };
CalculationCell calculationCell7 = new CalculationCell(){ CellReference = "I22" };
CalculationCell calculationCell8 = new CalculationCell(){ CellReference = "J22" };
CalculationCell calculationCell9 = new CalculationCell(){ CellReference = "C27" };
CalculationCell calculationCell10 = new CalculationCell(){ CellReference = "D27" };
CalculationCell calculationCell11 = new CalculationCell(){ CellReference = "E27" };
CalculationCell calculationCell12 = new CalculationCell(){ CellReference = "F27" };
CalculationCell calculationCell13 = new CalculationCell(){ CellReference = "G27" };
CalculationCell calculationCell14 = new CalculationCell(){ CellReference = "H27" };
CalculationCell calculationCell15 = new CalculationCell(){ CellReference = "I27" };
CalculationCell calculationCell16 = new CalculationCell(){ CellReference = "J27" };
CalculationCell calculationCell17 = new CalculationCell(){ CellReference = "C32" };
CalculationCell calculationCell18 = new CalculationCell(){ CellReference = "D32" };
CalculationCell calculationCell19 = new CalculationCell(){ CellReference = "E32" };
CalculationCell calculationCell20 = new CalculationCell(){ CellReference = "F32" };
CalculationCell calculationCell21 = new CalculationCell(){ CellReference = "G32" };
CalculationCell calculationCell22 = new CalculationCell(){ CellReference = "H32" };
CalculationCell calculationCell23 = new CalculationCell(){ CellReference = "I32" };
CalculationCell calculationCell24 = new CalculationCell(){ CellReference = "J32" };
CalculationCell calculationCell25 = new CalculationCell(){ CellReference = "C37" };
CalculationCell calculationCell26 = new CalculationCell(){ CellReference = "D37" };
CalculationCell calculationCell27 = new CalculationCell(){ CellReference = "E37" };
CalculationCell calculationCell28 = new CalculationCell(){ CellReference = "F37" };
CalculationCell calculationCell29 = new CalculationCell(){ CellReference = "G37" };
CalculationCell calculationCell30 = new CalculationCell(){ CellReference = "H37" };
CalculationCell calculationCell31 = new CalculationCell(){ CellReference = "I37" };
CalculationCell calculationCell32 = new CalculationCell(){ CellReference = "J37" };
CalculationCell calculationCell33 = new CalculationCell(){ CellReference = "C42" };
CalculationCell calculationCell34 = new CalculationCell(){ CellReference = "D42" };
CalculationCell calculationCell35 = new CalculationCell(){ CellReference = "E42" };
CalculationCell calculationCell36 = new CalculationCell(){ CellReference = "F42" };
CalculationCell calculationCell37 = new CalculationCell(){ CellReference = "G42" };
CalculationCell calculationCell38 = new CalculationCell(){ CellReference = "H42" };
CalculationCell calculationCell39 = new CalculationCell(){ CellReference = "I42" };
CalculationCell calculationCell40 = new CalculationCell(){ CellReference = "J42" };
CalculationCell calculationCell41 = new CalculationCell(){ CellReference = "I44" };
CalculationCell calculationCell42 = new CalculationCell(){ CellReference = "G45" };
CalculationCell calculationCell43 = new CalculationCell(){ CellReference = "G46" };
CalculationCell calculationCell44 = new CalculationCell(){ CellReference = "I46" };
CalculationCell calculationCell45 = new CalculationCell(){ CellReference = "G47" };
CalculationCell calculationCell46 = new CalculationCell(){ CellReference = "G48" };
CalculationCell calculationCell47 = new CalculationCell(){ CellReference = "I48" };
CalculationCell calculationCell48 = new CalculationCell(){ CellReference = "G49" };
CalculationCell calculationCell49 = new CalculationCell(){ CellReference = "I49" };
CalculationCell calculationCell50 = new CalculationCell(){ CellReference = "G50" };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChain1.Append(calculationCell3);
calculationChain1.Append(calculationCell4);
calculationChain1.Append(calculationCell5);
calculationChain1.Append(calculationCell6);
calculationChain1.Append(calculationCell7);
calculationChain1.Append(calculationCell8);
calculationChain1.Append(calculationCell9);
calculationChain1.Append(calculationCell10);
calculationChain1.Append(calculationCell11);
calculationChain1.Append(calculationCell12);
calculationChain1.Append(calculationCell13);
calculationChain1.Append(calculationCell14);
calculationChain1.Append(calculationCell15);
calculationChain1.Append(calculationCell16);
calculationChain1.Append(calculationCell17);
calculationChain1.Append(calculationCell18);
calculationChain1.Append(calculationCell19);
calculationChain1.Append(calculationCell20);
calculationChain1.Append(calculationCell21);
calculationChain1.Append(calculationCell22);
calculationChain1.Append(calculationCell23);
calculationChain1.Append(calculationCell24);
calculationChain1.Append(calculationCell25);
calculationChain1.Append(calculationCell26);
calculationChain1.Append(calculationCell27);
calculationChain1.Append(calculationCell28);
calculationChain1.Append(calculationCell29);
calculationChain1.Append(calculationCell30);
calculationChain1.Append(calculationCell31);
calculationChain1.Append(calculationCell32);
calculationChain1.Append(calculationCell33);
calculationChain1.Append(calculationCell34);
calculationChain1.Append(calculationCell35);
calculationChain1.Append(calculationCell36);
calculationChain1.Append(calculationCell37);
calculationChain1.Append(calculationCell38);
calculationChain1.Append(calculationCell39);
calculationChain1.Append(calculationCell40);
calculationChain1.Append(calculationCell41);
calculationChain1.Append(calculationCell42);
calculationChain1.Append(calculationCell43);
calculationChain1.Append(calculationCell44);
calculationChain1.Append(calculationCell45);
calculationChain1.Append(calculationCell46);
calculationChain1.Append(calculationCell47);
calculationChain1.Append(calculationCell48);
calculationChain1.Append(calculationCell49);
calculationChain1.Append(calculationCell50);
calculationChainPart1.CalculationChain = calculationChain1;
}
// Generates content of calculationChainPart1.
private void GenerateCalculationChainPart1Content(CalculationChainPart calculationChainPart1)
{
CalculationChain calculationChain1 = new CalculationChain();
CalculationCell calculationCell1 = new CalculationCell() { CellReference = "I49", SheetId = 1 };
CalculationCell calculationCell2 = new CalculationCell() { CellReference = "I48" };
CalculationCell calculationCell3 = new CalculationCell() { CellReference = "I46" };
CalculationCell calculationCell4 = new CalculationCell() { CellReference = "I44" };
CalculationCell calculationCell5 = new CalculationCell() { CellReference = "J42" };
CalculationCell calculationCell6 = new CalculationCell() { CellReference = "J37" };
CalculationCell calculationCell7 = new CalculationCell() { CellReference = "J32" };
CalculationCell calculationCell8 = new CalculationCell() { CellReference = "J27" };
CalculationCell calculationCell9 = new CalculationCell() { CellReference = "J22" };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChain1.Append(calculationCell3);
calculationChain1.Append(calculationCell4);
calculationChain1.Append(calculationCell5);
calculationChain1.Append(calculationCell6);
calculationChain1.Append(calculationCell7);
calculationChain1.Append(calculationCell8);
calculationChain1.Append(calculationCell9);
calculationChainPart1.CalculationChain = calculationChain1;
}
