Save data flexsheet in database

Posted by: joseph-guaresesiacorp-com-br on 9 September 2017, 8:18 am EST

  • Posted 9 September 2017, 8:18 am EST

    Hello good afternoon. I have a problem to save data from a flexsheet in my database. I can not carry the data from my view to my controller. Someone has gone through something? Already tried in many ways and always the controller receives null

    public ActionResult SaveSheet(Object requestData)
    {

    }



    <script type="text/javascript">
    $('#SaveSheet').click(function () {
    debugger
    //var batchEditGrid = wijmo.Control.getControl('#Ativo'),
    // cv = batchEditGrid.collectionView;
    var batchEditGrid = wijmo.Control.getControl('#Ativo'),
    cv = batchEditGrid.collectionView;

    var requestData = cv._view;

    //var data = {};
    //data.requestData = JSON.stringify(requestData);
    //var parameters = { "requestData": requestData };
    $.ajax(
    {
    type: "POST",
    url: "pna/Balancos/SaveSheet",
    dataType: 'json',
    contentType: 'application/json; charset=utf-8',
    data: { requestData: requestData },
    success: function () {
    alert(data);
    }
    }).done(function (response) {
    //Redirect
    console.log(response);
    })
    .fail(function (response) {
    console.log(response);
    })
    .always(function (response) {
    console.log(response);
    });



    });
    </script>



    @(Html.C1().FlexSheet().Id("Ativo").AutoGenerateColumns(false).AllowSorting(false)
    .AddBoundSheet(sheet => sheet.Bind(ib => ib.DisableServerRead(true).Bind(Model.Where(x => x.CodGrupoConta == 1))
    .BatchEdit(Url.Action("SaveSheet")))).Height(530)
    .IsTabHolderVisible(false).SelectionMode(C1.Web.Mvc.Grid.SelectionMode.Cell)
    .Columns(bl =>
    {
    bl.Add(cb => cb.Binding("NomConta").Header("Ativo").Width("230").IsReadOnly(true).Name("Ativo"));
    if (NroDemonstrativos >= 1)
    {
    bl.Add(cb => cb.Binding("ValConDemonstrativo1").Width("*").Header(Model.First().NroMes1.ToString() + "/" + Model.First().NroAno1.ToString() + " (" + Model.First().NumMeses1.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(false));
    }
    if (NroDemonstrativos >= 2)
    {
    bl.Add(cb => cb.Binding("ValConDemonstrativo2").Width("*").Header(Model.First().NroMes2.ToString() + "/" + Model.First().NroAno2.ToString() + " (" + Model.First().NumMeses2.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(false));
    bl.Add(cb => cb.Width("50").Header("% h").Align("center").Format("n1").IsReadOnly(false));
    }
    if (NroDemonstrativos >= 3)
    {
    bl.Add(cb => cb.Binding("ValConDemonstrativo3").Width("*").Header(Model.First().NroMes3.ToString() + "/" + Model.First().NroAno3.ToString() + " (" + Model.First().NumMeses3.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(false));
    bl.Add(cb => cb.Width("50").Header("% h").Align("center").Format("n1").IsReadOnly(false));
    }
    if (NroDemonstrativos >= 4)
    {
    bl.Add(cb => cb.Binding("ValConDemonstrativo4").Width("*").Header(Model.First().NroMes4.ToString() + "/" + Model.First().NroAno4.ToString() + " (" + Model.First().NumMeses4.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(true));
    bl.Add(cb => cb.Width("50").Header("% h").Align("center").Format("n1").IsReadOnly(true));
    }
    }))
  • Replied 9 September 2017, 8:18 am EST

    Hi Joseph,
    The BatchEdit for FlexSheet works similar to FlexGrid, you do not have make custom javascript ajax call to save data.
    The action url that is set in FlexSheet for batchedit should handle it automatically. You just need to call the commit method of flexsheet client collectionView in javascript when updates are ready to be submitted.

    Here is the FlexGrid example that shows how to save to database through batch editing: http://demos.componentone.com/ASPNET/MVCExplorer/FlexGrid/BatchEditing

    Here is a simple example of FlexSheet BatchEditing that works with a collection.

    Model:

    public class Sale
    {
    public int ID { get; set; }
    public DateTime Date { get; set; }
    public string Country { get; set; }
    public string Product { get; set; }
    public double Amount { get; set; }
    public bool Active { get; set; }

    private static List<string> COUNTRIES = new List<string> { "US", "Germany", "UK", "Japan", "Italy", "Greece" };
    private static List<string> PRODUCTS = new List<string> { "Widget", "Gadget", "Doohickey" };

    /// <summary>
    /// Get the data.
    /// </summary>
    /// <param name="total"></param>
    /// <returns></returns>
    public static IEnumerable<Sale> GetData(int total)
    {
    var rand = new Random(0);
    var list = Enumerable.Range(0, total).Select(i =>
    {
    var country = COUNTRIES[rand.Next(0, COUNTRIES.Count - 1)];
    var product = PRODUCTS[rand.Next(0, PRODUCTS.Count - 1)];
    var date = new DateTime(2014, i % 12 + 1, 25);

    return new Sale
    {
    ID = i + 1,
    Date = date,
    Country = country,
    Product = product,
    Amount = Math.Round(rand.NextDouble() * 10000 - 5000, 2),
    Active = (i % 4 == 0)
    };
    });
    return list;
    }

    public static List<string> GetCountries()
    {
    var countries = new List<string>();
    countries.AddRange(COUNTRIES);
    return countries;
    }

    public static List<string> GetProducts()
    {
    List<string> products = new List<string>();
    products.AddRange(PRODUCTS);
    return products;
    }
    }


    Controller:

    public class HomeController : Controller
    {
    public static List<Sale> SALES = CustomerSale.GetData(50).ToList();
    public ActionResult Index()
    {
    return View(SALES);

    }

    public ActionResult SalesUpdate([C1JsonRequest]CollectionViewBatchEditRequest<Sale> requestData)
    {
    return this.C1Json(CollectionViewHelper.BatchEdit<Sale>(requestData, batchData =>
    {
    string error = string.Empty;
    bool success = true;
    var results = new List<CollectionViewItemResult<Sale>>();

    batchData.ItemsUpdated.ToList().ForEach(sale =>
    {
    var fSale = SALES.Find(item => item.ID == sale.ID);
    fSale.Active = sale.Active;
    fSale.Amount = sale.Amount;
    fSale.Country = sale.Country;
    fSale.Product = sale.Product;

    results.Add(new CollectionViewItemResult<Sale>
    {
    Data = sale
    });
    });

    return new CollectionViewResponse<Sale>
    {
    Error = error,
    Success = success && ModelState.IsValid,
    OperatedItemResults = results
    };
    }, () => SALES));
    }

    }


    View:


    @using FlexSheetBatchUpdate.Models
    @model List<Sale>

    <script>
    function Save() {
    var grid = wijmo.Control.getControl("#fs");
    var cv = grid.collectionView;
    cv.commit();
    }

    </script>



    @(Html.C1().FlexSheet().Id("fs")
    .AddBoundSheet(
    Sale=>Sale
    .Bind(ib=>ib.Bind(Model).
    DisableServerRead(true).
    BatchEdit(Url.Action("SalesUpdate"))))
    .Height("520px"))
    <input type="button" onclick="Save()" value="Save"/>


    Thanks
  • Replied 9 September 2017, 8:18 am EST

    Thanks for helping.
    I discovered that there was a field that was invalidating my entity so I was getting null. I have some fields that are calculated in my set spreadsheet using formulas in javascript. And my model this field is as decimal only to include the javascript by setting the formula ended up being invalidated. When changing this field for the string controller ends up getting the class but generates error in the calculations. Do you have any idea how to solve.
    Thank you.
  • Replied 9 September 2017, 8:18 am EST

    Joseph,

    It is hard to comment without seeing a sample, can you attach a small sample replicating the issue.

    Thanks.
  • Replied 9 September 2017, 8:18 am EST

    Hi Joseph,
    The calculated values are not present in the FlexSheet's collectionView hence the problem. I suppose you could save the calculated values in a variable inside the "OnClientQueryData" method of the bound sheet, this saved variable can then be accessed in the BatchEdit action in the controller using the "requestData.ExtraRequestData" parameter.

    A simple example of using the "OnClientQueryData" method is given the Master Detail Example of FlexGrid
    http://helpcentral.componentone.com/nethelp/c1mvchelpers/MasterDetail.html

    Thanks
  • Replied 9 September 2017, 8:18 am EST


    I could by example. Continued sending the formula for my controller. What he wanted was something very basic. Only pick up the value of the custom function. When I call my batch function the value will the formula that I configured in the cell and not the value.

    When to set the formula = sum (a1 + b1) must send the total of this formula to the controller.



    @(Html.C1().FlexSheet().Id("Ativo").AutoGenerateColumns(false).AllowSorting(false)
    .AddBoundSheet(sheet => sheet.Bind(ib => ib.Bind(Model.Where(x => x.CodGrupoConta == 1))
    .DisableServerRead(true).BatchEdit(@Url.Action("GridBatchEdit"))))
    .Height(525)
    .IsTabHolderVisible(false).SelectionMode(C1.Web.Mvc.Grid.SelectionMode.Cell)
    .Columns(bl =>
    {
    bl.Add(cb => cb.Binding("NomConta").Header("Ativo").Width("200").IsReadOnly(true));

    bl.Add(cb => cb.Binding("ValConDemonstrativo1").Width("*").Header(Model.First().NroMes1.ToString() + "/" + Model.First().NroAno1.ToString() + " (" + Model.First().NumMeses1.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(true));

    bl.Add(cb => cb.Binding("ValConDemonstrativo2").Width("*").Header(Model.First().NroMes2.ToString() + "/" + Model.First().NroAno2.ToString() + " (" + Model.First().NumMeses2.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(true));
    bl.Add(cb => cb.Width("50").Header("% h").Align("center").Format("n1").IsReadOnly(true));

    bl.Add(cb => cb.Binding("ValConDemonstrativo3").Width("*").Header(Model.First().NroMes3.ToString() + "/" + Model.First().NroAno3.ToString() + " (" + Model.First().NumMeses3.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(true));
    bl.Add(cb => cb.Width("50").Header("% h").Align("center").Format("n1").IsReadOnly(true));

    bl.Add(cb => cb.Binding("ValConDemonstrativo4").Width("*").Header(Model.First().NroMes4.ToString() + "/" + Model.First().NroAno4.ToString() + " (" + Model.First().NumMeses4.ToString() + ")").Format("n0"));
    bl.Add(cb => cb.Width("50").Header("% v").Align("center").Format("n1").IsReadOnly(true));
    bl.Add(cb => cb.Width("50").Header("% h").Align("center").Format("n1").IsReadOnly(true));

    }))



    flexSheet.addCustomFunction('Horizontal', function (range1, range2) {
    var result = 0, val1, val2;
    if (range1.rowSpan === range2.rowSpan && range1.columnSpan === range2.columnSpan) {
    for (var rowIndex = 0; rowIndex < range1.rowSpan; rowIndex++) {
    for (var columnIndex = 0; columnIndex < range1.columnSpan; columnIndex++) {
    val1 = +flexSheet.getCellValue(range1.topRow + rowIndex, range1.leftCol + columnIndex, false);
    val2 = +flexSheet.getCellValue(range2.topRow + rowIndex, range2.leftCol + columnIndex, false);
    if (val1 > 0)
    result += (parseFloat((val2 / val1 - 1) * 100));

    }
    }
    }
    return result;
    }, 'Horizontal', true, 2, 2);

    for (var i = 1; i <= 19; i++) {
    if (Count >= 2) {
    flexSheet.setCellData(i, 5, '=Horizontal(B' + (parseInt(i) + 1).toString() + ',D' + (parseInt(i) + 1).toString() + ')');
    //verificar quantos balanços ele possui selecionado para executar
    flexSheet.setCellData(i, 4, '=Vertical(D' + (parseInt(i) + 1).toString() + ',D20)');
    }
    if (Count >= 3) {
    flexSheet.setCellData(i, 8, '=Horizontal(D' + (parseInt(i) + 1).toString() + ',G' + (parseInt(i) + 1).toString() + ')');
    flexSheet.setCellData(i, 7, '=Vertical(G' + (parseInt(i) + 1).toString() + ',G20)');
    }
    if (Count >= 4) {
    flexSheet.setCellData(i, 11, '=Horizontal(G' + (parseInt(i) + 1).toString() + ',J' + (parseInt(i) + 1).toString() + ')');
    flexSheet.setCellData(i, 10, '=Vertical(J' + (parseInt(i) + 1).toString() + ',J20)');
    }

    flexSheet.setCellData(i, 2, '=Vertical(B' + (parseInt(i) + 1).toString() + ',B20)');

    }
  • Replied 9 September 2017, 8:18 am EST


    I could by example. Continued sending the formula for my controller. What he wanted was something very basic. Only pick up the value of the custom function. When I call my batch function the value will the formula that I configured in the cell and not the value.

    When to set the formula = sum (a1 + b1) must send the total of this formula to the controller.





    flexSheet.addCustomFunction('Horizontal', function (range1, range2) {
    var result = 0, val1, val2;
    if (range1.rowSpan === range2.rowSpan && range1.columnSpan === range2.columnSpan) {
    for (var rowIndex = 0; rowIndex < range1.rowSpan; rowIndex++) {
    for (var columnIndex = 0; columnIndex < range1.columnSpan; columnIndex++) {
    val1 = +flexSheet.getCellValue(range1.topRow + rowIndex, range1.leftCol + columnIndex, false);
    val2 = +flexSheet.getCellValue(range2.topRow + rowIndex, range2.leftCol + columnIndex, false);
    if (val1 > 0)
    result += (parseFloat((val2 / val1 - 1) * 100));

    }
    }
    }
    return result;
    }, 'Horizontal', true, 2, 2);

    for (var i = 1; i <= 19; i++) {
    if (Count >= 2) {
    flexSheet.setCellData(i, 5, '=Horizontal(B' + (parseInt(i) + 1).toString() + ',D' + (parseInt(i) + 1).toString() + ')');
    //verificar quantos balanços ele possui selecionado para executar
    flexSheet.setCellData(i, 4, '=Vertical(D' + (parseInt(i) + 1).toString() + ',D20)');
    }
    if (Count >= 3) {
    flexSheet.setCellData(i, 8, '=Horizontal(D' + (parseInt(i) + 1).toString() + ',G' + (parseInt(i) + 1).toString() + ')');
    flexSheet.setCellData(i, 7, '=Vertical(G' + (parseInt(i) + 1).toString() + ',G20)');
    }
    if (Count >= 4) {
    flexSheet.setCellData(i, 11, '=Horizontal(G' + (parseInt(i) + 1).toString() + ',J' + (parseInt(i) + 1).toString() + ')');
    flexSheet.setCellData(i, 10, '=Vertical(J' + (parseInt(i) + 1).toString() + ',J20)');
    }

    flexSheet.setCellData(i, 2, '=Vertical(B' + (parseInt(i) + 1).toString() + ',B20)');

    }
  • Marked as Answer

    Replied 9 September 2017, 8:18 am EST

    Answered in the thread:
    http://our.componentone.com/groups/topic/saving-function-values-custom/#post-679300
  • Replied 9 September 2017, 8:18 am EST

    Thank you!
  • Replied 9 September 2017, 8:18 am EST

    Prabhakar Mishra,
    Thank you. I ended up solving the previous scenario.
    My biggest problem now is how to save the calculated value. I'm using some type formulas a1 + a2. But when it does not save command to save the sum a1 + a2. You are saving formula in the bank. It has gone through something like this before.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels