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);
}
}
/// <summary>
/// Assigns the section properties.
/// </summary>
/// <param name="document">The document.</param>
/// <param name="secProperties">The sec properties.</param>
private static void AssignSectionProperties(OpenXmlCompositeElement document, OpenXmlElement secProperties)
{
if (document == null)
{
throw new ArgumentNullException("document");
}
if (secProperties == null)
{
return;
}
var pageSize = secProperties.Descendants <PageSize>().FirstOrDefault();
if (pageSize != null)
{
pageSize.Remove();
}
var pageMargin = secProperties.Descendants <PageMargin>().FirstOrDefault();
if (pageMargin != null)
{
pageMargin.Remove();
}
document.AppendChild(new Paragraph(new ParagraphProperties(new SectionProperties(pageSize, pageMargin))));
}
private void AddCell(OpenXmlCompositeElement row, Action <ICellContextBuilder> options, TableCellProperties cellProperties)
{
var builder = new DocxDocumentCellContextBuilder(Document, cellProperties);
options(builder);
row.AppendChild(builder.ToElement());
}
private void AddParagraph(OXmlParagraphElement element)
{
Paragraph paragraph = new Paragraph();
if (element != null && element.Style != null)
{
paragraph.ParagraphProperties = new ParagraphProperties {
ParagraphStyleId = new ParagraphStyleId {
Val = element.Style
}
}
}
;
_paragraph = _element.AppendChild(paragraph);
//_run = _paragraph.AppendChild(new Run());
_run = null;
}
/// <summary>
/// Sets the SDT content keeping permission elements.
/// </summary>
/// <param name="openXmlCompositeElement">The open XML composite element.</param>
/// <param name="newChild">The new child.</param>
private static void SetSdtContentKeepingPermissionElements(OpenXmlCompositeElement openXmlCompositeElement, OpenXmlElement newChild)
{
var start = openXmlCompositeElement.Descendants <PermStart>().FirstOrDefault();
var end = openXmlCompositeElement.Descendants <PermEnd>().FirstOrDefault();
openXmlCompositeElement.RemoveAllChildren();
if (start != null)
{
openXmlCompositeElement.AppendChild(start);
}
openXmlCompositeElement.AppendChild(newChild);
if (end != null)
{
openXmlCompositeElement.AppendChild(end);
}
}
/// <summary>
/// Sets the SDT content keeping permission elements.
/// </summary>
/// <param name="openXmlCompositeElement">The open XML composite element.</param>
/// <param name="newChildren">The new children.</param>
private void SetSdtContentKeepingPermissionElements(OpenXmlCompositeElement openXmlCompositeElement, List <OpenXmlElement> newChildren)
{
PermStart start = openXmlCompositeElement.Descendants <PermStart>().FirstOrDefault();
PermEnd end = openXmlCompositeElement.Descendants <PermEnd>().FirstOrDefault();
openXmlCompositeElement.RemoveAllChildren();
if (start != null)
{
openXmlCompositeElement.AppendChild(start);
}
foreach (var newChild in newChildren)
{
openXmlCompositeElement.AppendChild(newChild);
}
if (end != null)
{
openXmlCompositeElement.AppendChild(end);
}
}
/// <summary>
/// Fill TableBorders or TableCellBorders element with borders.
/// </summary>
/// <param name="border"></param>
/// <param name="borders"></param>
private static void FillBorders(BorderModel border, OpenXmlCompositeElement borders)
{
if (border.BorderPositions.HasFlag(BorderPositions.LEFT))
{
LeftBorder leftBorder = new LeftBorder();
leftBorder.Color = border.UseVariableBorders && !string.IsNullOrWhiteSpace(border.BorderLeftColor) ? border.BorderLeftColor : border.BorderColor;
leftBorder.Val = DocumentFormat.OpenXml.Wordprocessing.BorderValues.Thick;
leftBorder.Size = border.UseVariableBorders ? border.BorderWidthLeft : border.BorderWidth;
borders.AppendChild(leftBorder);
}
if (border.BorderPositions.HasFlag(BorderPositions.TOP))
{
TopBorder topBorder = new TopBorder();
topBorder.Color = border.UseVariableBorders && !string.IsNullOrWhiteSpace(border.BorderTopColor) ? border.BorderTopColor : border.BorderColor;
topBorder.Val = DocumentFormat.OpenXml.Wordprocessing.BorderValues.Thick;
topBorder.Size = border.UseVariableBorders ? border.BorderWidthTop : border.BorderWidth;
borders.AppendChild(topBorder);
}
if (border.BorderPositions.HasFlag(BorderPositions.RIGHT))
{
RightBorder rightBorder = new RightBorder();
rightBorder.Color = border.UseVariableBorders && !string.IsNullOrWhiteSpace(border.BorderRightColor) ? border.BorderRightColor : border.BorderColor;
rightBorder.Val = DocumentFormat.OpenXml.Wordprocessing.BorderValues.Thick;
rightBorder.Size = border.UseVariableBorders ? border.BorderWidthRight : border.BorderWidth;
borders.AppendChild(rightBorder);
}
if (border.BorderPositions.HasFlag(BorderPositions.BOTTOM))
{
BottomBorder bottomBorder = new BottomBorder();
bottomBorder.Color = border.UseVariableBorders && !string.IsNullOrWhiteSpace(border.BorderBottomColor) ? border.BorderBottomColor : border.BorderColor;
bottomBorder.Val = DocumentFormat.OpenXml.Wordprocessing.BorderValues.Thick;
bottomBorder.Size = border.UseVariableBorders ? border.BorderWidthBottom : border.BorderWidth;
borders.AppendChild(bottomBorder);
}
if (border.BorderPositions.HasFlag(BorderPositions.INSIDEHORIZONTAL))
{
InsideHorizontalBorder insideHorizontalBorder = new InsideHorizontalBorder();
insideHorizontalBorder.Color = border.BorderColor;
insideHorizontalBorder.Val = DocumentFormat.OpenXml.Wordprocessing.BorderValues.Thick;
insideHorizontalBorder.Size = border.UseVariableBorders ? border.BorderWidthInsideHorizontal : border.BorderWidth;
borders.AppendChild(insideHorizontalBorder);
}
if (border.BorderPositions.HasFlag(BorderPositions.INSIDEVERTICAL))
{
InsideVerticalBorder insideVerticalBorder = new InsideVerticalBorder();
insideVerticalBorder.Color = border.BorderColor;
insideVerticalBorder.Val = DocumentFormat.OpenXml.Wordprocessing.BorderValues.Thick;
insideVerticalBorder.Size = border.UseVariableBorders ? border.BorderWidthInsideVertical : border.BorderWidth;
borders.AppendChild(insideVerticalBorder);
}
}
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());
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chart">The chart.</param>
/// <param name="sheetName">Name of the sheet.</param>
protected override void UpdateChart(OpenXmlCompositeElement chart, string sheetName)
{
if (chart != null)
{
chart.RemoveAllChildren <LineChartSeries>();
// Index 0 is for category axis data
for (int index = 1; index < chartData.columnNameToSeries.Count(); index++)
{
string columnName = GetExcelColumnName(index);
LineChartSeries lineChartSeries = chart.AppendChild <LineChartSeries>(new LineChartSeries());
UpdateSeriesText(sheetName, lineChartSeries, columnName, 1, chartData.columnNameToSeries.Skip(index).FirstOrDefault().Key);
// Update Category Axis data
CategoryAxisData catAxisData = new CategoryAxisData();
catAxisData.RemoveAllChildren <StringReference>();
catAxisData.RemoveAllChildren <NumberReference>();
StringReference catStringReference = GetStringReference(sheetName + "!$A$2:$A$" + (chartData.Count + 1).ToString(), chartData.Count);
// Series 0 is for category axis data
foreach (string cat in chartData.columnNameToSeries.First().Value)
{
AddStringPoint(catStringReference.StringCache, catStringReference.StringCache.Descendants <StringPoint>().Count(), cat);
}
catAxisData.Append(catStringReference);
// Update Values
NumberingCache numberingCache;
PointCount pointCount;
Values values = lineChartSeries.AppendChild <Values>(new Values());
NumberReference reference = CreateNumberReference(values, sheetName + "!$" + columnName + "$2:$" + columnName + "$" + (chartData.Count + 1).ToString());
SetNumberingCache(reference, out numberingCache, out pointCount);
int rowIndex = 0;
foreach (var point in chartData.columnNameToSeries.Skip(index).FirstOrDefault().Value)
{
AddNumericPoint(numberingCache, pointCount, rowIndex, point.ToString());
rowIndex += 1;
}
}
}
}
public override OpenXmlCompositeElement CreateChartSeries(string title, uint seriesNumber, OpenXmlCompositeElement chart)
{
// Create two new line series with specified name.
var chartSeries = chart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value(seriesNumber)
},
new Order()
{
Val = new UInt32Value(seriesNumber)
},
new SeriesText(new NumericValue()
{
Text = title
})));
return(chartSeries);
}
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);
}
/// <summary>
/// Updates the series text.
/// </summary>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="chartSeries">The chart series.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="pointCountVal">The point count val.</param>
/// <param name="stringPointNumericVal">The string point numeric val.</param>
protected static void UpdateSeriesText(string sheetName, OpenXmlCompositeElement chartSeries, string columnName, int pointCountVal, string stringPointNumericVal)
{
if (chartSeries != null)
{
SeriesText seriesText = chartSeries.Descendants <SeriesText>().FirstOrDefault();
if (seriesText == null)
{
seriesText = chartSeries.AppendChild <SeriesText>(new SeriesText());
}
else
{
seriesText.RemoveAllChildren <StringReference>();
}
StringReference stringReference = GetStringReference(sheetName + "!$" + columnName + "$1", pointCountVal);
AddStringPoint(stringReference.StringCache, 0, stringPointNumericVal);
seriesText.Append(stringReference);
}
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chart">The chart.</param>
/// <param name="sheetName">Name of the sheet.</param>
protected override void UpdateChart(OpenXmlCompositeElement chart, string sheetName)
{
if (chart != null)
{
chart.RemoveAllChildren <ScatterChartSeries>();
ScatterChartSeries scatterChartSeries = chart.AppendChild <ScatterChartSeries>(new ScatterChartSeries());
Outline outline = new Outline()
{
Width = 28575
};
NoFill noFill = new NoFill();
outline.Append(noFill);
scatterChartSeries.ChartShapeProperties = new ChartShapeProperties(outline);
UpdateSeriesText(sheetName, scatterChartSeries, "B", 1, chartData.yColumnName);
XValues xValues = scatterChartSeries.AppendChild <XValues>(new XValues());
YValues yValues = scatterChartSeries.AppendChild <YValues>(new YValues());
NumberReference referenceX = CreateNumberReference(xValues, sheetName + "!$A$2:$A$" + (chartData.Count + 1).ToString());
NumberReference referenceY = CreateNumberReference(yValues, sheetName + "!$B$2:$B$" + (chartData.Count + 1).ToString());
NumberingCache ncX;
PointCount ptXCount;
NumberingCache ncY;
PointCount ptYCount;
SetNumberingCache(referenceX, out ncX, out ptXCount);
SetNumberingCache(referenceY, out ncY, out ptYCount);
int rowIndex = 0;
foreach (var xToY in chartData.xValToYValMap)
{
AddNumericPoint(ncX, ptXCount, rowIndex, xToY.Key.ToString());
AddNumericPoint(ncY, ptYCount, rowIndex, xToY.Value.ToString());
rowIndex += 1;
}
}
}
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]));
}
}
private void FixNakedText(OpenXmlCompositeElement par, INodeProvider nodeProvider) //Simple Spreadsheets cells
{
if (par.ChildElements.Count != 1)
{
return;
}
var only = par.ChildElements.Only();
if (!nodeProvider.IsText(only))
{
return;
}
var text = nodeProvider.GetText(only);
if (!TemplateUtils.KeywordsRegex.IsMatch(text))
{
return;
}
par.RemoveChild(only);
par.AppendChild(nodeProvider.WrapInRun(only));
}
/// <summary>
/// Sets the SDT content keeping permission elements.
/// </summary>
/// <param name="openXmlCompositeElement">The open XML composite element.</param>
/// <param name="newChildren">The new children.</param>
private void SetSdtContentKeepingPermissionElements(OpenXmlCompositeElement openXmlCompositeElement, List<OpenXmlElement> newChildren)
{
PermStart start = openXmlCompositeElement.Descendants<PermStart>().FirstOrDefault();
PermEnd end = openXmlCompositeElement.Descendants<PermEnd>().FirstOrDefault();
openXmlCompositeElement.RemoveAllChildren();
if (start != null)
{
openXmlCompositeElement.AppendChild(start);
}
foreach (var newChild in newChildren)
{
openXmlCompositeElement.AppendChild(newChild);
}
if (end != null)
{
openXmlCompositeElement.AppendChild(end);
}
}
/// <summary>
/// Sets the SDT content keeping permission elements.
/// </summary>
/// <param name="openXmlCompositeElement">The open XML composite element.</param>
/// <param name="newChildren">The new children.</param>
private static void SetSdtContentKeepingPermissionElements(OpenXmlCompositeElement openXmlCompositeElement, IEnumerable<Run> newChildren)
{
var start = openXmlCompositeElement.Descendants<PermStart>().FirstOrDefault();
var end = openXmlCompositeElement.Descendants<PermEnd>().FirstOrDefault();
openXmlCompositeElement.RemoveAllChildren();
if (start != null)
{
openXmlCompositeElement.AppendChild(start);
}
foreach (var newChild in newChildren)
{
openXmlCompositeElement.AppendChild(newChild);
}
if (end != null)
{
openXmlCompositeElement.AppendChild(end);
}
}
/// <summary>
/// Assigns the section properties.
/// </summary>
/// <param name="document">The document.</param>
/// <param name="secProperties">The sec properties.</param>
private static void AssignSectionProperties(OpenXmlCompositeElement document, OpenXmlElement secProperties)
{
if (document == null)
{
throw new ArgumentNullException("document");
}
if (secProperties == null)
{
return;
}
var pageSize = secProperties.Descendants<PageSize>().FirstOrDefault();
if (pageSize != null)
{
pageSize.Remove();
}
var pageMargin = secProperties.Descendants<PageMargin>().FirstOrDefault();
if (pageMargin != null)
{
pageMargin.Remove();
}
document.AppendChild(new Paragraph(new ParagraphProperties(new SectionProperties(pageSize, pageMargin))));
}
private void AddCell(OpenXmlCompositeElement row, Action<ICellContextBuilder> options, TableCellProperties cellProperties)
{
var builder = new DocxDocumentCellContextBuilder(Document, cellProperties);
options(builder);
row.AppendChild(builder.ToElement());
}