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