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