Custom spreadsheed control

[serge]

Hello, 

 

I would like to ask if the telerok spreadsheet for asp.net core is able to be linked to a (sqlserver) datasource, and display to the user only the grid (columns and custom column names), without excel-like row and column names, something like is defined by red rectangle in the attached picture

[serge]

the picture is here

[Ivan Danchev]

Hello,

The DataSource binding demo shows how to load data that comes from a database into the Spreadsheet: https://demos.telerik.com/kendo-ui/spreadsheet/datasource

As for showing only the columns that have data, this is not supported out of the box, but is achievable through configuration and custom CSS.

The Spreadsheet's width can be set accordingly, to reflect the number of columns that will be shown:

.HtmlAttributes(new { style = "width:980px" })

The number of columns should be specified:

.Columns(5)

And here's some custom CSS that hides and positions certain elements:

<style>
	.k-spreadsheet-row-header,
	.k-spreadsheet-column-header,
	.k-spreadsheet-top-corner,
	.k-spreadsheet-name-editor,
	.k-spreadsheet-formula-bar {
		display: none !important;
	}

	.k-spreadsheet-view {
		margin-left: -32px;
		margin-top: -20px;
	}

	.k-spreadsheet-selection {
		right: 50px;
	}
</style>

As mentioned above, the Spreadsheet is not designed to display only columns. The suggested custom CSS has not been tested in all possible scenarios, so visual glitches are possible to occur.

Regards,
Ivan Danchev
Progress Telerik

[serge]

Hello and thank you for your answer. 

The link you provided is for JQuery. I am trying the asp.net core from here:
https://demos.telerik.com/aspnet-core/spreadsheet/datasource

Copied all the 5 files (one cshtml and 4 cs classes) in my project, however there are a lot of missing classes (like ProductViewModel). 

 

I installed the trial using the package, but I don't have the "Telerik" in the "Extensions" menu of Visual Studio.

I used the installer interface to install the trial and I checked only the Asp.net core package when installing.
I observed that there it seems to be no way to modify the installation of asp.net core package trough the installer, once installed first time, because lauching it the second time and not cheching anything on the first step (cause .net core package already installed) disables the "next" button. So there is no way to check if VS extensions are or not set up, so, install or uninstall them.

So I searched the program Files folder and found a folder with examples here "C:\Program Files (x86)\Progress\Telerik UI for ASP.NET Core R1 2021\wrappers\aspnetcore\Examples\AspNet.Core\VS2019\"

So I tried the method you suggested and it works, however with some drawbacks and some other improvements needed. I attach you a screenshot with the "problem" zones. The scrollbars and the empty space on the right. 

 

The locking formula by disabling the cells it a good workaround it's OK for us.

I will test the control in the upcoming days and give more feedback.

Thank you for suggestions and have a good weekend!

[Ivan Danchev]

Serge,

With regard to the VS extensions, we would recommend downloading the .msi installation file from the UI for ASP.NET Core download page. VS must be closed during the installation, in order for the extensions to be properly installed. Once they are installed, they should appear in VS Extensions. If the issue persists, let us know.

As for the improvements you mention, the extra empty columns can be removed with the Columns configuration I posted in my previous reply:

.Columns(5)

The columns in the demo have the following width settings:

.Columns(columns =>
            {
                columns.Add().Width(100);
                columns.Add().Width(415);
                columns.Add().Width(145);
                columns.Add().Width(145);
                columns.Add().Width(145);
            })

so the Spreadsheet's width can be controlled through the HtmlAttributes configuration, so that all 5 columns are visible, e.g.,

.HtmlAttributes(new { style = "width:980px" })

As for the scroll bars, this is one of the possible visual glitches I mentioned. The horizontal scroll bar can be removed and the vertical adjusted a little bit with this CSS rule:

.k-spreadsheet-scroller {
	overflow-x: visible;
    margin-top: 20px;
    height: 95%;
}

Regards,
Ivan Danchev
Progress Telerik

[serge]

".Columns(5)" gives me an error: 

Error CS1503 "Argument 1: cannot convert from 'int' to 'System.Action<Kendo.Mvc.UI.Fluent.SpreadsheetSheetColumnFactory>"

 

here is my code

 

001.<div id="example">
002.    <script src="https://kendo.cdn.telerik.com/2021.1.330/js/jszip.min.js"></script>
003.
004.    <div class="configurator">
005.        <div class="header">Configurator</div>
006.        <div class="box-col">
007.            <h4>Save data changes</h4>
008.            <ul class="options">
009.                <li>
010.                    <button id="save">Save changes</button>
011.                    <button id="cancel">Cancel changes</button>
012.                </li>
013.            </ul>
014.        </div>
015.    </div>
016.
017.    @(Html.Kendo().Spreadsheet()
018..Name("spreadsheet")
019..HtmlAttributes(new { style = "width:100%" })
020..Toolbar(false)
021..Sheetsbar(false)
022..Events(e => e
023..DataBinding("onDataBinding")
024..DataBound("onDataBound")
025.)
026..Excel(excel => excel
027..ProxyURL(Url.Action("Index_Save", "Entreprises"))
028.)
029..Pdf(pdf => pdf
030..ProxyURL(Url.Action("Index_Save", "Entreprises"))
031.)
032..Sheets(sheets =>
033.{
034.sheets.Add()
035..Name("Products")
036..DataSource<EntrepriseDTO>(ds => ds
037..Custom()
038..Batch(true)
039..Transport(t => t
040.    .Read("onRead")
041.    .Submit("onSubmit")
042.)
043..Events(e => e.Change("onChange"))
044..Schema(s => s
045.    .Model(m =>
046.    {
047.        m.Id(p => p.Id);
048.    })
049.)
050.)
051..Columns(columns =>
052.{
053.columns.Add().Width(100);
054.columns.Add().Width(415);
055.columns.Add().Width(145);
056.columns.Add().Width(145);
057.columns.Add().Width(145);
058.columns.Add().Width(145);
059.})
060..Rows(rows =>
061.{
062.rows.Add().Height(40).Cells(cells =>
063.{
064.    cells.Add()
065.        .Bold(true)
066.        .Background("#9c27b0")
067.        .TextAlign(SpreadsheetTextAlign.Center)
068.        .Color("white");
069.
070.    cells.Add()
071.        .Bold(true)
072.        .Background("#9c27b0")
073.        .TextAlign(SpreadsheetTextAlign.Center)
074.        .Color("white");
075.
076.    cells.Add()
077.        .Bold(true)
078.        .Background("#9c27b0")
079.        .TextAlign(SpreadsheetTextAlign.Center)
080.        .Color("white");
081.
082.    cells.Add()
083.        .Bold(true)
084.        .Background("#9c27b0")
085.        .TextAlign(SpreadsheetTextAlign.Center)
086.        .Color("white");
087.
088.    cells.Add()
089.        .Bold(true)
090.        .Background("#9c27b0")
091.        .TextAlign(SpreadsheetTextAlign.Center)
092.        .Color("white");
093.
094.    cells.Add()
095.        .Bold(true)
096.        .Background("#9c27b0")
097.        .TextAlign(SpreadsheetTextAlign.Center)
098.        .Color("white");
099.});
100.});
101.})
102.)
103.</div>

and my controllers code

01.[HttpPost]
02.public ActionResult Index_Save(string contentType, string base64, string fileName)
03.{
04.    var fileContents = Convert.FromBase64String(base64);
05.
06.    return File(fileContents, contentType, fileName);
07.}
08.
09.public ActionResult Data_Source_Products_Read([DataSourceRequest] DataSourceRequest request)
10.{
11.    var entreprises = _entrepriseService.ReadList();
12.    return Json(entreprises.ToDataSourceResult(request));
13.}
14.
15.public ActionResult Data_Source_Products_Submit(SpreadsheetSubmitViewModel model)
16.{
17.    var result = new SpreadsheetSubmitViewModel()
18.    {
19.        Created = new List<EntrepriseDTO>(),
20.        Updated = new List<EntrepriseDTO>(),
21.        Destroyed = new List<EntrepriseDTO>()
22.    };
23.
24.    if ((model.Created != null || model.Updated != null || model.Destroyed != null) && ModelState.IsValid)
25.    {
26.        if (model.Created != null)
27.        {
28.            foreach (var created in model.Created)
29.            {
30.                _entrepriseService.CreateAsync(created);
31.                //productService.Create(created);
32.                result.Created.Add(created);
33.            }
34.        }
35.
36.        if (model.Updated != null)
37.        {
38.            foreach (var updated in model.Updated)
39.            {
40.                _entrepriseService.UpdateAsync(updated);
41.                //productService.Update(updated);
42.                result.Updated.Add(updated);
43.            }
44.        }
45.
46.        if (model.Destroyed != null)
47.        {
48.            foreach (var destroyed in model.Destroyed)
49.            {
50.                _entrepriseService.DeleteAsync(destroyed.Id);
51.                //productService.Destroy(destroyed);
52.                result.Destroyed.Add(destroyed);
53.            }
54.        }
55.
56.        return Json(result);
57.    }
58.    else
59.    {
60.        return StatusCode(400, "The models contain invalid property values.");
61.    }
62.}

 

[serge]

The spreadsheet also receives data in the client, as can be seen in the following image, but does not display anything

[serge]

I discovered the problem: 

your Documentation JavaScript Code supposes that the JSON should start with an uppercase letter. However, my JSON starts with lowercase letters, so the result.Data is expected, but obtained result.data. I don't know why your example works and gives uppercase Json, but in my project it gives me lowercase members....

function onRead(options) {
    $.ajax({
        url: '@Url.Action("Data_Source_Products_Read", "Entreprises")',
        dataType: "json",
        success: function (result) {
            options.success(result.data); // was .Data !!!!!!!!!!!!!!!!!!!
        },
        error: function (result) {
            options.error(result);
        }
    });
}

[Ivan Danchev]

Hi Serge,

I've replied in the support ticket you submitted, but I'll paste the details here as well.

By default the ASP.NET Core serializes JSON in camel case, which actually changes the casing of the fields when they are sent to the client. This can be seen in the response of a request sent to the server. For example a field named "ProductName" will be returned as
"productName" to the client. We have documented this specific of the ASP.NET Core framework in this article: https://docs.telerik.com/aspnet-core/compatibility/json-serialization#configure-json-serialization-in-aspnet-core-3, which shows the necessary configuration that allows to avoid this behavior and persist the casing as it is on the server.

Regards,
Ivan Danchev
Progress Telerik

[serge]

Hello Ivan.

I see that in your linked article the "recommended" approach is to use the default ASP.NET Core serialization. That I believe correct, because all the new projects created by developers will have that default settings. However, is not that I see in the "Kendo.Mvc.Examples" project I downlaoded from Telerik (I don't remember from where exactly, now). That project, containing all the ASP.NET Core MVC sample controls, has the following in the ConfigureServices: 

 

services
    .AddMvc(options => options.EnableEndpointRouting = false).SetCompatibilityVersion(CompatibilityVersion.Version_3_0)
    .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());

 

here in that sample the recommended approach is NOT used. And is confuusing, because that is not the default option when creating a new ASP.NET MVC Core project. Would probably worth mentionning it in the docs for each control demo code, or change the samples to be compatible with the default settings when creating a new project in VS...

(by the way does CompatibilityVersiion.Version_3_0 still actual?)

[Ivan Danchev]

Actually a new ASP.NET Core application does not have its JSON serialization configured in the ConfigureServices method by default. So if the developer wants to configure the serialization, they have a choice between the approaches mentioned in the linked article. The approaches are alternative, so which one to use would be a matter of preference.

Our Telerik UI for ASP.NET Core application template comes with the AddNewtonsoftJson method, but if someone has a preference for the AddJsonOptions method, then it can be used instead. The result would be the same.

With regard to the SetCompatibilityVersion configuration, it is not required. For more details on it, refer to Microsoft's documentation: https://docs.microsoft.com/en-us/aspnet/core/mvc/compatibility-version?view=aspnetcore-5.0