private void writeMergedCells(OpenXmlWriter writer)
{
var mergedCellRanges = new Dictionary <SpreadsheetLocation, System.Drawing.Size>();
foreach (var row in _rows)
{
for (var i = 0; i < row.Value.RowCells.Count; i++)
{
var cell = row.Value.RowCells[i];
if (cell.MergedCellsRange != null)
{
mergedCellRanges[new SpreadsheetLocation(row.Key.RowIndex, row.Key.ColumnIndex + i)] = cell.MergedCellsRange.Value;
}
}
}
if (mergedCellRanges.Any())
{
writer.WriteStartElement(new MergeCells());
foreach (var cellRange in mergedCellRanges)
{
var cell1Name = SpreadsheetHelper.ExcelColumnFromNumber(cellRange.Key.ColumnIndex) + cellRange.Key.RowIndex;
var cell2Name = SpreadsheetHelper.ExcelColumnFromNumber(cellRange.Key.ColumnIndex + cellRange.Value.Width) + (cellRange.Key.RowIndex + cellRange.Value.Height - 1);
var range = cell1Name + ":" + cell2Name;
var mergeCell = new MergeCell()
{
Reference = new StringValue(range)
};
writer.WriteElement(mergeCell);
}
writer.WriteEndElement();
}
}
public override void WriteOpenXml(OpenXmlWriter writer)
{
X14.ConditionalFormatting cf = new X14.ConditionalFormatting();
cf.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");
ReferenceSequence referenceSequence = new ReferenceSequence();
referenceSequence.Text = $"{SpreadsheetHelper.ExcelColumnFromNumber(RangeStart.ColumnIndex)}{RangeStart.RowIndex}:{SpreadsheetHelper.ExcelColumnFromNumber(RangeEnd.ColumnIndex)}{RangeEnd.RowIndex}";
X14.ConditionalFormattingRule conditionalFormattingRule = new X14.ConditionalFormattingRule()
{
Type = ConditionalFormatValues.IconSet, Priority = 1, Id = Guid.NewGuid().ToString("B").ToUpper()
};
X14.IconSet iconSet1 = new X14.IconSet()
{
IconSetTypes = new EnumValue <IconSetTypeValues>(IconSetTypeValues.FourTrafficLights), ShowValue = false, Custom = true
};
//ConditionalFormattingIcon
var conditionalFormattingIcon1 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.NoIcons, IconId = (UInt32Value)0U
};
var conditionalFormattingIcon2 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.ThreeSymbols2, IconId = (UInt32Value)2U
};
var conditionalFormattingIcon3 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.ThreeSymbols, IconId = (UInt32Value)1U
};
var conditionalFormattingIcon4 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.ThreeSymbols, IconId = (UInt32Value)0U
};
X14.ConditionalFormattingValueObject conditionalFormattingValueObject1 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula1 = new Formula();
formula1.Text = "0";
conditionalFormattingValueObject1.Append(formula1);
X14.ConditionalFormattingValueObject conditionalFormattingValueObject2 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula2 = new Formula();
formula2.Text = "1";
conditionalFormattingValueObject2.Append(formula2);
X14.ConditionalFormattingValueObject conditionalFormattingValueObject3 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula3 = new Formula();
formula3.Text = "2";
conditionalFormattingValueObject3.Append(formula3);
X14.ConditionalFormattingValueObject conditionalFormattingValueObject4 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula4 = new Formula();
formula4.Text = "3";
conditionalFormattingValueObject4.Append(formula4);
iconSet1.Append(
conditionalFormattingValueObject1
, conditionalFormattingValueObject2
, conditionalFormattingValueObject3
, conditionalFormattingValueObject4
, conditionalFormattingIcon1
, conditionalFormattingIcon2
, conditionalFormattingIcon3
, conditionalFormattingIcon4
);
conditionalFormattingRule.Append(iconSet1);
cf.Append(conditionalFormattingRule);
cf.Append(referenceSequence);
writer.WriteElement(cf);
}
public Table GetTableDefinition(int id, int columnIndex, int rowIndex)
{
var startColumn = SpreadsheetHelper.ExcelColumnFromNumber(columnIndex);
var endColumn = SpreadsheetHelper.ExcelColumnFromNumber(columnIndex + Columns.Count - 1);
var reference = string.Format("{0}{1}:{2}{3}", startColumn, rowIndex, endColumn, rowIndex + RowCount + (ShowHeaderRow ? 0 : -1));
var table = new Table()
{
Id = (UInt32Value)(UInt32)id, Name = Name, DisplayName = "table" + id + "_", TotalsRowShown = false, Reference = reference
};
AutoFilter autoFilter = null;
if (!ShowHeaderRow)
{
table.HeaderRowCount = 0;
}
else
{
autoFilter = new AutoFilter()
{
Reference = reference
};
}
var tablesColumns = new TableColumns()
{
Count = (UInt32Value)(UInt32)Columns.Count
};
var i = 1;
foreach (var column in Columns)
{
tablesColumns.Append(column.ToOpenXmlColumn(i));
if (autoFilter != null && column.FilterValues.Any())
{
var filterColumn = new FilterColumn()
{
ColumnId = (UInt32)i - 1
};
var filters = new Filters();
foreach (var filterValue in column.FilterValues)
{
filters.AppendChild(new Filter()
{
Val = filterValue
});
}
filterColumn.AppendChild(filters);
autoFilter.AppendChild(filterColumn);
}
i++;
}
TableStyleInfo tableStyle = new TableStyleInfo()
{
Name = StyleName, ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false,
};
if (autoFilter != null)
{
table.Append(autoFilter);
}
table.Append(tablesColumns);
table.Append(tableStyle);
return(table);
}
private void writeHyperlinks(OpenXmlWriter writer, WorksheetPart woorksheetPart, SpreadsheetHyperlinkManager hyperlinkManager)
{
var hyperlinks = hyperlinkManager.GetHyperlinks();
if (!hyperlinks.Any())
{
return;
}
var hyperlinkTargetRelationshipIds = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
var clickableHyperLinks = new HashSet <KeyValuePair <SpreadsheetLocation, SpreadsheetHyperLink> >();
// we must add external URLs as relationships and reference them in the hyperlinks
// the more unique URLs there are the more performance problems there are
// because of this, a limit of INT_MaxUniqueHyperlinks was put in place
// without it the export can take 10x longer and it only gets worse the more unique links there are
// why is that?
// when adding a new relationship there is a uniqueness check. The check is performed sequentialy so the actual impact i O(n^2)
// also this part does not seem streamable so all of the openxml element would need to be stored in the memory
foreach (var hyperlink in hyperlinks.Where(x => !string.IsNullOrEmpty(x.Value.Target)))
{
var target = hyperlink.Value.Target;
if (hyperlinkTargetRelationshipIds.Count > MaxUniqueHyperlinks)
{
break;
}
clickableHyperLinks.Add(hyperlink);
if (!hyperlinkTargetRelationshipIds.ContainsKey(target))
{
var uri = Utilities.SafelyCreateUri(target);
if (uri == null)
{
hyperlinkTargetRelationshipIds[target] = "";
continue;
}
var relId = woorksheetPart.AddHyperlinkRelationship(uri, true).Id;
hyperlinkTargetRelationshipIds[target] = relId;
}
}
if (clickableHyperLinks.Count == 0)
{
return;
}
writer.WriteStartElement(new Hyperlinks());
foreach (var link in clickableHyperLinks)
{
var attributes = new List <OpenXmlAttribute>();
attributes.Add(new OpenXmlAttribute("ref", null, string.Format("{0}{1}", SpreadsheetHelper.ExcelColumnFromNumber(link.Key.ColumnIndex), link.Key.RowIndex)));
string id;
if (hyperlinkTargetRelationshipIds.TryGetValue(link.Value.Target, out id) && !string.IsNullOrEmpty(id))
{
var idAtt = new OpenXmlAttribute("r", "id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", hyperlinkTargetRelationshipIds[link.Value.Target]);
attributes.Add(idAtt);
}
else
{
attributes.Add(new OpenXmlAttribute("location", null, link.Value.Target));
attributes.Add(new OpenXmlAttribute("display", null, link.Value.DisplayValue));
}
writer.WriteStartElement(new Hyperlink(), attributes);
writer.WriteEndElement();
}
writer.WriteEndElement();
}