This is a migrated thread and some comments may be shown as answers.

Custom spreadsheed control

9 Answers 169 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
serge
Top achievements
Rank 2
Bronze
Iron
Iron
serge asked on 09 Mar 2021, 03:48 PM

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

9 Answers, 1 is accepted

Sort by
0
serge
Top achievements
Rank 2
Bronze
Iron
Iron
answered on 09 Mar 2021, 03:49 PM
the picture is here
0
Accepted
Ivan Danchev
Telerik team
answered on 12 Mar 2021, 11:48 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
serge
Top achievements
Rank 2
Bronze
Iron
Iron
answered on 12 Mar 2021, 10:52 PM

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!

0
Ivan Danchev
Telerik team
answered on 17 Mar 2021, 03:48 PM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
serge
Top achievements
Rank 2
Bronze
Iron
Iron
answered on 22 Apr 2021, 03:08 PM

".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">
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.}

 

0
serge
Top achievements
Rank 2
Bronze
Iron
Iron
answered on 22 Apr 2021, 03:53 PM
The spreadsheet also receives data in the client, as can be seen in the following image, but does not display anything
0
serge
Top achievements
Rank 2
Bronze
Iron
Iron
answered on 22 Apr 2021, 04:02 PM

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);
        }
    });
}
0
Ivan Danchev
Telerik team
answered on 23 Apr 2021, 03:18 PM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
serge
Top achievements
Rank 2
Bronze
Iron
Iron
answered on 26 Apr 2021, 07:14 AM

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
Telerik team
commented on 28 Apr 2021, 02:44 PM

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

 

Tags
Spreadsheet
Asked by
serge
Top achievements
Rank 2
Bronze
Iron
Iron
Answers by
serge
Top achievements
Rank 2
Bronze
Iron
Iron
Ivan Danchev
Telerik team
Share this question
or