/// <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;
int index = 0;
bool bStartSuccess = false, bEndSuccess = false;
bIsValid = 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 this.iStartRowIndex, out this.iStartColumnIndex);
bEndSuccess = SLTool.FormatCellReferenceToRowColumnIndex(sEndCell, out this.iEndRowIndex, out this.iEndColumnIndex);
if (bStartSuccess && bEndSuccess)
{
bIsValid = true;
}
}
}
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class from this SLRstType class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class.</returns>
public SharedStringItem ToSharedStringItem()
{
SharedStringItem ssi = new SharedStringItem();
ssi.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml);
return(ssi);
}
internal void FromHyperlink(Hyperlink hl)
{
this.SetAllNull();
this.IsNew = false;
if (hl.Reference != null)
{
this.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.
this.IsExternal = true;
this.Id = hl.Id.Value;
}
if (hl.Location != null)
{
this.Location = hl.Location.Value;
}
if (hl.Tooltip != null)
{
this.ToolTip = hl.Tooltip.Value;
}
if (hl.Display != null)
{
this.Display = hl.Display.Value;
}
}
internal AutoFilter ToAutoFilter()
{
AutoFilter af = new AutoFilter();
if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
{
af.Reference = SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex);
}
else
{
af.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex),
SLTool.ToCellReference(this.EndRowIndex, this.EndColumnIndex));
}
foreach (SLFilterColumn fc in this.FilterColumns)
{
af.Append(fc.ToFilterColumn());
}
if (HasSortState)
{
af.Append(this.SortState.ToSortState());
}
return(af);
}
internal Location ToLocation()
{
Location loc = new Location();
if (this.Reference.StartRowIndex == this.Reference.EndRowIndex &&
this.Reference.StartColumnIndex == this.Reference.EndColumnIndex)
{
// this shouldn't happen because what's the point of a single cell as
// the whole pivot table? Something's already very wrong by this time...
loc.Reference = SLTool.ToCellReference(this.Reference.StartRowIndex, this.Reference.StartColumnIndex);
}
else
{
loc.Reference = SLTool.ToCellRange(this.Reference.StartRowIndex, this.Reference.StartColumnIndex, this.Reference.EndRowIndex, this.Reference.EndColumnIndex);
}
loc.FirstHeaderRow = this.FirstHeaderRow;
loc.FirstDataRow = this.FirstDataRow;
loc.FirstDataColumn = this.FirstDataColumn;
if (this.RowPageCount != 0)
{
loc.RowPageCount = this.RowPageCount;
}
if (this.ColumnsPerPage != 0)
{
loc.ColumnsPerPage = this.ColumnsPerPage;
}
return(loc);
}
internal void FromLocation(Location loc)
{
this.SetAllNull();
if (loc.Reference != null)
{
this.Reference = SLTool.TranslateReferenceToCellPointRange(loc.Reference.Value);
}
if (loc.FirstHeaderRow != null)
{
this.FirstHeaderRow = loc.FirstHeaderRow.Value;
}
if (loc.FirstDataRow != null)
{
this.FirstDataRow = loc.FirstDataRow.Value;
}
if (loc.FirstDataColumn != null)
{
this.FirstDataColumn = loc.FirstDataColumn.Value;
}
if (loc.RowPageCount != null)
{
this.RowPageCount = loc.RowPageCount.Value;
}
if (loc.ColumnsPerPage != null)
{
this.ColumnsPerPage = loc.ColumnsPerPage.Value;
}
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.InlineString class from this SLRstType class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.InlineString class.</returns>
public InlineString ToInlineString()
{
InlineString istr = new InlineString();
istr.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml);
return(istr);
}
/// <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)
{
// it's the same cell! We'll treat this as invalid.
this.bIsValid = false;
}
else
{
this.bIsValid = SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex) && SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex);
}
}
internal CalculationCell ToCalculationCell()
{
CalculationCell cc = new CalculationCell();
cc.CellReference = SLTool.ToCellReference(this.RowIndex, this.ColumnIndex);
cc.SheetId = this.SheetId;
if (this.InChildChain != null && this.InChildChain.Value)
{
cc.InChildChain = this.InChildChain.Value;
}
if (this.NewLevel != null && this.NewLevel.Value)
{
cc.NewLevel = this.NewLevel.Value;
}
if (this.NewThread != null && this.NewThread.Value)
{
cc.NewThread = this.NewThread.Value;
}
if (this.Array != null && this.Array.Value)
{
cc.Array = this.Array.Value;
}
return(cc);
}
/// <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)
{
this.Type = DataValidationValues.Date;
this.Operator = this.TranslateOperatorValues(DataOperator);
DateTime dt;
if (DataValue.StartsWith("="))
{
this.Formula1 = DataValue.Substring(1);
}
else
{
if (DateTime.TryParse(DataValue, out dt))
{
this.Formula1 = SLTool.CalculateDaysFromEpoch(dt, this.Date1904).ToString(CultureInfo.InvariantCulture);
}
else
{
// 1 Jan 1900
this.Formula1 = "1";
}
}
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
internal void FromCalculationCell(CalculationCell cc)
{
this.SetAllNull();
int iRowIndex = -1;
int iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(cc.CellReference.Value, out iRowIndex, out iColumnIndex))
{
this.RowIndex = iRowIndex;
this.ColumnIndex = iColumnIndex;
}
this.SheetId = cc.SheetId ?? 0;
if (cc.InChildChain != null)
{
this.InChildChain = cc.InChildChain.Value;
}
if (cc.NewLevel != null)
{
this.NewLevel = cc.NewLevel.Value;
}
if (cc.NewThread != null)
{
this.NewThread = cc.NewThread.Value;
}
if (cc.Array != null)
{
this.Array = cc.Array.Value;
}
}
internal int DirectSaveToSharedStringTable(string Data)
{
int 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);
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.CommentText class from this SLRstType class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.CommentText class.</returns>
public CommentText ToCommentText()
{
CommentText ct = new CommentText();
ct.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml);
return(ct);
}
private void SetDisplayColor()
{
this.clrDisplay = System.Drawing.Color.FromArgb(255, 0, 0, 0);
int index = 0;
if (this.Theme != null)
{
index = (int)this.Theme.Value;
if (index >= 0 && index < this.listThemeColors.Count)
{
this.clrDisplay = System.Drawing.Color.FromArgb(255, this.listThemeColors[index]);
if (this.Tint != null)
{
this.clrDisplay = SLTool.ToColor(this.clrDisplay, this.Tint.Value);
}
}
}
else if (this.Rgb != null)
{
this.clrDisplay = SLTool.ToColor(this.Rgb);
}
else if (this.Indexed != null)
{
index = (int)this.Indexed.Value;
if (index >= 0 && index < this.listIndexedColors.Count)
{
this.clrDisplay = System.Drawing.Color.FromArgb(255, this.listIndexedColors[index]);
}
}
}
internal X14.ConditionalFormatting ToConditionalFormatting()
{
X14.ConditionalFormatting 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 (this.Pivot)
{
cf.Pivot = this.Pivot;
}
int i;
for (i = 0; i < this.Rules.Count; ++i)
{
cf.Append(this.Rules[i].ToConditionalFormattingRule());
}
if (this.ReferenceSequence.Count > 0)
{
cf.Append(new Excel.ReferenceSequence(SLTool.TranslateCellPointRangeToRefSeq(this.ReferenceSequence)));
}
return(cf);
}
internal void FromConditionalFormatting(X14.ConditionalFormatting cf)
{
this.SetAllNull();
if (cf.Pivot != null)
{
this.Pivot = cf.Pivot.Value;
}
using (OpenXmlReader oxr = OpenXmlReader.Create(cf))
{
while (oxr.Read())
{
SLConditionalFormattingRule2010 cfr;
if (oxr.ElementType == typeof(X14.ConditionalFormattingRule))
{
cfr = new SLConditionalFormattingRule2010();
cfr.FromConditionalFormattingRule((X14.ConditionalFormattingRule)oxr.LoadCurrentElement());
this.Rules.Add(cfr);
}
else if (oxr.ElementType == typeof(Excel.ReferenceSequence))
{
Excel.ReferenceSequence refseq = (Excel.ReferenceSequence)oxr.LoadCurrentElement();
this.ReferenceSequence = SLTool.TranslateRefSeqToCellPointRange(refseq);
}
}
}
}
/// <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)
{
this.Type = DataValidationValues.Date;
this.Operator = IsBetween ? DataValidationOperatorValues.Between : DataValidationOperatorValues.NotBetween;
this.Formula1 = SLTool.CalculateDaysFromEpoch(Minimum, this.Date1904).ToString(CultureInfo.InvariantCulture);
this.Formula2 = SLTool.CalculateDaysFromEpoch(Maximum, this.Date1904).ToString(CultureInfo.InvariantCulture);
this.AllowBlank = IgnoreBlank;
}
/// <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)
{
this.Type = DataValidationValues.Date;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = SLTool.CalculateDaysFromEpoch(DataValue, this.Date1904).ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
internal void ForceSaveToSharedStringTable(SharedStringItem ssi)
{
int index = listSharedString.Count;
string 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)
{
int iColumnIndex = -1;
iColumnIndex = SLTool.ToColumnIndex(ColumnName);
return(this.GetWidth(Unit, iColumnIndex, iColumnIndex));
}
internal void FromSortCondition(SortCondition sc)
{
this.SetAllNull();
if (sc.Descending != null && sc.Descending.Value)
{
this.Descending = sc.Descending.Value;
}
if (sc.SortBy != null)
{
this.SortBy = sc.SortBy.Value;
}
int iStartRowIndex = 1;
int iStartColumnIndex = 1;
int iEndRowIndex = 1;
int iEndColumnIndex = 1;
string sRef = sc.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iEndRowIndex;
this.EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iStartRowIndex;
this.EndColumnIndex = iStartColumnIndex;
}
}
if (sc.CustomList != null)
{
this.CustomList = sc.CustomList.Value;
}
if (sc.FormatId != null)
{
this.FormatId = sc.FormatId.Value;
}
if (sc.IconSet != null)
{
this.IconSet = sc.IconSet.Value;
}
if (sc.IconId != null)
{
this.IconId = sc.IconId.Value;
}
}
/// <summary>
/// Remove an existing hyperlink.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
public void RemoveHyperlink(string CellReference)
{
int iRowIndex = -1;
int iColumnIndex = -1;
SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex);
this.RemoveHyperlink(iRowIndex, iColumnIndex);
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.MergeCell class from this SLMergeCell class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.MergeCell class.</returns>
public MergeCell ToMergeCell()
{
MergeCell mc = new MergeCell();
string sStartCell = SLTool.ToCellReference(iStartRowIndex, iStartColumnIndex);
string sEndCell = SLTool.ToCellReference(iEndRowIndex, iEndColumnIndex);
mc.Reference = string.Format("{0}:{1}", sStartCell, sEndCell);
return(mc);
}
/// <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)
{
int iStartColumnIndex = -1;
int iEndColumnIndex = -1;
iStartColumnIndex = SLTool.ToColumnIndex(StartColumnName);
iEndColumnIndex = SLTool.ToColumnIndex(EndColumnName);
return(this.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)
{
int iRowIndex = -1;
int iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex))
{
this.ImportText(FileName, iRowIndex, iColumnIndex, null);
}
}
internal void FromAutoFilter(AutoFilter af)
{
this.SetAllNull();
int iStartRowIndex = 1;
int iStartColumnIndex = 1;
int iEndRowIndex = 1;
int iEndColumnIndex = 1;
string sRef = af.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iEndRowIndex;
this.EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iStartRowIndex;
this.EndColumnIndex = iStartColumnIndex;
}
}
if (af.HasChildren)
{
SLFilterColumn fc;
using (OpenXmlReader oxr = OpenXmlReader.Create(af))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(FilterColumn))
{
fc = new SLFilterColumn();
fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
this.FilterColumns.Add(fc);
}
else if (oxr.ElementType == typeof(SortState))
{
this.SortState = new SLSortState();
this.SortState.FromSortState((SortState)oxr.LoadCurrentElement());
this.HasSortState = 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)
{
int iRowIndex = -1;
int iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return(false);
}
return(InsertComment(iRowIndex, iColumnIndex, Comment));
}
/// <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)
{
int iRowIndex = -1;
int iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return(false);
}
return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, Display, ToolTip, OverwriteExistingCell));
}
/// <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)
{
int iRowIndex = -1;
int 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)
{
int iStartRowIndex = -1;
int iStartColumnIndex = -1;
int iEndRowIndex = -1;
int iEndColumnIndex = -1;
SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex);
SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex);
this.SetAddPrintArea(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true);
}