Add High-Low Lines with OpenXML SDK
The example shows how to add and format high-low lines with OpenXML SDK.
Output: HighLowLinesFormat.xlsx
public void HighLowLinesFormat()
{
using (var spreadsheetDocument = SpreadsheetDocument.Create("HighLowLinesFormat.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
sheets.Append(sheet);
workbook.Append(fv);
workbook.Append(sheets);
this.AddData(workbookPart, worksheetPart);
DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{ Id = worksheetPart.GetIdOfPart(drawingsPart) });
// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.RoundedCorners = new RoundedCorners() { Val = false };
//Set default style
var alternateContent = chartPart.ChartSpace.AppendChild(new AlternateContent());
alternateContent.AddNamespaceDeclaration("c14", "http://schemas.microsoft.com/office/drawing/2007/8/2/chart");
var choice = alternateContent.AppendNewAlternateContentChoice();
var c14Style = new DocumentFormat.OpenXml.Office2010.Drawing.Charts.Style() { Val = 102 };
choice.Requires = "c14";
choice.AppendChild(c14Style);
var fallback = alternateContent.AppendNewAlternateContentFallback();
var cStyle = new Drawing.Charts.Style() { Val = 2 };
fallback.AppendChild(cStyle);
//chartPart.ChartSpace.EditingLanguage = new EditingLanguage{Val = new StringValue("en-US")};
var chart = chartPart.ChartSpace.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Chart>(
new DocumentFormat.OpenXml.Drawing.Charts.Chart());
chart.AutoTitleDeleted = new AutoTitleDeleted() { Val = true };
PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
//Layout layout = plotArea.AppendChild<Layout>(new Layout());
var lineChart = plotArea.AppendChild(new LineChart());
lineChart.Grouping = new Grouping() { Val = GroupingValues.Standard };
lineChart.VaryColors = new VaryColors() { Val = false };
var ser0 = lineChart.AppendChild(new LineChartSeries());
ser0.Index = new Index() { Val = 0u };
ser0.Order = new Order() { Val = 0u };
ser0.SeriesText = new SeriesText();
ser0.SeriesText.StringReference = AddStringReference("Sheet1!$J$1", "Series 1");
var cat0 = new CategoryAxisData();
cat0.StringReference =
this.AddStringReference("Sheet1!$I$2:$I$5", "Point 1", "Point 2", "Point 3", "Point 4");
ser0.AppendChild(cat0);
var val0 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
val0.NumberReference = this.AddNumberReference("Sheet1!$J$2:$J$5", "2", "4", "6", "8");
ser0.AppendChild(val0);
ser0.AppendChild(new Smooth() { Val = false });
var ser1 = lineChart.AppendChild(new BarChartSeries());
ser1.Index = new Index() { Val = new UInt32Value(1u) };
ser1.Order = new Order() { Val = new UInt32Value(1u) };
ser1.SeriesText = new SeriesText();
ser1.SeriesText.StringReference = AddStringReference("Sheet1!$K$1", "Series 2");
var cat1 = new CategoryAxisData();
cat1.StringReference =
this.AddStringReference("Sheet1!$I$2:$I$5", "Point 1", "Point 2", "Point 3", "Point 4");
ser1.AppendChild(cat1);
var val1 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
val1.NumberReference = this.AddNumberReference("Sheet1!$K$2:$K$5", "4", "3", "2", "1");
ser1.AppendChild(val1);
ser1.AppendChild(new Smooth() { Val = false });
var ser2 = lineChart.AppendChild(new BarChartSeries());
ser2.Index = new Index() { Val = new UInt32Value(2u) };
ser2.Order = new Order() { Val = new UInt32Value(2u) };
ser2.SeriesText = new SeriesText();
ser2.SeriesText.StringReference = AddStringReference("Sheet1!$L$1", "Series 3");
var cat2 = new CategoryAxisData();
cat2.StringReference =
this.AddStringReference("Sheet1!$I$2:$I$5", "Point 1", "Point 2", "Point 3", "Point 4");
ser2.AppendChild(cat2);
var val2 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
val2.NumberReference = this.AddNumberReference("Sheet1!$L$2:$L$5", "2", "1", "-1", "-2");
ser2.AppendChild(val2);
ser2.AppendChild(new Smooth() { Val = false });
#region High-Low Lines
var highLowLines = lineChart.AppendChild(new HighLowLines());
highLowLines.ChartShapeProperties = new ChartShapeProperties();
var ln = highLowLines.ChartShapeProperties.AppendChild(new Drawing.Outline());
ln.Width = 38100;
ln.AppendChild(new SolidFill() { RgbColorModelHex = new RgbColorModelHex() { Val = "FFA500" } });
#endregion
lineChart.Append(new AxisId() { Val = 1250099810u });
lineChart.Append(new AxisId() { Val = 1687146081u });
var catAx = plotArea.AppendChild<CategoryAxis>(new CategoryAxis());
catAx.Append(new AxisId() { Val = 1250099810u });
catAx.Append(new Scaling()
{ Orientation = new Orientation() { Val = Drawing.Charts.OrientationValues.MinMax } });
catAx.Append(new Delete() { Val = false });
catAx.Append(new AxisPosition() { Val = AxisPositionValues.Bottom });
catAx.Append(new Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = false });
catAx.Append(new TickLabelPosition() { Val = TickLabelPositionValues.Low });
catAx.Append(new CrossingAxis() { Val = new UInt32Value(1687146081u) });
catAx.Append(new LabelOffset() { Val = new UInt16Value((ushort)100) });
ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis());
valAx.Append(new AxisId() { Val = 1687146081u });
valAx.Append(new Scaling() { Orientation = new Orientation() { Val = Drawing.Charts.OrientationValues.MinMax } });
valAx.Append(new Delete() { Val = new BooleanValue(false) });
valAx.Append(new AxisPosition() { Val = AxisPositionValues.Bottom });
valAx.Append(new Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = false });
valAx.Append(new TickLabelPosition() { Val = TickLabelPositionValues.NextTo });
valAx.Append(new CrossingAxis() { Val = 1250099810u });
valAx.Append(new Crosses() { Val = CrossesValues.AutoZero });
valAx.Append(new CrossBetween() { Val = CrossBetweenValues.Between });
chart.Append(new PlotVisibleOnly() { Val = true });
chart.Append(new DisplayBlanksAs() { Val = DisplayBlanksAsValues.Zero });
chart.Append(new ShowDataLabelsOverMaximum() { Val = false });
// Save the chart part.
chartPart.ChartSpace.Save();
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
var twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<TwoCellAnchor>(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("0"),
new ColumnOffset("5"),
new RowId("0"),
new RowOffset("5")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("5"),
new ColumnOffset("152405"),
new RowId("12"),
new RowOffset("5")));
// Append a GraphicFrame to the TwoCellAnchor object.
var graphicFrame =
twoCellAnchor.AppendChild(new Drawing.Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.NonVisualGraphicFrameProperties = new Drawing.Spreadsheet.NonVisualGraphicFrameProperties();
graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Drawing.Spreadsheet.NonVisualDrawingProperties();
graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Id = new UInt32Value(1u);
graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name = "Chart 1";
graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties();
graphicFrame.Transform = new Transform();
graphicFrame.Transform.Offset = new Offset();
graphicFrame.Transform.Offset.X = 0;
graphicFrame.Transform.Offset.Y = 0;
graphicFrame.Transform.Extents = new Extents();
graphicFrame.Transform.Extents.Cx = 0;
graphicFrame.Transform.Extents.Cy = 0;
graphicFrame.Graphic = new Graphic();
graphicFrame.Graphic.GraphicData = new GraphicData();
graphicFrame.Graphic.GraphicData.Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart";
graphicFrame.Graphic.GraphicData.AppendChild(new ChartReference()
{ Id = drawingsPart.GetIdOfPart(chartPart) });
twoCellAnchor.Append(new ClientData());
drawingsPart.WorksheetDrawing.Save();
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
}
internal NumberReference AddNumberReference(string formula, params string[] values)
{
var numRef = new NumberReference();
numRef.Formula = new Drawing.Charts.Formula() { Text = formula };
numRef.NumberingCache = new NumberingCache();
numRef.NumberingCache.FormatCode = new FormatCode("General");
numRef.NumberingCache.PointCount = new PointCount() { Val = new UInt32Value(Convert.ToUInt32(values.Length)) };
uint index = 0;
foreach (var value in values)
{
numRef.NumberingCache.AppendChild(new NumericPoint() { Index = new UInt32Value(index) })
.Append(new NumericValue(value));
index++;
}
return numRef;
}
internal StringReference AddStringReference(string formula, params string[] values)
{
var strRef = new StringReference();
strRef.Formula = new Drawing.Charts.Formula() { Text = formula };
strRef.StringCache = new StringCache();
strRef.StringCache.PointCount = new PointCount() { Val = new UInt32Value(Convert.ToUInt32(values.Length)) };
uint index = 0;
foreach (var value in values)
{
strRef.StringCache.AppendChild(new StringPoint { Index = new UInt32Value(index) })
.Append(new NumericValue(value));
index++;
}
return strRef;
}
internal void AddData(WorkbookPart workbookPart, WorksheetPart worksheetPart)
{
SheetData sheetData = new SheetData();
var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Series 1")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Series 2")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Series 3")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 1")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 2")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 3")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 4")));
var row1 = new Row() { RowIndex = 1 };
var row2 = new Row() { RowIndex = 2 };
var row3 = new Row() { RowIndex = 3 };
var row4 = new Row() { RowIndex = 4 };
var row5 = new Row() { RowIndex = 5 };
sheetData.Append(row1);
sheetData.Append(row2);
sheetData.Append(row3);
sheetData.Append(row4);
sheetData.Append(row5);
var cellJ1 = new Cell() { CellReference = "J1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
cellJ1.CellValue = new CellValue("0"); //Index to the shared string "Series 1"
var cellK1 = new Cell() { CellReference = "K1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
cellK1.CellValue = new CellValue("1");
var cellL1 = new Cell() { CellReference = "L1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
cellL1.CellValue = new CellValue("2");
row1.Append(cellJ1);
row1.Append(cellK1);
row1.Append(cellL1);
row2.Append(new Cell() { CellReference = "I2", CellValue = new CellValue("3"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
row2.Append(new Cell() { CellReference = "J2", CellValue = new CellValue("2") });
row2.Append(new Cell() { CellReference = "K2", CellValue = new CellValue("4") });
row2.Append(new Cell() { CellReference = "L2", CellValue = new CellValue("2") });
row3.Append(new Cell() { CellReference = "I3", CellValue = new CellValue("4"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
row3.Append(new Cell() { CellReference = "J3", CellValue = new CellValue("4") });
row3.Append(new Cell() { CellReference = "K3", CellValue = new CellValue("3") });
row3.Append(new Cell() { CellReference = "L3", CellValue = new CellValue("1") });
row4.Append(new Cell() { CellReference = "I4", CellValue = new CellValue("5"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
row4.Append(new Cell() { CellReference = "J4", CellValue = new CellValue("6") });
row4.Append(new Cell() { CellReference = "K4", CellValue = new CellValue("2") });
row4.Append(new Cell() { CellReference = "L4", CellValue = new CellValue("-1") });
row5.Append(new Cell() { CellReference = "I5", CellValue = new CellValue("6"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
row5.Append(new Cell() { CellReference = "J5", CellValue = new CellValue("8") });
row5.Append(new Cell() { CellReference = "K5", CellValue = new CellValue("1") });
row5.Append(new Cell() { CellReference = "L5", CellValue = new CellValue("-2") });
worksheetPart.Worksheet.Append(sheetData);
}