Exemplo n.º 1
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.º 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);
                }
            }
        }