public static void ApplyContent(ReportData client, OpenXmlPartContainer container, BlockItem block, TableDefinition content, Dictionary<string, string> options)
{
var contentblock = GetTableContentBlock(client, block);
if (null != contentblock)
{
UpdateBlock(client, container, contentblock, content, options);
}
}
/// <summary>
/// Returns the block configuration of the block item given in parameter.
/// </summary>
/// <param name="block">Block where the block configuration parameters will be found.</param>
/// <returns>The block configuration of the block item given in parameter.</returns>
protected override BlockConfiguration GetBlockConfiguration(BlockItem block)
{
string desc = string.Empty;
var elt = block.OxpBlock
.Descendants<NonVisualDrawingProperties>()
.FirstOrDefault(_ => !string.IsNullOrWhiteSpace(_.Description));
if (null != elt) { desc = elt.Description; }
return GetBlockConfiguration(desc);
}
private static OpenXmlElement GetTextContentBlock(ReportData client, BlockItem block)
{
switch (client.ReportType)
{
case FormatType.Word: return(block.OxpBlock.Descendants <OXW.SdtContentRun>().FirstOrDefault());
case FormatType.PowerPoint: return(block.OxpBlock);
case FormatType.Excel:
default: return(null);
}
}
private static OpenXmlElement GetTableContentBlock(ReportData client, BlockItem block)
{
switch (client.ReportType)
{
case FormatType.Word: return(block.OxpBlock.Descendants <OXW.Table>().FirstOrDefault());
case FormatType.PowerPoint: return(block.OxpBlock);
case FormatType.Excel: // TODO : Finalize Excel alimentation
default: break;
}
throw new NotImplementedException();
}
private static ChartPart GetChartPart(OpenXmlPackage package, BlockItem block, string chartId)
{
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (package is WordprocessingDocument)
{
return((ChartPart)((WordprocessingDocument)package).MainDocumentPart.GetPartById(chartId));
}
if (package is PresentationDocument)
{
return((ChartPart)((SlidePart)block.Container).GetPartById(chartId));
}
if (package is SpreadsheetDocument)
{
return((ChartPart)((WorksheetPart)block.Container).GetPartById(chartId));
}
return(null);
}
private static OpenXmlElement GetTableContentBlock(ReportData client, BlockItem block)
{
switch (client.ReportType)
{
case FormatType.Word:
var tblContent = block.OxpBlock.Descendants <OXW.Table>().FirstOrDefault();
return(tblContent ?? block.OxpBlock);
case FormatType.PowerPoint:
return(block.OxpBlock);
case FormatType.Excel:
break;
default:
throw new ArgumentOutOfRangeException();
}
throw new NotImplementedException();
}
public static void BuildContent(ReportData client, OpenXmlPartContainer container, BlockItem block, string blockName, Dictionary<string, string> options)
{
TableBlock instance = BlockHelper.GetAssociatedBlockInstance<TableBlock>(blockName);
if (null != instance)
{
LogHelper.Instance.LogDebugFormat("Start TableBlock generation : Type {0}", blockName);
Stopwatch treatmentWatch = Stopwatch.StartNew();
TableDefinition content = instance.Content(client, options);
if (null != content)
{
ApplyContent(client, container, block, content, options);
}
treatmentWatch.Stop();
LogHelper.Instance.LogDebugFormat
("End TableBlock generation ({0}) in {1} millisecond{2}"
, blockName
, treatmentWatch.ElapsedMilliseconds.ToString()
, treatmentWatch.ElapsedMilliseconds > 1 ? "s" : string.Empty
);
}
}
/// <summary>
/// Returns the block configuration of the block item given in parameter.
/// </summary>
/// <param name="block">Block where the block configuration parameters will be found.</param>
/// <returns>The block configuration of the block item given in parameter.</returns>
protected override BlockConfiguration GetBlockConfiguration(BlockItem block)
{
string alias = null;
string tag = null;
var xElement = block.OxpBlock.Descendants<SdtProperties>().FirstOrDefault();
if (xElement != null)
{
var aliasElement = xElement.Descendants<SdtAlias>().FirstOrDefault();
if (aliasElement != null) { alias = aliasElement.Val.Value; }
var tagElement = xElement.Descendants<Tag>().FirstOrDefault();
if (tagElement != null) { tag = tagElement.Val.Value; }
}
else
{
var elt = block.OxpBlock
.Descendants<DocProperties>()
.FirstOrDefault(_ => !string.IsNullOrWhiteSpace(_.Description));
if (null != elt) { tag = elt.Description; }
}
return GetBlockConfiguration(alias, tag);
}
/// <summary>
///
/// </summary>
/// <param name="client"></param>
/// <param name="pPackage"></param>
/// <param name="block"></param>
/// <param name="blockName"></param>
/// <param name="options"></param>
public static void BuildContent(ReportData client, OpenXmlPackage pPackage, BlockItem block, string blockName, Dictionary <string, string> options)
{
var previousCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
GraphBlock instance = BlockHelper.GetAssociatedBlockInstance <GraphBlock>(blockName);
if (null == instance)
{
return;
}
LogHelper.Instance.LogDebugFormat("Start GraphBlock generation : Type {0}", blockName);
Stopwatch treatmentWatch = Stopwatch.StartNew();
TableDefinition content = instance.Content(client, options);
try
{
if (null != content)
{
ApplyContent(client, pPackage, block, content, options);
}
}
finally
{
Thread.CurrentThread.CurrentCulture = previousCulture;
treatmentWatch.Stop();
LogHelper.Instance.LogDebugFormat
("End GraphBlock generation ({0}) in {1} millisecond{2}"
, blockName
, treatmentWatch.ElapsedMilliseconds
, treatmentWatch.ElapsedMilliseconds > 1 ? "s" : string.Empty
);
}
}
public static void ApplyContent(ReportData client, OpenXmlPartContainer container, BlockItem block, string content)
{
var contentblock = GetTextContentBlock(client, block);
if (null != contentblock)
{
UpdateBlock(client, container, contentblock, content);
}
}
public static void BuildContent(ReportData client, OpenXmlPartContainer container, BlockItem block, string blockName, Dictionary <string, string> options)
{
TextBlock instance = BlockHelper.GetAssociatedBlockInstance <TextBlock>(blockName);
if (null == instance)
{
return;
}
LogHelper.Instance.LogDebugFormat("Start TextBlock generation : Type {0}", blockName);
Stopwatch treatmentWatch = Stopwatch.StartNew();
string content = instance.Content(client, options);
ApplyContent(client, container, block, content);
treatmentWatch.Stop();
LogHelper.Instance.LogDebugFormat
("End TextBlock generation ({0}) in {1} ms"
, blockName
, treatmentWatch.ElapsedMilliseconds.ToString()
);
}
private static OpenXmlElement GetTableContentBlock(ReportData client, BlockItem block)
{
switch (client.ReportType)
{
case FormatType.Word: return block.OxpBlock.Descendants<OXW.Table>().FirstOrDefault();
case FormatType.PowerPoint: return block.OxpBlock;
case FormatType.Excel: // TODO : Finalize Excel alimentation
default: break;
}
throw new NotImplementedException();
}
public static void ApplyContent(ReportData client, OpenXmlPartContainer container, BlockItem block, TableDefinition content, Dictionary <string, string> options)
{
var contentblock = GetTableContentBlock(client, block);
if (null != contentblock)
{
UpdateBlock(client, container, contentblock, content, options);
}
}
public static void BuildContent(ReportData client, OpenXmlPartContainer container, BlockItem block, string blockName, Dictionary <string, string> options)
{
TableBlock instance = BlockHelper.GetAssociatedBlockInstance <TableBlock>(blockName);
if (null != instance)
{
LogHelper.Instance.LogDebugFormat("Start TableBlock generation : Type {0}", blockName);
Stopwatch treatmentWatch = Stopwatch.StartNew();
TableDefinition content = instance.Content(client, options);
if (null != content)
{
ApplyContent(client, container, block, content, options);
}
treatmentWatch.Stop();
LogHelper.Instance.LogDebugFormat
("End TableBlock generation ({0}) in {1} millisecond{2}"
, blockName
, treatmentWatch.ElapsedMilliseconds.ToString()
, treatmentWatch.ElapsedMilliseconds > 1 ? "s" : string.Empty
);
}
}
///// <summary>
///// Returns the block configuration of the block item given in parameter.
///// </summary>
///// <param name="block">Block where the block configuration parameters will be found.</param>
///// <returns>The block configuration of the block item given in parameter.</returns>
protected override BlockConfiguration GetBlockConfiguration(BlockItem block)
{
// TODO : Finalize Excel alimentation
//throw new NotImplementedException();
return null;
}
private static void ApplyContent(ReportData pClient, OpenXmlPackage pPackage, BlockItem pBlock, TableDefinition pContent, Dictionary <string, string> pOptions)
{
try
{
string chartId = null;
var phElem = GetElements(pBlock.OxpBlock, pClient.ReportType)?.ToList();
if (phElem == null || phElem.Count == 0)
{
LogHelper.Instance.LogError("No placeholder content found.");
return;
}
#region Get the block Id in document
var allElementInPlaceHolder = GetElementsInPlaceHolder(pClient, phElem);
XElement graphicElement = phElem.Descendants(A.graphic).FirstOrDefault() ?? phElem.FirstOrDefault(_ => A.graphic.Equals(_.Name));
if (null != graphicElement)
{
XElement chartElem = graphicElement.Descendants(C.chart).FirstOrDefault();
if (null != chartElem)
{
chartId = chartElem.Attribute(R.id)?.Value;
}
else
{
LogHelper.Instance.LogFatal("Graphic object present but not a graph.");
}
}
else
{
LogHelper.Instance.LogFatal("No graphic / chart object inside the block.");
}
if (null == chartId)
{
pBlock.XBlock.ReplaceWith(allElementInPlaceHolder); return;
}
#endregion Get the block Id in document
var chartPart = GetChartPart(pPackage, pBlock, chartId);
if (null != chartPart)
{
string spreadsheetId = GetSpreadsheetId(chartPart);
if (!string.IsNullOrWhiteSpace(spreadsheetId))
{
#region Associated content management
var embedPackage = (EmbeddedPackagePart)chartPart.GetPartById(spreadsheetId);
if (null != embedPackage)
{
using (var ms = new MemoryStream())
{
#region Set content in memory to work with
using (Stream str = embedPackage.GetStream())
{
StreamHelper.CopyStream(str, ms);
str.Close();
}
#endregion Set content in memory to work with
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(ms, true))
{
#region Associated Data File content Management
var ws = (OXS.Sheet)spreadsheetDoc.WorkbookPart.Workbook.Sheets.FirstOrDefault();
if (ws != null)
{
string sheetId = ws.Id;
var wsp = (WorksheetPart)spreadsheetDoc.WorkbookPart.GetPartById(sheetId);
XDocument shPart = wsp.GetXDocument();
XElement shData = shPart.Descendants(S.sheetData).FirstOrDefault();
#region Use of the data content (Only data, no titles)
if (null != shData)
{
IEnumerable <XElement> allRows = shData.Descendants(S.row); // => if ToList() cause some graph to fail to generate
int ctRow = 0;
int nbRows = allRows.Count();
// Cleaning Cells ======================================================================
// We clean row not having Cell information
int idxRow = 1;
var nbCorrectSeries = allRows.Where(x => x.Descendants(S.c).Any(y => y.Attribute(NoNamespace.r) != null &&
y.Attribute(NoNamespace.r)?.Value != ""))
.Max(x => x.Descendants(S.c).Count());
for (int ctn = 0; ctn < nbRows; ctn += 1)
{
var oneRow = allRows.ElementAt(ctn);//[ctn];
// DCO - 9/21/2012 - I added the condition that the count of CELL must be indeed equal of numCorrectSeries OR to the number of column
// It happens for graphs with 3 cells defined per row, but only two used (so no Value for third cell), when NbColumns was == 2
var isRowValid = ((oneRow.Descendants(S.c).Count() == nbCorrectSeries || oneRow.Descendants(S.c).Count() >= pContent.NbColumns) &&
(oneRow.Descendants(S.c).Descendants(S.v).Count() == oneRow.Descendants(S.c).Count() ||
oneRow.Descendants(S.c).Descendants(S.v).Count() >= pContent.NbColumns));
// We remove rows that are not defined in content
if (isRowValid == false || ctRow >= pContent.NbRows)
{
oneRow.Remove();
ctn -= 1;
nbRows -= 1;
continue;
}
var _xAttribute = oneRow.Attribute(NoNamespace.r);
if (_xAttribute != null)
{
_xAttribute.Value = idxRow.ToString();
}
idxRow += 1;
}
// ====================================================================================
// Copying new row ========================================================================
// We copied new row if needed and extrapolate formula
// Take cell with no t SharedString
nbRows = allRows.Count();
if (pContent.NbRows > nbRows)
{
// We need to detect the best ROW to copy
// Usually the first row is the header so it contains two cells with SharedString
// Case 1: For others rows, it will be two cells with value.
// Case 2: For other rows, it can be one cell with SharedString and one cell with value (in case of App Nane + value)
var oneSerieRow =
// Case 1
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" && x.Descendants(S.c).Any() &&
x.Descendants(S.c).Attributes(NoNamespace.t).Any() == false)
?? // Case 2: One or several SharedString, but at least one Value (by using < content.NbColumns)
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" && x.Descendants(S.c).Any() &&
x.Descendants(S.c).Attributes(NoNamespace.t).Count() < pContent.NbColumns)
?? // Case 3: Any row but the first (avoiding Header)
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" &&
x.Attribute(NoNamespace.r)?.Value != "1" && x.Descendants(S.c).Any());
if (oneSerieRow != null)
{
var previousRowValue = Convert.ToInt32(oneSerieRow.Attribute(NoNamespace.r)?.Value);
while (nbRows < pContent.NbRows)
{
var newRow = new XElement(oneSerieRow);
var _xAttribute = newRow.Attribute(NoNamespace.r);
if (_xAttribute != null)
{
_xAttribute.Value = (nbRows + 1).ToString(); // DCO Correction, ROW ID starts at 1
}
var serieCells = newRow.Descendants(S.c);
foreach (var oneCell in serieCells)
{
if (oneCell.Attribute(NoNamespace.r) == null)
{
continue;
}
var previousFormula = oneCell.Attribute(NoNamespace.r)?.Value;
// We extrapolate
int indexRow;
int indexCol;
WorksheetAccessorExt.GetRowColumnValue(previousFormula, out indexRow, out indexCol);
int newRowValue = nbRows + 1 + (indexRow - previousRowValue);
var _attribute = oneCell.Attribute(NoNamespace.r);
if (_attribute != null)
{
_attribute.Value = string.Concat(WorksheetAccessor.GetColumnId(indexCol), newRowValue.ToString());
}
if (oneCell.Attributes(NoNamespace.t).Any() != true)
{
continue;
}
var vElement = oneCell.Descendants(S.v).FirstOrDefault();
if (vElement != null)
{
vElement.Value = WorksheetAccessorExt.AddSharedStringValue(spreadsheetDoc, "").ToString();
}
// ---
}
shData.Add(newRow);
nbRows += 1;
}
}
else
{
LogHelper.Instance.LogWarn("Adding Rows: Could not find a row without a SharedString element.");
}
}
//-----
// Define Sheet Dimension ================================================================
int minStartRow = -1;
int minStartCol = -1;
int maxEndRow = -1;
int maxEndCol = -1;
var entireScope = allRows.SelectMany(x => x.Descendants(S.c).Attributes(NoNamespace.r).Select(y => y.Value));
foreach (var oneFormula in entireScope)
{
var startRow = -1;
var endRow = -1;
var startCol = -1;
var endCol = -1;
WorksheetAccessorExt.GetFormulaCoord(oneFormula, out startRow, out startCol,
out endRow, out endCol);
if (minStartRow == -1 || startRow < minStartRow)
{
minStartRow = startRow;
}
if (minStartCol == -1 || startCol < minStartCol)
{
minStartCol = startCol;
}
if (maxEndRow == -1 || endRow > maxEndRow)
{
maxEndRow = endRow;
}
if (maxEndCol == -1 || endCol > maxEndCol)
{
maxEndCol = endCol;
}
}
XElement sheetDimension = shPart.Descendants(S.s + "dimension").FirstOrDefault();
if (sheetDimension?.Attribute(NoNamespace._ref) != null)
{
sheetDimension.Attribute(NoNamespace._ref)?.SetValue(WorksheetAccessorExt.SetFormula("", minStartRow, minStartCol, maxEndRow, maxEndCol, false));
}
// ====================================================================================
int contentEltCount = pContent.Data?.Count() ?? 0;
// Apply values =======================================================================
for (int ctn = 0; ctn < nbRows; ctn++)
{
var oneRow = allRows.ElementAt(ctn);
// TODO: We may have to correct the "spans" in: <row r="1" spans="1:3"
List <XElement> allCells = oneRow.Descendants(S.c).ToList();
var ctCell = 0;
int nbCells = allCells.Count;
for (int ctc = 0; ctc < nbCells; ctc++)
{
var oneCell = allCells[ctc];
// We remove cell if they are not defined as content columns
if (ctCell >= pContent.NbColumns)
{
LogHelper.Instance.LogWarn("More cells that defined content ");
if (null != oneCell.Parent)
{
oneCell.Remove();
}
ctc -= 1;
nbCells -= 1;
continue;
}
// We inject text
var targetText = ((ctRow * pContent.NbColumns + ctCell) < contentEltCount ?
pContent.Data?.ElementAt(ctRow * pContent.NbColumns + ctCell) :
string.Empty);
if (null != targetText && !"<KEEP>".Equals(targetText)) // Keep for managing UniversalGraph
{
var isSharedString = oneCell.Attribute(NoNamespace.t);
if (null != isSharedString && "s".Equals(isSharedString.Value))
{
if ("".Equals(targetText))
{
LogHelper.Instance.LogWarn("Target Text empty for Shared String, this can create abnormal behavior.");
}
var idx = Convert.ToInt32(oneCell.Value);
WorksheetAccessorExt.SetSharedStringValue(spreadsheetDoc, idx, targetText);
}
else
{
XElement cell = oneCell.Descendants(S.v).FirstOrDefault();
if (null != cell)
{
cell.Value = targetText;
}
else
{
LogHelper.Instance.LogWarn("No correct cell value found");
}
}
}
ctCell += 1;
}
ctRow += 1;
}
}
else
{
LogHelper.Instance.LogWarn("Embedded spreadsheet is not formatted correctly");
}
// ====================================================================================
#endregion Get and use of the data content (Only data, no titles)
// We modify Table Definition (defining scope of Graph)
foreach (TableDefinitionPart t in wsp.TableDefinitionParts)
{
t.Table.Reference = String.Concat(WorksheetAccessor.GetColumnId(1), 1, ":",
WorksheetAccessor.GetColumnId(pContent.NbColumns), pContent.NbRows);
// We reduce the scope TableColumn if needed
var columnCount = t.Table.TableColumns.Count;
for (int ctCol = 0; ctCol < columnCount; ctCol += 1)
{
var tabColumn = t.Table.TableColumns.ElementAt(ctCol);
if (ctCol >= pContent.NbColumns)
{
tabColumn.Remove();
ctCol -= 1;
columnCount -= 1; // DCO - 10/23/2012 - Correction when reducing scope to a column (count is corrected afterwards).
continue;
}
// We align column name with the correct Shared String
if (!string.IsNullOrEmpty(pContent.Data?.ElementAt(ctCol)) && ctCol < pContent.NbColumns && "<KEEP>" != pContent.Data.ElementAt(ctCol))
{
tabColumn.SetAttribute(new OpenXmlAttribute("", "name", "", pContent.Data.ElementAt(ctCol)));
}
}
// The Count attribute is not updated correctly, so we do the work for them :)
if (pContent.NbColumns < t.Table.TableColumns.Count)
{
t.Table.TableColumns.SetAttribute(new OpenXmlAttribute("", "count", "", pContent.NbColumns.ToString()));
}
}
// We save the XML content
wsp.PutXDocument(shPart);
}
// We update cached data in Word document
UpdateCachedValues(pClient, chartPart, spreadsheetDoc, pContent);
#endregion Associated Data File content Management
}
// Write the modified memory stream back
// into the embedded package part.
using (Stream s = embedPackage.GetStream())
{
ms.WriteTo(s);
s.SetLength(ms.Length);
}
}
}
else
{
LogHelper.Instance.LogWarn("No embedded excel file found.");
}
#endregion Associated content management
}
else
{
LogHelper.Instance.LogWarn("No spreadsheet identifier found.");
}
#region Additionnal parameters
if (null == pContent.GraphOptions || !pContent.GraphOptions.HasConfiguration)
{
return;
}
Chart chart = chartPart.ChartSpace.Descendants <Chart>().FirstOrDefault();
PlotArea p_c = chart?.PlotArea;
var primaryVerticalAxis = p_c?.Descendants <ValueAxis>().FirstOrDefault(_ => "valAx".Equals(_.LocalName));
if (pContent.GraphOptions.AxisConfiguration.VerticalAxisMinimal.HasValue)
{
if (primaryVerticalAxis != null)
{
primaryVerticalAxis.Scaling.MinAxisValue.Val = DoubleValue.FromDouble(pContent.GraphOptions.AxisConfiguration.VerticalAxisMinimal.Value);
}
}
if (!pContent.GraphOptions.AxisConfiguration.VerticalAxisMaximal.HasValue)
{
return;
}
// ReSharper disable once PossibleInvalidOperationException
if (primaryVerticalAxis != null)
{
primaryVerticalAxis.Scaling.MaxAxisValue.Val = DoubleValue.FromDouble(pContent.GraphOptions.AxisConfiguration.VerticalAxisMaximal.Value);
}
#endregion Additionnal parameters
}
}
catch (Exception exception)
{
LogHelper.Instance.LogError("Unexpected exception thrown.", exception);
throw;
}
}
