November Happy Hour will be moved to Thursday December 5th.

Get submitted from page name when exporting

Vote:
 

Hi,

When viewing a form submission online the field "submitted from" shows the page name as a link. When the same form data is exported to Excel the field is now an ID which makes it hard for editors to work with.

Is it possible to get the name of the "submitted from" page when exporting to Excel?

#260534
Aug 13, 2021 8:00
Vote:
 

Hi,

You can create a custom data exporter like this:

[ServiceConfiguration(ServiceType = typeof(DataExporterBase))]
public class CustomCsvDataExporter : CSVDataExporter
{
    public override string Export(DataTable dataTable)
    {        
        // modify data
        return base.Export(dataTable);
    }
}

For excel you need to inherit from XLSXDataExporter and override other methods.

Hope this helps!

#260535
Edited, Aug 13, 2021 8:50
Vote:
 

Here's an updated version

[ServiceConfiguration(ServiceType = typeof(DataExporterBase))]
public class CustomExcelDataExporter : XLSXDataExporter
{
    private readonly IContentRepository _contentRepository = ServiceLocator.Current.GetInstance<IContentRepository>();

    protected override void EncodeXmlStringsFromDataTable(DataTable dataTable)
    {
        foreach (DataRow row in dataTable.Rows)
        {
            string language = row["SYSTEMCOLUMN_Language"].ToString();
            int pageId = int.Parse(row["Submitted_from"].ToString());

            if (_contentRepository.TryGet(
                new ContentReference(pageId), 
                new CultureInfo(language),
                out PageData page))
            {
                row["Submitted_from"] = page.Name;
            }
        }

        base.EncodeXmlStringsFromDataTable(dataTable);
    }
}
#260536
Aug 13, 2021 9:18
Vote:
 

Thanks Dejan, this is just what I was looking for

#260538
Aug 13, 2021 12:23
Vote:
 

I tested your code and it works perfectly when the UI language is English, but when I change it the name of the "Submitted_from" column is also changed.

Any idea how I can get the localized column name? 

I also considered doing something like ColumnCount-5 as the index but this is obviously prone to break if a new standard field is added to form submissions.

#260539
Aug 13, 2021 13:18
Vote:
 

Try this:

[ServiceConfiguration(ServiceType = typeof(DataExporterBase))]
public class CustomExcelDataExporter : XLSXDataExporter
{
    private readonly IContentRepository _contentRepository = ServiceLocator.Current.GetInstance<IContentRepository>();

    protected override void EncodeXmlStringsFromDataTable(DataTable dataTable)
    {
        string columnName = LocalizationService.Current
            .GetString("/episerver/forms/formdataview/hostedpage")
            .Replace(" ", "_");

        foreach (DataRow row in dataTable.Rows)
        {
            string language = row["SYSTEMCOLUMN_Language"].ToString();
            int pageId = int.Parse(row[columnName].ToString());

            if (_contentRepository.TryGet(
                new ContentReference(pageId),
                new CultureInfo(language),
                out PageData page))
            {
                row[columnName] = page.Name;
            }
        }

        base.EncodeXmlStringsFromDataTable(dataTable);
    }
}

Hope this helps :)

#260541
Aug 13, 2021 13:51
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.