/// <summary>
/// Insert comment given the row index and column index of the cell it's based on. This will overwrite any existing comment.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Comment">The cell comment.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool InsertComment(int RowIndex, int ColumnIndex, SLComment Comment)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return(false);
}
if (!slws.Authors.Contains(Comment.Author))
{
slws.Authors.Add(Comment.Author);
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLComment comm = Comment.Clone();
slws.Comments[pt] = comm;
return(true);
}
/// <summary>
/// Remove an existing hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
public void RemoveHyperlink(int RowIndex, int ColumnIndex)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit)
{
return;
}
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit)
{
return;
}
// I'm assuming hyperlinks are tied to just one cell. Apparently,
// you can assign a block of cells as the hyperlink.
// Excel removes the cells of the hyperlink that are empty. I'm not going to even try...
List <SLCellPointRange> listdelete = new List <SLCellPointRange>();
int i, j;
WorksheetPart wsp;
string sRelId;
HyperlinkRelationship hlrel;
if (!IsNewWorksheet)
{
for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
{
if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex &&
RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex &&
slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex &&
ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)
{
if (slws.Hyperlinks[i].IsNew)
{
slws.Hyperlinks.RemoveAt(i);
}
else
{
if (slws.Hyperlinks[i].IsExternal &&
slws.Hyperlinks[i].Id != null &&
slws.Hyperlinks[i].Id.Length > 0)
{
sRelId = slws.Hyperlinks[i].Id;
if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
{
wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
hlrel = wsp.HyperlinkRelationships.Where(hlid => hlid.Id == sRelId).FirstOrDefault();
if (hlrel != null)
{
wsp.DeleteReferenceRelationship(hlrel);
}
}
}
slws.Hyperlinks.RemoveAt(i);
}
listdelete.Add(new SLCellPointRange(
slws.Hyperlinks[i].Reference.StartRowIndex,
slws.Hyperlinks[i].Reference.StartColumnIndex,
slws.Hyperlinks[i].Reference.EndRowIndex,
slws.Hyperlinks[i].Reference.EndColumnIndex));
}
}
}
else
{
// if it's a new worksheet, all hyperlinks are new.
// Most importantly, no hyperlink relationships were added, so
// we can just remove from the SLWorksheet list.
// Start from the end because we'll be deleting.
for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
{
if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex &&
RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex &&
slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex &&
ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)
{
slws.Hyperlinks.RemoveAt(i);
listdelete.Add(new SLCellPointRange(
slws.Hyperlinks[i].Reference.StartRowIndex,
slws.Hyperlinks[i].Reference.StartColumnIndex,
slws.Hyperlinks[i].Reference.EndRowIndex,
slws.Hyperlinks[i].Reference.EndColumnIndex));
}
}
}
if (listdelete.Count > 0)
{
// remove hyperlink style
SLCell c;
SLCellPoint pt;
foreach (SLCellPointRange ptrange in listdelete)
{
for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i)
{
for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
c.StyleIndex = 0;
slws.Cells[pt] = c.Clone();
}
}
}
}
}
}
/// <summary>
/// Sort data either by column or row.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <param name="SortByColumn">True to sort by column. False to sort by row.</param>
/// <param name="SortByIndex">The row or column index of the row or column to be sorted by, depending on <paramref name="SortByColumn"/></param>
/// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param>
public void Sort(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool SortByColumn, int SortByIndex, bool SortAscending)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1)
{
iStartRowIndex = 1;
}
if (iStartColumnIndex < 1)
{
iStartColumnIndex = 1;
}
if (iEndRowIndex > SLConstants.RowLimit)
{
iEndRowIndex = SLConstants.RowLimit;
}
if (iEndColumnIndex > SLConstants.ColumnLimit)
{
iEndColumnIndex = SLConstants.ColumnLimit;
}
// if the given index is out of the data range, then don't have to sort.
if (SortByColumn)
{
if (SortByIndex < iStartColumnIndex || SortByIndex > iEndColumnIndex)
{
return;
}
}
else
{
if (SortByIndex < iStartRowIndex || SortByIndex > iEndRowIndex)
{
return;
}
}
Dictionary <SLCellPoint, SLCell> datacells = new Dictionary <SLCellPoint, SLCell>();
SLCellPoint pt;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
datacells[pt] = slws.Cells[pt].Clone();
slws.Cells.Remove(pt);
}
}
}
List <SLSortItem> listNumbers = new List <SLSortItem>();
List <SLSortItem> listText = new List <SLSortItem>();
List <SLSortItem> listBoolean = new List <SLSortItem>();
List <SLSortItem> listEmpty = new List <SLSortItem>();
bool bValue = false;
double fValue = 0.0;
string sText = string.Empty;
SLRstType rst;
int index = 0;
int iStartIndex = -1;
int iEndIndex = -1;
if (SortByColumn)
{
iStartIndex = iStartRowIndex;
iEndIndex = iEndRowIndex;
}
else
{
iStartIndex = iStartColumnIndex;
iEndIndex = iEndColumnIndex;
}
for (i = iStartIndex; i <= iEndIndex; ++i)
{
if (SortByColumn)
{
pt = new SLCellPoint(i, SortByIndex);
}
else
{
pt = new SLCellPoint(SortByIndex, i);
}
if (datacells.ContainsKey(pt))
{
if (datacells[pt].DataType == CellValues.Number)
{
if (datacells[pt].CellText != null)
{
if (double.TryParse(datacells[pt].CellText, out fValue))
{
listNumbers.Add(new SLSortItem()
{
Number = fValue, Index = i
});
}
else
{
listText.Add(new SLSortItem()
{
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
listNumbers.Add(new SLSortItem()
{
Number = datacells[pt].NumericValue, Index = i
});
}
}
else if (datacells[pt].DataType == CellValues.SharedString)
{
index = -1;
if (datacells[pt].CellText != null)
{
if (int.TryParse(datacells[pt].CellText, out index) &&
index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List <System.Drawing.Color>(), new List <System.Drawing.Color>());
rst.FromSharedStringItem(new SharedStringItem()
{
InnerXml = listSharedString[index]
});
listText.Add(new SLSortItem()
{
Text = rst.ToPlainString(), Index = i
});
}
else
{
listText.Add(new SLSortItem()
{
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
index = Convert.ToInt32(datacells[pt].NumericValue);
if (index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List <System.Drawing.Color>(), new List <System.Drawing.Color>());
rst.FromSharedStringItem(new SharedStringItem()
{
InnerXml = listSharedString[index]
});
listText.Add(new SLSortItem()
{
Text = rst.ToPlainString(), Index = i
});
}
else
{
listText.Add(new SLSortItem()
{
Text = datacells[pt].NumericValue.ToString(CultureInfo.InvariantCulture), Index = i
});
}
}
}
else if (datacells[pt].DataType == CellValues.Boolean)
{
if (datacells[pt].CellText != null)
{
if (double.TryParse(datacells[pt].CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue))
{
listBoolean.Add(new SLSortItem()
{
Number = fValue > 0.5 ? 1.0 : 0.0, Index = i
});
}
else if (bool.TryParse(datacells[pt].CellText, out bValue))
{
listBoolean.Add(new SLSortItem()
{
Number = bValue ? 1.0 : 0.0, Index = i
});
}
else
{
listText.Add(new SLSortItem()
{
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
listBoolean.Add(new SLSortItem()
{
Number = datacells[pt].NumericValue > 0.5 ? 1.0 : 0.0, Index = i
});
}
}
else
{
listText.Add(new SLSortItem()
{
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
listEmpty.Add(new SLSortItem()
{
Index = i
});
}
}
listNumbers.Sort(new SLSortItemNumberComparer());
if (!SortAscending)
{
listNumbers.Reverse();
}
listText.Sort(new SLSortItemTextComparer());
if (!SortAscending)
{
listText.Reverse();
}
listBoolean.Sort(new SLSortItemNumberComparer());
if (!SortAscending)
{
listBoolean.Reverse();
}
Dictionary <int, int> ReverseIndex = new Dictionary <int, int>();
if (SortAscending)
{
j = iStartIndex;
for (i = 0; i < listNumbers.Count; ++i)
{
ReverseIndex[listNumbers[i].Index] = j;
++j;
}
for (i = 0; i < listText.Count; ++i)
{
ReverseIndex[listText[i].Index] = j;
++j;
}
for (i = 0; i < listBoolean.Count; ++i)
{
ReverseIndex[listBoolean[i].Index] = j;
++j;
}
for (i = 0; i < listEmpty.Count; ++i)
{
ReverseIndex[listEmpty[i].Index] = j;
++j;
}
}
else
{
j = iStartIndex;
for (i = 0; i < listBoolean.Count; ++i)
{
ReverseIndex[listBoolean[i].Index] = j;
++j;
}
for (i = 0; i < listText.Count; ++i)
{
ReverseIndex[listText[i].Index] = j;
++j;
}
for (i = 0; i < listNumbers.Count; ++i)
{
ReverseIndex[listNumbers[i].Index] = j;
++j;
}
for (i = 0; i < listEmpty.Count; ++i)
{
ReverseIndex[listEmpty[i].Index] = j;
++j;
}
}
List <SLCellPoint> listCellKeys = datacells.Keys.ToList <SLCellPoint>();
SLCellPoint newpt;
for (i = 0; i < listCellKeys.Count; ++i)
{
pt = listCellKeys[i];
if (SortByColumn)
{
if (ReverseIndex.ContainsKey(pt.RowIndex))
{
newpt = new SLCellPoint(ReverseIndex[pt.RowIndex], pt.ColumnIndex);
}
else
{
// shouldn't happen, but just in case...
newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
}
}
else
{
if (ReverseIndex.ContainsKey(pt.ColumnIndex))
{
newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]);
}
else
{
// shouldn't happen, but just in case...
newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
}
}
slws.Cells[newpt] = datacells[pt];
}
}
// 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);
}