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(); }
protected InlineString FormatMessage(string value, string cellReference) { var returnObject = new InlineString(); var anchors = Anchor.Find(value); if (!anchors.Any()) { returnObject.Append(new Run(new Text(value.TrimEnd(Environment.NewLine.ToCharArray())) { Space = SpaceProcessingModeValues.Preserve })); } else { // Use the first instance because only one hyperlink per cell is allowed var anchor = anchors[0]; var hyperLinkId = string.Concat(cellReference, "HyperLink"); WorksheetPart.AddHyperlinkRelationship(new System.Uri(anchor.Href, System.UriKind.Absolute), true, hyperLinkId); Hyperlinks.Append(new Hyperlink() { Reference = cellReference, Id = hyperLinkId }); // The entire cell will be a hyperlink but only the anchor text will look like a link var valueWithoutAnchor = value.Replace(anchor.Value, string.Empty).TrimEnd('.').TrimEnd(Environment.NewLine.ToCharArray()); Run run1 = new Run(); RunProperties runProperties1 = new RunProperties(); FontSize fontSize3 = new FontSize() { Val = 11D }; Color color3 = new Color() { Theme = (UInt32Value)1U }; RunFont runFont1 = new RunFont() { Val = "Calibri" }; FontFamily fontFamily1 = new FontFamily() { Val = 2 }; FontScheme fontScheme4 = new FontScheme() { Val = FontSchemeValues.Minor }; runProperties1.Append(fontSize3); runProperties1.Append(color3); runProperties1.Append(runFont1); runProperties1.Append(fontFamily1); runProperties1.Append(fontScheme4); Text text1 = new Text(); text1.Text = valueWithoutAnchor; text1.Space = SpaceProcessingModeValues.Preserve; run1.Append(runProperties1); run1.Append(text1); Run run2 = new Run(); RunProperties runProperties2 = new RunProperties(); Underline underline2 = new Underline(); FontSize fontSize4 = new FontSize() { Val = 11D }; Color color4 = new Color() { Theme = (UInt32Value)10U }; RunFont runFont2 = new RunFont() { Val = "Calibri" }; FontFamily fontFamily2 = new FontFamily() { Val = 2 }; FontScheme fontScheme5 = new FontScheme() { Val = FontSchemeValues.Minor }; runProperties2.Append(underline2); runProperties2.Append(fontSize4); runProperties2.Append(color4); runProperties2.Append(runFont2); runProperties2.Append(fontFamily2); runProperties2.Append(fontScheme5); Text text2 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text2.Text = string.Concat(anchor.Text, "."); run2.Append(runProperties2); run2.Append(text2); returnObject.Append(run1); returnObject.Append(run2); } return(returnObject); }
private void Button_Click_1(object sender, RoutedEventArgs e) { string mainGoAction = Properties.Settings.Default.mainGoAction; if (mainGoAction == "email") { selectedOption.IsEnabled = true; notSelectedOption.IsEnabled = true; if (dataGrid.SelectedItems.Count == 0) { selectedOption.IsEnabled = false; } if (dataGrid.SelectedItems.Count == dataGrid.Items.Count) { notSelectedOption.IsEnabled = false; } System.Windows.Controls.Button senderButton = sender as System.Windows.Controls.Button; System.Windows.Controls.ContextMenu contextMenu = senderButton.ContextMenu; contextMenu.IsEnabled = true; contextMenu.PlacementTarget = senderButton; contextMenu.Placement = System.Windows.Controls.Primitives.PlacementMode.Left; contextMenu.IsOpen = true; } else if (mainGoAction == "excel") { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx"; if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(saveFileDialog.FileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorkbookStylesPart workbookStylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); workbookStylePart.Stylesheet = new Stylesheet( new DocumentFormat.OpenXml.Spreadsheet.Fonts( new DocumentFormat.OpenXml.Spreadsheet.Font(), new DocumentFormat.OpenXml.Spreadsheet.Font() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = 10 }, Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline() }, new DocumentFormat.OpenXml.Spreadsheet.Font() { Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold() }), new Fills( new Fill()), new Borders( new DocumentFormat.OpenXml.Spreadsheet.Border()), new CellFormats( new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0 }, new CellFormat() { NumberFormatId = 14, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) }, new CellFormat() { NumberFormatId = 10, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) }, new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 1, BorderId = 0, FillId = 0 }, new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 2, BorderId = 0, FillId = 0 })); workbookStylePart.Stylesheet.Save(); Hyperlinks hyperlinks = new Hyperlinks(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); worksheetPart.Worksheet.Append(hyperlinks); worksheetPart.Worksheet.Save(); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Table" }; sheets.Append(sheet); SharedStringTablePart sharedStringTablePart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>(); sharedStringTablePart.SharedStringTable = new SharedStringTable(); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); sheetData.Append(new Row( new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Requestor E-mail")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Feedback Rate")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of Feedbacks")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of No Feedbacks")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Reference")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Publish Date")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Title")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 })); int rowCounter = 1; foreach (UserData userData in dataGridSource) { foreach (SimRequestData simRequestData in userData.simRequests) { rowCounter++; sheetData.Append(new Row( new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, userData.email.ToString())), DataType = new EnumValue <CellValues>(CellValues.SharedString) }, new Cell() { CellValue = new CellValue((Convert.ToDouble(userData.rate) / 100d).ToString()), StyleIndex = 2 }, new Cell() { CellValue = new CellValue(userData.feedbacks.ToString()) }, new Cell() { CellValue = new CellValue(userData.noFeedbacks.ToString()) }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.reference.ToString())), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 3 }, new Cell() { CellValue = new CellValue(Convert.ToDateTime(simRequestData.publishDate).ToOADate().ToString()), StyleIndex = 1 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.title.ToString())), DataType = new EnumValue <CellValues>(CellValues.SharedString) } )); string hyperlinkID = "id" + rowCounter.ToString(); hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink() { Reference = "E" + rowCounter.ToString(), Id = hyperlinkID }); worksheetPart.AddHyperlinkRelationship(simRequestData.requestLink, true, hyperlinkID); } } workbookPart.Workbook.Save(); } } catch (Exception exception) { System.Windows.MessageBox.Show(exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK); } } } }