public byte[] Run(List <PersonFiles> files)
{
var mergeXml = GetMergeXml(files);
MergeDocument documentXml = mergeXml.Items.First();
IPresentationTools presentationTools = new PresentationTools();
byte[] emptyTemplate = files.Where(p => p.Person == "/" && p.Name == "template.pptx").Select(d => d.Data).FirstOrDefault();
using (MemoryStream emptyDocInMemoryStream = new MemoryStream(emptyTemplate, 0, emptyTemplate.Length, true, true))
{
OpenXmlPowerToolsDocument emptyDocPowerTools = new OpenXmlPowerToolsDocument(string.Empty, emptyDocInMemoryStream);
using (OpenXmlMemoryStreamDocument streamEmptyDoc = new OpenXmlMemoryStreamDocument(emptyDocPowerTools))
{
PresentationDocument emptyPresentation = streamEmptyDoc.GetPresentationDocument();
foreach (MergeDocumentPart part in documentXml.Part)
{
byte[] byteArray = files.Where(p => p.Person == part.Name && p.Name == part.Id).Select(d => d.Data).FirstOrDefault();
using (MemoryStream partDocInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true))
{
OpenXmlPowerToolsDocument partDocPowerTools = new OpenXmlPowerToolsDocument(string.Empty, partDocInMemoryStream);
using (OpenXmlMemoryStreamDocument streamDividedDoc = new OpenXmlMemoryStreamDocument(partDocPowerTools))
{
PresentationDocument templatePresentation = streamDividedDoc.GetPresentationDocument();
presentationTools.InsertSlidesFromTemplate(emptyPresentation, templatePresentation);
}
}
}
return(streamEmptyDoc.GetModifiedDocument().DocumentByteArray);
}
}
}
// Determines if and where to write the modified document
internal void OutputDocument(OpenXmlPowerToolsDocument doc)
{
if (PassThru)
WriteObject(doc, true);
else
doc.Save();
}
private static void Main()
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
// Change sheet name in formulas
using (var streamDoc = new OpenXmlMemoryStreamDocument(
OpenXmlPowerToolsDocument.FromFileName("../../Formulas.xlsx")))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.FormulaReplaceSheetName(doc, "Source", "'Source 2'");
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "FormulasUpdated.xlsx"));
}
// Change sheet name in formulas
using (var streamDoc = new OpenXmlMemoryStreamDocument(
OpenXmlPowerToolsDocument.FromFileName("../../Formulas.xlsx")))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
var sheet = WorksheetAccessor.GetWorksheet(doc, "References");
WorksheetAccessor.CopyCellRange(doc, sheet, 1, 1, 7, 5, 4, 8);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "FormulasCopied.xlsx"));
}
}
protected override void EndProcessing()
{
if (!File.Exists(outputPath) || ShouldProcess(outputPath, "Export-OpenXmlSpreadsheet"))
{
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
if (processedObjects.Count > 0)
{
List <string> headerList = new List <string>();
foreach (PSPropertyInfo propertyInfo in processedObjects[0].Properties)
{
headerList.Add(propertyInfo.Name.ToUpper());
}
// Stores into a matrix all properties of objects passed as parameter
int rowLength = headerList.Count;
int rowCount = processedObjects.Count;
string[][] valueMatrix = new string[rowCount][];
int currentRow = 0, currentColumn = 0;
foreach (PSObject obj in processedObjects)
{
currentColumn = 0;
valueMatrix[currentRow] = new string[rowLength];
foreach (PSPropertyInfo propertyInfo in obj.Properties)
{
try
{
if (propertyInfo.Value != null)
{
valueMatrix[currentRow][currentColumn] = propertyInfo.Value.ToString();
}
}
// Suppress errors on properties that cannot be read, but write the information to debug output.
catch (GetValueInvocationException e)
{
WriteDebug(string.Format(CultureInfo.InvariantCulture, "Exception ({0}) at Object {1}, property {2}", e.Message, currentRow, currentColumn));
}
currentColumn++;
}
currentRow++;
}
if (displayChart)
{
SpreadsheetDocumentManager.Create(document, headerList, valueMatrix, chartType, headerColumn, columnsToChart, initialRow);
}
else
{
SpreadsheetDocumentManager.Create(document, headerList, valueMatrix, initialRow);
}
}
}
OpenXmlPowerToolsDocument output = streamDoc.GetModifiedDocument();
output.FileName = outputPath;
OutputDocument(output);
}
}
}
public byte[] Run(List <PersonFiles> files)
{
var mergeXml = GetMergeXml(files);
byte[] template = files.Where(p => p.Person == "/" && p.Name == "template.xlsx").Select(d => d.Data).FirstOrDefault();
using (MemoryStream docInMemoryStream = new MemoryStream(template, 0, template.Length, true, true))
{
OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(string.Empty, docInMemoryStream);
using (OpenXmlMemoryStreamDocument streamEmptyDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
SpreadsheetDocument excelTemplateDoc = streamEmptyDoc.GetSpreadsheetDocument();
excelTemplateDoc.WorkbookPart.Workbook.Sheets = new Sheets();
MergeDocument documentXml = mergeXml.Items.First();
foreach (MergeDocumentPart part in documentXml.Part)
{
byte[] byteArray = files.Where(p => p.Person == part.Name && p.Name == part.Id).Select(d => d.Data).FirstOrDefault();
using (MemoryStream docPartInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true))
{
OpenXmlPowerToolsDocument docPartPowerTools = new OpenXmlPowerToolsDocument(string.Empty, docPartInMemoryStream);
using (OpenXmlMemoryStreamDocument streamPartDoc = new OpenXmlMemoryStreamDocument(docPartPowerTools))
{
SpreadsheetDocument spreadSheetDocument = streamPartDoc.GetSpreadsheetDocument();
ExcelTools.MergeWorkSheets(excelTemplateDoc, spreadSheetDocument);
// Close the handle explicitly.
spreadSheetDocument.Close();
}
}
}
return(streamEmptyDoc.GetModifiedDocument().DocumentByteArray);
}
}
}
protected override void ProcessRecord()
{
foreach (var document in AllDocuments("Get-OpenXmlTheme"))
{
try
{
if (!(document is WmlDocument))
{
throw new PowerToolsDocumentException("Not a wordprocessing document.");
}
OpenXmlPowerToolsDocument theme = ThemeAccessor.GetTheme((WmlDocument)document);
if (OutputPath != null)
{
theme.SaveAs(System.IO.Path.Combine(SessionState.Path.CurrentLocation.Path, OutputPath));
}
if (PassThru || OutputPath == null)
{
WriteObject(theme, true);
}
}
catch (Exception e)
{
WriteError(PowerToolsExceptionHandling.GetExceptionErrorRecord(e, document));
}
}
}
/// <summary>
/// Entry point for PowerShell cmdlets
/// </summary>
protected override void ProcessRecord()
{
if (themePath != null)
{
themePackage = OpenXmlPowerToolsDocument.FromFileName(themePath);
}
if (themePackage == null)
{
WriteError(new ErrorRecord(new ArgumentException("No theme was specified."), "OpenXmlPowerTools", ErrorCategory.InvalidArgument, null));
}
else
{
foreach (var document in AllDocuments("Set-OpenXmlTheme"))
{
try
{
if (!(document is WmlDocument))
{
throw new PowerToolsDocumentException("Not a wordprocessing document.");
}
OutputDocument(ThemeAccessor.SetTheme((WmlDocument)document, themePackage));
}
catch (Exception e)
{
WriteError(PowerToolsExceptionHandling.GetExceptionErrorRecord(e, document));
}
}
}
}
/// <summary>
/// Entry point for PowerShell cmdlets
/// </summary>
protected override void ProcessRecord()
{
if (themePath != null)
themePackage = OpenXmlPowerToolsDocument.FromFileName(themePath);
if (themePackage == null)
{
WriteError(new ErrorRecord(new ArgumentException("No theme was specified."), "OpenXmlPowerTools", ErrorCategory.InvalidArgument, null));
}
else
{
foreach (var document in AllDocuments("Set-OpenXmlTheme"))
{
try
{
if (!(document is WmlDocument))
throw new PowerToolsDocumentException("Not a wordprocessing document.");
OutputDocument(ThemeAccessor.SetTheme((WmlDocument)document, themePackage));
}
catch (Exception e)
{
WriteError(PowerToolsExceptionHandling.GetExceptionErrorRecord(e, document));
}
}
}
}
protected override void EndProcessing()
{
try
{
if (m_Sources != null)
{
foreach (DocumentSource source in m_Sources)
{
Collection <PathInfo> fileList = SessionState.Path.GetResolvedPSPathFromPSPath(source.SourceFile);
foreach (var file in fileList)
{
OpenXmlPowerToolsDocument document = OpenXmlPowerToolsDocument.FromFileName(file.Path);
try
{
if (!(document is WmlDocument))
{
throw new PowerToolsDocumentException("Not a wordprocessing document.");
}
if (source.Count != -1)
{
buildSources.Add(new Source((WmlDocument)document, source.Start - 1, source.Count, source.KeepSection));
}
else if (source.Start != -1)
{
buildSources.Add(new Source((WmlDocument)document, source.Start - 1, source.KeepSection));
}
else
{
buildSources.Add(new Source((WmlDocument)document, source.KeepSection));
}
}
catch (Exception e)
{
WriteError(PowerToolsExceptionHandling.GetExceptionErrorRecord(e, document));
}
}
}
}
WmlDocument result = DocumentBuilder.BuildDocument(buildSources);
if (m_OutputPath != null)
{
if (!File.Exists(m_OutputPath) || ShouldProcess(m_OutputPath, "Merge-OpenXmlDocumentCmdlet"))
{
result.SaveAs(m_OutputPath);
}
}
if (PassThru)
{
WriteObject(result, true);
}
}
catch (Exception e)
{
WriteError(PowerToolsExceptionHandling.GetExceptionErrorRecord(e, null));
}
}
public List <PersonFiles> SaveSplitDocument(Stream document)
{
List <PersonFiles> resultList = new List <PersonFiles>();
byte[] byteArray = StreamTools.ReadFully(document);
using (MemoryStream documentInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true))
{
foreach (OpenXMLDocumentPart <Sheet> element in DocumentElements)
{
OpenXmlPowerToolsDocument docDividedPowerTools = new OpenXmlPowerToolsDocument(DocumentName, documentInMemoryStream);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(docDividedPowerTools))
{
SpreadsheetDocument excelDoc = streamDoc.GetSpreadsheetDocument();
excelDoc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
Sheets sheets = excelDoc.WorkbookPart.Workbook.Sheets;
foreach (Sheet compo in element.CompositeElements)
{
sheets.Append(compo.CloneNode(false));
}
excelDoc.WorkbookPart.Workbook.Save();
var person = new PersonFiles();
person.Person = element.PartOwner;
resultList.Add(person);
person.Name = element.Guid.ToString();
person.Data = streamDoc.GetModifiedDocument().DocumentByteArray;
}
}
OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(DocumentName, documentInMemoryStream);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
SpreadsheetDocument excelDoc = streamDoc.GetSpreadsheetDocument();
excelDoc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
excelDoc.WorkbookPart.Workbook.Save();
var person = new PersonFiles();
person.Person = "/";
resultList.Add(person);
person.Name = "template.xlsx";
person.Data = streamDoc.GetModifiedDocument().DocumentByteArray;
}
}
var xmlPerson = new PersonFiles();
xmlPerson.Person = "/";
resultList.Add(xmlPerson);
xmlPerson.Name = "mergeXmlDefinition.xml";
xmlPerson.Data = CreateMergeXml();
return(resultList);
}
// Determines if and where to write the modified document
internal void OutputDocument(OpenXmlPowerToolsDocument doc)
{
if (PassThru)
{
WriteObject(doc, true);
}
else
{
doc.Save();
}
}
public ValidationInfo(OpenXmlPowerToolsDocument doc, ValidationErrorInfo err)
{
Document = doc;
FileName = doc.FileName;
Description = err.Description;
ErrorType = err.ErrorType;
Id = err.Id;
Node = err.Node;
Part = err.Part;
XPath = err.Path.XPath;
RelatedNode = err.RelatedNode;
RelatedPart = err.RelatedPart;
}
public static ErrorRecord GetExceptionErrorRecord(Exception e, OpenXmlPowerToolsDocument doc)
{
ErrorCategory cat = ErrorCategory.NotSpecified;
if (e is ArgumentException)
cat = ErrorCategory.InvalidArgument;
else if (e is InvalidOperationException)
cat = ErrorCategory.InvalidOperation;
else if (e is PowerToolsDocumentException)
cat = ErrorCategory.OpenError;
else if (e is PowerToolsInvalidDataException || e is XmlException)
cat = ErrorCategory.InvalidData;
return new ErrorRecord(e, (cat == ErrorCategory.NotSpecified) ? "General" : "OpenXmlPowerToolsError", cat, doc);
}
public ValidationInfo(OpenXmlPowerToolsDocument doc, ValidationErrorInfo err)
{
Document = doc;
FileName = doc.FileName;
Description = err.Description;
ErrorType = err.ErrorType;
Id = err.Id;
Node = err.Node;
Part = err.Part;
XPath = err.Path.XPath;
RelatedNode = err.RelatedNode;
RelatedPart = err.RelatedPart;
}
public List <PersonFiles> SaveSplitDocument(Stream document)
{
List <PersonFiles> resultList = new List <PersonFiles>();
byte[] byteArray = StreamTools.ReadFully(document);
using (MemoryStream documentInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true))
{
OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(DocumentName, documentInMemoryStream);
using (OpenXmlMemoryStreamDocument streamTemplateDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
PresentationDocument templatePresentation = streamTemplateDoc.GetPresentationDocument();
foreach (OpenXMLDocumentPart <SlideId> element in DocumentElements)
{
OpenXmlPowerToolsDocument emptyDocPowerTools = new OpenXmlPowerToolsDocument(DocumentName, documentInMemoryStream);
using (OpenXmlMemoryStreamDocument streamDividedDoc = new OpenXmlMemoryStreamDocument(emptyDocPowerTools))
{
var relationshipIds = element.CompositeElements.Select(c => c.RelationshipId.Value).ToList();
PresentationDocument dividedPresentation = streamDividedDoc.GetPresentationDocument();
PresentationTools.InsertSlidesFromTemplate(PresentationTools.RemoveAllSlides(dividedPresentation), templatePresentation, relationshipIds);
var person = new PersonFiles();
person.Person = element.PartOwner;
resultList.Add(person);
person.Name = element.Guid.ToString();
person.Data = streamDividedDoc.GetModifiedDocument().DocumentByteArray;
}
}
}
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
PresentationDocument preDoc = streamDoc.GetPresentationDocument();
PresentationTools.RemoveAllSlides(preDoc);
var person = new PersonFiles();
person.Person = "/";
resultList.Add(person);
person.Name = "template.pptx";
person.Data = streamDoc.GetModifiedDocument().DocumentByteArray;
}
var xmlPerson = new PersonFiles();
xmlPerson.Person = "/";
resultList.Add(xmlPerson);
xmlPerson.Name = "mergeXmlDefinition.xml";
xmlPerson.Data = CreateMergeXml();
}
return(resultList);
}
internal IEnumerable <OpenXmlPowerToolsDocument> AllDocuments(string action)
{
if (fileNameReferences != null)
{
foreach (var path in fileNameReferences)
{
Collection <PathInfo> fileList;
try
{
fileList = SessionState.Path.GetResolvedPSPathFromPSPath(path);
}
catch (ItemNotFoundException e)
{
WriteError(new ErrorRecord(e, "OpenXmlPowerToolsError", ErrorCategory.OpenError, path));
continue;
}
foreach (var file in fileList)
{
OpenXmlPowerToolsDocument document;
try
{
document = OpenXmlPowerToolsDocument.FromFileName(file.Path);
}
catch (Exception e)
{
WriteError(new ErrorRecord(e, "OpenXmlPowerToolsError", ErrorCategory.OpenError, file));
continue;
}
yield return(document);
}
}
}
else if (Document != null)
{
foreach (OpenXmlPowerToolsDocument document in Document)
{
OpenXmlPowerToolsDocument specificDoc;
try
{
specificDoc = OpenXmlPowerToolsDocument.FromDocument(document);
}
catch (Exception e)
{
WriteError(new ErrorRecord(e, "OpenXmlPowerToolsError", ErrorCategory.InvalidType, document));
continue;
}
yield return(specificDoc);
}
}
}
// Determines if and where to write the modified document
internal void OutputDocument(OpenXmlPowerToolsDocument doc)
{
if (OutputFolder != null)
{
FileInfo file = new FileInfo(doc.FileName);
string newName = OutputFolder + "\\" + file.Name;
doc.SaveAs(newName);
}
else if (!PassThru)
{
doc.Save();
}
if (PassThru)
{
WriteObject(doc, true);
}
}
protected override void EndProcessing()
{
if (PassThru || !File.Exists(outputPath) || ShouldProcess(outputPath, "Export-OpenXmlWordprocessing"))
{
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateWordprocessingDocument())
{
using (WordprocessingDocument document = streamDoc.GetWordprocessingDocument())
{
if (processedObjects.Count > 0)
{
PowerToolsExtensions.SetContent(document, processedObjects.Select(e => new XElement(W.p, new XElement(W.r, new XElement(W.t, new XAttribute(XNamespace.Xml + "space", "preserve"), e)))));
}
}
OpenXmlPowerToolsDocument output = streamDoc.GetModifiedDocument();
output.FileName = outputPath;
OutputDocument(output);
}
}
}
public byte[] Run(List <PersonFiles> files)
{
var mergeXml = GetMergeXml(files);
Body body = new Body();
MergeDocument documentXml = mergeXml.Items.First();
foreach (MergeDocumentPart part in documentXml.Part)
{
byte[] byteArray = files.Where(p => p.Person == part.Name && p.Name == part.Id).Select(d => d.Data).FirstOrDefault();
using (MemoryStream docInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true))
{
OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(string.Empty, docInMemoryStream);
using (OpenXmlMemoryStreamDocument streamEmptyDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
WordprocessingDocument wordprocessingDocument = streamEmptyDoc.GetWordprocessingDocument();
// Assign a reference to the existing document body.
foreach (OpenXmlElement element in wordprocessingDocument.MainDocumentPart.Document.Body.ChildElements)
{
body.Append(element.CloneNode(true));
}
// Close the handle explicitly.
wordprocessingDocument.Close();
}
}
}
byte[] template = files.Where(p => p.Person == "/" && p.Name == "template.docx").Select(d => d.Data).FirstOrDefault();
using (MemoryStream docInMemoryStream = new MemoryStream(template, 0, template.Length, true, true))
{
OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(string.Empty, docInMemoryStream);
using (OpenXmlMemoryStreamDocument streamEmptyDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
WordprocessingDocument wordDoc = streamEmptyDoc.GetWordprocessingDocument();
wordDoc.MainDocumentPart.Document.Body = body;
wordDoc.MainDocumentPart.Document.Save();
return(streamEmptyDoc.GetModifiedDocument().DocumentByteArray);
}
}
}
public void SaveSplitDocumentShouldReturnValidTest2dDocuments()
{
var personFilesList = DocSampleSplit.SaveSplitDocument(PreSampleDocInMemory);
var docs = personFilesList.Where(p => p.Person == "test2").Select(u => u.Data);
List <ValidationErrorInfo> validationErrors = new List <ValidationErrorInfo>();
foreach (byte[] doc in docs)
{
MemoryStream partDocInMemory = new MemoryStream(doc, 0, doc.Length, true, true);
var partDocPowerTools = new OpenXmlPowerToolsDocument("test2.pptx", partDocInMemory);
OpenXmlMemoryStreamDocument partDocInMemoryExpandable = new OpenXmlMemoryStreamDocument(partDocPowerTools);
validationErrors.AddRange(DocValidator.Validate(partDocInMemoryExpandable.GetPresentationDocument()));
}
Assert.IsTrue(docs.Count() > 0);
Assert.AreEqual(0, validationErrors.Count());
}
public static ErrorRecord GetExceptionErrorRecord(Exception e, OpenXmlPowerToolsDocument doc)
{
ErrorCategory cat = ErrorCategory.NotSpecified;
if (e is ArgumentException)
{
cat = ErrorCategory.InvalidArgument;
}
else if (e is InvalidOperationException)
{
cat = ErrorCategory.InvalidOperation;
}
else if (e is PowerToolsDocumentException)
{
cat = ErrorCategory.OpenError;
}
else if (e is PowerToolsInvalidDataException || e is XmlException)
{
cat = ErrorCategory.InvalidData;
}
return(new ErrorRecord(e, (cat == ErrorCategory.NotSpecified) ? "General" : "OpenXmlPowerToolsError", cat, doc));
}
public void Init()
{
PreTools = new PresentationTools();
DocValidator = new OpenXmlValidator();
byte[] PreCGWBytes = File.ReadAllBytes(@"../../../Files/6.CGW15-prezentacja.pptx");
byte[] PreSampleBytes = File.ReadAllBytes(@"../../../Files/przykladowa-prezentacja.pptx");
MemoryStream PreCGWDocInMemory = new MemoryStream(PreCGWBytes, 0, PreCGWBytes.Length, true, true);
MemoryStream PreSampleDocInMemory = new MemoryStream(PreSampleBytes, 0, PreSampleBytes.Length, true, true);
OpenXmlPowerToolsDocument PreCGWDocPowerTools = new OpenXmlPowerToolsDocument("6.CGW15 - prezentacja.pptx", PreCGWDocInMemory);
OpenXmlPowerToolsDocument PreSampleDocPowerTools = new OpenXmlPowerToolsDocument("6.CGW15 - przykladowa-prezentacja.pptx", PreSampleDocInMemory);
PreCGWDocInMemoryExpandable = new OpenXmlMemoryStreamDocument(PreCGWDocPowerTools);
PreSampleDocInMemoryExpandable = new OpenXmlMemoryStreamDocument(PreSampleDocPowerTools);
PreCGWDoc = PreCGWDocInMemoryExpandable.GetPresentationDocument();
PreSampleDoc = PreSampleDocInMemoryExpandable.GetPresentationDocument();
PreCGWDocInMemory.Close();
PreSampleDocInMemory.Close();
}
internal IEnumerable <OpenXmlPowerToolsDocument> AllDocuments(string action)
{
if (fileNameReferences != null)
{
foreach (var path in fileNameReferences)
{
Collection <PathInfo> fileList;
try
{
fileList = SessionState.Path.GetResolvedPSPathFromPSPath(path);
}
catch (ItemNotFoundException e)
{
WriteError(new ErrorRecord(e, "OpenXmlPowerToolsError", ErrorCategory.OpenError, path));
continue;
}
foreach (var file in fileList)
{
string target = file.Path;
if (OutputFolder != null)
{
FileInfo temp = new FileInfo(file.Path);
target = OutputFolder + "\\" + temp.Name;
}
if (!File.Exists(target) || ShouldProcess(target, action))
{
OpenXmlPowerToolsDocument document;
try
{
document = OpenXmlPowerToolsDocument.FromFileName(file.Path);
}
catch (Exception e)
{
WriteError(new ErrorRecord(e, "OpenXmlPowerToolsError", ErrorCategory.OpenError, file));
continue;
}
yield return(document);
}
}
}
}
else if (Document != null)
{
foreach (OpenXmlPowerToolsDocument document in Document)
{
string target = document.FileName;
if (OutputFolder != null)
{
FileInfo temp = new FileInfo(document.FileName);
target = OutputFolder + "\\" + temp.Name;
}
if (!File.Exists(target) || ShouldProcess(target, action))
{
OpenXmlPowerToolsDocument specificDoc;
try
{
specificDoc = OpenXmlPowerToolsDocument.FromDocument(document);
}
catch (Exception e)
{
WriteError(new ErrorRecord(e, "OpenXmlPowerToolsError", ErrorCategory.InvalidType, document));
continue;
}
yield return(specificDoc);
}
}
}
}
private static void Main()
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
// Update an existing pivot table
var qs = new FileInfo("../../QuarterlySales.xlsx");
var qsu = new FileInfo(Path.Combine(tempDi.FullName, "QuarterlyPivot.xlsx"));
var row = 1;
using (var streamDoc = new OpenXmlMemoryStreamDocument(
OpenXmlPowerToolsDocument.FromFileName(qs.FullName)))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
var sheet = WorksheetAccessor.GetWorksheet(doc, "Range");
using (var source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
var line = source.ReadLine();
if (line.Length > 3)
{
var fields = line.Split(',');
var column = 1;
foreach (var item in fields)
{
if (double.TryParse(item, out var num))
{
WorksheetAccessor.SetCellValue(sheet, row, column++, num);
}
else
{
WorksheetAccessor.SetCellValue(sheet, row, column++, item);
}
}
}
row++;
}
}
sheet.PutXDocument();
WorksheetAccessor.UpdateRangeEndRow(doc, "Sales", row - 1);
}
streamDoc.GetModifiedSmlDocument().SaveAs(qsu.FullName);
}
// Create from scratch
row = 1;
var maxColumn = 1;
using (var streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.CreateDefaultStyles(doc);
var sheet = WorksheetAccessor.AddWorksheet(doc, "Range");
var ms = new MemorySpreadsheet();
var southIndex = WorksheetAccessor.GetStyleIndex(doc, 0, 8, 1, 2,
new WorksheetAccessor.CellAlignment {
HorizontalAlignment = WorksheetAccessor.CellAlignment.Horizontal.Center
},
true, false);
var 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")));
var 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\\);_(\"$\"* \"-\"??_);_(@_)");
var amountIndex = WorksheetAccessor.GetStyleIndex(doc, 100, 0, 0, 0, null, false, false);
using (var source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
var line = source.ReadLine();
if (line.Length > 3)
{
var fields = line.Split(',');
var column = 1;
foreach (var item in fields)
{
if (double.TryParse(item, out var 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(sheet, ms);
WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
var pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Amount");
WorksheetAccessor.AddPivotAxis(pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "NewPivot.xlsx"));
}
// Add pivot table to existing spreadsheet
// Demonstrate multiple data fields
using (var streamDoc = new OpenXmlMemoryStreamDocument(
OpenXmlPowerToolsDocument.FromFileName("../../QuarterlyUnitSales.xlsx")))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
var pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Total");
WorksheetAccessor.AddDataValue(doc, pivot, "Quantity");
WorksheetAccessor.AddDataValue(doc, pivot, "Unit Price");
WorksheetAccessor.AddPivotAxis(pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "QuarterlyUnitSalesWithPivot.xlsx"));
}
}
// Determines if and where to write the modified document
internal void OutputDocument(OpenXmlPowerToolsDocument doc)
{
if (OutputFolder != null)
{
FileInfo file = new FileInfo(doc.FileName);
string newName = OutputFolder + "\\" + file.Name;
doc.SaveAs(newName);
}
else if (!PassThru)
doc.Save();
if (PassThru)
WriteObject(doc, true);
}
public List <PersonFiles> SaveSplitDocument(Stream document)
{
List <PersonFiles> resultList = new List <PersonFiles>();
byte[] byteArray = StreamTools.ReadFully(document);
using (MemoryStream documentInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true))
{
foreach (OpenXMLDocumentPart <OpenXmlElement> element in DocumentElements)
{
OpenXmlPowerToolsDocument docDividedPowerTools = new OpenXmlPowerToolsDocument(DocumentName, documentInMemoryStream);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(docDividedPowerTools))
{
WordprocessingDocument wordDoc = streamDoc.GetWordprocessingDocument();
wordDoc.MainDocumentPart.Document.Body.RemoveAllChildren();
Body body = wordDoc.MainDocumentPart.Document.Body;
foreach (OpenXmlElement compo in element.CompositeElements)
{
body.Append(compo.CloneNode(true));
}
wordDoc.MainDocumentPart.Document.Save();
var person = new PersonFiles();
person.Person = element.PartOwner;
resultList.Add(person);
person.Name = element.Guid.ToString();
person.Data = streamDoc.GetModifiedDocument().DocumentByteArray;
}
}
// At this point, the memory stream contains the modified document.
// We could write it back to a SharePoint document library or serve
// it from a web server.
OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(DocumentName, documentInMemoryStream);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(docPowerTools))
{
WordprocessingDocument wordDoc = streamDoc.GetWordprocessingDocument();
wordDoc.MainDocumentPart.Document.Body.RemoveAllChildren();
wordDoc.MainDocumentPart.Document.Save();
var person = new PersonFiles();
person.Person = "/";
resultList.Add(person);
person.Name = "template.docx";
person.Data = streamDoc.GetModifiedDocument().DocumentByteArray;
}
}
// At this point, the memory stream contains the modified document.
// We could write it back to a SharePoint document library or serve
// it from a web server.
var xmlPerson = new PersonFiles();
xmlPerson.Person = "/";
resultList.Add(xmlPerson);
xmlPerson.Name = "mergeXmlDefinition.xml";
xmlPerson.Data = CreateMergeXml();
return(resultList);
}
