internal ExcelTabularPage( SpreadsheetDocument spreadsheetDocument, Workbook workbook, WorksheetPart worksheetPart, Sheet sheet, Stylesheet stylesheet, uint index, ExcelTabularBook parent) { this.spreadsheetDocument = spreadsheetDocument; this.worksheetPart = worksheetPart; chartsheetPart = null; this.sheet = sheet; this.parent = parent; sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart?.SharedStringTable; PageSetup pageSetup = worksheetPart.Worksheet.GetFirstChild <PageSetup>(); if (pageSetup != null) { pageSetup.VerticalDpi = null; } }
/// <summary> /// Ctor /// </summary> /// <param name="xlsContext">The excel context object</param> public WindowMapping(ExcelContext xlsContext, ChartsheetPart chartsheetPart, int window1Id, bool isChartsheetSubstream) : base(chartsheetPart.XmlWriter) { this._xlsContext = xlsContext; this._chartsheetPart = chartsheetPart; this._window1Id = window1Id; this._isChartsheetSubstream = isChartsheetSubstream; }
/// <summary> /// Creates a spreadsheet document with a chart from a value table /// </summary> /// <param name="filePath">Path to store the document</param> /// <param name="headerList">Contents of first row (header)</param> /// <param name="valueTable">Contents of data</param> /// <param name="chartType">Chart type</param> /// <param name="categoryColumn">Column to use as category for charting</param> /// <param name="columnsToChart">Columns to use as data series</param> /// <param name="initialRow">Row index to start copying data</param> /// <returns>SpreadsheetDocument</returns> public static void Create(SpreadsheetDocument document, List <string> headerList, string[][] valueTable, ChartType chartType, string categoryColumn, List <string> columnsToChart, int initialRow) { headerRow = initialRow; //Creates worksheet with data WorksheetPart worksheet = WorksheetAccessor.Create(document, headerList, valueTable, headerRow); //Creates chartsheet with given series and category string sheetName = GetSheetName(worksheet, document); ChartsheetPart chartsheet = ChartsheetAccessor.Create(document, chartType, GetValueReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable), GetHeaderReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable), GetCategoryReference(sheetName, categoryColumn, headerList, valueTable) ); }
internal ExcelTabularPage(SpreadsheetDocument package, Workbook workbook, WorksheetPart worksheetPart, Sheet sheet, Stylesheet stylesheet, uint index, ExcelTabularBook parent) { _package = package; _worksheetPart = worksheetPart; _chartsheetPart = null; _sheet = sheet; _parent = parent; _sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); _sharedStringTable = package.WorkbookPart.SharedStringTablePart?.SharedStringTable; PageSetup pageSetup = worksheetPart.Worksheet.GetFirstChild <PageSetup>(); if (pageSetup != null) { pageSetup.VerticalDpi = null; } }
/// <summary> /// Ctor /// </summary> /// <param name="xlsContext">The excel context object</param> public ChartsheetMapping(ExcelContext xlsContext, ChartsheetPart targetPart) : base(targetPart.XmlWriter) { this._xlsContext = xlsContext; this._chartsheetPart = targetPart; }
/// <summary> /// The overload apply method /// Creates the Workbook xml document /// </summary> /// <param name="bsd">WorkSheetData</param> public void Apply(WorkBookData workbookData) { _writer.WriteStartDocument(); _writer.WriteStartElement("workbook", OpenXmlNamespaces.SpreadsheetML); _writer.WriteAttributeString("xmlns", Sml.Ns); _writer.WriteAttributeString("xmlns", "r", "", OpenXmlNamespaces.Relationships); // fileVersion _writer.WriteStartElement(Sml.Workbook.ElFileVersion, Sml.Ns); _writer.WriteAttributeString(Sml.Workbook.AttrAppName, "xl"); _writer.WriteEndElement(); // workbookPr _writer.WriteStartElement(Sml.Workbook.ElWorkbookPr, Sml.Ns); if (workbookData.CodeName != null) { _writer.WriteAttributeString(Sml.Workbook.AttrCodeName, workbookData.CodeName.codeName.Value); } _writer.WriteEndElement(); _writer.WriteStartElement("sheets"); // create the sheets foreach (SheetData sheet in workbookData.boundSheetDataList) { OpenXmlPart sheetPart; switch (sheet.boundsheetRecord.dt) { case BoundSheet8.SheetType.Worksheet: { WorksheetPart worksheetPart = this._workbookPart.AddWorksheetPart(); sheet.Convert(new WorksheetMapping(this._xlsContext, worksheetPart)); sheetPart = worksheetPart; } break; case BoundSheet8.SheetType.Chartsheet: { ChartsheetPart chartsheetPart = this._workbookPart.AddChartsheetPart(); sheet.Convert(new ChartsheetMapping(this._xlsContext, chartsheetPart)); sheetPart = chartsheetPart; } break; default: { sheet.emtpyWorksheet = true; WorksheetPart worksheetPart = this._workbookPart.AddWorksheetPart(); sheet.Convert(new WorksheetMapping(this._xlsContext, worksheetPart)); sheetPart = worksheetPart; } break; } _writer.WriteStartElement("sheet"); _writer.WriteAttributeString("name", sheet.boundsheetRecord.stName.Value); _writer.WriteAttributeString("sheetId", sheetPart.RelId.ToString()); _writer.WriteAttributeString("r", "id", OpenXmlNamespaces.Relationships, sheetPart.RelIdToString); _writer.WriteEndElement(); } _writer.WriteEndElement(); // close sheetData bool ParentTagWritten = false; if (workbookData.supBookDataList.Count != 0) { /* * <externalReferences> * <externalReference r:id="rId4" /> * <externalReference r:id="rId5" /> * </externalReferences> */ foreach (SupBookData var in workbookData.supBookDataList) { if (!var.SelfRef) { if (!ParentTagWritten) { _writer.WriteStartElement("externalReferences"); ParentTagWritten = true; } _writer.WriteStartElement("externalReference"); _writer.WriteAttributeString("r", "id", OpenXmlNamespaces.Relationships, var.ExternalLinkRef); _writer.WriteEndElement(); } } if (ParentTagWritten) { _writer.WriteEndElement(); } } // write definedNames if (workbookData.definedNameList.Count > 0) { //<definedNames> //<definedName name="abc" comment="test" localSheetId="1">Sheet1!$B$3</definedName> //</definedNames> _writer.WriteStartElement("definedNames"); foreach (Lbl item in workbookData.definedNameList) { if (item.rgce.Count > 0) { _writer.WriteStartElement("definedName"); if (item.Name.Value.Length > 1) { _writer.WriteAttributeString("name", ExcelHelperClass.AssertNameIsValid(item.Name.Value)); } else { string internName = "_xlnm." + ExcelHelperClass.getNameStringfromBuiltInFunctionID(item.Name.Value); _writer.WriteAttributeString("name", internName); } if (item.itab > 0) { _writer.WriteAttributeString("localSheetId", (item.itab - 1).ToString()); } if (item.fHidden) { _writer.WriteAttributeString("hidden", "1"); } _writer.WriteValue(FormulaInfixMapping.mapFormula(item.rgce, _xlsContext)); _writer.WriteEndElement(); } } _writer.WriteEndElement(); } // close tags _writer.WriteEndElement(); // close worksheet _writer.WriteEndDocument(); // flush writer _writer.Flush(); }
/// <summary> /// Creates a chartsheet part from given data /// </summary> /// <param name="chartType">Type of chart to generate</param> /// <param name="values">Values to represent in the chart</param> /// <param name="headerReference">Columns to be used as series</param> /// <param name="categoryReference">Column to be used as category</param> /// <returns>Chartsheet part with contents related</returns> public static ChartsheetPart Create(SpreadsheetDocument parentDocument, ChartType chartType, List <string> values, List <string> headerReference, string categoryReference) { //Creates base content and associates it to a new chartsheet part WorkbookPart workbook = parentDocument.WorkbookPart; ChartsheetPart chartsheetPart = workbook.AddNewPart <ChartsheetPart>(); XDocument chartsheetDocument = CreateEmptyChartsheet(); chartsheetPart.PutXDocument(chartsheetDocument); //Creates a base drawings part and associates it to the chartsheet part DrawingsPart drawingsPart = chartsheetPart.AddNewPart <DrawingsPart>(); XDocument drawingDocument = CreateEmptyDrawing(); drawingsPart.PutXDocument(drawingDocument); //Adds content to chartsheet document to reference drawing document chartsheetDocument .Element(ns + "chartsheet") .Add( new XElement(ns + "drawing", new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart)) ) ); //creates the chart part and associates it to the drawings part ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); XDocument chartDocument = CreateChart(chartType, values, headerReference, categoryReference); chartPart.PutXDocument(chartDocument); //Adds content to drawing document to reference chart document drawingDocument .Descendants(drawingns + "graphicData") .First() .Add( new XAttribute("uri", chartns), new XElement(chartns + "chart", new XAttribute(XNamespace.Xmlns + "c", chartns), new XAttribute(XNamespace.Xmlns + "r", relationshipsns), new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart)) ) ); //Associates the chartsheet part to the workbook part XDocument document = parentDocument.WorkbookPart.GetXDocument(); int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1; int chartsheetCount = document.Root .Element(ns + "sheets") .Elements(ns + "sheet") .Where( t => t.Attribute("name").Value.StartsWith("chart") ) .Count() + 1; //Adds content to workbook document to reference chartsheet document document.Root .Element(ns + "sheets") .Add( new XElement(ns + "sheet", new XAttribute("name", string.Format("chart{0}", chartsheetCount)), new XAttribute("sheetId", sheetId), new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart)) ) ); chartsheetPart.PutXDocument(); drawingsPart.PutXDocument(); parentDocument.WorkbookPart.PutXDocument(); return(chartsheetPart); }