How would one go about accomplishing the following in MVC: Import data from a database into the spreadsheet control and implement formula cells in certain columns.
I'm not sure the methodology to work in a sort of hybrid method using this control. Is there a way to feed formulas from the controller? Currently I am adding null values to properties of my viewmodel so the columns can show up in the spreadsheet, but these wipe out the formulae I am placing in the column. Here is the code I am using. Not the most elegant :)
@(Html.Kendo().Spreadsheet()
.Name("spreadsheet")
.HtmlAttributes(new { style = "width:100%" })
.Excel(excel => excel
.ProxyURL(Url.Action("Index_Save", "Spreadsheet"))
)
.Pdf(pdf => pdf
.ProxyURL(Url.Action("Index_Save", "Spreadsheet"))
)
.Sheets(sheets =>
{
sheets.Add()
.Name("Products")
.DataSource<BBC.Bids.ViewModels.OrderViewModel>(ds => ds
.Ajax()
.Batch(true)
.Read(r => r.Action("Orders_Read", "Grid").Data("selectedOrder"))
.Update("Products_Update", "Spreadsheet")
.Create("Products_Create", "Spreadsheet")
.Destroy("Products_Destroy", "Spreadsheet")
.Events(e => e.Change("onChange"))
.Model(m =>
{
m.Id(p => p.LineNo);
})
)
.Columns(columns =>
{
columns.Add().Width(25);
columns.Add().Width(200);
columns.Add().Width(100);
columns.Add().Width(25);
columns.Add().Width(50);
columns.Add().Width(50);
columns.Add().Width(50);
columns.Add().Width(50);
columns.Add().Width(50);
columns.Add().Width(50);
columns.Add().Width(50);
columns.Add().Width(50);
})
.Rows(rows =>
{
rows.Add().Height(40).Cells(cells =>
{
for (var i = 1; i < 23; i++)
{
cells.Add()
.Bold(true)
.Background("#9c27b0")
.TextAlign(SpreadsheetTextAlign.Center)
.Color("white");
}
});
for (var i = 2; i < 500; i++)
{
rows.Add().Cells(cells =>
{
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add().Formula("J" + i + "*W" + i); // calculated freight cost
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
cells.Add();
});
}
});
})
)