XlsxFormatProvider Delimiter

[Fredrik]

Hello how do i change the delimiter settings on XlsxFormatProvider?
currently it using ',' and xlsx file stores all the data in the first column due to my regional settings having ';' as the delimiter.

changing regional settings in windows is not an option for me.

please help

[Fredrik]

attatchment third try....

[Anna]

Hi Fredrik,

Do you by any chance mean the CsvFormatProvider? Your attachment makes me think that you are trying to open a csv file.

If you want to change the delimiter used by the CsvFormatProvider, you can use the following code:

CsvFormatProvider csvProvider = WorkbookFormatProvidersManager.GetProviderByName("CsvFormatProvider") as CsvFormatProvider;
csvProvider.Settings.Delimiter = ',';

Please, let me know if you encounter any problems along the way.

Regards,
Anna
Telerik

 

 

[Fredrik]

Hello, no i am talking about XlsxFormatProvider.
I am using code from your demo site (http://demos.telerik.com/silverlight/#GridView/PrintAndExportWithRadDocument)

if (settings.Format == ExportFormat.Excel)
                    {
                        var workbook = CreateWorkBook(grid, settings);

                        if (workbook != null)
                        {
                            var provider = new XlsxFormatProvider();
                            using (var output = dialog.OpenFile())
                            {
                                provider.Export(workbook, output);
                            }
                        }

The problem for me is the generated xlsx file appear like the attachement i uploaded both in my application and in your demo application. As i said am not sure the problem is the delimiter.

[quote]Anna said:Hi Fredrik,

Do you by any chance mean the CsvFormatProvider? Your attachment makes me think that you are trying to open a csv file.

If you want to change the delimiter used by the CsvFormatProvider, you can use the following code:

CsvFormatProvider csvProvider = WorkbookFormatProvidersManager.GetProviderByName("CsvFormatProvider") as CsvFormatProvider;
csvProvider.Settings.Delimiter = ',';

Please, let me know if you encounter any problems along the way.

Regards,
Anna
Telerik

 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 

[/quote]

[Anna]

Hello,

Thank you for the clarification. The problem is indeed in the delimiter, as you supposed. In order to construct the model of the workbook from the information from the GridView, the code uses the CsvFormatProvider. This happens in the CreateWorkbook method and it looks like this:

private static Workbook CreateWorkBook(RadGridView grid, PrintSettings settings)
{
    Workbook book = null;

    using (var stream = new MemoryStream())
    {
        grid.Export(stream, new GridViewExportOptions()
        {
            Format = Controls.ExportFormat.Csv,
            ShowColumnFooters = grid.ShowColumnFooters,
            ShowColumnHeaders = grid.ShowColumnHeaders,
            ShowGroupFooters = grid.ShowGroupFooters
        });

        stream.Position = 0;

        book = new CsvFormatProvider().Import(stream);
    }

    return book;
}

If you change this code as we discussed below, the workbook should be constructed correctly.

CsvFormatProvider provider = new CsvFormatProvider();
provider.Settings.Delimiter = ',';
book = provider.Import(stream);

Thank you very much for pointing out the issue in the demo.

Regards,
Anna
Telerik

 

 

[Anna]

Hi again,

I would just like to make an addition to my previous reply. Another option to solve this would be to adjust the settings of the csv export of the GridView to use the list separator of the current culture.

grid.Export(stream, new GridViewCsvExportOptions()
{
    Format = Controls.ExportFormat.Csv,
    ShowColumnFooters = grid.ShowColumnFooters,
    ShowColumnHeaders = grid.ShowColumnHeaders,
    ShowGroupFooters = grid.ShowGroupFooters,
    UseSystemCultureSeparator = true
});

In this case you won't have to set the delimiter of the CsvFormatProvider.

Regards,
Anna
Telerik

 

 

[Fredrik]

Hello, not really sure why you draw up examples using CsvFormatProvider i dont want to export a .csv formated file.
but infact a .xlsx Excel Microsoft Office Open XML Format Spreadsheet file.

According to your documentation this possible through XlsxFormatProvider, however both in my private application and in your demo application this exported .xlsx file appear with all the data in the first column.

[Anna]

Hi,

The CsvFormatProvider is only used as a link between the GridView and the Workbook model. The information is exported in csv format from the GridView and then imported into a workbook using the CsvFormatProvider. Only after that is the created workbook exported to an xlsx file, using the XlsxFormatProvider. The problem you encountered occurs during the creation of the workbook, not during the export to xlsx.

If I understood you right, in your application you've used the same code as in the demo. If you modify the CreateWorkbook method in one of the ways I've described above, the workbook will be created correctly and then the resulting xlsx file should also be correct.

Please, let me know if you are experiencing further issues.

Regards,
Anna
Telerik

 

 

[Fredrik]

my bad, didnt realize that the workbook creation relied on the csvformatprovider.
changing code:

grid.Export(stream, new GridViewCsvExportOptions()

{

    Format = Controls.ExportFormat.Csv,

    ShowColumnFooters = grid.ShowColumnFooters,

    ShowColumnHeaders = grid.ShowColumnHeaders,

    ShowGroupFooters = grid.ShowGroupFooters,

    UseSystemCultureSeparator = true

});

solved the problem, thanks :)


[Fredrik]

Hello my bad, didnt realize that the workbook relied on the csvformat provider
setting  UseSystemCultureSeparator  under  GridViewCsvExportOptions solved the problem
thanks :)