/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.CommentText class from this SLRstType class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.CommentText class.</returns>
public CommentText ToCommentText()
{
var ct = new CommentText();
ct.InnerXml = SLTool.RemoveNamespaceDeclaration(istrReal.InnerXml);
return(ct);
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.InlineString class from this SLRstType class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.InlineString class.</returns>
public InlineString ToInlineString()
{
var istr = new InlineString();
istr.InnerXml = SLTool.RemoveNamespaceDeclaration(istrReal.InnerXml);
return(istr);
}
/// <summary>
/// Form a DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class from this SLRstType class.
/// </summary>
/// <returns>A DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class.</returns>
public SharedStringItem ToSharedStringItem()
{
var ssi = new SharedStringItem();
ssi.InnerXml = SLTool.RemoveNamespaceDeclaration(istrReal.InnerXml);
return(ssi);
}
internal AutoFilter ToAutoFilter()
{
var af = new AutoFilter();
if ((StartRowIndex == EndRowIndex) && (StartColumnIndex == EndColumnIndex))
{
af.Reference = SLTool.ToCellReference(StartRowIndex, StartColumnIndex);
}
else
{
af.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(StartRowIndex, StartColumnIndex),
SLTool.ToCellReference(EndRowIndex, EndColumnIndex));
}
foreach (var fc in FilterColumns)
{
af.Append(fc.ToFilterColumn());
}
if (HasSortState)
{
af.Append(SortState.ToSortState());
}
return(af);
}
internal void FromSortCondition(SortCondition sc)
{
SetAllNull();
if ((sc.Descending != null) && sc.Descending.Value)
{
Descending = sc.Descending.Value;
}
if (sc.SortBy != null)
{
SortBy = sc.SortBy.Value;
}
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = sc.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (sc.CustomList != null)
{
CustomList = sc.CustomList.Value;
}
if (sc.FormatId != null)
{
FormatId = sc.FormatId.Value;
}
if (sc.IconSet != null)
{
IconSet = sc.IconSet.Value;
}
if (sc.IconId != null)
{
IconId = sc.IconId.Value;
}
}
internal void FromAutoFilter(AutoFilter af)
{
SetAllNull();
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = af.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (af.HasChildren)
{
SLFilterColumn fc;
using (var oxr = OpenXmlReader.Create(af))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(FilterColumn))
{
fc = new SLFilterColumn();
fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
FilterColumns.Add(fc);
}
else if (oxr.ElementType == typeof(SortState))
{
SortState = new SLSortState();
SortState.FromSortState((SortState)oxr.LoadCurrentElement());
HasSortState = true;
}
}
}
}
}
/// <summary>
/// Set the text. If text formatting is needed, use one of the AppendText() functions.
/// </summary>
/// <param name="Text">The text.</param>
public void SetText(string Text)
{
if ((Text == null) || (Text.Length == 0))
{
istrReal.Text = null;
}
else
{
istrReal.Text = new Text();
istrReal.Text.Text = Text;
if (SLTool.ToPreserveSpace(Text))
{
istrReal.Text.Space = SpaceProcessingModeValues.Preserve;
}
}
}
internal SortCondition ToSortCondition()
{
var sc = new SortCondition();
if (Descending != null)
{
sc.Descending = Descending.Value;
}
if (HasSortBy)
{
sc.SortBy = SortBy;
}
if ((StartRowIndex == EndRowIndex) && (StartColumnIndex == EndColumnIndex))
{
sc.Reference = SLTool.ToCellReference(StartRowIndex, StartColumnIndex);
}
else
{
sc.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(StartRowIndex, StartColumnIndex),
SLTool.ToCellReference(EndRowIndex, EndColumnIndex));
}
if (CustomList != null)
{
sc.CustomList = CustomList;
}
if (FormatId != null)
{
sc.FormatId = FormatId;
}
if (HasIconSet)
{
sc.IconSet = IconSet;
}
if (IconId != null)
{
sc.IconId = IconId.Value;
}
return(sc);
}
internal SortState ToSortState()
{
var ss = new SortState();
if ((ColumnSort != null) && ColumnSort.Value)
{
ss.ColumnSort = ColumnSort.Value;
}
if ((CaseSensitive != null) && CaseSensitive.Value)
{
ss.CaseSensitive = CaseSensitive.Value;
}
if (HasSortMethod)
{
ss.SortMethod = SortMethod;
}
if ((StartRowIndex == EndRowIndex) && (StartColumnIndex == EndColumnIndex))
{
ss.Reference = SLTool.ToCellReference(StartRowIndex, StartColumnIndex);
}
else
{
ss.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(StartRowIndex, StartColumnIndex),
SLTool.ToCellReference(EndRowIndex, EndColumnIndex));
}
if (SortConditions.Count > 0)
{
for (var i = 0; i < SortConditions.Count; ++i)
{
ss.Append(SortConditions[i].ToSortCondition());
}
}
return(ss);
}
/// <summary>
/// Get the cell reference given the worksheet name, and row and column index. For example "Sheet1!A1" or
/// "Sheet1!$A$1".
/// </summary>
/// <param name="WorksheetName">The worksheet name.</param>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="IsAbsolute">True for absolute reference. False for relative reference.</param>
/// <returns>The cell reference.</returns>
public static string ToCellReference(string WorksheetName, int RowIndex, int ColumnIndex, bool IsAbsolute)
{
return(SLTool.ToCellReference(WorksheetName, RowIndex, ColumnIndex, IsAbsolute));
}
internal A.Paragraph ToParagraph()
{
var para = new A.Paragraph();
para.ParagraphProperties = new A.ParagraphProperties();
para.ParagraphProperties.Append(new A.DefaultRunProperties());
A.Run run;
if (istrReal.Text != null)
{
run = new A.Run();
run.RunProperties = new A.RunProperties();
run.Text = new A.Text(istrReal.Text.Text);
para.Append(run);
}
Run xrun;
RunFont xrunRunFont;
Bold xrunBold;
Italic xrunItalic;
Strike xrunStrike;
DocumentFormat.OpenXml.Spreadsheet.Color xrunColor;
FontSize xrunFontSize;
Underline xrunUnderline;
VerticalTextAlignment xrunVertical;
FontScheme xrunScheme;
string sFont;
bool? bBold;
bool? bItalic;
bool? bStrike;
double? fFontSize;
UnderlineValues? vUnderline;
VerticalAlignmentRunValues?vVertical;
bool bHasColor;
var clrRun = new SLColorTransform(new List <Color>());
FontSchemeValues?vScheme;
using (var oxr = OpenXmlReader.Create(istrReal))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Run))
{
run = new A.Run();
run.RunProperties = new A.RunProperties();
sFont = string.Empty;
bBold = null;
bItalic = null;
bStrike = null;
fFontSize = null;
vUnderline = null;
vVertical = null;
bHasColor = false;
vScheme = null;
xrun = (Run)oxr.LoadCurrentElement();
if (xrun.RunProperties != null)
{
using (var oxrProps = OpenXmlReader.Create(xrun.RunProperties))
{
while (oxrProps.Read())
{
if (oxrProps.ElementType == typeof(RunFont))
{
xrunRunFont = (RunFont)oxrProps.LoadCurrentElement();
if (xrunRunFont.Val != null)
{
sFont = xrunRunFont.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(Bold))
{
xrunBold = (Bold)oxrProps.LoadCurrentElement();
if (xrunBold.Val != null)
{
bBold = xrunBold.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(Italic))
{
xrunItalic = (Italic)oxrProps.LoadCurrentElement();
if (xrunItalic.Val != null)
{
bItalic = xrunItalic.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(Strike))
{
xrunStrike = (Strike)oxrProps.LoadCurrentElement();
if (xrunStrike.Val != null)
{
bStrike = xrunStrike.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Color))
{
xrunColor =
(DocumentFormat.OpenXml.Spreadsheet.Color)oxrProps.LoadCurrentElement();
if (xrunColor.Rgb != null)
{
bHasColor = true;
clrRun = new SLColorTransform(new List <Color>());
clrRun.SetColor(SLTool.ToColor(xrunColor.Rgb.Value), 0);
}
else if (xrunColor.Theme != null)
{
bHasColor = true;
clrRun = new SLColorTransform(new List <Color>());
if (xrunColor.Tint != null)
{
clrRun.SetColor((SLThemeColorIndexValues)xrunColor.Theme.Value,
xrunColor.Tint.Value, 0);
}
else
{
clrRun.SetColor((SLThemeColorIndexValues)xrunColor.Theme.Value, 0, 0);
}
}
}
else if (oxrProps.ElementType == typeof(FontSize))
{
xrunFontSize = (FontSize)oxrProps.LoadCurrentElement();
if (xrunFontSize.Val != null)
{
fFontSize = xrunFontSize.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(Underline))
{
xrunUnderline = (Underline)oxrProps.LoadCurrentElement();
if (xrunUnderline.Val != null)
{
vUnderline = xrunUnderline.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(VerticalTextAlignment))
{
xrunVertical = (VerticalTextAlignment)oxrProps.LoadCurrentElement();
if (xrunVertical.Val != null)
{
vVertical = xrunVertical.Val.Value;
}
}
else if (oxrProps.ElementType == typeof(FontScheme))
{
xrunScheme = (FontScheme)oxrProps.LoadCurrentElement();
if (xrunScheme.Val != null)
{
vScheme = xrunScheme.Val.Value;
}
}
}
}
}
if (vScheme != null)
{
if (vScheme.Value == FontSchemeValues.Major)
{
sFont = "+mj-lt";
}
else if (vScheme.Value == FontSchemeValues.Minor)
{
sFont = "+mn-lt";
}
}
if (bHasColor)
{
if (clrRun.IsRgbColorModelHex)
{
run.RunProperties.Append(new A.SolidFill
{
RgbColorModelHex = clrRun.ToRgbColorModelHex()
});
}
else
{
run.RunProperties.Append(new A.SolidFill
{
SchemeColor = clrRun.ToSchemeColor()
});
}
}
if (sFont.Length > 0)
{
run.RunProperties.Append(new A.LatinFont {
Typeface = sFont
});
}
if (fFontSize != null)
{
run.RunProperties.FontSize = (int)(fFontSize.Value * 100);
}
if (bBold != null)
{
run.RunProperties.Bold = bBold.Value;
}
if (bItalic != null)
{
run.RunProperties.Italic = bItalic.Value;
}
if (vUnderline != null)
{
if ((vUnderline.Value == UnderlineValues.Single) ||
(vUnderline.Value == UnderlineValues.SingleAccounting))
{
run.RunProperties.Underline = A.TextUnderlineValues.Single;
}
else if ((vUnderline.Value == UnderlineValues.Double) ||
(vUnderline.Value == UnderlineValues.DoubleAccounting))
{
run.RunProperties.Underline = A.TextUnderlineValues.Double;
}
}
if (bStrike != null)
{
run.RunProperties.Strike = bStrike.Value
? A.TextStrikeValues.SingleStrike
: A.TextStrikeValues.NoStrike;
}
if (vVertical != null)
{
if (vVertical.Value == VerticalAlignmentRunValues.Superscript)
{
run.RunProperties.Baseline = 30000;
}
else if (vVertical.Value == VerticalAlignmentRunValues.Subscript)
{
run.RunProperties.Baseline = -25000;
}
else
{
run.RunProperties.Baseline = 0;
}
}
else
{
run.RunProperties.Baseline = 0;
}
run.Text = new A.Text(xrun.Text.Text);
para.Append(run);
}
}
}
return(para);
}
internal string ToHash()
{
var istr = ToInlineString();
return(SLTool.RemoveNamespaceDeclaration(istr.InnerXml));
}
/// <summary>
/// Get the cell range reference given a corner cell and its opposite corner cell in a cell range. For example "A1:C5"
/// or "$A$1:$C$5".
/// </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="IsAbsolute">True for absolute reference. False for relative reference.</param>
/// <returns>The cell range reference.</returns>
public static string ToCellRange(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex,
bool IsAbsolute)
{
return(SLTool.ToCellRange(string.Empty, StartRowIndex, StartColumnIndex, EndRowIndex, EndColumnIndex,
IsAbsolute));
}
/// <summary>
/// Get the cell range reference given a worksheet name, and a corner cell and its opposite corner cell in a cell
/// range. For example "Sheet1!A1:C5" or "Sheet1!$A$1:$C$5".
/// </summary>
/// <param name="WorksheetName">The worksheet name.</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="IsAbsolute">True for absolute reference. False for relative reference.</param>
/// <returns>The cell range reference.</returns>
public static string ToCellRange(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex,
int EndColumnIndex, bool IsAbsolute)
{
return(SLTool.ToCellRange(WorksheetName, StartRowIndex, StartColumnIndex, EndRowIndex, EndColumnIndex,
IsAbsolute));
}
/// <summary>
/// Get the column name given the column index.
/// </summary>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>The column name.</returns>
public static string ToColumnName(int ColumnIndex)
{
return(SLTool.ToColumnName(ColumnIndex));
}
/// <summary>
/// Append given text with a given font style.
/// </summary>
/// <param name="Text">The text.</param>
/// <param name="TextFont">The font style.</param>
public void AppendText(string Text, SLFont TextFont)
{
var run = new Run();
var runprops = new RunProperties();
if (TextFont.FontName != null)
{
runprops.Append(new RunFont {
Val = TextFont.FontName
});
}
if (TextFont.CharacterSet != null)
{
runprops.Append(new RunPropertyCharSet {
Val = TextFont.CharacterSet.Value
});
}
if (TextFont.FontFamily != null)
{
runprops.Append(new FontFamily {
Val = TextFont.FontFamily.Value
});
}
if (TextFont.Bold != null)
{
runprops.Append(new Bold {
Val = TextFont.Bold.Value
});
}
if (TextFont.Italic != null)
{
runprops.Append(new Italic {
Val = TextFont.Italic.Value
});
}
if (TextFont.Strike != null)
{
runprops.Append(new Strike {
Val = TextFont.Strike.Value
});
}
if (TextFont.Outline != null)
{
runprops.Append(new Outline {
Val = TextFont.Outline.Value
});
}
if (TextFont.Shadow != null)
{
runprops.Append(new Shadow {
Val = TextFont.Shadow.Value
});
}
if (TextFont.Condense != null)
{
runprops.Append(new Condense {
Val = TextFont.Condense.Value
});
}
if (TextFont.Extend != null)
{
runprops.Append(new Extend {
Val = TextFont.Extend.Value
});
}
if (TextFont.HasFontColor)
{
runprops.Append(TextFont.clrFontColor.ToSpreadsheetColor());
}
if (TextFont.FontSize != null)
{
runprops.Append(new FontSize {
Val = TextFont.FontSize.Value
});
}
if (TextFont.HasUnderline)
{
runprops.Append(new Underline {
Val = TextFont.Underline
});
}
if (TextFont.HasVerticalAlignment)
{
runprops.Append(new VerticalTextAlignment {
Val = TextFont.VerticalAlignment
});
}
if (TextFont.HasFontScheme)
{
runprops.Append(new FontScheme {
Val = TextFont.FontScheme
});
}
if (runprops.ChildElements.Count > 0)
{
run.Append(runprops);
}
run.Text = new Text();
run.Text.Text = Text;
if (SLTool.ToPreserveSpace(Text))
{
run.Text.Space = SpaceProcessingModeValues.Preserve;
}
var bFound = false;
var oxe = istrReal.FirstChild;
foreach (var child in istrReal.ChildElements)
{
if (child is Text || child is Run)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
istrReal.InsertAfter(run, oxe);
}
else
{
istrReal.PrependChild(run);
}
}
internal Run ToRun()
{
var r = new Run();
r.RunProperties = new RunProperties();
if (Font.FontName != null)
{
r.RunProperties.Append(new RunFont {
Val = Font.FontName
});
}
if (Font.CharacterSet != null)
{
r.RunProperties.Append(new RunPropertyCharSet {
Val = Font.CharacterSet.Value
});
}
if (Font.FontFamily != null)
{
r.RunProperties.Append(new FontFamily {
Val = Font.FontFamily.Value
});
}
if ((Font.Bold != null) && Font.Bold.Value)
{
r.RunProperties.Append(new Bold());
}
if ((Font.Italic != null) && Font.Italic.Value)
{
r.RunProperties.Append(new Italic());
}
if ((Font.Strike != null) && Font.Strike.Value)
{
r.RunProperties.Append(new Strike());
}
if ((Font.Outline != null) && Font.Outline.Value)
{
r.RunProperties.Append(new Outline());
}
if ((Font.Shadow != null) && Font.Shadow.Value)
{
r.RunProperties.Append(new Shadow());
}
if ((Font.Condense != null) && Font.Condense.Value)
{
r.RunProperties.Append(new Condense());
}
if ((Font.Extend != null) && Font.Extend.Value)
{
r.RunProperties.Append(new Extend());
}
if (Font.HasFontColor)
{
r.RunProperties.Append(Font.clrFontColor.ToSpreadsheetColor());
}
if (Font.FontSize != null)
{
r.RunProperties.Append(new FontSize {
Val = Font.FontSize.Value
});
}
if (Font.HasUnderline)
{
r.RunProperties.Append(new Underline {
Val = Font.Underline
});
}
if (Font.HasVerticalAlignment)
{
r.RunProperties.Append(new VerticalTextAlignment {
Val = Font.VerticalAlignment
});
}
if (Font.HasFontScheme)
{
r.RunProperties.Append(new FontScheme {
Val = Font.FontScheme
});
}
r.Text = new Text(Text);
if (SLTool.ToPreserveSpace(Text))
{
r.Text.Space = SpaceProcessingModeValues.Preserve;
}
return(r);
}
/// <summary>
/// Convert a color in hexadecimal to a System.Drawing.Color structure.
/// </summary>
/// <param name="HexValue">The color in hexadecimal.</param>
/// <returns>A System.Drawing.Color structure.</returns>
public static Color ToColor(string HexValue)
{
return(SLTool.ToColor(HexValue));
}
/// <summary>
/// Convert a set of HSL color values to a System.Drawing.Color structure.
/// </summary>
/// <param name="Hue">The hue measured in degrees ranging from 0 to 360 degrees.</param>
/// <param name="Saturation">The saturation ranging from 0.0 to 1.0, where 0.0 is grayscale and 1.0 is the most saturated.</param>
/// <param name="Luminance">
/// The luminance (sometimes known as brightness) ranging from 0.0 to 1.0, where 0.0 is effectively
/// black and 1.0 is effectively white.
/// </param>
/// <returns>A System.Drawing.Color structure.</returns>
public static Color ToColor(double Hue, double Saturation, double Luminance)
{
return(SLTool.ToColor(Hue, Saturation, Luminance));
}
/// <summary>
/// Get the cell reference given the worksheet name, and row and column index. For example "Sheet1!A1".
/// </summary>
/// <param name="WorksheetName">The worksheet name.</param>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>The cell reference.</returns>
public static string ToCellReference(string WorksheetName, int RowIndex, int ColumnIndex)
{
return(SLTool.ToCellReference(WorksheetName, RowIndex, ColumnIndex, false));
}
/// <summary>
/// Get the cell reference given the row and column index. For example "A1" or "$A$1".
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="IsAbsolute">True for absolute reference. False for relative reference.</param>
/// <returns>The cell reference.</returns>
public static string ToCellReference(int RowIndex, int ColumnIndex, bool IsAbsolute)
{
return(SLTool.ToCellReference(string.Empty, RowIndex, ColumnIndex, IsAbsolute));
}
/// <summary>
/// Get the cell reference given the row and column index. For example "A1".
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>The cell reference.</returns>
public static string ToCellReference(int RowIndex, int ColumnIndex)
{
return(SLTool.ToCellReference(string.Empty, RowIndex, ColumnIndex, false));
}
/// <summary>
/// Get the column index given a cell reference or column name.
/// </summary>
/// <param name="Input">
/// A cell reference such as "A1" or column name such as "A". If the input is invalid, then -1 is
/// returned.
/// </param>
/// <returns>The column index.</returns>
public static int ToColumnIndex(string Input)
{
return(SLTool.ToColumnIndex(Input));
}
internal void FromSortState(SortState ss)
{
SetAllNull();
if ((ss.ColumnSort != null) && ss.ColumnSort.Value)
{
ColumnSort = ss.ColumnSort.Value;
}
if ((ss.CaseSensitive != null) && ss.CaseSensitive.Value)
{
CaseSensitive = ss.CaseSensitive.Value;
}
if (ss.SortMethod != null)
{
SortMethod = ss.SortMethod.Value;
}
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = ss.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (ss.HasChildren)
{
SLSortCondition sc;
using (var oxr = OpenXmlReader.Create(ss))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(SortCondition))
{
sc = new SLSortCondition();
sc.FromSortCondition((SortCondition)oxr.LoadCurrentElement());
// limit of 64 from Open XML specs
if (SortConditions.Count < 64)
{
SortConditions.Add(sc);
}
}
}
}
}
}
/// <summary>
/// Get the cell range reference given a corner cell and its opposite corner cell in a cell range. For example "A1:C5".
/// </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>
/// <returns>The cell range reference.</returns>
public static string ToCellRange(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
{
return(SLTool.ToCellRange(string.Empty, StartRowIndex, StartColumnIndex, EndRowIndex, EndColumnIndex, false));
}
