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.Spreadsheet
Telerik.Documents.Spreadsheet.FormatProviders.OpenXml
-
Set the
@ref
attribute of the Spreadsheet, so that you can call component methods. -
Use the
ExportToExcelAsync()
Spreadsheet method to get the Excel file as abyte[]
. -
Create a
MemoryStream
that 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
XlsxFormatProvider
to import the Excel file as aWorkbook
. -
Get the desired
Worksheet
object from the workbook. -
Get the desired
CellRange
from the worksheet. -
Get each cell value (
ICellValue
object), cell value type (CellValueType
object), and cell format (CellValueFormat
object) to know more about the cell content. -
Use
ICellValue.RawValue
orICellValue.GetResultValueAsString(CellValueFormat cellFormat)
to obtain the actual cell content. -
Excel stores dates as numbers. Use
FormatHelper.ConvertDoubleToDateTime(Convert.ToDouble(ICellValue.RawValue))
to retrieve anyDateTime
values.
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==";
}