private void SetAllNull()
{
this.sAuthor = string.Empty;
this.rst = new SLRstType();
this.HasSetPosition = false;
this.Top = 0;
this.Left = 0;
this.UsePositionMargin = false;
this.TopMargin = 0;
this.LeftMargin = 0;
this.AutoSize = false;
this.fWidth = SLConstants.DefaultCommentBoxWidth;
this.fHeight = SLConstants.DefaultCommentBoxHeight;
this.Fill = new SLA.SLFill(this.listThemeColors);
this.Fill.SetSolidFill(System.Drawing.Color.FromArgb(255, 255, 225), 0);
this.bFromTransparency = 0;
this.bToTransparency = 0;
this.LineColor = null;
this.fLineWeight = null;
this.LineStyle = StrokeLineStyleValues.Single;
this.vLineDashStyle = null;
this.vEndCap = null;
this.HorizontalTextAlignment = SLHorizontalTextAlignmentValues.Left;
this.VerticalTextAlignment = SLVerticalTextAlignmentValues.Top;
this.Orientation = SLCommentOrientationValues.Horizontal;
this.TextDirection = SLAlignmentReadingOrderValues.ContextDependent;
this.HasShadow = true;
this.ShadowColor = System.Drawing.Color.Black;
this.Visible = false;
}
/// <summary>
/// Clone a new instance of SLRstType.
/// </summary>
/// <returns>A cloned instance of this SLRstType.</returns>
public SLRstType Clone()
{
SLRstType rst = new SLRstType(this.MajorFont, this.MinorFont, this.listThemeColors, this.listIndexedColors);
rst.istrReal = (InlineString)this.istrReal.CloneNode(true);
return(rst);
}
/// <summary>
/// Get existing shared strings. WARNING: This is only a snapshot. Any changes made to the returned result are not used.
/// </summary>
/// <returns>A list of existing shared strings.</returns>
public List <SLRstType> GetSharedStrings()
{
List <SLRstType> result = new List <SLRstType>();
SLRstType rst = new SLRstType();
for (int i = 0; i < listSharedString.Count; ++i)
{
rst.FromHash(listSharedString[i]);
result.Add(rst.Clone());
}
return(result);
}
/// <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);
}
/// <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];
}
}
/// <summary>
/// Set the comment text given rich text content.
/// </summary>
/// <param name="RichText">The rich text content</param>
public void SetText(SLRstType RichText)
{
this.rst = new SLRstType();
this.rst = RichText.Clone();
}
/// <summary>
/// Set the comment text.
/// </summary>
/// <param name="Text">The comment text.</param>
public void SetText(string Text)
{
this.rst = new SLRstType();
this.rst.SetText(Text);
}
