private static void GenerateCellContentStyle(SharedStringItem shareStringItem, string font, bool isBold, bool isItalic, string str)
{
Run run = new Run();
RunProperties runProperties = new RunProperties();
Bold bold = new Bold();
Italic italic = new Italic();
DocumentFormat.OpenXml.Spreadsheet.FontSize fontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
{
Val = 11D
};
Color color = new Color()
{
Theme = (UInt32Value)1U
};
RunFont runFont = new RunFont()
{
Val = font
};
FontFamily fontFamily = new FontFamily()
{
Val = 2
};
FontScheme fontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
if (isBold)
{
runProperties.Append(bold);
}
if (isItalic)
{
runProperties.Append(italic);
}
runProperties.Append(fontSize);
runProperties.Append(color);
runProperties.Append(runFont);
runProperties.Append(fontFamily);
runProperties.Append(fontScheme);
Text text1 = new Text()
{
Space = SpaceProcessingModeValues.Preserve
};
text1.Text = str;
run.Append(runProperties);
run.Append(text1);
shareStringItem.Append(run);
}
public static RunProperties[][] GetAminoAcidRunProperties()
{
var runPropertiesArray = new RunProperties[AminoAcidGroups.AminoAcidGroups.GetTotalGroups()][];
foreach (AminoAcidGroups.AminoAcidGroups.EnumAminoAcidGroups enumAminoAcidGroups in Enum.GetValues(typeof(AminoAcidGroups.AminoAcidGroups.EnumAminoAcidGroups)))
{
runPropertiesArray[(int)enumAminoAcidGroups] = new RunProperties[AminoAcidGroups.AminoAcidGroups.GetTotalSubgroups(enumAminoAcidGroups)];
for (var index = 0; index < runPropertiesArray[(int)enumAminoAcidGroups].Length; index++)
{
RunProperties runProperties = new RunProperties();
FontSize fontSize = new FontSize()
{
Val = 10D
};
Color color = new Color()
{
Rgb = "FF000000"
};
RunFont runFont = new RunFont()
{
Val = "Consolas"
};
FontFamily fontFamily = new FontFamily()
{
Val = 3
};
runProperties.Append(fontSize);
runProperties.Append(color);
runProperties.Append(runFont);
runProperties.Append(fontFamily);
runPropertiesArray[(int)enumAminoAcidGroups][index] = runProperties;
}
}
return(runPropertiesArray);
}
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 static Run GetRun(IXLRichString rt)
{
var run = new Run();
var runProperties = new RunProperties();
var bold = rt.Bold ? new Bold() : null;
var italic = rt.Italic ? new Italic() : null;
var underline = rt.Underline != XLFontUnderlineValues.None
? new Underline {Val = rt.Underline.ToOpenXml()}
: null;
var strike = rt.Strikethrough ? new Strike() : null;
var verticalAlignment = new VerticalTextAlignment
{Val = rt.VerticalAlignment.ToOpenXml()};
var shadow = rt.Shadow ? new Shadow() : null;
var fontSize = new FontSize {Val = rt.FontSize};
var color = GetNewColor(rt.FontColor);
var fontName = new RunFont {Val = rt.FontName};
var fontFamilyNumbering = new FontFamily {Val = (Int32)rt.FontFamilyNumbering};
if (bold != null) runProperties.Append(bold);
if (italic != null) runProperties.Append(italic);
if (strike != null) runProperties.Append(strike);
if (shadow != null) runProperties.Append(shadow);
if (underline != null) runProperties.Append(underline);
runProperties.Append(verticalAlignment);
runProperties.Append(fontSize);
runProperties.Append(color);
runProperties.Append(fontName);
runProperties.Append(fontFamilyNumbering);
var text = new Text {Text = rt.Text};
if (rt.Text.PreserveSpaces())
text.Space = SpaceProcessingModeValues.Preserve;
run.Append(runProperties);
run.Append(text);
return run;
}
internal void FromRun(Run r)
{
this.SetAllNull();
using (OpenXmlReader oxr = OpenXmlReader.Create(r))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Text))
{
this.Text = ((Text)oxr.LoadCurrentElement()).Text;
}
else if (oxr.ElementType == typeof(RunFont))
{
RunFont rft = (RunFont)oxr.LoadCurrentElement();
if (rft.Val != null)
{
this.Font.FontName = rft.Val.Value;
}
}
else if (oxr.ElementType == typeof(RunPropertyCharSet))
{
RunPropertyCharSet rpcs = (RunPropertyCharSet)oxr.LoadCurrentElement();
if (rpcs.Val != null)
{
this.Font.CharacterSet = rpcs.Val.Value;
}
}
else if (oxr.ElementType == typeof(FontFamily))
{
FontFamily ff = (FontFamily)oxr.LoadCurrentElement();
if (ff.Val != null)
{
this.Font.FontFamily = ff.Val.Value;
}
}
else if (oxr.ElementType == typeof(Bold))
{
Bold b = (Bold)oxr.LoadCurrentElement();
if (b.Val != null)
{
this.Font.Bold = b.Val.Value;
}
else
{
this.Font.Bold = true;
}
}
else if (oxr.ElementType == typeof(Italic))
{
Italic itlc = (Italic)oxr.LoadCurrentElement();
if (itlc.Val != null)
{
this.Font.Italic = itlc.Val.Value;
}
else
{
this.Font.Italic = true;
}
}
else if (oxr.ElementType == typeof(Strike))
{
Strike strk = (Strike)oxr.LoadCurrentElement();
if (strk.Val != null)
{
this.Font.Strike = strk.Val.Value;
}
else
{
this.Font.Strike = true;
}
}
else if (oxr.ElementType == typeof(Outline))
{
Outline outln = (Outline)oxr.LoadCurrentElement();
if (outln.Val != null)
{
this.Font.Outline = outln.Val.Value;
}
else
{
this.Font.Outline = true;
}
}
else if (oxr.ElementType == typeof(Shadow))
{
Shadow shdw = (Shadow)oxr.LoadCurrentElement();
if (shdw.Val != null)
{
this.Font.Shadow = shdw.Val.Value;
}
else
{
this.Font.Shadow = true;
}
}
else if (oxr.ElementType == typeof(Condense))
{
Condense cdns = (Condense)oxr.LoadCurrentElement();
if (cdns.Val != null)
{
this.Font.Condense = cdns.Val.Value;
}
else
{
this.Font.Condense = true;
}
}
else if (oxr.ElementType == typeof(Extend))
{
Extend ext = (Extend)oxr.LoadCurrentElement();
if (ext.Val != null)
{
this.Font.Extend = ext.Val.Value;
}
else
{
this.Font.Extend = true;
}
}
else if (oxr.ElementType == typeof(Color))
{
this.Font.clrFontColor.FromSpreadsheetColor((Color)oxr.LoadCurrentElement());
this.Font.HasFontColor = !this.Font.clrFontColor.IsEmpty();
}
else if (oxr.ElementType == typeof(FontSize))
{
FontSize ftsz = (FontSize)oxr.LoadCurrentElement();
if (ftsz.Val != null)
{
this.Font.FontSize = ftsz.Val.Value;
}
}
else if (oxr.ElementType == typeof(Underline))
{
Underline und = (Underline)oxr.LoadCurrentElement();
if (und.Val != null)
{
this.Font.Underline = und.Val.Value;
}
else
{
this.Font.Underline = UnderlineValues.Single;
}
}
else if (oxr.ElementType == typeof(VerticalTextAlignment))
{
VerticalTextAlignment vta = (VerticalTextAlignment)oxr.LoadCurrentElement();
if (vta.Val != null)
{
this.Font.VerticalAlignment = vta.Val.Value;
}
}
else if (oxr.ElementType == typeof(FontScheme))
{
FontScheme ftsch = (FontScheme)oxr.LoadCurrentElement();
if (ftsch.Val != null)
{
this.Font.FontScheme = ftsch.Val.Value;
}
}
}
}
}
// Generates content of worksheetCommentsPart1.
private void GenerateWorksheetCommentsPart1Content(WorksheetCommentsPart worksheetCommentsPart1)
{
Comments comments1 = new Comments();
Authors authors1 = new Authors();
Author author1 = new Author();
author1.Text = "Author";
authors1.Append(author1);
CommentList commentList1 = new CommentList();
Comment comment1 = new Comment() { Reference = "V10", AuthorId = (UInt32Value)0U, ShapeId = (UInt32Value)0U };
CommentText commentText1 = new CommentText();
Run run14 = new Run();
RunProperties runProperties14 = new RunProperties();
Bold bold1 = new Bold();
FontSize fontSize1 = new FontSize() { Val = 9D };
Color color1 = new Color() { Indexed = (UInt32Value)81U };
RunFont runFont1 = new RunFont() { Val = "Tahoma" };
RunPropertyCharSet runPropertyCharSet1 = new RunPropertyCharSet() { Val = 1 };
runProperties14.Append(bold1);
runProperties14.Append(fontSize1);
runProperties14.Append(color1);
runProperties14.Append(runFont1);
runProperties14.Append(runPropertyCharSet1);
Text text14 = new Text();
text14.Text = "Author:";
run14.Append(runProperties14);
run14.Append(text14);
Run run15 = new Run();
RunProperties runProperties15 = new RunProperties();
FontSize fontSize2 = new FontSize() { Val = 9D };
Color color2 = new Color() { Indexed = (UInt32Value)81U };
RunFont runFont2 = new RunFont() { Val = "Tahoma" };
RunPropertyCharSet runPropertyCharSet2 = new RunPropertyCharSet() { Val = 1 };
runProperties15.Append(fontSize2);
runProperties15.Append(color2);
runProperties15.Append(runFont2);
runProperties15.Append(runPropertyCharSet2);
Text text15 = new Text() { Space = SpaceProcessingModeValues.Preserve };
text15.Text = "\nThis is a comment";
run15.Append(runProperties15);
run15.Append(text15);
commentText1.Append(run14);
commentText1.Append(run15);
comment1.Append(commentText1);
commentList1.Append(comment1);
comments1.Append(authors1);
comments1.Append(commentList1);
worksheetCommentsPart1.Comments = comments1;
}
/// <summary>
/// Adds all the comments defined in the List to the current worksheet.
/// </summary>
/// <param name="worksheetPart">Worksheet Part of file.</param>
/// <param name="commentsToAddList">List of CellComment which contain cell coordinates and the text value to set as comment.</param>
public static void InsertComments(WorksheetPart worksheetPart, List <CellComment> commentsToAddList)
{
if (commentsToAddList.Any())
{
string commentsVmlXml = string.Empty;
// Create all the comment VML Shape XML
foreach (var commentToAdd in commentsToAddList)
{
commentsVmlXml += GetCommentVMLShapeXML(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row.ToString());
}
// The VMLDrawingPart should contain all the definitions for how to draw every comment shape for the worksheet
VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart <VmlDrawingPart>();
using (XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8))
{
writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n" +
"</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\"\r\n path=\"m,l,21600r21600,l21600,xe\">\r\n <v:stroke joinstyle=\"miter\"/>\r\n <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n </v:shapetype>"
+ commentsVmlXml + "</xml>");
}
// Create the comment elements
foreach (var commentToAdd in commentsToAddList)
{
WorksheetCommentsPart worksheetCommentsPart = worksheetPart.WorksheetCommentsPart ?? worksheetPart.AddNewPart <WorksheetCommentsPart>();
// We only want one legacy drawing element per worksheet for comments
if (worksheetPart.Worksheet.Descendants <LegacyDrawing>().SingleOrDefault() == null)
{
string vmlPartId = worksheetPart.GetIdOfPart(vmlDrawingPart);
LegacyDrawing legacyDrawing = new LegacyDrawing()
{
Id = vmlPartId
};
worksheetPart.Worksheet.Append(legacyDrawing);
}
Comments comments;
bool appendComments = false;
if (worksheetPart.WorksheetCommentsPart.Comments != null)
{
comments = worksheetPart.WorksheetCommentsPart.Comments;
}
else
{
comments = new Comments();
appendComments = true;
}
// We only want one Author element per Comments element
if (worksheetPart.WorksheetCommentsPart.Comments == null)
{
Authors authors = new Authors();
Author author = new Author
{
Text = "Author Name"
};
authors.Append(author);
comments.Append(authors);
}
CommentList commentList;
bool appendCommentList = false;
if (worksheetPart.WorksheetCommentsPart.Comments != null &&
worksheetPart.WorksheetCommentsPart.Comments.Descendants <CommentList>().SingleOrDefault() != null)
{
commentList = worksheetPart.WorksheetCommentsPart.Comments.Descendants <CommentList>().Single();
}
else
{
commentList = new CommentList();
appendCommentList = true;
}
Comment comment = new Comment()
{
Reference = string.Concat(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row), AuthorId = (UInt32Value)0U
};
CommentText commentTextElement = new CommentText();
Run run = new Run();
RunProperties runProperties = new RunProperties();
Bold bold = new Bold();
FontSize fontSize = new FontSize()
{
Val = 8D
};
Color color = new Color()
{
Indexed = (UInt32Value)81U
};
RunFont runFont = new RunFont()
{
Val = "Tahoma"
};
RunPropertyCharSet runPropertyCharSet = new RunPropertyCharSet()
{
Val = 1
};
runProperties.Append(bold);
runProperties.Append(fontSize);
runProperties.Append(color);
runProperties.Append(runFont);
runProperties.Append(runPropertyCharSet);
Text text = new Text
{
Text = commentToAdd.text
};
run.Append(runProperties);
run.Append(text);
commentTextElement.Append(run);
comment.Append(commentTextElement);
commentList.Append(comment);
// Only append the Comment List if this is the first time adding a comment
if (appendCommentList)
{
comments.Append(commentList);
}
// Only append the Comments if this is the first time adding Comments
if (appendComments)
{
worksheetCommentsPart.Comments = comments;
}
}
}
}
