private static void AddRows(OpenXmlCompositeElement sheet, int maxCount, int columns) { var maxIndex = sheet.Elements <Row>() .Select(r => r.RowIndex.Value).Max(); var count = sheet.Elements <Row>() .Count(); for (var i = count; i <= maxCount; i++) { var row = new Row { RowIndex = ++maxIndex }; for (var j = 1; j < columns + 1; j++) { var cell = new Cell { DataType = CellValues.String, CellValue = new CellValue(string.Empty), CellReference = GetExcelColumnName(j) + row.RowIndex }; row.AppendChild(cell); } sheet.AppendChild(row); } }
private IEnumerable <T> SetChartSeries <T>(OpenXmlCompositeElement chart, int newSeriesCount, bool addNewIfRequired) where T : OpenXmlCompositeElement { var seriesList = chart.Elements <T>(); int currentSeriesCount = seriesList.Count(); if (currentSeriesCount > newSeriesCount) { //chart on template has more series than in the data, remove last x series int seriesToRemove = currentSeriesCount - newSeriesCount; for (int i = 0; i < seriesToRemove; i++) { chart.RemoveChild <T>(seriesList.Last()); } seriesList = chart.Elements <T>(); } else if (addNewIfRequired && currentSeriesCount < newSeriesCount) { //chart on the template has fewer series so we need to add some by clonning the last one for (int i = 0; i < newSeriesCount - currentSeriesCount; i++) { var lastSeries = chart.Elements <T>().Last(); var seriesClone = (T)lastSeries.CloneNode(true); var lastSeriesIndex = lastSeries.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Index>(); var seriesCloneIndex = seriesClone.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Index>(); seriesCloneIndex.Val = new UInt32Value(lastSeriesIndex.Val.Value + 1); var lastSeriesOrder = lastSeries.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Order>(); var seriesCloneOrder = seriesClone.FirstElement <DocumentFormat.OpenXml.Drawing.Charts.Order>(); seriesCloneOrder.Val = new UInt32Value(lastSeriesOrder.Val.Value + 1); chart.InsertAfter(seriesClone, lastSeries); } seriesList = chart.Elements <T>(); } return(chart.Elements <T>()); }
private Tuple <int, int> GetFixedDataRange <T>(OpenXmlCompositeElement chart) where T : OpenXmlCompositeElement { int columns = chart.Elements <T>().Count(); var values = chart.Elements <T>().First().FirstElement <Values>(); int rows = (int)values.NumberReference.NumberingCache.PointCount.Val.Value; return(new Tuple <int, int>(columns, rows)); }
internal static OpenXmlElement Find <T>(this OpenXmlCompositeElement element) where T : OpenXmlElement, new() { if (element.Elements <T>().Count() > 0) { return(element.Elements <T>().First()); } return(null); }
FromCompositeElement( OpenXmlCompositeElement compositeElement) // TODO: set annotation that about it cannot be NULL { // Get <a:lvlXpPr> elements, eg. <a:lvl1pPr>, <a:lvl2pPr> IEnumerable <OpenXmlElement> lvlParagraphPropertyList = compositeElement.Elements() .Where(e => e.LocalName.StartsWith("lvl", StringComparison.Ordinal)); var lvlToFontData = new Dictionary <int, FontData>(); foreach (OpenXmlElement textPr in lvlParagraphPropertyList) { A.DefaultRunProperties aDefRPr = textPr.GetFirstChild <A.DefaultRunProperties>(); Int32Value fontSize = aDefRPr?.FontSize; BooleanValue isBold = aDefRPr?.Bold; BooleanValue isItalic = aDefRPr?.Italic; A.LatinFont aLatinFont = aDefRPr?.GetFirstChild <A.LatinFont>(); A.RgbColorModelHex aRgbColorModelHex = aDefRPr?.GetFirstChild <A.SolidFill>()?.RgbColorModelHex; A.SchemeColor aSchemeColor = aDefRPr?.GetFirstChild <A.SolidFill>()?.SchemeColor; #if NETSTANDARD2_0 var lvl = int.Parse(textPr.LocalName[3].ToString(System.Globalization.CultureInfo.CurrentCulture), System.Globalization.CultureInfo.CurrentCulture); #else // fourth character of LocalName contains level number, example: "lvl1pPr -> 1, lvl2pPr -> 2, etc." ReadOnlySpan <char> localNameAsSpan = textPr.LocalName.AsSpan(); int lvl = int.Parse(localNameAsSpan.Slice(3, 1)); #endif lvlToFontData.Add(lvl, new FontData(fontSize, aLatinFont, isBold, isItalic, aRgbColorModelHex, aSchemeColor)); } return(lvlToFontData); }
public void RemoveContentControlsAndKeepContents(OpenXmlCompositeElement compositeElement, XmlElement customXmlPartDocElement) { if (compositeElement == null) { throw new ArgumentNullException("compositeElement"); } SdtElement acompositeElement = compositeElement as SdtElement; if (acompositeElement != null) { var elementsList = this.RemoveContentControlAndKeepContents(acompositeElement, customXmlPartDocElement); foreach (var innerCompositeElement in elementsList) { this.RemoveContentControlsAndKeepContents(innerCompositeElement, customXmlPartDocElement); } } else { var childCompositeElements = compositeElement.Elements <OpenXmlCompositeElement>().ToList(); foreach (var childCompositeElement in childCompositeElements) { this.RemoveContentControlsAndKeepContents(childCompositeElement, customXmlPartDocElement); } } }
internal static T GetOrCreateBefore <T>(this OpenXmlCompositeElement element, OpenXmlElement before) where T : OpenXmlElement, new() { if (!element.Has <T>()) { element.InsertBefore(new T(), before); } return(element.Elements <T>().First()); }
internal static T GetOrCreateAfter <T>(this OpenXmlCompositeElement element, OpenXmlElement after) where T : OpenXmlElement, new() { if (!element.Has <T>()) { element.InsertAfter(new T(), after); } return(element.Elements <T>().First()); }
private void FillSeriesDataPoints(OpenXmlCompositeElement seriesItem, Column dataColumn) { var seriesChartShapeProperties = seriesItem.FirstElement <ChartShapeProperties>(); for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++) { if (dataColumn.Data[rowNo] != null) { DataPoint dp = seriesItem.Elements <DataPoint>().FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == rowNo); if (dp == null) { var dataPoint = new DataPoint(); DocumentFormat.OpenXml.Drawing.Charts.Index index = new DocumentFormat.OpenXml.Drawing.Charts.Index() { Val = new UInt32Value((uint)rowNo) }; InvertIfNegative invertIfNegative = new InvertIfNegative() { Val = false }; Bubble3D bubble3D = new Bubble3D() { Val = false }; ChartShapeProperties chartShapeProperties = seriesChartShapeProperties == null ? new ChartShapeProperties() : (ChartShapeProperties)seriesChartShapeProperties.CloneNode(true); dataPoint.Append(index); dataPoint.Append(invertIfNegative); dataPoint.Append(bubble3D); dataPoint.Append(chartShapeProperties); DataPoint lastDp = seriesItem.Elements <DataPoint>().LastOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value < rowNo); if (lastDp != null) { seriesItem.InsertAfter(dataPoint, lastDp); } else { seriesItem.Append(dataPoint); } } } } }
private void ParseParagraphs(OpenXmlCompositeElement compositeElement) { // Parses non-empty paragraphs var paragraphs = compositeElement.Elements <A.Paragraph>().Where(e => e.Descendants <A.Text>().Any()); // Sets paragraphs Paragraphs = new List <Paragraph>(paragraphs.Count()); foreach (var p in paragraphs) { Paragraphs.Add(new Paragraph(_spContext, p)); } }
private static void ReplaceParagraph(OpenXmlCompositeElement element, string value) { string[] vs = value?.Split('\n'); if (vs == null) { vs = new string[1]; } Paragraph sp = element.Elements <Paragraph>().Count() > 0 ? element.Elements <Paragraph>().First() : new Paragraph(); element.RemoveAllChildren <Paragraph>(); for (int i = 0; i < vs.Length; i++) { Paragraph p = sp.Clone() as Paragraph; element.AppendChild(p); Run r = p.Elements <Run>().Count() > 0 ? p.Elements <Run>().First() : new Run(); p.RemoveAllChildren <Run>(); p.AppendChild(r); r.RemoveAllChildren <Text>(); r.AppendChild(new Text(vs[i])); } }
// todo (a.dobrynin, 05.08.2020): replace this method with targetWorksheet.AddChild(child) when DocumentFormat.OpenXml 2.12.0 is released // https://github.com/OfficeDev/Open-XML-SDK/pull/774 /// <summary> /// Adds node in the correct location according to the schema /// http://msdn.microsoft.com/en-us/library/office/cc880096(v=office.15).aspx /// https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.worksheet?view=openxml-2.8.1#definition /// https://github.com/OfficeDev/Open-XML-SDK/blob/058ec42001ca97850fd82cc16e3b234c155a6e7e/src/DocumentFormat.OpenXml/GeneratedCode/schemas_microsoft_com_office_excel_2006_main.g.cs#L90 /// </summary> public static T AddChildToCorrectLocation <T>(this OpenXmlCompositeElement parent, T child) where T : OpenXmlElement { if (!(parent is Worksheet)) { throw new InvalidOperationException("Schema-oriented insertion is supported only for Worksheets"); } var precedingElementTypes = openXmlWorksheetNodesOrder.TakeWhile(x => x != typeof(T)); var closestPrecedingSibling = precedingElementTypes.Reverse() .Select(precedingElementType => parent.Elements().LastOrDefault(x => x.GetType() == precedingElementType)) .FirstOrDefault(existingElement => existingElement != null); return(closestPrecedingSibling != null ? parent.InsertAfter(child, closestPrecedingSibling) : parent.InsertAt(child, 0)); }
internal static T GetOrCreate <T>(this OpenXmlCompositeElement element, bool prepend) where T : OpenXmlElement, new() { if (!element.Has <T>()) { if (prepend) { element.PrependChild(new T()); } else { element.AppendChild(new T()); } } return(element.Elements <T>().First()); }
private void SetSeriesText(OpenXmlCompositeElement seriesItem, ChartSeriesElement newSeriesItem, string seriesHeader) { SeriesText seriesText = seriesItem.Elements <SeriesText>().First(); seriesText.StringReference.Formula.Text = newSeriesItem.SeriesTextAddress; seriesText.StringReference.StringCache.RemoveAllChildren <StringPoint>(); StringPoint stringReferencePoint = new StringPoint() { Index = (UInt32Value)((uint)0) }; NumericValue stringReferenceNumericValue = new NumericValue() { Text = seriesHeader }; stringReferencePoint.Append(stringReferenceNumericValue); seriesText.StringReference.StringCache.Append(stringReferencePoint); }
/// <summary> /// Parses and returns font height from <see cref="OpenXmlCompositeElement"/> instance. /// </summary> /// <param name="compositeElement"></param> /// <returns></returns> public static Dictionary <int, int> FromCompositeElement(OpenXmlCompositeElement compositeElement) { var result = new Dictionary <int, int>(); foreach (var textPr in compositeElement.Elements().Where(e => e.LocalName.StartsWith("lvl"))) // <a:lvl1pPr>, <a:lvl2pPr>, etc. { var fs = textPr.GetFirstChild <DocumentFormat.OpenXml.Drawing.DefaultRunProperties>()?.FontSize; if (fs == null) { continue; } // fourth character of LocalName contains level number, example: "lvl1pPr -> 1, lvl2pPr -> 2, etc." var lvl = int.Parse(textPr.LocalName[3].ToString()); result.Add(lvl, fs.Value); } return(result); }
public Excel.Cell GetCell(OpenXmlCompositeElement row, object value, int c, int r, uint styleIndex, StringKeyList sharedStrings) { string reference = Helper.IntToChar(c) + r.ToString(); Excel.Cell cell = null; if (row != null) { foreach (var rowCell in row.Elements <Excel.Cell>()) { var cref = CellReference.Parse(rowCell.CellReference.Value); if (cref.Col == c) { cell = rowCell; break; } else if (cref.Col > c) { cell = new Excel.Cell() { CellReference = reference, StyleIndex = styleIndex }; row.InsertBefore <Excel.Cell>(cell, rowCell); break; } } } if (cell == null || cell.CellReference.Value != reference) { cell = new Excel.Cell() { CellReference = reference, StyleIndex = styleIndex }; if (row != null) { row.AppendChild <Excel.Cell>(cell); } } WriteCell(cell, value, sharedStrings); return(cell); }
static void ConvertPointList(OpenXmlCompositeElement element, string defineKey, StringBuilder stringPath) { bool isFirstPoint = true; foreach (var point in element.Elements <Point>()) { if (isFirstPoint) { isFirstPoint = false; stringPath.Append(defineKey); } else { // 同类型的点之间用空格分开 stringPath.Append(" "); } var pixelPoint = PointToPixelPoint(point); PixelPointToString(pixelPoint, stringPath); } }
internal static bool Has <T>(this OpenXmlCompositeElement element) where T : OpenXmlElement { return(element.Elements <T>().Count() != 0); }
private void SetPropertiesFromLegend(OpenXmlCompositeElement seriesItem, Column dataColumn) { if (dataColumn.Legends.All(l => l == null)) { return; } var dataPoints = seriesItem.Elements <DataPoint>(); var labels = seriesItem.FirstElement <DataLabels>(); for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++) { if (dataColumn.Legends[rowNo] != null && dataColumn.Data[rowNo] != null) { ShapeElement legend = dataColumn.Legends[rowNo] as ShapeElement; DataPoint dataPoint = dataPoints.FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == rowNo); if (legend != null) //KAJ: Actually always true if you have reached this line { if (dataPoint != null) { dataPoint.RemoveAllChildren <ChartShapeProperties>(); A.SolidFill legendFill = legend.Element.GetFill(); A.Outline legendOutline = legend.Element.GetOutline(); ChartShapeProperties chartShapeProperties = new ChartShapeProperties(); chartShapeProperties.Append(legendFill); chartShapeProperties.Append(legendOutline); dataPoint.Append(chartShapeProperties); } if (labels != null) { DataLabel label = labels.Elements <DataLabel>().FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == rowNo); if (label != null) { TextProperties labelTextProperties = label.FirstElement <TextProperties>(); if (labelTextProperties == null) { labelTextProperties = labels.FirstElement <TextProperties>().CloneNode(true) as TextProperties; label.Append(labelTextProperties); } NumberingFormat labelNumberingFormat = label.FirstElement <NumberingFormat>(); if (labelNumberingFormat == null) { labelNumberingFormat = labels.FirstElement <NumberingFormat>().CloneNode(true) as NumberingFormat; label.Append(labelNumberingFormat); } A.Paragraph labelParagraph = labelTextProperties.FirstElement <A.Paragraph>(); var legendRunProperties = legend.Element.GetRunProperties(); labelParagraph.ParagraphProperties.RemoveAllChildren <A.DefaultRunProperties>(); List <OpenXmlElement> list = new List <OpenXmlElement>(); foreach (var item in legendRunProperties.ChildElements) { list.Add(item.CloneNode(true)); } var newLabelRunProperties = new A.DefaultRunProperties(list); labelParagraph.ParagraphProperties.Append(newLabelRunProperties); var labelShapeProperties = label.FirstElement <ChartShapeProperties>(); if (labelShapeProperties != null && labelShapeProperties.FirstElement <A.NoFill>() == null) { label.RemoveAllChildren <ChartShapeProperties>(); } A.SolidFill legendFill = legend.Element.GetFill(); ChartShapeProperties chartShapeProperties = new ChartShapeProperties(); chartShapeProperties.Append(legendFill); label.Append(chartShapeProperties); } } } } } }
public void RemoveContentControlsAndKeepContents(OpenXmlCompositeElement compositeElement, XmlElement customXmlPartDocElement) { if (compositeElement == null) { throw new ArgumentNullException("compositeElement"); } SdtElement acompositeElement = compositeElement as SdtElement; if (acompositeElement != null) { var elementsList = this.RemoveContentControlAndKeepContents(acompositeElement, customXmlPartDocElement); foreach (var innerCompositeElement in elementsList) { this.RemoveContentControlsAndKeepContents(innerCompositeElement, customXmlPartDocElement); } } else { var childCompositeElements = compositeElement.Elements<OpenXmlCompositeElement>().ToList(); foreach (var childCompositeElement in childCompositeElements) { this.RemoveContentControlsAndKeepContents(childCompositeElement, customXmlPartDocElement); } } }