internal int DirectSaveToSharedStringTable(string Data) { var index = 0; string sHash; if (SLTool.ToPreserveSpace(Data)) { sHash = string.Format("<x:t xml:space=\"preserve\">{0}</x:t>", Data); } else { sHash = string.Format("<x:t>{0}</x:t>", Data); } if (dictSharedStringHash.ContainsKey(sHash)) { index = dictSharedStringHash[sHash]; } else { index = listSharedString.Count; listSharedString.Add(sHash); dictSharedStringHash[sHash] = index; } return(index); }
internal void FromCalculationCell(CalculationCell cc) { SetAllNull(); var iRowIndex = -1; var iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(cc.CellReference.Value, out iRowIndex, out iColumnIndex)) { RowIndex = iRowIndex; ColumnIndex = iColumnIndex; } SheetId = cc.SheetId ?? 0; if (cc.InChildChain != null) { InChildChain = cc.InChildChain.Value; } if (cc.NewLevel != null) { NewLevel = cc.NewLevel.Value; } if (cc.NewThread != null) { NewThread = cc.NewThread.Value; } if (cc.Array != null) { Array = cc.Array.Value; } }
private void SetDisplayColor() { clrDisplay = Color.FromArgb(255, 0, 0, 0); var index = 0; if (Theme != null) { index = (int)Theme.Value; if ((index >= 0) && (index < listThemeColors.Count)) { clrDisplay = Color.FromArgb(255, listThemeColors[index]); if (Tint != null) { clrDisplay = SLTool.ToColor(clrDisplay, Tint.Value); } } } else if (Rgb != null) { clrDisplay = SLTool.ToColor(Rgb); } else if (Indexed != null) { index = (int)Indexed.Value; if ((index >= 0) && (index < listIndexedColors.Count)) { clrDisplay = Color.FromArgb(255, listIndexedColors[index]); } } }
internal X14.ConditionalFormatting ToConditionalFormatting() { var cf = new X14.ConditionalFormatting(); // otherwise xm:f and xm:seqref becomes xne:f and xne:seqref cf.AddNamespaceDeclaration("xm", SLConstants.NamespaceXm); // how come sparklines don't need explicit namespace declarations? if (Pivot) { cf.Pivot = Pivot; } int i; for (i = 0; i < Rules.Count; ++i) { cf.Append(Rules[i].ToConditionalFormattingRule()); } if (ReferenceSequence.Count > 0) { cf.Append(new ReferenceSequence(SLTool.TranslateCellPointRangeToRefSeq(ReferenceSequence))); } return(cf); }
internal void FromConditionalFormatting(X14.ConditionalFormatting cf) { SetAllNull(); if (cf.Pivot != null) { Pivot = cf.Pivot.Value; } using (var oxr = OpenXmlReader.Create(cf)) { while (oxr.Read()) { SLConditionalFormattingRule2010 cfr; if (oxr.ElementType == typeof(X14.ConditionalFormattingRule)) { cfr = new SLConditionalFormattingRule2010(); cfr.FromConditionalFormattingRule((X14.ConditionalFormattingRule)oxr.LoadCurrentElement()); Rules.Add(cfr); } else if (oxr.ElementType == typeof(ReferenceSequence)) { var refseq = (ReferenceSequence)oxr.LoadCurrentElement(); ReferenceSequence = SLTool.TranslateRefSeqToCellPointRange(refseq); } } } }
internal CalculationCell ToCalculationCell() { var cc = new CalculationCell(); cc.CellReference = SLTool.ToCellReference(RowIndex, ColumnIndex); cc.SheetId = SheetId; if ((InChildChain != null) && InChildChain.Value) { cc.InChildChain = InChildChain.Value; } if ((NewLevel != null) && NewLevel.Value) { cc.NewLevel = NewLevel.Value; } if ((NewThread != null) && NewThread.Value) { cc.NewThread = NewThread.Value; } if ((Array != null) && Array.Value) { cc.Array = Array.Value; } return(cc); }
/// <summary> /// Form a SLMergeCell from a DocumentFormat.OpenXml.Spreadsheet.MergeCell class. /// </summary> /// <param name="mc">The source DocumentFormat.OpenXml.Spreadsheet.MergeCell class.</param> public void FromMergeCell(MergeCell mc) { string sStartCell = string.Empty, sEndCell = string.Empty; var index = 0; bool bStartSuccess = false, bEndSuccess = false; IsValid = false; if (mc.Reference != null) { index = mc.Reference.Value.IndexOf(":"); // if "A1:C3", then the index must be at least at the 3rd position (or index 2) if (index >= 2) { sStartCell = mc.Reference.Value.Substring(0, index); sEndCell = mc.Reference.Value.Substring(index + 1); bStartSuccess = SLTool.FormatCellReferenceToRowColumnIndex(sStartCell, out iStartRowIndex, out iStartColumnIndex); bEndSuccess = SLTool.FormatCellReferenceToRowColumnIndex(sEndCell, out iEndRowIndex, out iEndColumnIndex); if (bStartSuccess && bEndSuccess) { IsValid = true; } } } }
internal Location ToLocation() { var loc = new Location(); if ((Reference.StartRowIndex == Reference.EndRowIndex) && (Reference.StartColumnIndex == Reference.EndColumnIndex)) { loc.Reference = SLTool.ToCellReference(Reference.StartRowIndex, Reference.StartColumnIndex); } else { loc.Reference = SLTool.ToCellRange(Reference.StartRowIndex, Reference.StartColumnIndex, Reference.EndRowIndex, Reference.EndColumnIndex); } loc.FirstHeaderRow = FirstHeaderRow; loc.FirstDataRow = FirstDataRow; loc.FirstDataColumn = FirstDataColumn; if (RowPageCount != 0) { loc.RowPageCount = RowPageCount; } if (ColumnsPerPage != 0) { loc.ColumnsPerPage = ColumnsPerPage; } return(loc); }
internal Hyperlink ToHyperlink() { var hl = new Hyperlink(); hl.Reference = SLTool.ToCellRange(Reference.StartRowIndex, Reference.StartColumnIndex, Reference.EndRowIndex, Reference.EndColumnIndex); if ((Id != null) && (Id.Length > 0)) { hl.Id = Id; } if ((Location != null) && (Location.Length > 0)) { hl.Location = Location; } if ((ToolTip != null) && (ToolTip.Length > 0)) { hl.Tooltip = ToolTip; } if ((Display != null) && (Display.Length > 0)) { hl.Display = Display; } return(hl); }
/// <summary> /// Form a SLMergeCell given a corner cell of the to-be-merged rectangle of cells, and the opposite corner cell. For /// example, the top-left corner cell and the bottom-right corner cell. Or the bottom-left corner cell and the /// top-right corner cell. /// </summary> /// <param name="StartRowIndex">The row index of the corner cell.</param> /// <param name="StartColumnIndex">The column index of the corner cell.</param> /// <param name="EndRowIndex">The row index of the opposite corner cell.</param> /// <param name="EndColumnIndex">The column index of the opposite corner cell.</param> public void FromIndices(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex) { 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 == iEndRowIndex) && (iStartColumnIndex == iEndColumnIndex)) { IsValid = false; } else { IsValid = SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex) && SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex); } }
internal void FromHyperlink(Hyperlink hl) { SetAllNull(); IsNew = false; if (hl.Reference != null) { Reference = SLTool.TranslateReferenceToCellPointRange(hl.Reference.Value); } if (hl.Id != null) { // At least I think if there's a relationship ID, it's an external link. IsExternal = true; Id = hl.Id.Value; } if (hl.Location != null) { Location = hl.Location.Value; } if (hl.Tooltip != null) { ToolTip = hl.Tooltip.Value; } if (hl.Display != null) { Display = hl.Display.Value; } }
internal void FromLocation(Location loc) { SetAllNull(); if (loc.Reference != null) { Reference = SLTool.TranslateReferenceToCellPointRange(loc.Reference.Value); } if (loc.FirstHeaderRow != null) { FirstHeaderRow = loc.FirstHeaderRow.Value; } if (loc.FirstDataRow != null) { FirstDataRow = loc.FirstDataRow.Value; } if (loc.FirstDataColumn != null) { FirstDataColumn = loc.FirstDataColumn.Value; } if (loc.RowPageCount != null) { RowPageCount = loc.RowPageCount.Value; } if (loc.ColumnsPerPage != null) { ColumnsPerPage = loc.ColumnsPerPage.Value; } }
/// <summary> /// Allow date values. /// </summary> /// <param name="DataOperator">The type of operation.</param> /// <param name="DataValue"> /// The data value. Any valid date formatted value is fine. It is suggested to just copy the value /// you have in Excel interface. /// </param> /// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param> public void AllowDate(SLDataValidationSingleOperandValues DataOperator, string DataValue, bool IgnoreBlank) { Type = DataValidationValues.Date; Operator = TranslateOperatorValues(DataOperator); DateTime dt; if (DataValue.StartsWith("=")) { Formula1 = DataValue.Substring(1); } else { if (DateTime.TryParse(DataValue, out dt)) { Formula1 = SLTool.CalculateDaysFromEpoch(dt, Date1904).ToString(CultureInfo.InvariantCulture); } else { Formula1 = "1"; } } Formula2 = string.Empty; AllowBlank = IgnoreBlank; }
internal void ForceSaveToSharedStringTable(SharedStringItem ssi) { var index = listSharedString.Count; var sHash = SLTool.RemoveNamespaceDeclaration(ssi.InnerXml); listSharedString.Add(sHash); dictSharedStringHash[sHash] = index; }
/// <summary> /// Get the width of the specified column. /// </summary> /// <param name="Unit">The unit of the width to be returned.</param> /// <param name="ColumnName">The column name, such as "A".</param> /// <returns>The width in the specified unit type.</returns> public double GetWidth(SLMeasureUnitTypeValues Unit, string ColumnName) { var iColumnIndex = -1; iColumnIndex = SLTool.ToColumnIndex(ColumnName); return(GetWidth(Unit, iColumnIndex, iColumnIndex)); }
/// <summary> /// Allow date values. /// </summary> /// <param name="DataOperator">The type of operation.</param> /// <param name="DataValue"> /// The data value. Any valid date formatted value is fine. It is suggested to just copy the value /// you have in Excel interface. /// </param> /// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param> public void AllowDate(SLDataValidationSingleOperandValues DataOperator, DateTime DataValue, bool IgnoreBlank) { Type = DataValidationValues.Date; Operator = TranslateOperatorValues(DataOperator); Formula1 = SLTool.CalculateDaysFromEpoch(DataValue, Date1904).ToString(CultureInfo.InvariantCulture); Formula2 = string.Empty; AllowBlank = IgnoreBlank; }
/// <summary> /// Allow date values. /// </summary> /// <param name="IsBetween">True if the data is between 2 values. False otherwise.</param> /// <param name="Minimum">The minimum value.</param> /// <param name="Maximum">The maximum value.</param> /// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param> public void AllowDate(bool IsBetween, DateTime Minimum, DateTime Maximum, bool IgnoreBlank) { Type = DataValidationValues.Date; Operator = IsBetween ? DataValidationOperatorValues.Between : DataValidationOperatorValues.NotBetween; Formula1 = SLTool.CalculateDaysFromEpoch(Minimum, Date1904).ToString(CultureInfo.InvariantCulture); Formula2 = SLTool.CalculateDaysFromEpoch(Maximum, Date1904).ToString(CultureInfo.InvariantCulture); AllowBlank = IgnoreBlank; }
/// <summary> /// Remove an existing hyperlink. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> public void RemoveHyperlink(string CellReference) { var iRowIndex = -1; var iColumnIndex = -1; SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex); RemoveHyperlink(iRowIndex, iColumnIndex); }
/// <summary> /// Get the width of the specified columns. /// </summary> /// <param name="Unit">The unit of the width to be returned.</param> /// <param name="StartColumnName">The column name of the start column.</param> /// <param name="EndColumnName">The column name of the end column.</param> /// <returns>The width in the specified unit type.</returns> public double GetWidth(SLMeasureUnitTypeValues Unit, string StartColumnName, string EndColumnName) { var iStartColumnIndex = -1; var iEndColumnIndex = -1; iStartColumnIndex = SLTool.ToColumnIndex(StartColumnName); iEndColumnIndex = SLTool.ToColumnIndex(EndColumnName); return(GetWidth(Unit, iStartColumnIndex, iEndColumnIndex)); }
/// <summary> /// Import a text file as a data source, with the first data row and first data column at a specific cell. /// </summary> /// <param name="FileName">The file name.</param> /// <param name="AnchorCellReference">The anchor cell reference, such as "A1".</param> public void ImportText(string FileName, string AnchorCellReference) { var iRowIndex = -1; var iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex)) { ImportText(FileName, iRowIndex, iColumnIndex, null); } }
/// <summary> /// Form a DocumentFormat.OpenXml.Spreadsheet.MergeCell class from this SLMergeCell class. /// </summary> /// <returns>A DocumentFormat.OpenXml.Spreadsheet.MergeCell class.</returns> public MergeCell ToMergeCell() { var mc = new MergeCell(); var sStartCell = SLTool.ToCellReference(iStartRowIndex, iStartColumnIndex); var sEndCell = SLTool.ToCellReference(iEndRowIndex, iEndColumnIndex); mc.Reference = string.Format("{0}:{1}", sStartCell, sEndCell); return(mc); }
/// <summary> /// Insert a hyperlink. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</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> /// <returns>True if successful. False otherwise.</returns> public bool InsertHyperlink(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address) { var iRowIndex = -1; var iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return(false); } return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, null, null, false)); }
/// <summary> /// Adds a print area to the existing print area on the currently selected worksheet given a corner cell of the print /// area and the opposite corner cell. /// </summary> /// <param name="StartCellReference">The cell reference of the corner cell, such as "A1".</param> /// <param name="EndCellReference">The cell reference of the opposite corner cell, such as "A1".</param> public void AddToPrintArea(string StartCellReference, string EndCellReference) { var iStartRowIndex = -1; var iStartColumnIndex = -1; var iEndRowIndex = -1; var iEndColumnIndex = -1; SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex); SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex); SetAddPrintArea(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true); }
/// <summary> /// Insert comment given the cell reference of the cell it's based on. This will overwrite any existing comment. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> /// <param name="Comment">The cell comment.</param> /// <returns>False if the cell reference is invalid. True otherwise.</returns> public bool InsertComment(string CellReference, SLComment Comment) { var iRowIndex = -1; var iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return(false); } return(InsertComment(iRowIndex, iColumnIndex, Comment)); }
// merging spreadsheets is kinda like importing data, right? So it's here then. // public void MergeSpreadsheet(string SpreadsheetFileName) // { // this.MergeSpreadsheet(true, SpreadsheetFileName, null); // } // public void MergeSpreadsheet(Stream SpreadsheetStream) // { // this.MergeSpreadsheet(false, null, SpreadsheetStream); // } // private void MergeSpreadsheet(bool IsFile, string SpreadsheetFileName, Stream SpreadsheetStream) // { // using (MemoryStream msAnother = new MemoryStream()) // { // if (IsFile) // { // byte[] baData = File.ReadAllBytes(SpreadsheetFileName); // msAnother.Write(baData, 0, baData.Length); // } // else // { // SpreadsheetStream.Position = 0; // byte[] baData = new byte[SpreadsheetStream.Length]; // SpreadsheetStream.Read(baData, 0, baData.Length); // msAnother.Write(baData, 0, baData.Length); // } // using (SpreadsheetDocument xlAnother = SpreadsheetDocument.Open(msAnother, false)) // { // HashSet<string> hsCurrentSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase); // foreach (SLSheet sheet in slwb.Sheets) // { // // current sheet names supposed to be unique, so I'm not checking for collisions. // hsCurrentSheetNames.Add(sheet.SheetName); // } // HashSet<string> hsAnotherSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase); // List<string> listAnotherSheetNames = new List<string>(); // using (OpenXmlReader oxr = OpenXmlReader.Create(xlAnother.WorkbookPart.Workbook.Sheets)) // { // string sSheetName; // while (oxr.Read()) // { // if (oxr.ElementType == typeof(Sheet)) // { // sSheetName = ((Sheet)oxr.LoadCurrentElement()).SheetName.Value; // hsAnotherSheetNames.Add(sSheetName); // listAnotherSheetNames.Add(sSheetName); // } // } // } ////Sheet1 ////Sheet2 ////Sheet3 ////Sheet1 -> Sheet7 -> Sheet8 ////Sheet6 ////Sheet7 // Dictionary<string, string> dictAnotherNewSheetNames = new Dictionary<string, string>(); // foreach (string s in listAnotherSheetNames) // { // } // } // // end of using SpreadsheetDocument // } // } /// <summary> /// Import a System.Data.DataTable as a data source, with the first data row and first data column at a specific cell. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</param> /// <param name="Data">The data table.</param> /// <param name="IncludeHeader"> /// True if the data table's column names are to be used in the first row as a header row. /// False otherwise. /// </param> public void ImportDataTable(string CellReference, DataTable Data, bool IncludeHeader) { var iRowIndex = -1; var iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return; } ImportDataTable(iRowIndex, iColumnIndex, Data, IncludeHeader); }
/// <summary> /// Set the corresponding value axis to cross this axis at a given date. This is for date axis. WARNING: Internally, this is used for category, date and value axes. Remember to set the axis type. /// </summary> /// <param name="DateToBeCrossed">Date to cross at.</param> public void SetOtherAxisCrossing(DateTime DateToBeCrossed) { this.OtherAxisIsCrosses = false; this.OtherAxisCrosses = C.CrossesValues.AutoZero; this.OtherAxisCrossesAt = SLTool.CalculateDaysFromEpoch(DateToBeCrossed, this.Date1904); // the given date is before the epochs (1900 or 1904). // Just set to whatever the current epoch is being used. if (this.OtherAxisCrossesAt < 0.0) { this.OtherAxisCrossesAt = this.Date1904 ? 0.0 : 1.0; } }
internal SLCalculationCell(string CellReference) { SetAllNull(); var iRowIndex = -1; var iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { RowIndex = iRowIndex; ColumnIndex = iColumnIndex; } }
/// <summary> /// Sort data by row. /// </summary> /// <param name="StartCellReference"> /// The cell reference of the start cell of the cell range to be sorted, such as "A1". /// This is typically the top-left cell. /// </param> /// <param name="EndCellReference"> /// The cell reference of the end cell of the cell range to be sorted, such as "A1". This is /// typically the bottom-right cell. /// </param> /// <param name="SortByRowIndex">The row index of the row to be sorted by.</param> /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param> public void Sort(string StartCellReference, string EndCellReference, int SortByRowIndex, bool SortAscending) { var iStartRowIndex = -1; var iStartColumnIndex = -1; var iEndRowIndex = -1; var iEndColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex) && SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex)) { Sort(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, false, SortByRowIndex, SortAscending); } }
/// <summary> /// Insert a hyperlink. /// </summary> /// <param name="CellReference">The cell reference, such as "A1".</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(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell) { var iRowIndex = -1; var iColumnIndex = -1; if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex)) { return(false); } return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, Display, ToolTip, OverwriteExistingCell)); }
/// <summary> /// Sort data by column. /// </summary> /// <param name="StartCellReference"> /// The cell reference of the start cell of the cell range to be sorted, such as "A1". /// This is typically the top-left cell. /// </param> /// <param name="EndCellReference"> /// The cell reference of the end cell of the cell range to be sorted, such as "A1". This is /// typically the bottom-right cell. /// </param> /// <param name="SortByColumnName">The column name of the column to be sorted by, such as "AA".</param> /// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param> public void Sort(string StartCellReference, string EndCellReference, string SortByColumnName, bool SortAscending) { var iStartRowIndex = -1; var iStartColumnIndex = -1; var iEndRowIndex = -1; var iEndColumnIndex = -1; var iSortByColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex) && SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex)) { iSortByColumnIndex = SLTool.ToColumnIndex(SortByColumnName); Sort(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true, iSortByColumnIndex, SortAscending); } }