XlsxFormatProvider Delimiter

12 posts, 1 answers
  1. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 27 Dec 2014 Link to this post

    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
  2. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 29 Dec 2014 Link to this post

    If use your demo site
    http://demos.telerik.com/silverlight/#GridView/PrintAndExportWithRadDocument
    and generate excel export, i also get everything in the first column. see the attached screen of the excel file.

    Tot really sure if its the delimiter or something else? the file is not a proper xlsx file for sure
  3. DevCraft banner
  4. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 29 Dec 2014 Link to this post

    attachement
  5. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 29 Dec 2014 Link to this post

    attatchment third try....
  6. Anna
    Admin
    Anna avatar
    99 posts

    Posted 30 Dec 2014 Link to this post

    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.

     
  7. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 03 Jan 2015 in reply to Anna Link to this post

    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.

    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.

     

  8. Anna
    Admin
    Anna avatar
    99 posts

    Posted 06 Jan 2015 Link to this post

    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
     

    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.

     
  9. Answer
    Anna
    Admin
    Anna avatar
    99 posts

    Posted 06 Jan 2015 Link to this post

    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
     

    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.

     
  10. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 06 Jan 2015 Link to this post

    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.

  11. Anna
    Admin
    Anna avatar
    99 posts

    Posted 07 Jan 2015 Link to this post

    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
     

    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.

     
  12. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 07 Jan 2015 in reply to Anna Link to this post

    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 :)
  13. Fredrik
    Fredrik avatar
    58 posts
    Member since:
    Mar 2013

    Posted 07 Jan 2015 in reply to Anna Link to this post

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

     

     

     

     

Back to Top
DevCraft banner