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