/// <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));
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit)
{
return(false);
}
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit)
{
return(false);
}
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0)
{
hl.ToolTip = ToolTip;
}
SLCell c;
SLStyle style;
if (slws.CellWarehouse.Exists(RowIndex, ColumnIndex))
{
c = slws.CellWarehouse.Cells[RowIndex][ColumnIndex];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count)
{
style.FromHash(listStyle[(int)c.StyleIndex]);
}
else
{
style.FromHash(listStyle[0]);
}
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.CellWarehouse.SetValue(RowIndex, ColumnIndex, c);
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.CellWarehouse.SetValue(RowIndex, ColumnIndex, c);
}
slws.Hyperlinks.Add(hl);
return(true);
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip)
{
return(InsertHyperlink(RowIndex, ColumnIndex, HyperlinkType, Address, Display, ToolTip, false));
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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="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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, bool OverwriteExistingCell)
{
return(InsertHyperlink(RowIndex, ColumnIndex, HyperlinkType, Address, null, null, OverwriteExistingCell));
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address)
{
return(InsertHyperlink(RowIndex, ColumnIndex, HyperlinkType, Address, null, null, false));
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return false;
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return false;
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0) hl.ToolTip = ToolTip;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
SLStyle style;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count) style.FromHash(listStyle[(int)c.StyleIndex]);
else style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.Cells[pt] = c.Clone();
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return true;
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip)
{
return InsertHyperlink(RowIndex, ColumnIndex, HyperlinkType, Address, Display, ToolTip, false);
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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="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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, bool OverwriteExistingCell)
{
return InsertHyperlink(RowIndex, ColumnIndex, HyperlinkType, Address, null, null, OverwriteExistingCell);
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address)
{
return InsertHyperlink(RowIndex, ColumnIndex, HyperlinkType, Address, null, null, false);
}
/// <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);
}