Ejemplo n.º 1
0
        internal void FromConditionalFormatting(X14.ConditionalFormatting cf)
        {
            this.SetAllNull();

            if (cf.Pivot != null)
            {
                this.Pivot = cf.Pivot.Value;
            }

            using (OpenXmlReader oxr = OpenXmlReader.Create(cf))
            {
                while (oxr.Read())
                {
                    SLConditionalFormattingRule2010 cfr;
                    if (oxr.ElementType == typeof(X14.ConditionalFormattingRule))
                    {
                        cfr = new SLConditionalFormattingRule2010();
                        cfr.FromConditionalFormattingRule((X14.ConditionalFormattingRule)oxr.LoadCurrentElement());
                        this.Rules.Add(cfr);
                    }
                    else if (oxr.ElementType == typeof(Excel.ReferenceSequence))
                    {
                        Excel.ReferenceSequence refseq = (Excel.ReferenceSequence)oxr.LoadCurrentElement();
                        this.ReferenceSequence = SLTool.TranslateRefSeqToCellPointRange(refseq);
                    }
                }
            }
        }
Ejemplo n.º 2
0
        internal void FromCalculationCell(CalculationCell cc)
        {
            this.SetAllNull();

            int iRowIndex    = -1;
            int iColumnIndex = -1;

            if (SLTool.FormatCellReferenceToRowColumnIndex(cc.CellReference.Value, out iRowIndex, out iColumnIndex))
            {
                this.RowIndex    = iRowIndex;
                this.ColumnIndex = iColumnIndex;
            }


            this.SheetId = cc.SheetId ?? 0;
            if (cc.InChildChain != null)
            {
                this.InChildChain = cc.InChildChain.Value;
            }
            if (cc.NewLevel != null)
            {
                this.NewLevel = cc.NewLevel.Value;
            }
            if (cc.NewThread != null)
            {
                this.NewThread = cc.NewThread.Value;
            }
            if (cc.Array != null)
            {
                this.Array = cc.Array.Value;
            }
        }
Ejemplo n.º 3
0
        internal X14.ConditionalFormatting ToConditionalFormatting()
        {
            X14.ConditionalFormatting cf = new X14.ConditionalFormatting();
            // otherwise xm:f and xm:seqref becomes xne:f and xne:seqref
            cf.AddNamespaceDeclaration("xm", SLConstants.NamespaceXm);
            // how come sparklines don't need explicit namespace declarations?

            if (this.Pivot)
            {
                cf.Pivot = this.Pivot;
            }

            int i;

            for (i = 0; i < this.Rules.Count; ++i)
            {
                cf.Append(this.Rules[i].ToConditionalFormattingRule());
            }

            if (this.ReferenceSequence.Count > 0)
            {
                cf.Append(new Excel.ReferenceSequence(SLTool.TranslateCellPointRangeToRefSeq(this.ReferenceSequence)));
            }

            return(cf);
        }
        internal AutoFilter ToAutoFilter()
        {
            AutoFilter af = new AutoFilter();

            if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
            {
                af.Reference = SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex);
            }
            else
            {
                af.Reference = string.Format("{0}:{1}",
                                             SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex),
                                             SLTool.ToCellReference(this.EndRowIndex, this.EndColumnIndex));
            }

            foreach (SLFilterColumn fc in this.FilterColumns)
            {
                af.Append(fc.ToFilterColumn());
            }

            if (HasSortState)
            {
                af.Append(this.SortState.ToSortState());
            }

            return(af);
        }
Ejemplo n.º 5
0
        internal void FromLocation(Location loc)
        {
            this.SetAllNull();

            if (loc.Reference != null)
            {
                this.Reference = SLTool.TranslateReferenceToCellPointRange(loc.Reference.Value);
            }
            if (loc.FirstHeaderRow != null)
            {
                this.FirstHeaderRow = loc.FirstHeaderRow.Value;
            }
            if (loc.FirstDataRow != null)
            {
                this.FirstDataRow = loc.FirstDataRow.Value;
            }
            if (loc.FirstDataColumn != null)
            {
                this.FirstDataColumn = loc.FirstDataColumn.Value;
            }
            if (loc.RowPageCount != null)
            {
                this.RowPageCount = loc.RowPageCount.Value;
            }
            if (loc.ColumnsPerPage != null)
            {
                this.ColumnsPerPage = loc.ColumnsPerPage.Value;
            }
        }
Ejemplo n.º 6
0
        internal void FromHyperlink(Hyperlink hl)
        {
            this.SetAllNull();

            this.IsNew = false;

            if (hl.Reference != null)
            {
                this.Reference = SLTool.TranslateReferenceToCellPointRange(hl.Reference.Value);
            }

            if (hl.Id != null)
            {
                // At least I think if there's a relationship ID, it's an external link.
                this.IsExternal = true;
                this.Id         = hl.Id.Value;
            }

            if (hl.Location != null)
            {
                this.Location = hl.Location.Value;
            }
            if (hl.Tooltip != null)
            {
                this.ToolTip = hl.Tooltip.Value;
            }
            if (hl.Display != null)
            {
                this.Display = hl.Display.Value;
            }
        }
Ejemplo n.º 7
0
        internal CalculationCell ToCalculationCell()
        {
            CalculationCell cc = new CalculationCell();

            cc.CellReference = SLTool.ToCellReference(this.RowIndex, this.ColumnIndex);
            cc.SheetId       = this.SheetId;
            if (this.InChildChain != null && this.InChildChain.Value)
            {
                cc.InChildChain = this.InChildChain.Value;
            }
            if (this.NewLevel != null && this.NewLevel.Value)
            {
                cc.NewLevel = this.NewLevel.Value;
            }
            if (this.NewThread != null && this.NewThread.Value)
            {
                cc.NewThread = this.NewThread.Value;
            }
            if (this.Array != null && this.Array.Value)
            {
                cc.Array = this.Array.Value;
            }

            return(cc);
        }
        internal int DirectSaveToSharedStringTable(string Data)
        {
            int    index = 0;
            string sHash;

            if (SLTool.ToPreserveSpace(Data))
            {
                sHash = string.Format("<x:t xml:space=\"preserve\">{0}</x:t>", Data);
            }
            else
            {
                sHash = string.Format("<x:t>{0}</x:t>", Data);
            }

            if (dictSharedStringHash.ContainsKey(sHash))
            {
                index = dictSharedStringHash[sHash];
            }
            else
            {
                index = listSharedString.Count;
                listSharedString.Add(sHash);
                dictSharedStringHash[sHash] = index;
            }

            return(index);
        }
Ejemplo n.º 9
0
        internal Location ToLocation()
        {
            Location loc = new Location();

            if (this.Reference.StartRowIndex == this.Reference.EndRowIndex &&
                this.Reference.StartColumnIndex == this.Reference.EndColumnIndex)
            {
                // this shouldn't happen because what's the point of a single cell as
                // the whole pivot table? Something's already very wrong by this time...
                loc.Reference = SLTool.ToCellReference(this.Reference.StartRowIndex, this.Reference.StartColumnIndex);
            }
            else
            {
                loc.Reference = SLTool.ToCellRange(this.Reference.StartRowIndex, this.Reference.StartColumnIndex, this.Reference.EndRowIndex, this.Reference.EndColumnIndex);
            }

            loc.FirstHeaderRow  = this.FirstHeaderRow;
            loc.FirstDataRow    = this.FirstDataRow;
            loc.FirstDataColumn = this.FirstDataColumn;
            if (this.RowPageCount != 0)
            {
                loc.RowPageCount = this.RowPageCount;
            }
            if (this.ColumnsPerPage != 0)
            {
                loc.ColumnsPerPage = this.ColumnsPerPage;
            }

            return(loc);
        }
        /// <summary>
        /// Allow date values.
        /// </summary>
        /// <param name="DataOperator">The type of operation.</param>
        /// <param name="DataValue">The data value. Any valid date formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
        /// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
        public void AllowDate(SLDataValidationSingleOperandValues DataOperator, string DataValue, bool IgnoreBlank)
        {
            this.Type     = DataValidationValues.Date;
            this.Operator = this.TranslateOperatorValues(DataOperator);

            DateTime dt;

            if (DataValue.StartsWith("="))
            {
                this.Formula1 = DataValue.Substring(1);
            }
            else
            {
                if (DateTime.TryParse(DataValue, out dt))
                {
                    this.Formula1 = SLTool.CalculateDaysFromEpoch(dt, this.Date1904).ToString(CultureInfo.InvariantCulture);
                }
                else
                {
                    // 1 Jan 1900
                    this.Formula1 = "1";
                }
            }

            this.Formula2   = string.Empty;
            this.AllowBlank = IgnoreBlank;
        }
Ejemplo n.º 11
0
        /// <summary>
        /// Form a DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class from this SLRstType class.
        /// </summary>
        /// <returns>A DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class.</returns>
        public SharedStringItem ToSharedStringItem()
        {
            SharedStringItem ssi = new SharedStringItem();

            ssi.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml);
            return(ssi);
        }
Ejemplo n.º 12
0
        /// <summary>
        /// Form a SLMergeCell from a DocumentFormat.OpenXml.Spreadsheet.MergeCell class.
        /// </summary>
        /// <param name="mc">The source DocumentFormat.OpenXml.Spreadsheet.MergeCell class.</param>
        public void FromMergeCell(MergeCell mc)
        {
            string sStartCell = string.Empty, sEndCell = string.Empty;
            int    index = 0;
            bool   bStartSuccess = false, bEndSuccess = false;

            bIsValid = false;

            if (mc.Reference != null)
            {
                index = mc.Reference.Value.IndexOf(":");
                // if "A1:C3", then the index must be at least at the 3rd position (or index 2)
                if (index >= 2)
                {
                    sStartCell = mc.Reference.Value.Substring(0, index);
                    sEndCell   = mc.Reference.Value.Substring(index + 1);

                    bStartSuccess = SLTool.FormatCellReferenceToRowColumnIndex(sStartCell, out this.iStartRowIndex, out this.iStartColumnIndex);
                    bEndSuccess   = SLTool.FormatCellReferenceToRowColumnIndex(sEndCell, out this.iEndRowIndex, out this.iEndColumnIndex);

                    if (bStartSuccess && bEndSuccess)
                    {
                        bIsValid = true;
                    }
                }
            }
        }
Ejemplo n.º 13
0
        /// <summary>
        /// Form a DocumentFormat.OpenXml.Spreadsheet.InlineString class from this SLRstType class.
        /// </summary>
        /// <returns>A DocumentFormat.OpenXml.Spreadsheet.InlineString class.</returns>
        public InlineString ToInlineString()
        {
            InlineString istr = new InlineString();

            istr.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml);
            return(istr);
        }
Ejemplo n.º 14
0
        /// <summary>
        /// Form a DocumentFormat.OpenXml.Spreadsheet.CommentText class from this SLRstType class.
        /// </summary>
        /// <returns>A DocumentFormat.OpenXml.Spreadsheet.CommentText class.</returns>
        public CommentText ToCommentText()
        {
            CommentText ct = new CommentText();

            ct.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml);
            return(ct);
        }
Ejemplo n.º 15
0
        private void SetDisplayColor()
        {
            this.clrDisplay = System.Drawing.Color.FromArgb(255, 0, 0, 0);

            int index = 0;

            if (this.Theme != null)
            {
                index = (int)this.Theme.Value;
                if (index >= 0 && index < this.listThemeColors.Count)
                {
                    this.clrDisplay = System.Drawing.Color.FromArgb(255, this.listThemeColors[index]);
                    if (this.Tint != null)
                    {
                        this.clrDisplay = SLTool.ToColor(this.clrDisplay, this.Tint.Value);
                    }
                }
            }
            else if (this.Rgb != null)
            {
                this.clrDisplay = SLTool.ToColor(this.Rgb);
            }
            else if (this.Indexed != null)
            {
                index = (int)this.Indexed.Value;
                if (index >= 0 && index < this.listIndexedColors.Count)
                {
                    this.clrDisplay = System.Drawing.Color.FromArgb(255, this.listIndexedColors[index]);
                }
            }
        }
Ejemplo n.º 16
0
        /// <summary>
        /// Form a SLMergeCell given a corner cell of the to-be-merged rectangle of cells, and the opposite corner cell. For example, the top-left corner cell and the bottom-right corner cell. Or the bottom-left corner cell and the top-right corner cell.
        /// </summary>
        /// <param name="StartRowIndex">The row index of the corner cell.</param>
        /// <param name="StartColumnIndex">The column index of the corner cell.</param>
        /// <param name="EndRowIndex">The row index of the opposite corner cell.</param>
        /// <param name="EndColumnIndex">The column index of the opposite corner cell.</param>
        public void FromIndices(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
        {
            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 == iEndRowIndex && iStartColumnIndex == iEndColumnIndex)
            {
                // it's the same cell! We'll treat this as invalid.
                this.bIsValid = false;
            }
            else
            {
                this.bIsValid = SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex) && SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex);
            }
        }
 /// <summary>
 /// Allow date values.
 /// </summary>
 /// <param name="IsBetween">True if the data is between 2 values. False otherwise.</param>
 /// <param name="Minimum">The minimum value.</param>
 /// <param name="Maximum">The maximum value.</param>
 /// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
 public void AllowDate(bool IsBetween, DateTime Minimum, DateTime Maximum, bool IgnoreBlank)
 {
     this.Type       = DataValidationValues.Date;
     this.Operator   = IsBetween ? DataValidationOperatorValues.Between : DataValidationOperatorValues.NotBetween;
     this.Formula1   = SLTool.CalculateDaysFromEpoch(Minimum, this.Date1904).ToString(CultureInfo.InvariantCulture);
     this.Formula2   = SLTool.CalculateDaysFromEpoch(Maximum, this.Date1904).ToString(CultureInfo.InvariantCulture);
     this.AllowBlank = IgnoreBlank;
 }
        internal void ForceSaveToSharedStringTable(SharedStringItem ssi)
        {
            int    index = listSharedString.Count;
            string sHash = SLTool.RemoveNamespaceDeclaration(ssi.InnerXml);

            listSharedString.Add(sHash);
            dictSharedStringHash[sHash] = index;
        }
 /// <summary>
 /// Allow date values.
 /// </summary>
 /// <param name="DataOperator">The type of operation.</param>
 /// <param name="DataValue">The data value. Any valid date formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
 /// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
 public void AllowDate(SLDataValidationSingleOperandValues DataOperator, DateTime DataValue, bool IgnoreBlank)
 {
     this.Type       = DataValidationValues.Date;
     this.Operator   = this.TranslateOperatorValues(DataOperator);
     this.Formula1   = SLTool.CalculateDaysFromEpoch(DataValue, this.Date1904).ToString(CultureInfo.InvariantCulture);
     this.Formula2   = string.Empty;
     this.AllowBlank = IgnoreBlank;
 }
        /// <summary>
        /// Get the width of the specified column.
        /// </summary>
        /// <param name="Unit">The unit of the width to be returned.</param>
        /// <param name="ColumnName">The column name, such as "A".</param>
        /// <returns>The width in the specified unit type.</returns>
        public double GetWidth(SLMeasureUnitTypeValues Unit, string ColumnName)
        {
            int iColumnIndex = -1;

            iColumnIndex = SLTool.ToColumnIndex(ColumnName);

            return(this.GetWidth(Unit, iColumnIndex, iColumnIndex));
        }
        internal void FromSortCondition(SortCondition sc)
        {
            this.SetAllNull();

            if (sc.Descending != null && sc.Descending.Value)
            {
                this.Descending = sc.Descending.Value;
            }
            if (sc.SortBy != null)
            {
                this.SortBy = sc.SortBy.Value;
            }

            int    iStartRowIndex    = 1;
            int    iStartColumnIndex = 1;
            int    iEndRowIndex      = 1;
            int    iEndColumnIndex   = 1;
            string sRef = sc.Reference.Value;

            if (sRef.IndexOf(":") > 0)
            {
                if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
                {
                    this.StartRowIndex    = iStartRowIndex;
                    this.StartColumnIndex = iStartColumnIndex;
                    this.EndRowIndex      = iEndRowIndex;
                    this.EndColumnIndex   = iEndColumnIndex;
                }
            }
            else
            {
                if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
                {
                    this.StartRowIndex    = iStartRowIndex;
                    this.StartColumnIndex = iStartColumnIndex;
                    this.EndRowIndex      = iStartRowIndex;
                    this.EndColumnIndex   = iStartColumnIndex;
                }
            }

            if (sc.CustomList != null)
            {
                this.CustomList = sc.CustomList.Value;
            }
            if (sc.FormatId != null)
            {
                this.FormatId = sc.FormatId.Value;
            }
            if (sc.IconSet != null)
            {
                this.IconSet = sc.IconSet.Value;
            }
            if (sc.IconId != null)
            {
                this.IconId = sc.IconId.Value;
            }
        }
        /// <summary>
        /// Remove an existing hyperlink.
        /// </summary>
        /// <param name="CellReference">The cell reference, such as "A1".</param>
        public void RemoveHyperlink(string CellReference)
        {
            int iRowIndex    = -1;
            int iColumnIndex = -1;

            SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex);

            this.RemoveHyperlink(iRowIndex, iColumnIndex);
        }
Ejemplo n.º 23
0
        /// <summary>
        /// Form a DocumentFormat.OpenXml.Spreadsheet.MergeCell class from this SLMergeCell class.
        /// </summary>
        /// <returns>A DocumentFormat.OpenXml.Spreadsheet.MergeCell class.</returns>
        public MergeCell ToMergeCell()
        {
            MergeCell mc         = new MergeCell();
            string    sStartCell = SLTool.ToCellReference(iStartRowIndex, iStartColumnIndex);
            string    sEndCell   = SLTool.ToCellReference(iEndRowIndex, iEndColumnIndex);

            mc.Reference = string.Format("{0}:{1}", sStartCell, sEndCell);

            return(mc);
        }
        /// <summary>
        /// Get the width of the specified columns.
        /// </summary>
        /// <param name="Unit">The unit of the width to be returned.</param>
        /// <param name="StartColumnName">The column name of the start column.</param>
        /// <param name="EndColumnName">The column name of the end column.</param>
        /// <returns>The width in the specified unit type.</returns>
        public double GetWidth(SLMeasureUnitTypeValues Unit, string StartColumnName, string EndColumnName)
        {
            int iStartColumnIndex = -1;
            int iEndColumnIndex   = -1;

            iStartColumnIndex = SLTool.ToColumnIndex(StartColumnName);
            iEndColumnIndex   = SLTool.ToColumnIndex(EndColumnName);

            return(this.GetWidth(Unit, iStartColumnIndex, iEndColumnIndex));
        }
        /// <summary>
        /// Import a text file as a data source, with the first data row and first data column at a specific cell.
        /// </summary>
        /// <param name="FileName">The file name.</param>
        /// <param name="AnchorCellReference">The anchor cell reference, such as "A1".</param>
        /// <param name="Options">Text import options.</param>
        public void ImportText(string FileName, string AnchorCellReference, SLTextImportOptions Options)
        {
            int iRowIndex    = -1;
            int iColumnIndex = -1;

            if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex))
            {
                this.ImportText(FileName, iRowIndex, iColumnIndex, Options);
            }
        }
Ejemplo n.º 26
0
        internal void FromAutoFilter(AutoFilter af)
        {
            this.SetAllNull();

            int    iStartRowIndex    = 1;
            int    iStartColumnIndex = 1;
            int    iEndRowIndex      = 1;
            int    iEndColumnIndex   = 1;
            string sRef = af.Reference.Value;

            if (sRef.IndexOf(":") > 0)
            {
                if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
                {
                    this.StartRowIndex    = iStartRowIndex;
                    this.StartColumnIndex = iStartColumnIndex;
                    this.EndRowIndex      = iEndRowIndex;
                    this.EndColumnIndex   = iEndColumnIndex;
                }
            }
            else
            {
                if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
                {
                    this.StartRowIndex    = iStartRowIndex;
                    this.StartColumnIndex = iStartColumnIndex;
                    this.EndRowIndex      = iStartRowIndex;
                    this.EndColumnIndex   = iStartColumnIndex;
                }
            }

            if (af.HasChildren)
            {
                SLFilterColumn fc;
                using (OpenXmlReader oxr = OpenXmlReader.Create(af))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(FilterColumn))
                        {
                            fc = new SLFilterColumn();
                            fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
                            this.FilterColumns.Add(fc);
                        }
                        else if (oxr.ElementType == typeof(SortState))
                        {
                            this.SortState = new SLSortState();
                            this.SortState.FromSortState((SortState)oxr.LoadCurrentElement());
                            this.HasSortState = true;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Insert a hyperlink.
        /// </summary>
        /// <param name="CellReference">The cell reference, such as "A1".</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>
        /// <returns>True if successful. False otherwise.</returns>
        public bool InsertHyperlink(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address)
        {
            int iRowIndex    = -1;
            int iColumnIndex = -1;

            if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
            {
                return(false);
            }

            return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, null, null, false));
        }
        // merging spreadsheets is kinda like importing data, right? So it's here then.

//        public void MergeSpreadsheet(string SpreadsheetFileName)
//        {
//            this.MergeSpreadsheet(true, SpreadsheetFileName, null);
//        }

//        public void MergeSpreadsheet(Stream SpreadsheetStream)
//        {
//            this.MergeSpreadsheet(false, null, SpreadsheetStream);
//        }

//        private void MergeSpreadsheet(bool IsFile, string SpreadsheetFileName, Stream SpreadsheetStream)
//        {
//            using (MemoryStream msAnother = new MemoryStream())
//            {
//                if (IsFile)
//                {
//                    byte[] baData = File.ReadAllBytes(SpreadsheetFileName);
//                    msAnother.Write(baData, 0, baData.Length);
//                }
//                else
//                {
//                    SpreadsheetStream.Position = 0;
//                    byte[] baData = new byte[SpreadsheetStream.Length];
//                    SpreadsheetStream.Read(baData, 0, baData.Length);
//                    msAnother.Write(baData, 0, baData.Length);
//                }

//                using (SpreadsheetDocument xlAnother = SpreadsheetDocument.Open(msAnother, false))
//                {
//                    HashSet<string> hsCurrentSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
//                    foreach (SLSheet sheet in slwb.Sheets)
//                    {
//                        // current sheet names supposed to be unique, so I'm not checking for collisions.
//                        hsCurrentSheetNames.Add(sheet.Name);
//                    }

//                    HashSet<string> hsAnotherSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
//                    List<string> listAnotherSheetNames = new List<string>();
//                    using (OpenXmlReader oxr = OpenXmlReader.Create(xlAnother.WorkbookPart.Workbook.Sheets))
//                    {
//                        string sSheetName;
//                        while (oxr.Read())
//                        {
//                            if (oxr.ElementType == typeof(Sheet))
//                            {
//                                sSheetName = ((Sheet)oxr.LoadCurrentElement()).Name.Value;
//                                hsAnotherSheetNames.Add(sSheetName);
//                                listAnotherSheetNames.Add(sSheetName);
//                            }
//                        }
//                    }

////Sheet1
////Sheet2
////Sheet3

////Sheet1 -> Sheet7 -> Sheet8
////Sheet6
////Sheet7

//                    Dictionary<string, string> dictAnotherNewSheetNames = new Dictionary<string, string>();
//                    foreach (string s in listAnotherSheetNames)
//                    {
//                    }
//                }
//                // end of using SpreadsheetDocument
//            }
//        }

        /// <summary>
        /// Import a System.Data.DataTable as a data source, with the first data row and first data column at a specific cell.
        /// </summary>
        /// <param name="CellReference">The cell reference, such as "A1".</param>
        /// <param name="Data">The data table.</param>
        /// <param name="IncludeHeader">True if the data table's column names are to be used in the first row as a header row. False otherwise.</param>
        public void ImportDataTable(string CellReference, DataTable Data, bool IncludeHeader)
        {
            int iRowIndex    = -1;
            int iColumnIndex = -1;

            if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
            {
                return;
            }

            ImportDataTable(iRowIndex, iColumnIndex, Data, IncludeHeader);
        }
        /// <summary>
        /// Adds a print area to the existing print area on the currently selected worksheet given a corner cell of the print area and the opposite corner cell.
        /// </summary>
        /// <param name="StartCellReference">The cell reference of the corner cell, such as "A1".</param>
        /// <param name="EndCellReference">The cell reference of the opposite corner cell, such as "A1".</param>
        public void AddToPrintArea(string StartCellReference, string EndCellReference)
        {
            int iStartRowIndex    = -1;
            int iStartColumnIndex = -1;
            int iEndRowIndex      = -1;
            int iEndColumnIndex   = -1;

            SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex);
            SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex);

            this.SetAddPrintArea(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true);
        }
        /// <summary>
        /// Insert a hyperlink.
        /// </summary>
        /// <param name="CellReference">The cell reference, such as "A1".</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(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
        {
            int iRowIndex    = -1;
            int iColumnIndex = -1;

            if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
            {
                return(false);
            }

            return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, Display, ToolTip, OverwriteExistingCell));
        }