diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index a2420fa..9fa2aa3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1672,360 +1672,6 @@ tableDefinitionPart.Table = table; } - //private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, XLWorksheet worksheet) - //{ - // if (drawingsPart.WorksheetDrawing == null) - // drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); - - // var worksheetDrawing = drawingsPart.WorksheetDrawing; - - // if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"))) - // worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); - // if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair("a", "http://schemas.openxmlformats.org/drawingml/2006/main"))) - // worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); - - // foreach (var chart in worksheet.Charts.OrderBy(c => c.ZOrder).Select(c => c)) - // { - // Xdr.TwoCellAnchor twoCellAnchor = new Xdr.TwoCellAnchor(); - // worksheetDrawing.AppendChild(twoCellAnchor); - // if (chart.Anchor == XLDrawingAnchor.MoveAndSizeWithCells) - // twoCellAnchor.EditAs = Xdr.EditAsValues.TwoCell; - // else if (chart.Anchor == XLDrawingAnchor.MoveWithCells) - // twoCellAnchor.EditAs = Xdr.EditAsValues.OneCell; - // else - // twoCellAnchor.EditAs = Xdr.EditAsValues.Absolute; - - // if (twoCellAnchor.FromMarker == null) - // twoCellAnchor.FromMarker = new Xdr.FromMarker(); - // twoCellAnchor.FromMarker.RowId = new Xdr.RowId((chart.FirstRow - 1).ToString()); - // twoCellAnchor.FromMarker.RowOffset = new Xdr.RowOffset(chart.FirstRowOffset.ToString()); - // twoCellAnchor.FromMarker.ColumnId = new Xdr.ColumnId((chart.FirstColumn - 1).ToString()); - // twoCellAnchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(chart.FirstColumnOffset.ToString()); - - // if (twoCellAnchor.ToMarker == null) - // twoCellAnchor.ToMarker = new Xdr.ToMarker(); - // twoCellAnchor.ToMarker.RowId = new Xdr.RowId((chart.LastRow - 1).ToString()); - // twoCellAnchor.ToMarker.RowOffset = new Xdr.RowOffset(chart.LastRowOffset.ToString()); - // twoCellAnchor.ToMarker.ColumnId = new Xdr.ColumnId((chart.LastColumn - 1).ToString()); - // twoCellAnchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(chart.LastColumnOffset.ToString()); - - // Xdr.GraphicFrame graphicFrame = new Xdr.GraphicFrame(); - // twoCellAnchor.AppendChild(graphicFrame); - - // if (graphicFrame.NonVisualGraphicFrameProperties == null) - // graphicFrame.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties(); - - // if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties == null) - // graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = (UInt32)chart.Id, Name = chart.Name, Description = chart.Description, Hidden = chart.Hidden }; - // if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties == null) - // graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties(); - - // if (graphicFrame.Transform == null) - // graphicFrame.Transform = new Xdr.Transform(); - - // if (chart.HorizontalFlip) - // graphicFrame.Transform.HorizontalFlip = true; - // else - // graphicFrame.Transform.HorizontalFlip = null; - - // if (chart.VerticalFlip) - // graphicFrame.Transform.VerticalFlip = true; - // else - // graphicFrame.Transform.VerticalFlip = null; - - // if (chart.Rotation != 0) - // graphicFrame.Transform.Rotation = chart.Rotation; - // else - // graphicFrame.Transform.Rotation = null; - - // if (graphicFrame.Transform.Offset == null) - // graphicFrame.Transform.Offset = new A.Offset(); - - // graphicFrame.Transform.Offset.X = chart.OffsetX; - // graphicFrame.Transform.Offset.Y = chart.OffsetY; - - // if (graphicFrame.Transform.Extents == null) - // graphicFrame.Transform.Extents = new A.Extents(); - - // graphicFrame.Transform.Extents.Cx = chart.ExtentLength; - // graphicFrame.Transform.Extents.Cy = chart.ExtentWidth; - - // if (graphicFrame.Graphic == null) - // graphicFrame.Graphic = new A.Graphic(); - - // if (graphicFrame.Graphic.GraphicData == null) - // graphicFrame.Graphic.GraphicData = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }; - - // if (!graphicFrame.Graphic.GraphicData.Elements().Any()) - // { - // C.ChartReference chartReference = new C.ChartReference() { Id = "rId" + chart.Id.ToStringLookup() }; - // chartReference.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); - // chartReference.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - - // graphicFrame.Graphic.GraphicData.AppendChild(chartReference); - // } - - // if (!twoCellAnchor.Elements().Any()) - // twoCellAnchor.AppendChild(new Xdr.ClientData()); - // } - //} - - //private void GenerateChartPartContent(ChartPart chartPart, XLChart xlChart) - //{ - // if (chartPart.ChartSpace == null) - // chartPart.ChartSpace = new C.ChartSpace(); - - // C.ChartSpace chartSpace = chartPart.ChartSpace; - - // if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"))) - // chartSpace.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); - // if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair("a", "http://schemas.openxmlformats.org/drawingml/2006/main"))) - // chartSpace.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); - // if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) - // chartSpace.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - - // if (chartSpace.EditingLanguage == null) - // chartSpace.EditingLanguage = new C.EditingLanguage() { Val = CultureInfo.CurrentCulture.Name }; - // else - // chartSpace.EditingLanguage.Val = CultureInfo.CurrentCulture.Name; - - // C.Chart chart = new C.Chart(); - // chartSpace.AppendChild(chart); - - // if (chart.Title == null) - // chart.Title = new C.Title(); - - // if (chart.Title.Layout == null) - // chart.Title.Layout = new C.Layout(); - - // if (chart.View3D == null) - // chart.View3D = new C.View3D(); - - // if (chart.View3D.RightAngleAxes == null) - // chart.View3D.RightAngleAxes = new C.RightAngleAxes(); - - // chart.View3D.RightAngleAxes.Val = xlChart.RightAngleAxes; - - // if (chart.PlotArea == null) - // chart.PlotArea = new C.PlotArea(); - - // if (chart.PlotArea.Layout == null) - // chart.PlotArea.Layout = new C.Layout(); - - // OpenXmlElement chartElement = GetChartElement(xlChart); - - // chart.PlotArea.AppendChild(chartElement); - - // C.CategoryAxis categoryAxis1 = new C.CategoryAxis(); - // C.AxisId axisId4 = new C.AxisId() { Val = (UInt32Value)71429120U }; - - // C.Scaling scaling1 = new C.Scaling(); - // C.Orientation orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax }; - - // scaling1.AppendChild(orientation1); - // C.AxisPosition axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom }; - // C.TickLabelPosition tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }; - // C.CrossingAxis crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)71432064U }; - // C.Crosses crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero }; - // C.AutoLabeled autoLabeled1 = new C.AutoLabeled() { Val = true }; - // C.LabelAlignment labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center }; - // C.LabelOffset labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U }; - - // categoryAxis1.AppendChild(axisId4); - // categoryAxis1.AppendChild(scaling1); - // categoryAxis1.AppendChild(axisPosition1); - // categoryAxis1.AppendChild(tickLabelPosition1); - // categoryAxis1.AppendChild(crossingAxis1); - // categoryAxis1.AppendChild(crosses1); - // categoryAxis1.AppendChild(autoLabeled1); - // categoryAxis1.AppendChild(labelAlignment1); - // categoryAxis1.AppendChild(labelOffset1); - - // C.ValueAxis valueAxis1 = new C.ValueAxis(); - // C.AxisId axisId5 = new C.AxisId() { Val = (UInt32Value)71432064U }; - - // C.Scaling scaling2 = new C.Scaling(); - // C.Orientation orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax }; - - // scaling2.AppendChild(orientation2); - // C.AxisPosition axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Left }; - // C.MajorGridlines majorGridlines1 = new C.MajorGridlines(); - // C.NumberingFormat numberingFormat1 = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true }; - // C.TickLabelPosition tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }; - // C.CrossingAxis crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)71429120U }; - // C.Crosses crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero }; - // C.CrossBetween crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.Between }; - - // valueAxis1.AppendChild(axisId5); - // valueAxis1.AppendChild(scaling2); - // valueAxis1.AppendChild(axisPosition2); - // valueAxis1.AppendChild(majorGridlines1); - // valueAxis1.AppendChild(numberingFormat1); - // valueAxis1.AppendChild(tickLabelPosition2); - // valueAxis1.AppendChild(crossingAxis2); - // valueAxis1.AppendChild(crosses2); - // valueAxis1.AppendChild(crossBetween1); - - // plotArea.AppendChild(bar3DChart1); - // plotArea.AppendChild(categoryAxis1); - // plotArea.AppendChild(valueAxis1); - - // C.Legend legend1 = new C.Legend(); - // C.LegendPosition legendPosition1 = new C.LegendPosition() { Val = C.LegendPositionValues.Right }; - // C.Layout layout3 = new C.Layout(); - - // legend1.AppendChild(legendPosition1); - // legend1.AppendChild(layout3); - // C.PlotVisibleOnly plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true }; - - // chart.AppendChild(legend1); - // chart.AppendChild(plotVisibleOnly1); - - // C.PrintSettings printSettings1 = new C.PrintSettings(); - // C.HeaderFooter headerFooter1 = new C.HeaderFooter(); - // C.PageMargins pageMargins4 = new C.PageMargins() { Left = 0.70000000000000018D, Right = 0.70000000000000018D, Top = 0.75000000000000022D, Bottom = 0.75000000000000022D, Header = 0.3000000000000001D, Footer = 0.3000000000000001D }; - // C.PageSetup pageSetup1 = new C.PageSetup(); - - // printSettings1.AppendChild(headerFooter1); - // printSettings1.AppendChild(pageMargins4); - // printSettings1.AppendChild(pageSetup1); - - // chartSpace.AppendChild(printSettings1); - - //} - - //private OpenXmlElement GetChartElement(XLChart xlChart) - //{ - // if (xlChart.ChartTypeCategory == XLChartTypeCategory.Bar3D) - // return GetBar3DChart(xlChart); - // else - // return null; - //} - - //private OpenXmlElement GetBar3DChart(XLChart xlChart) - //{ - - // C.Bar3DChart bar3DChart = new C.Bar3DChart(); - // bar3DChart.BarDirection = new C.BarDirection() { Val = GetBarDirection(xlChart) }; - // bar3DChart.BarGrouping = new C.BarGrouping() { Val = GetBarGrouping(xlChart) }; - - // C.BarChartSeries barChartSeries = new C.BarChartSeries(); - // barChartSeries.Index = new C.Index() { Val = (UInt32Value)0U }; - // barChartSeries.Order = new C.Order() { Val = (UInt32Value)0U }; - - // C.SeriesText seriesText1 = new C.SeriesText(); - - // C.StringReference stringReference1 = new C.StringReference(); - // C.Formula formula1 = new C.Formula(); - // formula1.Text = "Sheet1!$B$1"; - - // stringReference1.AppendChild(formula1); - - // seriesText1.AppendChild(stringReference1); - - // C.CategoryAxisData categoryAxisData1 = new C.CategoryAxisData(); - - // C.StringReference stringReference2 = new C.StringReference(); - // C.Formula formula2 = new C.Formula(); - // formula2.Text = "Sheet1!$A$2:$A$3"; - - // C.StringCache stringCache2 = new C.StringCache(); - // C.PointCount pointCount2 = new C.PointCount() { Val = (UInt32Value)2U }; - - // C.StringPoint stringPoint2 = new C.StringPoint() { Index = (UInt32Value)0U }; - // C.NumericValue numericValue2 = new C.NumericValue(); - // numericValue2.Text = "A"; - - // stringPoint2.AppendChild(numericValue2); - - // C.StringPoint stringPoint3 = new C.StringPoint() { Index = (UInt32Value)1U }; - // C.NumericValue numericValue3 = new C.NumericValue(); - // numericValue3.Text = "B"; - - // stringPoint3.AppendChild(numericValue3); - - // stringCache2.AppendChild(pointCount2); - // stringCache2.AppendChild(stringPoint2); - // stringCache2.AppendChild(stringPoint3); - - // stringReference2.AppendChild(formula2); - // stringReference2.AppendChild(stringCache2); - - // categoryAxisData1.AppendChild(stringReference2); - - // C.Values values1 = new C.Values(); - - // C.NumberReference numberReference1 = new C.NumberReference(); - // C.Formula formula3 = new C.Formula(); - // formula3.Text = "Sheet1!$B$2:$B$3"; - - // C.NumberingCache numberingCache1 = new C.NumberingCache(); - // C.FormatCode formatCode1 = new C.FormatCode(); - // formatCode1.Text = "General"; - // C.PointCount pointCount3 = new C.PointCount() { Val = (UInt32Value)2U }; - - // C.NumericPoint numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)0U }; - // C.NumericValue numericValue4 = new C.NumericValue(); - // numericValue4.Text = "5"; - - // numericPoint1.AppendChild(numericValue4); - - // C.NumericPoint numericPoint2 = new C.NumericPoint() { Index = (UInt32Value)1U }; - // C.NumericValue numericValue5 = new C.NumericValue(); - // numericValue5.Text = "10"; - - // numericPoint2.AppendChild(numericValue5); - - // numberingCache1.AppendChild(formatCode1); - // numberingCache1.AppendChild(pointCount3); - // numberingCache1.AppendChild(numericPoint1); - // numberingCache1.AppendChild(numericPoint2); - - // numberReference1.AppendChild(formula3); - // numberReference1.AppendChild(numberingCache1); - - // values1.AppendChild(numberReference1); - - // barChartSeries.AppendChild(index1); - // barChartSeries.AppendChild(order1); - // barChartSeries.AppendChild(seriesText1); - // barChartSeries.AppendChild(categoryAxisData1); - // barChartSeries.AppendChild(values1); - // C.Shape shape1 = new C.Shape() { Val = C.ShapeValues.Box }; - // C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)71429120U }; - // C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)71432064U }; - // C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)0U }; - - // bar3DChart.AppendChild(barChartSeries); - // bar3DChart.AppendChild(shape1); - // bar3DChart.AppendChild(axisId1); - // bar3DChart.AppendChild(axisId2); - // bar3DChart.AppendChild(axisId3); - - // return bar3DChart; - //} - - //private C.BarGroupingValues GetBarGrouping(XLChart xlChart) - //{ - // if (xlChart.BarGrouping == XLBarGrouping.Clustered) - // return C.BarGroupingValues.Clustered; - // else if (xlChart.BarGrouping == XLBarGrouping.Percent) - // return C.BarGroupingValues.PercentStacked; - // else if (xlChart.BarGrouping == XLBarGrouping.Stacked) - // return C.BarGroupingValues.Stacked; - // else - // return C.BarGroupingValues.Standard; - //} - - //private C.BarDirectionValues GetBarDirection(XLChart xlChart) - //{ - // if (xlChart.BarOrientation == XLBarOrientation.Vertical) - // return C.BarDirectionValues.Column; - // else - // return C.BarDirectionValues.Bar; - //} - //-- - private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context) @@ -4082,7 +3728,7 @@ if (opCell.FormulaReference == null) opCell.FormulaReference = opCell.AsRange().RangeAddress; - if ( opCell.FormulaReference.FirstAddress.Equals(opCell.Address)) + if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address)) { f.Text = formula; f.Reference = opCell.FormulaReference.ToStringRelative(); @@ -4091,7 +3737,10 @@ cell.CellFormula = f; } else - cell.CellFormula = new CellFormula(formula); + { + cell.CellFormula = new CellFormula(); + cell.CellFormula.Text = formula; + } cell.CellValue = null; } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 78668d7..f28b5dd 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -16,29 +16,13 @@ private static void Main(string[] args) { var wb = new XLWorkbook(); - foreach (var sheetNum in Enumerable.Range(1, 1)) - { - CreateSheet(wb, sheetNum); - Console.WriteLine("Sheet " + sheetNum); - } + var ws = wb.AddWorksheet("Sheet"); + ws.FirstCell().SetValue(1) + .CellBelow().SetFormulaA1("IF(A1>0,Yes,No)") // Invalid + .CellBelow().SetFormulaA1("IF(A1>0,\"Yes\",\"No\")") // OK + .CellBelow().SetFormulaA1("IF(A1>0,TRUE,FALSE)"); // OK wb.SaveAs(@"c:\temp\saved.xlsx"); Console.WriteLine("Done"); } - - private static void CreateSheet(XLWorkbook wb, Int32 sheetNum) - { - using (var ws = wb.AddWorksheet("Sheet " + sheetNum)) - { - foreach (var ro in Enumerable.Range(1, 1000)) - { - foreach (var co in Enumerable.Range(1, 100)) - { - ws.Cell(ro, co).Value = ro + co; - } - } - ws.Dispose(); - } - } - } }