private string ConvertCellToString(SLCell cell, List <SLRstType> sharedStrings)
{
// Check if it is a number
if (cell != null &&
cell.DataType == CellValues.String &&
cell.CellText != null)
{
return(cell.CellText.ToUpper());
}
else if (cell != null &&
cell.DataType == CellValues.SharedString)
{
return(sharedStrings[(int)cell.NumericValue].GetText().ToUpper());
}
else if (cell != null &&
cell.DataType == CellValues.Number)
{
return(cell.NumericValue.ToString().ToUpper());
}
throw new ArgumentException(
"The given cell is not able to be converted to a String.",
"SLCell cell",
new Exception(
$"Given Cell: {cell.ToString()}"));
}
private double ConvertCellToDouble(SLCell cell)
{
// Check if it is a number
if (cell != null &&
cell.DataType == CellValues.Number &&
cell.CellText == null)
{
return(cell.NumericValue);
}
throw new ArgumentException(
"The given cell is not able to be converted to a Double.",
"SLCell cell",
new Exception(
$"Given Cell: {cell.ToString()}"));
}
// 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);
}
var 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;
}
var 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)SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = 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)SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return(true);
}
