/// <summary>
/// Get the height of specified rows.
/// </summary>
/// <param name="Unit">The unit of the height to be returned.</param>
/// <param name="StartRowIndex">The row index of the start row.</param>
/// <param name="EndRowIndex">The row index of the end row.</param>
/// <returns>The height in the specified unit type.</returns>
public double GetHeight(SLMeasureUnitTypeValues Unit, int StartRowIndex, int EndRowIndex)
{
if (StartRowIndex < 1)
{
StartRowIndex = 1;
}
if (StartRowIndex > SLConstants.RowLimit)
{
StartRowIndex = SLConstants.RowLimit;
}
if (EndRowIndex < 1)
{
EndRowIndex = 1;
}
if (EndRowIndex > SLConstants.RowLimit)
{
EndRowIndex = SLConstants.RowLimit;
}
long lHeight = 0;
int i = 0;
SLRowProperties rp;
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
lHeight += rp.HeightInEMU;
}
else
{
lHeight += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
double result = 0;
switch (Unit)
{
case SLMeasureUnitTypeValues.Centimeter:
result = SLConvert.FromEmuToCentimeter((double)lHeight);
break;
case SLMeasureUnitTypeValues.Emu:
result = (double)lHeight;
break;
case SLMeasureUnitTypeValues.Inch:
result = SLConvert.FromEmuToInch((double)lHeight);
break;
case SLMeasureUnitTypeValues.Point:
result = SLConvert.FromEmuToPoint((double)lHeight);
break;
}
return(result);
}
/// <summary>
/// Get the width of the specified columns.
/// </summary>
/// <param name="Unit">The unit of the width to be returned.</param>
/// <param name="StartColumnIndex">The column index of the start column.</param>
/// <param name="EndColumnIndex">The column index of the end column.</param>
/// <returns>The width in the specified unit type.</returns>
public double GetWidth(SLMeasureUnitTypeValues Unit, int StartColumnIndex, int EndColumnIndex)
{
if (StartColumnIndex < 1)
{
StartColumnIndex = 1;
}
if (StartColumnIndex > SLConstants.ColumnLimit)
{
StartColumnIndex = SLConstants.ColumnLimit;
}
if (EndColumnIndex < 1)
{
EndColumnIndex = 1;
}
if (EndColumnIndex > SLConstants.ColumnLimit)
{
EndColumnIndex = SLConstants.ColumnLimit;
}
long lWidth = 0;
int i = 0;
SLColumnProperties cp;
for (i = StartColumnIndex; i <= EndColumnIndex; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
lWidth += cp.WidthInEMU;
}
else
{
lWidth += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
double result = 0;
switch (Unit)
{
case SLMeasureUnitTypeValues.Centimeter:
result = SLConvert.FromEmuToCentimeter((double)lWidth);
break;
case SLMeasureUnitTypeValues.Emu:
result = (double)lWidth;
break;
case SLMeasureUnitTypeValues.Inch:
result = SLConvert.FromEmuToInch((double)lWidth);
break;
case SLMeasureUnitTypeValues.Point:
result = SLConvert.FromEmuToPoint((double)lWidth);
break;
}
return(result);
}
/// <summary>
/// Set value to DefinedName range with offset
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="doc"></param>
/// <param name="definedName"></param>
/// <param name="value"></param>
/// <param name="rowOffset"></param>
/// <param name="columnOffset"></param>
public static void SetDefinedNameValue <T>(this SLDocument doc, string definedName, T value, int rowOffset = 0, int columnOffset = 0)
{
var address = doc.GetDefinedNameText(definedName);
if (String.IsNullOrWhiteSpace(address))
{
return;
}
var @switch = new Dictionary <Type, Action <int, int, object> > {
{ typeof(string), (row, col, val) => { doc.SetCellValue(row, col, (string)val); } },
{ typeof(int), (row, col, val) => { doc.SetCellValue(row, col, (int)val); } },
{ typeof(float), (row, col, val) => { doc.SetCellValue(row, col, (float)val); } },
{ typeof(double), (row, col, val) => { doc.SetCellValue(row, col, (double)val); } },
{ typeof(decimal), (row, col, val) => { doc.SetCellValue(row, col, (decimal)val); } },
{ typeof(DateTime), (row, col, val) => { doc.SetCellValue(row, col, (DateTime)val); } },
{ typeof(bool), (row, col, val) => { doc.SetCellValue(row, col, (bool)val); } },
};
var addresses = address.Split(',');
for (int i = 0; i < addresses.Length; i++)
{
var adr = addresses[i];
// parse full address
var indexExl = adr.IndexOf('!');
if (indexExl == -1)
{
continue;
}
var sheetName = adr.Substring(0, indexExl);
if (adr.Contains(':')) // range in address
{
var addressRange = adr.Substring(indexExl + 1);
var rangeArray = addressRange.Split(':');
var beginArray = rangeArray[0].Split('$');
var endArray = rangeArray[1].Split('$');
// for cycle
int rowStart = -1;
int colStart = -1;
int rowEnd = -1;
int colEnd = -1;
bool doCycle = false;
if (beginArray.Length == 2) // entire column or row
{
int val = -1;
if (Int32.TryParse(beginArray[1], out val)) // is entire row
{
rowStart = val;
doCycle = Int32.TryParse(endArray[1], out rowEnd);
colStart = 1;
colEnd = SLDocument.ColumnLimit;
rowStart += rowOffset;
rowEnd += rowOffset;
if (rowStart <= 0)
{
if (rowEnd <= 0)
{
doCycle = false;
}
else // shrink range
{
rowStart = 1;
}
}
}
else // entire column
{
rowStart = 1;
rowEnd = SLDocument.RowLimit; // very slowly!!!! Don't do it
colStart = SLConvert.ToColumnIndex(beginArray[1]);
colEnd = SLConvert.ToColumnIndex(endArray[1]);
colStart += columnOffset;
colEnd += columnOffset;
doCycle = colStart > 0 && colEnd > 0;
}
}
else // simple range
{
var correctStartCell = SLDocument.WhatIsRowColumnIndex(rangeArray[0].Replace("$", "")
, out rowStart, out colStart);
var correctEndCell = SLDocument.WhatIsRowColumnIndex(rangeArray[1].Replace("$", "")
, out rowEnd, out colEnd);
doCycle = correctStartCell && correctEndCell;
// do with offsets
rowStart += rowOffset;
rowEnd += rowOffset;
if (rowStart <= 0)
{
if (rowEnd <= 0)
{
doCycle = false;
}
else // shrink range vertically
{
rowStart = 1;
}
}
colStart += columnOffset;
colEnd += columnOffset;
if (colStart <= 0)
{
if (colEnd <= 0)
{
doCycle = false;
}
else // shrink range horizontally
{
colStart = 1;
}
}
}
// Fill cells
if (doCycle)
{
// select worksheet and insert value
if (doc.SelectWorksheet(sheetName))
{
for (int r = rowStart; r <= rowEnd; r++)
{
for (int c = colStart; c <= colEnd; c++)
{
@switch[typeof(T)](r, c, value);
}
}
}
}
}
else // single cells in address
{
var addressCell = adr.Substring(indexExl + 1).Replace("$", "");
int row = -1;
int col = -1;
if (SLDocument.WhatIsRowColumnIndex(addressCell, out row, out col))
{
row += rowOffset;
col += columnOffset;
// select worksheet and insert value
if (row > 0 && row <= SLDocument.RowLimit &&
col > 0 && col <= SLDocument.ColumnLimit &&
doc.SelectWorksheet(sheetName))
{
@switch[typeof(T)](row, col, value);
}
}
}
}
} // SetRangeValue<T>
