diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 68bc0ca..6f06459 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -237,7 +237,7 @@ formula = cell.CellFormula.Text; xlCell.FormulaA1 = formula; - sharedFormulasR1C1.Add(cell.CellFormula.SharedIndex.Value, formula); + sharedFormulasR1C1.Add(cell.CellFormula.SharedIndex.Value, xlCell.FormulaR1C1); } else if (dCell.CellFormula != null) { diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 03b9dda..556d636 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -15,46 +15,46 @@ static void Main(string[] args) { - //var fileName = "Issue_6285"; + var fileName = "Issue_6364"; //var fileName = "Blank"; - var fileName = "Sandbox"; - //var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); + //var fileName = "Sandbox"; + var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Shifting Formulas"); - ws.Cell("B2").Value = 5; - ws.Cell("B3").Value = 6; - ws.Cell("C2").Value = 1; - ws.Cell("C3").Value = 2; - ws.Cell("A4").Value = "Sum:"; - ws.Range("B4:C4").FormulaR1C1 = "Sum(R[-2]C:R[-1]C)"; - ws.Cell("E2").Value = "Avg:"; + //var wb = new XLWorkbook(); + //var ws = wb.Worksheets.Add("Shifting Formulas"); + //ws.Cell("B2").Value = 5; + //ws.Cell("B3").Value = 6; + //ws.Cell("C2").Value = 1; + //ws.Cell("C3").Value = 2; + //ws.Cell("A4").Value = "Sum:"; + //ws.Range("B4:C4").FormulaR1C1 = "Sum(R[-2]C:R[-1]C)"; + //ws.Cell("E2").Value = "Avg:"; - ws.Cell("F2").FormulaA1 = "Average(B2:C3)"; - ws.Ranges("A4,E2").Style - .Font.SetBold() - .Fill.SetBackgroundColor(XLColor.CyanProcess); + //ws.Cell("F2").FormulaA1 = "Average(B2:C3)"; + //ws.Ranges("A4,E2").Style + // .Font.SetBold() + // .Fill.SetBackgroundColor(XLColor.CyanProcess); - var ws2 = wb.Worksheets.Add("WS2"); - ws2.Cell(1, 1).FormulaA1 = "='Shifting Formulas'!B2"; - ws2.Cell(1, 2).Value = ws2.Cell(1, 1).Value; - ws2.Cell(2, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C$3)"; - ws2.Cell(3, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C3)"; - ws2.Cell(4, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:C3)"; - ws2.Cell(5, 1).FormulaA1 = "Average('Shifting Formulas'!$B2:C3)"; - ws2.Cell(6, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C3)"; - ws2.Cell(7, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C$3)"; - ws2.Cell(8, 1).FormulaA1 = "Average('Shifting Formulas'!B2:$C$3)"; - ws2.Cell(9, 1).FormulaA1 = "Average('Shifting Formulas'!B$2:$C$3)"; + //var ws2 = wb.Worksheets.Add("WS2"); + //ws2.Cell(1, 1).FormulaA1 = "='Shifting Formulas'!B2"; + //ws2.Cell(1, 2).Value = ws2.Cell(1, 1).Value; + //ws2.Cell(2, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C$3)"; + //ws2.Cell(3, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C3)"; + //ws2.Cell(4, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:C3)"; + //ws2.Cell(5, 1).FormulaA1 = "Average('Shifting Formulas'!$B2:C3)"; + //ws2.Cell(6, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C3)"; + //ws2.Cell(7, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C$3)"; + //ws2.Cell(8, 1).FormulaA1 = "Average('Shifting Formulas'!B2:$C$3)"; + //ws2.Cell(9, 1).FormulaA1 = "Average('Shifting Formulas'!B$2:$C$3)"; - var dataGrid = ws.Range("B2:D3"); - ws.Row(1).InsertRowsAbove(1); - var newRow = dataGrid.LastRow().InsertRowsAbove(1).First(); - newRow.Value = 1; - dataGrid.LastColumn().FormulaR1C1 = String.Format("SUM(RC[-{0}]:RC[-1])", dataGrid.ColumnCount() - 1); - ws.Cell(1, 1).InsertCellsBelow(1); - ws.Column(1).InsertColumnsBefore(1); - ws.Row(4).Delete(); + //var dataGrid = ws.Range("B2:D3"); + //ws.Row(1).InsertRowsAbove(1); + //var newRow = dataGrid.LastRow().InsertRowsAbove(1).First(); + //newRow.Value = 1; + //dataGrid.LastColumn().FormulaR1C1 = String.Format("SUM(RC[-{0}]:RC[-1])", dataGrid.ColumnCount() - 1); + //ws.Cell(1, 1).InsertCellsBelow(1); + //ws.Column(1).InsertColumnsBefore(1); + //ws.Row(4).Delete(); wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); }