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 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);
}
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 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);
}
/// <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);
}
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);
}
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);
}
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 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);
}
/// <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);
}