public static WmlDocument AddToc(WmlDocument document, string xPath, string switches, string title, int? rightTabPos)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
AddToc(doc, xPath, switches, title, rightTabPos);
}
return streamDoc.GetModifiedWmlDocument();
}
}
public static OpenXmlPowerToolsDocument Insert(OpenXmlPowerToolsDocument doc, IEnumerable<string> certificateList)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (Package package = streamDoc.GetPackage())
{
foreach (string digitalCertificate in certificateList)
{
X509Certificate x509Certificate = X509Certificate2.CreateFromCertFile(digitalCertificate);
PackageDigitalSignatureManager digitalSigntaureManager = new PackageDigitalSignatureManager(package);
digitalSigntaureManager.CertificateOption = CertificateEmbeddingOption.InSignaturePart;
System.Collections.Generic.List<Uri> partsToSign = new System.Collections.Generic.List<Uri>();
//Adds each part to the list, except relationships parts.
foreach (PackagePart openPackagePart in package.GetParts())
{
if (!PackUriHelper.IsRelationshipPartUri(openPackagePart.Uri))
partsToSign.Add(openPackagePart.Uri);
}
List<PackageRelationshipSelector> relationshipSelectors = new List<PackageRelationshipSelector>();
//Creates one selector for each package-level relationship, based on id
foreach (PackageRelationship relationship in package.GetRelationships())
{
PackageRelationshipSelector relationshipSelector =
new PackageRelationshipSelector(relationship.SourceUri, PackageRelationshipSelectorType.Id, relationship.Id);
relationshipSelectors.Add(relationshipSelector);
}
digitalSigntaureManager.Sign(partsToSign, x509Certificate, relationshipSelectors);
}
}
return streamDoc.GetModifiedDocument();
}
}
/// <summary>
/// Sets the document theme
/// </summary>
/// <param name="theme">Theme package</param>
public static OpenXmlPowerToolsDocument SetTheme(WmlDocument doc, OpenXmlPowerToolsDocument themeDoc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
using (OpenXmlMemoryStreamDocument themeStream = new OpenXmlMemoryStreamDocument(themeDoc))
using (Package theme = themeStream.GetPackage())
{
// Gets the theme manager part
PackageRelationship themeManagerRelationship =
theme.GetRelationshipsByType(mainDocumentRelationshipType).FirstOrDefault();
if (themeManagerRelationship != null)
{
PackagePart themeManagerPart = theme.GetPart(themeManagerRelationship.TargetUri);
// Gets the theme main part
PackageRelationship themeRelationship =
themeManagerPart.GetRelationshipsByType(themeRelationshipType).FirstOrDefault();
if (themeRelationship != null)
{
PackagePart themePart = theme.GetPart(themeRelationship.TargetUri);
XDocument newThemeDocument = XDocument.Load(XmlReader.Create(themePart.GetStream(FileMode.Open, FileAccess.Read)));
// Removes existing theme part from document
if (document.MainDocumentPart.ThemePart != null)
{
document.MainDocumentPart.DeletePart(document.MainDocumentPart.ThemePart);
}
// Creates a new theme part
ThemePart documentThemePart = document.MainDocumentPart.AddNewPart <ThemePart>();
var embeddedItems =
newThemeDocument
.Descendants()
.Attributes(relationshipns + "embed");
foreach (PackageRelationship imageRelationship in themePart.GetRelationships())
{
// Adds an image part to the theme part and stores contents inside
PackagePart imagePart = theme.GetPart(imageRelationship.TargetUri);
ImagePart newImagePart =
documentThemePart.AddImagePart(GetImagePartType(imagePart.ContentType));
newImagePart.FeedData(imagePart.GetStream(FileMode.Open, FileAccess.Read));
// Updates relationship references into the theme XDocument
XAttribute relationshipAttribute = embeddedItems.FirstOrDefault(e => e.Value == imageRelationship.Id);
if (relationshipAttribute != null)
{
relationshipAttribute.Value = documentThemePart.GetIdOfPart(newImagePart);
}
}
documentThemePart.PutXDocument(newThemeDocument);
}
}
}
}
return(streamDoc.GetModifiedDocument());
}
}
/// <summary>
/// Tests a Digital Signature from a package
/// </summary>
/// <returns>Digital signatures list</returns>
public static Collection <string> GetList(OpenXmlPowerToolsDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
// Creates the PackageDigitalSignatureManager
PackageDigitalSignatureManager digitalSignatureManager = new PackageDigitalSignatureManager(streamDoc.GetPackage());
// Verifies the collection of certificates in the package
Collection <string> digitalSignatureDescriptions = new Collection <string>();
ReadOnlyCollection <PackageDigitalSignature> digitalSignatures = digitalSignatureManager.Signatures;
if (digitalSignatures.Count > 0)
{
foreach (PackageDigitalSignature signature in digitalSignatures)
{
if (PackageDigitalSignatureManager.VerifyCertificate(signature.Signer) != X509ChainStatusFlags.NoError)
{
digitalSignatureDescriptions.Add(string.Format(System.Globalization.CultureInfo.InvariantCulture, "Signature: {0} ({1})", signature.Signer.Subject, PackageDigitalSignatureManager.VerifyCertificate(signature.Signer)));
}
else
{
digitalSignatureDescriptions.Add("Signature: " + signature.Signer.Subject);
}
}
}
else
{
digitalSignatureDescriptions.Add("No digital signatures found");
}
return(digitalSignatureDescriptions);
}
}
/// <summary>
/// Sets the document background color
/// </summary>
/// <param name="colorValue">String representation of the hexadecimal RGB color</param>
public static OpenXmlPowerToolsDocument SetColor(WmlDocument doc, string colorValue)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainDocument = document.MainDocumentPart.GetXDocument();
// If the background element already exists, deletes it
XElement backgroundElement = BackgroundElement(document);
if (backgroundElement != null)
{
backgroundElement.Remove();
}
mainDocument.Root.AddFirst(
new XElement(ns + "background",
new XAttribute(ns + "color", colorValue)
)
);
// Enables background displaying by adding "displayBackgroundShape" tag
if (SettingAccessor.DisplayBackgroundShapeElements(document) == null)
{
SettingAccessor.AddBackgroundShapeElement(document);
}
document.MainDocumentPart.PutXDocument();
}
return(streamDoc.GetModifiedDocument());
}
}
/// <summary>
/// Set a new header in a document
/// </summary>
/// <param name="header">XDocument containing the header to add in the document</param>
/// <param name="type">The header part type</param>
public static OpenXmlPowerToolsDocument SetHeader(WmlDocument doc, XDocument header, HeaderType type, int sectionIndex)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
// Removes the reference in the document.xml and the header part if those already
// exist
XElement headerReferenceElement = GetHeaderReference(document, type, sectionIndex);
if (headerReferenceElement != null)
{
GetHeaderPart(document, type, sectionIndex).RemovePart();
headerReferenceElement.Remove();
}
// Add the new header
HeaderPart headerPart = document.MainDocumentPart.AddNewPart <HeaderPart>();
headerPart.PutXDocument(header);
// Creates the relationship of the header inside the section properties in the document
string relID = document.MainDocumentPart.GetIdOfPart(headerPart);
AddHeaderReference(document, type, relID, sectionIndex);
// add in the settings part the EvendAndOddHeaders. this element
// allow to see the odd and even headers and headers in the document.
SettingAccessor.AddEvenAndOddHeadersElement(document);
}
return(streamDoc.GetModifiedDocument());
}
}
public static object GetAllComments(WmlDocument doc, CommentFormat format)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
IEnumerable<XElement> comments = null;
WordprocessingCommentsPart commentsPart = document.MainDocumentPart.WordprocessingCommentsPart;
if (commentsPart != null)
{
XDocument commentsPartDocument = commentsPart.GetXDocument();
comments = commentsPartDocument.Element(W.comments).Elements(W.comment);
}
if (comments != null)
{
XDocument commentsDocument =
new XDocument(
new XElement(W.comments,
new XAttribute(XNamespace.Xmlns + "w", W.w),
comments
)
);
switch (format)
{
case CommentFormat.PlainText:
return commentsDocument.ToString();
case CommentFormat.Xml:
return commentsDocument;
case CommentFormat.Docx:
return CreateCommentsDocument(comments);
}
}
return null;
}
}
/// <summary>
/// Tests a Digital Signature from a package
/// </summary>
/// <returns>Digital signatures list</returns>
public static Collection<string> GetList(OpenXmlPowerToolsDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
// Creates the PackageDigitalSignatureManager
PackageDigitalSignatureManager digitalSignatureManager = new PackageDigitalSignatureManager(streamDoc.GetPackage());
// Verifies the collection of certificates in the package
Collection<string> digitalSignatureDescriptions = new Collection<string>();
ReadOnlyCollection<PackageDigitalSignature> digitalSignatures = digitalSignatureManager.Signatures;
if (digitalSignatures.Count > 0)
{
foreach (PackageDigitalSignature signature in digitalSignatures)
{
if (PackageDigitalSignatureManager.VerifyCertificate(signature.Signer) != X509ChainStatusFlags.NoError)
{
digitalSignatureDescriptions.Add(string.Format(System.Globalization.CultureInfo.InvariantCulture, "Signature: {0} ({1})", signature.Signer.Subject, PackageDigitalSignatureManager.VerifyCertificate(signature.Signer)));
}
else
digitalSignatureDescriptions.Add("Signature: " + signature.Signer.Subject);
}
}
else
{
digitalSignatureDescriptions.Add("No digital signatures found");
}
return digitalSignatureDescriptions;
}
}
public WmlDocument(WmlDocument other, params XElement[] replacementParts)
: base(other)
{
using (var streamDoc = new OpenXmlMemoryStreamDocument(this))
{
using (Package package = streamDoc.GetPackage())
{
foreach (XElement replacementPart in replacementParts)
{
XAttribute uriAttribute = replacementPart.Attribute(PtOpenXml.Uri);
if (uriAttribute == null)
{
throw new OpenXmlPowerToolsException("Replacement part does not contain a Uri as an attribute");
}
string uri = uriAttribute.Value;
PackagePart part = package.GetParts().FirstOrDefault(p => p.Uri.ToString() == uri);
using (Stream partStream = part.GetStream(FileMode.Create, FileAccess.Write))
using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
replacementPart.Save(partXmlWriter);
}
}
DocumentByteArray = streamDoc.GetModifiedDocument().DocumentByteArray;
}
}
public static IEnumerable <ValidationErrorInfo> ValidateXml(OpenXmlPowerToolsDocument document)
{
if (document is WmlDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator();
return(validator.Validate(doc));
}
}
else if (document is SmlDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator();
return(validator.Validate(doc));
}
}
else if (document is PmlDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
using (PresentationDocument doc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator();
return(validator.Validate(doc));
}
}
throw new PowerToolsDocumentException("Not an Open XML document.");
}
/// <summary>
/// Apply a cell style to a specific cell
/// </summary>
/// <param name="worksheet">worksheet containing the cell to be affected</param>
/// <param name="fromColumn">Starting Cell Column</param>
/// <param name="toColumn">Ending Cell Column</param>
/// <param name="fromRow">Starting Cell Row</param>
/// <param name="toRow">Ending Cell Row</param>
/// <param name="cellStyle">Cell Style</param>
public static OpenXmlPowerToolsDocument SetCellStyle(SmlDocument doc, string worksheetName, short fromColumn, short toColumn, int fromRow, int toRow, string cellStyle)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (short col = fromColumn; col <= toColumn; col++)
{
XElement cellXelement = GetCell(worksheetXDocument, col, row);
cellXelement.SetAttributeValue("s", SpreadSheetStyleAccessor.GetCellStyleIndex(document, cellStyle));
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return(streamDoc.GetModifiedDocument());
}
}
public static OpenXmlPowerToolsDocument Insert(OpenXmlPowerToolsDocument doc, IEnumerable <string> certificateList)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (Package package = streamDoc.GetPackage())
{
foreach (string digitalCertificate in certificateList)
{
X509Certificate x509Certificate = X509Certificate2.CreateFromCertFile(digitalCertificate);
PackageDigitalSignatureManager digitalSigntaureManager = new PackageDigitalSignatureManager(package);
digitalSigntaureManager.CertificateOption = CertificateEmbeddingOption.InSignaturePart;
System.Collections.Generic.List <Uri> partsToSign = new System.Collections.Generic.List <Uri>();
//Adds each part to the list, except relationships parts.
foreach (PackagePart openPackagePart in package.GetParts())
{
if (!PackUriHelper.IsRelationshipPartUri(openPackagePart.Uri))
{
partsToSign.Add(openPackagePart.Uri);
}
}
List <PackageRelationshipSelector> relationshipSelectors = new List <PackageRelationshipSelector>();
//Creates one selector for each package-level relationship, based on id
foreach (PackageRelationship relationship in package.GetRelationships())
{
PackageRelationshipSelector relationshipSelector =
new PackageRelationshipSelector(relationship.SourceUri, PackageRelationshipSelectorType.Id, relationship.Id);
relationshipSelectors.Add(relationshipSelector);
}
digitalSigntaureManager.Sign(partsToSign, x509Certificate, relationshipSelectors);
}
}
return(streamDoc.GetModifiedDocument());
}
}
static void Main(string[] args)
{
// Change sheet name in formulas
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../Formulas.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.FormulaReplaceSheetName(doc, "Source", "'Source 2'");
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../FormulasUpdated.xlsx");
}
// Change sheet name in formulas
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../Formulas.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "References");
WorksheetAccessor.CopyCellRange(doc, sheet, 1, 1, 7, 5, 4, 8);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../FormulasCopied.xlsx");
}
}
/// <summary>
/// Inserts Xml markup representing format attributes inside a specific paragraph or paragraph run
/// </summary>
/// <param name="document">Document to insert formatting Xml tags</param>
/// <param name="xpathInsertionPoint">Paragraph or paragraph run to set format</param>
/// <param name="content">Formatting tags</param>
public static OpenXmlPowerToolsDocument Insert(WmlDocument doc, string xpathInsertionPoint, string content)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument xDocument = document.MainDocumentPart.GetXDocument();
XmlDocument xmlMainDocument = new XmlDocument();
xmlMainDocument.Load(xDocument.CreateReader());
XmlNamespaceManager namespaceManager = new XmlNamespaceManager(new NameTable());
namespaceManager.AddNamespace("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
XmlNodeList insertionPoints = xmlMainDocument.SelectNodes(xpathInsertionPoint, namespaceManager);
if (insertionPoints.Count == 0)
{
throw new ArgumentException("The xpath query did not return a valid location.");
}
foreach (XmlNode insertionPoint in insertionPoints)
{
XmlNode propertiesElement = insertionPoint.SelectSingleNode(@"w:pPr|w:rPr", namespaceManager);
if (insertionPoint.Name == "w:p")
{
// Checks if the rPr or pPr element exists
if (propertiesElement == null)
{
propertiesElement = xmlMainDocument.CreateElement("w", "pPr", namespaceManager.LookupNamespace("w"));
insertionPoint.PrependChild(propertiesElement);
}
}
else if (insertionPoint.Name == "w:r")
{
// Checks if the rPr or pPr element exists
if (propertiesElement == null)
{
propertiesElement = xmlMainDocument.CreateElement("w", "rPr", namespaceManager.LookupNamespace("w"));
insertionPoint.PrependChild(propertiesElement);
}
}
if (propertiesElement != null)
{
propertiesElement.InnerXml += content;
}
else
{
throw new ArgumentException("Specified xpath query result is not a valid location to place a formatting markup");
}
}
XDocument xNewDocument = new XDocument();
using (XmlWriter xWriter = xNewDocument.CreateWriter())
xmlMainDocument.WriteTo(xWriter);
document.MainDocumentPart.PutXDocument(xNewDocument);
}
return(streamDoc.GetModifiedDocument());
}
}
public static XDocument GetStylesDocument(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
return(GetStylesDocument(document));
}
}
/// <summary>
/// Inserts Xml markup representing format attributes inside a specific paragraph or paragraph run
/// </summary>
/// <param name="document">Document to insert formatting Xml tags</param>
/// <param name="xpathInsertionPoint">Paragraph or paragraph run to set format</param>
/// <param name="content">Formatting tags</param>
public static OpenXmlPowerToolsDocument Insert(WmlDocument doc, string xpathInsertionPoint, string content)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument xDocument = document.MainDocumentPart.GetXDocument();
XmlDocument xmlMainDocument = new XmlDocument();
xmlMainDocument.Load(xDocument.CreateReader());
XmlNamespaceManager namespaceManager = new XmlNamespaceManager(new NameTable());
namespaceManager.AddNamespace("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
XmlNodeList insertionPoints = xmlMainDocument.SelectNodes(xpathInsertionPoint, namespaceManager);
if (insertionPoints.Count == 0)
throw new ArgumentException("The xpath query did not return a valid location.");
foreach (XmlNode insertionPoint in insertionPoints)
{
XmlNode propertiesElement = insertionPoint.SelectSingleNode(@"w:pPr|w:rPr", namespaceManager);
if (insertionPoint.Name == "w:p")
{
// Checks if the rPr or pPr element exists
if (propertiesElement == null)
{
propertiesElement = xmlMainDocument.CreateElement("w", "pPr", namespaceManager.LookupNamespace("w"));
insertionPoint.PrependChild(propertiesElement);
}
}
else if (insertionPoint.Name == "w:r")
{
// Checks if the rPr or pPr element exists
if (propertiesElement == null)
{
propertiesElement = xmlMainDocument.CreateElement("w", "rPr", namespaceManager.LookupNamespace("w"));
insertionPoint.PrependChild(propertiesElement);
}
}
if (propertiesElement != null)
{
propertiesElement.InnerXml += content;
}
else
{
throw new ArgumentException("Specified xpath query result is not a valid location to place a formatting markup");
}
}
XDocument xNewDocument = new XDocument();
using (XmlWriter xWriter = xNewDocument.CreateWriter())
xmlMainDocument.WriteTo(xWriter);
document.MainDocumentPart.PutXDocument(xNewDocument);
}
return streamDoc.GetModifiedDocument();
}
}
/// <summary>
/// Set the width for a range of columns
/// </summary>
/// <param name="worksheet">Worksheet containing the columns to be affected</param>
/// <param name="fromColumn">Initial column to affect</param>
/// <param name="toColumn">Final column to affect</param>
/// <param name="width">Column width</param>
public static OpenXmlPowerToolsDocument SetColumnWidth(SmlDocument doc, string worksheetName, short fromColumn, short toColumn, int width)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
//Get the worksheet markup
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
//Look for worksheet cols element
XElement colsXElement = worksheetXDocument.Root.Element(ns + "cols");
if (colsXElement == null)
{
//cols elements does not exist
//create a new one
colsXElement = new XElement(ns + "cols");
//create a new col element (for setting the width)
//the col element could span more than one column -span is controlled by min (initial column) and max (final column) attributes
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
//cols element must be added before worksheet sheetData element
worksheetXDocument.Root.Element(ns + "sheetData").AddBeforeSelf(colsXElement);
}
else
{
//look for a col element for the column range indicated for fromColumn and toColumn
XElement colXElement = colsXElement.Elements(ns + "col")
.Where(c => (System.Convert.ToInt32(c.Attribute("min").Value) == fromColumn) && (System.Convert.ToInt32(c.Attribute("max").Value) == toColumn)).FirstOrDefault();
if (colXElement != null)
{
//col element does exist
//change its width value
colXElement.SetAttributeValue("width", width);
}
else
{
//col element does not exist
//create a new one
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
}
}
//Update the worksheet part markup at worksheet part stream
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return(streamDoc.GetModifiedDocument());
}
}
public static IEnumerable <ValidationErrorInfo> GetOpenXmlValidationErrors(string fileName,
string officeVersion)
{
#if !NET35
FileFormatVersions fileFormatVersion = FileFormatVersions.Office2013;
#else
FileFormatVersions fileFormatVersion = FileFormatVersions.Office2010;
#endif
try
{
fileFormatVersion = (FileFormatVersions)Enum.Parse(fileFormatVersion.GetType(), officeVersion);
}
catch (Exception)
{
#if !NET35
fileFormatVersion = FileFormatVersions.Office2013;
#else
fileFormatVersion = FileFormatVersions.Office2010;
#endif
}
FileInfo fi = new FileInfo(fileName);
if (Util.IsWordprocessingML(fi.Extension))
{
WmlDocument wml = new WmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wml))
using (WordprocessingDocument wDoc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsSpreadsheetML(fi.Extension))
{
SmlDocument Sml = new SmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Sml))
using (SpreadsheetDocument wDoc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsPresentationML(fi.Extension))
{
PmlDocument Pml = new PmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Pml))
using (PresentationDocument wDoc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
return(Enumerable.Empty <ValidationErrorInfo>());
}
public static WmlDocument SimplifyMarkup(WmlDocument doc, SimplifyMarkupSettings settings)
{
using (var streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
SimplifyMarkup(document, settings);
return(streamDoc.GetModifiedWmlDocument());
}
}
/// <summary>
/// Removes the specified header in the document
/// </summary>
/// <param name="type">The header part type</param>
public void RemoveHeader(WmlDocument doc, HeaderType type, int sectionIndex)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
OpenXmlPart headerPart = GetHeaderPart(document, type, sectionIndex);
headerPart.RemovePart();
}
}
public static string GetBackgroundColor(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainDocument = document.MainDocumentPart.GetXDocument();
XElement backgroundElement = mainDocument.Descendants(W.background).FirstOrDefault();
return (backgroundElement == null) ? string.Empty : backgroundElement.Attribute(W.color).Value;
}
}
public static string GetBackgroundColor(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainDocument = document.MainDocumentPart.GetXDocument();
XElement backgroundElement = mainDocument.Descendants(W.background).FirstOrDefault();
return((backgroundElement == null) ? string.Empty : backgroundElement.Attribute(W.color).Value);
}
}
public static XElement ConvertToHtml(WmlDocument doc, HtmlConverterSettings htmlConverterSettings)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
return ConvertToHtml(document, htmlConverterSettings);
}
}
}
public static XElement ConvertToHtml(WmlDocument doc, HtmlConverterSettings htmlConverterSettings, Func <ImageInfo, XElement> imageHandler)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
return(ConvertToHtml(document, htmlConverterSettings, imageHandler));
}
}
}
public static XElement ConvertToHtml(WmlDocument doc, HtmlConverterSettings htmlConverterSettings, Func<ImageInfo, XElement> imageHandler)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
return ConvertToHtml(document, htmlConverterSettings, imageHandler);
}
}
}
public static XElement ConvertToHtml(WmlDocument doc, HtmlConverterSettings htmlConverterSettings)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
return(ConvertToHtml(document, htmlConverterSettings));
}
}
}
public static PmlDocument SearchAndReplace(PmlDocument doc, string search, string replace, bool matchCase)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (PresentationDocument document = streamDoc.GetPresentationDocument())
{
SearchAndReplace(document, search, replace, matchCase);
}
return(streamDoc.GetModifiedPmlDocument());
}
}
public static string RetrieveListItem(WmlDocument document,
XElement paragraph, string bulletReplacementString)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument wdoc = streamDoc.GetWordprocessingDocument())
{
return RetrieveListItem(wdoc, paragraph, bulletReplacementString);
}
}
}
public static WmlDocument SearchAndReplace(WmlDocument doc, string search, string replace, bool matchCase)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
SearchAndReplace(document, search, replace, matchCase);
}
return(streamDoc.GetModifiedWmlDocument());
}
}
public static string RetrieveListItem(WmlDocument document,
XElement paragraph, string bulletReplacementString)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument wdoc = streamDoc.GetWordprocessingDocument())
{
return(RetrieveListItem(wdoc, paragraph, bulletReplacementString));
}
}
}
public static WmlDocument AddToc(WmlDocument document, string xPath, string switches, string title, int?rightTabPos)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
AddToc(doc, xPath, switches, title, rightTabPos);
}
return(streamDoc.GetModifiedWmlDocument());
}
}
public static WmlDocument AssembleFormatting(WmlDocument document, FormattingAssemblerSettings settings)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
AssembleFormatting(doc, settings);
}
return streamDoc.GetModifiedWmlDocument();
}
}
public static WmlDocument SimplifyMarkup(WmlDocument doc, SimplifyMarkupSettings settings)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
SimplifyMarkup(document, settings);
}
return streamDoc.GetModifiedWmlDocument();
}
}
/// <summary>
/// Get the specified footer from the document
/// </summary>
/// <param name="type">The footer part type</param>
/// <returns>the XDocument containing the footer</returns>
public static XDocument GetFooter(WmlDocument doc, FooterType type, int sectionIndex)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
OpenXmlPart footer = GetFooterPart(document, type, sectionIndex);
if (footer != null)
return footer.GetXDocument();
return null;
}
}
public static WmlDocument AcceptRevisions(WmlDocument document)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
AcceptRevisions(doc);
}
return streamDoc.GetModifiedWmlDocument();
}
}
private static OpenXmlPowerToolsDocument CreateCommentsDocument(IEnumerable <XElement> contents)
{
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateWordprocessingDocument())
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
PowerToolsExtensions.SetContent(document, contents);
}
return(streamDoc.GetModifiedDocument());
}
}
public static WmlDocument SearchAndReplace(WmlDocument doc, string search, string replace, bool matchCase)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
SearchAndReplace(document, search, replace, matchCase);
}
return streamDoc.GetModifiedWmlDocument();
}
}
public static PmlDocument SearchAndReplace(PmlDocument doc, string search, string replace, bool matchCase)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (PresentationDocument document = streamDoc.GetPresentationDocument())
{
SearchAndReplace(document, search, replace, matchCase);
}
return streamDoc.GetModifiedPmlDocument();
}
}
public static WmlDocument AcceptRevisions(WmlDocument document)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
AcceptRevisions(doc);
}
return(streamDoc.GetModifiedWmlDocument());
}
}
/// <summary>
/// Sets the document background image
/// </summary>
/// <param name="imagePath">Path of the background image</param>
public static OpenXmlPowerToolsDocument SetImage(WmlDocument doc, string imagePath)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainDocument = document.MainDocumentPart.GetXDocument();
// Adds the image to the package
ImagePart imagePart = document.MainDocumentPart.AddImagePart(ImagePartType.Bmp);
Stream imageStream = new StreamReader(imagePath).BaseStream;
byte[] imageBytes = new byte[imageStream.Length];
imageStream.Read(imageBytes, 0, imageBytes.Length);
imagePart.GetStream().Write(imageBytes, 0, imageBytes.Length);
// Creates a "background" element relating the image and the document
// If the background element already exists, deletes it
XElement backgroundElement = BackgroundElement(document);
if (backgroundElement != null)
{
backgroundElement.Remove();
}
// Background element construction
mainDocument.Root.Add(
new XElement(ns + "background",
new XAttribute(ns + "color", defaultBackgroundColor),
new XElement(vmlns + "background",
new XAttribute(vmlns + "id", defaultVmlBackgroundImageId),
new XAttribute(officens + "bwmode", defaultBWMode),
new XAttribute(officens + "targetscreensize", defaultTargetScreenSize),
new XElement(vmlns + "fill",
new XAttribute(relationshipsns + "id", document.MainDocumentPart.GetIdOfPart(imagePart)),
new XAttribute("recolor", defaultImageRecolor),
new XAttribute("type", defaultImageType)
)
)
)
);
// Enables background displaying by adding "displayBackgroundShape" tag
if (SettingAccessor.DisplayBackgroundShapeElements(document) == null)
{
SettingAccessor.AddBackgroundShapeElement(document);
}
document.MainDocumentPart.PutXDocument();
}
return(streamDoc.GetModifiedDocument());
}
}
/// <summary>
/// RemoveAll
/// </summary>
public static OpenXmlPowerToolsDocument RemoveAll(OpenXmlPowerToolsDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (Package package = streamDoc.GetPackage())
{
// Creates the PackageDigitalSignatureManager
PackageDigitalSignatureManager digitalSignatureManager = new PackageDigitalSignatureManager(package);
digitalSignatureManager.RemoveAllSignatures();
}
return(streamDoc.GetModifiedDocument());
}
}
/// <summary>
/// Get the specified header from the document
/// </summary>
/// <param name="type">The header part type</param>
/// <returns>A XDocument containing the header</returns>
public static XDocument GetHeader(WmlDocument doc, HeaderType type, int sectionIndex)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
OpenXmlPart header = GetHeaderPart(document, type, sectionIndex);
if (header != null)
{
return(header.GetXDocument());
}
return(null);
}
}
/// <summary>
/// Searches for a custom Xml part with a given name
/// </summary>
/// <param name="xmlPartName">Name of custom Xml part</param>
/// <returns>XDocument with customXml part loaded</returns>
public static XDocument Find(WmlDocument doc, string xmlPartName)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
WordprocessingDocument document = streamDoc.GetWordprocessingDocument();
string partName = "/" + xmlPartName;
var customXmlPart =
document.MainDocumentPart.CustomXmlParts.Where(
t => t.Uri.OriginalString.EndsWith(partName, System.StringComparison.OrdinalIgnoreCase)
).FirstOrDefault();
if (customXmlPart == null)
throw new ArgumentException("Part name '" + xmlPartName + "' not found.");
return customXmlPart.GetXDocument();
}
}
public static XElement GetDocxMetrics(WmlDocument wmlDoc, MetricsGetterSettings settings)
{
WmlDocument converted = new WmlDocument(wmlDoc, true);
WmlDocument noTrackedRevisions = new WmlDocument(converted);
try
{
using (OpenXmlMemoryStreamDocument noTrackedStreamDoc = new OpenXmlMemoryStreamDocument(noTrackedRevisions))
using (WordprocessingDocument noTrackedDocument = noTrackedStreamDoc.GetWordprocessingDocument())
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(converted))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
if (RevisionAccepter.HasTrackedRevisions(noTrackedDocument))
RevisionAccepter.AcceptRevisions(noTrackedDocument);
return GetWmlMetrics(converted.FileName, false, document, noTrackedDocument, settings);
}
}
catch (OpenXmlPowerToolsException e)
{
if (e.ToString().Contains("Invalid Hyperlink"))
{
using (MemoryStream ms = new MemoryStream())
{
WmlDocument fixedWmlDoc = new WmlDocument(converted);
ms.Write(converted.DocumentByteArray, 0, converted.DocumentByteArray.Length);
#if !NET35
UriFixer.FixInvalidUri(ms, brokenUri => FixUri(brokenUri));
#endif
converted = new WmlDocument("dummy.docx", ms.ToArray());
}
noTrackedRevisions = new WmlDocument(converted);
using (OpenXmlMemoryStreamDocument noTrackedStreamDoc = new OpenXmlMemoryStreamDocument(noTrackedRevisions))
using (WordprocessingDocument noTrackedDocument = noTrackedStreamDoc.GetWordprocessingDocument())
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(converted))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
if (RevisionAccepter.HasTrackedRevisions(noTrackedDocument))
RevisionAccepter.AcceptRevisions(noTrackedDocument);
return GetWmlMetrics(converted.FileName, true, document, noTrackedDocument, settings);
}
}
}
var metrics = new XElement(H.Metrics,
new XAttribute(H.FileName, converted.FileName),
new XAttribute(H.FileType, "WordprocessingML"),
new XAttribute(H.Error, "Unknown error, metrics not determined"));
return metrics;
}
/// <summary>
/// Sets a new styles part inside the document
/// </summary>
/// <param name="newStylesDocument">Path of styles definition file</param>
public static OpenXmlPowerToolsDocument SetStylePart(WmlDocument doc, XDocument newStylesDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
// Replaces XDocument with the style file to transfer
if (document.MainDocumentPart.StyleDefinitionsPart == null)
{
document.MainDocumentPart.AddNewPart <StyleDefinitionsPart>();
}
document.MainDocumentPart.StyleDefinitionsPart.PutXDocument(newStylesDocument);
}
return(streamDoc.GetModifiedDocument());
}
}
public static IEnumerable <ValidationErrorInfo> GetOpenXmlValidationErrors(string fileName,
string officeVersion)
{
FileFormatVersions fileFormatVersion;
if (!Enum.TryParse(officeVersion, out fileFormatVersion))
{
fileFormatVersion = FileFormatVersions.Office2013;
}
FileInfo fi = new FileInfo(fileName);
if (Util.IsWordprocessingML(fi.Extension))
{
WmlDocument wml = new WmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wml))
using (WordprocessingDocument wDoc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsSpreadsheetML(fi.Extension))
{
SmlDocument Sml = new SmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Sml))
using (SpreadsheetDocument wDoc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsPresentationML(fi.Extension))
{
PmlDocument Pml = new PmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Pml))
using (PresentationDocument wDoc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
return(Enumerable.Empty <ValidationErrorInfo>());
}
/// <summary>
/// Searches for a custom Xml part with a given name
/// </summary>
/// <param name="xmlPartName">Name of custom Xml part</param>
/// <returns>XDocument with customXml part loaded</returns>
public static XDocument Find(WmlDocument doc, string xmlPartName)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
WordprocessingDocument document = streamDoc.GetWordprocessingDocument();
string partName = "/" + xmlPartName;
var customXmlPart =
document.MainDocumentPart.CustomXmlParts.Where(
t => t.Uri.OriginalString.EndsWith(partName, System.StringComparison.OrdinalIgnoreCase)
).FirstOrDefault();
if (customXmlPart == null)
{
throw new ArgumentException("Part name '" + xmlPartName + "' not found.");
}
return(customXmlPart.GetXDocument());
}
}
public static OpenXmlPowerToolsDocument Lock(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
//Finds the settings part
XDocument settingsDocument;
XElement documentProtectionElement = null;
if (document.MainDocumentPart.DocumentSettingsPart == null)
{
//If settings part does not exist creates a new one
DocumentSettingsPart settingsPart = document.MainDocumentPart.AddNewPart <DocumentSettingsPart>();
settingsDocument = settingsPart.GetXDocument();
settingsDocument.Add(new XElement(W.settings,
new XAttribute(XNamespace.Xmlns + "w", W.w),
new XAttribute(XNamespace.Xmlns + "r", R.r)));
}
else
{
//If the settings part does exist looks if documentProtection has been included
settingsDocument = document.MainDocumentPart.DocumentSettingsPart.GetXDocument();
documentProtectionElement = settingsDocument.Element(W.settings).Element(W.documentProtection);
}
//Creates the documentProtection element, or edits it if it exists
if (documentProtectionElement == null)
{
settingsDocument
.Element(W.settings)
.Add(
new XElement(W.documentProtection,
new XAttribute(W.edit, "readOnly")
)
);
}
else
{
documentProtectionElement.SetAttributeValue(W.edit, "readOnly");
}
document.MainDocumentPart.DocumentSettingsPart.PutXDocument();
}
return(streamDoc.GetModifiedDocument());
}
}
/// <summary>
/// Removes all of the comments existing in the document
/// </summary>
public static OpenXmlPowerToolsDocument RemoveAll(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
//Removes comment-related tags inside the main document part
IEnumerable<XElement> commentReferences = CommentReferences(document).ToList();
commentReferences.Remove();
document.MainDocumentPart.PutXDocument();
WordprocessingCommentsPart commentsPart = document.MainDocumentPart.WordprocessingCommentsPart;
if (commentsPart != null)
commentsPart.RemovePart();
}
return streamDoc.GetModifiedDocument();
}
}
/// <summary>
/// Replaces a previously existing customXml part by another one
/// </summary>
/// <param name="customXmlDocument">XDocument of part to replace inside the document package</param>
/// <param name="partNameOnly">Name of the part.</param>
public static OpenXmlPowerToolsDocument SetDocument(WmlDocument doc, XDocument customXmlDocument, string partNameOnly)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
string partName = "/" + partNameOnly;
var customXmlPart = document.MainDocumentPart.CustomXmlParts.Where(
t => t.Uri.OriginalString.EndsWith(partName, System.StringComparison.OrdinalIgnoreCase)
).FirstOrDefault();
if (customXmlPart == null)
customXmlPart = document.MainDocumentPart.AddCustomXmlPart(CustomXmlPartType.CustomXml);
customXmlPart.PutXDocument(customXmlDocument);
}
return streamDoc.GetModifiedDocument();
}
}
public static string GetImageFileName(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainDocument = document.MainDocumentPart.GetXDocument();
XElement fillElement = mainDocument.Descendants(W.background).Descendants(VML.fill).FirstOrDefault();
if (fillElement != null)
{
string imageRelationshipId = fillElement.Attribute(R.id).Value;
OpenXmlPart imagePart = document.MainDocumentPart.GetPartById(imageRelationshipId);
// Gets the image name (path stripped)
string imagePath = imagePart.Uri.OriginalString;
return imagePath.Substring(imagePath.LastIndexOf('/') + 1);
}
else
return string.Empty;
}
}
/// <summary>
/// Gets the document structure related to watermark description
/// </summary>
/// <returns>Document structure related to watermark description</returns>
public static IEnumerable<XElement> GetWatermark(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
// to get the watermark text, we have to look inside the document
// get the default header reference and get the header reference id part
XElement defaultHeaderReference = HeaderAccessor.GetHeaderReference(document, HeaderType.Default, 0);
if (defaultHeaderReference != null)
{
string headerReferenceId = defaultHeaderReference.Attribute(relationshipsns + "id").Value;
OpenXmlPart headerPart = document.MainDocumentPart.GetPartById(headerReferenceId);
if (headerPart != null)
{
XDocument headerPartXml = headerPart.GetXDocument();
return headerPartXml.Descendants(ns + "pict");
}
}
return null;
}
}
public static WmlDocument MergeComments(WmlDocument document1, WmlDocument document2,
bool ensureLocked)
{
WmlDocument cDoc1 = new WmlDocument(document1);
WmlDocument cDoc2 = new WmlDocument(document2);
using (OpenXmlMemoryStreamDocument streamDoc1 = new OpenXmlMemoryStreamDocument(cDoc1))
using (WordprocessingDocument doc1 = streamDoc1.GetWordprocessingDocument())
using (OpenXmlMemoryStreamDocument streamDoc2 = new OpenXmlMemoryStreamDocument(cDoc2))
using (WordprocessingDocument doc2 = streamDoc2.GetWordprocessingDocument())
{
SimplifyMarkupSettings mss = new SimplifyMarkupSettings()
{
RemoveProof = true,
RemoveRsidInfo = true,
RemoveGoBackBookmark = true,
};
MarkupSimplifier.SimplifyMarkup(doc1, mss);
MarkupSimplifier.SimplifyMarkup(doc2, mss);
// If documents don't contain the same content, then don't attempt to merge comments.
bool same = DocumentComparer.CompareDocuments(doc1, doc2);
if (!same)
throw new CommentMergerDifferingContentsException(
"Documents do not contain the same content");
if (doc1.MainDocumentPart.WordprocessingCommentsPart == null &&
doc2.MainDocumentPart.WordprocessingCommentsPart == null)
return new WmlDocument(document1);
if (doc1.MainDocumentPart.WordprocessingCommentsPart != null &&
doc2.MainDocumentPart.WordprocessingCommentsPart == null)
return new WmlDocument(document1);
if (doc1.MainDocumentPart.WordprocessingCommentsPart == null &&
doc2.MainDocumentPart.WordprocessingCommentsPart != null)
return new WmlDocument(document2);
// If either of the documents have no comments, then return the other one.
if (! doc1.MainDocumentPart.WordprocessingCommentsPart.GetXDocument().Root
.Elements(W.comment).Any())
return new WmlDocument(document2);
if (! doc2.MainDocumentPart.WordprocessingCommentsPart.GetXDocument().Root
.Elements(W.comment).Any())
return new WmlDocument(document1);
if (ensureLocked)
{
// If either document is not locked (allowing only commenting), don't attempt to
// merge comments.
if (doc1.ExtendedFilePropertiesPart.GetXDocument().Root
.Element(EP.DocSecurity).Value != "8")
throw new CommentMergerUnlockedDocumentException(
"Document1 is not locked");
if (doc2.ExtendedFilePropertiesPart.GetXDocument().Root
.Element(EP.DocSecurity).Value != "8")
throw new CommentMergerUnlockedDocumentException(
"Document2 is not locked");
}
RenumberCommentsInDoc2(doc1, doc2);
WmlDocument destDoc = new WmlDocument(document1);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(destDoc))
{
using (WordprocessingDocument destWDoc = streamDoc.GetWordprocessingDocument())
{
// Merge the comments part.
XDocument commentsPartXDoc = new XDocument(
new XElement(W.comments,
new XAttribute(XNamespace.Xmlns + "w", W.w),
doc1.MainDocumentPart.WordprocessingCommentsPart.GetXDocument().Root.Elements(),
doc2.MainDocumentPart.WordprocessingCommentsPart.GetXDocument().Root.Elements()));
destWDoc.MainDocumentPart.WordprocessingCommentsPart.PutXDocument(commentsPartXDoc);
MergeCommentsInPart(doc1.MainDocumentPart, doc2.MainDocumentPart,
destWDoc.MainDocumentPart, commentsPartXDoc);
}
return streamDoc.GetModifiedWmlDocument();
}
}
}
private static void BuildDocument(List<Source> sources, WordprocessingDocument output)
{
if (RelationshipMarkup == null)
RelationshipMarkup = new Dictionary<XName, XName[]>()
{
//{ button, new [] { image }},
{ A.blip, new [] { R.embed, R.link }},
{ A.hlinkClick, new [] { R.id }},
{ A.relIds, new [] { R.cs, R.dm, R.lo, R.qs }},
//{ a14:imgLayer, new [] { R.embed }},
//{ ax:ocx, new [] { R.id }},
{ C.chart, new [] { R.id }},
{ C.externalData, new [] { R.id }},
{ C.userShapes, new [] { R.id }},
{ DGM.relIds, new [] { R.cs, R.dm, R.lo, R.qs }},
{ O.OLEObject, new [] { R.id }},
{ VML.fill, new [] { R.id }},
{ VML.imagedata, new [] { R.href, R.id, R.pict }},
{ VML.stroke, new [] { R.id }},
{ W.altChunk, new [] { R.id }},
{ W.attachedTemplate, new [] { R.id }},
{ W.control, new [] { R.id }},
{ W.dataSource, new [] { R.id }},
{ W.embedBold, new [] { R.id }},
{ W.embedBoldItalic, new [] { R.id }},
{ W.embedItalic, new [] { R.id }},
{ W.embedRegular, new [] { R.id }},
{ W.footerReference, new [] { R.id }},
{ W.headerReference, new [] { R.id }},
{ W.headerSource, new [] { R.id }},
{ W.hyperlink, new [] { R.id }},
{ W.printerSettings, new [] { R.id }},
{ W.recipientData, new [] { R.id }}, // Mail merge, not required
{ W.saveThroughXslt, new [] { R.id }},
{ W.sourceFileName, new [] { R.id }}, // Framesets, not required
{ W.src, new [] { R.id }}, // Mail merge, not required
{ W.subDoc, new [] { R.id }}, // Sub documents, not required
//{ w14:contentPart, new [] { R.id }},
{ WNE.toolbarData, new [] { R.id }},
};
// This list is used to eliminate duplicate images
List<ImageData> images = new List<ImageData>();
XDocument mainPart = output.MainDocumentPart.GetXDocument();
mainPart.Declaration.Standalone = "yes";
mainPart.Declaration.Encoding = "UTF-8";
mainPart.Root.ReplaceWith(
new XElement(W.document, NamespaceAttributes,
new XElement(W.body)));
if (sources.Count > 0)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(sources[0].WmlDocument))
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
CopyStartingParts(doc, output, images);
}
int sourceNum = 0;
foreach (Source source in sources)
{
if (source.InsertId != null)
{
while (true)
{
XDocument mainXDoc = output.MainDocumentPart.GetXDocument();
if (!mainXDoc.Descendants(PtOpenXml.Insert).Any(d => (string)d.Attribute(PtOpenXml.Id) == source.InsertId))
break;
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(source.WmlDocument))
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
#if TestForUnsupportedDocuments
// throws exceptions if a document contains unsupported content
TestForUnsupportedDocument(doc, sources.IndexOf(source));
#endif
List<XElement> contents = doc.MainDocumentPart.GetXDocument()
.Root
.Element(W.body)
.Elements()
.Skip(source.Start)
.Take(source.Count)
.ToList();
try
{
AppendDocument(doc, output, contents, source.KeepSections, source.InsertId, images);
}
catch (DocumentBuilderInternalException dbie)
{
if (dbie.Message.Contains("{0}"))
throw new DocumentBuilderException(string.Format(dbie.Message, sourceNum));
else
throw dbie;
}
}
}
}
else
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(source.WmlDocument))
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
#if TestForUnsupportedDocuments
// throws exceptions if a document contains unsupported content
TestForUnsupportedDocument(doc, sources.IndexOf(source));
#endif
List<XElement> contents = doc.MainDocumentPart.GetXDocument()
.Root
.Element(W.body)
.Elements()
.Skip(source.Start)
.Take(source.Count)
.ToList();
try
{
AppendDocument(doc, output, contents, source.KeepSections, null, images);
}
catch (DocumentBuilderInternalException dbie)
{
if (dbie.Message.Contains("{0}"))
throw new DocumentBuilderException(string.Format(dbie.Message, sourceNum));
else
throw dbie;
}
}
}
++sourceNum;
}
if (!sources.Any(s => s.KeepSections))
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(sources[0].WmlDocument))
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
var sectPr = doc.MainDocumentPart.GetXDocument().Root.Element(W.body)
.Elements().Last();
if (sectPr.Name == W.sectPr)
{
AddSectionAndDependencies(doc, output, sectPr, images);
output.MainDocumentPart.GetXDocument().Root.Element(W.body).Add(sectPr);
}
}
}
else
FixUpSectionProperties(output);
}
foreach (var part in output.GetAllParts())
if (part.Annotation<XDocument>() != null)
part.PutXDocument();
}
private static WmlDocument AdjustSectionBreak(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainXDoc = document.MainDocumentPart.GetXDocument();
XElement lastElement = mainXDoc.Root
.Element(W.body)
.Elements()
.LastOrDefault();
if (lastElement != null)
{
if (lastElement.Name != W.sectPr &&
lastElement.Descendants(W.sectPr).Any())
{
mainXDoc.Root.Element(W.body).Add(lastElement.Descendants(W.sectPr).First());
lastElement.Descendants(W.sectPr).Remove();
if (!lastElement.Elements()
.Where(e => e.Name != W.pPr)
.Any())
lastElement.Remove();
document.MainDocumentPart.PutXDocument();
}
}
}
return streamDoc.GetModifiedWmlDocument();
}
}
public static IEnumerable<WmlDocument> SplitOnSections(WmlDocument doc)
{
List<TempSource> tempSourceList;
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
XDocument mainDocument = document.MainDocumentPart.GetXDocument();
var divs = mainDocument
.Root
.Element(W.body)
.Elements()
.Select((p, i) => new
{
BlockLevelContent = p,
Index = i,
})
.Rollup(new
{
BlockLevelContent = (XElement)null,
Index = -1,
Div = 0,
},
(b, p) =>
{
XElement elementBefore = b.BlockLevelContent
.ElementsBeforeSelfReverseDocumentOrder()
.FirstOrDefault();
if (elementBefore != null && elementBefore.Descendants(W.sectPr).Any())
return new
{
BlockLevelContent = b.BlockLevelContent,
Index = b.Index,
Div = p.Div + 1,
};
return new
{
BlockLevelContent = b.BlockLevelContent,
Index = b.Index,
Div = p.Div,
};
});
var groups = divs
.GroupAdjacent(b => b.Div);
tempSourceList = groups
.Select(g => new TempSource
{
Start = g.First().Index,
Count = g.Count(),
})
.ToList();
foreach (var ts in tempSourceList)
{
List<Source> sources = new List<Source>()
{
new Source(doc, ts.Start, ts.Count, true)
};
WmlDocument newDoc = DocumentBuilder.BuildDocument(sources);
newDoc = AdjustSectionBreak(newDoc);
yield return newDoc;
}
}
}
/// <summary>
/// Gets the document theme
/// </summary>
public static OpenXmlPowerToolsDocument GetTheme(WmlDocument doc)
{
using (OpenXmlMemoryStreamDocument sourceStreamDoc = new OpenXmlMemoryStreamDocument(doc))
using (WordprocessingDocument document = sourceStreamDoc.GetWordprocessingDocument())
{
// Loads the theme part main file
ThemePart theme = document.MainDocumentPart.ThemePart;
if (theme != null)
{
XDocument themeDocument = theme.GetXDocument();
// Creates the theme package (thmx file)
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreatePackage())
{
using (Package themePackage = streamDoc.GetPackage())
{
// Creates the theme manager part on the new package and loads default content
PackagePart newThemeManagerPart = themePackage.CreatePart(new Uri("/theme/theme/themeManager.xml", UriKind.RelativeOrAbsolute), "application/vnd.openxmlformats-officedocument.themeManager+xml");
themePackage.CreateRelationship(newThemeManagerPart.Uri, TargetMode.Internal, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument");
using (XmlWriter xWriter = XmlWriter.Create(newThemeManagerPart.GetStream(FileMode.Create, FileAccess.Write)))
{
CreateEmptyThemeManager().WriteTo(xWriter);
xWriter.Flush();
}
// Creates the main theme part
PackagePart newThemePart = themePackage.CreatePart(new Uri("/theme/theme/" + theme.Uri.OriginalString.Substring(theme.Uri.OriginalString.LastIndexOf('/') + 1), UriKind.RelativeOrAbsolute), theme.ContentType);
newThemeManagerPart.CreateRelationship(newThemePart.Uri, TargetMode.Internal, theme.RelationshipType);
// Gets embeded part references
var embeddedItems =
themeDocument
.Descendants()
.Attributes(relationshipns + "embed");
foreach (IdPartPair partId in theme.Parts)
{
OpenXmlPart part = partId.OpenXmlPart;
// Creates the new media part inside the destination package
PackagePart newPart = themePackage.CreatePart(new Uri("/theme/media/" + part.Uri.OriginalString.Substring(part.Uri.OriginalString.LastIndexOf('/') + 1), UriKind.RelativeOrAbsolute), part.ContentType);
PackageRelationship relationship =
newThemePart.CreateRelationship(newPart.Uri, TargetMode.Internal, part.RelationshipType);
// Copies binary content from original part to destination part
Stream partStream = part.GetStream(FileMode.Open, FileAccess.Read);
Stream newPartStream = newPart.GetStream(FileMode.Create, FileAccess.Write);
byte[] fileContent = new byte[partStream.Length];
partStream.Read(fileContent, 0, (int)partStream.Length);
newPartStream.Write(fileContent, 0, (int)partStream.Length);
newPartStream.Flush();
// Replaces old embed part reference with the freshly created one
XAttribute relationshipAttribute = embeddedItems.FirstOrDefault(e => e.Value == theme.GetIdOfPart(part));
if (relationshipAttribute != null)
relationshipAttribute.Value = relationship.Id;
}
// Writes the updated theme XDocument into the destination package
using (XmlWriter newThemeWriter = XmlWriter.Create(newThemePart.GetStream(FileMode.Create, FileAccess.Write)))
themeDocument.WriteTo(newThemeWriter);
}
return streamDoc.GetModifiedDocument();
}
}
return null;
}
}
/// <summary>
/// Sets the document theme
/// </summary>
/// <param name="theme">Theme package</param>
public static OpenXmlPowerToolsDocument SetTheme(WmlDocument doc, OpenXmlPowerToolsDocument themeDoc)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
using (OpenXmlMemoryStreamDocument themeStream = new OpenXmlMemoryStreamDocument(themeDoc))
using (Package theme = themeStream.GetPackage())
{
// Gets the theme manager part
PackageRelationship themeManagerRelationship =
theme.GetRelationshipsByType(mainDocumentRelationshipType).FirstOrDefault();
if (themeManagerRelationship != null)
{
PackagePart themeManagerPart = theme.GetPart(themeManagerRelationship.TargetUri);
// Gets the theme main part
PackageRelationship themeRelationship =
themeManagerPart.GetRelationshipsByType(themeRelationshipType).FirstOrDefault();
if (themeRelationship != null)
{
PackagePart themePart = theme.GetPart(themeRelationship.TargetUri);
XDocument newThemeDocument = XDocument.Load(XmlReader.Create(themePart.GetStream(FileMode.Open, FileAccess.Read)));
// Removes existing theme part from document
if (document.MainDocumentPart.ThemePart != null)
document.MainDocumentPart.DeletePart(document.MainDocumentPart.ThemePart);
// Creates a new theme part
ThemePart documentThemePart = document.MainDocumentPart.AddNewPart<ThemePart>();
var embeddedItems =
newThemeDocument
.Descendants()
.Attributes(relationshipns + "embed");
foreach (PackageRelationship imageRelationship in themePart.GetRelationships())
{
// Adds an image part to the theme part and stores contents inside
PackagePart imagePart = theme.GetPart(imageRelationship.TargetUri);
ImagePart newImagePart =
documentThemePart.AddImagePart(GetImagePartType(imagePart.ContentType));
newImagePart.FeedData(imagePart.GetStream(FileMode.Open, FileAccess.Read));
// Updates relationship references into the theme XDocument
XAttribute relationshipAttribute = embeddedItems.FirstOrDefault(e => e.Value == imageRelationship.Id);
if (relationshipAttribute != null)
relationshipAttribute.Value = documentThemePart.GetIdOfPart(newImagePart);
}
documentThemePart.PutXDocument(newThemeDocument);
}
}
}
}
return streamDoc.GetModifiedDocument();
}
}
static void Main(string[] args)
{
// Update an existing pivot table
FileInfo qs = new FileInfo("../../QuarterlySales.xlsx");
FileInfo qsu = new FileInfo("../../QuarterlyPivot.xlsx");
int row = 1;
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName(qs.FullName)))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "Range");
using (StreamReader source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
string line = source.ReadLine();
if (line.Length > 3)
{
string[] fields = line.Split(',');
int column = 1;
foreach (string item in fields)
{
double num;
if (double.TryParse(item, out num))
WorksheetAccessor.SetCellValue(doc, sheet, row, column++, num);
else
WorksheetAccessor.SetCellValue(doc, sheet, row, column++, item);
}
}
row++;
}
}
sheet.PutXDocument();
WorksheetAccessor.UpdateRangeEndRow(doc, "Sales", row - 1);
}
streamDoc.GetModifiedSmlDocument().SaveAs(qsu.FullName);
}
// Create from scratch
row = 1;
int maxColumn = 1;
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.CreateDefaultStyles(doc);
WorksheetPart sheet = WorksheetAccessor.AddWorksheet(doc, "Range");
MemorySpreadsheet ms = new MemorySpreadsheet();
#if false
int font0 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font2 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 18,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Cambria",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Major
});
int font3 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 15,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font4 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 13,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font5 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font6 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF006100"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font7 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF9C0006"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font8 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF9C6500"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font9 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF3F3F76"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font10 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF3F3F3F"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font11 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FFFA7D00"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font12 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FFFA7D00"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font13 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 0),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font14 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FFFF0000"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font15 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Italic = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF7F7F7F"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font16 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font17 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 0),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int fill0 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.None, null, null));
int fill1 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Gray125, null, null));
int fill2 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFC6EFCE")));
int fill3 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFC7CE")));
int fill4 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFEB9C")));
int fill5 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFCC99")));
int fill6 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFF2F2F2")));
int fill7 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFA5A5A5")));
int fill8 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFFFCC")));
int fill9 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)));
int fill10 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(4, 0.79998168889431442)));
int fill11 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(4, 0.59999389629810485)));
int fill12 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(4, 0.39997558519241921)));
int fill13 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 5)));
int fill14 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(5, 0.79998168889431442)));
int fill15 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(5, 0.59999389629810485)));
int fill16 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(5, 0.39997558519241921)));
int fill17 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 6)));
int fill18 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(6, 0.79998168889431442)));
int fill19 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(6, 0.59999389629810485)));
int fill20 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(6, 0.39997558519241921)));
int fill21 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 7)));
int fill22 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(7, 0.79998168889431442)));
int fill23 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(7, 0.59999389629810485)));
int fill24 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(7, 0.39997558519241921)));
int fill25 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 8)));
int fill26 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(8, 0.79998168889431442)));
int fill27 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(8, 0.59999389629810485)));
int fill28 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(8, 0.39997558519241921)));
int fill29 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 9)));
int fill30 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(9, 0.79998168889431442)));
int fill31 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(9, 0.59999389629810485)));
int fill32 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(9, 0.39997558519241921)));
int border1 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thick,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4))
});
int border2 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thick, new WorksheetAccessor.ColorInfo(4, 0.499984740745262))
});
int border3 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Medium, new WorksheetAccessor.ColorInfo(4, 0.39997558519241921))
});
int border4 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F"))
});
int border5 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F"))
});
int border6 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FFFF8001"))
});
int border7 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F"))
});
int border8 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2"))
});
int border9 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4))
});
#endif
int southIndex = WorksheetAccessor.GetStyleIndex(doc, 0, 8, 1, 2,
new WorksheetAccessor.CellAlignment { HorizontalAlignment = WorksheetAccessor.CellAlignment.Horizontal.Center },
true, false);
WorksheetAccessor.GradientFill gradient = new WorksheetAccessor.GradientFill(90);
gradient.AddStop(new WorksheetAccessor.GradientStop(0, new WorksheetAccessor.ColorInfo("FF92D050")));
gradient.AddStop(new WorksheetAccessor.GradientStop(1, new WorksheetAccessor.ColorInfo("FF0070C0")));
int northIndex = WorksheetAccessor.GetStyleIndex(doc, 0,
WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Italic = true,
Size = 8,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Times New Roman",
Family = 1
}),
WorksheetAccessor.GetFillIndex(doc, gradient),
WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
DiagonalDown = true,
Diagonal =
new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF616100"))
}),
null, false, false);
WorksheetAccessor.CheckNumberFormat(doc, 100, "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)");
int amountIndex = WorksheetAccessor.GetStyleIndex(doc, 100, 0, 0, 0, null, false, false);
using (StreamReader source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
string line = source.ReadLine();
if (line.Length > 3)
{
string[] fields = line.Split(',');
int column = 1;
foreach (string item in fields)
{
double num;
if (double.TryParse(item, out num))
{
if (column == 6)
ms.SetCellValue(row, column++, num, amountIndex);
else
ms.SetCellValue(row, column++, num);
}
else if (item == "Accessories")
ms.SetCellValue(row, column++, item, WorksheetAccessor.GetStyleIndex(doc, "Good"));
else if (item == "South")
ms.SetCellValue(row, column++, item, southIndex);
else if (item == "North")
ms.SetCellValue(row, column++, item, northIndex);
else
ms.SetCellValue(row, column++, item);
}
maxColumn = column - 1;
}
row++;
}
}
WorksheetAccessor.SetSheetContents(doc, sheet, ms);
WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Amount");
WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../NewPivot.xlsx");
}
// Add pivot table to existing spreadsheet
// Demonstrate multiple data fields
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../QuarterlyUnitSales.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Total");
WorksheetAccessor.AddDataValue(doc, pivot, "Quantity");
WorksheetAccessor.AddDataValue(doc, pivot, "Unit Price");
WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../QuarterlyUnitSalesWithPivot.xlsx");
}
}
public static bool HasTrackedRevisions(WmlDocument document)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
{
using (WordprocessingDocument wdoc = streamDoc.GetWordprocessingDocument())
{
return RevisionAccepter.HasTrackedRevisions(wdoc);
}
}
}
