/// <summary> /// Set the cell value given the row index and column index. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Data">The cell value data.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool SetCellValue(int RowIndex, int ColumnIndex, bool Data) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; } else { c = new SLCell(); uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex); if (iStyleIndex != 0) { c.StyleIndex = iStyleIndex; } } c.DataType = CellValues.Boolean; c.NumericValue = Data ? 1 : 0; slws.Cells[pt] = c; return true; }
internal static bool IsSuitableCategoryHeader(Dictionary<SLCellPoint, SLCell> Cells, int RowIndex, int ColumnIndex) { bool result = false; SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (Cells.ContainsKey(pt)) { if (Cells[pt].DataType == CellValues.String || Cells[pt].DataType == CellValues.SharedString) { result = true; } } return result; }
internal static bool IsSuitableCategoryHeader(Dictionary <SLCellPoint, SLCell> Cells, int RowIndex, int ColumnIndex) { bool result = false; SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (Cells.ContainsKey(pt)) { if (Cells[pt].DataType == CellValues.String || Cells[pt].DataType == CellValues.SharedString) { result = true; } } return(result); }
/// <summary> /// Insert comment given the row index and column index of the cell it's based on. This will overwrite any existing comment. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Comment">The cell comment.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool InsertComment(int RowIndex, int ColumnIndex, SLComment Comment) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } if (!slws.Authors.Contains(Comment.Author)) { slws.Authors.Add(Comment.Author); } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLComment comm = Comment.Clone(); slws.Comments[pt] = comm; return true; }
/// <summary> /// Insert comment given the row index and column index of the cell it's based on. This will overwrite any existing /// comment. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Comment">The cell comment.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool InsertComment(int RowIndex, int ColumnIndex, SLComment Comment) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return(false); } if (!slws.Authors.Contains(Comment.Author)) { slws.Authors.Add(Comment.Author); } var pt = new SLCellPoint(RowIndex, ColumnIndex); var comm = Comment.Clone(); slws.Comments[pt] = comm; return(true); }
/// <summary> /// Set the cell value given the row index and column index. Be sure to follow up with a date format style. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Data">The cell value data.</param> /// <param name="Format">The format string used if the given date is before the date epoch. A date before the date epoch is stored as a string, so the date precision is only as good as the format string. For example, "dd/MM/yyyy HH:mm:ss" is more precise than "dd/MM/yyyy" because the latter loses information about the hours, minutes and seconds.</param> /// <param name="For1904Epoch">True if using 1 Jan 1904 as the date epoch. False if using 1 Jan 1900 as the date epoch. This is independent of the workbook's Date1904 property.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool SetCellValue(int RowIndex, int ColumnIndex, DateTime Data, string Format, bool For1904Epoch) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; } else { c = new SLCell(); uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex); if (iStyleIndex != 0) { c.StyleIndex = iStyleIndex; } } if (For1904Epoch) slwb.WorkbookProperties.Date1904 = true; double fDateTime = SLTool.CalculateDaysFromEpoch(Data, For1904Epoch); // see CalculateDaysFromEpoch to see why there's a difference double fDateCheck = For1904Epoch ? 0.0 : 1.0; if (fDateTime < fDateCheck) { // given datetime is earlier than epoch // So we set date to string format c.DataType = CellValues.SharedString; c.NumericValue = this.DirectSaveToSharedStringTable(Data.ToString(Format)); slws.Cells[pt] = c; } else { c.DataType = CellValues.Number; c.NumericValue = fDateTime; slws.Cells[pt] = c; } return true; }
/// <summary> /// Copy a range of cells from another worksheet to the currently selected worksheet, given the anchor cell of the destination range (top-left cell). /// </summary> /// <param name="WorksheetName">The name of the source worksheet.</param> /// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param> /// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param> /// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param> /// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param> /// <param name="AnchorRowIndex">The row index of the anchor cell.</param> /// <param name="AnchorColumnIndex">The column index of the anchor cell.</param> /// <param name="PasteOption">Paste option.</param> /// <returns>True if successful. False otherwise.</returns> public bool CopyCellFromWorksheet(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, SLPasteTypeValues PasteOption) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; 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 (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase)) { return this.CopyCell(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, AnchorRowIndex, AnchorColumnIndex, false); } string sRelId = string.Empty; foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase)) { sRelId = sheet.Id; break; } } // there has to be a valid existing worksheet if (sRelId.Length == 0) return false; bool result = false; if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit && AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit && AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit) { result = true; WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sRelId); int i, j, iSwap, iStyleIndex, iStyleIndexNew; SLCell origcell, newcell; SLCellPoint pt, newpt; int rowdiff = AnchorRowIndex - iStartRowIndex; int coldiff = AnchorColumnIndex - iStartColumnIndex; Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>(); Dictionary<SLCellPoint, SLCell> sourcecells = new Dictionary<SLCellPoint, SLCell>(); Dictionary<int, uint> sourcecolstyleindex = new Dictionary<int, uint>(); Dictionary<int, uint> sourcerowstyleindex = new Dictionary<int, uint>(); string sCellRef = string.Empty; HashSet<string> hsCellRef = new HashSet<string>(); // I use a hash set on the logic that it's easier to check a string hash (of cell references) // first, rather than load a Cell class into SLCell and then check with row/column indices. for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { sCellRef = SLTool.ToCellReference(i, j); if (!hsCellRef.Contains(sCellRef)) { hsCellRef.Add(sCellRef); } } } // hyperlink ID, URL Dictionary<string, string> hlurl = new Dictionary<string, string>(); List<SLHyperlink> sourcehyperlinks = new List<SLHyperlink>(); foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships) { if (hlrel.IsExternal) { hlurl[hlrel.Id] = hlrel.Uri.OriginalString; } } using (OpenXmlReader oxr = OpenXmlReader.Create(wsp)) { Column col; int iColumnMin, iColumnMax; Row r; Cell c; SLHyperlink hl; while (oxr.Read()) { if (oxr.ElementType == typeof(Column)) { col = (Column)oxr.LoadCurrentElement(); iColumnMin = (int)col.Min.Value; iColumnMax = (int)col.Max.Value; for (i = iColumnMin; i <= iColumnMax; ++i) { sourcecolstyleindex[i] = (col.Style != null) ? col.Style.Value : 0; } } else if (oxr.ElementType == typeof(Row)) { r = (Row)oxr.LoadCurrentElement(); if (r.RowIndex != null) { if (r.StyleIndex != null) sourcerowstyleindex[(int)r.RowIndex.Value] = r.StyleIndex.Value; else sourcerowstyleindex[(int)r.RowIndex.Value] = 0; } using (OpenXmlReader oxrRow = OpenXmlReader.Create(r)) { while (oxrRow.Read()) { if (oxrRow.ElementType == typeof(Cell)) { c = (Cell)oxrRow.LoadCurrentElement(); if (c.CellReference != null) { sCellRef = c.CellReference.Value; if (hsCellRef.Contains(sCellRef)) { origcell = new SLCell(); origcell.FromCell(c); // this should work because hsCellRef already contains valid cell references SLTool.FormatCellReferenceToRowColumnIndex(sCellRef, out i, out j); pt = new SLCellPoint(i, j); sourcecells[pt] = origcell.Clone(); } } } } } } else if (oxr.ElementType == typeof(Hyperlink)) { hl = new SLHyperlink(); hl.FromHyperlink((Hyperlink)oxr.LoadCurrentElement()); sourcehyperlinks.Add(hl); } } } Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>(); Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>(); List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>(); SLRowProperties rp; foreach (int rowindex in rowindexkeys) { rp = slws.RowProperties[rowindex]; rowstyleindex[rowindex] = rp.StyleIndex; } List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>(); SLColumnProperties cp; foreach (int colindex in colindexkeys) { cp = slws.ColumnProperties[colindex]; colstyleindex[colindex] = cp.StyleIndex; } for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); newpt = new SLCellPoint(i + rowdiff, j + coldiff); switch (PasteOption) { case SLPasteTypeValues.Formatting: if (sourcecells.ContainsKey(pt)) { origcell = sourcecells[pt]; if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = origcell.StyleIndex; cells[newpt] = newcell.Clone(); } else { if (origcell.StyleIndex != 0) { // if not the default style, then must create a new // destination cell. newcell = new SLCell(); newcell.StyleIndex = origcell.StyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } else { // else source cell has default style. // Now check if destination cell lies on a row/column // that has non-default style. Remember, we don't have // a destination cell here. iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = 0; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } } else { // else no source cell if (slws.Cells.ContainsKey(newpt)) { iStyleIndex = 0; if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex]; newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = (uint)iStyleIndex; cells[newpt] = newcell.Clone(); } else { // else no source and no destination, so we check for row/column // with non-default styles. iStyleIndex = 0; if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex]; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndex != 0 || iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } break; case SLPasteTypeValues.Formulas: if (sourcecells.ContainsKey(pt)) { origcell = sourcecells[pt]; if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone(); else newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; cells[newpt] = newcell.Clone(); } else { newcell = new SLCell(); if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone(); else newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew; cells[newpt] = newcell.Clone(); } } else { if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.CellText = string.Empty; newcell.DataType = CellValues.Number; cells[newpt] = newcell.Clone(); } // no else because don't have to do anything } break; case SLPasteTypeValues.Paste: if (sourcecells.ContainsKey(pt)) { origcell = sourcecells[pt].Clone(); cells[newpt] = origcell.Clone(); } else { // else the source cell is empty if (slws.Cells.ContainsKey(newpt)) { iStyleIndex = 0; if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex]; if (iStyleIndex != 0) { newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } else { // if the source cell is empty, then direct pasting // means overwrite the existing cell, which is faster // by just removing it. slws.Cells.Remove(newpt); } } else { // else no source and no destination, so we check for row/column // with non-default styles. iStyleIndex = 0; if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex]; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndex != 0 || iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } break; case SLPasteTypeValues.Transpose: newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex); iSwap = newpt.RowIndex; newpt.RowIndex = newpt.ColumnIndex; newpt.ColumnIndex = iSwap; newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff; newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff; // in case say the millionth row is transposed, because we can't have a millionth column. if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit) { // this part is identical to normal paste if (sourcecells.ContainsKey(pt)) { origcell = sourcecells[pt].Clone(); cells[newpt] = origcell.Clone(); } else { // else the source cell is empty if (slws.Cells.ContainsKey(newpt)) { iStyleIndex = 0; if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex]; if (iStyleIndex != 0) { newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } else { // if the source cell is empty, then direct pasting // means overwrite the existing cell, which is faster // by just removing it. slws.Cells.Remove(newpt); } } else { // else no source and no destination, so we check for row/column // with non-default styles. iStyleIndex = 0; if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex]; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndex != 0 || iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } } break; case SLPasteTypeValues.Values: // this part is identical to the formula part, except // for assigning the cell formula part. if (sourcecells.ContainsKey(pt)) { origcell = sourcecells[pt]; if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; cells[newpt] = newcell.Clone(); } else { newcell = new SLCell(); newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew; cells[newpt] = newcell.Clone(); } } else { if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.CellFormula = null; newcell.CellText = string.Empty; newcell.DataType = CellValues.Number; cells[newpt] = newcell.Clone(); } // no else because don't have to do anything } break; } } } int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex; int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex; for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i) { for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j) { // any cell within destination "paste" operation is taken out pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) slws.Cells.Remove(pt); } } foreach (SLCellPoint cellkey in cells.Keys) { origcell = cells[cellkey]; // the source cells are from another worksheet. Don't know how to rearrange any // cell references in cell formulas... slws.Cells[cellkey] = origcell.Clone(); } // See CopyCell() for the behaviour explanation // I'm not going to figure out how to copy merged cells from the source worksheet // and decide under what conditions the existing merged cells in the destination // worksheet should be removed. // So I'm going to just remove any merged cells in the delete range. List<SLMergeCell> mca = this.GetWorksheetMergeCells(); foreach (SLMergeCell mc in mca) { if (mc.StartRowIndex >= AnchorRowIndex && mc.EndRowIndex <= AnchorEndRowIndex && mc.StartColumnIndex >= AnchorColumnIndex && mc.EndColumnIndex <= AnchorEndColumnIndex) { slws.MergeCells.Remove(mc); } } // TODO: conditional formatting and data validations? #region Hyperlinks if (sourcehyperlinks.Count > 0) { // we only care if normal paste or transpose paste. Just like Excel. if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose) { List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>(); SLHyperlink hlCopied; int iOverlapStartRowIndex = 1; int iOverlapStartColumnIndex = 1; int iOverlapEndRowIndex = 1; int iOverlapEndColumnIndex = 1; foreach (SLHyperlink hl in sourcehyperlinks) { // this comes from the separating axis theorem. // See merged cells for more details. // In this case however, we're doing stuff when there's overlapping. if (!(iEndRowIndex < hl.Reference.StartRowIndex || iStartRowIndex > hl.Reference.EndRowIndex || iEndColumnIndex < hl.Reference.StartColumnIndex || iStartColumnIndex > hl.Reference.EndColumnIndex)) { // get the overlapping region iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex); iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex); iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex); iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex); // offset to the correctly pasted region if (PasteOption == SLPasteTypeValues.Paste) { iOverlapStartRowIndex += rowdiff; iOverlapStartColumnIndex += coldiff; iOverlapEndRowIndex += rowdiff; iOverlapEndColumnIndex += coldiff; } else { // can only be transpose. See if check above. if (iOverlapEndRowIndex > SLConstants.ColumnLimit) { // probably won't happen. This means that after transpose, // the end row index will flip to exceed the column limit. // I don't feel like testing how Excel handles this, so // I'm going to just take it as normal paste. iOverlapStartRowIndex += rowdiff; iOverlapStartColumnIndex += coldiff; iOverlapEndRowIndex += rowdiff; iOverlapEndColumnIndex += coldiff; } else { iOverlapStartRowIndex -= iStartRowIndex; iOverlapStartColumnIndex -= iStartColumnIndex; iOverlapEndRowIndex -= iStartRowIndex; iOverlapEndColumnIndex -= iStartColumnIndex; iSwap = iOverlapStartRowIndex; iOverlapStartRowIndex = iOverlapStartColumnIndex; iOverlapStartColumnIndex = iSwap; iSwap = iOverlapEndRowIndex; iOverlapEndRowIndex = iOverlapEndColumnIndex; iOverlapEndColumnIndex = iSwap; iOverlapStartRowIndex += (iStartRowIndex + rowdiff); iOverlapStartColumnIndex += (iStartColumnIndex + coldiff); iOverlapEndRowIndex += (iStartRowIndex + rowdiff); iOverlapEndColumnIndex += (iStartColumnIndex + coldiff); } } hlCopied = new SLHyperlink(); hlCopied = hl.Clone(); hlCopied.IsNew = true; if (hlCopied.IsExternal) { if (hlurl.ContainsKey(hlCopied.Id)) { hlCopied.HyperlinkUri = hlurl[hlCopied.Id]; if (hlCopied.HyperlinkUri.StartsWith(".")) { // assume this is a relative file path such as ../ or ./ hlCopied.HyperlinkUriKind = UriKind.Relative; } else { hlCopied.HyperlinkUriKind = UriKind.Absolute; } hlCopied.Id = string.Empty; } } hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex); copiedhyperlinks.Add(hlCopied); } } if (copiedhyperlinks.Count > 0) { slws.Hyperlinks.AddRange(copiedhyperlinks); } } } #endregion #region Calculation cells if (slwb.CalculationCells.Count > 0) { List<int> listToDelete = new List<int>(); int iRowIndex = -1; int iColumnIndex = -1; for (i = 0; i < slwb.CalculationCells.Count; ++i) { if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID) { iRowIndex = slwb.CalculationCells[i].RowIndex; iColumnIndex = slwb.CalculationCells[i].ColumnIndex; if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex && iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex) { // existing calculation cell lies within destination "paste" operation if (!listToDelete.Contains(i)) listToDelete.Add(i); } } } for (i = listToDelete.Count - 1; i >= 0; --i) { slwb.CalculationCells.RemoveAt(listToDelete[i]); } } #endregion } return result; }
private bool CopyCell(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, bool ToCut, SLPasteTypeValues PasteOption) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; if (StartRowIndex < EndRowIndex) { iStartRowIndex = StartRowIndex; iEndRowIndex = EndRowIndex; } else { iStartRowIndex = EndRowIndex; iEndRowIndex = StartRowIndex; } if (StartColumnIndex < EndColumnIndex) { iStartColumnIndex = StartColumnIndex; iEndColumnIndex = EndColumnIndex; } else { iStartColumnIndex = EndColumnIndex; iEndColumnIndex = StartColumnIndex; } bool result = false; if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit && AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit && AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit && (iStartRowIndex != AnchorRowIndex || iStartColumnIndex != AnchorColumnIndex)) { result = true; int i, j, iSwap, iStyleIndex, iStyleIndexNew; SLCell origcell, newcell; SLCellPoint pt, newpt; int rowdiff = AnchorRowIndex - iStartRowIndex; int coldiff = AnchorColumnIndex - iStartColumnIndex; Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>(); Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>(); Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>(); List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>(); SLRowProperties rp; foreach (int rowindex in rowindexkeys) { rp = slws.RowProperties[rowindex]; rowstyleindex[rowindex] = rp.StyleIndex; } List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>(); SLColumnProperties cp; foreach (int colindex in colindexkeys) { cp = slws.ColumnProperties[colindex]; colstyleindex[colindex] = cp.StyleIndex; } for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); newpt = new SLCellPoint(i + rowdiff, j + coldiff); if (ToCut) { if (slws.Cells.ContainsKey(pt)) { cells[newpt] = slws.Cells[pt].Clone(); slws.Cells.Remove(pt); } } else { switch (PasteOption) { case SLPasteTypeValues.Formatting: if (slws.Cells.ContainsKey(pt)) { origcell = slws.Cells[pt]; if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = origcell.StyleIndex; cells[newpt] = newcell.Clone(); } else { if (origcell.StyleIndex != 0) { // if not the default style, then must create a new // destination cell. newcell = new SLCell(); newcell.StyleIndex = origcell.StyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } else { // else source cell has default style. // Now check if destination cell lies on a row/column // that has non-default style. Remember, we don't have // a destination cell here. iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = 0; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } } else { // else no source cell if (slws.Cells.ContainsKey(newpt)) { iStyleIndex = 0; if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex]; newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = (uint)iStyleIndex; cells[newpt] = newcell.Clone(); } else { // else no source and no destination, so we check for row/column // with non-default styles. iStyleIndex = 0; if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex]; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndex != 0 || iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } break; case SLPasteTypeValues.Formulas: if (slws.Cells.ContainsKey(pt)) { origcell = slws.Cells[pt]; if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone(); else newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; cells[newpt] = newcell.Clone(); } else { newcell = new SLCell(); if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone(); else newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew; cells[newpt] = newcell.Clone(); } } else { if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.CellText = string.Empty; newcell.DataType = CellValues.Number; cells[newpt] = newcell.Clone(); } // no else because don't have to do anything } break; case SLPasteTypeValues.Paste: if (slws.Cells.ContainsKey(pt)) { origcell = slws.Cells[pt].Clone(); cells[newpt] = origcell.Clone(); } else { // else the source cell is empty if (slws.Cells.ContainsKey(newpt)) { iStyleIndex = 0; if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex]; if (iStyleIndex != 0) { newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } else { // if the source cell is empty, then direct pasting // means overwrite the existing cell, which is faster // by just removing it. slws.Cells.Remove(newpt); } } else { // else no source and no destination, so we check for row/column // with non-default styles. iStyleIndex = 0; if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex]; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndex != 0 || iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } break; case SLPasteTypeValues.Transpose: newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex); iSwap = newpt.RowIndex; newpt.RowIndex = newpt.ColumnIndex; newpt.ColumnIndex = iSwap; newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff; newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff; // in case say the millionth row is transposed, because we can't have a millionth column. if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit) { // this part is identical to normal paste if (slws.Cells.ContainsKey(pt)) { origcell = slws.Cells[pt].Clone(); cells[newpt] = origcell.Clone(); } else { // else the source cell is empty if (slws.Cells.ContainsKey(newpt)) { iStyleIndex = 0; if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex]; if (iStyleIndex != 0) { newcell = slws.Cells[newpt].Clone(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } else { // if the source cell is empty, then direct pasting // means overwrite the existing cell, which is faster // by just removing it. slws.Cells.Remove(newpt); } } else { // else no source and no destination, so we check for row/column // with non-default styles. iStyleIndex = 0; if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex]; if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex]; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndex != 0 || iStyleIndexNew != 0) { newcell = new SLCell(); newcell.StyleIndex = (uint)iStyleIndex; newcell.CellText = string.Empty; cells[newpt] = newcell.Clone(); } } } } break; case SLPasteTypeValues.Values: // this part is identical to the formula part, except // for assigning the cell formula part. if (slws.Cells.ContainsKey(pt)) { origcell = slws.Cells[pt]; if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; cells[newpt] = newcell.Clone(); } else { newcell = new SLCell(); newcell.CellFormula = null; newcell.CellText = origcell.CellText; newcell.fNumericValue = origcell.fNumericValue; newcell.DataType = origcell.DataType; iStyleIndexNew = 0; if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex]; if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex]; if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew; cells[newpt] = newcell.Clone(); } } else { if (slws.Cells.ContainsKey(newpt)) { newcell = slws.Cells[newpt].Clone(); newcell.CellFormula = null; newcell.CellText = string.Empty; newcell.DataType = CellValues.Number; cells[newpt] = newcell.Clone(); } // no else because don't have to do anything } break; } } } } int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex; int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex; for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i) { for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { // any cell within destination "paste" operation is taken out slws.Cells.Remove(pt); } } } int iNumberOfRows = iEndRowIndex - iStartRowIndex + 1; if (AnchorRowIndex <= iStartRowIndex) iNumberOfRows = -iNumberOfRows; int iNumberOfColumns = iEndColumnIndex - iStartColumnIndex + 1; if (AnchorColumnIndex <= iStartColumnIndex) iNumberOfColumns = -iNumberOfColumns; foreach (SLCellPoint cellkey in cells.Keys) { origcell = cells[cellkey]; if (PasteOption != SLPasteTypeValues.Transpose) { this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfRows, AnchorColumnIndex, iNumberOfColumns); } else { this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfColumns, AnchorColumnIndex, iNumberOfRows); } slws.Cells[cellkey] = origcell.Clone(); } // TODO: tables! // cutting and pasting into a region with merged cells unmerges the existing merged cells // copying and pasting into a region with merged cells leaves existing merged cells alone. // Why does Excel do that? Don't know. // Will just standardise to leaving existing merged cells alone. List<SLMergeCell> mca = this.GetWorksheetMergeCells(); foreach (SLMergeCell mc in mca) { if (mc.StartRowIndex >= iStartRowIndex && mc.EndRowIndex <= iEndRowIndex && mc.StartColumnIndex >= iStartColumnIndex && mc.EndColumnIndex <= iEndColumnIndex) { if (ToCut) { slws.MergeCells.Remove(mc); } if (PasteOption == SLPasteTypeValues.Transpose) { pt = new SLCellPoint(mc.StartRowIndex - iStartRowIndex, mc.StartColumnIndex - iStartColumnIndex); iSwap = pt.RowIndex; pt.RowIndex = pt.ColumnIndex; pt.ColumnIndex = iSwap; pt.RowIndex = pt.RowIndex + iStartRowIndex + rowdiff; pt.ColumnIndex = pt.ColumnIndex + iStartColumnIndex + coldiff; newpt = new SLCellPoint(mc.EndRowIndex - iStartRowIndex, mc.EndColumnIndex - iStartColumnIndex); iSwap = newpt.RowIndex; newpt.RowIndex = newpt.ColumnIndex; newpt.ColumnIndex = iSwap; newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff; newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff; this.MergeWorksheetCells(pt.RowIndex, pt.ColumnIndex, newpt.RowIndex, newpt.ColumnIndex); } else { this.MergeWorksheetCells(mc.StartRowIndex + rowdiff, mc.StartColumnIndex + coldiff, mc.EndRowIndex + rowdiff, mc.EndColumnIndex + coldiff); } } } // TODO: conditional formatting and data validations? #region Hyperlinks if (slws.Hyperlinks.Count > 0) { if (ToCut) { foreach (SLHyperlink hl in slws.Hyperlinks) { // if hyperlink is completely within copy range if (iStartRowIndex <= hl.Reference.StartRowIndex && hl.Reference.EndRowIndex <= iEndRowIndex && iStartColumnIndex <= hl.Reference.StartColumnIndex && hl.Reference.EndColumnIndex <= iEndColumnIndex) { hl.Reference = new SLCellPointRange(hl.Reference.StartRowIndex + rowdiff, hl.Reference.StartColumnIndex + coldiff, hl.Reference.EndRowIndex + rowdiff, hl.Reference.EndColumnIndex + coldiff); } // else don't change anything (Excel doesn't, so we don't). } } else { // we only care if normal paste or transpose paste. Just like Excel. if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose) { List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>(); SLHyperlink hlCopied; // hyperlink ID, URL Dictionary<string, string> hlurl = new Dictionary<string, string>(); if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) { WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships) { if (hlrel.IsExternal) { hlurl[hlrel.Id] = hlrel.Uri.OriginalString; } } } int iOverlapStartRowIndex = 1; int iOverlapStartColumnIndex = 1; int iOverlapEndRowIndex = 1; int iOverlapEndColumnIndex = 1; foreach (SLHyperlink hl in slws.Hyperlinks) { // this comes from the separating axis theorem. // See merged cells for more details. // In this case however, we're doing stuff when there's overlapping. if (!(iEndRowIndex < hl.Reference.StartRowIndex || iStartRowIndex > hl.Reference.EndRowIndex || iEndColumnIndex < hl.Reference.StartColumnIndex || iStartColumnIndex > hl.Reference.EndColumnIndex)) { // get the overlapping region iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex); iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex); iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex); iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex); // offset to the correctly pasted region if (PasteOption == SLPasteTypeValues.Paste) { iOverlapStartRowIndex += rowdiff; iOverlapStartColumnIndex += coldiff; iOverlapEndRowIndex += rowdiff; iOverlapEndColumnIndex += coldiff; } else { // can only be transpose. See if check above. if (iOverlapEndRowIndex > SLConstants.ColumnLimit) { // probably won't happen. This means that after transpose, // the end row index will flip to exceed the column limit. // I don't feel like testing how Excel handles this, so // I'm going to just take it as normal paste. iOverlapStartRowIndex += rowdiff; iOverlapStartColumnIndex += coldiff; iOverlapEndRowIndex += rowdiff; iOverlapEndColumnIndex += coldiff; } else { iOverlapStartRowIndex -= iStartRowIndex; iOverlapStartColumnIndex -= iStartColumnIndex; iOverlapEndRowIndex -= iStartRowIndex; iOverlapEndColumnIndex -= iStartColumnIndex; iSwap = iOverlapStartRowIndex; iOverlapStartRowIndex = iOverlapStartColumnIndex; iOverlapStartColumnIndex = iSwap; iSwap = iOverlapEndRowIndex; iOverlapEndRowIndex = iOverlapEndColumnIndex; iOverlapEndColumnIndex = iSwap; iOverlapStartRowIndex += (iStartRowIndex + rowdiff); iOverlapStartColumnIndex += (iStartColumnIndex + coldiff); iOverlapEndRowIndex += (iStartRowIndex + rowdiff); iOverlapEndColumnIndex += (iStartColumnIndex + coldiff); } } hlCopied = new SLHyperlink(); hlCopied = hl.Clone(); hlCopied.IsNew = true; if (hlCopied.IsExternal) { if (hlurl.ContainsKey(hlCopied.Id)) { hlCopied.HyperlinkUri = hlurl[hlCopied.Id]; if (hlCopied.HyperlinkUri.StartsWith(".")) { // assume this is a relative file path such as ../ or ./ hlCopied.HyperlinkUriKind = UriKind.Relative; } else { hlCopied.HyperlinkUriKind = UriKind.Absolute; } hlCopied.Id = string.Empty; } } hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex); copiedhyperlinks.Add(hlCopied); } } if (copiedhyperlinks.Count > 0) { slws.Hyperlinks.AddRange(copiedhyperlinks); } } } } #endregion #region Calculation cells if (slwb.CalculationCells.Count > 0) { List<int> listToDelete = new List<int>(); int iRowIndex = -1; int iColumnIndex = -1; for (i = 0; i < slwb.CalculationCells.Count; ++i) { if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID) { iRowIndex = slwb.CalculationCells[i].RowIndex; iColumnIndex = slwb.CalculationCells[i].ColumnIndex; if (ToCut && iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex && iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex) { // just remove because recalculation of cell references is too complicated... if (!listToDelete.Contains(i)) listToDelete.Add(i); } if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex && iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex) { // existing calculation cell lies within destination "paste" operation if (!listToDelete.Contains(i)) listToDelete.Add(i); } } } for (i = listToDelete.Count - 1; i >= 0; --i) { slwb.CalculationCells.RemoveAt(listToDelete[i]); } } #endregion // defined names is hard to calculate... // need to check the row and column indices based on the cell references within. } return result; }
// TODO: Hyperlink cell range /// <summary> /// Insert a hyperlink. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell) { if ((RowIndex < 1) || (RowIndex > SLConstants.RowLimit)) { return(false); } if ((ColumnIndex < 1) || (ColumnIndex > SLConstants.ColumnLimit)) { return(false); } var hl = new SLHyperlink(); hl.IsNew = true; hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex); switch (HyperlinkType) { case SLHyperlinkTypeValues.EmailAddress: hl.IsExternal = true; hl.HyperlinkUri = string.Format("mailto:{0}", Address); hl.HyperlinkUriKind = UriKind.Absolute; break; case SLHyperlinkTypeValues.FilePath: hl.IsExternal = true; hl.HyperlinkUri = Address; // assume if it starts with ../ or ./ it's a relative path. hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute; break; case SLHyperlinkTypeValues.InternalDocumentLink: hl.IsExternal = false; hl.Location = Address; break; case SLHyperlinkTypeValues.Url: hl.IsExternal = true; hl.HyperlinkUri = Address; hl.HyperlinkUriKind = UriKind.Absolute; break; } if (Display == null) { hl.Display = Address; } else { if (Display.Length == 0) { hl.Display = Address; } else { hl.Display = Display; } } if ((ToolTip != null) && (ToolTip.Length > 0)) { hl.ToolTip = ToolTip; } var pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; SLStyle style; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; style = new SLStyle(); if (c.StyleIndex < listStyle.Count) { style.FromHash(listStyle[(int)c.StyleIndex]); } else { style.FromHash(listStyle[0]); } style.SetFontUnderline(UnderlineValues.Single); style.SetFontColor(SLThemeColorIndexValues.Hyperlink); c.StyleIndex = (uint)SaveToStylesheet(style.ToHash()); if (OverwriteExistingCell) { // in case there's a formula c.CellFormula = null; c.DataType = CellValues.SharedString; c.CellText = DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture); } // else don't have to do anything slws.Cells[pt] = c.Clone(); } else { c = new SLCell(); style = new SLStyle(); style.FromHash(listStyle[0]); style.SetFontUnderline(UnderlineValues.Single); style.SetFontColor(SLThemeColorIndexValues.Hyperlink); c.StyleIndex = (uint)SaveToStylesheet(style.ToHash()); c.DataType = CellValues.SharedString; c.CellText = DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture); slws.Cells[pt] = c.Clone(); } slws.Hyperlinks.Add(hl); return(true); }
/// <summary> /// Copy the style of one row to a range of rows. /// </summary> /// <param name="FromRowIndex">The row index of the row to be copied from.</param> /// <param name="ToStartRowIndex">The row index of the start row of the row range. This is typically the top row.</param> /// <param name="ToEndRowIndex">The row index of the end row of the row range. This is typically the bottom row.</param> /// <returns>True if successful. False otherwise.</returns> public bool CopyRowStyle(int FromRowIndex, int ToStartRowIndex, int ToEndRowIndex) { int iStartRowIndex = 1, iEndRowIndex = 1; bool result = false; if (ToStartRowIndex < ToEndRowIndex) { iStartRowIndex = ToStartRowIndex; iEndRowIndex = ToEndRowIndex; } else { iStartRowIndex = ToEndRowIndex; iEndRowIndex = ToStartRowIndex; } if (FromRowIndex >= 1 && FromRowIndex <= SLConstants.RowLimit && iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit) { result = true; uint iStyleIndex = 0; if (slws.RowProperties.ContainsKey(FromRowIndex)) { iStyleIndex = slws.RowProperties[FromRowIndex].StyleIndex; } SLRowProperties rp; int i, j; for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { if (i != FromRowIndex) { if (iStyleIndex == 0) { if (slws.RowProperties.ContainsKey(i)) { slws.RowProperties[i].StyleIndex = 0; } } else { if (slws.RowProperties.ContainsKey(i)) { slws.RowProperties[i].StyleIndex = iStyleIndex; } else { rp = new SLRowProperties(SimpleTheme.ThemeRowHeight); rp.StyleIndex = iStyleIndex; slws.RowProperties[i] = rp; } slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(true, i)); } } } #region copying cell styles SLCell cell; SLCellPoint pt, destpt; for (j = 1; j <= SLConstants.ColumnLimit; ++j) { pt = new SLCellPoint(FromRowIndex, j); if (slws.Cells.ContainsKey(pt)) { for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { if (i != FromRowIndex) { destpt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(destpt)) { slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex; } else { cell = new SLCell(); cell.CellText = string.Empty; cell.StyleIndex = slws.Cells[pt].StyleIndex; slws.Cells[destpt] = cell; } } } } else { for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { if (i != FromRowIndex) { destpt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(destpt)) { slws.Cells[destpt].StyleIndex = iStyleIndex; } // no need else because the default style will take over } } } } #endregion } return result; }
/// <summary> /// Set the style of a range of cells. /// </summary> /// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param> /// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param> /// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param> /// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param> /// <param name="Style">The style to set.</param> /// <returns>True if successful. False otherwise.</returns> public bool SetCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, SLStyle Style) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; if (StartRowIndex < EndRowIndex) { iStartRowIndex = StartRowIndex; iEndRowIndex = EndRowIndex; } else { iStartRowIndex = EndRowIndex; iEndRowIndex = StartRowIndex; } if (StartColumnIndex < EndColumnIndex) { iStartColumnIndex = StartColumnIndex; iEndColumnIndex = EndColumnIndex; } else { iStartColumnIndex = EndColumnIndex; iEndColumnIndex = StartColumnIndex; } bool result = false; if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit) { int iStyleIndex = this.SaveToStylesheet(Style.ToHash()); // default is 0, the default style if (iStyleIndex > 0) { result = true; SLCellPoint pt; SLCell c; int i, j; for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; c.StyleIndex = (uint)iStyleIndex; slws.Cells[pt] = c; } else { c = new SLCell(); c.CellText = string.Empty; c.StyleIndex = (uint)iStyleIndex; slws.Cells[pt] = c; } } } } } return result; }
/// <summary> /// Sort data either by column or row. /// </summary> /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param> /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param> /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param> /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param> /// <param name="SortByColumn">True to sort by column. False to sort by row.</param> /// <param name="SortByIndex">The row or column index of the row or column to be sorted by, depending on <paramref name="SortByColumn"/></param> /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param> public void Sort(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool SortByColumn, int SortByIndex, bool SortAscending) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; 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 < 1) iStartRowIndex = 1; if (iStartColumnIndex < 1) iStartColumnIndex = 1; if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit; if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit; // if the given index is out of the data range, then don't have to sort. if (SortByColumn) { if (SortByIndex < iStartColumnIndex || SortByIndex > iEndColumnIndex) return; } else { if (SortByIndex < iStartRowIndex || SortByIndex > iEndRowIndex) return; } Dictionary<SLCellPoint, SLCell> datacells = new Dictionary<SLCellPoint, SLCell>(); SLCellPoint pt; int i, j; for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { datacells[pt] = slws.Cells[pt].Clone(); slws.Cells.Remove(pt); } } } List<SLSortItem> listNumbers = new List<SLSortItem>(); List<SLSortItem> listText = new List<SLSortItem>(); List<SLSortItem> listBoolean = new List<SLSortItem>(); List<SLSortItem> listEmpty = new List<SLSortItem>(); bool bValue = false; double fValue = 0.0; string sText = string.Empty; SLRstType rst; int index = 0; int iStartIndex = -1; int iEndIndex = -1; if (SortByColumn) { iStartIndex = iStartRowIndex; iEndIndex = iEndRowIndex; } else { iStartIndex = iStartColumnIndex; iEndIndex = iEndColumnIndex; } for (i = iStartIndex; i <= iEndIndex; ++i) { if (SortByColumn) pt = new SLCellPoint(i, SortByIndex); else pt = new SLCellPoint(SortByIndex, i); if (datacells.ContainsKey(pt)) { if (datacells[pt].DataType == CellValues.Number) { if (datacells[pt].CellText != null) { if (double.TryParse(datacells[pt].CellText, out fValue)) { listNumbers.Add(new SLSortItem() { Number = fValue, Index = i }); } else { listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i }); } } else { listNumbers.Add(new SLSortItem() { Number = datacells[pt].NumericValue, Index = i }); } } else if (datacells[pt].DataType == CellValues.SharedString) { index = -1; if (datacells[pt].CellText != null) { if (int.TryParse(datacells[pt].CellText, out index) && index >= 0 && index < listSharedString.Count) { rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); rst.FromSharedStringItem(new SharedStringItem() { InnerXml = listSharedString[index] }); listText.Add(new SLSortItem() { Text = rst.ToPlainString(), Index = i }); } else { listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i }); } } else { index = Convert.ToInt32(datacells[pt].NumericValue); if (index >= 0 && index < listSharedString.Count) { rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); rst.FromSharedStringItem(new SharedStringItem() { InnerXml = listSharedString[index] }); listText.Add(new SLSortItem() { Text = rst.ToPlainString(), Index = i }); } else { listText.Add(new SLSortItem() { Text = datacells[pt].NumericValue.ToString(CultureInfo.InvariantCulture), Index = i }); } } } else if (datacells[pt].DataType == CellValues.Boolean) { if (datacells[pt].CellText != null) { if (double.TryParse(datacells[pt].CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue)) { listBoolean.Add(new SLSortItem() { Number = fValue > 0.5 ? 1.0 : 0.0, Index = i }); } else if (bool.TryParse(datacells[pt].CellText, out bValue)) { listBoolean.Add(new SLSortItem() { Number = bValue ? 1.0 : 0.0, Index = i }); } else { listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i }); } } else { listBoolean.Add(new SLSortItem() { Number = datacells[pt].NumericValue > 0.5 ? 1.0 : 0.0, Index = i }); } } else { listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i }); } } else { listEmpty.Add(new SLSortItem() { Index = i }); } } listNumbers.Sort(new SLSortItemNumberComparer()); if (!SortAscending) listNumbers.Reverse(); listText.Sort(new SLSortItemTextComparer()); if (!SortAscending) listText.Reverse(); listBoolean.Sort(new SLSortItemNumberComparer()); if (!SortAscending) listBoolean.Reverse(); Dictionary<int, int> ReverseIndex = new Dictionary<int,int>(); if (SortAscending) { j = iStartIndex; for (i = 0; i < listNumbers.Count; ++i) { ReverseIndex[listNumbers[i].Index] = j; ++j; } for (i = 0; i < listText.Count; ++i) { ReverseIndex[listText[i].Index] = j; ++j; } for (i = 0; i < listBoolean.Count; ++i) { ReverseIndex[listBoolean[i].Index] = j; ++j; } for (i = 0; i < listEmpty.Count; ++i) { ReverseIndex[listEmpty[i].Index] = j; ++j; } } else { j = iStartIndex; for (i = 0; i < listBoolean.Count; ++i) { ReverseIndex[listBoolean[i].Index] = j; ++j; } for (i = 0; i < listText.Count; ++i) { ReverseIndex[listText[i].Index] = j; ++j; } for (i = 0; i < listNumbers.Count; ++i) { ReverseIndex[listNumbers[i].Index] = j; ++j; } for (i = 0; i < listEmpty.Count; ++i) { ReverseIndex[listEmpty[i].Index] = j; ++j; } } List<SLCellPoint> listCellKeys = datacells.Keys.ToList<SLCellPoint>(); SLCellPoint newpt; for (i = 0; i < listCellKeys.Count; ++i) { pt = listCellKeys[i]; if (SortByColumn) { if (ReverseIndex.ContainsKey(pt.RowIndex)) { newpt = new SLCellPoint(ReverseIndex[pt.RowIndex], pt.ColumnIndex); } else { // shouldn't happen, but just in case... newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex); } } else { if (ReverseIndex.ContainsKey(pt.ColumnIndex)) { newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]); } else { // shouldn't happen, but just in case... newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex); } } slws.Cells[newpt] = datacells[pt]; } }
/// <summary> /// Remove an existing hyperlink. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> public void RemoveHyperlink(int RowIndex, int ColumnIndex) { if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return; if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return; // I'm assuming hyperlinks are tied to just one cell. Apparently, // you can assign a block of cells as the hyperlink. // Excel removes the cells of the hyperlink that are empty. I'm not going to even try... List<SLCellPointRange> listdelete = new List<SLCellPointRange>(); int i, j; WorksheetPart wsp; string sRelId; HyperlinkRelationship hlrel; if (!IsNewWorksheet) { for (i = slws.Hyperlinks.Count - 1; i >= 0; --i) { if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex && RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex && slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex && ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex) { if (slws.Hyperlinks[i].IsNew) { slws.Hyperlinks.RemoveAt(i); } else { if (slws.Hyperlinks[i].IsExternal && slws.Hyperlinks[i].Id != null && slws.Hyperlinks[i].Id.Length > 0) { sRelId = slws.Hyperlinks[i].Id; if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) { wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); hlrel = wsp.HyperlinkRelationships.Where(hlid => hlid.Id == sRelId).FirstOrDefault(); if (hlrel != null) { wsp.DeleteReferenceRelationship(hlrel); } } } slws.Hyperlinks.RemoveAt(i); } listdelete.Add(new SLCellPointRange( slws.Hyperlinks[i].Reference.StartRowIndex, slws.Hyperlinks[i].Reference.StartColumnIndex, slws.Hyperlinks[i].Reference.EndRowIndex, slws.Hyperlinks[i].Reference.EndColumnIndex)); } } } else { // if it's a new worksheet, all hyperlinks are new. // Most importantly, no hyperlink relationships were added, so // we can just remove from the SLWorksheet list. // Start from the end because we'll be deleting. for (i = slws.Hyperlinks.Count - 1; i >= 0; --i) { if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex && RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex && slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex && ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex) { slws.Hyperlinks.RemoveAt(i); listdelete.Add(new SLCellPointRange( slws.Hyperlinks[i].Reference.StartRowIndex, slws.Hyperlinks[i].Reference.StartColumnIndex, slws.Hyperlinks[i].Reference.EndRowIndex, slws.Hyperlinks[i].Reference.EndColumnIndex)); } } } if (listdelete.Count > 0) { // remove hyperlink style SLCell c; SLCellPoint pt; foreach (SLCellPointRange ptrange in listdelete) { for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i) { for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; c.StyleIndex = 0; slws.Cells[pt] = c.Clone(); } } } } } }
/// <summary> /// Clear all cell content within specified rows and columns. If the top-left cell of a merged cell is within specified rows and columns, the merged cell content is also cleared. /// </summary> /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param> /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param> /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param> /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param> /// <returns>True if content has been cleared. False otherwise. If there are no content within specified rows and columns, false is also returned.</returns> public bool ClearCellContent(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; bool result = false; 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 < 1) iStartRowIndex = 1; if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit; if (iStartColumnIndex < 1) iStartColumnIndex = 1; if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit; long iSize = (iEndRowIndex - iStartRowIndex + 1) * (iEndColumnIndex - iStartColumnIndex + 1); int iRowIndex = -1, iColumnIndex = -1; if (iSize <= (long)slws.Cells.Count) { SLCellPoint pt; for (iRowIndex = iStartRowIndex; iRowIndex <= iEndRowIndex; ++iRowIndex) { for (iColumnIndex = iStartColumnIndex; iColumnIndex <= iEndColumnIndex; ++iColumnIndex) { pt = new SLCellPoint(iRowIndex, iColumnIndex); if (slws.Cells.ContainsKey(pt)) { this.ClearCellContentData(pt); result = true; } } } } else { List<SLCellPoint> list = slws.Cells.Keys.ToList<SLCellPoint>(); foreach (SLCellPoint pt in list) { if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex && iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex) { this.ClearCellContentData(pt); result = true; } } } List<int> listToDelete = new List<int>(); int i; for (i = 0; i < slwb.CalculationCells.Count; ++i) { if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID) { iRowIndex = slwb.CalculationCells[i].RowIndex; iColumnIndex = slwb.CalculationCells[i].ColumnIndex; if (iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex && iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex) { if (!listToDelete.Contains(i)) listToDelete.Add(i); } } } for (i = listToDelete.Count - 1; i >= 0; --i) { slwb.CalculationCells.RemoveAt(listToDelete[i]); } return result; }
/// <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; }
private void ClearCellContentData(SLCellPoint pt) { SLCell c = slws.Cells[pt]; c.CellFormula = null; c.DataType = CellValues.Number; c.NumericValue = 0; // if the cell still has attributes (say the style index), then update it // otherwise remove the cell if (c.StyleIndex != 0 || c.CellMetaIndex != 0 || c.ValueMetaIndex != 0 || c.ShowPhonetic != false) { slws.Cells[pt] = c.Clone(); } else { slws.Cells.Remove(pt); } }
/// <summary> /// Clear all cell content within specified rows and columns. If the top-left cell of a merged cell is within specified rows and columns, the merged cell content is also cleared. /// </summary> /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param> /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param> /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param> /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param> /// <returns>True if content has been cleared. False otherwise. If there are no content within specified rows and columns, false is also returned.</returns> public bool ClearCellContent(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; bool result = false; 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 < 1) iStartRowIndex = 1; if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit; if (iStartColumnIndex < 1) iStartColumnIndex = 1; if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit; long iSize = (iEndRowIndex - iStartRowIndex + 1) * (iEndColumnIndex - iStartColumnIndex + 1); int iRowIndex = -1, iColumnIndex = -1; if (iSize <= (long)slws.Cells.Count) { SLCellPoint pt; for (iRowIndex = iStartRowIndex; iRowIndex <= iEndRowIndex; ++iRowIndex) { for (iColumnIndex = iStartColumnIndex; iColumnIndex <= iEndColumnIndex; ++iColumnIndex) { pt = new SLCellPoint(iRowIndex, iColumnIndex); if (slws.Cells.ContainsKey(pt)) { this.ClearCellContentData(pt); result = true; } } } } else { foreach (SLCellPoint pt in slws.Cells.Keys) { if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex && iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex) { this.ClearCellContentData(pt); result = true; } } } return result; }
/// <summary> /// Get the cell value as a string. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <returns>A string cell value.</returns> public string GetCellValueAsString(int RowIndex, int ColumnIndex) { string result = string.Empty; int index = 0; SLRstType rst = new SLRstType(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors); if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (slws.Cells.ContainsKey(pt)) { SLCell c = slws.Cells[pt]; if (c.CellText != null) { if (c.DataType == CellValues.String) { result = c.CellText; } else if (c.DataType == CellValues.SharedString) { try { index = int.Parse(c.CellText); if (index >= 0 && index < listSharedString.Count) { rst.FromHash(listSharedString[index]); result = rst.ToPlainString(); } else { result = SLTool.XmlRead(c.CellText); } } catch { // something terrible just happened. We'll just use whatever's in the cell... result = SLTool.XmlRead(c.CellText); } } //else if (c.DataType == CellValues.InlineString) //{ // // there shouldn't be any inline strings // // because they'd already be transferred to shared strings //} else { result = SLTool.XmlRead(c.CellText); } } else { if (c.DataType == CellValues.Number) { result = c.NumericValue.ToString(CultureInfo.InvariantCulture); } else if (c.DataType == CellValues.SharedString) { index = Convert.ToInt32(c.NumericValue); if (index >= 0 && index < listSharedString.Count) { rst.FromHash(listSharedString[index]); result = rst.ToPlainString(); } else { result = SLTool.XmlRead(c.CellText); } } else if (c.DataType == CellValues.Boolean) { if (c.NumericValue > 0.5) result = "true"; else result = "false"; } } } } return result; }
/// <summary> /// Get the cell value as a System.DateTime value. If the cell value wasn't originally a date/time value, the return value is undetermined. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Format">The format string used to parse the date value in the cell if the date is before the date epoch. A date before the date epoch is stored as a string, so the date precision is only as good as the format string. For example, "dd/MM/yyyy HH:mm:ss" is more precise than "dd/MM/yyyy" because the latter loses information about the hours, minutes and seconds.</param> /// <param name="For1904Epoch">True if using 1 Jan 1904 as the date epoch. False if using 1 Jan 1900 as the date epoch. This is independent of the workbook's Date1904 property.</param> /// <returns>A System.DateTime cell value.</returns> public DateTime GetCellValueAsDateTime(int RowIndex, int ColumnIndex, string Format, bool For1904Epoch) { DateTime dt; if (For1904Epoch) dt = SLConstants.Epoch1904(); else dt = SLConstants.Epoch1900(); // If the cell data type is Number, then it's on or after the epoch. // If it's a string or a shared string, then a string representation of the date // is stored, where the date is before the epoch. Then we parse the string to // get the date. double fDateOffset = 0.0; string sDate = string.Empty; if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (slws.Cells.ContainsKey(pt)) { SLCell c = slws.Cells[pt]; if (c.DataType == CellValues.Number) { if (c.CellText != null) { if (double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fDateOffset)) { dt = SLTool.CalculateDateTimeFromDaysFromEpoch(fDateOffset, For1904Epoch); } } else { dt = SLTool.CalculateDateTimeFromDaysFromEpoch(c.NumericValue, For1904Epoch); } } else if (c.DataType == CellValues.SharedString) { SLRstType rst = new SLRstType(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors); int index = 0; try { if (c.CellText != null) { index = int.Parse(c.CellText); } else { index = Convert.ToInt32(c.NumericValue); } if (index >= 0 && index < listSharedString.Count) { rst.FromHash(listSharedString[index]); sDate = rst.ToPlainString(); if (Format.Length > 0) { dt = DateTime.ParseExact(sDate, Format, CultureInfo.InvariantCulture); } else { dt = DateTime.Parse(sDate, CultureInfo.InvariantCulture); } } // no else part, because there's nothing we can do! // Just return the default date value... } catch { // something terrible just happened. (the shared string index probably // isn't even correct!) Don't do anything... } } else if (c.DataType == CellValues.String) { sDate = c.CellText ?? string.Empty; try { if (Format.Length > 0) { dt = DateTime.ParseExact(sDate, Format, CultureInfo.InvariantCulture); } else { dt = DateTime.Parse(sDate, CultureInfo.InvariantCulture); } } catch { // don't need to do anything. Just return the default date value. // The point is to avoid throwing exceptions. } } } } return dt; }
/// <summary> /// This returns a list of index with pixel lengths. Depending on the type, /// the pixel length is for row heights or column widths /// </summary> /// <param name="IsRow"></param> /// <param name="StartIndex"></param> /// <param name="EndIndex"></param> /// <param name="MaxPixelLength"></param> /// <returns></returns> internal Dictionary<int, int> AutoFitRowColumn(bool IsRow, int StartIndex, int EndIndex, int MaxPixelLength) { int i; Dictionary<int, int> pixellength = new Dictionary<int, int>(); // initialise all to zero first. This also ensures the existence of a dictionary entry. for (i = StartIndex; i <= EndIndex; ++i) { pixellength[i] = 0; } List<SLCellPoint> ptkeys = slws.Cells.Keys.ToList<SLCellPoint>(); SLCell c; string sAutoFitSharedStringCacheKey; string sAutoFitCacheKey; double fCellValue; SLRstType rst; Text txt; Run run; FontSchemeValues vFontScheme; int index; SLStyle style = new SLStyle(); int iStyleIndex; string sFontName; double fFontSize; bool bBold; bool bItalic; bool bStrike; bool bUnderline; System.Drawing.FontStyle drawstyle; System.Drawing.Font ftUsable; string sFormatCode; string sDotNetFormatCode; int iTextRotation; System.Drawing.SizeF szf; string sText; float fWidth; float fHeight; int iPointIndex; bool bSkipAdjustment; SLCellPoint ptCheck; // remove points that are part of merged cells // Merged cells don't factor into autofitting. // Start from end because we will be deleting points. if (slws.MergeCells.Count > 0) { for (i = ptkeys.Count - 1; i >= 0; --i) { ptCheck = ptkeys[i]; foreach (SLMergeCell mc in slws.MergeCells) { if (mc.StartRowIndex <= ptCheck.RowIndex && ptCheck.RowIndex <= mc.EndRowIndex && mc.StartColumnIndex <= ptCheck.ColumnIndex && ptCheck.ColumnIndex <= mc.EndColumnIndex) { ptkeys.RemoveAt(i); break; } } } } HashSet<SLCellPoint> hsFilter = new HashSet<SLCellPoint>(); if (slws.HasAutoFilter) { for (i = slws.AutoFilter.StartColumnIndex; i <= slws.AutoFilter.EndColumnIndex; ++i) { hsFilter.Add(new SLCellPoint(slws.AutoFilter.StartRowIndex, i)); } } if (slws.Tables.Count > 0) { foreach (SLTable t in slws.Tables) { if (t.HasAutoFilter) { for (i = t.AutoFilter.StartColumnIndex; i <= t.AutoFilter.EndColumnIndex; ++i) { ptCheck = new SLCellPoint(t.AutoFilter.StartRowIndex, i); if (!hsFilter.Contains(ptCheck)) { hsFilter.Add(ptCheck); } } } } } // Excel seems to stop the maximum column pixel width at 2300 pixels (at least at 120 DPI). // We need a bitmap of sufficient size because we're rendering the text and measuring it. // 4096 pixels wide should cover the 2300 pixel thing. Note that this is also wider than // typical screen monitors. // 2048 pixels high should also cover most screen monitors' vertical height. // If your text fills up the entire height of your screen, I would say your font size is // too large... // If you're doing this in some distant future where you can do spreadsheets on the // freaking wall with Olympic pool sized screens, feel free to increase the dimensions. using (System.Drawing.Bitmap bm = new System.Drawing.Bitmap(4096, 2048)) { using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bm)) { foreach (SLCellPoint pt in ptkeys) { if (IsRow) iPointIndex = pt.RowIndex; else iPointIndex = pt.ColumnIndex; if (StartIndex <= iPointIndex && iPointIndex <= EndIndex) { c = slws.Cells[pt]; iStyleIndex = (int)c.StyleIndex; // assume if the font cache contains the style index, // the other caches also have it. if (dictAutoFitFontCache.ContainsKey(iStyleIndex)) { ftUsable = dictAutoFitFontCache[iStyleIndex]; sDotNetFormatCode = dictAutoFitFormatCodeCache[iStyleIndex]; sFormatCode = sDotNetFormatCode; iTextRotation = dictAutoFitTextRotationCache[iStyleIndex]; } else { style = new SLStyle(); style.FromHash(listStyle[iStyleIndex]); #region Get style stuff sFontName = SimpleTheme.MinorLatinFont; fFontSize = SLConstants.DefaultFontSize; bBold = false; bItalic = false; bStrike = false; bUnderline = false; drawstyle = System.Drawing.FontStyle.Regular; if (style.HasFont) { if (style.fontReal.HasFontScheme) { if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont; else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont; else if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName; } else { if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName; } if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value; if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true; if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true; if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true; if (style.fontReal.HasUnderline) bUnderline = true; } if (bBold) drawstyle |= System.Drawing.FontStyle.Bold; if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic; if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout; if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline; ftUsable = SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle); sFormatCode = style.FormatCode; sDotNetFormatCode = SLTool.ToDotNetFormatCode(sFormatCode); if (style.HasAlignment && style.alignReal.TextRotation != null) { iTextRotation = style.alignReal.TextRotation.Value; } else { iTextRotation = 0; } #endregion dictAutoFitFontCache[iStyleIndex] = (System.Drawing.Font)ftUsable.Clone(); dictAutoFitFormatCodeCache[iStyleIndex] = sDotNetFormatCode; dictAutoFitTextRotationCache[iStyleIndex] = iTextRotation; } sText = string.Empty; fWidth = 0; fHeight = 0; // must set empty first! Used for checking if shared string and if should set into cache. sAutoFitSharedStringCacheKey = string.Empty; bSkipAdjustment = false; if (c.DataType == CellValues.SharedString) { index = Convert.ToInt32(c.NumericValue); sAutoFitSharedStringCacheKey = string.Format("{0}{1}{2}", index.ToString(CultureInfo.InvariantCulture), SLConstants.AutoFitCacheSeparator, c.StyleIndex.ToString(CultureInfo.InvariantCulture)); if (dictAutoFitSharedStringCache.ContainsKey(sAutoFitSharedStringCacheKey)) { fHeight = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Height; fWidth = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Width; bSkipAdjustment = true; } else if (index >= 0 && index < listSharedString.Count) { rst = new SLRstType(); rst.FromHash(listSharedString[index]); if (rst.istrReal.ChildElements.Count == 1 && rst.istrReal.Text != null) { sText = rst.istrReal.Text.Text.TrimEnd(); sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture)); if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey)) { szf = dictAutoFitTextCache[sAutoFitCacheKey]; fHeight = szf.Height; fWidth = szf.Width; } else { szf = SLTool.MeasureText(bm, g, sText, ftUsable); fHeight = szf.Height; fWidth = szf.Width; dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight); } } else { i = 0; foreach (var child in rst.istrReal.ChildElements.Reverse()) { if (child is Text || child is Run) { if (child is Text) { txt = (Text)child; sText = txt.Text; // the last element has the trailing spaces ignored. Hence the Reverse() above. if (i == 0) sText = sText.TrimEnd(); szf = SLTool.MeasureText(bm, g, sText, ftUsable); if (szf.Height > fHeight) fHeight = szf.Height; fWidth += szf.Width; } else if (child is Run) { sText = string.Empty; sFontName = (ftUsable.Name != null && ftUsable.Name.Length > 0) ? ftUsable.Name : SimpleTheme.MinorLatinFont; fFontSize = ftUsable.SizeInPoints; bBold = ((ftUsable.Style & System.Drawing.FontStyle.Bold) > 0) ? true : false; bItalic = ((ftUsable.Style & System.Drawing.FontStyle.Italic) > 0) ? true : false; bStrike = ((ftUsable.Style & System.Drawing.FontStyle.Strikeout) > 0) ? true : false; bUnderline = ((ftUsable.Style & System.Drawing.FontStyle.Underline) > 0) ? true : false; drawstyle = System.Drawing.FontStyle.Regular; run = (Run)child; sText = run.Text.Text; vFontScheme = FontSchemeValues.None; #region Run properties if (run.RunProperties != null) { foreach (var grandchild in run.RunProperties.ChildElements) { if (grandchild is RunFont) { sFontName = ((RunFont)grandchild).Val; } else if (grandchild is FontSize) { fFontSize = ((FontSize)grandchild).Val; } else if (grandchild is Bold) { Bold b = (Bold)grandchild; if (b.Val == null) bBold = true; else bBold = b.Val.Value; } else if (grandchild is Italic) { Italic itlc = (Italic)grandchild; if (itlc.Val == null) bItalic = true; else bItalic = itlc.Val.Value; } else if (grandchild is Strike) { Strike strk = (Strike)grandchild; if (strk.Val == null) bStrike = true; else bStrike = strk.Val.Value; } else if (grandchild is Underline) { Underline und = (Underline)grandchild; if (und.Val == null) { bUnderline = true; } else { if (und.Val.Value != UnderlineValues.None) bUnderline = true; else bUnderline = false; } } else if (grandchild is FontScheme) { vFontScheme = ((FontScheme)grandchild).Val; } } } #endregion if (vFontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont; else if (vFontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont; if (bBold) drawstyle |= System.Drawing.FontStyle.Bold; if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic; if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout; if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline; // the last element has the trailing spaces ignored. Hence the Reverse() above. if (i == 0) sText = sText.TrimEnd(); szf = SLTool.MeasureText(bm, g, sText, SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle)); if (szf.Height > fHeight) fHeight = szf.Height; fWidth += szf.Width; } ++i; } } } } } else { if (c.DataType == CellValues.Number) { #region Numbers if (sDotNetFormatCode.Length > 0) { if (c.CellText != null) { if (!double.TryParse(c.CellText, out fCellValue)) { fCellValue = 0; } if (sFormatCode.Equals("@")) { sText = fCellValue.ToString(CultureInfo.InvariantCulture); } else { sText = SLTool.ToSampleDisplayFormat(fCellValue, sDotNetFormatCode); } } else { if (sFormatCode.Equals("@")) { sText = c.NumericValue.ToString(CultureInfo.InvariantCulture); } else { sText = SLTool.ToSampleDisplayFormat(c.NumericValue, sDotNetFormatCode); } } } else { if (c.CellText != null) { if (!double.TryParse(c.CellText, out fCellValue)) { fCellValue = 0; } sText = SLTool.ToSampleDisplayFormat(fCellValue, "G10"); } else { sText = SLTool.ToSampleDisplayFormat(c.NumericValue, "G10"); } } #endregion } else if (c.DataType == CellValues.Boolean) { if (c.NumericValue > 0.5) sText = "TRUE"; else sText = "FALSE"; } else { if (c.CellText != null) sText = c.CellText; else sText = string.Empty; } sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture)); if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey)) { szf = dictAutoFitTextCache[sAutoFitCacheKey]; fHeight = szf.Height; fWidth = szf.Width; } else { szf = SLTool.MeasureText(bm, g, sText, ftUsable); fHeight = szf.Height; fWidth = szf.Width; dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight); } } if (!bSkipAdjustment) { // Through empirical experimental data, it appears that there's still a bit of padding // at the end of the column when autofitting column widths. I don't know how to // calculate this padding. So I guess. I experimented with the widths of obvious // characters such as a space, an exclamation mark, a period. // Then I remember there's the documentation on the Open XML class property // Column.Width, which says there's an extra 5 pixels, 2 pixels on the left/right // and a pixel for the gridlines. // Note that this padding appears to change depending on the font/typeface and // font size used. (Haha... where have I seen this before...) So 5 pixels doesn't // seem to work exactly. Or maybe it's wrong because the method of measuring isn't // what Excel actually uses to measure the text. // Since we're autofitting, it seems fitting (haha) that the column width is slightly // larger to accomodate the text. So it's best to err on the larger side. // Thus we add 7 instead of the "recommended" or "documented" 5 pixels, 1 extra pixel // on the left and right. fWidth += 7; // I could also have used 8, but it might have been too much of an extra padding. // The number 8 is a lucky number in Chinese culture. Goodness knows I need some // luck figuring out what Excel is doing... if (iTextRotation != 0) { szf = SLTool.CalculateOuterBoundsOfRotatedRectangle(fWidth, fHeight, iTextRotation); fHeight = szf.Height; fWidth = szf.Width; } // meaning the shared string portion was accessed (otherwise it'd be empty string) if (sAutoFitSharedStringCacheKey.Length > 0) { dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey] = new System.Drawing.SizeF(fWidth, fHeight); } } if (IsRow) { if (fHeight > MaxPixelLength) fHeight = MaxPixelLength; if (pixellength[iPointIndex] < fHeight) { pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fHeight)); } } else { if (hsFilter.Contains(pt)) fWidth += SLConstants.AutoFilterPixelWidth; if (fWidth > MaxPixelLength) fWidth = MaxPixelLength; if (pixellength[iPointIndex] < fWidth) { pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fWidth)); } } } } // end of Graphics } } return pixellength; }
/// <summary> /// Sort data either by column or row. /// </summary> /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param> /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param> /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param> /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param> /// <param name="SortByColumn">True to sort by column. False to sort by row.</param> /// <param name="SortByIndex"> /// The row or column index of the row or column to be sorted by, depending on /// <paramref name="SortByColumn" /> /// </param> /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param> public void Sort(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool SortByColumn, int SortByIndex, bool SortAscending) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; 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 < 1) { iStartRowIndex = 1; } if (iStartColumnIndex < 1) { iStartColumnIndex = 1; } if (iEndRowIndex > SLConstants.RowLimit) { iEndRowIndex = SLConstants.RowLimit; } if (iEndColumnIndex > SLConstants.ColumnLimit) { iEndColumnIndex = SLConstants.ColumnLimit; } // if the given index is out of the data range, then don't have to sort. if (SortByColumn) { if ((SortByIndex < iStartColumnIndex) || (SortByIndex > iEndColumnIndex)) { return; } } else { if ((SortByIndex < iStartRowIndex) || (SortByIndex > iEndRowIndex)) { return; } } var datacells = new Dictionary <SLCellPoint, SLCell>(); SLCellPoint pt; int i, j; for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { datacells[pt] = slws.Cells[pt].Clone(); slws.Cells.Remove(pt); } } } var listNumbers = new List <SLSortItem>(); var listText = new List <SLSortItem>(); var listBoolean = new List <SLSortItem>(); var listEmpty = new List <SLSortItem>(); var bValue = false; var fValue = 0.0; var sText = string.Empty; SLRstType rst; var index = 0; var iStartIndex = -1; var iEndIndex = -1; if (SortByColumn) { iStartIndex = iStartRowIndex; iEndIndex = iEndRowIndex; } else { iStartIndex = iStartColumnIndex; iEndIndex = iEndColumnIndex; } for (i = iStartIndex; i <= iEndIndex; ++i) { if (SortByColumn) { pt = new SLCellPoint(i, SortByIndex); } else { pt = new SLCellPoint(SortByIndex, i); } if (datacells.ContainsKey(pt)) { if (datacells[pt].DataType == CellValues.Number) { if (datacells[pt].CellText != null) { if (double.TryParse(datacells[pt].CellText, out fValue)) { listNumbers.Add(new SLSortItem { Number = fValue, Index = i }); } else { listText.Add(new SLSortItem { Text = datacells[pt].CellText, Index = i }); } } else { listNumbers.Add(new SLSortItem { Number = datacells[pt].NumericValue, Index = i }); } } else if (datacells[pt].DataType == CellValues.SharedString) { index = -1; if (datacells[pt].CellText != null) { if (int.TryParse(datacells[pt].CellText, out index) && (index >= 0) && (index < listSharedString.Count)) { rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List <Color>(), new List <Color>()); rst.FromSharedStringItem(new SharedStringItem { InnerXml = listSharedString[index] }); listText.Add(new SLSortItem { Text = rst.ToPlainString(), Index = i }); } else { listText.Add(new SLSortItem { Text = datacells[pt].CellText, Index = i }); } } else { index = Convert.ToInt32(datacells[pt].NumericValue); if ((index >= 0) && (index < listSharedString.Count)) { rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List <Color>(), new List <Color>()); rst.FromSharedStringItem(new SharedStringItem { InnerXml = listSharedString[index] }); listText.Add(new SLSortItem { Text = rst.ToPlainString(), Index = i }); } else { listText.Add(new SLSortItem { Text = datacells[pt].NumericValue.ToString(CultureInfo.InvariantCulture), Index = i }); } } } else if (datacells[pt].DataType == CellValues.Boolean) { if (datacells[pt].CellText != null) { if (double.TryParse(datacells[pt].CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue)) { listBoolean.Add(new SLSortItem { Number = fValue > 0.5 ? 1.0 : 0.0, Index = i }); } else if (bool.TryParse(datacells[pt].CellText, out bValue)) { listBoolean.Add(new SLSortItem { Number = bValue ? 1.0 : 0.0, Index = i }); } else { listText.Add(new SLSortItem { Text = datacells[pt].CellText, Index = i }); } } else { listBoolean.Add(new SLSortItem { Number = datacells[pt].NumericValue > 0.5 ? 1.0 : 0.0, Index = i }); } } else { listText.Add(new SLSortItem { Text = datacells[pt].CellText, Index = i }); } } else { listEmpty.Add(new SLSortItem { Index = i }); } } listNumbers.Sort(new SLSortItemNumberComparer()); if (!SortAscending) { listNumbers.Reverse(); } listText.Sort(new SLSortItemTextComparer()); if (!SortAscending) { listText.Reverse(); } listBoolean.Sort(new SLSortItemNumberComparer()); if (!SortAscending) { listBoolean.Reverse(); } var ReverseIndex = new Dictionary <int, int>(); if (SortAscending) { j = iStartIndex; for (i = 0; i < listNumbers.Count; ++i) { ReverseIndex[listNumbers[i].Index] = j; ++j; } for (i = 0; i < listText.Count; ++i) { ReverseIndex[listText[i].Index] = j; ++j; } for (i = 0; i < listBoolean.Count; ++i) { ReverseIndex[listBoolean[i].Index] = j; ++j; } for (i = 0; i < listEmpty.Count; ++i) { ReverseIndex[listEmpty[i].Index] = j; ++j; } } else { j = iStartIndex; for (i = 0; i < listBoolean.Count; ++i) { ReverseIndex[listBoolean[i].Index] = j; ++j; } for (i = 0; i < listText.Count; ++i) { ReverseIndex[listText[i].Index] = j; ++j; } for (i = 0; i < listNumbers.Count; ++i) { ReverseIndex[listNumbers[i].Index] = j; ++j; } for (i = 0; i < listEmpty.Count; ++i) { ReverseIndex[listEmpty[i].Index] = j; ++j; } } var listCellKeys = datacells.Keys.ToList(); SLCellPoint newpt; for (i = 0; i < listCellKeys.Count; ++i) { pt = listCellKeys[i]; if (SortByColumn) { if (ReverseIndex.ContainsKey(pt.RowIndex)) { newpt = new SLCellPoint(ReverseIndex[pt.RowIndex], pt.ColumnIndex); } else { newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex); } } else { if (ReverseIndex.ContainsKey(pt.ColumnIndex)) { newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]); } else { newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex); } } slws.Cells[newpt] = datacells[pt]; } }
private bool SetCellValueNumberFinal(int RowIndex, int ColumnIndex, bool IsNumeric, double NumericValue, string NumberData) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; } else { c = new SLCell(); uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex); if (iStyleIndex != 0) { c.StyleIndex = iStyleIndex; } } c.DataType = CellValues.Number; if (IsNumeric) c.NumericValue = NumericValue; else c.CellText = NumberData; slws.Cells[pt] = c; return true; }
// TODO: Hyperlink cell range /// <summary> /// Insert a hyperlink. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell) { if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return false; if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return false; SLHyperlink hl = new SLHyperlink(); hl.IsNew = true; hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex); switch (HyperlinkType) { case SLHyperlinkTypeValues.EmailAddress: hl.IsExternal = true; hl.HyperlinkUri = string.Format("mailto:{0}", Address); hl.HyperlinkUriKind = UriKind.Absolute; break; case SLHyperlinkTypeValues.FilePath: hl.IsExternal = true; hl.HyperlinkUri = Address; // assume if it starts with ../ or ./ it's a relative path. hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute; break; case SLHyperlinkTypeValues.InternalDocumentLink: hl.IsExternal = false; hl.Location = Address; break; case SLHyperlinkTypeValues.Url: hl.IsExternal = true; hl.HyperlinkUri = Address; hl.HyperlinkUriKind = UriKind.Absolute; break; } if (Display == null) { hl.Display = Address; } else { if (Display.Length == 0) { hl.Display = Address; } else { hl.Display = Display; } } if (ToolTip != null && ToolTip.Length > 0) hl.ToolTip = ToolTip; SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; SLStyle style; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; style = new SLStyle(); if (c.StyleIndex < listStyle.Count) style.FromHash(listStyle[(int)c.StyleIndex]); else style.FromHash(listStyle[0]); style.SetFontUnderline(UnderlineValues.Single); style.SetFontColor(SLThemeColorIndexValues.Hyperlink); c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash()); if (OverwriteExistingCell) { // in case there's a formula c.CellFormula = null; c.DataType = CellValues.SharedString; c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture); } // else don't have to do anything slws.Cells[pt] = c.Clone(); } else { c = new SLCell(); style = new SLStyle(); style.FromHash(listStyle[0]); style.SetFontUnderline(UnderlineValues.Single); style.SetFontColor(SLThemeColorIndexValues.Hyperlink); c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash()); c.DataType = CellValues.SharedString; c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture); slws.Cells[pt] = c.Clone(); } slws.Hyperlinks.Add(hl); return true; }
/// <summary> /// Set the cell value given the row index and column index. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Data">The cell value data. Try the SLRstType class for easy InlineString generation.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool SetCellValue(int RowIndex, int ColumnIndex, InlineString Data) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; } else { c = new SLCell(); uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex); if (iStyleIndex != 0) { c.StyleIndex = iStyleIndex; } } c.DataType = CellValues.SharedString; c.NumericValue = this.DirectSaveToSharedStringTable(Data); slws.Cells[pt] = c; return true; }
/// <summary> /// Copy the style of one column to a range of columns. /// </summary> /// <param name="FromColumnIndex">The column index of the column to be copied from.</param> /// <param name="ToStartColumnIndex">The column index of the start column of the column range. This is typically the left-most column.</param> /// <param name="ToEndColumnIndex">The column index of the end column of the column range. This is typically the right-most column.</param> /// <returns>True if successful. False otherwise.</returns> public bool CopyColumnStyle(int FromColumnIndex, int ToStartColumnIndex, int ToEndColumnIndex) { int iStartColumnIndex = 1, iEndColumnIndex = 1; bool result = false; if (ToStartColumnIndex < ToEndColumnIndex) { iStartColumnIndex = ToStartColumnIndex; iEndColumnIndex = ToEndColumnIndex; } else { iStartColumnIndex = ToEndColumnIndex; iEndColumnIndex = ToStartColumnIndex; } if (FromColumnIndex >= 1 && FromColumnIndex <= SLConstants.ColumnLimit && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit) { result = true; uint iStyleIndex = 0; if (slws.ColumnProperties.ContainsKey(FromColumnIndex)) { iStyleIndex = slws.ColumnProperties[FromColumnIndex].StyleIndex; } SLColumnProperties cp; int i, j; for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i) { if (i != FromColumnIndex) { if (iStyleIndex == 0) { if (slws.ColumnProperties.ContainsKey(i)) { slws.ColumnProperties[i].StyleIndex = 0; } } else { if (slws.ColumnProperties.ContainsKey(i)) { slws.ColumnProperties[i].StyleIndex = iStyleIndex; } else { cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize); cp.StyleIndex = iStyleIndex; slws.ColumnProperties[i] = cp; } slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i)); } } } #region copying cell styles SLCell cell; SLCellPoint pt, destpt; for (i = 1; i <= SLConstants.RowLimit; ++i) { pt = new SLCellPoint(i, FromColumnIndex); if (slws.Cells.ContainsKey(pt)) { if (slws.Cells[pt].StyleIndex > 0) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { if (j != FromColumnIndex) { destpt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(destpt)) { slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex; } else { cell = new SLCell(); cell.CellText = string.Empty; cell.StyleIndex = slws.Cells[pt].StyleIndex; slws.Cells[destpt] = cell; } } } } } else { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { if (j != FromColumnIndex) { destpt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(destpt)) { slws.Cells[destpt].StyleIndex = iStyleIndex; } // no need else because the default style will take over } } } } #endregion } return result; }
/// <summary> /// Set the cell value given the row index and column index. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Data">The cell value data.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool SetCellValue(int RowIndex, int ColumnIndex, string Data) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; } else { // if there's no existing cell, then we don't have to assign // a new cell when the data string is empty if (Data == null || Data.Length == 0) return true; c = new SLCell(); uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex); if (iStyleIndex != 0) { c.StyleIndex = iStyleIndex; } } if (Data == null || Data.Length == 0) { c.DataType = CellValues.Number; c.CellText = string.Empty; slws.Cells[pt] = c; } else if (Data.StartsWith("=")) { // in case it's just one equal sign if (Data.Equals("=", StringComparison.InvariantCultureIgnoreCase)) { c.DataType = CellValues.SharedString; c.NumericValue = this.DirectSaveToSharedStringTable("="); slws.Cells[pt] = c; } else { // For simplicity, we're gonna assume that if it starts with an equal sign, it's a formula. // TODO Formula calculation engine c.DataType = CellValues.Number; //c.Formula = new CellFormula(slxe.Write(Data.Substring(1))); c.CellFormula = new SLCellFormula(); c.CellFormula.FormulaText = SLTool.XmlWrite(Data.Substring(1)); c.CellText = string.Empty; slws.Cells[pt] = c; } } else if (Data.StartsWith("'")) { c.DataType = CellValues.SharedString; c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(Data.Substring(1))); slws.Cells[pt] = c; } else { c.DataType = CellValues.SharedString; c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(Data)); slws.Cells[pt] = c; } return true; }
/// <summary> /// Remove the style from a range of cells. /// </summary> /// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param> /// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param> /// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param> /// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param> public void RemoveCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; if (StartRowIndex < EndRowIndex) { iStartRowIndex = StartRowIndex; iEndRowIndex = EndRowIndex; } else { iStartRowIndex = EndRowIndex; iEndRowIndex = StartRowIndex; } if (StartColumnIndex < EndColumnIndex) { iStartColumnIndex = StartColumnIndex; iEndColumnIndex = EndColumnIndex; } else { iStartColumnIndex = EndColumnIndex; iEndColumnIndex = StartColumnIndex; } SLCellPoint pt; SLCell c; int i, j; for (i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; c.StyleIndex = 0; slws.Cells[pt] = c; } } } }
/// <summary> /// Get the cell value as a boolean. If the cell value wasn't originally a boolean value, the return value is undetermined (but is by default false). /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <returns>A boolean cell value.</returns> public bool GetCellValueAsBoolean(int RowIndex, int ColumnIndex) { bool result = false; if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (slws.Cells.ContainsKey(pt)) { SLCell c = slws.Cells[pt]; if (c.DataType == CellValues.Boolean) { double fValue = 0; if (c.CellText != null) { if (double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue)) { if (fValue > 0.5) result = true; else result = false; } else { bool.TryParse(c.CellText, out result); } } else { if (c.NumericValue > 0.5) result = true; else result = false; } } } } return result; }
/// <summary> /// Copy the style of one cell to a range of cells. /// </summary> /// <param name="FromRowIndex">The row index of the cell to be copied from.</param> /// <param name="FromColumnIndex">The column index of the cell to be copied from.</param> /// <param name="ToStartRowIndex">The row index of the starting cell of the cell range. This is typically the top-left cell.</param> /// <param name="ToStartColumnIndex">The column index of the starting cell of the cell range. This is typically the top-left cell.</param> /// <param name="ToEndRowIndex">The row index of the ending cell of the cell range. This is typically the bottom-right cell.</param> /// <param name="ToEndColumnIndex">The column index of the ending cell of the cell range. This is typically the bottom-right cell.</param> /// <returns>True if successful. False otherwise.</returns> public bool CopyCellStyle(int FromRowIndex, int FromColumnIndex, int ToStartRowIndex, int ToStartColumnIndex, int ToEndRowIndex, int ToEndColumnIndex) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; bool result = false; if (ToStartRowIndex < ToEndRowIndex) { iStartRowIndex = ToStartRowIndex; iEndRowIndex = ToEndRowIndex; } else { iStartRowIndex = ToEndRowIndex; iEndRowIndex = ToStartRowIndex; } if (ToStartColumnIndex < ToEndColumnIndex) { iStartColumnIndex = ToStartColumnIndex; iEndColumnIndex = ToEndColumnIndex; } else { iStartColumnIndex = ToEndColumnIndex; iEndColumnIndex = ToStartColumnIndex; } if (SLTool.CheckRowColumnIndexLimit(FromRowIndex, FromColumnIndex) && SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex) && SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex)) { result = true; uint iStyleIndex = 0; SLCellPoint pt = new SLCellPoint(FromRowIndex, FromColumnIndex); if (slws.Cells.ContainsKey(pt)) { iStyleIndex = slws.Cells[pt].StyleIndex; } SLCell c; // we'll just overwrite any existing styles, instead of merging // like when we're copying row/column styles. for (int i = iStartRowIndex; i <= iEndRowIndex; ++i) { for (int j = iStartColumnIndex; j <= iEndColumnIndex; ++j) { if (i != FromRowIndex && j != FromColumnIndex) { pt = new SLCellPoint(i, j); // so default style if (iStyleIndex == 0) { if (slws.Cells.ContainsKey(pt)) { slws.Cells[pt].StyleIndex = 0; } } else { if (slws.Cells.ContainsKey(pt)) { slws.Cells[pt].StyleIndex = iStyleIndex; } else { c = new SLCell(); c.CellText = string.Empty; c.StyleIndex = iStyleIndex; slws.Cells[pt] = c; } } } } } } return result; }
/// <summary> /// Get the cell value as a double precision floating point number. If the cell value wasn't originally a floating point number, the return value is undetermined (but is by default 0). /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <returns>A double precision floating point number cell value.</returns> public double GetCellValueAsDouble(int RowIndex, int ColumnIndex) { double result = 0.0; if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (slws.Cells.ContainsKey(pt)) { SLCell c = slws.Cells[pt]; if (c.DataType == CellValues.Number) { if (c.CellText != null) { double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out result); } else { result = c.NumericValue; } } } } return result; }
/// <summary> /// Remove an existing hyperlink. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> public void RemoveHyperlink(int RowIndex, int ColumnIndex) { if ((RowIndex < 1) || (RowIndex > SLConstants.RowLimit)) { return; } if ((ColumnIndex < 1) || (ColumnIndex > SLConstants.ColumnLimit)) { return; } // I'm assuming hyperlinks are tied to just one cell. Apparently, // you can assign a block of cells as the hyperlink. // Excel removes the cells of the hyperlink that are empty. I'm not going to even try... var listdelete = new List <SLCellPointRange>(); int i, j; WorksheetPart wsp; string sRelId; HyperlinkRelationship hlrel; if (!IsNewWorksheet) { for (i = slws.Hyperlinks.Count - 1; i >= 0; --i) { if ((slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex) && (RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex) && (slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex) && (ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)) { if (slws.Hyperlinks[i].IsNew) { slws.Hyperlinks.RemoveAt(i); } else { if (slws.Hyperlinks[i].IsExternal && (slws.Hyperlinks[i].Id != null) && (slws.Hyperlinks[i].Id.Length > 0)) { sRelId = slws.Hyperlinks[i].Id; if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) { wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); hlrel = wsp.HyperlinkRelationships.Where(hlid => hlid.Id == sRelId).FirstOrDefault(); if (hlrel != null) { wsp.DeleteReferenceRelationship(hlrel); } } } slws.Hyperlinks.RemoveAt(i); } listdelete.Add(new SLCellPointRange( slws.Hyperlinks[i].Reference.StartRowIndex, slws.Hyperlinks[i].Reference.StartColumnIndex, slws.Hyperlinks[i].Reference.EndRowIndex, slws.Hyperlinks[i].Reference.EndColumnIndex)); } else { for (i = slws.Hyperlinks.Count - 1; i >= 0; --i) { if ((slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex) && (RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex) && (slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex) && (ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)) { slws.Hyperlinks.RemoveAt(i); listdelete.Add(new SLCellPointRange( slws.Hyperlinks[i].Reference.StartRowIndex, slws.Hyperlinks[i].Reference.StartColumnIndex, slws.Hyperlinks[i].Reference.EndRowIndex, slws.Hyperlinks[i].Reference.EndColumnIndex)); } } } } } if (listdelete.Count > 0) { // remove hyperlink style SLCell c; SLCellPoint pt; foreach (var ptrange in listdelete) { for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i) { for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; c.StyleIndex = 0; slws.Cells[pt] = c.Clone(); } } } } } }
/// <summary> /// Creates an instance of SLTable, given row and column indices of opposite cells in a cell range. /// </summary> /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param> /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param> /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param> /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param> /// <returns>An SLTable with the required information.</returns> public SLTable CreateTable(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; 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 < 1) iStartRowIndex = 1; if (iStartRowIndex == SLConstants.RowLimit) iStartRowIndex = SLConstants.RowLimit - 1; if (iStartColumnIndex < 1) iStartColumnIndex = 1; // consider minus 1 in case there's a totals row so there's less checking... if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit; if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit; if (iEndRowIndex <= iStartRowIndex) iEndRowIndex = iStartRowIndex + 1; SLTable tbl = new SLTable(); tbl.SetAllNull(); slwb.RefreshPossibleTableId(); tbl.Id = slwb.PossibleTableId; tbl.DisplayName = string.Format("Table{0}", tbl.Id); tbl.Name = tbl.DisplayName; tbl.StartRowIndex = iStartRowIndex; tbl.StartColumnIndex = iStartColumnIndex; tbl.EndRowIndex = iEndRowIndex; tbl.EndColumnIndex = iEndColumnIndex; tbl.AutoFilter.StartRowIndex = tbl.StartRowIndex; tbl.AutoFilter.StartColumnIndex = tbl.StartColumnIndex; tbl.AutoFilter.EndRowIndex = tbl.EndRowIndex; tbl.AutoFilter.EndColumnIndex = tbl.EndColumnIndex; tbl.HasAutoFilter = true; SLTableColumn tc; uint iColumnId = 1; int i, index; uint j; SLCell c; SLCellPoint pt; string sHeaderText = string.Empty; SharedStringItem ssi; SLRstType rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); for (i = tbl.StartColumnIndex; i <= tbl.EndColumnIndex; ++i) { pt = new SLCellPoint(StartRowIndex, i); sHeaderText = string.Empty; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; if (c.CellText == null) { if (c.DataType == CellValues.Number) sHeaderText = c.NumericValue.ToString(CultureInfo.InvariantCulture); else if (c.DataType == CellValues.Boolean) sHeaderText = c.NumericValue > 0.5 ? "TRUE" : "FALSE"; else sHeaderText = string.Empty; } else { sHeaderText = c.CellText; } if (c.DataType == CellValues.SharedString) { index = -1; if (c.CellText != null) { if (int.TryParse(c.CellText, out index)) { index = -1; } } else { index = Convert.ToInt32(c.NumericValue); } if (index >= 0 && index < listSharedString.Count) { ssi = new SharedStringItem(); ssi.InnerXml = listSharedString[index]; rst.FromSharedStringItem(ssi); sHeaderText = rst.ToPlainString(); } } } j = iColumnId; if (sHeaderText.Length == 0) { sHeaderText = string.Format("Column{0}", j); } while (tbl.TableNames.Contains(sHeaderText)) { ++j; sHeaderText = string.Format("Column{0}", j); } tc = new SLTableColumn(); tc.Id = iColumnId; tc.Name = sHeaderText; tbl.TableColumns.Add(tc); tbl.TableNames.Add(sHeaderText); ++iColumnId; } tbl.TableStyleInfo.ShowFirstColumn = false; tbl.TableStyleInfo.ShowLastColumn = false; tbl.TableStyleInfo.ShowRowStripes = true; tbl.TableStyleInfo.ShowColumnStripes = false; tbl.HasTableStyleInfo = true; return tbl; }
private List<SLC.SLDataSeries> FillChartDataSeries(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool RowsAsDataSeries, bool ShowHiddenData) { List<SLC.SLDataSeries> series = new List<SLC.SLDataSeries>(); int i, j; SLCell c; SLCellPoint pt; Dictionary<int, bool> HiddenRows = new Dictionary<int, bool>(); Dictionary<int, bool> HiddenColumns = new Dictionary<int, bool>(); Dictionary<SLCellPoint, SLCell> cellstore = new Dictionary<SLCellPoint, SLCell>(); #region GetCells for (i = StartRowIndex; i <= EndRowIndex; ++i) { HiddenRows[i] = false; } for (j = StartColumnIndex; j <= EndColumnIndex; ++j) { HiddenColumns[j] = false; } bool bFound = false; string sWorksheetRelID = string.Empty; if (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase)) { bFound = false; } else { bFound = false; foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase)) { bFound = true; sWorksheetRelID = sheet.Id; break; } } } if (bFound) { Dictionary<string, SLCellPoint> cellref = new Dictionary<string, SLCellPoint>(); for (i = StartRowIndex; i <= EndRowIndex; ++i) { for (j = StartColumnIndex; j <= EndColumnIndex; ++j) { pt = new SLCellPoint(i, j); cellref[SLTool.ToCellReference(i, j)] = pt; } } WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sWorksheetRelID); Row row; Column column; Cell cell; if (!ShowHiddenData) { // running it twice because Row contains Cell classes and it's easier this way using (OpenXmlReader oxr = OpenXmlReader.Create(wsp)) { while (oxr.Read()) { if (oxr.ElementType == typeof(Row)) { row = (Row)oxr.LoadCurrentElement(); if (row.RowIndex != null) { foreach (var rowindex in HiddenRows.Keys) { if (row.RowIndex.Value == rowindex && row.Hidden != null && row.Hidden.Value) { HiddenRows[rowindex] = true; } } } } } } } using (OpenXmlReader oxr = OpenXmlReader.Create(wsp)) { while (oxr.Read()) { if (oxr.ElementType == typeof(Column)) { if (!ShowHiddenData) { column = (Column)oxr.LoadCurrentElement(); foreach (var colindex in HiddenColumns.Keys) { if (column.Min <= colindex && colindex <= column.Max && column.Hidden != null && column.Hidden.Value) { HiddenColumns[colindex] = true; } } } } else if (oxr.ElementType == typeof(Cell)) { cell = (Cell)oxr.LoadCurrentElement(); if (cell.CellReference != null) { if (cellref.ContainsKey(cell.CellReference.Value)) { c = new SLCell(); c.FromCell(cell); cellstore[cellref[cell.CellReference.Value]] = c.Clone(); } } } } } } else { SLRowProperties rp; SLColumnProperties cp; if (!ShowHiddenData) { for (j = StartColumnIndex; j <= EndColumnIndex; ++j) { if (slws.ColumnProperties.ContainsKey(j)) { cp = slws.ColumnProperties[j]; if (cp.Hidden) HiddenColumns[j] = true; } } } for (i = StartRowIndex; i <= EndRowIndex; ++i) { if (!ShowHiddenData && slws.RowProperties.ContainsKey(i)) { rp = slws.RowProperties[i]; if (rp.Hidden) HiddenRows[i] = true; } for (j = StartColumnIndex; j <= EndColumnIndex; ++j) { pt = new SLCellPoint(i, j); if (slws.Cells.ContainsKey(pt)) { cellstore[pt] = slws.Cells[pt].Clone(); } } } } #endregion int index = 0; int index2 = 0; string sCellValue; string sFormatCode; SLC.SLDataSeries ser; SLC.SLStringReference sr; SLC.SLNumberReference nr; SLStyle style; bool bIsStringReference = true; // we're going to assume that the format code applies to all in the "category" cells. string sAxisFormatCode = string.Empty; SLC.SLAxisDataSourceType cat = new SLC.SLAxisDataSourceType(); if (RowsAsDataSeries) { bIsStringReference = true; sAxisFormatCode = SLConstants.NumberFormatGeneral; pt = new SLCellPoint(StartRowIndex, StartColumnIndex + 1); if (cellstore.ContainsKey(pt)) { // dates are also numbers, so we lump it together if (cellstore[pt].DataType == CellValues.Number) { bIsStringReference = false; style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]); this.TranslateStylesToStyleIds(ref style); sAxisFormatCode = style.FormatCode; } } sr = new SLC.SLStringReference(); nr = new SLC.SLNumberReference(); if (bIsStringReference) { cat.UseStringReference = true; sr = new SLC.SLStringReference(); sr.WorksheetName = WorksheetName; sr.StartRowIndex = StartRowIndex; sr.StartColumnIndex = StartColumnIndex + 1; sr.EndRowIndex = StartRowIndex; sr.EndColumnIndex = EndColumnIndex; sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex); } else { cat.UseNumberReference = true; nr.WorksheetName = WorksheetName; nr.StartRowIndex = StartRowIndex; nr.StartColumnIndex = StartColumnIndex + 1; nr.EndRowIndex = StartRowIndex; nr.EndColumnIndex = EndColumnIndex; nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex); nr.NumberingCache.FormatCode = sAxisFormatCode; } index2 = 0; // if the header row is hidden, I don't know what to do... for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j) { if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j]) { pt = new SLCellPoint(StartRowIndex, j); sCellValue = string.Empty; if (cellstore.ContainsKey(pt)) { c = cellstore[pt]; sCellValue = this.GetCellTrueValue(c); if (bIsStringReference) { sr.Points.Add(new SLC.SLStringPoint() { Index = (uint)index2, NumericValue = sCellValue }); } else { nr.NumberingCache.Points.Add(new SLC.SLNumericPoint() { Index = (uint)index2, NumericValue = sCellValue }); } } ++index2; } } if (bIsStringReference) { sr.PointCount = (uint)index2; cat.StringReference = sr; } else { nr.NumberingCache.PointCount = (uint)index2; cat.NumberReference = nr; } index = 0; for (i = StartRowIndex + 1; i <= EndRowIndex; ++i) { if (HiddenRows.ContainsKey(i) && !HiddenRows[i]) { ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors); ser.Index = (uint)index; ser.Order = (uint)index; ser.IsStringReference = true; sr = new SLC.SLStringReference(); pt = new SLCellPoint(i, StartColumnIndex); sCellValue = string.Empty; if (cellstore.ContainsKey(pt)) { c = cellstore[pt]; sCellValue = this.GetCellTrueValue(c); } sr.WorksheetName = WorksheetName; sr.StartRowIndex = i; sr.StartColumnIndex = StartColumnIndex; sr.EndRowIndex = i; sr.EndColumnIndex = StartColumnIndex; sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex); sr.PointCount = 1; sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue }); ser.StringReference = sr; ser.AxisData = cat.Clone(); ser.NumberData.UseNumberReference = true; nr = new SLC.SLNumberReference(); nr.WorksheetName = WorksheetName; nr.StartRowIndex = i; nr.StartColumnIndex = StartColumnIndex + 1; nr.EndRowIndex = i; nr.EndColumnIndex = EndColumnIndex; nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex + 1, i, EndColumnIndex); nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral; index2 = 0; for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j) { if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j]) { pt = new SLCellPoint(i, j); sCellValue = string.Empty; sFormatCode = string.Empty; if (cellstore.ContainsKey(pt)) { c = cellstore[pt]; sCellValue = this.GetCellTrueValue(c); style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); style.FromHash(listStyle[(int)c.StyleIndex]); this.TranslateStylesToStyleIds(ref style); if (style.HasNumberingFormat) sFormatCode = style.FormatCode; nr.NumberingCache.Points.Add(new SLC.SLNumericPoint() { FormatCode = sFormatCode, Index = (uint)index2, NumericValue = sCellValue }); } ++index2; } } nr.NumberingCache.PointCount = (uint)index2; ser.NumberData.NumberReference = nr; series.Add(ser); ++index; } } // end of rows as data series } else { bIsStringReference = true; sAxisFormatCode = SLConstants.NumberFormatGeneral; pt = new SLCellPoint(StartRowIndex + 1, StartColumnIndex); if (cellstore.ContainsKey(pt)) { // dates are also numbers, so we lump it together if (cellstore[pt].DataType == CellValues.Number) { bIsStringReference = false; style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]); this.TranslateStylesToStyleIds(ref style); sAxisFormatCode = style.FormatCode; } } sr = new SLC.SLStringReference(); nr = new SLC.SLNumberReference(); if (bIsStringReference) { cat.UseStringReference = true; sr.WorksheetName = WorksheetName; sr.StartRowIndex = StartRowIndex + 1; sr.StartColumnIndex = StartColumnIndex; sr.EndRowIndex = EndRowIndex; sr.EndColumnIndex = StartColumnIndex; sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex); } else { cat.UseNumberReference = true; nr.WorksheetName = WorksheetName; nr.StartRowIndex = StartRowIndex + 1; nr.StartColumnIndex = StartColumnIndex; nr.EndRowIndex = EndRowIndex; nr.EndColumnIndex = StartColumnIndex; nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex); nr.NumberingCache.FormatCode = sAxisFormatCode; } index2 = 0; // if the header column is hidden, I don't know what to do... for (i = StartRowIndex + 1; i <= EndRowIndex; ++i) { if (HiddenRows.ContainsKey(i) && !HiddenRows[i]) { pt = new SLCellPoint(i, StartColumnIndex); sCellValue = string.Empty; if (cellstore.ContainsKey(pt)) { c = cellstore[pt]; sCellValue = this.GetCellTrueValue(c); if (bIsStringReference) { sr.Points.Add(new SLC.SLStringPoint() { Index = (uint)index2, NumericValue = sCellValue }); } else { nr.NumberingCache.Points.Add(new SLC.SLNumericPoint() { Index = (uint)index2, NumericValue = sCellValue }); } } ++index2; } } if (bIsStringReference) { sr.PointCount = (uint)index2; cat.StringReference = sr; } else { nr.NumberingCache.PointCount = (uint)index2; cat.NumberReference = nr; } index = 0; for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j) { if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j]) { ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors); ser.Index = (uint)index; ser.Order = (uint)index; ser.IsStringReference = true; sr = new SLC.SLStringReference(); pt = new SLCellPoint(StartRowIndex, j); sCellValue = string.Empty; if (cellstore.ContainsKey(pt)) { c = cellstore[pt]; sCellValue = this.GetCellTrueValue(c); } sr.WorksheetName = WorksheetName; sr.StartRowIndex = StartRowIndex; sr.StartColumnIndex = j; sr.EndRowIndex = StartRowIndex; sr.EndColumnIndex = j; sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, j); sr.PointCount = 1; sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue }); ser.StringReference = sr; ser.AxisData = cat.Clone(); ser.NumberData.UseNumberReference = true; nr = new SLC.SLNumberReference(); nr.WorksheetName = WorksheetName; nr.StartRowIndex = StartRowIndex + 1; nr.StartColumnIndex = j; nr.EndRowIndex = EndRowIndex; nr.EndColumnIndex = j; nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, j, EndRowIndex, j); nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral; index2 = 0; for (i = StartRowIndex + 1; i <= EndRowIndex; ++i) { if (HiddenRows.ContainsKey(i) && !HiddenRows[i]) { pt = new SLCellPoint(i, j); sCellValue = string.Empty; sFormatCode = string.Empty; if (cellstore.ContainsKey(pt)) { c = cellstore[pt]; sCellValue = this.GetCellTrueValue(c); style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); style.FromHash(listStyle[(int)c.StyleIndex]); this.TranslateStylesToStyleIds(ref style); if (style.HasNumberingFormat) sFormatCode = style.FormatCode; nr.NumberingCache.Points.Add(new SLC.SLNumericPoint() { FormatCode = sFormatCode, Index = (uint)index2, NumericValue = sCellValue }); } ++index2; } } nr.NumberingCache.PointCount = (uint)index2; ser.NumberData.NumberReference = nr; series.Add(ser); ++index; } } // end of columns as data series } return series; }
/// <summary> /// Set the cell value given the row index and column index. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <param name="Data">The cell value data.</param> /// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns> public bool SetCellValue(int RowIndex, int ColumnIndex, bool Data) { if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { return false; } SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); SLCell c; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; } else { c = new SLCell(); if (slws.RowProperties.ContainsKey(RowIndex)) { c.StyleIndex = slws.RowProperties[RowIndex].StyleIndex; } else if (slws.ColumnProperties.ContainsKey(ColumnIndex)) { c.StyleIndex = slws.ColumnProperties[ColumnIndex].StyleIndex; } } c.DataType = CellValues.Boolean; c.NumericValue = Data ? 1 : 0; slws.Cells[pt] = c; return true; }
/// <summary> /// Get the cell's style. The default style is returned if cell doesn't have an existing style, or if the row or column indices are invalid. /// </summary> /// <param name="RowIndex">The row index.</param> /// <param name="ColumnIndex">The column index.</param> /// <returns>The cell's style.</returns> public SLStyle GetCellStyle(int RowIndex, int ColumnIndex) { bool bFound = false; SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors); if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex)) { SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex); if (slws.Cells.ContainsKey(pt)) { SLCell c = slws.Cells[pt]; bFound = true; style.FromHash(listStyle[(int)c.StyleIndex]); } } if (!bFound) { style.FromHash(listStyle[0]); } return style; }