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
}
private void SetCellValue(CellSelection cell, object value)
{
if (value == null)
{
value = string.Empty;
}
switch (Type.GetTypeCode(value.GetType()))
{
case TypeCode.Byte:
case TypeCode.Char:
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.SByte:
case TypeCode.Single:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64: cell.SetValue(Convert.ToDouble(value)); break;
case TypeCode.DateTime: cell.SetValue((DateTime)value); break;
case TypeCode.Boolean: cell.SetValue((bool)value); break;
default: cell.SetValue(value.ToString()); break;
}
}
private Workbook GenerateWorkbook()
{
var export = pivot.GenerateExport();
Workbook workbook = new Workbook();
workbook.History.IsEnabled = false;
var worksheet = workbook.Worksheets.Add();
workbook.SuspendLayoutUpdate();
int rowCount = export.RowCount;
int columnCount = export.ColumnCount;
var allCells = worksheet.Cells[0, 0, rowCount - 1, columnCount - 1];
allCells.SetFontFamily(new ThemableFontFamily(pivot.FontFamily));
allCells.SetFontSize(12);
allCells.SetFill(GenerateFill(pivot.Background));
foreach (var cellInfo in export.Cells)
{
int rowStartIndex = cellInfo.Row;
int rowEndIndex = rowStartIndex + cellInfo.RowSpan - 1;
int columnStartIndex = cellInfo.Column;
int columnEndIndex = columnStartIndex + cellInfo.ColumnSpan - 1;
CellSelection cellSelection = worksheet.Cells[rowStartIndex, columnStartIndex];
var value = cellInfo.Value;
if (value != null)
{
cellSelection.SetValue(Convert.ToString(value));
cellSelection.SetVerticalAlignment(RadVerticalAlignment.Center);
cellSelection.SetHorizontalAlignment(GetHorizontalAlignment(cellInfo.TextAlignment));
int indent = cellInfo.Indent;
if (indent > 0)
{
cellSelection.SetIndent(indent);
}
}
cellSelection = worksheet.Cells[rowStartIndex, columnStartIndex, rowEndIndex, columnEndIndex];
SetCellProperties(cellInfo, cellSelection);
}
for (int i = 0; i < columnCount; i++)
{
var columnSelection = worksheet.Columns[i];
columnSelection.AutoFitWidth();
//NOTE: workaround for incorrect autofit.
var newWidth = worksheet.Columns[i].GetWidth().Value.Value + 15;
columnSelection.SetWidth(new ColumnWidth(newWidth, false));
}
workbook.ResumeLayoutUpdate();
return(workbook);
}
static void Main(string[] args)
{
Console.WriteLine("Hello, would you like to create a workbook? [Y/y][N/n]:");
var result = Console.ReadLine();
if (result?.ToLower() == "y")
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection selection = worksheet.Cells[1, 1]; //B2 cell
selection.SetValue("Hello RadSpreadProcessing");
string fileName = "SampleFile.xlsx";
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
using Stream output = new FileStream(fileName, FileMode.Create);
formatProvider.Export(workbook, output);
Console.WriteLine("Done!");
}
}
private void SetCellValue(string value, CellIndex cellIndex, string fontFamilyName, bool isItalic, bool isBold)
{
CellSelection cell = this.Worksheet.Cells[cellIndex];
cell.SetValue(value);
cell.SetFontFamily(new ThemableFontFamily(fontFamilyName));
cell.SetIsItalic(isItalic);
cell.SetIsBold(isBold);
}
private void SetDocumentTitle()
{
CellSelection companyNameCells = worksheet.Cells[1, 1, 1, 4];
companyNameCells.Merge();
companyNameCells.SetValue("My Company");
companyNameCells.SetHorizontalAlignment(RadHorizontalAlignment.Left);
CellSelection expenseReportCells = worksheet.Cells[2, 1, 2, 4];
expenseReportCells.Merge();
expenseReportCells.SetValue("Expense Report");
expenseReportCells.SetHorizontalAlignment(RadHorizontalAlignment.Right);
CellSelection periodCells = worksheet.Cells[3, 1, 3, 4];
periodCells.Merge();
periodCells.SetValue("1 Jan 2014 - 31 Mar 2014");
periodCells.SetHorizontalAlignment(RadHorizontalAlignment.Right);
}
public void IterateWorksheets()
{
#region radspreadsheet-model-working-with-worksheets-iterate-through-worksheets_1
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
workbook.Worksheets.Add();
workbook.Worksheets.Add();
ThemableColor foregroundColor = new ThemableColor(Colors.Red);
Color backgroundColor = Colors.Green;
IFill backgroundFill = new PatternFill(PatternType.Solid, backgroundColor, backgroundColor);
foreach (Worksheet worksheet in workbook.Worksheets)
{
CellSelection cell = worksheet.Cells[0, 0];
cell.SetValue("The name of this worksheet is: " + worksheet.Name);
cell.SetForeColor(foregroundColor);
cell.SetFill(backgroundFill);
}
#endregion
}
public void GettingStarted()
{
#region radspreadprocessing-getting-started_0
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
#endregion
#region radspreadprocessing-getting-started_1
CellSelection selection = worksheet.Cells[1, 1]; //B2 cell
selection.SetValue("Hello RadSpreadProcessing");
#endregion
#region radspreadprocessing-getting-started_2
string fileName = "Hello.xlsx";
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
using (FileStream input = new FileStream(fileName, FileMode.Open))
{
workbook = formatProvider.Import(input);
}
#endregion
}
public async Task <byte[]> ExportPdf <T>(IQueryable <T> data, DataSourceRequest gridRequest)
{
var workbook = new Workbook();
workbook.Name = "workbook-1";
// performance
workbook.History.IsEnabled = false;
workbook.SuspendLayoutUpdate();
workbook.Sheets.Add(SheetType.Worksheet);
Worksheet worksheet = workbook.ActiveWorksheet;
var dataResult = await data.ToDataSourceResultAsync(gridRequest);
List <T> dataToExport = (dataResult.Data as IEnumerable <T>).ToList();
int currRow = 0;
Type typeParameterType = typeof(T);
var fieldsList = typeParameterType.GetProperties();
//some styling for the cells - borders in this example
ThemableColor black = new ThemableColor(Telerik.Documents.Media.Color.FromArgb(0, 0, 0, 0));
CellBorders desiredBorders = new CellBorders(
new CellBorder(CellBorderStyle.Medium, black), // Left border
new CellBorder(CellBorderStyle.Medium, black), // Top border
new CellBorder(CellBorderStyle.Medium, black), // Right border
new CellBorder(CellBorderStyle.Medium, black), // Bottom border
new CellBorder(CellBorderStyle.Thin, black), // Inside horizontal border
new CellBorder(CellBorderStyle.Thin, black), // Inside vertical border
new CellBorder(CellBorderStyle.None, black), // Diagonal up border
new CellBorder(CellBorderStyle.None, black) // Diagonal down border
);
//column sizes
for (int i = 0; i < fieldsList.Length; i++)
{
worksheet.Columns[i].SetWidth(new ColumnWidth(120, true));
}
//automatic resizing is convenient, but comes with some performance hit
//it would also have to be moved after setting the content
//for (int i = 0; i < fieldsList.Length; i++)
//{
// worksheet.Columns[i].AutoFitWidth();
//}
for (int i = 0; i < fieldsList.Length; i++)
{
CellSelection currCell = worksheet.Cells[0, i];
currCell.SetValue(fieldsList[i].Name);
currCell.SetBorders(desiredBorders);
}
currRow++;
// content
for (int i = 0; i < dataToExport.Count; i++)
{
for (int columnIndex = 0; columnIndex < fieldsList.Length; columnIndex++)
{
var cellValue = GetFieldValue(dataToExport[i], fieldsList[columnIndex].Name);
CellSelection currCell = worksheet.Cells[currRow, columnIndex];
SetCellValue(currCell, cellValue);
currCell.SetBorders(desiredBorders);
}
currRow++;
}
// performance
workbook.ResumeLayoutUpdate();
//convert the excel workbook to a pdf
var pdfFormatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.Pdf.PdfFormatProvider();
byte[] fileBytes = null;
using (MemoryStream ms = new MemoryStream())
{
pdfFormatProvider.Export(workbook, ms);
fileBytes = ms.ToArray();
}
return(await Task.FromResult(fileBytes));
}
