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