public double GetAmount(int row, int column)
{
CellIndex cellIndex = new CellIndex(row, column);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
// in case of DBNULL the cell contains a text with content 'NULL'
// return '0'
if (cellValue.ValueType == CellValueType.Text)
{
if (cellValue.RawValue == "NULL")
{
return(0);
}
// An Exception will be thrown
}
if (cellValue.ValueType == CellValueType.Empty)
{
return(0);
}
if (cellValue.ValueType != CellValueType.Number)
{
throw new Exception("Value is not a number");
}
return(double.Parse(cellValue.RawValue));
}
public void GetSetCellValue()
{
#region radspreadsheet-features-formatting-cells_7
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection selection = worksheet.Cells[1, 1];
ICellValue cellValue = selection.GetValue().Value;
#endregion
#region radspreadsheet-features-formatting-cells_2
// set DateTime value
selection.SetValue(DateTime.Now);
// set double value
selection.SetValue(51.345);
// set ICellValue
ICellValue value = worksheet.Cells[5, 5].GetValue().Value;
selection.SetValue(value);
// set string value
selection.SetValue("Total");
// set formula value
selection.SetValue("=C1+C10");
#endregion
}
public override void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue)
{
base.RenderHtmlStyle(tagBuilder, cell, cellValue);
if (cellValue == null)
{
return;
}
var value = calculateCellValue(cellValue);
if (value > PositiveZero)
{
if (!_isBgColor)
{
tagBuilder.AddCssClass("text-success");
}
else
{
tagBuilder.AddCssClass("bg-success");
}
}
else if (value < NegativeZero)
{
if (!_isBgColor)
{
tagBuilder.AddCssClass("text-danger");
}
else
{
tagBuilder.AddCssClass("bg-danger");
}
}
}
public DateTime GetDate(int row, int column)
{
int serialDate = 0;
CellIndex cellIndex = new CellIndex(row, column);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Empty)
{
return(new DateTime(2000, 1, 1));
}
if (cellValue.ValueType != CellValueType.Number)
{
// try datetime.tryparse
string[] formats = { "dd.MM.yyyy" };
DateTime d = DateTime.Now;
if (DateTime.TryParseExact(cellValue.RawValue, formats,
System.Globalization.CultureInfo.CreateSpecificCulture("de-De"), System.Globalization.DateTimeStyles.None, out d))
{
return(d);
}
throw new Exception("Value is not a date");
}
if (int.TryParse(cellValue.RawValue, out serialDate))
{
return(FromExcelSerialDate((int)serialDate));
}
return(DateTime.MinValue);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
CellIndex cellIndex = (sender as Button).Tag as CellIndex;
ICellValue cellValue = this.Owner.Worksheet.Cells[cellIndex].GetValue().Value;
MessageBox.Show("Age: " + cellValue.RawValue);
}
public override void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue)
{
if (_textBold)
{
tagBuilder.AddCssClass("font-weight-bold");
}
}
public override void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue)
{
tagBuilder.Attributes.Add("style", $"height:{SPERATOR_HEIGTH}px;");
foreach (var htmlClass in htmlClassList)
{
tagBuilder.AddCssClass(htmlClass);
}
}
public void SetNumberCelllValueUsingICellValue()
{
#region radspreadsheet-model-working-with-cells-cell-value-types_5
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ICellValue value = CellValueFactory.Create(3.14);
worksheet.Cells[0, 0].SetValue(value);
#endregion
}
public void SetBooleanValueUsingICellValue()
{
#region radspreadsheet-model-working-with-cells-cell-value-types_3
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ICellValue booleanCellValueTrue = CellValueFactory.Create(true);
ICellValue booleanCellValueFalse = CellValueFactory.Create(false);
worksheet.Cells[0, 0].SetValue(booleanCellValueTrue);
worksheet.Cells[0, 1].SetValue(booleanCellValueFalse);
#endregion
}
public override void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue)
{
if (cell.TextPosition != TextPositionEnum.Center)
{
htmlClassList.Remove("center");
}
foreach (var htmlClass in htmlClassList)
{
tagBuilder.AddCssClass(htmlClass);
}
}
public bool IsText(int row, int column)
{
CellIndex cellIndex = new CellIndex(row, column);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Text)
{
return(true);
}
return(false);
}
public void SetTextCellValueUsingString2()
{
#region radspreadsheet-model-working-with-cells-cell-value-types_16
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("Total");
#endregion
#region radspreadsheet-model-working-with-cells-cell-value-types_17
RangePropertyValue <ICellValue> rangeValue = worksheet.Cells[0, 0].GetValue();
ICellValue value = rangeValue.Value;
#endregion
}
public override void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue)
{
if (cell.TextPosition == TextPositionEnum.Left)
{
htmlClassList.Remove("center");
}
foreach (var htmlClass in htmlClassList)
{
tagBuilder.AddCssClass(htmlClass);
}
tagBuilder.MergeAttribute("style", $"top:{TopOffset}px;");
}
static bool IsCellEmpty(ICellValue cellValue)
{
var value = cellValue.ToString();
value = value == "0" ? "" :
value == "0,0" ? "" :
value == "0,00" ? "" :
value == "0,000" ? "" :
value == "0" ? "" :
value == "0.0" ? "" :
value == "0.00" ? "" :
value == "0.000" ? "" : value;
return(!(value != null && value != ""));
}
/// <summary>
/// this routine starts in row of column and looks downwards for a string.
/// It returns the found string or string.empty in case there was no string
/// </summary>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
public string FindString(int column, int row)
{
for (int i = row; i < RowCount; i++)
{
CellIndex cellIndex = new CellIndex(i, column);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Text)
{
return(cellValue.RawValue);
}
}
return(string.Empty);
}
void setPredefinedOptions(ExcelRange selectedRange, ICell cell, ICellValue cellValue)
{
var horizontalAlignment = ExcelHorizontalAlignment.Left;
switch (cell.TextPosition)
{
case TextPositionEnum.Left:
horizontalAlignment = ExcelHorizontalAlignment.Left;
break;
case TextPositionEnum.Center:
horizontalAlignment = ExcelHorizontalAlignment.Center;
break;
case TextPositionEnum.Right:
horizontalAlignment = ExcelHorizontalAlignment.Right;
break;
}
selectedRange.Style.HorizontalAlignment = horizontalAlignment;
var numberFormat = "General";
if (cellValue is DoubleValue doubleValue)
{
numberFormat = doubleValue.NumberFormat;
}
if (cellValue is DateValue)
{
numberFormat = "dd/MM/yyyy";
}
if (cellValue is DecoratedDoubleValue decorated)
{
if (decorated.BeforeText != null)
{
numberFormat = $"\"{decorated.BeforeText} \"{numberFormat}";
}
if (decorated.AfterText != null)
{
numberFormat = $"{numberFormat}\" {decorated.AfterText}\"";
}
}
selectedRange.Style.Numberformat.Format = numberFormat;
}
public override void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue)
{
base.RenderHtmlStyle(tagBuilder, cell, cellValue);
if (IsCellEmpty(cellValue))
{
tagBuilder.AddCssClass("hidden");
}
else
{
foreach (var htmlClass in _htmlClasses)
{
tagBuilder.AddCssClass(htmlClass);
}
}
}
protected decimal calculateCellValue(ICellValue cellValue)
{
if (cellValue.Value is int intValue)
{
return(intValue);
}
else if (cellValue.Value is double doubleValue)
{
return((decimal)doubleValue);
}
else if (cellValue.Value is decimal decimalValue)
{
return(decimalValue);
}
return(0);
}
public string GetText(int row, int column)
{
CellIndex cellIndex = new CellIndex(row, column);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Empty)
{
return(string.Empty);
}
if (cellValue.ValueType != CellValueType.Text)
{
if (cellValue.ValueType == CellValueType.Number)
{
return(cellValue.RawValue.ToString());
}
throw new Exception("Value ist not a text");
}
return(cellValue.RawValue);
}
public CellSelection FindTextStartsWith(string searchText)
{
for (int i = 0; i < RowCount; i++)
{
CellIndex cellIndex = new CellIndex(i, 0);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Text)
{
string content = cellValue.RawValue;
content = content.Trim();
if (content.Contains(searchText))
{
return(selection);
}
}
}
return(null);
}
public int FindRowforTextStartsWith(string searchText)
{
for (int i = 0; i < RowCount; i++)
{
CellIndex cellIndex = new CellIndex(i, 0);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Text)
{
string content = cellValue.RawValue;
content = content.Replace(" ", "");
if (content.Contains(searchText))
{
return(i);
}
}
}
return(-1);
}
public int FindTextInHeadline(int row, string searchText1, string searchText2)
{
for (int i = 0; i < ColumnCount; i++)
{
CellIndex cellIndex = new CellIndex(row, i);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Text)
{
string content = cellValue.RawValue;
content = content.Trim();
if (content.Contains(searchText1) && content.Contains(searchText2))
{
return(i);
}
}
}
return(-1);
}
protected override string ConvertRowIndexToNameOverride(HeaderNameRenderingConverterContext context, int rowIndex)
{
int firstVisibleColumnIndex = context.VisibleRange.FromIndex.ColumnIndex;
int firstTableColumnIndex = this.TableCellRange.FromIndex.ColumnIndex;
int lastTableColumnIndex = this.TableCellRange.ToIndex.ColumnIndex;
string result = base.ConvertRowIndexToNameOverride(context, rowIndex);
if ((firstVisibleColumnIndex > firstTableColumnIndex && firstVisibleColumnIndex <= lastTableColumnIndex) &&
this.TableCellRange.Contains(rowIndex, firstVisibleColumnIndex))
{
CellSelection selection = context.Worksheet.Cells[rowIndex, firstTableColumnIndex];
ICellValue cellValue = selection.GetValue().Value;
CellValueFormat cellFormat = selection.GetFormat().Value;
result = cellValue.GetResultValueAsString(cellFormat);
}
return(result);
}
/// <summary>
/// this routine starts in row of column and looks downwards for a date.
/// if it finds one, it is returned, if not Datetime.minvalue is returned
/// </summary>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
public DateTime FindDate(int column, int row)
{
int serialDate = 0;
for (int i = row; i < RowCount; i++)
{
CellIndex cellIndex = new CellIndex(i, column);
CellSelection selection = sheet.Cells[cellIndex];
ICellValue cellValue = selection.GetValue().Value;
if (cellValue.ValueType == CellValueType.Number)
{
if (int.TryParse(cellValue.RawValue, out serialDate))
{
return(FromExcelSerialDate((int)serialDate));
}
break;
}
}
return(DateTime.MinValue);
}
protected override void RenderExcelStyle(ExcelRange selectedRange, IRow row, ICell cell, ICellValue cellValue)
{
base.RenderExcelStyle(selectedRange, row, cell, cellValue);
if (cellValue == null)
{
return;
}
var value = calculateCellValue(cellValue);
if (value > PositiveZero)
{
if (!_isBgColor)
{
selectedRange.Style.Font.Color.SetColor(greenFontColor);
}
else
{
selectedRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
selectedRange.Style.Fill.BackgroundColor.SetColor(greenBgColor);
}
}
else if (value < NegativeZero)
{
if (!_isBgColor)
{
selectedRange.Style.Font.Color.SetColor(redFontColor);
}
else
{
selectedRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
selectedRange.Style.Fill.BackgroundColor.SetColor(redBgColor);
}
}
}
protected override void RenderExcelStyle(ExcelRange selectedRange, IRow row, ICell cell, ICellValue cellValue)
{
selectedRange.Style.Border.BorderAround(ExcelBorderStyle.Medium, borderColor);
selectedRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
selectedRange.Style.Fill.BackgroundColor.SetColor(bgColor);
selectedRange.Style.Font.Bold = true;
selectedRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
public abstract void RenderHtmlStyle(TagBuilder tagBuilder, ICell cell, ICellValue cellValue);
public void Render(ExcelRange selectedRange, IRow row, ICell cell, ICellValue cellValue)
{
setPredefinedOptions(selectedRange, cell, cellValue);
RenderExcelStyle(selectedRange, row, cell, cellValue);
}
protected override void RenderExcelStyle(ExcelRange selectedRange, IRow row, ICell cell, ICellValue cellValue)
{
base.RenderExcelStyle(selectedRange, row, cell, cellValue);
if (!IsCellEmpty(cellValue))
{
if (_bgColor.HasValue)
{
selectedRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
selectedRange.Style.Fill.BackgroundColor.SetColor(_bgColor.Value);
}
if (_fontColor.HasValue)
{
selectedRange.Style.Font.Color.SetColor(_fontColor.Value);
}
}
}
protected override void RenderExcelStyle(ExcelRange selectedRange, IRow row, ICell cell, ICellValue cellValue)
{
selectedRange.Merge = true;
selectedRange.Worksheet.Row(selectedRange.Start.Row).Height = SPERATOR_HEIGTH;
}
