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);
}
}
}