internal SLCalculationCell Clone()
        {
            SLCalculationCell cc = new SLCalculationCell();
            cc.RowIndex = this.RowIndex;
            cc.ColumnIndex = this.ColumnIndex;
            cc.SheetId = this.SheetId;
            cc.InChildChain = this.InChildChain;
            cc.NewLevel = this.NewLevel;
            cc.NewThread = this.NewThread;
            cc.Array = this.Array;

            return cc;
        }
Exemplo n.º 2
0
        /// <summary>
        /// Adds a calculation cell if it doesn't already exist
        /// </summary>
        /// <param name="cc"></param>
        internal void AddCalculationCell(SLCalculationCell cc)
        {
            bool bFound = false;
            foreach (SLCalculationCell calc in this.CalculationCells)
            {
                if (calc.SheetId == cc.SheetId && calc.RowIndex == cc.RowIndex && calc.ColumnIndex == cc.ColumnIndex)
                {
                    bFound = true;
                    break;
                }
            }

            if (!bFound) this.CalculationCells.Add(cc.Clone());
        }
Exemplo n.º 3
0
        private void OpenExistingSpreadsheet(string SheetNameOnOpen)
        {
            xl = SpreadsheetDocument.Open(memstream, true);
            wbp = xl.WorkbookPart;
            IsNewSpreadsheet = false;
            slwb = new SLWorkbook();

            this.DocumentProperties = new SLDocumentProperties();
            this.LoadDocumentProperties();

            InitialiseAutoFitCache();

            LoadBuiltInNumberingFormats();
            InitialiseStylesheetWhatNots(SLThemeTypeValues.Office);
            LoadSharedStringTable();

            giWorksheetIdCounter = 0;
            using (OpenXmlReader oxr = OpenXmlReader.Create(wbp))
            {
                SLWorkbookView wv;
                Sheet s;
                SLSheet sheet;
                DefinedName dn;
                SLDefinedName sldn;
                while (oxr.Read())
                {
                    if (oxr.ElementType == typeof(WorkbookView))
                    {
                        wv = new SLWorkbookView();
                        wv.FromWorkbookView((WorkbookView)oxr.LoadCurrentElement());
                        slwb.WorkbookViews.Add(wv);
                    }
                    else if (oxr.ElementType == typeof(Sheet))
                    {
                        s = (Sheet)oxr.LoadCurrentElement();
                        sheet = new SLSheet(s.Name.Value, s.SheetId.Value, s.Id.Value, SLSheetType.Unknown);
                        if (s.State != null) sheet.State = s.State.Value;
                        slwb.Sheets.Add(sheet);
                        if (sheet.SheetId > giWorksheetIdCounter)
                        {
                            giWorksheetIdCounter = (int)sheet.SheetId;
                        }
                    }
                    else if (oxr.ElementType == typeof(DefinedName))
                    {
                        dn = (DefinedName)oxr.LoadCurrentElement();
                        sldn = new SLDefinedName(dn.Name.Value);
                        sldn.FromDefinedName(dn);
                        slwb.DefinedNames.Add(sldn);
                    }
                    else if (oxr.ElementType == typeof(PivotCache))
                    {
                        // cache IDs supposed to be unique, so I'm not gonna check for the hash set
                        slwb.PivotTableCacheIds.Add(((PivotCache)oxr.LoadCurrentElement()).CacheId.Value);
                    }
                }
            }

            if (wbp.Workbook.WorkbookProperties != null)
            {
                slwb.WorkbookProperties.FromWorkbookProperties(wbp.Workbook.WorkbookProperties);
            }

            if (wbp.CalculationChainPart != null)
            {
                int iCurrentSheetId = 0;
                SLCalculationCell slcc = new SLCalculationCell(string.Empty);
                CalculationCell cc;
                using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculationCell))
                        {
                            cc = (CalculationCell)oxr.LoadCurrentElement();
                            if (cc.SheetId == null)
                            {
                                cc.SheetId = iCurrentSheetId;
                            }
                            else
                            {
                                if (cc.SheetId.Value != iCurrentSheetId)
                                    iCurrentSheetId = cc.SheetId.Value;
                            }
                            slcc.FromCalculationCell(cc);
                            slwb.CalculationCells.Add(slcc.Clone());
                        }
                    }
                }
            }

            // To determine the type of sheet. Do this before the part
            // where the table and pivot table parts are set.

            bool bFound = false;
            string sRelID = string.Empty;
            foreach (SLSheet sheet in slwb.Sheets)
            {
                bFound = false;
                foreach (WorksheetPart wspFound in wbp.WorksheetParts)
                {
                    sRelID = wbp.GetIdOfPart(wspFound);
                    if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                    {
                        sheet.SheetType = SLSheetType.Worksheet;
                        bFound = true;
                        break;
                    }
                }

                if (!bFound)
                {
                    foreach (ChartsheetPart csp in wbp.ChartsheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(csp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.Chartsheet;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (!bFound)
                {
                    foreach (DialogsheetPart dsp in wbp.DialogsheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(dsp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.DialogSheet;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (!bFound)
                {
                    foreach (MacroSheetPart msp in wbp.MacroSheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(msp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.Macrosheet;
                            bFound = true;
                            break;
                        }
                    }
                }
            }

            WorksheetPart wsp;
            foreach (SLSheet sheet in slwb.Sheets)
            {
                if (sheet.SheetType == SLSheetType.Worksheet)
                {
                    wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
                    foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
                    {
                        if (tdp.Table.Id != null && !slwb.TableIds.Contains(tdp.Table.Id.Value))
                            slwb.TableIds.Add(tdp.Table.Id.Value);

                        if (tdp.Table.Name != null && !slwb.TableNames.Contains(tdp.Table.Name.Value))
                            slwb.TableNames.Add(tdp.Table.Name.Value);
                    }

                    foreach (PivotTablePart ptp in wsp.PivotTableParts)
                    {
                        if (ptp.PivotTableDefinition.Name != null
                            && !slwb.PivotTableNames.Contains(ptp.PivotTableDefinition.Name.Value))
                            slwb.PivotTableNames.Add(ptp.PivotTableDefinition.Name.Value);

                        // the cache ID should already be added, from the workbook part above.
                        // But we check again just to be sure. Cache IDs have to be unique throughout
                        // the workbook.
                        if (ptp.PivotTableDefinition.CacheId != null
                            && !slwb.PivotTableCacheIds.Contains(ptp.PivotTableDefinition.CacheId.Value))
                            slwb.PivotTableCacheIds.Add(ptp.PivotTableDefinition.CacheId.Value);
                    }
                }
            }

            string sWorksheetName = SLConstants.DefaultFirstSheetName;
            int i = 1;
            bool bCannotFind = true;
            bool bIsLegit = true;
            if (wbp.WorksheetParts.Count() == 0)
            {
                // no worksheets! Apparently an Excel file with only 1 dialog sheet is perfectly legit...
                // come up with a legit worksheet name that's not already taken...
                i = 1;
                bCannotFind = true;
                while (bCannotFind)
                {
                    sWorksheetName = string.Format("Sheet{0}", i);
                    bIsLegit = true;
                    foreach (SLSheet sheet in slwb.Sheets)
                    {
                        if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
                        {
                            bIsLegit = false;
                            break;
                        }
                    }
                    ++i;
                    if (bIsLegit) bCannotFind = false;
                }

                AddWorksheet(sWorksheetName);
            }
            else
            {
                bFound = false;
                // there's a given worksheet name
                if (SheetNameOnOpen.Length > 0)
                {
                    foreach (SLSheet sheet in slwb.Sheets)
                    {
                        if (sheet.Name.Equals(SheetNameOnOpen, StringComparison.OrdinalIgnoreCase)
                            && sheet.SheetType == SLSheetType.Worksheet)
                        {
                            giSelectedWorksheetID = sheet.SheetId;
                            gsSelectedWorksheetName = sheet.Name;
                            gsSelectedWorksheetRelationshipID = sheet.Id;
                            bFound = true;
                            break;
                        }
                    }
                }
                else
                {
                    // we try to get the "actively selected" worksheet already selected.
                    uint iActiveTab = 0;
                    if (slwb.WorkbookViews.Count > 0)
                    {
                        iActiveTab = slwb.WorkbookViews[0].ActiveTab;
                    }

                    // there should be at least *this* number of sheets (whether it's a worksheet
                    // chartsheet or whatnot).
                    if (slwb.Sheets.Count > iActiveTab
                        && slwb.Sheets[(int)iActiveTab].SheetType == SLSheetType.Worksheet)
                    {
                        giSelectedWorksheetID = slwb.Sheets[(int)iActiveTab].SheetId;
                        gsSelectedWorksheetName = slwb.Sheets[(int)iActiveTab].Name;
                        gsSelectedWorksheetRelationshipID = slwb.Sheets[(int)iActiveTab].Id;
                        bFound = true;
                    }
                }

                if (!bFound)
                {
                    // we get here either if there's no given worksheet name (bFound is still false),
                    // or there's a given worksheet name but corresponding values weren't found.
                    // The given worksheet name must be that of a worksheet. A chartsheet name is
                    // considered "invalid".
                    // Either way, we use the first available worksheet as the selected worksheet.
                    wsp = wbp.WorksheetParts.First();
                    sRelID = wbp.GetIdOfPart(wsp);

                    foreach (SLSheet sheet in slwb.Sheets)
                    {
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            giSelectedWorksheetID = sheet.SheetId;
                            gsSelectedWorksheetName = sheet.Name;
                            gsSelectedWorksheetRelationshipID = sheet.Id;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (bFound)
                {
                    // A viable worksheet should be found by now. Otherwise, it's probably
                    // a corrupted spreadsheet...
                    LoadSelectedWorksheet();
                    IsNewWorksheet = false;
                }
                else
                {
                    // why is it not found!?! The file is corrupted somehow... we'll try to recover
                    // by adding a new worksheet and selecting it. Same algorithm as above.
                    i = 1;
                    bCannotFind = true;
                    while (bCannotFind)
                    {
                        sWorksheetName = string.Format("Sheet{0}", i);
                        bIsLegit = true;
                        foreach (SLSheet sheet in slwb.Sheets)
                        {
                            if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
                            {
                                bIsLegit = false;
                                break;
                            }
                        }
                        ++i;
                        if (bIsLegit) bCannotFind = false;
                    }

                    AddWorksheet(sWorksheetName);
                }
            }
        }
        /// <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;
        }