private void trackMaxChars(SpreadsheetRow row, SpreadsheetLocation location, bool isTableHeaderRow = false)
{
for (var i = 0; i < row.RowCells.Count; i++)
{
var cell = row.RowCells[i];
trackMaxChars(location.ColumnIndex + i, cell, isTableHeaderRow);
}
}
public SpreadsheetHyperLink(SpreadsheetWorksheet worksheet, SpreadsheetLocation locationToFocus)
{
var worksheetName = new string(worksheet.Name.Take(30).ToArray());
var locationRef = string.Format("{0}{1}", locationToFocus.ColumnName, locationToFocus.RowIndex);
Target = string.Format("'{0}'!{1}", worksheetName, locationRef);
DisplayValue = string.Format("Go to {0}.", worksheet.Name);
}
public void SetChartLocation(DrawingsPart drawingsPart, ChartPart chartPart, SpreadsheetLocation location)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());
// Chart position
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId(location.ColumnIndex.ToString()),
new ColumnOffset("0"),
new RowId(location.RowIndex.ToString()),
new RowOffset("114300")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId((location.ColumnIndex + 19).ToString()),
new ColumnOffset("0"),
new RowId((location.RowIndex + 15).ToString()),
new RowOffset("0")));
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.
Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.
Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
// Chart title
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u), Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
}
internal virtual void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
// Set chart title
chartContainer.AppendChild(ChartPropertySetter.SetTitle(ChartPropertySetter.ChartProperties.Title));
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
uint chartSeriesCounter = 0;
OpenXmlCompositeElement chart = ChartPropertySetter.CreateChart(plotArea);
foreach (var chartDataSeriesGrouped in ChartData.GroupBy(x => x.Series))
{
// Set chart and series depending on type.
OpenXmlCompositeElement chartSeries = ChartPropertySetter.CreateChartSeries(chartDataSeriesGrouped.Key, chartSeriesCounter, chart);
// Every method from chartPropertySetter can be overriden to customize chart export.
ChartPropertySetter.SetChartShapeProperties(chartSeries);
ChartPropertySetter.SetChartAxis(chartSeries, chartDataSeriesGrouped.ToList());
chartSeriesCounter++;
}
chart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
chart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ChartPropertySetter.SetLineCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ChartPropertySetter.SetValueAxis(plotArea);
ChartPropertySetter.SetLegend(chartContainer);
ChartPropertySetter.SetChartLocation(drawingsPart, chartPart, location);
}
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
Layout layout1 = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart());
barChart.Append(new BarDirection()
{
Val = BarDirectionValues.Bar
});
barChart.Append(new BarGrouping()
{
Val = BarGroupingValues.Stacked
});
barChart.Append(new GapWidth()
{
Val = (UInt16Value)75U
});
barChart.Append(new Overlap()
{
Val = 100
});
GanttTypeChart ganttChart = new GanttTypeChart(UserSettings);
var groupedData = GanttData
.GroupBy(x => x.Name);
List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>();
for (int i = 0; i < groupedData.Max(x => x.Count()); i++)
{
// For each series create a hidden one for spacing.
BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2))
},
new Order()
{
Val = new UInt32Value((uint)(i * 2))
},
new SeriesText(new NumericValue()
{
Text = "Not Active"
})));
BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new Order()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new SeriesText(new NumericValue()
{
Text = "Time Spent"
})));
ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false);
ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count);
var ganttData = new List <GanttData>();
foreach (var data in groupedData.Where(x => x.Count() >= i + 1))
{
ganttData.Add(data.ElementAt(i));
}
ganttDataWithSeries.Add(new GanttDataPairedSeries()
{
BarChartSeriesHidden = barChartSeriesHidden,
BarChartSeriesValue = barChartSeriesValue,
Values = ganttData
});
}
ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList());
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ganttChart.SetGanttCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End));
chartContainer.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
ganttChart.SetChartLocation(drawingsPart, chartPart, location);
}
public void AddHyperlink(SpreadsheetLocation location, SpreadsheetHyperLink hyperLink)
{
_hyperLinks[location] = hyperLink;
}