/// <summary>
/// Gets a formatted representation of a cell range from a worksheet
/// </summary>
private static string GetRangeReference(string worksheet, int startColumn, int startRow, int endColumn, int endRow)
{
return(string.Format("{0}!{1}{2}:{3}{4}",
worksheet,
WorksheetAccessor.GetColumnId(startColumn),
startRow,
WorksheetAccessor.GetColumnId(endColumn),
endRow
));
}
/// <summary>
/// Get the values for the table header row (table initial row)
/// </summary>
/// <param name="worksheet">Worksheet where the table is being defined</param>
/// <param name="row">Table initial row</param>
/// <param name="fromColumn">Table initial column</param>
/// <param name="toColumn">Table final column</param>
/// <returns></returns>
private string[] GetTableHeaders(OpenXmlSDK.WorksheetPart worksheet, int row, short fromColumn, short toColumn)
{
List <string> tableHeaders = new List <string>();
for (short c = fromColumn; c <= toColumn; c++)
{
tableHeaders.Add(WorksheetAccessor.GetValue(worksheet, c, row));
}
return(tableHeaders.ToArray <string>());
}
/// <summary>
/// Class constructor
/// </summary>
/// <param name="document">Document to perform operations on</param>
/// <param name="fullName">Full path name of document</param>
public SpreadsheetDocument(OpenXmlSDK.SpreadsheetDocument document, string fullName)
: base()
{
Document = document;
FullName = fullName;
InnerContent = new SpreadsheetDocumentManager(this);
Worksheets = new WorksheetAccessor(this);
Chartsheets = new ChartsheetAccessor(this);
CustomProperties = new CustomPropertiesAccesor(this);
Tables = new SpreadSheetTableAccesor(this);
Style = new SpreadSheetStyleAccessor(this);
}
/// <summary>
/// Creates a new SpreadsheetDocument from a new or existing file
/// </summary>
/// <param name="filePath">Path of file</param>
/// <param name="createNew">Whether create a new document or load from an existing one</param>
public SpreadsheetDocument(string filePath, bool createNew)
{
try
{
if (createNew)
{
Document = OpenXmlSDK.SpreadsheetDocument.Create(filePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
}
else
{
Document = OpenXmlSDK.SpreadsheetDocument.Open(filePath, true);
}
}
catch (Exception e)
{
System.Diagnostics.Debug.Write(e.Message);
}
FullName = filePath;
InnerContent = new SpreadsheetDocumentManager(this);
Worksheets = new WorksheetAccessor(this);
Chartsheets = new ChartsheetAccessor(this);
CustomProperties = new CustomPropertiesAccesor(this);
}
/// <summary>
/// Setting width for a range of columns
/// <remarks>Author:Johann Granados Company: Staff DotNet Creation Date: 8/30/2008</remarks>
/// </summary>
public override void SetColumnWidth(string worksheetName, short fromColumn, short toColumn, int width)
{
WorksheetAccessor.SetColumnWidth(Worksheets.Get(worksheetName), fromColumn, toColumn, width);
}
/// <summary>
/// Setting value for a spreadsheet cell
/// <remarks>Author:Johann Granados Company: Staff DotNet Creation Date: 8/30/2008</remarks>
/// </summary>
public override void SetCellStyle(string worksheetName, int fromRow, int toRow, short fromColumn, short toColumn, string cellStyle)
{
WorksheetAccessor.SetCellStyle(Worksheets.Get(worksheetName), fromColumn, toColumn, fromRow, toRow, cellStyle);
}
/// <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);
}
/// <summary>
/// Gets a formatted representation of a cell range from a worksheet
/// </summary>
private static string GetRangeReference(string worksheet, int column, int row)
{
return(string.Format("{0}!{1}{2}", worksheet, WorksheetAccessor.GetColumnId(column), row));
}
