public List <string> GetZHeaders()
{
int row = 0;
var zHeaders = new List <string>();
var constraints = GetCell(0, 0).ConstraintSet;
foreach (var header in ZAxis.GetSlice(ZSlice))
{
string label = XbrlUtils.GetHeaderLabel(ZAxis, header, row);
if (header.DefinitionNode is Xbrl.Table.AspectNode)
{
var aspect = (header.DefinitionNode as Xbrl.Table.AspectNode).ParticipatingAspect;
var val = constraints[aspect];
if (val.Aspect.Type == Xbrl.AspectType.Dimension)
{
if (val.Aspect.Dimension.IsExplicit)
{
label = XbrlUtils.GetLabel((val as Xbrl.ExplicitDimensionAspectValue).Value);
}
else
{
label = string.Format("{0} {1}", label, (val as Xbrl.TypedDimensionAspectValue).Value.SchemaNormalizedValue);
}
}
}
zHeaders.Add(label);
++row;
}
return(zHeaders);
}
public Report(string url, Xbrl.Taxonomy.Dts dts = null)
{
Logger.Debug("Loading XBRL instance {0}", url);
ErrorLog errorLog;
var instance = Xbrl.Instance.CreateFromUrl(url, XbrlUtils.GetCreateInstanceOptions(dts), out errorLog);
if (instance == null || errorLog.HasErrors)
{
throw new ApplicationException(errorLog.ToString());
}
Init(instance);
}
void WriteTableDataCell(Xbrl.Taxonomy.Dts dts, Xbrl.Item item, IXLRange range)
{
var concept = item.Concept;
if (concept.IsNumeric)
{
if (concept.IsMonetary)
{
Style.ApplyDataCellFormat(range, DataCellType.Monetary).Value = item.NumericValue;
}
else if (XbrlUtils.IsPercentItem(dts, concept))
{
Style.ApplyDataCellFormat(range, DataCellType.Monetary).Value = item.NumericValue;
}
else
{
range.DataType = XLDataType.Number;
range.Value = item.NumericValue;
if (item.InferredDecimals.IsInfinity)
{
range.Style.NumberFormat.Format = "#,##0.######";
}
else if (item.InferredDecimals.Value > 0)
{
range.Style.NumberFormat.Format = "#,##0." + new string('0', item.InferredDecimals.Value);
}
else
{
range.Style.NumberFormat.NumberFormatId = 3; // #,##0
}
}
}
else if (concept.IsBoolean)
{
Style.ApplyDataCellFormat(range, DataCellType.Boolean).Value = item.BooleanValue;
}
else if (concept.IsEnum)
{
WriteTableDataCell(dts, item.EnumValue, range);
}
else
{
WriteTableDataCell(dts, item.Element, range);
}
foreach (var footnote in item.Footnotes)
{
var cell = range.FirstCell();
cell.Comment.Style.Alignment.SetAutomaticSize();
cell.Comment.AddText(footnote.Text);
}
}
static bool ShowGroupHeader(Xbrl.Taxonomy.GenericRelationshipNetwork netGroupTable, object node)
{
if (netGroupTable.GetRelationshipsFrom(node).Count == 1)
{
var child = netGroupTable.GetRelationshipsFrom(node).First().Target;
if (child is Xbrl.Table.Table)
{
var tableLabel = Regex.Replace(XbrlUtils.GetVerboseLabel(child as Xbrl.Table.Table).ToLower(), @"[^0-9a-zA-Z]", string.Empty);
var groupLabel = Regex.Replace(XbrlUtils.GetLabel(node).ToLower(), @"[^0-9a-zA-Z]", string.Empty);
return(!tableLabel.Contains(groupLabel));
}
}
return(true);
}
static List <string> GetTableSuffixNames(Table table)
{
var suffixes = new List <string>();
foreach (var header in table.ZAxis.GetSlice(table.ZSlice))
{
string label = null;
if (header.DefinitionNode is Xbrl.Table.AspectNode)
{
var constraints = table.GetCell(0, 0).ConstraintSet;
var aspect = header.DefinitionNode.ParticipatingAspects.FirstOrDefault();
if (aspect.Type == Xbrl.AspectType.Dimension)
{
if (aspect.Dimension.IsExplicit)
{
label = XbrlUtils.GetLabel((constraints[aspect] as Xbrl.ExplicitDimensionAspectValue).Value);
if (label == null || label.Length > 3)
{
label = (constraints[aspect] as Xbrl.ExplicitDimensionAspectValue).Value.Name;
}
}
else
{
label = (constraints[aspect] as Xbrl.TypedDimensionAspectValue).Value.SchemaNormalizedValue;
}
}
else
{
label = constraints[aspect].ToString();
}
}
else if (table.ZAxis.SliceCount > 1)
{
label = XbrlUtils.GetRCCode(header);
if (label == null)
{
label = XbrlUtils.GetLabel(header);
}
}
if (label != null)
{
suffixes.Add(label);
}
}
return(suffixes);
}
void WriteTableDataCell(Xbrl.Taxonomy.Dts dts, Xsd.AnySimpleType val, IXLRange range)
{
if (val.IsBoolean)
{
Style.ApplyDataCellFormat(range, DataCellType.Boolean).Value = (bool)(val as Xsd.Boolean);
}
else if (val.IsInteger)
{
Style.ApplyDataCellFormat(range, DataCellType.Integer).Value = (decimal)(val as Xsd.Integer);
}
else if (val.IsDecimal)
{
Style.ApplyDataCellFormat(range, DataCellType.Decimal).Value = (decimal)(val as Xsd.Decimal);
}
else if (val.IsFloat)
{
Style.ApplyDataCellFormat(range, DataCellType.Decimal).Value = (decimal)(val as Xsd.Float);
}
else if (val.IsDouble)
{
Style.ApplyDataCellFormat(range, DataCellType.Decimal).Value = (decimal)(val as Xsd.Double);
}
else if (val is Xsd.Date)
{
Style.ApplyDataCellFormat(range, DataCellType.Date).Value = (DateTime)(val as Xsd.Date);
}
else if (val is Xsd.DateTime)
{
Style.ApplyDataCellFormat(range, DataCellType.DateTime).Value = (DateTime)(val as Xsd.DateTime);
}
else if (val is Xsd.QName)
{
var qnameConcept = dts.ResolveConcept(val as Xsd.QName);
var label = qnameConcept != null?XbrlUtils.GetLabel(qnameConcept) : null;
Style.ApplyDataCellFormat(range, DataCellType.Label).Value = label ?? (val as Xsd.QName).ToString();
}
else
{
Style.ApplyDataCellFormat(range, DataCellType.Text).Value = val.LexicalValue;
}
}
void TraverseTableTree(Report report, Xbrl.Taxonomy.GenericRelationshipNetwork netGroupTable, object node, int depth = 0)
{
if (node is Xbrl.Table.Table)
{
var defTable = node as Xbrl.Table.Table;
foreach (var table in XbrlUtils.FindTableSet(report.TableModel, defTable))
{
var tableInfo = new Table(report, table);
if (table.GetAxis(Xbrl.Table.AxisType.Z).SliceCount > 1 || XbrlUtils.HasOpenAspectNodes(table.GetAxis(Xbrl.Table.AxisType.Z)))
{
Lines.Add(new TableTreeLine(null, tableInfo.Title, depth));
for (uint z = 0; z < table.Shape.Z; ++z)
{
if (table.ContainsFactsInZSlice(z))
{
tableInfo = new Table(report, table, z);
Lines.Add(new TableTreeLine(tableInfo, string.Join(", ", tableInfo.GetZHeaders()), depth + 1));
}
}
}
else if (table.ContainsFacts)
{
Lines.Add(new TableTreeLine(tableInfo, tableInfo.Title, depth));
}
}
}
else
{
if (ShowGroupHeader(netGroupTable, node))
{
Lines.Add(new TableTreeLine(null, XbrlUtils.GetLabel(node), depth++));
}
foreach (var rel in netGroupTable.GetRelationshipsFrom(node))
{
TraverseTableTree(report, netGroupTable, rel.Target, depth);
}
}
}
int WriteZHeaders(Table table, IXLWorksheet ws, int xOffset = 0, int yOffset = 0)
{
var constraints = table.GetCell(0, 0).ConstraintSet;
var yaxisCols = table.HasOpenAspects ? table.OpenAspectCount : (int)table.YAxis.RowCount + 1;
var axis = table.ZAxis;
int row = 0;
foreach (var header in axis.GetSlice(table.ZSlice))
{
if (header.DefinitionNode is Xbrl.Table.AspectNode)
{
string rcCode = XbrlUtils.GetOpenAspectRCCode(axis, header.DefinitionNode as Xbrl.Table.AspectNode, row);
string zAxisLabel = XbrlUtils.GetOpenAspectHeaderLabel(axis, header.DefinitionNode as Xbrl.Table.AspectNode, row);
string label = rcCode != null ? rcCode + " " + zAxisLabel : zAxisLabel;
var aspect = header.DefinitionNode.ParticipatingAspects.FirstOrDefault();
var range = ws.Range(yOffset + row + 1, xOffset + 1, yOffset + row + 1, xOffset + yaxisCols);
Style.ApplyHeaderFormat(range, HeaderType.ZAxis, true).Value = label;
var cell = ws.Cell(yOffset + row + 1, xOffset + yaxisCols + 1).AsRange();
WriteTableDataCell(table.Dts, constraints[aspect], cell);
cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
else
{
string rcCode = XbrlUtils.GetRCCode(header);
string zAxisLabel = XbrlUtils.GetHeaderLabel(axis, header, row);
string label = rcCode != null ? rcCode + " " + zAxisLabel : zAxisLabel;
var range = ws.Range(yOffset + row + 1, xOffset + 1, yOffset + row + 1, xOffset + yaxisCols);
Style.ApplyHeaderFormat(range, HeaderType.ZAxis).Value = label;
}
++row;
}
return(row);
}
void Init(Xbrl.Instance instance)
{
this.Instance = instance;
Logger.Debug("Processing XBRL table linkbase");
ErrorLog tableErrorLog;
this.TableModel = this.Instance.GenerateLayoutModel(XbrlUtils.GetTableLayoutOptions(), out tableErrorLog);
if (this.TableModel == null || tableErrorLog.HasErrors)
{
throw new ApplicationException(tableErrorLog.ToString());
}
foreach (var context in this.Instance.Contexts)
{
if (context.Period.IsInstant && context.DimensionAspectValues.Count == 0)
{
this.ReportingContext = context;
break;
}
}
if (this.ReportingContext == null)
{
foreach (var context in this.Instance.Contexts)
{
if (context.DimensionAspectValues.Count == 0)
{
this.ReportingContext = context;
break;
}
}
}
if (this.Instance.Units.Count > 0)
{
var units = new Dictionary <string, uint>();
foreach (var unit in this.Instance.Units)
{
if (unit.AspectValue.IsMonetary)
{
units.Add(unit.AspectValue.Iso4217Currency, 0);
}
}
foreach (var fact in this.Instance.Facts)
{
var item = fact as Xbrl.Item;
if (item != null && item.UnitAspectValue != null && item.UnitAspectValue.IsMonetary)
{
units[item.UnitAspectValue.Iso4217Currency] += 1;
}
}
var mostUsedCurrency = units.OrderByDescending(x => x.Value).First();
if (mostUsedCurrency.Value > 0)
{
this.ReportingCurrency = mostUsedCurrency.Key;
}
}
FilingIndicators = XbrlUtils.FilingIndicators(Instance);
}
void WriteXAxis(Table table, IXLWorksheet ws, int xOffset = 0, int yOffset = 0)
{
var axis = table.XAxis;
int shapeX = (int)axis.Shape.X;
int shapeY = (int)axis.Shape.Y;
if (table.HasOpenAspects)
{
// Set column widths
ws.Columns(xOffset + 1, xOffset + (int)table.OpenAspectCount + shapeX).Width = 15;
for (int x = 0; x < table.OpenAspectCount; ++x)
{
// Write open header columns
var range = ws.Range(yOffset + 1, xOffset + x + 1, yOffset + shapeY, xOffset + x + 1);
Style.ApplyHeaderFormat(range, HeaderType.XAxis, true).Value = XbrlUtils.GetOpenAspectHeaderLabel(table.YAxis, table.OpenAspects[x], x);
// Write open header RC codes
string rcCode = XbrlUtils.GetOpenAspectRCCode(table.YAxis, table.OpenAspects[x], x);
range = ws.Cell(yOffset + shapeY + 1, xOffset + x + 1).AsRange();
Style.ApplyHeaderRcCodeFormat(range, HeaderType.XAxis, true).Value = rcCode ?? (10 * (x + 1)).ToString();
}
xOffset += (int)table.OpenAspectCount;
}
else
{
// Set column widths
int yaxisCols = (int)table.YAxis.RowCount + 1;
ws.Columns(xOffset + yaxisCols + 1, xOffset + yaxisCols + shapeX).Width = 15;
// Write top left cell
var range = ws.Range(yOffset + 1, xOffset + 1, yOffset + shapeY + 1, xOffset + yaxisCols);
Style.ApplyTitleHeaderFormat(range).Value = table.Title;
xOffset += yaxisCols;
}
// Write X header rows
for (int y = 0; y < shapeY; ++y)
{
for (int x = 0; x < shapeX; ++x)
{
var header = axis.GetHeader((uint)x, (uint)y);
if (!header.IsRollup)
{
string value = null;
if (header.DefinitionNode is Xbrl.Table.AspectNode)
{
value = XbrlUtils.GetOpenAspectHeaderLabel(axis, header.DefinitionNode as Xbrl.Table.AspectNode, y);
}
else
{
value = XbrlUtils.GetHeaderLabel(axis, header, y);
}
int span = (int)header.Span;
var finalRollupChild = header.LastRollupDescendent;
var range = ws.Range(yOffset + y + 1, xOffset + x + 1, yOffset + y + 1, xOffset + x + span);
if (span == 1 && finalRollupChild != null)
{
range = ws.Range(y + yOffset + 1, x + xOffset + 1, yOffset + (int)finalRollupChild.Row + 1, xOffset + x + 1);
}
Style.ApplyHeaderFormat(range, HeaderType.XAxis).Value = value;
if (span > 1 && finalRollupChild != null)
{
var range2 = ws.Range(yOffset + y + 2, xOffset + (int)finalRollupChild.Slice + 1, yOffset + (int)finalRollupChild.Row + 1, xOffset + (int)finalRollupChild.Slice + 1);
Style.ApplyHeaderFormat(range2, HeaderType.XAxis);
ws.Cell(yOffset + y + 1, xOffset + (int)finalRollupChild.Slice + 1).Style.Border.BottomBorder = XLBorderStyleValues.None;
ws.Cell(yOffset + y + 2, xOffset + (int)finalRollupChild.Slice + 1).Style.Border.TopBorder = XLBorderStyleValues.None;
}
x += span - 1;
}
}
}
// Write row with RC codes
for (int x = 0; x < shapeX; ++x)
{
var header = XbrlUtils.GetAxisHeader(axis, x);
string rcCode = header != null?XbrlUtils.GetRCCode(header) : XbrlUtils.GetRCCode(axis.GetDefinitionBreakdown(0));
var range = ws.Cell(yOffset + shapeY + 1, xOffset + x + 1).AsRange();
Style.ApplyHeaderRcCodeFormat(range, HeaderType.XAxis).Value = rcCode ?? (10 * (x + 1)).ToString();
}
}
// Create valid sheet name from label and sheet-number
static string CreateSheetName(Table table, HashSet <string> names)
{
var suffixes = GetTableSuffixNames(table);
var suffix = suffixes.Count > 0 ? string.Join(".", suffixes) : null;
var label = XbrlUtils.GetWorksheetNameForTable(table.DefinitionTable);
StringBuilder builder = new StringBuilder(label.Length);
int maxBaseNameLength = Math.Max(suffix != null ? 25 - suffix.Length : 28, 15);
int i = 0;
int nParanCount = 0;
foreach (char c in label.Trim())
{
switch (c)
{
case '(':
case '[':
++nParanCount;
break;
case ')':
case ']':
--nParanCount;
break;
case '\\':
case '/':
case '?':
case '*':
break;
default:
if (nParanCount == 0)
{
++i;
builder.Append(c);
}
break;
}
if (i >= maxBaseNameLength)
{
break;
}
}
var baseName = builder.ToString().Trim();
if (suffix != null && baseName.Length + suffix.Length > 25)
{
suffix = suffix.Substring(0, 25 - baseName.Length); // adjust suffix length to allow at least 15 chars basename
}
i = 0;
string sheetName = baseName;
while (true)
{
if (suffix != null)
{
sheetName = baseName + " (" + suffix + (i != 0 ? "." + i.ToString() : "") + ")";
}
else
{
sheetName = baseName + (i != 0 ? "." + i.ToString() : "");
}
if (!names.Contains(sheetName))
{
names.Add(sheetName);
break;
}
++i;
}
return(sheetName);
}
void WriteTableDataCell(Xbrl.Taxonomy.Dts dts, Xbrl.Taxonomy.Item item, IXLRange range)
{
Style.ApplyDataCellFormat(range, DataCellType.Label).Value = XbrlUtils.GetLabel(item) ?? item.Name;
}
void WriteYAxis(Table table, IXLWorksheet ws, int xOffset = 0, int yOffset = 0)
{
var axis = table.YAxis;
int shapeX = (int)axis.Shape.X;
int shapeY = (int)axis.Shape.Y;
bool[] arrVerticalTextCol = new bool[shapeY];
for (uint y = 0; y < shapeY; ++y)
{
foreach (var header in axis.GetRow(y))
{
if (header.Span > 1 && !header.IsRollup)
{
arrVerticalTextCol[y] = true;
break;
}
}
}
int nMin = 0;
for (int i = 1; i < shapeY; ++i)
{
if (arrVerticalTextCol[nMin] != arrVerticalTextCol[i])
{
ws.Columns(xOffset + nMin + 1, xOffset + i + 1).Width = arrVerticalTextCol[nMin] ? 2 : 21;
nMin = i;
}
}
ws.Columns(xOffset + nMin + 1, xOffset + shapeY).Width = arrVerticalTextCol[nMin] ? 2 : 21;
ws.Column(xOffset + shapeY + 1).Width = 5;
for (int y = 0; y < shapeX; ++y)
{
for (int x = 0; x < shapeY; ++x)
{
var axisHeader = axis.GetHeader((uint)y, (uint)x);
if (!axisHeader.IsRollup)
{
var value = XbrlUtils.GetHeaderLabel(axis, axisHeader, y);
var finalRollupChild = axisHeader.LastRollupDescendent;
int span = (int)axisHeader.Span;
if (span > 1)
{
if (finalRollupChild != null)
{
if (y == finalRollupChild.Slice)
{
var range = ws.Range(yOffset + y + 1, xOffset + x + 1, yOffset + y + 1, xOffset + (int)finalRollupChild.Row + 1);
Style.ApplyHeaderFormat(range, HeaderType.YAxis).Value = value;
if (y == axisHeader.Slice)
{
var range2 = ws.Range(yOffset + y + 2, xOffset + x + 1, yOffset + y + span, xOffset + x + 1);
Style.ApplyHeaderFormat(range2, HeaderType.YAxis);
range.FirstCell().Style.Border.BottomBorder = XLBorderStyleValues.None;
range2.FirstCell().Style.Border.TopBorder = XLBorderStyleValues.None;
}
else
{
var range2 = ws.Range(yOffset + y - span + 2, xOffset + x + 1, yOffset + y, xOffset + x + 1);
Style.ApplyHeaderFormat(range2, HeaderType.YAxis);
range.FirstCell().Style.Border.TopBorder = XLBorderStyleValues.None;
range2.LastCell().Style.Border.BottomBorder = XLBorderStyleValues.None;
}
}
}
else
{
if (y == axisHeader.Slice)
{
var range = ws.Range(yOffset + y + 1, xOffset + x + 1, yOffset + y + span, xOffset + x + 1);
Style.ApplyHeaderFormat(range, HeaderType.YAxis).Value = value;
range.Style.Alignment.TextRotation = 90;
}
}
}
else
{
var range = ws.Cell(yOffset + y + 1, xOffset + x + 1).AsRange();
if (finalRollupChild != null)
{
range = ws.Range(yOffset + y + 1, xOffset + x + 1, yOffset + y + 1, xOffset + (int)finalRollupChild.Row + 1);
}
Style.ApplyHeaderFormat(range, HeaderType.YAxis).Value = value;
if (arrVerticalTextCol[x] && finalRollupChild == null)
{
range.Style.Alignment.TextRotation = 90;
}
}
}
}
}
// Write RC code column
for (int y = 0; y < shapeX; ++y)
{
var axisHeader = XbrlUtils.GetAxisHeader(axis, y);
var rcCode = axisHeader != null?XbrlUtils.GetRCCode(axisHeader) : XbrlUtils.GetRCCode(axis.GetDefinitionBreakdown(0));
var range = ws.Cell(yOffset + y + 1, xOffset + shapeY + 1).AsRange();
Style.ApplyHeaderRcCodeFormat(range, HeaderType.YAxis).Value = rcCode ?? (10 * (y + 1)).ToString();
}
}