This is a migrated thread and some comments may be shown as answers.

Excel format issus

27 Answers 640 Views

GridView

This is a migrated thread and some comments may be shown as answers.

Fu Wen asked on 14 Jul 2009, 09:11 PM

Hi There

When I try to export the data inside gridview table to an excel file with "xls" file extension a warning message is shown like "different format than specified by the file extension"(Office 2007 is installed in the PC). However, when I change the file extension to xlsx it does not work at all.

Can anyone help me to figure out the problem?

Many thanks

Bo

Bellow is the code

Many thanksBoBellow is the code

string content = string.Empty;
            string extension = "xls";
            content = radGridView1.ToHtml(true);
            string path = String.Format("Export.{0}", extension);

            if (File.Exists(path))
            {
                File.Delete(path);
            }

            using (FileStream fs = File.Create(path))
            {
                Byte[] info = Encoding.Default.GetBytes(content);
                fs.Write(info, 0, info.Length);
            }

Process.Start(path);

27 Answers, 1 is accepted

answered on 26 Jul 2009, 07:15 PM

Stefan,
We (asnwering instead of Asmund here...) need xls-Files which the user can double-click. Importing files in excel is not really an option here. And for that to save them as html, name them "xls" and tell the user to "ok" the message is currently the only option to create Excel-Sheets from the GridView (if Excel 2007 is unavaliable, that is...)

Summarizing:
ToHTML - only real option when trying to create "xls"-Files. Excel-Extension-Warning is problematic
ToCSV - complies to RFC4180 (mostly, haven't checkt all) - but only useful for Excel when opened from Excel via File|Open.
ToExcelML - only for Excel2007

To me it seems that a real ToExcel(2003) is "missing".

Yours,
Nils

answered on 28 Jul 2009, 08:31 PM

I think i ought to clarify a little of what I wrote previously...
I had a mistaken ExcelML for OOXML (see http://en.wikipedia.org/wiki/Microsoft_Office_2003_XML_formats for details).
That means
a) My previous summary was wrong: ToExcelML is valid for Excel 2002,2003 and 2007, and
b) answers Fu Wen's Problem: "when I change the file extension to xlsx it does not work at all" -> Right. The correct extension for Microsoft Office XML Type Excel is ".xml" (surprise..)

Only one Problem remains on one of our developer-machines:
The xml-PI "mso-application" application is ignored and the xml-File is opened using Safari-Browser (bummer..) -- soo if someone has a hint on this can be "savely removed" (and no by removing Safari...)

Yours.
Nils

answered on 18 Nov 2009, 06:53 PM

I know this is late to the game in this thread, however I didn't see any answer to resolve the original issue. I had the same problem and was able to get around it easily.

Basically you give the ToExcelML exported file the extension of .XML and then when you start the process you have to tell it to start excel.exe with the file as the parameter. This will launch excel with this file, it will recognize it as ExcelML and open it without issue.

Here is the code snippet I use to export all the supported types from a WPF RadGridView. This not only deals with the ExcelML issue in this thread, but also with a know default browser issue with Firefox if you want to launch HTML in a browser. I hope this is helpful.

                    private string GetExportString(ExportTypes exportType)         
            {         
                string exportString = null;         
                     
                switch (exportType)         
                {         
                    case ExportTypes.Excel:         
                        exportString = searchResultsDataGrid.ToExcelML(true);         
                        break;         
                    case ExportTypes.Html:         
                        exportString = searchResultsDataGrid.ToHtml(true);         
                        break;         
                    case ExportTypes.CSV:         
                        exportString = searchResultsDataGrid.ToCsv(true);         
                        break;         
                    case ExportTypes.Text:         
                        exportString = searchResultsDataGrid.ToText(true);         
                        break;         
                }         
                     
                return exportString;         
            }         
                     
            private void ExportString(ExportTypes exportType, string exportString)         
            {         
                string tempFileName = System.IO.Path.GetTempFileName();         
                     
                switch (exportType)         
                {         
                    case ExportTypes.Excel:         
                        tempFileName = tempFileName.Replace(".tmp", ".xml");         
                        break;         
                    case ExportTypes.Html:         
                        tempFileName = tempFileName.Replace(".tmp", ".html");         
                        break;         
                    case ExportTypes.CSV:         
                        tempFileName = tempFileName.Replace(".tmp", ".csv");         
                        break;         
                    case ExportTypes.Text:         
                        tempFileName = tempFileName.Replace(".tmp", ".txt");         
                        break;         
                }         
                     
                File.WriteAllText(tempFileName, exportString);         
                     
                switch (exportType)         
                {         
                    case ExportTypes.Excel:         
                        try         
                        {         
                            Process.Start("excel.exe", tempFileName);         
                        }         
                        catch         
                        {         
                            ShowErrorMessage(GetResourceString("exportErrorTitleText"),          
                                             GetResourceString("cannotOpenExcelErrorText"));         
                        }         
                        break;         
                    case ExportTypes.Html:         
                        LaunchBrowser(tempFileName);         
                        break;         
                    default:         
                        Process.Start(tempFileName);         
                        break;         
                }         
            }         
                     
            private void LaunchBrowser(string urlString)         
            {         
                try         
                {         
                    System.Diagnostics.Process.Start(urlString);         
                }         
                /*        
                 * Win32Exception is occurs when Firefox is the default browser, and it has not been run yet.        
                 * This is safe to ignore as the browser still opens.        
                 */         
                catch (System.ComponentModel.Win32Exception) { /* Ignore */ }         
                /*        
                 * If any other exception occurs we need to try to specifically call the built in IE.        
                 */         
                catch         
                {         
                    try         
                    {         
                        ProcessStartInfo startInfo = new ProcessStartInfo("IExplore.exe", urlString);         
                        Process.Start(startInfo);         
                    }         
                    /*        
                     * Failed final attempted, need to provide back an error message.        
                     */         
                    catch (Exception ex)         
                    {         
                        StringBuilder builder = new StringBuilder();         
                        builder.AppendLine(GetResourceString("cannotLaunchBrowserErrorText"));         
                        builder.AppendLine(GetResourceString("errorLabelText"));         
                        builder.AppendLine(ex.Message);         
                     
                        ShowErrorMessage(GetResourceString("exportErrorTitleText"), builder.ToString());         
                    }         
                }         
            }         

answered on 19 Nov 2009, 07:55 AM

Rodney,
It's never too late to join a good dicussion ;-)
Actually the answer was there. Thouhg I must confess -- after re-reading my post from Jul 28 -- it's not that obvious.

one thing i got to remark to your post:
"normally" you dont have to call excel.exe to open the xml-file. It will be opened correctly, if the xml-pi is correctly interpreted.
The line in the xml-file:

    
            <?mso-application progid="Excel.Sheet"?>

Tells your Windows what Icon to display and what app to use..
If that is working, that is. We had one machine on which - after installing Safari browser - every xml-file was opened by safari...

Yours,
Nils

answered on 05 Feb 2010, 09:41 PM

.ToExcelXML seems to create invalid ExcelXML.

Using RadControls_for_WPF_2009_3_1314_TRIAL
Running on Win7 Englisch with regional settings "German".
The problem could be related to exporting a wrong decimal separator.

    
                    Dim fileName = IO.Path.ChangeExtension(IO.Path.GetTempFileName, "xml")  
        
                    If IO.File.Exists(fileName) Then IO.File.Delete(fileName)  
        
                    Using fs = IO.File.Create(fileName)  
        
                        Dim bytes = Text.Encoding.Default.GetBytes(RadGridView1.ToExcelML)  
        
                        fs.Write(bytes, 0, bytes.Length)  
        
                    End Using  
        
                    Process.Start("Excel.exe", fileName)

Excel creates a log file with the following entries:
...
FILE: C:\Users\pm\AppData\Local\Temp\tmpA9C4.xml
GROUP: Table
TAG: Column
ATTRIB: Width
VALUE: 166,086666666667

XML Spreadsheet Warning in Table
REASON: Value too large
ACTION: Limiting value
FILE: C:\Users\pm\AppData\Local\Temp\tmpA9C4.xml
GROUP: Table
TAG: Column
ATTRIB: Width
VALUE: 154,743333333333XML PARSE ERROR: Missing end-tag
Error occurs at or below this element stack:
    <ss:Workbook>
     <ss:Worksheet>
      <ss:Table>
       <ss:Row>
        <ss:Cell>
         <ss:Data>

answered on 03 May 2010, 09:09 PM

Vlad,

whatever you did in February seems to have been only partial, as my experiments today showed. I am using 2010.1 build 422 on a German Win7 (64bit) with German Excel 2007 and English VS2010.

My RadGridView displayed some query results (marvellous, how easy this is and how fast it becomes once you add a single line for virtualization), one of them the numeric value "4.5". Running in a German environment this was displayed correctly as "4,5". However, exporting this grid to both CSV and ExcelML also pushed "4,5" out, which is wrong for ExcelML, as a "," in a number field seems to be treated as "thousands separator" during import (German Excel 2007 on German Win7) and the cell contains the number "45".

I came up with one quick, wrong, and extremely ugly fix, but this at least produces "correct" ExcelML, where the grid number "4,5" ends up as the excel cell with value "4.5" (displayed as "4,5" of course).

    
                        Dim cc = System.Threading.Thread.CurrentThread.CurrentCulture 
        
                        System.Threading.Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US") 
        
                        IO.File.WriteAllText("export.csv", MyGrid.ToCsv, System.Text.Encoding.Default) 
        
                        IO.File.WriteAllText("export.xml", MyGrid.ToExcelML, System.Text.Encoding.UTF8) 
        
                        System.Threading.Thread.CurrentThread.CurrentCulture = cc

So my guess is that you have to ignore the current culture during formatting the cell contents for ExcelML.

I also tried exporting to CSV or Text (==Tab-separated values), but this got too messy too soon and there basically is no way to export a csv, which is "correct by definition".

BTW: I'm also submitting a support ticket pointing to this post, as I'd like to have this issue cleared up quickly.

Thanks,
s.

answered on 04 May 2010, 09:04 AM

Hi Vlad,

thanks for the quick response. I looked at the demo (which has some display problems in Firefox) and have some remarks on it:

    
                    Private Sub Button_Click(sender As Object, e As System.Windows.RoutedEventArgs) 
        
                        Dim extension As String = "" 
        
                        Dim format As ExportFormat = ExportFormat.Html 
        
                        Dim comboItem As RadComboBoxItem = TryCast(ComboBox1.SelectedItem, RadComboBoxItem) 
        
                        Dim selectedItem As String = comboItem.Content.ToString() 
        
                        If selectedItem = "Excel" Then 
        
                            extension = "xls" 
        
                            format = ExportFormat.Html 
        
                        ElseIf selectedItem = "ExcelML" Then 
        
                            extension = "xml" 
        
                            format = ExportFormat.ExcelML 
        
                        ElseIf selectedItem = "Word" Then 
        
                            extension = "doc" 
        
                            format = ExportFormat.Html 
        
                        ElseIf selectedItem = "Csv" Then 
        
                            extension = "csv" 
        
                            format = ExportFormat.Csv 
        
                        End If 
        
                        Dim dialog As New SaveFileDialog() 
        
                        If dialog.ShowDialog() = True Then 
        
                            Using stream As Stream = dialog.OpenFile() 
        
                                RadGridView1.Export(stream, New GridViewExportOptions()) 
        
                            End Using 
        
                        End If 
        
                    End Sub

This event handler does not set the culture, it only assigns the basic export format to the variable format, which is then not used in the export method call.

My current code using the Export method looks like this:

    
                Private Sub MyButton2_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) 
        
                    If MyGrid.Columns.Count > 0 Then 
        
                        IO.File.WriteAllText("export.csv", MyGrid.ToCsv, System.Text.Encoding.Default) 
        
                        Using fs = New IO.FileStream("export.xml", IO.FileMode.OpenOrCreate) 
        
                            Dim options = New Telerik.Windows.Controls.GridViewExportOptions 
        
                            options.Format = ExportFormat.ExcelML 
        
                            options.Encoding = System.Text.Encoding.UTF8 
        
                            MyGrid.Export(fs, options) 
        
                        End Using 
        
                    End If

Intellisense for options only shows Encoding, Format, Items, ShowColumnFooters, ShowColumnHeaders, ShowGroupFooters in the Common tab, not much more in the All tab. So I don't really see how I should change the culture via the options argument to Export.

The export of the code above is still using "," as decimal separator, so Export behaves just like ToExcelML here.

What am I overlooking?

Thanks,
s.

answered on 12 May 2010, 10:30 AM

Hi Stefan Schmiedl,

Straight to your questions:

The .vb version of the file is created by a convertion tool (from C#) which seems cannot properly convert type initializers.
Here is the C# code:

using (Stream stream = dialog.OpenFile())

{

RadGridView1.Export(stream,

new GridViewExportOptions()

{

Format = format,

ShowColumnHeaders = true,

ShowColumnFooters = true,

ShowGroupFooters = true,

});

}

which is incorrectly converted as:

Using stream As Stream = dialog.OpenFile()

RadGridView1.Export(stream, New GridViewExportOptions())

End Using

What you can do is to create a new GridViewExportOptions object before exporting and set its properties.

We will fix the problem in the next version of the demos.

As for the culture - you cannot set it on export options. To my regret the only workaround is to set it to the CurrentThread as you already have done.

I am sorry for your troubles and hope that the solution you found is feasible enough.

Sincerely yours,
Veskoni
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

answered on 01 Jun 2010, 10:19 AM

mmmm...is it the conclusion that the Export funtion does not support changing the decimal seperator to "," ? I am coding in c#, so no VB issues here..

The code is (as your sample):

using (Stream stream = sfd.OpenFile())

{

GridViewExportOptions options = new GridViewExportOptions()

{

Format =

ExportFormat.ExcelML,

ShowColumnHeaders =

true,

ShowColumnFooters =

true,

ShowGroupFooters =

false,

};

GridView.Export(stream, options);

}

How do I set the decimal separator to "," ? Is there a work around you can give an example of ?

also tried somthing like this - which generated an XML file I couldent open. :

string exportString = GridView.ToExcelML(true);

tempFileName = sfd.FileName;

File.WriteAllText(tempFileName, exportString, System.Text.Encoding.Default);

Setting the Encoding to UTF8 gave me the same problem with the decimal...

File.WriteAllText(tempFileName, exportString, System.Text.Encoding.UTF8);

regards

Torben

answered on 01 Jun 2010, 01:08 PM

Hi Yavor

Thanks for the super quick response :-)

What I want is to export the Grid content into an excellML file. When doing this with the new Export og the old method, the decimal seperator is ignored on danish machines, where the decimal separator is "," and the thousand separator is ".2).

What you propose does not fix the problem, after doing this I cannot open the excellML file with excell. I have tried a few different approaches, none of them works. Either the values are a factor 100 to big (the separators are ignored) or excell will not open the spreadsheet.

The Yavor way ;-) :

    
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("da-DK");  
        
            if (sfd.ShowDialog().GetValueOrDefault(false))  
        
            {  
        
              string exportString = GridView.ToExcelML(true);  
        
              tempFileName = sfd.FileName;  
        
              File.WriteAllText(tempFileName, exportString, System.Text.Encoding.Default);  
        
            ..  
        
            ..

The brand new Export way:

    
            using (Stream stream = sfd.OpenFile())  
        
            {  
        
              GridViewExportOptions options = new GridViewExportOptions()  
        
              {  
        
                Format = ExportFormat.ExcelML,  
        
                ShowColumnHeaders = true,  
        
                ShowColumnFooters = true,  
        
                ShowGroupFooters = false,  
        
              };  
        
              GridView.Export(stream, options);  
        
            }

The old way:

    
            string exportString = GridView.ToExcelML(true);  
        
            tempFileName = sfd.FileName;  
        
            File.WriteAllText(tempFileName, exportString);

Any idear for at workaround or something ?

Thanx !

Best regards

Torben

answered on 01 Jun 2010, 02:32 PM

Thanks Stefan - now I get it

Set the app culture at startup to :

    
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("da-DK");

results in the data in the grid is shown with "," separator for numbers (danish), and "," for currency (danish), but I cannot export grid content to an excelML which excell can read...

when setting to:

    
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

I get "." separator in the grid for digits (US style) - I get "," and "Kr" for currency (Danish) - BUT when exporting to xml both digits and currency are set to danish.

This is really weird :-)

But thanx, I gues I must pesuade my users to accept the US style for digits in the grid for now until things have been worked out.

Thank you - is there some kind of points or somethin I should donate to somebody ? I have put you in my book for a free beer here in Århus :-)

Regards

Torben