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