public void Test46535()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("46535.xlsx");
CalculationChain chain = wb.GetCalculationChain();
//the bean holding the reference to the formula to be deleted
CT_CalcCell c = chain.GetCTCalcChain().GetCArray(0);
int cnt = chain.GetCTCalcChain().c.Count;
Assert.AreEqual(10, c.i);
Assert.AreEqual("E1", c.r);
ISheet sheet = wb.GetSheet("Test");
ICell cell = sheet.GetRow(0).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
cell.SetCellFormula(null);
//the count of items is less by one
c = chain.GetCTCalcChain().GetCArray(0);
int cnt2 = chain.GetCTCalcChain().c.Count;
Assert.AreEqual(cnt - 1, cnt2);
//the first item in the calculation chain is the former second one
Assert.AreEqual(10, c.i);
Assert.AreEqual("C1", c.r);
Assert.AreEqual(CellType.String, cell.CellType);
cell.SetCellValue("ABC");
Assert.AreEqual(CellType.String, cell.CellType);
}
private void OnSheetDelete(int index)
{
this.workbook.sheets.RemoveSheet(index);
if (this.calcChain != null)
{
this.RemoveRelation((POIXMLDocumentPart)this.calcChain);
this.calcChain = (CalculationChain)null;
}
List <XSSFName> xssfNameList = new List <XSSFName>();
foreach (XSSFName namedRange in this.namedRanges)
{
CT_DefinedName ctName = namedRange.GetCTName();
if (ctName.IsSetLocalSheetId())
{
if ((long)ctName.localSheetId == (long)index)
{
xssfNameList.Add(namedRange);
}
else if ((long)ctName.localSheetId > (long)index)
{
--ctName.localSheetId;
ctName.localSheetIdSpecified = true;
}
}
}
foreach (XSSFName xssfName in xssfNameList)
{
this.namedRanges.Remove(xssfName);
}
}
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);
}
}
/**
* update cell references when Shifting rows
*
* @param n the number of rows to move
*/
internal void Shift(int n)
{
int rownum = RowNum + n;
CalculationChain calcChain = ((XSSFWorkbook)_sheet.Workbook).GetCalculationChain();
int sheetId = (int)_sheet.sheet.sheetId;
String msg = "Row[rownum=" + RowNum + "] contains cell(s) included in a multi-cell array formula. " +
"You cannot change part of an array.";
foreach (ICell c in this)
{
XSSFCell cell = (XSSFCell)c;
if (cell.IsPartOfArrayFormulaGroup)
{
cell.NotifyArrayFormulaChanging(msg);
}
//remove the reference in the calculation chain
if (calcChain != null)
{
calcChain.RemoveItem(sheetId, cell.GetReference());
}
CT_Cell CT_Cell = cell.GetCTCell();
String r = new CellReference(rownum, cell.ColumnIndex).FormatAsString();
CT_Cell.r = r;
}
RowNum = rownum;
}
private void SaveCalculationChain()
{
if (this.calcChain == null || this.calcChain.GetCTCalcChain().SizeOfCArray() != 0)
{
return;
}
this.RemoveRelation((POIXMLDocumentPart)this.calcChain);
this.calcChain = (CalculationChain)null;
}
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 void Shift(int n)
{
int pRow = this.RowNum + n;
CalculationChain calculationChain = ((XSSFWorkbook)this._sheet.Workbook).GetCalculationChain();
int sheetId = (int)this._sheet.sheet.sheetId;
string msg = "Row[rownum=" + (object)this.RowNum + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
foreach (XSSFCell xssfCell in this)
{
if (xssfCell.IsPartOfArrayFormulaGroup)
{
xssfCell.NotifyArrayFormulaChanging(msg);
}
calculationChain?.RemoveItem(sheetId, xssfCell.GetReference());
xssfCell.GetCTCell().r = new CellReference(pRow, xssfCell.ColumnIndex).FormatAsString();
}
this.RowNum = pRow;
}
public void RemoveAllFormulas()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("TwoFunctions.xlsx");
CalculationChain chain = wb.GetCalculationChain();
//the bean holding the reference to the formula to be deleted
CT_CalcCell c = chain.GetCTCalcChain().GetCArray(0);
int cnt = chain.GetCTCalcChain().c.Count;
Assert.AreEqual(1, c.i);
Assert.AreEqual("A5", c.r);
Assert.AreEqual(2, cnt);
ISheet sheet = wb.GetSheet("Sheet1");
ICell cell = sheet.GetRow(4).GetCell(0);
Assert.AreEqual(CellType.Formula, cell.CellType);
cell.SetCellFormula(null);
//the count of items is less by one
c = chain.GetCTCalcChain().GetCArray(0);
int cnt2 = chain.GetCTCalcChain().c.Count;
Assert.AreEqual(cnt - 1, cnt2);
//the first item in the calculation chain is the former second one
Assert.AreEqual(1, c.i);
Assert.AreEqual("A4", c.r);
Assert.AreEqual(1, cnt2);
//remove final formula from spread sheet
ICell cell2 = sheet.GetRow(3).GetCell(0);
Assert.AreEqual(CellType.Formula, cell2.CellType);
cell2.SetCellFormula(null);
//the count of items within the chain should be 0
int cnt3 = chain.GetCTCalcChain().c.Count;
Assert.AreEqual(0, cnt3);
}
// 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 = "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;
}
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 = "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;
}
// 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 = "G20", SheetId = 2 };
CalculationCell calculationCell2 = new CalculationCell() { CellReference = "G21" };
calculationChain1.Append(calculationCell1);
calculationChain1.Append(calculationCell2);
calculationChainPart1.CalculationChain = calculationChain1;
}
internal override void OnDocumentRead()
{
try
{
this.workbook = WorkbookDocument.Parse(this.GetPackagePart().GetInputStream()).Workbook;
Dictionary <string, XSSFSheet> dictionary = new Dictionary <string, XSSFSheet>();
foreach (POIXMLDocumentPart relation in this.GetRelations())
{
if (relation is SharedStringsTable)
{
this.sharedStringSource = (SharedStringsTable)relation;
}
else if (relation is StylesTable)
{
this.stylesSource = (StylesTable)relation;
}
else if (relation is ThemesTable)
{
this.theme = (ThemesTable)relation;
}
else if (relation is CalculationChain)
{
this.calcChain = (CalculationChain)relation;
}
else if (relation is MapInfo)
{
this.mapInfo = (MapInfo)relation;
}
else if (relation is XSSFSheet)
{
dictionary.Add(relation.GetPackageRelationship().Id, (XSSFSheet)relation);
}
}
if (this.stylesSource != null)
{
this.stylesSource.SetTheme(this.theme);
}
if (this.sharedStringSource == null)
{
this.sharedStringSource = (SharedStringsTable)this.CreateRelationship((POIXMLRelation)XSSFRelation.SHARED_STRINGS, (POIXMLFactory)XSSFFactory.GetInstance());
}
this.sheets = new List <XSSFSheet>(dictionary.Count);
foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet)
{
XSSFSheet xssfSheet = dictionary[ctSheet.id];
if (xssfSheet == null)
{
XSSFWorkbook.logger.Log(5, (object)("Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping"));
}
else
{
xssfSheet.sheet = ctSheet;
xssfSheet.OnDocumentRead();
this.sheets.Add(xssfSheet);
}
}
this.namedRanges = new List <XSSFName>();
if (!this.workbook.IsSetDefinedNames())
{
return;
}
foreach (CT_DefinedName name in this.workbook.definedNames.definedName)
{
this.namedRanges.Add(new XSSFName(name, this));
}
}
catch (XmlException ex)
{
throw new POIXMLException((Exception)ex);
}
}
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();
}
public void WriteCalculation(CalculationChain calculationChain)
{
XElement calculationChainElement = new XElement(ExcelCommon.Schema_WorkBook_Main + "calcChain");
if (calculationChain.CalculationCells.Count > 0)
{
foreach (var c in calculationChain.CalculationCells)
{
XElement t = new XElement("c");
c.Cell.IsNotWhiteSpace(o => t.Add(new XAttribute("r", ((string)o))));
c.WorkSheet.IsNotWhiteSpace(o => t.Add(new XAttribute("i", ((string)o))));
c.NewLevel.IsNotWhiteSpace(o => t.Add(new XAttribute("l", ((string)o))));
c.InChildChain.IsNotWhiteSpace(o => t.Add(new XAttribute("s", ((string)o))));
calculationChainElement.Add(t);
}
PackagePart calculationChainPart = this.Context.Package.CreatePart(ExcelCommon.Uri_CalculationChain,
ExcelCommon.ContentType_CalculationChain, CompressionOption.Maximum);
XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"), calculationChainElement);
using (Stream stream = calculationChainPart.GetStream(FileMode.Create, FileAccess.Write))
{
doc.Save(stream);
stream.Flush();
}
}
}
// 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 = "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;
}
