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> /// 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; }