Iterate Spreadsheet Data and Get Cell Values
Environment
| Product | Spreadsheet for Blazor |
Description
This KB article answers the following questions:
- How to iterate Spreadsheet cells?
- How to get the Spreadsheet cell values?
- How to obtain the data that users entered in the Blazor Spreadsheet?
Solution
- Install the required Telerik Document Processing NuGet packages:
Telerik.Documents.SpreadsheetTelerik.Documents.Spreadsheet.FormatProviders.OpenXml
- Set the
@refattribute of the Spreadsheet, so that you can call component methods. - Use the
ExportToExcelAsync()Spreadsheet method to get the Excel file as abyte[]. -
Create a
MemoryStreamthat contains the Excel file byte array.The steps below use the Telerik RadSpreadProcessing API and are outside the scope of Telerik UI for Blazor.
Use an
XlsxFormatProviderto import the Excel file as aWorkbook.- Get the desired
Worksheetobject from the workbook. - Get the desired
CellRangefrom the worksheet. - Iterate the cell range.
- Get each cell value (
ICellValueobject), cell value type (CellValueTypeobject), and cell format (CellValueFormatobject) to know more about the cell content. - Use
ICellValue.RawValueorICellValue.GetResultValueAsString(CellValueFormat cellFormat)to obtain the actual cell content. -
Excel stores dates as numbers. Use
FormatHelper.ConvertDoubleToDateTime(Convert.ToDouble(ICellValue.RawValue))to retrieve anyDateTimevalues.
Iterate the Spreadsheet cells and get their values
@using Telerik.Windows.Documents.Spreadsheet.FormatProviders
@using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx
@using Telerik.Windows.Documents.Spreadsheet.Formatting
@using Telerik.Windows.Documents.Spreadsheet.Formatting.FormatStrings
@using Telerik.Windows.Documents.Spreadsheet.Model
<p>
<TelerikButton ThemeColor="@ThemeConstants.Button.ThemeColor.Primary"
OnClick="@OnButtonClick">Get Cell Values</TelerikButton>
<TelerikLoader Visible="@LoaderVisible" />
</p>
<TelerikSpreadsheet @ref="@SpreadsheetRef"
Data="@SpreadsheetData"
Height="300px">
</TelerikSpreadsheet>
@if (SpreadsheetValues.Any())
{
<h3>Cell Values</h3>
<table class="spreadsheet-values">
@foreach (KeyValuePair<int, List<object?>> row in SpreadsheetValues)
{
<tr>
@foreach (var cell in row.Value)
{
<td>@cell</td>
}
</tr>
}
</table>
<h3>Cell Strings</h3>
<table class="spreadsheet-values">
@foreach (KeyValuePair<int, List<string>> row in SpreadsheetStrings)
{
<tr>
@foreach (var cell in row.Value)
{
<td>@cell</td>
}
</tr>
}
</table>
}
<style>
.spreadsheet-values {
margin: 1em;
}
.spreadsheet-values td {
border: 1px solid #ccc;
padding: .2em .4em;
}
</style>
@code {
private TelerikSpreadsheet? SpreadsheetRef { get; set; }
private byte[]? SpreadsheetData { get; set; }
private bool LoaderVisible { get; set; }
private Dictionary<int, List<object?>> SpreadsheetValues { get; set; } = new();
private Dictionary<int, List<string>> SpreadsheetStrings { get; set; } = new();
private async Task OnButtonClick()
{
if (SpreadsheetRef == null)
{
return;
}
SpreadsheetValues = new();
SpreadsheetStrings = new();
LoaderVisible = true;
// Refresh the UI: https://docs.telerik.com/blazor-ui/components/loader/overview#creating-blazor-loader
await Task.Delay(1);
byte[] excelFileToRead = await SpreadsheetRef.ExportToExcelAsync();
using MemoryStream ms = new MemoryStream(excelFileToRead);
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
Workbook workbook = formatProvider.Import(ms);
Worksheet worksheet = workbook.Worksheets.First();
CellRange cellRange = worksheet.UsedCellRange;
for (int rowIndex = cellRange.FromIndex.RowIndex; rowIndex <= cellRange.ToIndex.RowIndex; rowIndex++)
{
var rowValues = new List<object?>();
var rowStringValues = new List<string>();
for (int columnIndex = cellRange.FromIndex.ColumnIndex; columnIndex <= cellRange.ToIndex.ColumnIndex; columnIndex++)
{
CellSelection cell = worksheet.Cells[rowIndex, columnIndex];
ICellValue cellValue = cell.GetValue().Value;
CellValueType valueType = cellValue.ValueType;
CellValueFormat cellFormat = cell.GetFormat().Value;
if (valueType == CellValueType.Number &&
cellFormat.FormatStringInfo.FormatType == FormatStringType.DateTime)
{
// Excel stores dates as numbers.
// Excel date format strings differ from .NET format strings.
DateTime? actualDateTimeValue = FormatHelper.ConvertDoubleToDateTime(Convert.ToDouble(cellValue.RawValue));
rowValues.Add(actualDateTimeValue);
rowStringValues.Add(cellValue.GetResultValueAsString(cellFormat));
}
else if (valueType == CellValueType.Number)
{
double actualNumericValue = Convert.ToDouble(cellValue.RawValue);
rowValues.Add(actualNumericValue);
rowStringValues.Add(actualNumericValue.ToString(cellFormat.FormatString));
}
else if (valueType == CellValueType.Boolean)
{
bool actualBooleanValue = Convert.ToBoolean(cellValue.RawValue);
rowValues.Add(actualBooleanValue);
rowStringValues.Add(actualBooleanValue.ToString().ToLowerInvariant());
}
else if (valueType == CellValueType.Text || valueType == CellValueType.Empty)
{
rowValues.Add(cellValue.RawValue);
rowStringValues.Add(cellValue.RawValue);
}
}
SpreadsheetValues.Add(rowIndex, rowValues);
SpreadsheetStrings.Add(rowIndex, rowStringValues);
}
LoaderVisible = false;
}
protected override void OnInitialized()
{
SpreadsheetData = Convert.FromBase64String(Base64ExcelSource);
base.OnInitialized();
}
private const string Base64ExcelSource = "UEsDBAoAAAAAAFheG1kAAAAAAAAAAAAAAAAJAAAAZG9jUHJvcHMvUEsDBAoAAAAIAFheG1nNjhd0PQEAAIECAAARAAAAZG9jUHJvcHMvY29yZS54bWylkl9rwjAUxd+FfYeQ9zZtdepKW2EbwtiEwSobewvJVcuaPyTZqt9+adVimW97TM7J795zSLbYixr9gLGVkjmOwwgjkEzxSm5zvC6XwRwj66jktFYScnwAixfFzQhlTKdMGXg1SoNxFVjkUdKmTOd455xOCbFsB4La0DukFzfKCOr80WyJpuyLboEkUTQlAhzl1FHSAgPdE/EIoROVs56qv03dMTgjUIMA6SyJw5jg3uvACHv1QaeQAVhU7qDhqvss9ui9rXpj0zRhM+6sPkVMPlYvb13goJJtZQxw0c7JOEuZAeqUKZ5BcoXWTxm5uOxMvs6aWrfy1W8q4PeHC+9f7cTt0hw5wJFfLj1GOSvv44fHcomLJEomQTQPklkZx+ltlE7uwul89tmuMWAMweI08F/kM6QYdUGGf6b4BVBLAwQKAAAACABYXhtZlbIrOIkBAABdAwAAEAAAAGRvY1Byb3BzL2FwcC54bWydU8Fu2zAMvQfoPwi6N7K7YhgCWcXQrthhwwIkbc+aTMdCFckQWSPZ10+y6yRbkEtv0nvkIx8pybvd1rEeItrgK17OC87Am1Bbv6n40/rx+gtnSNrX2gUPFd8D8jt1NZPLGDqIZAFZkvBY8ZaoWwiBpoWtxnmifWKaELea0jVuRGgaa+AhmLcteBI3RfFZwI7A11BfdwdBPiouevqoaB1M7g+f1/su6akZY/Jr1zlrNCWj6qc1MWBoiH3bGXBSnJI5OMmtwLxFS3tVSHF6zfTKaAf3qYJqtEOQ4ghk+jvoPMClthEzkKCeFj0YCpGh/ZPmeMPZb42Q+6t4r6PVnvgY+x49YhM0gq5DiuolxFdsAQilOIBTqjjPvShnb1U5JKTDxfwRGXofzIn/3cm1JQf4q1nqSJf8lqd+h465OjpaZTfliZuzomc15A/rX/GpW4cHTTDt4V9wWFWrI9RpgYdVHYBhVamh6HLWfav9Buop7Jx4f0TP42dR5e28+FQUw9uZsJkUx3+h/gJQSwMECgAAAAAAWF4bWQAAAAAAAAAAAAAAAAYAAABfcmVscy9QSwMECgAAAAgAWF4bWQXcJMnyAAAAlQIAAAsAAABfcmVscy8ucmVsc62Sz0rEMBDG74LvEOa+ne4KItJ2L4uwN5H6AGMy/UPbTEiidt/eeHIrq1Rwbsl8+b5fhin28zSqN/ahF1vCNstBsdVietuW8Fw/bO5AhUjW0CiWSzhxgH11faXOqnjikWIyCF3vgkqONpTQxejuEYPueKKQiWObOo34iWI6+hYd6YFaxl2e36I/94BqEfA9Qx1NCf5obkDVJ8drsqRpes0H0a8T23ghEnmObA2bjfPpvY99+qmqybccSzCiH9N1QHIuS9aA6wh36wl/ngZOHMlQJNTi+Xe+T8VfALf/OcKl4otuHvFd/PAiMlxiK3CxQNUHUEsDBAoAAAAAAFheG1kAAAAAAAAAAAAAAAADAAAAeGwvUEsDBAoAAAAAAFheG1kAAAAAAAAAAAAAAAAJAAAAeGwvX3JlbHMvUEsDBAoAAAAIAFheG1lG4bv54wAAAEgCAAAaAAAAeGwvX3JlbHMvd29ya2Jvb2sueG1sLnJlbHOtkc1qwzAMgO+FvYPQfXHSwRgjTi9l0OvWPYCJlTg0sY2l/eTt520QUihsh4JBSMKfPqR69zmN8E6Jh+A1VkWJQL4NdvC9xtfj0+0DAovx1ozBk8aZGHfNzaZ+ptFI/sRuiAyZ4lmjE4mPSnHraDJchEg+d7qQJiM5Tb2Kpj2ZntS2LO9VWjOw2QDkB3DGhoPVmA62QjjOkf4zI3Td0NI+tG8TebkwSn2EdGJHJBlqUk+icSmx+glVkakI6tvqotD2mkIs85g3u9j85n8p3F1VwZlE9kVSPv3aZF1ehGp1dv/mC1BLAwQKAAAACABYXhtZz2ThMVwBAABdAgAADwAAAHhsL3dvcmtib29rLnhtbI1Ry27CMBC8I/UfLN9LHgQKKAGpLxWpaiuVwtnEG2Lh2JHtEPj7bhKFwq23mVnv7M46Xp4KSY5grNAqocHQpwRUqrlQ+4T+rF/vp5RYxxRnUitI6BksXS7uBnGtzWGn9YGggbIJzZ0r555n0xwKZoe6BIWVTJuCOaRm79nSAOM2B3CF9ELfn3gFE4p2DnPzHw+dZSKFZ51WBSjXmRiQzOH6NhelpYsBIXEmJGy6UISV5QcrcPWTpEQy6164cMATOkaqa7gRTFU+VkIimY38ESVea9dn/TKEQ8Yq6da4YT8BzxZGYTjpXzcvNwJq27Cr7kYjp61QXNcJDSM89blnEc6uW7wV3OVoOQ1mF+0NxD53CX2YNVo3xbsZE7dnvYJEtZm/Gxy0X+iQHoUVOwnIG32FMbFm5gKBWfHg4v3n1tCUyRSjZ5WUTwg/1btmXWtTaV2i8STs2mOvz7v4BVBLAwQKAAAAAABYXhtZAAAAAAAAAAAAAAAADgAAAHhsL3dvcmtzaGVldHMvUEsDBAoAAAAAAFheG1kAAAAAAAAAAAAAAAAMAAAAeGwvZHJhd2luZ3MvUEsDBAoAAAAAAFheG1kAAAAAAAAAAAAAAAASAAAAeGwvZHJhd2luZ3MvX3JlbHMvUEsDBAoAAAAAAFheG1kAAAAAAAAAAAAAAAAUAAAAeGwvd29ya3NoZWV0cy9fcmVscy9QSwMECgAAAAgAWF4bWXf0yx2DAgAAogkAABgAAAB4bC93b3Jrc2hlZXRzL3NoZWV0MS54bWy1lttu4jAQhu+R9h0s3zeGHKCgkKotqrYXK626p2vjOMRqbEe2KeXt19gBuqEVWNoiAfE4/mbm/604+c0rb8ALVZpJMYejaAgBFUSWTKzm8NfPh6trCLTBosSNFHQOt1TDm+LLIN9I9axrSg2wBKHnsDamnSGkSU051pFsqbAzlVQcGztUK6RbRXHpFvEGxcPhGHHMBPSEGSeXQDhWz+v2ikjeYsOWrGFm61h7jLqEIquKEbqQZM2pML4WRRtLlELXrNV72usoxad1cUaU1LIyka2jg522N0VThAkEnMweV0IqvGysgo4IiwEAIC+ZTb+THihazeHtaLZIIEDFwM061G9GN9rd/TYCgMHLH7ShxNDS+gbBzo+llM+72UcbskZ2q7q16LDY50Zv8cd8D06l7wq4OmfldkE1sRpZYhRnEJC1NpJ/pWxVG5e4pBVeN+ZJbg7BDPqc3dS9bP6w0tRzOI2GkzhL03EaX2eTURofmu2KJLLZN7uP+ajdhNudeiO4j/Y+nNn9a8Xj+NX9+zq7vB+u2nTz1+8W1gnnizpKtMAG/6Otu03JDVBOkfeV61XQGxaDD+dysuPeWrB2eAvUsOg31B/nL8UwRy+9+2wz/Yij34XTR5fT78Pp8eX0RTg9OUfPkbXzZCMeXY4/y+XY9RK7XsRlPsRnuzny7+JgrdIAnz09Cag+zcbZJMDrt/Uv/88+Ped18lleJ66XNEitaBxgtk+QBZidBZjt6eNQs6PpdJoMM/cJMD4JNv7s4+/U+O5Q9A94dyy985O3eEW/YbViQoOGVm47TCBQ/vRz10a27spqv5TGHkT7UW1fEajajWxDlZTmMPDnjfvm6PB2VfwFUEsDBAoAAAAIAFheG1lt/UQY0QIAANgMAAANAAAAeGwvc3R5bGVzLnhtbO1XXW/TMBR9n8R/sDweWd12W8dKkglNqrQHJsSGhIR4cBKnteaPyHFGw6/nOk7zsVJYBRqTIA/Nte+955w49r1pcLGWAt0zU3CtQjwZjTFiKtEpV8sQf7xdHL3GqLBUpVRoxUJcsQJfRC8OgsJWgt2sGLMHCC6AUUWIV9bmc0KKZMUkLUY6Zwo8mTaSWhiaJSlyw2hauEQpyHQ8nhFJucIdylwmjwGS1NyV+VGiZU4tj7ngtqrxPJRM5ldLpQ2NBcheT05o0ueoJ7ZoJE+MLnRmRwBLdJbxhG0rPifnBNAigAtUKRfSFijRpbIhPq5na57GhbzmS52CDElSUmHkPVcpLPjsFCMS7Ux4efjq8HA8Go/ffP7A0i/tcIgx+ylGTQoXWs2lHCaeNYkBaZ6jHmRadU8EIfVaze+U/qoWzgWZuKFzod50o+IbuqcC3JNWkJtOtNAGWVhi5lL7LkUl8zmXVPDY8IE3o5KLyvunA0/9wppMyeE1dytAOk3bGn94a3Wf9zh2q96I6Kt/azgVfYW7xW+p/C/w3xIYxA95nqHI5yfQW02F4kK0FWqKWyluPgqgH1hm1AIGqLFvqxzEKWhhmESA5OIembQ0tJpMTx/keUFORi0o1iaFJrqR1NZHPx8FgmUWAAxfrtzd6hx+Y20ttJkoSDldakWFo2gyOpIGuqZJmBA3rvF+yra51hnysa62Q4dw6jYmrFxjNvW+j9RC91DPeqhdxxhC9QgGxOusz9XtHoDauNq3j1pA2Dk0z0XlWowb9UJ+aXa7acNF1tk+5NNHkXcLsYm/LmXMzKLutrvzflve8Z7y6s+KpxR4srfA2dMKPP2bu2u29+qc/cHV8Ye9f87rg79dQFoXcvU6xNeOVvTOc1xyYbnyo4eFxMOn666GtNXGuo/wISn4UpbRUtjb1hnizn7HUl7KaRv1nt9r20R1to/y3Sgg3V+S6DtQSwMECgAAAAgAWF4bWXitoZPOAAAAwwEAABQAAAB4bC9zaGFyZWRTdHJpbmdzLnhtbJWPz0oEMQzG7wu+Q8jd7ehhkaXTBf/s0Yv6AN1O3ClM07HJLPr2BkE8ykAIfOT7JV/84bNMcKEmuXKPN9sOgTjVIfO5x7fX4/UdgmjkIU6VqccvEjyEq40XUTCWpcdRdd47J2mkEmVbZ2KbvNdWoppsZydzozjISKRlcrddt3MlZkZIdWHtcYewcP5Y6OFXhw2AFYCXHPzPqb3MMVkE2yXULoTheSkni+6dBu/M9y/wos0eWwE8RqUV9vtaJ4q8gointCINPMHxz25dNHwDUEsDBAoAAAAIAFheG1l4lwyVegEAAEgFAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbK2UXUvDMBSG7wf+h5BbabN5ISLtduH0UgfOHxCb0zZb80FO9vXvTTodQ9oOcVAIzcnzvm9PkmazvWrIFhxKo3M6SceUgC6MkLrK6cfyJXmgBD3XgjdGQ04PgHQ2vRlly4MFJIHWmNPae/vIGBY1KI6psaBDpTROcR9eXcUsL9a8AnY3Ht+zwmgP2ic+atDpiJBsDiXfNJ4870PlGMbqipKn49LollOpokScZ31QJctOKM73Qivb7bSyMGDloMFfGLe2kQX3oc62WvxqRPLdhDSQ7RqspcXbsICSXpe22msyzG4H2Y6ApixlAcIUGxWQNPBzx3fy1PC3cFKcFEAW3PlXroIi2zdsZ9z605h1Ohz2siFaB1xgDeBVk7ZjqrjU5z3qyYD+0ABeO0Er2roPm9fcgXj3LrTq6hnOtS9HiXvRosjaYXLlOCf9sz05Pp2JgsrCGYvhzjv4e5SfGxPpxAYhcF4CXjoPJ9cg/+/vh3ihBIgu/4y1/8HpF1BLAQIUAAoAAAAAAFheG1kAAAAAAAAAAAAAAAAJAAAAAAAAAAAAEAAAAAAAAABkb2NQcm9wcy9QSwECFAAKAAAACABYXhtZzY4XdD0BAACBAgAAEQAAAAAAAAAAAAAAAAAnAAAAZG9jUHJvcHMvY29yZS54bWxQSwECFAAKAAAACABYXhtZlbIrOIkBAABdAwAAEAAAAAAAAAAAAAAAAACTAQAAZG9jUHJvcHMvYXBwLnhtbFBLAQIUAAoAAAAAAFheG1kAAAAAAAAAAAAAAAAGAAAAAAAAAAAAEAAAAEoDAABfcmVscy9QSwECFAAKAAAACABYXhtZBdwkyfIAAACVAgAACwAAAAAAAAAAAAAAAABuAwAAX3JlbHMvLnJlbHNQSwECFAAKAAAAAABYXhtZAAAAAAAAAAAAAAAAAwAAAAAAAAAAABAAAACJBAAAeGwvUEsBAhQACgAAAAAAWF4bWQAAAAAAAAAAAAAAAAkAAAAAAAAAAAAQAAAAqgQAAHhsL19yZWxzL1BLAQIUAAoAAAAIAFheG1lG4bv54wAAAEgCAAAaAAAAAAAAAAAAAAAAANEEAAB4bC9fcmVscy93b3JrYm9vay54bWwucmVsc1BLAQIUAAoAAAAIAFheG1nPZOExXAEAAF0CAAAPAAAAAAAAAAAAAAAAAOwFAAB4bC93b3JrYm9vay54bWxQSwECFAAKAAAAAABYXhtZAAAAAAAAAAAAAAAADgAAAAAAAAAAABAAAAB1BwAAeGwvd29ya3NoZWV0cy9QSwECFAAKAAAAAABYXhtZAAAAAAAAAAAAAAAADAAAAAAAAAAAABAAAAChBwAAeGwvZHJhd2luZ3MvUEsBAhQACgAAAAAAWF4bWQAAAAAAAAAAAAAAABIAAAAAAAAAAAAQAAAAywcAAHhsL2RyYXdpbmdzL19yZWxzL1BLAQIUAAoAAAAAAFheG1kAAAAAAAAAAAAAAAAUAAAAAAAAAAAAEAAAAPsHAAB4bC93b3Jrc2hlZXRzL19yZWxzL1BLAQIUAAoAAAAIAFheG1l39MsdgwIAAKIJAAAYAAAAAAAAAAAAAAAAAC0IAAB4bC93b3Jrc2hlZXRzL3NoZWV0MS54bWxQSwECFAAKAAAACABYXhtZbf1EGNECAADYDAAADQAAAAAAAAAAAAAAAADmCgAAeGwvc3R5bGVzLnhtbFBLAQIUAAoAAAAIAFheG1l4raGTzgAAAMMBAAAUAAAAAAAAAAAAAAAAAOINAAB4bC9zaGFyZWRTdHJpbmdzLnhtbFBLAQIUAAoAAAAIAFheG1l4lwyVegEAAEgFAAATAAAAAAAAAAAAAAAAAOIOAABbQ29udGVudF9UeXBlc10ueG1sUEsFBgAAAAARABEACgQAAI0QAAAAAA==";
}