/// <summary> /// Remove an existing hyperlink. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> public void RemoveHyperlink(string CellReference) { int iRowIndex = -1; int iColumnIndex = -1; SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex); this.RemoveHyperlink(iRowIndex, iColumnIndex); }
internal void FromSortCondition(SortCondition sc) { this.SetAllNull(); if (sc.Descending != null && sc.Descending.Value) { this.Descending = sc.Descending.Value; } if (sc.SortBy != null) { this.SortBy = sc.SortBy.Value; } int iStartRowIndex = 1; int iStartColumnIndex = 1; int iEndRowIndex = 1; int iEndColumnIndex = 1; string sRef = sc.Reference.Value; if (sRef.IndexOf(":") > 0) { if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iEndRowIndex; this.EndColumnIndex = iEndColumnIndex; } } else { if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iStartRowIndex; this.EndColumnIndex = iStartColumnIndex; } } if (sc.CustomList != null) { this.CustomList = sc.CustomList.Value; } if (sc.FormatId != null) { this.FormatId = sc.FormatId.Value; } if (sc.IconSet != null) { this.IconSet = sc.IconSet.Value; } if (sc.IconId != null) { this.IconId = sc.IconId.Value; } }
/// <summary> /// Import a text file as a data source, with the first data row and first data column at a specific cell. /// </summary> /// <param name="FileName">The file name.</param> /// <param name="AnchorCellReference">The anchor cell reference, such as "A1".</param> /// <param name="Options">Text import options.</param> public void ImportText(string FileName, string AnchorCellReference, SLTextImportOptions Options) { int iRowIndex = -1; int iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex)) { this.ImportText(FileName, iRowIndex, iColumnIndex, Options); } }
internal void FromAutoFilter(AutoFilter af) { this.SetAllNull(); int iStartRowIndex = 1; int iStartColumnIndex = 1; int iEndRowIndex = 1; int iEndColumnIndex = 1; string sRef = af.Reference.Value; if (sRef.IndexOf(":") > 0) { if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iEndRowIndex; this.EndColumnIndex = iEndColumnIndex; } } else { if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iStartRowIndex; this.EndColumnIndex = iStartColumnIndex; } } if (af.HasChildren) { SLFilterColumn fc; using (OpenXmlReader oxr = OpenXmlReader.Create(af)) { while (oxr.Read()) { if (oxr.ElementType == typeof(FilterColumn)) { fc = new SLFilterColumn(); fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement()); this.FilterColumns.Add(fc); } else if (oxr.ElementType == typeof(SortState)) { this.SortState = new SLSortState(); this.SortState.FromSortState((SortState)oxr.LoadCurrentElement()); this.HasSortState = true; } } } } }
// merging spreadsheets is kinda like importing data, right? So it's here then. // public void MergeSpreadsheet(string SpreadsheetFileName) // { // this.MergeSpreadsheet(true, SpreadsheetFileName, null); // } // public void MergeSpreadsheet(Stream SpreadsheetStream) // { // this.MergeSpreadsheet(false, null, SpreadsheetStream); // } // private void MergeSpreadsheet(bool IsFile, string SpreadsheetFileName, Stream SpreadsheetStream) // { // using (MemoryStream msAnother = new MemoryStream()) // { // if (IsFile) // { // byte[] baData = File.ReadAllBytes(SpreadsheetFileName); // msAnother.Write(baData, 0, baData.Length); // } // else // { // SpreadsheetStream.Position = 0; // byte[] baData = new byte[SpreadsheetStream.Length]; // SpreadsheetStream.Read(baData, 0, baData.Length); // msAnother.Write(baData, 0, baData.Length); // } // using (SpreadsheetDocument xlAnother = SpreadsheetDocument.Open(msAnother, false)) // { // HashSet<string> hsCurrentSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase); // foreach (SLSheet sheet in slwb.Sheets) // { // // current sheet names supposed to be unique, so I'm not checking for collisions. // hsCurrentSheetNames.Add(sheet.Name); // } // HashSet<string> hsAnotherSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase); // List<string> listAnotherSheetNames = new List<string>(); // using (OpenXmlReader oxr = OpenXmlReader.Create(xlAnother.WorkbookPart.Workbook.Sheets)) // { // string sSheetName; // while (oxr.Read()) // { // if (oxr.ElementType == typeof(Sheet)) // { // sSheetName = ((Sheet)oxr.LoadCurrentElement()).Name.Value; // hsAnotherSheetNames.Add(sSheetName); // listAnotherSheetNames.Add(sSheetName); // } // } // } ////Sheet1 ////Sheet2 ////Sheet3 ////Sheet1 -> Sheet7 -> Sheet8 ////Sheet6 ////Sheet7 // Dictionary<string, string> dictAnotherNewSheetNames = new Dictionary<string, string>(); // foreach (string s in listAnotherSheetNames) // { // } // } // // end of using SpreadsheetDocument // } // } /// <summary> /// Import a System.Data.DataTable as a data source, with the first data row and first data column at a specific cell. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> /// <param name="Data">The data table.</param> /// <param name="IncludeHeader">True if the data table's column names are to be used in the first row as a header row. False otherwise.</param> public void ImportDataTable(string CellReference, DataTable Data, bool IncludeHeader) { int iRowIndex = -1; int iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return; } ImportDataTable(iRowIndex, iColumnIndex, Data, IncludeHeader); }
/// <summary> /// Insert a hyperlink. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> /// <param name="HyperlinkType">The type of hyperlink.</param> /// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param> /// <param name="Display">The display text. Set null or an empty string to use the default.</param> /// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param> /// <param name="OverwriteExistingCell">True to overwrite the existing cell value with the hyperlink display text. False otherwise.</param> /// <returns>True if successful. False otherwise.</returns> public bool InsertHyperlink(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell) { int iRowIndex = -1; int iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return(false); } return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, Display, ToolTip, OverwriteExistingCell)); }
/// <summary> /// Insert a hyperlink. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> /// <param name="HyperlinkType">The type of hyperlink.</param> /// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param> /// <returns>True if successful. False otherwise.</returns> public bool InsertHyperlink(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address) { int iRowIndex = -1; int iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return(false); } return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, null, null, false)); }
/// <summary> /// Adds a print area to the existing print area on the currently selected worksheet given a corner cell of the print area and the opposite corner cell. /// </summary> /// <param name="StartCellReference">The cell reference of the corner cell, such as "A1".</param> /// <param name="EndCellReference">The cell reference of the opposite corner cell, such as "A1".</param> public void AddToPrintArea(string StartCellReference, string EndCellReference) { int iStartRowIndex = -1; int iStartColumnIndex = -1; int iEndRowIndex = -1; int iEndColumnIndex = -1; SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex); SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex); this.SetAddPrintArea(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true); }
/// <summary> /// Insert comment given the cell reference of the cell it's based on. This will overwrite any existing comment. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> /// <param name="Comment">The cell comment.</param> /// <returns>False if the cell reference is invalid. True otherwise.</returns> public bool InsertComment(string CellReference, SLComment Comment) { int iRowIndex = -1; int iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return(false); } return(InsertComment(iRowIndex, iColumnIndex, Comment)); }
internal SLCalculationCell(string CellReference) { this.SetAllNull(); int iRowIndex = -1; int iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { this.RowIndex = iRowIndex; this.ColumnIndex = iColumnIndex; } }
/// <summary> /// Sort data by row. /// </summary> /// <param name="StartCellReference">The cell reference of the start cell of the cell range to be sorted, such as "A1". This is typically the top-left cell.</param> /// <param name="EndCellReference">The cell reference of the end cell of the cell range to be sorted, such as "A1". This is typically the bottom-right cell.</param> /// <param name="SortByRowIndex">The row index of the row to be sorted by.</param> /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param> public void Sort(string StartCellReference, string EndCellReference, int SortByRowIndex, bool SortAscending) { int iStartRowIndex = -1; int iStartColumnIndex = -1; int iEndRowIndex = -1; int iEndColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex) && SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex)) { this.Sort(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, false, SortByRowIndex, SortAscending); } }
/// <summary> /// Sort data by column. /// </summary> /// <param name="StartCellReference">The cell reference of the start cell of the cell range to be sorted, such as "A1". This is typically the top-left cell.</param> /// <param name="EndCellReference">The cell reference of the end cell of the cell range to be sorted, such as "A1". This is typically the bottom-right cell.</param> /// <param name="SortByColumnName">The column name of the column to be sorted by, such as "AA".</param> /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param> public void Sort(string StartCellReference, string EndCellReference, string SortByColumnName, bool SortAscending) { int iStartRowIndex = -1; int iStartColumnIndex = -1; int iEndRowIndex = -1; int iEndColumnIndex = -1; int iSortByColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex) && SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex)) { iSortByColumnIndex = SLTool.ToColumnIndex(SortByColumnName); this.Sort(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true, iSortByColumnIndex, SortAscending); } }
/// <summary> /// Get existing comments in the currently selected worksheet. WARNING: This is only a snapshot. Any changes made to the returned result are not used. /// </summary> /// <returns>A Dictionary of existing comments.</returns> public Dictionary <SLCellPoint, SLRstType> GetCommentText() { Dictionary <SLCellPoint, SLRstType> result = new Dictionary <SLCellPoint, SLRstType>(); // we don't add to existing comments, so it's either get existing comments // or use the newly inserted comments. if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) { WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); if (wsp.WorksheetCommentsPart != null) { Comment comm; int iRowIndex, iColumnIndex; SLRstType rst = new SLRstType(); using (OpenXmlReader oxr = OpenXmlReader.Create(wsp.WorksheetCommentsPart.Comments.CommentList)) { while (oxr.Read()) { if (oxr.ElementType == typeof(Comment)) { comm = (Comment)oxr.LoadCurrentElement(); SLTool.FormatCellReferenceToRowColumnIndex(comm.Reference.Value, out iRowIndex, out iColumnIndex); rst.FromCommentText(comm.CommentText); result[new SLCellPoint(iRowIndex, iColumnIndex)] = rst.Clone(); } } } } else { List <SLCellPoint> pts = slws.Comments.Keys.ToList <SLCellPoint>(); foreach (SLCellPoint pt in pts) { result[pt] = slws.Comments[pt].rst.Clone(); } } } return(result); }
internal void FromTable(Table t) { this.SetAllNull(); if (t.AutoFilter != null) { this.AutoFilter.FromAutoFilter(t.AutoFilter); this.HasAutoFilter = true; } if (t.SortState != null) { this.SortState.FromSortState(t.SortState); this.HasSortState = true; } using (OpenXmlReader oxr = OpenXmlReader.Create(t.TableColumns)) { SLTableColumn tc; while (oxr.Read()) { if (oxr.ElementType == typeof(TableColumn)) { tc = new SLTableColumn(); tc.FromTableColumn((TableColumn)oxr.LoadCurrentElement()); this.TableColumns.Add(tc); } } } if (t.TableStyleInfo != null) { this.TableStyleInfo.FromTableStyleInfo(t.TableStyleInfo); this.HasTableStyleInfo = true; } this.Id = t.Id.Value; if (t.Name != null) { this.Name = t.Name.Value; } this.sDisplayName = t.DisplayName.Value; if (t.Comment != null) { this.Comment = t.Comment.Value; } int iStartRowIndex = 1; int iStartColumnIndex = 1; int iEndRowIndex = 1; int iEndColumnIndex = 1; string sRef = t.Reference.Value; if (sRef.IndexOf(":") > 0) { if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iEndRowIndex; this.EndColumnIndex = iEndColumnIndex; } } else { if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iStartRowIndex; this.EndColumnIndex = iStartColumnIndex; } } if (t.TableType != null) { this.TableType = t.TableType.Value; } if (t.HeaderRowCount != null && t.HeaderRowCount.Value != 1) { this.HeaderRowCount = t.HeaderRowCount.Value; } if (t.InsertRow != null && t.InsertRow.Value) { this.InsertRow = t.InsertRow.Value; } if (t.InsertRowShift != null && t.InsertRowShift.Value) { this.InsertRowShift = t.InsertRowShift.Value; } if (t.TotalsRowCount != null && t.TotalsRowCount.Value != 0) { this.TotalsRowCount = t.TotalsRowCount.Value; } if (t.TotalsRowShown != null && !t.TotalsRowShown.Value) { this.TotalsRowShown = t.TotalsRowShown.Value; } if (t.Published != null && t.Published.Value) { this.Published = t.Published.Value; } if (t.HeaderRowFormatId != null) { this.HeaderRowFormatId = t.HeaderRowFormatId.Value; } if (t.DataFormatId != null) { this.DataFormatId = t.DataFormatId.Value; } if (t.TotalsRowFormatId != null) { this.TotalsRowFormatId = t.TotalsRowFormatId.Value; } if (t.HeaderRowBorderFormatId != null) { this.HeaderRowBorderFormatId = t.HeaderRowBorderFormatId.Value; } if (t.BorderFormatId != null) { this.BorderFormatId = t.BorderFormatId.Value; } if (t.TotalsRowBorderFormatId != null) { this.TotalsRowBorderFormatId = t.TotalsRowBorderFormatId.Value; } if (t.HeaderRowCellStyle != null) { this.HeaderRowCellStyle = t.HeaderRowCellStyle.Value; } if (t.DataCellStyle != null) { this.DataCellStyle = t.DataCellStyle.Value; } if (t.TotalsRowCellStyle != null) { this.TotalsRowCellStyle = t.TotalsRowCellStyle.Value; } if (t.ConnectionId != null) { this.ConnectionId = t.ConnectionId.Value; } }
internal void FromSortState(SortState ss) { this.SetAllNull(); if (ss.ColumnSort != null && ss.ColumnSort.Value) { this.ColumnSort = ss.ColumnSort.Value; } if (ss.CaseSensitive != null && ss.CaseSensitive.Value) { this.CaseSensitive = ss.CaseSensitive.Value; } if (ss.SortMethod != null) { this.SortMethod = ss.SortMethod.Value; } int iStartRowIndex = 1; int iStartColumnIndex = 1; int iEndRowIndex = 1; int iEndColumnIndex = 1; string sRef = ss.Reference.Value; if (sRef.IndexOf(":") > 0) { if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iEndRowIndex; this.EndColumnIndex = iEndColumnIndex; } } else { if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex)) { this.StartRowIndex = iStartRowIndex; this.StartColumnIndex = iStartColumnIndex; this.EndRowIndex = iStartRowIndex; this.EndColumnIndex = iStartColumnIndex; } } if (ss.HasChildren) { SLSortCondition sc; using (OpenXmlReader oxr = OpenXmlReader.Create(ss)) { while (oxr.Read()) { if (oxr.ElementType == typeof(SortCondition)) { sc = new SLSortCondition(); sc.FromSortCondition((SortCondition)oxr.LoadCurrentElement()); // limit of 64 from Open XML specs if (this.SortConditions.Count < 64) { this.SortConditions.Add(sc); } } } } } }
/// <summary> /// Get the row and column indices given a cell reference such as "C5". A return value indicates whether the conversion succeeded. /// </summary> /// <param name="CellReference">The cell reference in A1 format, such as "C5".</param> /// <param name="RowIndex">When this method returns, this contains the row index of the given cell reference if the conversion succeeded.</param> /// <param name="ColumnIndex">When this method returns, this contains the column index of the given cell reference if the conversion succeeded.</param> /// <returns>True if the conversion succeeded. False otherwise.</returns> public static bool WhatIsRowColumnIndex(string CellReference, out int RowIndex, out int ColumnIndex) { RowIndex = -1; ColumnIndex = -1; return(SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out RowIndex, out ColumnIndex)); }