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(); }