/// <summary>
/// Adds a given worksheet to the document
/// </summary>
/// <param name="worksheet">Worksheet document to add</param>
/// <returns>Worksheet part just added</returns>
public OpenXmlSDK.WorksheetPart Add(XDocument worksheet)
{
// Associates base content to a new worksheet part
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>();
XDocument worksheetDocument = parentDocument.GetXDocument(worksheetPart);
if (worksheetDocument.Root == null)
{
worksheetDocument.Add(
new XElement("root")
);
}
worksheetDocument.Root.ReplaceWith(worksheet.Root);
// Associates the worksheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Count() + 1;
int worksheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
)
.Count() + 1;
// Adds content to workbook document to reference worksheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
)
);
return(worksheetPart);
}
/// <summary>
/// Creates a spreadsheet document 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="initialRow">Row to start copying data from</param>
/// <returns></returns>
public static SpreadsheetDocument Create(string filePath, List <string> headerList, string[][] valueTable, int initialRow)
{
headerRow = initialRow;
SpreadsheetDocument document = Create(filePath);
//Creates a worksheet with given data
OpenXmlSDK.WorksheetPart worksheet = document.Worksheets.Create(headerList, valueTable, headerRow);
XDocument worksheetDocument = document.GetXDocument(document.Document.WorkbookPart);
return(document);
}
/// <summary>
/// Gets the internal name of a worksheet from a document
/// </summary>
private static string GetSheetName(OpenXmlSDK.WorksheetPart worksheet, SpreadsheetDocument document)
{
//Gets the id of worksheet part
string partId = document.Document.WorkbookPart.GetIdOfPart(worksheet);
XDocument workbookDocument = document.GetXDocument(document.Document.WorkbookPart);
//Gets the name from sheet tag related to worksheet
string sheetName =
workbookDocument.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute(relationshipsns + "id").Value == partId
).First()
.Attribute("name").Value;
return(sheetName);
}
/// <summary>
/// Creates an empty (base) SpreadsheetDocument
/// </summary>
private static SpreadsheetDocument Create(string filePath)
{
SpreadsheetDocument document = new SpreadsheetDocument(filePath, true);
OpenXmlSDK.WorkbookPart workbookPart = ((OpenXmlSDK.SpreadsheetDocument)document.Document).AddWorkbookPart();
XDocument workbookDocument = document.GetXDocument(workbookPart);
//Creates an empty workbook
XDocument workbookContent = CreateEmptyWorkbook();
if (workbookDocument.Root == null)
{
workbookDocument.Add(workbookContent.Root);
}
else
{
workbookDocument.Root.ReplaceWith(workbookContent.Root);
}
return(document);
}
/// <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 SpreadsheetDocument Create(string filePath, List <string> headerList, string[][] valueTable, ChartType chartType, string categoryColumn, List <string> columnsToChart, int initialRow)
{
headerRow = initialRow;
SpreadsheetDocument document = Create(filePath);
//Creates worksheet with data
OpenXmlSDK.WorksheetPart worksheet = document.Worksheets.Create(headerList, valueTable, headerRow);
XDocument worksheetDocument = document.GetXDocument(document.Document.WorkbookPart);
//Creates chartsheet with given series and category
string sheetName = GetSheetName(worksheet, document);
OpenXmlSDK.ChartsheetPart chartsheet =
document.Chartsheets.Create(
chartType,
GetValueReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
GetHeaderReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
GetCategoryReference(sheetName, categoryColumn, headerList, valueTable)
);
return(document);
}
/// <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 OpenXmlSDK.ChartsheetPart Create(ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
{
//Creates base content and associates it to a new chartsheet part
XDocument chartsheet = CreateEmptyChartsheet();
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.ChartsheetPart chartsheetPart = workbook.AddNewPart <OpenXmlSDK.ChartsheetPart>();
XDocument chartsheetDocument = parentDocument.GetXDocument(chartsheetPart);
XDocument newChartsheetDocument = CreateEmptyChartsheet();
if (chartsheetDocument.Root == null)
{
chartsheetDocument.Add(
newChartsheetDocument.Root
);
}
else
{
chartsheetDocument.Root.ReplaceWith(newChartsheetDocument.Root);
}
//Creates a base drawings part and associates it to the chartsheet part
OpenXmlSDK.DrawingsPart drawingsPart = chartsheetPart.AddNewPart <OpenXmlSDK.DrawingsPart>();
XDocument drawingsDocument = parentDocument.GetXDocument(drawingsPart);
XDocument newDrawingDocument = CreateEmptyDrawing();
if (drawingsDocument.Root == null)
{
drawingsDocument.Add(
newDrawingDocument.Root
);
}
else
{
drawingsDocument.Root.ReplaceWith(newDrawingDocument.Root);
}
//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
OpenXmlSDK.ChartPart chartPart = drawingsPart.AddNewPart <OpenXmlSDK.ChartPart>();
XDocument chartDocument = parentDocument.GetXDocument(chartPart);
XDocument newChartDocument = CreateChart(chartType, values, headerReference, categoryReference);// CreateEmptyChart();
if (chartDocument.Root == null)
{
chartDocument.Add(
newChartDocument.Root
);
}
else
{
chartDocument.Root.ReplaceWith(newChartDocument.Root);
}
//Adds content to drawing document to reference chart document
drawingsDocument
.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.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
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))
)
);
return(chartsheetPart);
}
/// <summary>
/// Method for adding a new table definition part
/// </summary>
/// <param name="worksheet">Worksheet to add the table to</param>
/// <param name="tableStyle">Style to be assigned to the table</param>
/// <param name="useHeaders">Set a header row</param>
/// <param name="fromColumn">Initial column for table</param>
/// <param name="toColumn">Final column for table</param>
/// <param name="fromRow">Intial row for table</param>
/// <param name="toRow">Final row for table</param>
public void Add(OpenXmlSDK.WorksheetPart worksheet, string tableStyle, bool useHeaders, short fromColumn, short toColumn, int fromRow, int toRow)
{
//Getting the id for this table
int tableId = GetNextTableId();
//Set the table cell range
string tableRange = string.Format("{0}{1}:{2}{3}", WorksheetAccessor.GetColumnId(fromColumn),
fromRow,
WorksheetAccessor.GetColumnId(toColumn),
toRow);
//Creating a new id for the relationship between the table definition part and the worksheet
string tableRelationShipId = "rId" + Guid.NewGuid();
//Create a new table definition part
OpenXmlSDK.TableDefinitionPart table = worksheet.AddNewPart <OpenXmlSDK.TableDefinitionPart>(tableRelationShipId);
//string tableColumns = string.Empty;
XElement tableColumnsXElement = new XElement(ns + "tableColumns", new XAttribute("count", (toColumn - fromColumn) + 1));
//Get the name for table column elements from the first table row
string[] tableHeaders = GetTableHeaders(worksheet, fromRow, fromColumn, toColumn);
for (int i = 0; i <= (toColumn - fromColumn); i++)
{
//Create the markup for the SpreadsheetML table column elements
tableColumnsXElement.Add(
new XElement(ns + "tableColumn", new XAttribute("id", i + 1), new XAttribute("name", tableHeaders[i])));
}
XElement tableXElement =
new XElement(ns + "table",
new XAttribute("xmlns", ns), //default namespace
new XAttribute("id", tableId),
new XAttribute("name", "Table" + tableId.ToString()),
new XAttribute("displayName", "Table" + tableId.ToString()),
new XAttribute("ref", tableRange),
new XAttribute("totalsRowShown", "0"));
if (useHeaders)
{
tableXElement.Add(
new XElement(ns + "autoFilter", new XAttribute("ref", tableRange)));
}
tableXElement.Add(tableColumnsXElement);
tableXElement.Add(
new XElement(ns + "tableStyleInfo",
new XAttribute("name", tableStyle),
new XAttribute("showFirstColumn", "0"),
new XAttribute("showLastColumn", "0"),
new XAttribute("showRowStripes", "0"),
new XAttribute("showColumnStripes", "0")));
//Write the markup to the Table Definition Part Stream
XmlWriter tablePartStreamWriter = XmlWriter.Create(table.GetStream());
tableXElement.WriteTo(tablePartStreamWriter);
tablePartStreamWriter.Flush();
tablePartStreamWriter.Close();
//Create or modify the table parts definition at worksheet (for setting the relationship id with the new table)
XDocument worksheetMarkup = parentDocument.GetXDocument(worksheet);
//Look for the tableParts element at worksheet markup
XElement tablePartsElement = worksheetMarkup.Root.Element(ns + "tableParts");
if (tablePartsElement != null)
{
//tableParts elements does exist at worksheet markup
//increment the tableParts count attribute value
short tableCount = System.Convert.ToInt16(tablePartsElement.Attribute("count").Value);
tablePartsElement.SetAttributeValue("count", tableCount++.ToString());
}
else
{
//tableParts does not exist at worksheet markup
//create a new tableParts element
tablePartsElement = new XElement(ns + "tableParts",
new XAttribute(ns + "count", "1"));
worksheetMarkup.Root.Add(tablePartsElement);
}
//create the tablePart element
XElement tablePartEntryElement = new XElement(ns + "tablePart",
new XAttribute(relationshipns + "id", tableRelationShipId));
//add the new tablePart element to the worksheet tableParts element
tablePartsElement.Add(tablePartEntryElement);
}
