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.ThrowExceptionsIfAny);
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 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>
/// 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>
/// This returns a list of index with pixel lengths. Depending on the type,
/// the pixel length is for row heights or column widths
/// </summary>
/// <param name="IsRow"></param>
/// <param name="StartIndex"></param>
/// <param name="EndIndex"></param>
/// <returns></returns>
internal Dictionary<int, int> AutoFitRowColumn(bool IsRow, int StartIndex, int EndIndex)
{
int i;
Dictionary<int, int> pixellength = new Dictionary<int, int>();
// initialise all to zero first. This also ensures the existence of a dictionary entry.
for (i = StartIndex; i <= EndIndex; ++i)
{
pixellength[i] = 0;
}
List<SLCellPoint> ptkeys = slws.Cells.Keys.ToList<SLCellPoint>();
SLCell c;
SLRstType rst;
Text txt;
Run run;
FontSchemeValues vFontScheme;
int index;
SLStyle style;
string sFontName;
double fFontSize;
bool bBold;
bool bItalic;
bool bStrike;
bool bUnderline;
System.Drawing.FontStyle drawstyle;
System.Drawing.SizeF szf;
string sText;
float fWidth;
float fHeight;
int iPointIndex;
foreach (SLCellPoint pt in ptkeys)
{
if (IsRow) iPointIndex = pt.RowIndex;
else iPointIndex = pt.ColumnIndex;
if (StartIndex <= iPointIndex && iPointIndex <= EndIndex)
{
c = slws.Cells[pt];
style = this.GetCellStyle(pt.RowIndex, pt.ColumnIndex);
sText = string.Empty;
fWidth = 0;
fHeight = 0;
if (c.DataType == CellValues.SharedString)
{
index = Convert.ToInt32(c.NumericValue);
if (index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType();
rst.FromHash(listSharedString[index]);
i = 0;
foreach (var child in rst.istrReal.ChildElements.Reverse())
{
sText = string.Empty;
sFontName = SimpleTheme.MinorLatinFont;
fFontSize = SLConstants.DefaultFontSize;
bBold = false;
bItalic = false;
bStrike = false;
bUnderline = false;
drawstyle = System.Drawing.FontStyle.Regular;
if (style.HasFont)
{
if (style.fontReal.HasFontScheme)
{
if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
else if (style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
else if (!string.IsNullOrEmpty(style.fontReal.FontName))
sFontName = style.fontReal.FontName;
if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value;
if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true;
if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true;
if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true;
if (style.fontReal.HasUnderline) bUnderline = true;
}
if (child is Text)
{
txt = (Text)child;
sText = txt.Text;
}
else if (child is Run)
{
run = (Run)child;
sText = run.Text.Text;
vFontScheme = FontSchemeValues.None;
using (OpenXmlReader oxr = OpenXmlReader.Create(run))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(RunFont))
{
sFontName = ((RunFont)oxr.LoadCurrentElement()).Val;
}
else if (oxr.ElementType == typeof(FontSize))
{
fFontSize = ((FontSize)oxr.LoadCurrentElement()).Val;
}
else if (oxr.ElementType == typeof(Bold))
{
Bold b = (Bold)oxr.LoadCurrentElement();
if (b.Val == null) bBold = true;
else bBold = b.Val.Value;
}
else if (oxr.ElementType == typeof(Italic))
{
Italic itlc = (Italic)oxr.LoadCurrentElement();
if (itlc.Val == null) bItalic = true;
else bItalic = itlc.Val.Value;
}
else if (oxr.ElementType == typeof(Strike))
{
Strike strk = (Strike)oxr.LoadCurrentElement();
if (strk.Val == null) bStrike = true;
else bStrike = strk.Val.Value;
}
else if (oxr.ElementType == typeof(Underline))
{
Underline und = (Underline)oxr.LoadCurrentElement();
if (und.Val == null)
{
bUnderline = true;
}
else
{
if (und.Val.Value != UnderlineValues.None) bUnderline = true;
else bUnderline = false;
}
}
else if (oxr.ElementType == typeof(FontScheme))
{
vFontScheme = ((FontScheme)oxr.LoadCurrentElement()).Val;
}
}
}
if (vFontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (vFontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
}
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0)
{
sText = sText.TrimEnd();
}
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
szf = SLTool.MeasureText(sText, sFontName, fFontSize, drawstyle);
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
++i;
}
}
}
else
{
if (c.DataType == CellValues.Number)
{
if (style.FormatCode.Length > 0)
{
if (!string.IsNullOrEmpty(c.CellText))
{
sText = SLTool.ToSampleDisplayFormat(Convert.ToDouble(c.CellText), style.FormatCode);
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, style.FormatCode);
}
}
else
{
if (!string.IsNullOrEmpty(c.CellText))
{
sText = SLTool.ToSampleDisplayFormat(Convert.ToDouble(c.CellText), "General");
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, "General");
}
}
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) sText = "TRUE";
else sText = "FALSE";
}
else
{
if (c.CellText != null) sText = c.CellText;
else sText = string.Empty;
}
sFontName = SimpleTheme.MinorLatinFont;
fFontSize = SLConstants.DefaultFontSize;
bBold = false;
bItalic = false;
bStrike = false;
bUnderline = false;
drawstyle = System.Drawing.FontStyle.Regular;
if (style.HasFont)
{
if (style.fontReal.HasFontScheme)
{
if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
else if (style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
else if (!string.IsNullOrEmpty(style.fontReal.FontName))
sFontName = style.fontReal.FontName;
if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value;
if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true;
if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true;
if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true;
if (style.fontReal.HasUnderline) bUnderline = true;
}
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
szf = SLTool.MeasureText(sText, sFontName, fFontSize, drawstyle);
fWidth = szf.Width;
fHeight = szf.Height;
}
// Through empirical experimental data, it appears that there's still a bit of padding
// at the end of the column when autofitting column widths. I don't know how to
// calculate this padding. So I guess. I experimented with the widths of obvious
// characters such as a space, an exclamation mark, a period.
// Then I remember there's the documentation on the Open XML class property
// Column.Width, which says there's an extra 5 pixels, 2 pixels on the left/right
// and a pixel for the gridlines.
// Note that this padding appears to change depending on the font/typeface and
// font size used. (Haha... where have I seen this before...) So 5 pixels doesn't
// seem to work exactly. Or maybe it's wrong because the method of measuring isn't
// what Excel actually uses to measure the text.
// Since we're autofitting, it seems fitting (haha) that the column width is slightly
// larger to accomodate the text. So it's best to err on the larger side.
// Thus we add 7 instead of the "recommended" or "documented" 5 pixels, 1 extra pixel
// on the left and right.
fWidth += 7;
// I could also have used 8, but it might have been too much of an extra padding.
// The number 8 is a lucky number in Chinese culture. Goodness knows I need some
// luck figuring out what Excel is doing...
if (style.HasAlignment && style.alignReal.TextRotation != null)
{
szf = SLTool.CalculateOuterBoundsOfRotatedRectangle(fWidth, fHeight, style.alignReal.TextRotation.Value);
fHeight = szf.Height;
fWidth = szf.Width;
}
if (IsRow)
{
if (pixellength[iPointIndex] < fHeight)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fHeight));
}
}
else
{
if (pixellength[iPointIndex] < fWidth)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fWidth));
}
}
}
}
return pixellength;
}
/// <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>
/// Get the cell value as a System.DateTime value. If the cell value wasn't originally a date/time value, the return value is undetermined.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Format">The format string used to parse the date value in the cell if the date is before the date epoch. A date before the date epoch is stored as a string, so the date precision is only as good as the format string. For example, "dd/MM/yyyy HH:mm:ss" is more precise than "dd/MM/yyyy" because the latter loses information about the hours, minutes and seconds.</param>
/// <param name="For1904Epoch">True if using 1 Jan 1904 as the date epoch. False if using 1 Jan 1900 as the date epoch. This is independent of the workbook's Date1904 property.</param>
/// <returns>A System.DateTime cell value.</returns>
public DateTime GetCellValueAsDateTime(int RowIndex, int ColumnIndex, string Format, bool For1904Epoch)
{
DateTime dt;
if (For1904Epoch) dt = SLConstants.Epoch1904();
else dt = SLConstants.Epoch1900();
// If the cell data type is Number, then it's on or after the epoch.
// If it's a string or a shared string, then a string representation of the date
// is stored, where the date is before the epoch. Then we parse the string to
// get the date.
double fDateOffset = 0.0;
string sDate = string.Empty;
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
if (c.DataType == CellValues.Number)
{
if (c.CellText != null)
{
if (double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fDateOffset))
{
dt = SLTool.CalculateDateTimeFromDaysFromEpoch(fDateOffset, For1904Epoch);
}
}
else
{
dt = SLTool.CalculateDateTimeFromDaysFromEpoch(c.NumericValue, For1904Epoch);
}
}
else if (c.DataType == CellValues.SharedString)
{
SLRstType rst = new SLRstType(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
int index = 0;
try
{
if (c.CellText != null)
{
index = int.Parse(c.CellText);
}
else
{
index = Convert.ToInt32(c.NumericValue);
}
if (index >= 0 && index < listSharedString.Count)
{
rst.FromHash(listSharedString[index]);
sDate = rst.ToPlainString();
if (Format.Length > 0)
{
dt = DateTime.ParseExact(sDate, Format, CultureInfo.InvariantCulture);
}
else
{
dt = DateTime.Parse(sDate, CultureInfo.InvariantCulture);
}
}
// no else part, because there's nothing we can do!
// Just return the default date value...
}
catch
{
// something terrible just happened. (the shared string index probably
// isn't even correct!) Don't do anything...
}
}
else if (c.DataType == CellValues.String)
{
sDate = c.CellText ?? string.Empty;
try
{
if (Format.Length > 0)
{
dt = DateTime.ParseExact(sDate, Format, CultureInfo.InvariantCulture);
}
else
{
dt = DateTime.Parse(sDate, CultureInfo.InvariantCulture);
}
}
catch
{
// don't need to do anything. Just return the default date value.
// The point is to avoid throwing exceptions.
}
}
}
}
return dt;
}
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>
/// 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);
}
/// <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);
}
/// <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>
/// 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>
/// Get the cell value as a string.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>A string cell value.</returns>
public string GetCellValueAsString(int RowIndex, int ColumnIndex)
{
string result = string.Empty;
int index = 0;
SLRstType rst = new SLRstType(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
if (c.CellText != null)
{
if (c.DataType == CellValues.String)
{
result = c.CellText;
}
else if (c.DataType == CellValues.SharedString)
{
try
{
index = int.Parse(c.CellText);
if (index >= 0 && index < listSharedString.Count)
{
rst.FromHash(listSharedString[index]);
result = rst.ToPlainString();
}
else
{
result = SLTool.XmlRead(c.CellText);
}
}
catch
{
// something terrible just happened. We'll just use whatever's in the cell...
result = SLTool.XmlRead(c.CellText);
}
}
//else if (c.DataType == CellValues.InlineString)
//{
// // there shouldn't be any inline strings
// // because they'd already be transferred to shared strings
//}
else
{
result = SLTool.XmlRead(c.CellText);
}
}
else
{
if (c.DataType == CellValues.Number)
{
result = c.NumericValue.ToString(CultureInfo.InvariantCulture);
}
else if (c.DataType == CellValues.SharedString)
{
index = Convert.ToInt32(c.NumericValue);
if (index >= 0 && index < listSharedString.Count)
{
rst.FromHash(listSharedString[index]);
result = rst.ToPlainString();
}
else
{
result = SLTool.XmlRead(c.CellText);
}
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) result = "true";
else result = "false";
}
}
}
}
return result;
}
/// <summary>
/// This returns a list of index with pixel lengths. Depending on the type,
/// the pixel length is for row heights or column widths
/// </summary>
/// <param name="IsRow"></param>
/// <param name="StartIndex"></param>
/// <param name="EndIndex"></param>
/// <param name="MaxPixelLength"></param>
/// <returns></returns>
internal Dictionary<int, int> AutoFitRowColumn(bool IsRow, int StartIndex, int EndIndex, int MaxPixelLength)
{
int i;
Dictionary<int, int> pixellength = new Dictionary<int, int>();
// initialise all to zero first. This also ensures the existence of a dictionary entry.
for (i = StartIndex; i <= EndIndex; ++i)
{
pixellength[i] = 0;
}
List<SLCellPoint> ptkeys = slws.Cells.Keys.ToList<SLCellPoint>();
SLCell c;
string sAutoFitSharedStringCacheKey;
string sAutoFitCacheKey;
double fCellValue;
SLRstType rst;
Text txt;
Run run;
FontSchemeValues vFontScheme;
int index;
SLStyle style = new SLStyle();
int iStyleIndex;
string sFontName;
double fFontSize;
bool bBold;
bool bItalic;
bool bStrike;
bool bUnderline;
System.Drawing.FontStyle drawstyle;
System.Drawing.Font ftUsable;
string sFormatCode;
string sDotNetFormatCode;
int iTextRotation;
System.Drawing.SizeF szf;
string sText;
float fWidth;
float fHeight;
int iPointIndex;
bool bSkipAdjustment;
SLCellPoint ptCheck;
// remove points that are part of merged cells
// Merged cells don't factor into autofitting.
// Start from end because we will be deleting points.
if (slws.MergeCells.Count > 0)
{
for (i = ptkeys.Count - 1; i >= 0; --i)
{
ptCheck = ptkeys[i];
foreach (SLMergeCell mc in slws.MergeCells)
{
if (mc.StartRowIndex <= ptCheck.RowIndex && ptCheck.RowIndex <= mc.EndRowIndex
&& mc.StartColumnIndex <= ptCheck.ColumnIndex && ptCheck.ColumnIndex <= mc.EndColumnIndex)
{
ptkeys.RemoveAt(i);
break;
}
}
}
}
HashSet<SLCellPoint> hsFilter = new HashSet<SLCellPoint>();
if (slws.HasAutoFilter)
{
for (i = slws.AutoFilter.StartColumnIndex; i <= slws.AutoFilter.EndColumnIndex; ++i)
{
hsFilter.Add(new SLCellPoint(slws.AutoFilter.StartRowIndex, i));
}
}
if (slws.Tables.Count > 0)
{
foreach (SLTable t in slws.Tables)
{
if (t.HasAutoFilter)
{
for (i = t.AutoFilter.StartColumnIndex; i <= t.AutoFilter.EndColumnIndex; ++i)
{
ptCheck = new SLCellPoint(t.AutoFilter.StartRowIndex, i);
if (!hsFilter.Contains(ptCheck))
{
hsFilter.Add(ptCheck);
}
}
}
}
}
// Excel seems to stop the maximum column pixel width at 2300 pixels (at least at 120 DPI).
// We need a bitmap of sufficient size because we're rendering the text and measuring it.
// 4096 pixels wide should cover the 2300 pixel thing. Note that this is also wider than
// typical screen monitors.
// 2048 pixels high should also cover most screen monitors' vertical height.
// If your text fills up the entire height of your screen, I would say your font size is
// too large...
// If you're doing this in some distant future where you can do spreadsheets on the
// freaking wall with Olympic pool sized screens, feel free to increase the dimensions.
using (System.Drawing.Bitmap bm = new System.Drawing.Bitmap(4096, 2048))
{
using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bm))
{
foreach (SLCellPoint pt in ptkeys)
{
if (IsRow) iPointIndex = pt.RowIndex;
else iPointIndex = pt.ColumnIndex;
if (StartIndex <= iPointIndex && iPointIndex <= EndIndex)
{
c = slws.Cells[pt];
iStyleIndex = (int)c.StyleIndex;
// assume if the font cache contains the style index,
// the other caches also have it.
if (dictAutoFitFontCache.ContainsKey(iStyleIndex))
{
ftUsable = dictAutoFitFontCache[iStyleIndex];
sDotNetFormatCode = dictAutoFitFormatCodeCache[iStyleIndex];
sFormatCode = sDotNetFormatCode;
iTextRotation = dictAutoFitTextRotationCache[iStyleIndex];
}
else
{
style = new SLStyle();
style.FromHash(listStyle[iStyleIndex]);
#region Get style stuff
sFontName = SimpleTheme.MinorLatinFont;
fFontSize = SLConstants.DefaultFontSize;
bBold = false;
bItalic = false;
bStrike = false;
bUnderline = false;
drawstyle = System.Drawing.FontStyle.Regular;
if (style.HasFont)
{
if (style.fontReal.HasFontScheme)
{
if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
else if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
else
{
if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value;
if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true;
if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true;
if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true;
if (style.fontReal.HasUnderline) bUnderline = true;
}
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
ftUsable = SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle);
sFormatCode = style.FormatCode;
sDotNetFormatCode = SLTool.ToDotNetFormatCode(sFormatCode);
if (style.HasAlignment && style.alignReal.TextRotation != null)
{
iTextRotation = style.alignReal.TextRotation.Value;
}
else
{
iTextRotation = 0;
}
#endregion
dictAutoFitFontCache[iStyleIndex] = (System.Drawing.Font)ftUsable.Clone();
dictAutoFitFormatCodeCache[iStyleIndex] = sDotNetFormatCode;
dictAutoFitTextRotationCache[iStyleIndex] = iTextRotation;
}
sText = string.Empty;
fWidth = 0;
fHeight = 0;
// must set empty first! Used for checking if shared string and if should set into cache.
sAutoFitSharedStringCacheKey = string.Empty;
bSkipAdjustment = false;
if (c.DataType == CellValues.SharedString)
{
index = Convert.ToInt32(c.NumericValue);
sAutoFitSharedStringCacheKey = string.Format("{0}{1}{2}",
index.ToString(CultureInfo.InvariantCulture),
SLConstants.AutoFitCacheSeparator,
c.StyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitSharedStringCache.ContainsKey(sAutoFitSharedStringCacheKey))
{
fHeight = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Height;
fWidth = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Width;
bSkipAdjustment = true;
}
else if (index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType();
rst.FromHash(listSharedString[index]);
if (rst.istrReal.ChildElements.Count == 1 && rst.istrReal.Text != null)
{
sText = rst.istrReal.Text.Text.TrimEnd();
sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey))
{
szf = dictAutoFitTextCache[sAutoFitCacheKey];
fHeight = szf.Height;
fWidth = szf.Width;
}
else
{
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
fHeight = szf.Height;
fWidth = szf.Width;
dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
else
{
i = 0;
foreach (var child in rst.istrReal.ChildElements.Reverse())
{
if (child is Text || child is Run)
{
if (child is Text)
{
txt = (Text)child;
sText = txt.Text;
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0) sText = sText.TrimEnd();
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
}
else if (child is Run)
{
sText = string.Empty;
sFontName = (ftUsable.Name != null && ftUsable.Name.Length > 0) ? ftUsable.Name : SimpleTheme.MinorLatinFont;
fFontSize = ftUsable.SizeInPoints;
bBold = ((ftUsable.Style & System.Drawing.FontStyle.Bold) > 0) ? true : false;
bItalic = ((ftUsable.Style & System.Drawing.FontStyle.Italic) > 0) ? true : false;
bStrike = ((ftUsable.Style & System.Drawing.FontStyle.Strikeout) > 0) ? true : false;
bUnderline = ((ftUsable.Style & System.Drawing.FontStyle.Underline) > 0) ? true : false;
drawstyle = System.Drawing.FontStyle.Regular;
run = (Run)child;
sText = run.Text.Text;
vFontScheme = FontSchemeValues.None;
#region Run properties
if (run.RunProperties != null)
{
foreach (var grandchild in run.RunProperties.ChildElements)
{
if (grandchild is RunFont)
{
sFontName = ((RunFont)grandchild).Val;
}
else if (grandchild is FontSize)
{
fFontSize = ((FontSize)grandchild).Val;
}
else if (grandchild is Bold)
{
Bold b = (Bold)grandchild;
if (b.Val == null) bBold = true;
else bBold = b.Val.Value;
}
else if (grandchild is Italic)
{
Italic itlc = (Italic)grandchild;
if (itlc.Val == null) bItalic = true;
else bItalic = itlc.Val.Value;
}
else if (grandchild is Strike)
{
Strike strk = (Strike)grandchild;
if (strk.Val == null) bStrike = true;
else bStrike = strk.Val.Value;
}
else if (grandchild is Underline)
{
Underline und = (Underline)grandchild;
if (und.Val == null)
{
bUnderline = true;
}
else
{
if (und.Val.Value != UnderlineValues.None) bUnderline = true;
else bUnderline = false;
}
}
else if (grandchild is FontScheme)
{
vFontScheme = ((FontScheme)grandchild).Val;
}
}
}
#endregion
if (vFontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (vFontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0) sText = sText.TrimEnd();
szf = SLTool.MeasureText(bm, g, sText, SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle));
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
}
++i;
}
}
}
}
}
else
{
if (c.DataType == CellValues.Number)
{
#region Numbers
if (sDotNetFormatCode.Length > 0)
{
if (c.CellText != null)
{
if (!double.TryParse(c.CellText, out fCellValue))
{
fCellValue = 0;
}
if (sFormatCode.Equals("@"))
{
sText = fCellValue.ToString(CultureInfo.InvariantCulture);
}
else
{
sText = SLTool.ToSampleDisplayFormat(fCellValue, sDotNetFormatCode);
}
}
else
{
if (sFormatCode.Equals("@"))
{
sText = c.NumericValue.ToString(CultureInfo.InvariantCulture);
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, sDotNetFormatCode);
}
}
}
else
{
if (c.CellText != null)
{
if (!double.TryParse(c.CellText, out fCellValue))
{
fCellValue = 0;
}
sText = SLTool.ToSampleDisplayFormat(fCellValue, "G10");
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, "G10");
}
}
#endregion
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) sText = "TRUE";
else sText = "FALSE";
}
else
{
if (c.CellText != null) sText = c.CellText;
else sText = string.Empty;
}
sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey))
{
szf = dictAutoFitTextCache[sAutoFitCacheKey];
fHeight = szf.Height;
fWidth = szf.Width;
}
else
{
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
fHeight = szf.Height;
fWidth = szf.Width;
dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
if (!bSkipAdjustment)
{
// Through empirical experimental data, it appears that there's still a bit of padding
// at the end of the column when autofitting column widths. I don't know how to
// calculate this padding. So I guess. I experimented with the widths of obvious
// characters such as a space, an exclamation mark, a period.
// Then I remember there's the documentation on the Open XML class property
// Column.Width, which says there's an extra 5 pixels, 2 pixels on the left/right
// and a pixel for the gridlines.
// Note that this padding appears to change depending on the font/typeface and
// font size used. (Haha... where have I seen this before...) So 5 pixels doesn't
// seem to work exactly. Or maybe it's wrong because the method of measuring isn't
// what Excel actually uses to measure the text.
// Since we're autofitting, it seems fitting (haha) that the column width is slightly
// larger to accomodate the text. So it's best to err on the larger side.
// Thus we add 7 instead of the "recommended" or "documented" 5 pixels, 1 extra pixel
// on the left and right.
fWidth += 7;
// I could also have used 8, but it might have been too much of an extra padding.
// The number 8 is a lucky number in Chinese culture. Goodness knows I need some
// luck figuring out what Excel is doing...
if (iTextRotation != 0)
{
szf = SLTool.CalculateOuterBoundsOfRotatedRectangle(fWidth, fHeight, iTextRotation);
fHeight = szf.Height;
fWidth = szf.Width;
}
// meaning the shared string portion was accessed (otherwise it'd be empty string)
if (sAutoFitSharedStringCacheKey.Length > 0)
{
dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
if (IsRow)
{
if (fHeight > MaxPixelLength) fHeight = MaxPixelLength;
if (pixellength[iPointIndex] < fHeight)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fHeight));
}
}
else
{
if (hsFilter.Contains(pt)) fWidth += SLConstants.AutoFilterPixelWidth;
if (fWidth > MaxPixelLength) fWidth = MaxPixelLength;
if (pixellength[iPointIndex] < fWidth)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fWidth));
}
}
}
}
// end of Graphics
}
}
return pixellength;
}
/// <summary>
/// Set the cell value given a cell reference.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <param name="Data">The cell value data in rich text.</param>
/// <returns>False if the cell reference is invalid. True otherwise.</returns>
public bool SetCellValue(string CellReference, SLRstType Data)
{
int iRowIndex = -1;
int iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return false;
}
return SetCellValue(iRowIndex, iColumnIndex, Data.ToInlineString());
}
/// <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 cell value given the row index and column index.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Data">The cell value data in rich text.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool SetCellValue(int RowIndex, int ColumnIndex, SLRstType Data)
{
return SetCellValue(RowIndex, ColumnIndex, Data.ToInlineString());
}
internal SLRstType Clone()
{
SLRstType rst = new SLRstType(this.MajorFont, this.MinorFont, this.listThemeColors, this.listIndexedColors);
rst.istrReal = (InlineString)this.istrReal.CloneNode(true);
return rst;
}
/// <summary>
/// Creates an instance of SLTable, given row and column indices of opposite cells in a cell range.
/// </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>
/// <returns>An SLTable with the required information.</returns>
public SLTable CreateTable(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
{
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 (iStartRowIndex == SLConstants.RowLimit) iStartRowIndex = SLConstants.RowLimit - 1;
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
// consider minus 1 in case there's a totals row so there's less checking...
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
if (iEndRowIndex <= iStartRowIndex) iEndRowIndex = iStartRowIndex + 1;
SLTable tbl = new SLTable();
tbl.SetAllNull();
slwb.RefreshPossibleTableId();
tbl.Id = slwb.PossibleTableId;
tbl.DisplayName = string.Format("Table{0}", tbl.Id);
tbl.Name = tbl.DisplayName;
tbl.StartRowIndex = iStartRowIndex;
tbl.StartColumnIndex = iStartColumnIndex;
tbl.EndRowIndex = iEndRowIndex;
tbl.EndColumnIndex = iEndColumnIndex;
tbl.AutoFilter.StartRowIndex = tbl.StartRowIndex;
tbl.AutoFilter.StartColumnIndex = tbl.StartColumnIndex;
tbl.AutoFilter.EndRowIndex = tbl.EndRowIndex;
tbl.AutoFilter.EndColumnIndex = tbl.EndColumnIndex;
tbl.HasAutoFilter = true;
SLTableColumn tc;
uint iColumnId = 1;
int i, index;
uint j;
SLCell c;
SLCellPoint pt;
string sHeaderText = string.Empty;
SharedStringItem ssi;
SLRstType rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
for (i = tbl.StartColumnIndex; i <= tbl.EndColumnIndex; ++i)
{
pt = new SLCellPoint(StartRowIndex, i);
sHeaderText = string.Empty;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
if (c.CellText == null)
{
if (c.DataType == CellValues.Number) sHeaderText = c.NumericValue.ToString(CultureInfo.InvariantCulture);
else if (c.DataType == CellValues.Boolean) sHeaderText = c.NumericValue > 0.5 ? "TRUE" : "FALSE";
else sHeaderText = string.Empty;
}
else
{
sHeaderText = c.CellText;
}
if (c.DataType == CellValues.SharedString)
{
index = -1;
if (c.CellText != null)
{
if (int.TryParse(c.CellText, out index))
{
index = -1;
}
}
else
{
index = Convert.ToInt32(c.NumericValue);
}
if (index >= 0 && index < listSharedString.Count)
{
ssi = new SharedStringItem();
ssi.InnerXml = listSharedString[index];
rst.FromSharedStringItem(ssi);
sHeaderText = rst.ToPlainString();
}
}
}
j = iColumnId;
if (sHeaderText.Length == 0)
{
sHeaderText = string.Format("Column{0}", j);
}
while (tbl.TableNames.Contains(sHeaderText))
{
++j;
sHeaderText = string.Format("Column{0}", j);
}
tc = new SLTableColumn();
tc.Id = iColumnId;
tc.Name = sHeaderText;
tbl.TableColumns.Add(tc);
tbl.TableNames.Add(sHeaderText);
++iColumnId;
}
tbl.TableStyleInfo.ShowFirstColumn = false;
tbl.TableStyleInfo.ShowLastColumn = false;
tbl.TableStyleInfo.ShowRowStripes = true;
tbl.TableStyleInfo.ShowColumnStripes = false;
tbl.HasTableStyleInfo = true;
return tbl;
}
// this is here because it's only used by the FillChartDataSeries() function.
private string GetCellTrueValue(SLCell c)
{
string sValue = c.CellText ?? string.Empty;
if (c.DataType == CellValues.Number)
{
if (c.CellText == null)
{
// apparently we can only print up to a limited number of decimal places,
// albeit a large number. This is a limitation on the double variable type.
// Excel can print more decimal places. You go Excel...
// Go Google IEEE and the floating point standard for more details.
// We could store using a decimal type in SLCell, but I don't think
// it's worth it given speed vs accuracy vs number range issues.
// If you need larger number of decimal places of accuracy in a chart,
// you've probably got a problem... No one's gonna be able to tell if
// there's a difference anyway... And if you need to present that many
// decimal places of accuracy, a chart is probably the wrong method of
// displaying it.
// You really need the extra decimal places? Try setting the original values
// with SetCellValueNumeric() and use up to the desired accuracy in the string.
sValue = c.NumericValue.ToString(CultureInfo.InvariantCulture);
}
}
else if (c.DataType == CellValues.SharedString)
{
if (c.CellText == null)
{
int index = Convert.ToInt32(c.NumericValue);
SLRstType rst;
sValue = string.Empty;
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] });
sValue = rst.ToPlainString();
}
}
}
else if (c.DataType == CellValues.Boolean)
{
if (c.CellText == null)
{
if (c.NumericValue > 0.5) sValue = "1";
else sValue = "0";
}
}
// no inline strings
return sValue;
}