internal SLWorkbookView Clone() { SLWorkbookView wv = new SLWorkbookView(); wv.Visibility = this.Visibility; wv.Minimized = this.Minimized; wv.ShowHorizontalScroll = this.ShowHorizontalScroll; wv.ShowVerticalScroll = this.ShowVerticalScroll; wv.ShowSheetTabs = this.ShowSheetTabs; wv.XWindow = this.XWindow; wv.YWindow = this.YWindow; wv.WindowWidth = this.WindowWidth; wv.WindowHeight = this.WindowHeight; wv.TabRatio = this.TabRatio; wv.FirstSheet = this.FirstSheet; wv.ActiveTab = this.ActiveTab; wv.AutoFilterDateGrouping = this.AutoFilterDateGrouping; return(wv); }
/// <summary> /// Make sure to call this only after all worksheets are done! But before writing the workbook! /// </summary> private void WriteTabSelectedWorksheet() { bool bFound = false; string sRelID = string.Empty; foreach (WorksheetPart wsp in wbp.WorksheetParts) { #region Worksheets sRelID = wbp.GetIdOfPart(wsp); if (sRelID.Equals(gsSelectedWorksheetRelationshipID, StringComparison.OrdinalIgnoreCase)) { if (wsp.Worksheet.SheetViews != null) { bFound = false; foreach (SheetView sv in wsp.Worksheet.SheetViews) { // need to do for all sheet views? if (sv.WorkbookViewId == 0) { bFound = true; sv.TabSelected = true; } } if (!bFound) { wsp.Worksheet.SheetViews.Append(new SheetView() { TabSelected = true, WorkbookViewId = 0 }); } } else { wsp.Worksheet.SheetViews = new SheetViews(); wsp.Worksheet.SheetViews.Append(new SheetView() { TabSelected = true, WorkbookViewId = 0 }); } } else { if (wsp.Worksheet.SheetViews != null) { foreach (SheetView sv in wsp.Worksheet.SheetViews) { // the default value is false, and setting null generates less XML data sv.TabSelected = null; } } // no else because we'll just ignore } // TODO: Custom sheet views? wsp.Worksheet.Save(); #endregion } #region Chartsheets foreach (ChartsheetPart csp in wbp.ChartsheetParts) { if (csp.Chartsheet.ChartSheetViews != null) { foreach (ChartSheetView csv in csp.Chartsheet.ChartSheetViews) { // the default value is false, and setting null generates less XML data csv.TabSelected = null; } } // no else because we'll just ignore // TODO: Custom chart sheet views? csp.Chartsheet.Save(); } #endregion #region Dialogsheets foreach (DialogsheetPart dsp in wbp.DialogsheetParts) { if (dsp.DialogSheet.SheetViews != null) { foreach (SheetView sv in dsp.DialogSheet.SheetViews) { // the default value is false, and setting null generates less XML data sv.TabSelected = null; } } // no else because we'll just ignore // TODO: Custom sheet views? dsp.DialogSheet.Save(); } #endregion #region Macrosheets foreach (MacroSheetPart msp in wbp.MacroSheetParts) { if (msp.Macrosheet.SheetViews != null) { foreach (SheetView sv in msp.Macrosheet.SheetViews) { // the default value is false, and setting null generates less XML data sv.TabSelected = null; } } // no else because we'll just ignore // TODO: Custom sheet views? msp.Macrosheet.Save(); } #endregion // the currently selected worksheet "must" be visible, right? this.ShowWorksheet(gsSelectedWorksheetName); // if you think about it, the active tab is always >= the first sheet uint? iFirstSheet = null; uint? iActiveTab = null; int i; for (i = 0; i < slwb.Sheets.Count; ++i) { // there's guaranteed to be at least one visible worksheet // because we made it so above. if (iFirstSheet == null) { if (slwb.Sheets[i].State == SheetStateValues.Visible) iFirstSheet = (uint)i; } // there's guaranteed to be one worksheet with the same name as the // currently selected worksheet. if (iActiveTab == null) { if (slwb.Sheets[i].Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase)) { iActiveTab = (uint)i; } } } // basically with the double guarantees, the first sheet and active tab cannot be both null. // but we check for nullity just in case... if (iFirstSheet != null && iActiveTab != null) { if (slwb.WorkbookViews.Count > 0) { if (iFirstSheet != null) slwb.WorkbookViews[0].FirstSheet = iFirstSheet.Value; if (iActiveTab != null) slwb.WorkbookViews[0].ActiveTab = iActiveTab.Value; } else { SLWorkbookView wv = new SLWorkbookView(); if (iFirstSheet != null) wv.FirstSheet = iFirstSheet.Value; if (iActiveTab != null) wv.ActiveTab = iActiveTab.Value; slwb.WorkbookViews.Add(wv); } } // I'm not creating a default workbook view because the main info seems to be the position // and size of the workbook view within the application (read: Excel). // Since I don't know how to calculate it (because I can't *see* the user's screen), I'm // going to ignore it. Twips? Good grief... }
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); } } }
internal SLWorkbookView Clone() { SLWorkbookView wv = new SLWorkbookView(); wv.Visibility = this.Visibility; wv.Minimized = this.Minimized; wv.ShowHorizontalScroll = this.ShowHorizontalScroll; wv.ShowVerticalScroll = this.ShowVerticalScroll; wv.ShowSheetTabs = this.ShowSheetTabs; wv.XWindow = this.XWindow; wv.YWindow = this.YWindow; wv.WindowWidth = this.WindowWidth; wv.WindowHeight = this.WindowHeight; wv.TabRatio = this.TabRatio; wv.FirstSheet = this.FirstSheet; wv.ActiveTab = this.ActiveTab; wv.AutoFilterDateGrouping = this.AutoFilterDateGrouping; return wv; }