Exemplo n.º 1
0
        /// <summary>
        /// Insert comment given the row index and column index of the cell it's based on. This will overwrite any existing comment.
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        /// <param name="Comment">The cell comment.</param>
        /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
        public bool InsertComment(int RowIndex, int ColumnIndex, SLComment Comment)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return(false);
            }

            if (!slws.Authors.Contains(Comment.Author))
            {
                slws.Authors.Add(Comment.Author);
            }

            SLCellPoint pt   = new SLCellPoint(RowIndex, ColumnIndex);
            SLComment   comm = Comment.Clone();

            slws.Comments[pt] = comm;

            return(true);
        }
        /// <summary>
        /// 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>
        /// Remove an existing hyperlink.
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        public void RemoveHyperlink(int RowIndex, int ColumnIndex)
        {
            if (RowIndex < 1 || RowIndex > SLConstants.RowLimit)
            {
                return;
            }
            if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit)
            {
                return;
            }

            // I'm assuming hyperlinks are tied to just one cell. Apparently,
            // you can assign a block of cells as the hyperlink.
            // Excel removes the cells of the hyperlink that are empty. I'm not going to even try...

            List <SLCellPointRange> listdelete = new List <SLCellPointRange>();

            int                   i, j;
            WorksheetPart         wsp;
            string                sRelId;
            HyperlinkRelationship hlrel;

            if (!IsNewWorksheet)
            {
                for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
                {
                    if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex &&
                        RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex &&
                        slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex &&
                        ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)
                    {
                        if (slws.Hyperlinks[i].IsNew)
                        {
                            slws.Hyperlinks.RemoveAt(i);
                        }
                        else
                        {
                            if (slws.Hyperlinks[i].IsExternal &&
                                slws.Hyperlinks[i].Id != null &&
                                slws.Hyperlinks[i].Id.Length > 0)
                            {
                                sRelId = slws.Hyperlinks[i].Id;
                                if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
                                {
                                    wsp   = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
                                    hlrel = wsp.HyperlinkRelationships.Where(hlid => hlid.Id == sRelId).FirstOrDefault();
                                    if (hlrel != null)
                                    {
                                        wsp.DeleteReferenceRelationship(hlrel);
                                    }
                                }
                            }

                            slws.Hyperlinks.RemoveAt(i);
                        }

                        listdelete.Add(new SLCellPointRange(
                                           slws.Hyperlinks[i].Reference.StartRowIndex,
                                           slws.Hyperlinks[i].Reference.StartColumnIndex,
                                           slws.Hyperlinks[i].Reference.EndRowIndex,
                                           slws.Hyperlinks[i].Reference.EndColumnIndex));
                    }
                }
            }
            else
            {
                // if it's a new worksheet, all hyperlinks are new.
                // Most importantly, no hyperlink relationships were added, so
                // we can just remove from the SLWorksheet list.
                // Start from the end because we'll be deleting.
                for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
                {
                    if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex &&
                        RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex &&
                        slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex &&
                        ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)
                    {
                        slws.Hyperlinks.RemoveAt(i);

                        listdelete.Add(new SLCellPointRange(
                                           slws.Hyperlinks[i].Reference.StartRowIndex,
                                           slws.Hyperlinks[i].Reference.StartColumnIndex,
                                           slws.Hyperlinks[i].Reference.EndRowIndex,
                                           slws.Hyperlinks[i].Reference.EndColumnIndex));
                    }
                }
            }

            if (listdelete.Count > 0)
            {
                // remove hyperlink style
                SLCell      c;
                SLCellPoint pt;
                foreach (SLCellPointRange ptrange in listdelete)
                {
                    for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i)
                    {
                        for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j)
                        {
                            pt = new SLCellPoint(i, j);
                            if (slws.Cells.ContainsKey(pt))
                            {
                                c              = slws.Cells[pt];
                                c.StyleIndex   = 0;
                                slws.Cells[pt] = c.Clone();
                            }
                        }
                    }
                }
            }
        }
        // TODO: Hyperlink cell range

        /// <summary>
        /// Insert a hyperlink.
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        /// <param name="HyperlinkType">The type of hyperlink.</param>
        /// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param>
        /// <param name="Display">The display text. Set null or an empty string to use the default.</param>
        /// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param>
        /// <param name="OverwriteExistingCell">True to overwrite the existing cell value with the hyperlink display text. False otherwise.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
        {
            if (RowIndex < 1 || RowIndex > SLConstants.RowLimit)
            {
                return(false);
            }
            if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit)
            {
                return(false);
            }

            SLHyperlink hl = new SLHyperlink();

            hl.IsNew = true;

            hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);

            switch (HyperlinkType)
            {
            case SLHyperlinkTypeValues.EmailAddress:
                hl.IsExternal       = true;
                hl.HyperlinkUri     = string.Format("mailto:{0}", Address);
                hl.HyperlinkUriKind = UriKind.Absolute;
                break;

            case SLHyperlinkTypeValues.FilePath:
                hl.IsExternal   = true;
                hl.HyperlinkUri = Address;
                // assume if it starts with ../ or ./ it's a relative path.
                hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
                break;

            case SLHyperlinkTypeValues.InternalDocumentLink:
                hl.IsExternal = false;
                hl.Location   = Address;
                break;

            case SLHyperlinkTypeValues.Url:
                hl.IsExternal       = true;
                hl.HyperlinkUri     = Address;
                hl.HyperlinkUriKind = UriKind.Absolute;
                break;
            }

            if (Display == null)
            {
                hl.Display = Address;
            }
            else
            {
                if (Display.Length == 0)
                {
                    hl.Display = Address;
                }
                else
                {
                    hl.Display = Display;
                }
            }

            if (ToolTip != null && ToolTip.Length > 0)
            {
                hl.ToolTip = ToolTip;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell      c;
            SLStyle     style;

            if (slws.Cells.ContainsKey(pt))
            {
                c     = slws.Cells[pt];
                style = new SLStyle();
                if (c.StyleIndex < listStyle.Count)
                {
                    style.FromHash(listStyle[(int)c.StyleIndex]);
                }
                else
                {
                    style.FromHash(listStyle[0]);
                }
                style.SetFontUnderline(UnderlineValues.Single);
                style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
                c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());

                if (OverwriteExistingCell)
                {
                    // in case there's a formula
                    c.CellFormula = null;
                    c.DataType    = CellValues.SharedString;
                    c.CellText    = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
                }
                // else don't have to do anything

                slws.Cells[pt] = c.Clone();
            }
            else
            {
                c = new SLCell();

                style = new SLStyle();
                style.FromHash(listStyle[0]);
                style.SetFontUnderline(UnderlineValues.Single);
                style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
                c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());

                c.DataType     = CellValues.SharedString;
                c.CellText     = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
                slws.Cells[pt] = c.Clone();
            }

            slws.Hyperlinks.Add(hl);

            return(true);
        }