public static void SetAndGetValueOfCellA1()
{
#region radspreadsheet-features-formulas_0
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
workbook.ActiveWorksheet.Cells[0, 0].SetValue("=3+4");
#endregion
#region radspreadsheet-features-formulas_1
FormulaCellValue formulaCellValue = workbook.ActiveWorksheet.Cells[0, 0].GetValue().Value as FormulaCellValue;
if (formulaCellValue != null)
{
RadExpression expression = formulaCellValue.Value;
//expression is AdditionExpression with operands 3 and 4
CellValueFormat format = workbook.ActiveWorksheet.Cells[0, 0].GetFormat().Value;
string valueAsString = formulaCellValue.GetValueAsString(format);
//valueAsString = "=3+4"
string resultValueAsString = formulaCellValue.GetResultValueAsString(format);
//resultAsString = "7"
CellValueType valueType = formulaCellValue.ValueType;
//valueType = Formula
CellValueType resultValueType = formulaCellValue.ResultValueType;
//resultValueType = Number
}
#endregion
}
public void GetFormat()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)];
CellSelection cellSelectionA2B3 = worksheet.Cells[new CellRange(new CellIndex(1, 0), new CellIndex(2, 1))];
CellValueFormat cellSelectioA1Format = cellSelectionA1.GetFormat().Value;
CellValueFormat cellSelectioA2Format = cellSelectionA2B3.GetFormat().Value;
}
public void ApplyFormat()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)];
CellValueFormat scientificFormat = new CellValueFormat("0.00E+00");
cellSelectionA1.SetFormat(scientificFormat);
CellSelection cellSelectionA2B3 = worksheet.Cells[new CellRange(new CellIndex(1, 0), new CellIndex(2, 1))];
CellValueFormat percentageFormat = new CellValueFormat("0.00%");
cellSelectionA2B3.SetFormat(percentageFormat);
}
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 static Workbook CreateWorkBook(RadGridView grid)
{
Workbook book = null;
using (var stream = new MemoryStream())
{
int index = 0;
foreach (GroupDescriptor group in grid.GroupDescriptors)
{
grid.Columns.Insert(index++, new GridViewDataColumn() { DataMemberBinding = new System.Windows.Data.Binding(group.Member) });
}
grid.ElementExporting += elementExporting;
var exportOptions = new GridViewExportOptions()
{
Format = ExportFormat.Csv,
ShowColumnFooters = grid.ShowColumnFooters,
ShowColumnHeaders = grid.ShowColumnHeaders,
ShowGroupFooters = grid.ShowGroupFooters,
Culture = new CultureInfo("fr-FR"),
Items = grid.Items
};
grid.Export(stream, exportOptions);
grid.ElementExporting -= elementExporting;
stream.Position = 0;
var csvProvider = new CsvFormatProvider();
book = csvProvider.Import(stream);
// /!\ telerik date format workaround
var pattern = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
var dateCellFormat = new CellValueFormat(pattern);
var dateColsIdx = grid.Columns.Cast<GridViewColumn>().OfType<GridViewDataColumn>().Where(col => col.DataType == typeof(DateTime)).Select(col => col.DisplayIndex).ToList();
if (dateColsIdx.Any())
{
var skipFirstRow = true;
var rowCount = grid.Items.Count;
for (int i = skipFirstRow ? 1 : 0; i < rowCount; i++)
{
foreach (var colIdx in dateColsIdx)
{
book.Worksheets[0].Cells[i, colIdx].SetFormat(dateCellFormat);
}
}
}
foreach (GroupDescriptor group in grid.GroupDescriptors)
{
grid.Columns.RemoveAt(0);
}
}
return book;
}
