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