Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Alejandro Ocampo 67 posts 320 karma points c-trib
    Aug 02, 2021 @ 05:13
    Alejandro Ocampo
    0

    Export forms data to xlsx using the forms API

    Hi,

    Can anyone give me some pointer to how I can export the form records to Excel using the API ?

    The requirement is to create the excel file and send it through email, this task needs to be in an scheduler.

    I have the scheduler up and running ✅

    This is the code that I'm playing with to generate the excel file:

     public string ExportFormData()
            {
                //var excelProvider = Umbraco.Forms.Core.Providers.ExportTypeProviderCollection.Instance.GetProviderInstance(Guid.Parse(Umbraco.Core.Constants.ExportTypes.Excel));
                //excelProvider.ExportToFile(recordFilter, "someFilePath");
    
                var formLocation1 = HostingEnvironment.MapPath("~/App_Data/");
                var formLocation2 = HostingEnvironment.MapPath("~/App_Data/FormsExport.xlsx");
                var formLocation3 = HostingEnvironment.MapPath("~/App_Data/FormsExportTT");
                //excelProvider.ExportToFile(formFilter, formLocation);
    
                var forms = _formStorage.GetAll();
                var umbracoFormsExportProvider = new Umbraco.Forms.Core.Providers.Export.SaveAllUploadedFiles();
                var excelProvider = new Umbraco.Forms.Core.Providers.Export.ExportToExcel(_formRecordSearcher);
    
                //var hhh = new Umbraco.Forms.Core.Providers.Export.ExportToExcel();
                //ggg.ExportToFile()
    
                var recordExportFilter = new RecordExportFilter
                {
                    StartDate = DateTime.Today.AddDays(-30),
                    EndDate = DateTime.Today,
                    ExportType = "ExportToExcel" //testing more things
                };
    
                foreach (var form in forms)
                {
                    //test with simple form
                    if (!form.Id.ToString().Equals("999db7fa-a3f8-4c51-b05a-2c96ff343b62"))
                    {
                        continue;
                    }
    
                    //umbracoFormsExportProvider.ExportToFile(recordExportFilter, formLocation1);
                    //umbracoFormsExportProvider.ExportToFile(recordExportFilter, formLocation1);
                    //var ggg = umbracoFormsExportProvider.ExportRecords(recordExportFilter);
                    excelProvider.ExportToFile(recordExportFilter, formLocation3);
    
                    recordExportFilter.Form = form.Id;
                    var formRecords = _recordService.GetAllRecords(form).Where(x => x.GetRecordFieldByAlias("exported").HasValue());
    
                    foreach (var formRecord in formRecords)
                    {
                        var exportedHiddenField = formRecord.GetRecordFieldByAlias("exported");
                        if (exportedHiddenField.Values.Contains("True")) { continue; }
    
                        //saved as exported record
                        exportedHiddenField.Values = new List<object> { true };
                        _recordService.Submit(formRecord, form);
                    }
                }
    
                return string.Empty;
            }
    

    And this is the error I'm seeing:

    enter image description here

    I think I'm in the right path but I can't find any documentation or similar cases I can use to move on so I'm blocked here.

    Any ideas or maybe a different approach?

    Thanks, Ale

Please Sign in or register to post replies

Write your reply to:

Draft