Example #1
0
        /// <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.</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, bool Data)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return false;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell c;
            if (slws.Cells.ContainsKey(pt))
            {
                c = slws.Cells[pt];
            }
            else
            {
                c = new SLCell();
                uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
                if (iStyleIndex != 0)
                {
                    c.StyleIndex = iStyleIndex;
                }
            }
            c.DataType = CellValues.Boolean;
            c.NumericValue = Data ? 1 : 0;
            slws.Cells[pt] = c;

            return true;
        }
Example #2
0
        internal static bool IsSuitableCategoryHeader(Dictionary<SLCellPoint, SLCell> Cells, int RowIndex, int ColumnIndex)
        {
            bool result = false;
            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            if (Cells.ContainsKey(pt))
            {
                if (Cells[pt].DataType == CellValues.String || Cells[pt].DataType == CellValues.SharedString)
                {
                    result = true;
                }
            }

            return result;
        }
        internal static bool IsSuitableCategoryHeader(Dictionary <SLCellPoint, SLCell> Cells, int RowIndex, int ColumnIndex)
        {
            bool        result = false;
            SLCellPoint pt     = new SLCellPoint(RowIndex, ColumnIndex);

            if (Cells.ContainsKey(pt))
            {
                if (Cells[pt].DataType == CellValues.String || Cells[pt].DataType == CellValues.SharedString)
                {
                    result = true;
                }
            }

            return(result);
        }
Example #4
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;
        }
Example #5
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);
            }

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

            slws.Comments[pt] = comm;

            return(true);
        }
Example #6
0
        /// <summary>
        /// Set the cell value given the row index and column index. Be sure to follow up with a date format style.
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        /// <param name="Data">The cell value data.</param>
        /// <param name="Format">The format string used if the given 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>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
        public bool SetCellValue(int RowIndex, int ColumnIndex, DateTime Data, string Format, bool For1904Epoch)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return false;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell c;
            if (slws.Cells.ContainsKey(pt))
            {
                c = slws.Cells[pt];
            }
            else
            {
                c = new SLCell();
                uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
                if (iStyleIndex != 0)
                {
                    c.StyleIndex = iStyleIndex;
                }
            }

            if (For1904Epoch) slwb.WorkbookProperties.Date1904 = true;

            double fDateTime = SLTool.CalculateDaysFromEpoch(Data, For1904Epoch);
            // see CalculateDaysFromEpoch to see why there's a difference
            double fDateCheck = For1904Epoch ? 0.0 : 1.0;

            if (fDateTime < fDateCheck)
            {
                // given datetime is earlier than epoch
                // So we set date to string format
                c.DataType = CellValues.SharedString;
                c.NumericValue = this.DirectSaveToSharedStringTable(Data.ToString(Format));
                slws.Cells[pt] = c;
            }
            else
            {
                c.DataType = CellValues.Number;
                c.NumericValue = fDateTime;
                slws.Cells[pt] = c;
            }

            return true;
        }
        /// <summary>
        /// Copy a range of cells from another worksheet to the currently selected worksheet, given the anchor cell of the destination range (top-left cell).
        /// </summary>
        /// <param name="WorksheetName">The name of the source worksheet.</param>
        /// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
        /// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
        /// <param name="AnchorRowIndex">The row index of the anchor cell.</param>
        /// <param name="AnchorColumnIndex">The column index of the anchor cell.</param>
        /// <param name="PasteOption">Paste option.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool CopyCellFromWorksheet(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, SLPasteTypeValues PasteOption)
        {
            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 (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
            {
                return this.CopyCell(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, AnchorRowIndex, AnchorColumnIndex, false);
            }

            string sRelId = string.Empty;
            foreach (SLSheet sheet in slwb.Sheets)
            {
                if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase))
                {
                    sRelId = sheet.Id;
                    break;
                }
            }

            // there has to be a valid existing worksheet
            if (sRelId.Length == 0) return false;

            bool result = false;
            if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
                && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
                && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
                && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit
                && AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit
                && AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit)
            {
                result = true;

                WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sRelId);

                int i, j, iSwap, iStyleIndex, iStyleIndexNew;
                SLCell origcell, newcell;
                SLCellPoint pt, newpt;
                int rowdiff = AnchorRowIndex - iStartRowIndex;
                int coldiff = AnchorColumnIndex - iStartColumnIndex;
                Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>();
                Dictionary<SLCellPoint, SLCell> sourcecells = new Dictionary<SLCellPoint, SLCell>();

                Dictionary<int, uint> sourcecolstyleindex = new Dictionary<int, uint>();
                Dictionary<int, uint> sourcerowstyleindex = new Dictionary<int, uint>();

                string sCellRef = string.Empty;
                HashSet<string> hsCellRef = new HashSet<string>();
                // I use a hash set on the logic that it's easier to check a string hash (of cell references)
                // first, rather than load a Cell class into SLCell and then check with row/column indices.
                for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                {
                    for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                    {
                        sCellRef = SLTool.ToCellReference(i, j);
                        if (!hsCellRef.Contains(sCellRef))
                        {
                            hsCellRef.Add(sCellRef);
                        }
                    }
                }

                // hyperlink ID, URL
                Dictionary<string, string> hlurl = new Dictionary<string, string>();
                List<SLHyperlink> sourcehyperlinks = new List<SLHyperlink>();

                foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships)
                {
                    if (hlrel.IsExternal)
                    {
                        hlurl[hlrel.Id] = hlrel.Uri.OriginalString;
                    }
                }

                using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
                {
                    Column col;
                    int iColumnMin, iColumnMax;
                    Row r;
                    Cell c;
                    SLHyperlink hl;
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(Column))
                        {
                            col = (Column)oxr.LoadCurrentElement();
                            iColumnMin = (int)col.Min.Value;
                            iColumnMax = (int)col.Max.Value;
                            for (i = iColumnMin; i <= iColumnMax; ++i)
                            {
                                sourcecolstyleindex[i] = (col.Style != null) ? col.Style.Value : 0;
                            }
                        }
                        else if (oxr.ElementType == typeof(Row))
                        {
                            r = (Row)oxr.LoadCurrentElement();
                            if (r.RowIndex != null)
                            {
                                if (r.StyleIndex != null) sourcerowstyleindex[(int)r.RowIndex.Value] = r.StyleIndex.Value;
                                else sourcerowstyleindex[(int)r.RowIndex.Value] = 0;
                            }

                            using (OpenXmlReader oxrRow = OpenXmlReader.Create(r))
                            {
                                while (oxrRow.Read())
                                {
                                    if (oxrRow.ElementType == typeof(Cell))
                                    {
                                        c = (Cell)oxrRow.LoadCurrentElement();
                                        if (c.CellReference != null)
                                        {
                                            sCellRef = c.CellReference.Value;
                                            if (hsCellRef.Contains(sCellRef))
                                            {
                                                origcell = new SLCell();
                                                origcell.FromCell(c);
                                                // this should work because hsCellRef already contains valid cell references
                                                SLTool.FormatCellReferenceToRowColumnIndex(sCellRef, out i, out j);
                                                pt = new SLCellPoint(i, j);
                                                sourcecells[pt] = origcell.Clone();
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        else if (oxr.ElementType == typeof(Hyperlink))
                        {
                            hl = new SLHyperlink();
                            hl.FromHyperlink((Hyperlink)oxr.LoadCurrentElement());
                            sourcehyperlinks.Add(hl);
                        }
                    }
                }

                Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>();
                Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>();

                List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>();
                SLRowProperties rp;
                foreach (int rowindex in rowindexkeys)
                {
                    rp = slws.RowProperties[rowindex];
                    rowstyleindex[rowindex] = rp.StyleIndex;
                }

                List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>();
                SLColumnProperties cp;
                foreach (int colindex in colindexkeys)
                {
                    cp = slws.ColumnProperties[colindex];
                    colstyleindex[colindex] = cp.StyleIndex;
                }

                for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                {
                    for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                    {
                        pt = new SLCellPoint(i, j);
                        newpt = new SLCellPoint(i + rowdiff, j + coldiff);
                        switch (PasteOption)
                        {
                            case SLPasteTypeValues.Formatting:
                                if (sourcecells.ContainsKey(pt))
                                {
                                    origcell = sourcecells[pt];
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        newcell = slws.Cells[newpt].Clone();
                                        newcell.StyleIndex = origcell.StyleIndex;
                                        cells[newpt] = newcell.Clone();
                                    }
                                    else
                                    {
                                        if (origcell.StyleIndex != 0)
                                        {
                                            // if not the default style, then must create a new
                                            // destination cell.
                                            newcell = new SLCell();
                                            newcell.StyleIndex = origcell.StyleIndex;
                                            newcell.CellText = string.Empty;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        else
                                        {
                                            // else source cell has default style.
                                            // Now check if destination cell lies on a row/column
                                            // that has non-default style. Remember, we don't have
                                            // a destination cell here.
                                            iStyleIndexNew = 0;
                                            if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                            if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                            if (iStyleIndexNew != 0)
                                            {
                                                newcell = new SLCell();
                                                newcell.StyleIndex = 0;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    // else no source cell
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        iStyleIndex = 0;
                                        if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
                                        if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];

                                        newcell = slws.Cells[newpt].Clone();
                                        newcell.StyleIndex = (uint)iStyleIndex;
                                        cells[newpt] = newcell.Clone();
                                    }
                                    else
                                    {
                                        // else no source and no destination, so we check for row/column
                                        // with non-default styles.
                                        iStyleIndex = 0;
                                        if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
                                        if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];

                                        iStyleIndexNew = 0;
                                        if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                        if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                        if (iStyleIndex != 0 || iStyleIndexNew != 0)
                                        {
                                            newcell = new SLCell();
                                            newcell.StyleIndex = (uint)iStyleIndex;
                                            newcell.CellText = string.Empty;
                                            cells[newpt] = newcell.Clone();
                                        }
                                    }
                                }
                                break;
                            case SLPasteTypeValues.Formulas:
                                if (sourcecells.ContainsKey(pt))
                                {
                                    origcell = sourcecells[pt];
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        newcell = slws.Cells[newpt].Clone();
                                        if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
                                        else newcell.CellFormula = null;
                                        newcell.CellText = origcell.CellText;
                                        newcell.fNumericValue = origcell.fNumericValue;
                                        newcell.DataType = origcell.DataType;
                                        cells[newpt] = newcell.Clone();
                                    }
                                    else
                                    {
                                        newcell = new SLCell();
                                        if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
                                        else newcell.CellFormula = null;
                                        newcell.CellText = origcell.CellText;
                                        newcell.fNumericValue = origcell.fNumericValue;
                                        newcell.DataType = origcell.DataType;

                                        iStyleIndexNew = 0;
                                        if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                        if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                        if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
                                        cells[newpt] = newcell.Clone();
                                    }
                                }
                                else
                                {
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        newcell = slws.Cells[newpt].Clone();
                                        newcell.CellText = string.Empty;
                                        newcell.DataType = CellValues.Number;
                                        cells[newpt] = newcell.Clone();
                                    }
                                    // no else because don't have to do anything
                                }
                                break;
                            case SLPasteTypeValues.Paste:
                                if (sourcecells.ContainsKey(pt))
                                {
                                    origcell = sourcecells[pt].Clone();
                                    cells[newpt] = origcell.Clone();
                                }
                                else
                                {
                                    // else the source cell is empty
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        iStyleIndex = 0;
                                        if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
                                        if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];

                                        if (iStyleIndex != 0)
                                        {
                                            newcell = slws.Cells[newpt].Clone();
                                            newcell.StyleIndex = (uint)iStyleIndex;
                                            newcell.CellText = string.Empty;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        else
                                        {
                                            // if the source cell is empty, then direct pasting
                                            // means overwrite the existing cell, which is faster
                                            // by just removing it.
                                            slws.Cells.Remove(newpt);
                                        }
                                    }
                                    else
                                    {
                                        // else no source and no destination, so we check for row/column
                                        // with non-default styles.
                                        iStyleIndex = 0;
                                        if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
                                        if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];

                                        iStyleIndexNew = 0;
                                        if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                        if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                        if (iStyleIndex != 0 || iStyleIndexNew != 0)
                                        {
                                            newcell = new SLCell();
                                            newcell.StyleIndex = (uint)iStyleIndex;
                                            newcell.CellText = string.Empty;
                                            cells[newpt] = newcell.Clone();
                                        }
                                    }
                                }
                                break;
                            case SLPasteTypeValues.Transpose:
                                newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex);
                                iSwap = newpt.RowIndex;
                                newpt.RowIndex = newpt.ColumnIndex;
                                newpt.ColumnIndex = iSwap;
                                newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
                                newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
                                // in case say the millionth row is transposed, because we can't have a millionth column.
                                if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit)
                                {
                                    // this part is identical to normal paste

                                    if (sourcecells.ContainsKey(pt))
                                    {
                                        origcell = sourcecells[pt].Clone();
                                        cells[newpt] = origcell.Clone();
                                    }
                                    else
                                    {
                                        // else the source cell is empty
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            iStyleIndex = 0;
                                            if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
                                            if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];

                                            if (iStyleIndex != 0)
                                            {
                                                newcell = slws.Cells[newpt].Clone();
                                                newcell.StyleIndex = (uint)iStyleIndex;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                            else
                                            {
                                                // if the source cell is empty, then direct pasting
                                                // means overwrite the existing cell, which is faster
                                                // by just removing it.
                                                slws.Cells.Remove(newpt);
                                            }
                                        }
                                        else
                                        {
                                            // else no source and no destination, so we check for row/column
                                            // with non-default styles.
                                            iStyleIndex = 0;
                                            if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
                                            if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];

                                            iStyleIndexNew = 0;
                                            if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                            if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                            if (iStyleIndex != 0 || iStyleIndexNew != 0)
                                            {
                                                newcell = new SLCell();
                                                newcell.StyleIndex = (uint)iStyleIndex;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                        }
                                    }
                                }
                                break;
                            case SLPasteTypeValues.Values:
                                // this part is identical to the formula part, except
                                // for assigning the cell formula part.

                                if (sourcecells.ContainsKey(pt))
                                {
                                    origcell = sourcecells[pt];
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        newcell = slws.Cells[newpt].Clone();
                                        newcell.CellFormula = null;
                                        newcell.CellText = origcell.CellText;
                                        newcell.fNumericValue = origcell.fNumericValue;
                                        newcell.DataType = origcell.DataType;
                                        cells[newpt] = newcell.Clone();
                                    }
                                    else
                                    {
                                        newcell = new SLCell();
                                        newcell.CellFormula = null;
                                        newcell.CellText = origcell.CellText;
                                        newcell.fNumericValue = origcell.fNumericValue;
                                        newcell.DataType = origcell.DataType;

                                        iStyleIndexNew = 0;
                                        if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                        if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                        if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
                                        cells[newpt] = newcell.Clone();
                                    }
                                }
                                else
                                {
                                    if (slws.Cells.ContainsKey(newpt))
                                    {
                                        newcell = slws.Cells[newpt].Clone();
                                        newcell.CellFormula = null;
                                        newcell.CellText = string.Empty;
                                        newcell.DataType = CellValues.Number;
                                        cells[newpt] = newcell.Clone();
                                    }
                                    // no else because don't have to do anything
                                }
                                break;
                        }
                    }
                }

                int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex;
                int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex;

                for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i)
                {
                    for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j)
                    {
                        // any cell within destination "paste" operation is taken out
                        pt = new SLCellPoint(i, j);
                        if (slws.Cells.ContainsKey(pt)) slws.Cells.Remove(pt);
                    }
                }

                foreach (SLCellPoint cellkey in cells.Keys)
                {
                    origcell = cells[cellkey];
                    // the source cells are from another worksheet. Don't know how to rearrange any
                    // cell references in cell formulas...
                    slws.Cells[cellkey] = origcell.Clone();
                }

                // See CopyCell() for the behaviour explanation
                // I'm not going to figure out how to copy merged cells from the source worksheet
                // and decide under what conditions the existing merged cells in the destination
                // worksheet should be removed.
                // So I'm going to just remove any merged cells in the delete range.
                List<SLMergeCell> mca = this.GetWorksheetMergeCells();
                foreach (SLMergeCell mc in mca)
                {
                    if (mc.StartRowIndex >= AnchorRowIndex && mc.EndRowIndex <= AnchorEndRowIndex
                        && mc.StartColumnIndex >= AnchorColumnIndex && mc.EndColumnIndex <= AnchorEndColumnIndex)
                    {
                        slws.MergeCells.Remove(mc);
                    }
                }

                // TODO: conditional formatting and data validations?

                #region Hyperlinks
                if (sourcehyperlinks.Count > 0)
                {
                    // we only care if normal paste or transpose paste. Just like Excel.
                    if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose)
                    {
                        List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>();
                        SLHyperlink hlCopied;

                        int iOverlapStartRowIndex = 1;
                        int iOverlapStartColumnIndex = 1;
                        int iOverlapEndRowIndex = 1;
                        int iOverlapEndColumnIndex = 1;
                        foreach (SLHyperlink hl in sourcehyperlinks)
                        {
                            // this comes from the separating axis theorem.
                            // See merged cells for more details.
                            // In this case however, we're doing stuff when there's overlapping.
                            if (!(iEndRowIndex < hl.Reference.StartRowIndex
                                || iStartRowIndex > hl.Reference.EndRowIndex
                                || iEndColumnIndex < hl.Reference.StartColumnIndex
                                || iStartColumnIndex > hl.Reference.EndColumnIndex))
                            {
                                // get the overlapping region
                                iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex);
                                iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex);
                                iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex);
                                iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex);

                                // offset to the correctly pasted region
                                if (PasteOption == SLPasteTypeValues.Paste)
                                {
                                    iOverlapStartRowIndex += rowdiff;
                                    iOverlapStartColumnIndex += coldiff;
                                    iOverlapEndRowIndex += rowdiff;
                                    iOverlapEndColumnIndex += coldiff;
                                }
                                else
                                {
                                    // can only be transpose. See if check above.

                                    if (iOverlapEndRowIndex > SLConstants.ColumnLimit)
                                    {
                                        // probably won't happen. This means that after transpose,
                                        // the end row index will flip to exceed the column limit.
                                        // I don't feel like testing how Excel handles this, so
                                        // I'm going to just take it as normal paste.
                                        iOverlapStartRowIndex += rowdiff;
                                        iOverlapStartColumnIndex += coldiff;
                                        iOverlapEndRowIndex += rowdiff;
                                        iOverlapEndColumnIndex += coldiff;
                                    }
                                    else
                                    {
                                        iOverlapStartRowIndex -= iStartRowIndex;
                                        iOverlapStartColumnIndex -= iStartColumnIndex;
                                        iOverlapEndRowIndex -= iStartRowIndex;
                                        iOverlapEndColumnIndex -= iStartColumnIndex;

                                        iSwap = iOverlapStartRowIndex;
                                        iOverlapStartRowIndex = iOverlapStartColumnIndex;
                                        iOverlapStartColumnIndex = iSwap;

                                        iSwap = iOverlapEndRowIndex;
                                        iOverlapEndRowIndex = iOverlapEndColumnIndex;
                                        iOverlapEndColumnIndex = iSwap;

                                        iOverlapStartRowIndex += (iStartRowIndex + rowdiff);
                                        iOverlapStartColumnIndex += (iStartColumnIndex + coldiff);
                                        iOverlapEndRowIndex += (iStartRowIndex + rowdiff);
                                        iOverlapEndColumnIndex += (iStartColumnIndex + coldiff);
                                    }
                                }

                                hlCopied = new SLHyperlink();
                                hlCopied = hl.Clone();
                                hlCopied.IsNew = true;
                                if (hlCopied.IsExternal)
                                {
                                    if (hlurl.ContainsKey(hlCopied.Id))
                                    {
                                        hlCopied.HyperlinkUri = hlurl[hlCopied.Id];
                                        if (hlCopied.HyperlinkUri.StartsWith("."))
                                        {
                                            // assume this is a relative file path such as ../ or ./
                                            hlCopied.HyperlinkUriKind = UriKind.Relative;
                                        }
                                        else
                                        {
                                            hlCopied.HyperlinkUriKind = UriKind.Absolute;
                                        }
                                        hlCopied.Id = string.Empty;
                                    }
                                }
                                hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex);
                                copiedhyperlinks.Add(hlCopied);
                            }
                        }

                        if (copiedhyperlinks.Count > 0)
                        {
                            slws.Hyperlinks.AddRange(copiedhyperlinks);
                        }
                    }
                }
                #endregion

                #region Calculation cells
                if (slwb.CalculationCells.Count > 0)
                {
                    List<int> listToDelete = new List<int>();
                    int iRowIndex = -1;
                    int iColumnIndex = -1;
                    for (i = 0; i < slwb.CalculationCells.Count; ++i)
                    {
                        if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
                        {
                            iRowIndex = slwb.CalculationCells[i].RowIndex;
                            iColumnIndex = slwb.CalculationCells[i].ColumnIndex;

                            if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex
                                && iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex)
                            {
                                // existing calculation cell lies within destination "paste" operation
                                if (!listToDelete.Contains(i)) listToDelete.Add(i);
                            }
                        }
                    }

                    for (i = listToDelete.Count - 1; i >= 0; --i)
                    {
                        slwb.CalculationCells.RemoveAt(listToDelete[i]);
                    }
                }
                #endregion
            }

            return result;
        }
        private bool CopyCell(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, bool ToCut, SLPasteTypeValues PasteOption)
        {
            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;
            }

            bool result = false;
            if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
                && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
                && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
                && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit
                && AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit
                && AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit
                && (iStartRowIndex != AnchorRowIndex || iStartColumnIndex != AnchorColumnIndex))
            {
                result = true;

                int i, j, iSwap, iStyleIndex, iStyleIndexNew;
                SLCell origcell, newcell;
                SLCellPoint pt, newpt;
                int rowdiff = AnchorRowIndex - iStartRowIndex;
                int coldiff = AnchorColumnIndex - iStartColumnIndex;
                Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>();

                Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>();
                Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>();

                List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>();
                SLRowProperties rp;
                foreach (int rowindex in rowindexkeys)
                {
                    rp = slws.RowProperties[rowindex];
                    rowstyleindex[rowindex] = rp.StyleIndex;
                }

                List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>();
                SLColumnProperties cp;
                foreach (int colindex in colindexkeys)
                {
                    cp = slws.ColumnProperties[colindex];
                    colstyleindex[colindex] = cp.StyleIndex;
                }

                for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                {
                    for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                    {
                        pt = new SLCellPoint(i, j);
                        newpt = new SLCellPoint(i + rowdiff, j + coldiff);
                        if (ToCut)
                        {
                            if (slws.Cells.ContainsKey(pt))
                            {
                                cells[newpt] = slws.Cells[pt].Clone();
                                slws.Cells.Remove(pt);
                            }
                        }
                        else
                        {
                            switch (PasteOption)
                            {
                                case SLPasteTypeValues.Formatting:
                                    if (slws.Cells.ContainsKey(pt))
                                    {
                                        origcell = slws.Cells[pt];
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            newcell = slws.Cells[newpt].Clone();
                                            newcell.StyleIndex = origcell.StyleIndex;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        else
                                        {
                                            if (origcell.StyleIndex != 0)
                                            {
                                                // if not the default style, then must create a new
                                                // destination cell.
                                                newcell = new SLCell();
                                                newcell.StyleIndex = origcell.StyleIndex;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                            else
                                            {
                                                // else source cell has default style.
                                                // Now check if destination cell lies on a row/column
                                                // that has non-default style. Remember, we don't have
                                                // a destination cell here.
                                                iStyleIndexNew = 0;
                                                if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                                if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                                if (iStyleIndexNew != 0)
                                                {
                                                    newcell = new SLCell();
                                                    newcell.StyleIndex = 0;
                                                    newcell.CellText = string.Empty;
                                                    cells[newpt] = newcell.Clone();
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        // else no source cell
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            iStyleIndex = 0;
                                            if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
                                            if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];

                                            newcell = slws.Cells[newpt].Clone();
                                            newcell.StyleIndex = (uint)iStyleIndex;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        else
                                        {
                                            // else no source and no destination, so we check for row/column
                                            // with non-default styles.
                                            iStyleIndex = 0;
                                            if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
                                            if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];

                                            iStyleIndexNew = 0;
                                            if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                            if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                            if (iStyleIndex != 0 || iStyleIndexNew != 0)
                                            {
                                                newcell = new SLCell();
                                                newcell.StyleIndex = (uint)iStyleIndex;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                        }
                                    }
                                    break;
                                case SLPasteTypeValues.Formulas:
                                    if (slws.Cells.ContainsKey(pt))
                                    {
                                        origcell = slws.Cells[pt];
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            newcell = slws.Cells[newpt].Clone();
                                            if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
                                            else newcell.CellFormula = null;
                                            newcell.CellText = origcell.CellText;
                                            newcell.fNumericValue = origcell.fNumericValue;
                                            newcell.DataType = origcell.DataType;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        else
                                        {
                                            newcell = new SLCell();
                                            if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
                                            else newcell.CellFormula = null;
                                            newcell.CellText = origcell.CellText;
                                            newcell.fNumericValue = origcell.fNumericValue;
                                            newcell.DataType = origcell.DataType;

                                            iStyleIndexNew = 0;
                                            if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                            if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                            if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
                                            cells[newpt] = newcell.Clone();
                                        }
                                    }
                                    else
                                    {
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            newcell = slws.Cells[newpt].Clone();
                                            newcell.CellText = string.Empty;
                                            newcell.DataType = CellValues.Number;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        // no else because don't have to do anything
                                    }
                                    break;
                                case SLPasteTypeValues.Paste:
                                    if (slws.Cells.ContainsKey(pt))
                                    {
                                        origcell = slws.Cells[pt].Clone();
                                        cells[newpt] = origcell.Clone();
                                    }
                                    else
                                    {
                                        // else the source cell is empty
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            iStyleIndex = 0;
                                            if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
                                            if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];

                                            if (iStyleIndex != 0)
                                            {
                                                newcell = slws.Cells[newpt].Clone();
                                                newcell.StyleIndex = (uint)iStyleIndex;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                            else
                                            {
                                                // if the source cell is empty, then direct pasting
                                                // means overwrite the existing cell, which is faster
                                                // by just removing it.
                                                slws.Cells.Remove(newpt);
                                            }
                                        }
                                        else
                                        {
                                            // else no source and no destination, so we check for row/column
                                            // with non-default styles.
                                            iStyleIndex = 0;
                                            if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
                                            if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];

                                            iStyleIndexNew = 0;
                                            if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                            if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                            if (iStyleIndex != 0 || iStyleIndexNew != 0)
                                            {
                                                newcell = new SLCell();
                                                newcell.StyleIndex = (uint)iStyleIndex;
                                                newcell.CellText = string.Empty;
                                                cells[newpt] = newcell.Clone();
                                            }
                                        }
                                    }
                                    break;
                                case SLPasteTypeValues.Transpose:
                                    newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex);
                                    iSwap = newpt.RowIndex;
                                    newpt.RowIndex = newpt.ColumnIndex;
                                    newpt.ColumnIndex = iSwap;
                                    newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
                                    newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
                                    // in case say the millionth row is transposed, because we can't have a millionth column.
                                    if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit)
                                    {
                                        // this part is identical to normal paste

                                        if (slws.Cells.ContainsKey(pt))
                                        {
                                            origcell = slws.Cells[pt].Clone();
                                            cells[newpt] = origcell.Clone();
                                        }
                                        else
                                        {
                                            // else the source cell is empty
                                            if (slws.Cells.ContainsKey(newpt))
                                            {
                                                iStyleIndex = 0;
                                                if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
                                                if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];

                                                if (iStyleIndex != 0)
                                                {
                                                    newcell = slws.Cells[newpt].Clone();
                                                    newcell.StyleIndex = (uint)iStyleIndex;
                                                    newcell.CellText = string.Empty;
                                                    cells[newpt] = newcell.Clone();
                                                }
                                                else
                                                {
                                                    // if the source cell is empty, then direct pasting
                                                    // means overwrite the existing cell, which is faster
                                                    // by just removing it.
                                                    slws.Cells.Remove(newpt);
                                                }
                                            }
                                            else
                                            {
                                                // else no source and no destination, so we check for row/column
                                                // with non-default styles.
                                                iStyleIndex = 0;
                                                if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
                                                if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];

                                                iStyleIndexNew = 0;
                                                if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                                if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                                if (iStyleIndex != 0 || iStyleIndexNew != 0)
                                                {
                                                    newcell = new SLCell();
                                                    newcell.StyleIndex = (uint)iStyleIndex;
                                                    newcell.CellText = string.Empty;
                                                    cells[newpt] = newcell.Clone();
                                                }
                                            }
                                        }
                                    }
                                    break;
                                case SLPasteTypeValues.Values:
                                    // this part is identical to the formula part, except
                                    // for assigning the cell formula part.

                                    if (slws.Cells.ContainsKey(pt))
                                    {
                                        origcell = slws.Cells[pt];
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            newcell = slws.Cells[newpt].Clone();
                                            newcell.CellFormula = null;
                                            newcell.CellText = origcell.CellText;
                                            newcell.fNumericValue = origcell.fNumericValue;
                                            newcell.DataType = origcell.DataType;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        else
                                        {
                                            newcell = new SLCell();
                                            newcell.CellFormula = null;
                                            newcell.CellText = origcell.CellText;
                                            newcell.fNumericValue = origcell.fNumericValue;
                                            newcell.DataType = origcell.DataType;

                                            iStyleIndexNew = 0;
                                            if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
                                            if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];

                                            if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
                                            cells[newpt] = newcell.Clone();
                                        }
                                    }
                                    else
                                    {
                                        if (slws.Cells.ContainsKey(newpt))
                                        {
                                            newcell = slws.Cells[newpt].Clone();
                                            newcell.CellFormula = null;
                                            newcell.CellText = string.Empty;
                                            newcell.DataType = CellValues.Number;
                                            cells[newpt] = newcell.Clone();
                                        }
                                        // no else because don't have to do anything
                                    }
                                    break;
                            }
                        }
                    }
                }

                int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex;
                int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex;

                for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i)
                {
                    for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j)
                    {
                        pt = new SLCellPoint(i, j);
                        if (slws.Cells.ContainsKey(pt))
                        {
                            // any cell within destination "paste" operation is taken out
                            slws.Cells.Remove(pt);
                        }
                    }
                }

                int iNumberOfRows = iEndRowIndex - iStartRowIndex + 1;
                if (AnchorRowIndex <= iStartRowIndex) iNumberOfRows = -iNumberOfRows;
                int iNumberOfColumns = iEndColumnIndex - iStartColumnIndex + 1;
                if (AnchorColumnIndex <= iStartColumnIndex) iNumberOfColumns = -iNumberOfColumns;
                foreach (SLCellPoint cellkey in cells.Keys)
                {
                    origcell = cells[cellkey];
                    if (PasteOption != SLPasteTypeValues.Transpose)
                    {
                        this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfRows, AnchorColumnIndex, iNumberOfColumns);
                    }
                    else
                    {
                        this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfColumns, AnchorColumnIndex, iNumberOfRows);
                    }
                    slws.Cells[cellkey] = origcell.Clone();
                }

                // TODO: tables!

                // cutting and pasting into a region with merged cells unmerges the existing merged cells
                // copying and pasting into a region with merged cells leaves existing merged cells alone.
                // Why does Excel do that? Don't know.
                // Will just standardise to leaving existing merged cells alone.
                List<SLMergeCell> mca = this.GetWorksheetMergeCells();
                foreach (SLMergeCell mc in mca)
                {
                    if (mc.StartRowIndex >= iStartRowIndex && mc.EndRowIndex <= iEndRowIndex
                        && mc.StartColumnIndex >= iStartColumnIndex && mc.EndColumnIndex <= iEndColumnIndex)
                    {
                        if (ToCut)
                        {
                            slws.MergeCells.Remove(mc);
                        }

                        if (PasteOption == SLPasteTypeValues.Transpose)
                        {
                            pt = new SLCellPoint(mc.StartRowIndex - iStartRowIndex, mc.StartColumnIndex - iStartColumnIndex);
                            iSwap = pt.RowIndex;
                            pt.RowIndex = pt.ColumnIndex;
                            pt.ColumnIndex = iSwap;
                            pt.RowIndex = pt.RowIndex + iStartRowIndex + rowdiff;
                            pt.ColumnIndex = pt.ColumnIndex + iStartColumnIndex + coldiff;

                            newpt = new SLCellPoint(mc.EndRowIndex - iStartRowIndex, mc.EndColumnIndex - iStartColumnIndex);
                            iSwap = newpt.RowIndex;
                            newpt.RowIndex = newpt.ColumnIndex;
                            newpt.ColumnIndex = iSwap;
                            newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
                            newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;

                            this.MergeWorksheetCells(pt.RowIndex, pt.ColumnIndex, newpt.RowIndex, newpt.ColumnIndex);
                        }
                        else
                        {
                            this.MergeWorksheetCells(mc.StartRowIndex + rowdiff, mc.StartColumnIndex + coldiff, mc.EndRowIndex + rowdiff, mc.EndColumnIndex + coldiff);
                        }
                    }
                }

                // TODO: conditional formatting and data validations?

                #region Hyperlinks
                if (slws.Hyperlinks.Count > 0)
                {
                    if (ToCut)
                    {
                        foreach (SLHyperlink hl in slws.Hyperlinks)
                        {
                            // if hyperlink is completely within copy range
                            if (iStartRowIndex <= hl.Reference.StartRowIndex
                                && hl.Reference.EndRowIndex <= iEndRowIndex
                                && iStartColumnIndex <= hl.Reference.StartColumnIndex
                                && hl.Reference.EndColumnIndex <= iEndColumnIndex)
                            {
                                hl.Reference = new SLCellPointRange(hl.Reference.StartRowIndex + rowdiff,
                                    hl.Reference.StartColumnIndex + coldiff,
                                    hl.Reference.EndRowIndex + rowdiff,
                                    hl.Reference.EndColumnIndex + coldiff);
                            }
                            // else don't change anything (Excel doesn't, so we don't).
                        }
                    }
                    else
                    {
                        // we only care if normal paste or transpose paste. Just like Excel.
                        if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose)
                        {
                            List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>();
                            SLHyperlink hlCopied;

                            // hyperlink ID, URL
                            Dictionary<string, string> hlurl = new Dictionary<string, string>();

                            if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
                            {
                                WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
                                foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships)
                                {
                                    if (hlrel.IsExternal)
                                    {
                                        hlurl[hlrel.Id] = hlrel.Uri.OriginalString;
                                    }
                                }
                            }

                            int iOverlapStartRowIndex = 1;
                            int iOverlapStartColumnIndex = 1;
                            int iOverlapEndRowIndex = 1;
                            int iOverlapEndColumnIndex = 1;
                            foreach (SLHyperlink hl in slws.Hyperlinks)
                            {
                                // this comes from the separating axis theorem.
                                // See merged cells for more details.
                                // In this case however, we're doing stuff when there's overlapping.
                                if (!(iEndRowIndex < hl.Reference.StartRowIndex
                                    || iStartRowIndex > hl.Reference.EndRowIndex
                                    || iEndColumnIndex < hl.Reference.StartColumnIndex
                                    || iStartColumnIndex > hl.Reference.EndColumnIndex))
                                {
                                    // get the overlapping region
                                    iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex);
                                    iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex);
                                    iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex);
                                    iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex);

                                    // offset to the correctly pasted region
                                    if (PasteOption == SLPasteTypeValues.Paste)
                                    {
                                        iOverlapStartRowIndex += rowdiff;
                                        iOverlapStartColumnIndex += coldiff;
                                        iOverlapEndRowIndex += rowdiff;
                                        iOverlapEndColumnIndex += coldiff;
                                    }
                                    else
                                    {
                                        // can only be transpose. See if check above.

                                        if (iOverlapEndRowIndex > SLConstants.ColumnLimit)
                                        {
                                            // probably won't happen. This means that after transpose,
                                            // the end row index will flip to exceed the column limit.
                                            // I don't feel like testing how Excel handles this, so
                                            // I'm going to just take it as normal paste.
                                            iOverlapStartRowIndex += rowdiff;
                                            iOverlapStartColumnIndex += coldiff;
                                            iOverlapEndRowIndex += rowdiff;
                                            iOverlapEndColumnIndex += coldiff;
                                        }
                                        else
                                        {
                                            iOverlapStartRowIndex -= iStartRowIndex;
                                            iOverlapStartColumnIndex -= iStartColumnIndex;
                                            iOverlapEndRowIndex -= iStartRowIndex;
                                            iOverlapEndColumnIndex -= iStartColumnIndex;

                                            iSwap = iOverlapStartRowIndex;
                                            iOverlapStartRowIndex = iOverlapStartColumnIndex;
                                            iOverlapStartColumnIndex = iSwap;

                                            iSwap = iOverlapEndRowIndex;
                                            iOverlapEndRowIndex = iOverlapEndColumnIndex;
                                            iOverlapEndColumnIndex = iSwap;

                                            iOverlapStartRowIndex += (iStartRowIndex + rowdiff);
                                            iOverlapStartColumnIndex += (iStartColumnIndex + coldiff);
                                            iOverlapEndRowIndex += (iStartRowIndex + rowdiff);
                                            iOverlapEndColumnIndex += (iStartColumnIndex + coldiff);
                                        }
                                    }

                                    hlCopied = new SLHyperlink();
                                    hlCopied = hl.Clone();
                                    hlCopied.IsNew = true;
                                    if (hlCopied.IsExternal)
                                    {
                                        if (hlurl.ContainsKey(hlCopied.Id))
                                        {
                                            hlCopied.HyperlinkUri = hlurl[hlCopied.Id];
                                            if (hlCopied.HyperlinkUri.StartsWith("."))
                                            {
                                                // assume this is a relative file path such as ../ or ./
                                                hlCopied.HyperlinkUriKind = UriKind.Relative;
                                            }
                                            else
                                            {
                                                hlCopied.HyperlinkUriKind = UriKind.Absolute;
                                            }
                                            hlCopied.Id = string.Empty;
                                        }
                                    }
                                    hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex);
                                    copiedhyperlinks.Add(hlCopied);
                                }
                            }

                            if (copiedhyperlinks.Count > 0)
                            {
                                slws.Hyperlinks.AddRange(copiedhyperlinks);
                            }
                        }
                    }
                }
                #endregion

                #region Calculation cells
                if (slwb.CalculationCells.Count > 0)
                {
                    List<int> listToDelete = new List<int>();
                    int iRowIndex = -1;
                    int iColumnIndex = -1;
                    for (i = 0; i < slwb.CalculationCells.Count; ++i)
                    {
                        if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
                        {
                            iRowIndex = slwb.CalculationCells[i].RowIndex;
                            iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
                            if (ToCut && iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex
                                    && iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex)
                            {
                                // just remove because recalculation of cell references is too complicated...
                                if (!listToDelete.Contains(i)) listToDelete.Add(i);
                            }

                            if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex
                                && iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex)
                            {
                                // existing calculation cell lies within destination "paste" operation
                                if (!listToDelete.Contains(i)) listToDelete.Add(i);
                            }
                        }
                    }

                    for (i = listToDelete.Count - 1; i >= 0; --i)
                    {
                        slwb.CalculationCells.RemoveAt(listToDelete[i]);
                    }
                }
                #endregion

                // defined names is hard to calculate...
                // need to check the row and column indices based on the cell references within.
            }

            return result;
        }
Example #9
0
        // 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);
            }

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

            var     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)SaveToStylesheet(style.ToHash());

                if (OverwriteExistingCell)
                {
                    // in case there's a formula
                    c.CellFormula = null;
                    c.DataType    = CellValues.SharedString;
                    c.CellText    = 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)SaveToStylesheet(style.ToHash());

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

            slws.Hyperlinks.Add(hl);

            return(true);
        }
Example #10
0
        /// <summary>
        /// Copy the style of one row to a range of rows.
        /// </summary>
        /// <param name="FromRowIndex">The row index of the row to be copied from.</param>
        /// <param name="ToStartRowIndex">The row index of the start row of the row range. This is typically the top row.</param>
        /// <param name="ToEndRowIndex">The row index of the end row of the row range. This is typically the bottom row.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool CopyRowStyle(int FromRowIndex, int ToStartRowIndex, int ToEndRowIndex)
        {
            int iStartRowIndex = 1, iEndRowIndex = 1;
            bool result = false;
            if (ToStartRowIndex < ToEndRowIndex)
            {
                iStartRowIndex = ToStartRowIndex;
                iEndRowIndex = ToEndRowIndex;
            }
            else
            {
                iStartRowIndex = ToEndRowIndex;
                iEndRowIndex = ToStartRowIndex;
            }

            if (FromRowIndex >= 1 && FromRowIndex <= SLConstants.RowLimit
                && iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
                && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit)
            {
                result = true;

                uint iStyleIndex = 0;
                if (slws.RowProperties.ContainsKey(FromRowIndex))
                {
                    iStyleIndex = slws.RowProperties[FromRowIndex].StyleIndex;
                }

                SLRowProperties rp;
                int i, j;
                for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                {
                    if (i != FromRowIndex)
                    {
                        if (iStyleIndex == 0)
                        {
                            if (slws.RowProperties.ContainsKey(i))
                            {
                                slws.RowProperties[i].StyleIndex = 0;
                            }
                        }
                        else
                        {
                            if (slws.RowProperties.ContainsKey(i))
                            {
                                slws.RowProperties[i].StyleIndex = iStyleIndex;
                            }
                            else
                            {
                                rp = new SLRowProperties(SimpleTheme.ThemeRowHeight);
                                rp.StyleIndex = iStyleIndex;
                                slws.RowProperties[i] = rp;
                            }

                            slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(true, i));
                        }
                    }
                }

                #region copying cell styles
                SLCell cell;
                SLCellPoint pt, destpt;
                for (j = 1; j <= SLConstants.ColumnLimit; ++j)
                {
                    pt = new SLCellPoint(FromRowIndex, j);
                    if (slws.Cells.ContainsKey(pt))
                    {
                        for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                        {
                            if (i != FromRowIndex)
                            {
                                destpt = new SLCellPoint(i, j);
                                if (slws.Cells.ContainsKey(destpt))
                                {
                                    slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex;
                                }
                                else
                                {
                                    cell = new SLCell();
                                    cell.CellText = string.Empty;
                                    cell.StyleIndex = slws.Cells[pt].StyleIndex;
                                    slws.Cells[destpt] = cell;
                                }
                            }
                        }
                    }
                    else
                    {
                        for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                        {
                            if (i != FromRowIndex)
                            {
                                destpt = new SLCellPoint(i, j);
                                if (slws.Cells.ContainsKey(destpt))
                                {
                                    slws.Cells[destpt].StyleIndex = iStyleIndex;
                                }
                                // no need else because the default style will take over
                            }
                        }
                    }
                }
                #endregion
            }

            return result;
        }
Example #11
0
        /// <summary>
        /// Set the style of a range of cells.
        /// </summary>
        /// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
        /// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
        /// <param name="Style">The style to set.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool SetCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, SLStyle Style)
        {
            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;
            }

            bool result = false;
            if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
                && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
                && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
                && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
            {
                int iStyleIndex = this.SaveToStylesheet(Style.ToHash());
                // default is 0, the default style
                if (iStyleIndex > 0)
                {
                    result = true;
                    SLCellPoint pt;
                    SLCell c;
                    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))
                            {
                                c = slws.Cells[pt];
                                c.StyleIndex = (uint)iStyleIndex;
                                slws.Cells[pt] = c;
                            }
                            else
                            {
                                c = new SLCell();
                                c.CellText = string.Empty;
                                c.StyleIndex = (uint)iStyleIndex;
                                slws.Cells[pt] = c;
                            }
                        }
                    }
                }
            }

            return result;
        }
        /// <summary>
        /// Sort data either by column or row.
        /// </summary>
        /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
        /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
        /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
        /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
        /// <param name="SortByColumn">True to sort by column. False to sort by row.</param>
        /// <param name="SortByIndex">The row or column index of the row or column to be sorted by, depending on <paramref name="SortByColumn"/></param>
        /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param>
        public void Sort(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool SortByColumn, int SortByIndex, bool SortAscending)
        {
            int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
            if (StartRowIndex < EndRowIndex)
            {
                iStartRowIndex = StartRowIndex;
                iEndRowIndex = EndRowIndex;
            }
            else
            {
                iStartRowIndex = EndRowIndex;
                iEndRowIndex = StartRowIndex;
            }

            if (StartColumnIndex < EndColumnIndex)
            {
                iStartColumnIndex = StartColumnIndex;
                iEndColumnIndex = EndColumnIndex;
            }
            else
            {
                iStartColumnIndex = EndColumnIndex;
                iEndColumnIndex = StartColumnIndex;
            }

            if (iStartRowIndex < 1) iStartRowIndex = 1;
            if (iStartColumnIndex < 1) iStartColumnIndex = 1;
            if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
            if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;

            // if the given index is out of the data range, then don't have to sort.
            if (SortByColumn)
            {
                if (SortByIndex < iStartColumnIndex || SortByIndex > iEndColumnIndex) return;
            }
            else
            {
                if (SortByIndex < iStartRowIndex || SortByIndex > iEndRowIndex) return;
            }

            Dictionary<SLCellPoint, SLCell> datacells = new Dictionary<SLCellPoint, SLCell>();
            SLCellPoint pt;
            int i, j;
            for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
            {
                for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                {
                    pt = new SLCellPoint(i, j);
                    if (slws.Cells.ContainsKey(pt))
                    {
                        datacells[pt] = slws.Cells[pt].Clone();
                        slws.Cells.Remove(pt);
                    }
                }
            }

            List<SLSortItem> listNumbers = new List<SLSortItem>();
            List<SLSortItem> listText = new List<SLSortItem>();
            List<SLSortItem> listBoolean = new List<SLSortItem>();
            List<SLSortItem> listEmpty = new List<SLSortItem>();

            bool bValue = false;
            double fValue = 0.0;
            string sText = string.Empty;
            SLRstType rst;
            int index = 0;
            int iStartIndex = -1;
            int iEndIndex = -1;

            if (SortByColumn)
            {
                iStartIndex = iStartRowIndex;
                iEndIndex = iEndRowIndex;
            }
            else
            {
                iStartIndex = iStartColumnIndex;
                iEndIndex = iEndColumnIndex;
            }

            for (i = iStartIndex; i <= iEndIndex; ++i)
            {
                if (SortByColumn) pt = new SLCellPoint(i, SortByIndex);
                else pt = new SLCellPoint(SortByIndex, i);

                if (datacells.ContainsKey(pt))
                {
                    if (datacells[pt].DataType == CellValues.Number)
                    {
                        if (datacells[pt].CellText != null)
                        {
                            if (double.TryParse(datacells[pt].CellText, out fValue))
                            {
                                listNumbers.Add(new SLSortItem() { Number = fValue, Index = i });
                            }
                            else
                            {
                                listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
                            }
                        }
                        else
                        {
                            listNumbers.Add(new SLSortItem() { Number = datacells[pt].NumericValue, Index = i });
                        }
                    }
                    else if (datacells[pt].DataType == CellValues.SharedString)
                    {
                        index = -1;

                        if (datacells[pt].CellText != null)
                        {
                            if (int.TryParse(datacells[pt].CellText, out index)
                                && index >= 0 && index < listSharedString.Count)
                            {
                                rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
                                rst.FromSharedStringItem(new SharedStringItem() { InnerXml = listSharedString[index] });
                                listText.Add(new SLSortItem() { Text = rst.ToPlainString(), Index = i });
                            }
                            else
                            {
                                listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
                            }
                        }
                        else
                        {
                            index = Convert.ToInt32(datacells[pt].NumericValue);
                            if (index >= 0 && index < listSharedString.Count)
                            {
                                rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
                                rst.FromSharedStringItem(new SharedStringItem() { InnerXml = listSharedString[index] });
                                listText.Add(new SLSortItem() { Text = rst.ToPlainString(), Index = i });
                            }
                            else
                            {
                                listText.Add(new SLSortItem() { Text = datacells[pt].NumericValue.ToString(CultureInfo.InvariantCulture), Index = i });
                            }
                        }
                    }
                    else if (datacells[pt].DataType == CellValues.Boolean)
                    {
                        if (datacells[pt].CellText != null)
                        {
                            if (double.TryParse(datacells[pt].CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue))
                            {
                                listBoolean.Add(new SLSortItem() { Number = fValue > 0.5 ? 1.0 : 0.0, Index = i });
                            }
                            else if (bool.TryParse(datacells[pt].CellText, out bValue))
                            {
                                listBoolean.Add(new SLSortItem() { Number = bValue ? 1.0 : 0.0, Index = i });
                            }
                            else
                            {
                                listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
                            }
                        }
                        else
                        {
                            listBoolean.Add(new SLSortItem() { Number = datacells[pt].NumericValue > 0.5 ? 1.0 : 0.0, Index = i });
                        }
                    }
                    else
                    {
                        listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
                    }
                }
                else
                {
                    listEmpty.Add(new SLSortItem() { Index = i });
                }
            }

            listNumbers.Sort(new SLSortItemNumberComparer());
            if (!SortAscending) listNumbers.Reverse();

            listText.Sort(new SLSortItemTextComparer());
            if (!SortAscending) listText.Reverse();

            listBoolean.Sort(new SLSortItemNumberComparer());
            if (!SortAscending) listBoolean.Reverse();

            Dictionary<int, int> ReverseIndex = new Dictionary<int,int>();
            if (SortAscending)
            {
                j = iStartIndex;
                for (i = 0; i < listNumbers.Count; ++i)
                {
                    ReverseIndex[listNumbers[i].Index] = j;
                    ++j;
                }

                for (i = 0; i < listText.Count; ++i)
                {
                    ReverseIndex[listText[i].Index] = j;
                    ++j;
                }

                for (i = 0; i < listBoolean.Count; ++i)
                {
                    ReverseIndex[listBoolean[i].Index] = j;
                    ++j;
                }

                for (i = 0; i < listEmpty.Count; ++i)
                {
                    ReverseIndex[listEmpty[i].Index] = j;
                    ++j;
                }
            }
            else
            {
                j = iStartIndex;
                for (i = 0; i < listBoolean.Count; ++i)
                {
                    ReverseIndex[listBoolean[i].Index] = j;
                    ++j;
                }

                for (i = 0; i < listText.Count; ++i)
                {
                    ReverseIndex[listText[i].Index] = j;
                    ++j;
                }

                for (i = 0; i < listNumbers.Count; ++i)
                {
                    ReverseIndex[listNumbers[i].Index] = j;
                    ++j;
                }

                for (i = 0; i < listEmpty.Count; ++i)
                {
                    ReverseIndex[listEmpty[i].Index] = j;
                    ++j;
                }
            }

            List<SLCellPoint> listCellKeys = datacells.Keys.ToList<SLCellPoint>();
            SLCellPoint newpt;
            for (i = 0; i < listCellKeys.Count; ++i)
            {
                pt = listCellKeys[i];
                if (SortByColumn)
                {
                    if (ReverseIndex.ContainsKey(pt.RowIndex))
                    {
                        newpt = new SLCellPoint(ReverseIndex[pt.RowIndex], pt.ColumnIndex);
                    }
                    else
                    {
                        // shouldn't happen, but just in case...
                        newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
                    }
                }
                else
                {
                    if (ReverseIndex.ContainsKey(pt.ColumnIndex))
                    {
                        newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]);
                    }
                    else
                    {
                        // shouldn't happen, but just in case...
                        newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
                    }
                }

                slws.Cells[newpt] = datacells[pt];
            }
        }
        /// <summary>
        /// Remove an existing hyperlink.
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        public void RemoveHyperlink(int RowIndex, int ColumnIndex)
        {
            if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return;
            if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return;

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

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

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

                            slws.Hyperlinks.RemoveAt(i);
                        }

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

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

            if (listdelete.Count > 0)
            {
                // remove hyperlink style
                SLCell c;
                SLCellPoint pt;
                foreach (SLCellPointRange ptrange in listdelete)
                {
                    for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i)
                    {
                        for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j)
                        {
                            pt = new SLCellPoint(i, j);
                            if (slws.Cells.ContainsKey(pt))
                            {
                                c = slws.Cells[pt];
                                c.StyleIndex = 0;
                                slws.Cells[pt] = c.Clone();
                            }
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Clear all cell content within specified rows and columns. If the top-left cell of a merged cell is within specified rows and columns, the merged cell content is also cleared.
        /// </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>True if content has been cleared. False otherwise. If there are no content within specified rows and columns, false is also returned.</returns>
        public bool ClearCellContent(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
        {
            int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
            bool result = false;
            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 (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
            if (iStartColumnIndex < 1) iStartColumnIndex = 1;
            if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;

            long iSize = (iEndRowIndex - iStartRowIndex + 1) * (iEndColumnIndex - iStartColumnIndex + 1);

            int iRowIndex = -1, iColumnIndex = -1;
            if (iSize <= (long)slws.Cells.Count)
            {
                SLCellPoint pt;
                for (iRowIndex = iStartRowIndex; iRowIndex <= iEndRowIndex; ++iRowIndex)
                {
                    for (iColumnIndex = iStartColumnIndex; iColumnIndex <= iEndColumnIndex; ++iColumnIndex)
                    {
                        pt = new SLCellPoint(iRowIndex, iColumnIndex);
                        if (slws.Cells.ContainsKey(pt))
                        {
                            this.ClearCellContentData(pt);
                            result = true;
                        }
                    }
                }
            }
            else
            {
                List<SLCellPoint> list = slws.Cells.Keys.ToList<SLCellPoint>();
                foreach (SLCellPoint pt in list)
                {
                    if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex && iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex)
                    {
                        this.ClearCellContentData(pt);
                        result = true;
                    }
                }
            }

            List<int> listToDelete = new List<int>();
            int i;
            for (i = 0; i < slwb.CalculationCells.Count; ++i)
            {
                if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
                {
                    iRowIndex = slwb.CalculationCells[i].RowIndex;
                    iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
                    if (iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex
                        && iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex)
                    {
                        if (!listToDelete.Contains(i)) listToDelete.Add(i);
                    }
                }
            }

            for (i = listToDelete.Count - 1; i >= 0; --i)
            {
                slwb.CalculationCells.RemoveAt(listToDelete[i]);
            }

            return result;
        }
        /// <summary>
        /// Insert a table into the currently selected worksheet.
        /// </summary>
        /// <param name="Table">An SLTable object with the properties already set.</param>
        /// <returns>True if the table is successfully inserted. False otherwise. If it failed, check if the given table overlaps any existing tables or merged cell range.</returns>
        public bool InsertTable(SLTable Table)
        {
            // This is the separating axis theorem. See merging cells for more details.
            // We're checking if the table collides with merged cells, the worksheet's autofilter range
            // and existing tables.
            // Technically, Excel unmerges cells when a table overlaps a merged cell range.
            // We're just going to fail that.

            bool result = true;
            int i, j;
            for (i = 0; i < slws.MergeCells.Count; ++i)
            {
                if (!(Table.EndRowIndex < slws.MergeCells[i].StartRowIndex
                    || Table.StartRowIndex > slws.MergeCells[i].EndRowIndex
                    || Table.EndColumnIndex < slws.MergeCells[i].StartColumnIndex
                    || Table.StartColumnIndex > slws.MergeCells[i].EndColumnIndex))
                {
                    result = false;
                    break;
                }
            }

            if (slws.HasAutoFilter)
            {
                if (!(Table.EndRowIndex < slws.AutoFilter.StartRowIndex
                    || Table.StartRowIndex > slws.AutoFilter.EndRowIndex
                    || Table.EndColumnIndex < slws.AutoFilter.StartColumnIndex
                    || Table.StartColumnIndex > slws.AutoFilter.EndColumnIndex))
                {
                    result = false;
                }
            }

            if (!result) return false;

            for (i = 0; i < slws.Tables.Count; ++i)
            {
                if (!(Table.EndRowIndex < slws.Tables[i].StartRowIndex
                    || Table.StartRowIndex > slws.Tables[i].EndRowIndex
                    || Table.EndColumnIndex < slws.Tables[i].StartColumnIndex
                    || Table.StartColumnIndex > slws.Tables[i].EndColumnIndex))
                {
                    result = false;
                    break;
                }
            }

            if (result)
            {
                // sorting first!
                // We'll do just one level deep sorting. Multiple level sorting is hard...
                if (Table.HasSortState && Table.SortState.SortConditions.Count > 0)
                {
                    bool bSortAscending = true;
                    if (Table.SortState.SortConditions[0].Descending != null)
                        bSortAscending = !Table.SortState.SortConditions[0].Descending.Value;

                    this.Sort(Table.SortState.StartRowIndex, Table.SortState.StartColumnIndex,
                        Table.SortState.EndRowIndex, Table.SortState.EndColumnIndex, true,
                        Table.SortState.SortConditions[0].StartColumnIndex, bSortAscending);
                }

                // filtering next! Because rows might be hidden

                int iStartRowIndex = -1;
                int iEndRowIndex = -1;
                if (Table.HeaderRowCount > 0) iStartRowIndex = Table.StartRowIndex + 1;
                else iStartRowIndex = Table.StartRowIndex;
                // not inclusive of the last totals row
                iEndRowIndex = Table.EndRowIndex - 1;

                SLTableColumn tc;
                SLCellPoint pt;
                List<SLCell> cells;
                SLCell c;
                string sResultText = string.Empty;
                SLCalculationCell cc;

                uint iStyleIndex;

                for (j = 0; j < Table.TableColumns.Count; ++j)
                {
                    tc = Table.TableColumns[j];
                    if (tc.TotalsRowLabel != null && tc.TotalsRowLabel.Length > 0)
                    {
                        c = new SLCell();
                        c.DataType = CellValues.SharedString;
                        c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(tc.TotalsRowLabel));
                        slws.Cells[new SLCellPoint(Table.EndRowIndex, Table.StartColumnIndex + j)] = c;
                    }
                    if (tc.HasTotalsRowFunction)
                    {
                        cells = new List<SLCell>();
                        for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
                        {
                            pt = new SLCellPoint(i, Table.StartColumnIndex + j);
                            if (slws.Cells.ContainsKey(pt))
                            {
                                cells.Add(slws.Cells[pt].Clone());
                            }
                        }

                        c = new SLCell();
                        c.CellFormula = new SLCellFormula();
                        c.CellFormula.FormulaText = string.Format("SUBTOTAL({0},[{1}])", this.GetFunctionNumber(tc.TotalsRowFunction), tc.Name);
                        if (!this.Calculate(tc.TotalsRowFunction, cells, out sResultText))
                        {
                            c.DataType = CellValues.Error;
                        }
                        c.CellText = sResultText;
                        pt = new SLCellPoint(Table.EndRowIndex, Table.StartColumnIndex + j);

                        iStyleIndex = 0;
                        if (slws.RowProperties.ContainsKey(pt.RowIndex)) iStyleIndex = slws.RowProperties[pt.RowIndex].StyleIndex;
                        if (iStyleIndex == 0 && slws.ColumnProperties.ContainsKey(pt.ColumnIndex)) iStyleIndex = slws.ColumnProperties[pt.ColumnIndex].StyleIndex;
                        if (iStyleIndex != 0) c.StyleIndex = (uint)iStyleIndex;

                        slws.Cells[pt] = c;

                        cc = new SLCalculationCell(SLTool.ToCellReference(Table.EndRowIndex, Table.StartColumnIndex + j));
                        cc.SheetId = (int)giSelectedWorksheetID;
                        slwb.AddCalculationCell(cc);
                    }
                }

                if (slwb.HasTableName(Table.DisplayName) || Table.DisplayName.Contains(" "))
                {
                    slwb.RefreshPossibleTableId();
                    Table.Id = slwb.PossibleTableId;
                    Table.sDisplayName = string.Format("Table{0}", Table.Id.ToString(CultureInfo.InvariantCulture));
                    Table.Name = Table.sDisplayName;
                }

                if (!slwb.TableIds.Contains(Table.Id)) slwb.TableIds.Add(Table.Id);

                if (!slwb.TableNames.Contains(Table.DisplayName)) slwb.TableNames.Add(Table.DisplayName);

                slws.Tables.Add(Table.Clone());
            }

            return result;
        }
Example #16
0
 private void ClearCellContentData(SLCellPoint pt)
 {
     SLCell c = slws.Cells[pt];
     c.CellFormula = null;
     c.DataType = CellValues.Number;
     c.NumericValue = 0;
     // if the cell still has attributes (say the style index), then update it
     // otherwise remove the cell
     if (c.StyleIndex != 0 || c.CellMetaIndex != 0 || c.ValueMetaIndex != 0 || c.ShowPhonetic != false)
     {
         slws.Cells[pt] = c.Clone();
     }
     else
     {
         slws.Cells.Remove(pt);
     }
 }
Example #17
0
        /// <summary>
        /// Clear all cell content within specified rows and columns. If the top-left cell of a merged cell is within specified rows and columns, the merged cell content is also cleared.
        /// </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>True if content has been cleared. False otherwise. If there are no content within specified rows and columns, false is also returned.</returns>
        public bool ClearCellContent(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
        {
            int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
            bool result = false;
            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 (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
            if (iStartColumnIndex < 1) iStartColumnIndex = 1;
            if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;

            long iSize = (iEndRowIndex - iStartRowIndex + 1) * (iEndColumnIndex - iStartColumnIndex + 1);

            int iRowIndex = -1, iColumnIndex = -1;
            if (iSize <= (long)slws.Cells.Count)
            {
                SLCellPoint pt;
                for (iRowIndex = iStartRowIndex; iRowIndex <= iEndRowIndex; ++iRowIndex)
                {
                    for (iColumnIndex = iStartColumnIndex; iColumnIndex <= iEndColumnIndex; ++iColumnIndex)
                    {
                        pt = new SLCellPoint(iRowIndex, iColumnIndex);
                        if (slws.Cells.ContainsKey(pt))
                        {
                            this.ClearCellContentData(pt);
                            result = true;
                        }
                    }
                }
            }
            else
            {
                foreach (SLCellPoint pt in slws.Cells.Keys)
                {
                    if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex && iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex)
                    {
                        this.ClearCellContentData(pt);
                        result = true;
                    }
                }
            }

            return result;
        }
Example #18
0
        /// <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;
        }
Example #19
0
        /// <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;
        }
        /// <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;
        }
Example #21
0
        /// <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;
                }
            }

            var         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);
                    }
                }
            }

            var listNumbers = new List <SLSortItem>();
            var listText    = new List <SLSortItem>();
            var listBoolean = new List <SLSortItem>();
            var listEmpty   = new List <SLSortItem>();

            var       bValue = false;
            var       fValue = 0.0;
            var       sText  = string.Empty;
            SLRstType rst;
            var       index       = 0;
            var       iStartIndex = -1;
            var       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 <Color>(), new List <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 <Color>(), new List <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();
            }

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

            var         listCellKeys = datacells.Keys.ToList();
            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
                    {
                        newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
                    }
                }
                else
                {
                    if (ReverseIndex.ContainsKey(pt.ColumnIndex))
                    {
                        newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]);
                    }
                    else
                    {
                        newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
                    }
                }

                slws.Cells[newpt] = datacells[pt];
            }
        }
Example #22
0
        private bool SetCellValueNumberFinal(int RowIndex, int ColumnIndex, bool IsNumeric, double NumericValue, string NumberData)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return false;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell c;
            if (slws.Cells.ContainsKey(pt))
            {
                c = slws.Cells[pt];
            }
            else
            {
                c = new SLCell();
                uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
                if (iStyleIndex != 0)
                {
                    c.StyleIndex = iStyleIndex;
                }
            }
            c.DataType = CellValues.Number;
            if (IsNumeric) c.NumericValue = NumericValue;
            else c.CellText = NumberData;
            slws.Cells[pt] = c;

            return true;
        }
        // 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;
        }
Example #24
0
        /// <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. Try the SLRstType class for easy InlineString generation.</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, InlineString Data)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return false;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell c;
            if (slws.Cells.ContainsKey(pt))
            {
                c = slws.Cells[pt];
            }
            else
            {
                c = new SLCell();
                uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
                if (iStyleIndex != 0)
                {
                    c.StyleIndex = iStyleIndex;
                }
            }
            c.DataType = CellValues.SharedString;
            c.NumericValue = this.DirectSaveToSharedStringTable(Data);
            slws.Cells[pt] = c;

            return true;
        }
Example #25
0
        /// <summary>
        /// Copy the style of one column to a range of columns.
        /// </summary>
        /// <param name="FromColumnIndex">The column index of the column to be copied from.</param>
        /// <param name="ToStartColumnIndex">The column index of the start column of the column range. This is typically the left-most column.</param>
        /// <param name="ToEndColumnIndex">The column index of the end column of the column range. This is typically the right-most column.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool CopyColumnStyle(int FromColumnIndex, int ToStartColumnIndex, int ToEndColumnIndex)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            bool result = false;

            if (ToStartColumnIndex < ToEndColumnIndex)
            {
                iStartColumnIndex = ToStartColumnIndex;
                iEndColumnIndex = ToEndColumnIndex;
            }
            else
            {
                iStartColumnIndex = ToEndColumnIndex;
                iEndColumnIndex = ToStartColumnIndex;
            }

            if (FromColumnIndex >= 1 && FromColumnIndex <= SLConstants.ColumnLimit
                && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
                && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
            {
                result = true;

                uint iStyleIndex = 0;
                if (slws.ColumnProperties.ContainsKey(FromColumnIndex))
                {
                    iStyleIndex = slws.ColumnProperties[FromColumnIndex].StyleIndex;
                }

                SLColumnProperties cp;
                int i, j;
                for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
                {
                    if (i != FromColumnIndex)
                    {
                        if (iStyleIndex == 0)
                        {
                            if (slws.ColumnProperties.ContainsKey(i))
                            {
                                slws.ColumnProperties[i].StyleIndex = 0;
                            }
                        }
                        else
                        {
                            if (slws.ColumnProperties.ContainsKey(i))
                            {
                                slws.ColumnProperties[i].StyleIndex = iStyleIndex;
                            }
                            else
                            {
                                cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                                cp.StyleIndex = iStyleIndex;
                                slws.ColumnProperties[i] = cp;
                            }

                            slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i));
                        }
                    }
                }

                #region copying cell styles
                SLCell cell;
                SLCellPoint pt, destpt;
                for (i = 1; i <= SLConstants.RowLimit; ++i)
                {
                    pt = new SLCellPoint(i, FromColumnIndex);
                    if (slws.Cells.ContainsKey(pt))
                    {
                        if (slws.Cells[pt].StyleIndex > 0)
                        {
                            for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                            {
                                if (j != FromColumnIndex)
                                {
                                    destpt = new SLCellPoint(i, j);
                                    if (slws.Cells.ContainsKey(destpt))
                                    {
                                        slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex;
                                    }
                                    else
                                    {
                                        cell = new SLCell();
                                        cell.CellText = string.Empty;
                                        cell.StyleIndex = slws.Cells[pt].StyleIndex;
                                        slws.Cells[destpt] = cell;
                                    }
                                }
                            }
                        }
                    }
                    else
                    {
                        for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                        {
                            if (j != FromColumnIndex)
                            {
                                destpt = new SLCellPoint(i, j);
                                if (slws.Cells.ContainsKey(destpt))
                                {
                                    slws.Cells[destpt].StyleIndex = iStyleIndex;
                                }
                                // no need else because the default style will take over
                            }
                        }
                    }
                }
                #endregion
            }

            return result;
        }
Example #26
0
        /// <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.</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, string Data)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return false;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell c;
            if (slws.Cells.ContainsKey(pt))
            {
                c = slws.Cells[pt];
            }
            else
            {
                // if there's no existing cell, then we don't have to assign
                // a new cell when the data string is empty
                if (Data == null || Data.Length == 0) return true;

                c = new SLCell();
                uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
                if (iStyleIndex != 0)
                {
                    c.StyleIndex = iStyleIndex;
                }
            }

            if (Data == null || Data.Length == 0)
            {
                c.DataType = CellValues.Number;
                c.CellText = string.Empty;
                slws.Cells[pt] = c;
            }
            else if (Data.StartsWith("="))
            {
                // in case it's just one equal sign
                if (Data.Equals("=", StringComparison.InvariantCultureIgnoreCase))
                {
                    c.DataType = CellValues.SharedString;
                    c.NumericValue = this.DirectSaveToSharedStringTable("=");
                    slws.Cells[pt] = c;
                }
                else
                {
                    // For simplicity, we're gonna assume that if it starts with an equal sign, it's a formula.

                    // TODO Formula calculation engine
                    c.DataType = CellValues.Number;
                    //c.Formula = new CellFormula(slxe.Write(Data.Substring(1)));
                    c.CellFormula = new SLCellFormula();
                    c.CellFormula.FormulaText = SLTool.XmlWrite(Data.Substring(1));
                    c.CellText = string.Empty;
                    slws.Cells[pt] = c;
                }
            }
            else if (Data.StartsWith("'"))
            {
                c.DataType = CellValues.SharedString;
                c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(Data.Substring(1)));
                slws.Cells[pt] = c;
            }
            else
            {
                c.DataType = CellValues.SharedString;
                c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(Data));
                slws.Cells[pt] = c;
            }

            return true;
        }
Example #27
0
        /// <summary>
        /// Remove the style from a range of cells.
        /// </summary>
        /// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
        /// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
        public void RemoveCellStyle(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;
            }

            SLCellPoint pt;
            SLCell c;
            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))
                    {
                        c = slws.Cells[pt];
                        c.StyleIndex = 0;
                        slws.Cells[pt] = c;
                    }
                }
            }
        }
Example #28
0
        /// <summary>
        /// Get the cell value as a boolean. If the cell value wasn't originally a boolean value, the return value is undetermined (but is by default false).
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        /// <returns>A boolean cell value.</returns>
        public bool GetCellValueAsBoolean(int RowIndex, int ColumnIndex)
        {
            bool result = false;

            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.Boolean)
                    {
                        double fValue = 0;
                        if (c.CellText != null)
                        {
                            if (double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue))
                            {
                                if (fValue > 0.5) result = true;
                                else result = false;
                            }
                            else
                            {
                                bool.TryParse(c.CellText, out result);
                            }
                        }
                        else
                        {
                            if (c.NumericValue > 0.5) result = true;
                            else result = false;
                        }
                    }
                }
            }

            return result;
        }
Example #29
0
        /// <summary>
        /// Copy the style of one cell to a range of cells.
        /// </summary>
        /// <param name="FromRowIndex">The row index of the cell to be copied from.</param>
        /// <param name="FromColumnIndex">The column index of the cell to be copied from.</param>
        /// <param name="ToStartRowIndex">The row index of the starting cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="ToStartColumnIndex">The column index of the starting cell of the cell range. This is typically the top-left cell.</param>
        /// <param name="ToEndRowIndex">The row index of the ending cell of the cell range. This is typically the bottom-right cell.</param>
        /// <param name="ToEndColumnIndex">The column index of the ending cell of the cell range. This is typically the bottom-right cell.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool CopyCellStyle(int FromRowIndex, int FromColumnIndex, int ToStartRowIndex, int ToStartColumnIndex, int ToEndRowIndex, int ToEndColumnIndex)
        {
            int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
            bool result = false;
            if (ToStartRowIndex < ToEndRowIndex)
            {
                iStartRowIndex = ToStartRowIndex;
                iEndRowIndex = ToEndRowIndex;
            }
            else
            {
                iStartRowIndex = ToEndRowIndex;
                iEndRowIndex = ToStartRowIndex;
            }

            if (ToStartColumnIndex < ToEndColumnIndex)
            {
                iStartColumnIndex = ToStartColumnIndex;
                iEndColumnIndex = ToEndColumnIndex;
            }
            else
            {
                iStartColumnIndex = ToEndColumnIndex;
                iEndColumnIndex = ToStartColumnIndex;
            }

            if (SLTool.CheckRowColumnIndexLimit(FromRowIndex, FromColumnIndex)
                && SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex)
                && SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex))
            {
                result = true;

                uint iStyleIndex = 0;
                SLCellPoint pt = new SLCellPoint(FromRowIndex, FromColumnIndex);
                if (slws.Cells.ContainsKey(pt))
                {
                    iStyleIndex = slws.Cells[pt].StyleIndex;
                }

                SLCell c;
                // we'll just overwrite any existing styles, instead of merging
                // like when we're copying row/column styles.
                for (int i = iStartRowIndex; i <= iEndRowIndex; ++i)
                {
                    for (int j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                    {
                        if (i != FromRowIndex && j != FromColumnIndex)
                        {
                            pt = new SLCellPoint(i, j);
                            // so default style
                            if (iStyleIndex == 0)
                            {
                                if (slws.Cells.ContainsKey(pt))
                                {
                                    slws.Cells[pt].StyleIndex = 0;
                                }
                            }
                            else
                            {
                                if (slws.Cells.ContainsKey(pt))
                                {
                                    slws.Cells[pt].StyleIndex = iStyleIndex;
                                }
                                else
                                {
                                    c = new SLCell();
                                    c.CellText = string.Empty;
                                    c.StyleIndex = iStyleIndex;
                                    slws.Cells[pt] = c;
                                }
                            }
                        }
                    }
                }
            }

            return result;
        }
Example #30
0
        /// <summary>
        /// Get the cell value as a double precision floating point number. If the cell value wasn't originally a floating point number, the return value is undetermined (but is by default 0).
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        /// <returns>A double precision floating point number cell value.</returns>
        public double GetCellValueAsDouble(int RowIndex, int ColumnIndex)
        {
            double result = 0.0;

            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)
                        {
                            double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out result);
                        }
                        else
                        {
                            result = c.NumericValue;
                        }
                    }
                }
            }

            return result;
        }
Example #31
0
        /// <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...

            var 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
                    {
                        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 (var ptrange in listdelete)
                {
                    for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i)
                    {
                        for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j)
                        {
                            pt = new SLCellPoint(i, j);
                            if (slws.Cells.ContainsKey(pt))
                            {
                                c              = slws.Cells[pt];
                                c.StyleIndex   = 0;
                                slws.Cells[pt] = c.Clone();
                            }
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 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;
        }
        private List<SLC.SLDataSeries> FillChartDataSeries(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool RowsAsDataSeries, bool ShowHiddenData)
        {
            List<SLC.SLDataSeries> series = new List<SLC.SLDataSeries>();
            int i, j;
            SLCell c;
            SLCellPoint pt;
            Dictionary<int, bool> HiddenRows = new Dictionary<int, bool>();
            Dictionary<int, bool> HiddenColumns = new Dictionary<int, bool>();
            Dictionary<SLCellPoint, SLCell> cellstore = new Dictionary<SLCellPoint, SLCell>();

            #region GetCells
            for (i = StartRowIndex; i <= EndRowIndex; ++i)
            {
                HiddenRows[i] = false;
            }
            for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
            {
                HiddenColumns[j] = false;
            }

            bool bFound = false;
            string sWorksheetRelID = string.Empty;
            if (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
            {
                bFound = false;
            }
            else
            {
                bFound = false;
                foreach (SLSheet sheet in slwb.Sheets)
                {
                    if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase))
                    {
                        bFound = true;
                        sWorksheetRelID = sheet.Id;
                        break;
                    }
                }
            }

            if (bFound)
            {
                Dictionary<string, SLCellPoint> cellref = new Dictionary<string, SLCellPoint>();
                for (i = StartRowIndex; i <= EndRowIndex; ++i)
                {
                    for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
                    {
                        pt = new SLCellPoint(i, j);
                        cellref[SLTool.ToCellReference(i, j)] = pt;
                    }
                }

                WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sWorksheetRelID);
                Row row;
                Column column;
                Cell cell;
                if (!ShowHiddenData)
                {
                    // running it twice because Row contains Cell classes and it's easier this way
                    using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
                    {
                        while (oxr.Read())
                        {
                            if (oxr.ElementType == typeof(Row))
                            {
                                row = (Row)oxr.LoadCurrentElement();
                                if (row.RowIndex != null)
                                {
                                    foreach (var rowindex in HiddenRows.Keys)
                                    {
                                        if (row.RowIndex.Value == rowindex && row.Hidden != null && row.Hidden.Value)
                                        {
                                            HiddenRows[rowindex] = true;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(Column))
                        {
                            if (!ShowHiddenData)
                            {
                                column = (Column)oxr.LoadCurrentElement();
                                foreach (var colindex in HiddenColumns.Keys)
                                {
                                    if (column.Min <= colindex && colindex <= column.Max && column.Hidden != null && column.Hidden.Value)
                                    {
                                        HiddenColumns[colindex] = true;
                                    }
                                }
                            }
                        }
                        else if (oxr.ElementType == typeof(Cell))
                        {
                            cell = (Cell)oxr.LoadCurrentElement();
                            if (cell.CellReference != null)
                            {
                                if (cellref.ContainsKey(cell.CellReference.Value))
                                {
                                    c = new SLCell();
                                    c.FromCell(cell);
                                    cellstore[cellref[cell.CellReference.Value]] = c.Clone();
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                SLRowProperties rp;
                SLColumnProperties cp;

                if (!ShowHiddenData)
                {
                    for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
                    {
                        if (slws.ColumnProperties.ContainsKey(j))
                        {
                            cp = slws.ColumnProperties[j];
                            if (cp.Hidden) HiddenColumns[j] = true;
                        }
                    }
                }

                for (i = StartRowIndex; i <= EndRowIndex; ++i)
                {
                    if (!ShowHiddenData && slws.RowProperties.ContainsKey(i))
                    {
                        rp = slws.RowProperties[i];
                        if (rp.Hidden) HiddenRows[i] = true;
                    }

                    for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
                    {
                        pt = new SLCellPoint(i, j);
                        if (slws.Cells.ContainsKey(pt))
                        {
                            cellstore[pt] = slws.Cells[pt].Clone();
                        }
                    }
                }
            }

            #endregion

            int index = 0;
            int index2 = 0;
            string sCellValue;
            string sFormatCode;
            SLC.SLDataSeries ser;
            SLC.SLStringReference sr;
            SLC.SLNumberReference nr;
            SLStyle style;

            bool bIsStringReference = true;
            // we're going to assume that the format code applies to all in the "category" cells.
            string sAxisFormatCode = string.Empty;

            SLC.SLAxisDataSourceType cat = new SLC.SLAxisDataSourceType();
            if (RowsAsDataSeries)
            {
                bIsStringReference = true;
                sAxisFormatCode = SLConstants.NumberFormatGeneral;
                pt = new SLCellPoint(StartRowIndex, StartColumnIndex + 1);
                if (cellstore.ContainsKey(pt))
                {
                    // dates are also numbers, so we lump it together
                    if (cellstore[pt].DataType == CellValues.Number)
                    {
                        bIsStringReference = false;
                        style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
                        style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]);
                        this.TranslateStylesToStyleIds(ref style);
                        sAxisFormatCode = style.FormatCode;
                    }
                }

                sr = new SLC.SLStringReference();
                nr = new SLC.SLNumberReference();
                if (bIsStringReference)
                {
                    cat.UseStringReference = true;
                    sr = new SLC.SLStringReference();
                    sr.WorksheetName = WorksheetName;
                    sr.StartRowIndex = StartRowIndex;
                    sr.StartColumnIndex = StartColumnIndex + 1;
                    sr.EndRowIndex = StartRowIndex;
                    sr.EndColumnIndex = EndColumnIndex;
                    sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex);
                }
                else
                {
                    cat.UseNumberReference = true;
                    nr.WorksheetName = WorksheetName;
                    nr.StartRowIndex = StartRowIndex;
                    nr.StartColumnIndex = StartColumnIndex + 1;
                    nr.EndRowIndex = StartRowIndex;
                    nr.EndColumnIndex = EndColumnIndex;
                    nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex);
                    nr.NumberingCache.FormatCode = sAxisFormatCode;
                }

                index2 = 0;
                // if the header row is hidden, I don't know what to do...
                for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
                {
                    if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
                    {
                        pt = new SLCellPoint(StartRowIndex, j);
                        sCellValue = string.Empty;
                        if (cellstore.ContainsKey(pt))
                        {
                            c = cellstore[pt];
                            sCellValue = this.GetCellTrueValue(c);

                            if (bIsStringReference)
                            {
                                sr.Points.Add(new SLC.SLStringPoint()
                                {
                                    Index = (uint)index2,
                                    NumericValue = sCellValue
                                });
                            }
                            else
                            {
                                nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
                                {
                                    Index = (uint)index2,
                                    NumericValue = sCellValue
                                });
                            }
                        }
                        ++index2;
                    }
                }

                if (bIsStringReference)
                {
                    sr.PointCount = (uint)index2;
                    cat.StringReference = sr;
                }
                else
                {
                    nr.NumberingCache.PointCount = (uint)index2;
                    cat.NumberReference = nr;
                }

                index = 0;
                for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
                {
                    if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
                    {
                        ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors);
                        ser.Index = (uint)index;
                        ser.Order = (uint)index;
                        ser.IsStringReference = true;

                        sr = new SLC.SLStringReference();
                        pt = new SLCellPoint(i, StartColumnIndex);
                        sCellValue = string.Empty;
                        if (cellstore.ContainsKey(pt))
                        {
                            c = cellstore[pt];
                            sCellValue = this.GetCellTrueValue(c);
                        }
                        sr.WorksheetName = WorksheetName;
                        sr.StartRowIndex = i;
                        sr.StartColumnIndex = StartColumnIndex;
                        sr.EndRowIndex = i;
                        sr.EndColumnIndex = StartColumnIndex;
                        sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex);
                        sr.PointCount = 1;
                        sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue });
                        ser.StringReference = sr;

                        ser.AxisData = cat.Clone();

                        ser.NumberData.UseNumberReference = true;

                        nr = new SLC.SLNumberReference();
                        nr.WorksheetName = WorksheetName;
                        nr.StartRowIndex = i;
                        nr.StartColumnIndex = StartColumnIndex + 1;
                        nr.EndRowIndex = i;
                        nr.EndColumnIndex = EndColumnIndex;
                        nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex + 1, i, EndColumnIndex);
                        nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral;
                        index2 = 0;
                        for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
                        {
                            if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
                            {
                                pt = new SLCellPoint(i, j);
                                sCellValue = string.Empty;
                                sFormatCode = string.Empty;
                                if (cellstore.ContainsKey(pt))
                                {
                                    c = cellstore[pt];
                                    sCellValue = this.GetCellTrueValue(c);

                                    style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
                                    style.FromHash(listStyle[(int)c.StyleIndex]);
                                    this.TranslateStylesToStyleIds(ref style);
                                    if (style.HasNumberingFormat) sFormatCode = style.FormatCode;

                                    nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
                                    {
                                        FormatCode = sFormatCode,
                                        Index = (uint)index2,
                                        NumericValue = sCellValue
                                    });
                                }
                                ++index2;
                            }
                        }
                        nr.NumberingCache.PointCount = (uint)index2;
                        ser.NumberData.NumberReference = nr;

                        series.Add(ser);
                        ++index;
                    }
                }

                // end of rows as data series
            }
            else
            {
                bIsStringReference = true;
                sAxisFormatCode = SLConstants.NumberFormatGeneral;
                pt = new SLCellPoint(StartRowIndex + 1, StartColumnIndex);
                if (cellstore.ContainsKey(pt))
                {
                    // dates are also numbers, so we lump it together
                    if (cellstore[pt].DataType == CellValues.Number)
                    {
                        bIsStringReference = false;
                        style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
                        style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]);
                        this.TranslateStylesToStyleIds(ref style);
                        sAxisFormatCode = style.FormatCode;
                    }
                }

                sr = new SLC.SLStringReference();
                nr = new SLC.SLNumberReference();
                if (bIsStringReference)
                {
                    cat.UseStringReference = true;
                    sr.WorksheetName = WorksheetName;
                    sr.StartRowIndex = StartRowIndex + 1;
                    sr.StartColumnIndex = StartColumnIndex;
                    sr.EndRowIndex = EndRowIndex;
                    sr.EndColumnIndex = StartColumnIndex;
                    sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex);
                }
                else
                {
                    cat.UseNumberReference = true;
                    nr.WorksheetName = WorksheetName;
                    nr.StartRowIndex = StartRowIndex + 1;
                    nr.StartColumnIndex = StartColumnIndex;
                    nr.EndRowIndex = EndRowIndex;
                    nr.EndColumnIndex = StartColumnIndex;
                    nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex);
                    nr.NumberingCache.FormatCode = sAxisFormatCode;
                }

                index2 = 0;
                // if the header column is hidden, I don't know what to do...
                for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
                {
                    if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
                    {
                        pt = new SLCellPoint(i, StartColumnIndex);
                        sCellValue = string.Empty;
                        if (cellstore.ContainsKey(pt))
                        {
                            c = cellstore[pt];
                            sCellValue = this.GetCellTrueValue(c);

                            if (bIsStringReference)
                            {
                                sr.Points.Add(new SLC.SLStringPoint()
                                {
                                    Index = (uint)index2,
                                    NumericValue = sCellValue
                                });
                            }
                            else
                            {
                                nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
                                {
                                    Index = (uint)index2,
                                    NumericValue = sCellValue
                                });
                            }
                        }
                        ++index2;
                    }
                }

                if (bIsStringReference)
                {
                    sr.PointCount = (uint)index2;
                    cat.StringReference = sr;
                }
                else
                {
                    nr.NumberingCache.PointCount = (uint)index2;
                    cat.NumberReference = nr;
                }

                index = 0;
                for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
                {
                    if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
                    {
                        ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors);
                        ser.Index = (uint)index;
                        ser.Order = (uint)index;
                        ser.IsStringReference = true;

                        sr = new SLC.SLStringReference();
                        pt = new SLCellPoint(StartRowIndex, j);
                        sCellValue = string.Empty;
                        if (cellstore.ContainsKey(pt))
                        {
                            c = cellstore[pt];
                            sCellValue = this.GetCellTrueValue(c);
                        }
                        sr.WorksheetName = WorksheetName;
                        sr.StartRowIndex = StartRowIndex;
                        sr.StartColumnIndex = j;
                        sr.EndRowIndex = StartRowIndex;
                        sr.EndColumnIndex = j;
                        sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, j);
                        sr.PointCount = 1;
                        sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue });
                        ser.StringReference = sr;

                        ser.AxisData = cat.Clone();

                        ser.NumberData.UseNumberReference = true;

                        nr = new SLC.SLNumberReference();
                        nr.WorksheetName = WorksheetName;
                        nr.StartRowIndex = StartRowIndex + 1;
                        nr.StartColumnIndex = j;
                        nr.EndRowIndex = EndRowIndex;
                        nr.EndColumnIndex = j;
                        nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, j, EndRowIndex, j);
                        nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral;
                        index2 = 0;
                        for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
                        {
                            if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
                            {
                                pt = new SLCellPoint(i, j);
                                sCellValue = string.Empty;
                                sFormatCode = string.Empty;
                                if (cellstore.ContainsKey(pt))
                                {
                                    c = cellstore[pt];
                                    sCellValue = this.GetCellTrueValue(c);

                                    style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
                                    style.FromHash(listStyle[(int)c.StyleIndex]);
                                    this.TranslateStylesToStyleIds(ref style);
                                    if (style.HasNumberingFormat) sFormatCode = style.FormatCode;

                                    nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
                                    {
                                        FormatCode = sFormatCode,
                                        Index = (uint)index2,
                                        NumericValue = sCellValue
                                    });
                                }
                                ++index2;
                            }
                        }
                        nr.NumberingCache.PointCount = (uint)index2;
                        ser.NumberData.NumberReference = nr;

                        series.Add(ser);
                        ++index;
                    }
                }

                // end of columns as data series
            }

            return series;
        }
        /// <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.</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, bool Data)
        {
            if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
            {
                return false;
            }

            SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
            SLCell c;
            if (slws.Cells.ContainsKey(pt))
            {
                c = slws.Cells[pt];
            }
            else
            {
                c = new SLCell();
                if (slws.RowProperties.ContainsKey(RowIndex))
                {
                    c.StyleIndex = slws.RowProperties[RowIndex].StyleIndex;
                }
                else if (slws.ColumnProperties.ContainsKey(ColumnIndex))
                {
                    c.StyleIndex = slws.ColumnProperties[ColumnIndex].StyleIndex;
                }
            }
            c.DataType = CellValues.Boolean;
            c.NumericValue = Data ? 1 : 0;
            slws.Cells[pt] = c;

            return true;
        }
Example #35
-1
        /// <summary>
        /// Get the cell's style. The default style is returned if cell doesn't have an existing style, or if the row or column indices are invalid.
        /// </summary>
        /// <param name="RowIndex">The row index.</param>
        /// <param name="ColumnIndex">The column index.</param>
        /// <returns>The cell's style.</returns>
        public SLStyle GetCellStyle(int RowIndex, int ColumnIndex)
        {
            bool bFound = false;
            SLStyle style = new SLStyle(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];
                    bFound = true;
                    style.FromHash(listStyle[(int)c.StyleIndex]);
                }
            }

            if (!bFound)
            {
                style.FromHash(listStyle[0]);
            }

            return style;
        }