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 X14.Sparkline ToSparkline()
{
X14.Sparkline spk = new X14.Sparkline();
if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
{
spk.Formula = new Excel.Formula();
spk.Formula.Text = SLTool.ToCellReference(this.WorksheetName, this.StartRowIndex, this.StartColumnIndex);
}
else
{
spk.Formula = new Excel.Formula();
spk.Formula.Text = SLTool.ToCellRange(this.WorksheetName, this.StartRowIndex, this.StartColumnIndex, this.EndRowIndex, this.EndColumnIndex);
}
spk.ReferenceSequence = new Excel.ReferenceSequence();
spk.ReferenceSequence.Text = SLTool.ToCellReference(this.LocationRowIndex, this.LocationColumnIndex);
return(spk);
}
internal Hyperlink ToHyperlink()
{
Hyperlink hl = new Hyperlink();
hl.Reference = SLTool.ToCellRange(this.Reference.StartRowIndex, this.Reference.StartColumnIndex, this.Reference.EndRowIndex, this.Reference.EndColumnIndex);
if (this.Id != null && this.Id.Length > 0)
{
hl.Id = this.Id;
}
if (this.Location != null && this.Location.Length > 0)
{
hl.Location = this.Location;
}
if (this.ToolTip != null && this.ToolTip.Length > 0)
{
hl.Tooltip = this.ToolTip;
}
if (this.Display != null && this.Display.Length > 0)
{
hl.Display = this.Display;
}
return(hl);
}
/// <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 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 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));
}
private void SetAddPrintArea(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool ToAdd)
{
if (StartRowIndex < 1)
{
StartRowIndex = 1;
}
if (StartRowIndex > SLConstants.RowLimit)
{
StartRowIndex = SLConstants.RowLimit;
}
if (StartColumnIndex < 1)
{
StartColumnIndex = 1;
}
if (StartColumnIndex > SLConstants.ColumnLimit)
{
StartColumnIndex = SLConstants.ColumnLimit;
}
if (EndRowIndex < 1)
{
EndRowIndex = 1;
}
if (EndRowIndex > SLConstants.RowLimit)
{
EndRowIndex = SLConstants.RowLimit;
}
if (EndColumnIndex < 1)
{
EndColumnIndex = 1;
}
if (EndColumnIndex > SLConstants.ColumnLimit)
{
EndColumnIndex = SLConstants.ColumnLimit;
}
// no overlapping checked.
int iSheetPosition = 0;
int i;
for (i = 0; i < slwb.Sheets.Count; ++i)
{
if (slwb.Sheets[i].Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
iSheetPosition = i;
break;
}
}
string sPrintArea = string.Empty;
if (StartRowIndex == EndRowIndex && StartColumnIndex == EndColumnIndex)
{
// why would you print just one cell? Even Excel questions this with a message box...
sPrintArea = SLTool.ToCellReference(gsSelectedWorksheetName, StartRowIndex, StartColumnIndex, true);
}
else
{
sPrintArea = SLTool.ToCellRange(gsSelectedWorksheetName, StartRowIndex, StartColumnIndex, EndRowIndex, EndColumnIndex, true);
}
bool bFound = false;
for (i = 0; i < slwb.DefinedNames.Count; ++i)
{
if (slwb.DefinedNames[i].Name.Equals(SLConstants.PrintAreaDefinedName, StringComparison.OrdinalIgnoreCase) &&
slwb.DefinedNames[i].LocalSheetId != null &&
slwb.DefinedNames[i].LocalSheetId.Value == iSheetPosition)
{
bFound = true;
if (ToAdd)
{
slwb.DefinedNames[i].Text = string.Format("{0},{1}", slwb.DefinedNames[i].Text, sPrintArea);
}
else
{
slwb.DefinedNames[i].Text = sPrintArea;
}
}
}
if (!bFound)
{
SLDefinedName dn = new SLDefinedName(SLConstants.PrintAreaDefinedName);
dn.LocalSheetId = (uint)iSheetPosition;
dn.Text = sPrintArea;
slwb.DefinedNames.Add(dn);
}
}
