# Excel format issus

GridView
Top achievements
Rank 1
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";
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

0
Allan
Top achievements
Rank 1
answered on 15 Jul 2009, 04:48 PM
The error you are seeing is due to something called 'Extension Hardening' that was introduced as part of Office 2007

http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

Basically, from the code you are using, you are saving HTML with an XLS file extension. When you open that, Excel does not recognize the HTML content so it flags it as a security risk.

Any reason why you are using ToHtml and not ToCsv or ToExcelML?
0
Asmund Stensrud
Top achievements
Rank 1
answered on 21 Jul 2009, 07:43 AM
Allan:
If I may answer (having the same Problem here...):
Can't use ToExcelML: Excel 2007 is created. We need pre-2007-excel format....
Can't use ToCsv: Seperator is "," wich excel does not recognize. ("\t" or ";" would be nice...)
So.. ToHTML seems to be the only valid option.
0
Stefan Dobrev
Telerik team
answered on 24 Jul 2009, 12:19 PM
Hi Asmund,

We may add some export options in the future that will allow you to specify common properties for different export types. For example for CSV we can have delimiter character. Will this work for you?

Excel 2007 has import external data capabilities which allow you to specify what will be delimiters when you import CSV files. I'm not sure does Excel 2003 has this.

Best wishes,
Stefan Dobrev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Nils
Top achievements
Rank 1
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
0
Nils
Top achievements
Rank 1
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

0
Allan
Top achievements
Rank 1
answered on 06 Aug 2009, 06:29 PM
Also, is it possible to export a hierarchy grid to Excel?
0
Telerik team
answered on 12 Aug 2009, 06:43 AM
Hello Allan,

Currently hierarchy export is not supported however we will do our best to provide this as soon as possible.

All the best,
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Noam
Top achievements
Rank 1
answered on 26 Oct 2009, 12:39 PM
Hi,
Is it possible to export a grouped grid to Excel?
It seems that ToExcelML doesn't work with a group grid.
0
Telerik team
answered on 29 Oct 2009, 09:07 AM
Hi Noam,

While data will be grouped in all formats visually only HTML format will display group headers.

Best wishes,
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
0
Mily
Top achievements
Rank 1
answered on 09 Nov 2009, 12:09 PM

Could you please confirm formatting is not supported when exporting to Excel ML format? I had a look at the code for ExportExtensions.ToExcelML methods and it looks like formatting information is not persisted in the output (i.e. <Style ss:ID="78"><Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FF0000"/></Style>). If not, are you planning to extend export to Excel ML functionality or even better, is there a chance to see Excel 2007 OOXML export in the near future?

Best Regards

Milosz
0
Telerik team
answered on 09 Nov 2009, 03:45 PM
Hi Milosz,

Unfortunately you are right. We didn't have enough time to include formatting for ExcelML however we will do our best to provide it as soon as possible.  We don't have currently plans for Excel 2007 OOXML support however I've logged your request and we will consider this for future versions.

Sincerely yours,
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
0
Rodney Foley
Top achievements
Rank 1
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()); } } }

0
Nils
Top achievements
Rank 1
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:

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
0
Rodney Foley
Top achievements
Rank 1
answered on 19 Nov 2009, 02:15 PM
You are correct if all they have installed on their system is Office and nothing else that like to read XML files you are safe.  However I cannot control what will be installed on users systems so I had to be extra sure. In testing with out launching excel.exe I found that  whatever the last thing installed on a system that like to read XML took over.  Things like Browsers, XML Spy, even Visual Studio, after that testing I added the excel.exe and it worked every time. I only had to deal with what happens if excel is not installed.
0
Peter Meinl
Top achievements
Rank 1
answered on 05 Feb 2010, 09:41 PM

.ToExcelXML seems to create invalid ExcelXML.

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

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>

0
Telerik team
answered on 11 Feb 2010, 08:03 AM
Hi Peter,

We fixed this and the fix will be part of our upcoming latest build this Friday. I've added 1000 Telerik points to your account.

Best wishes,
the Telerik team

Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Follow the status of features or bugs in PITS and vote for them to affect their priority.
0
Stefan Schmiedl
Top achievements
Rank 2
answered on 03 May 2010, 09:09 PM

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).

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.

0
Telerik team
answered on 04 May 2010, 06:20 AM
Hi,

Have you tried our new Export() method? You can set the culture for GridViewExportOptions - please check this demo for more info: http://demos.telerik.com/wpf/?GridView/Exporting

Regards,
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.
0
Stefan Schmiedl
Top achievements
Rank 2
answered on 04 May 2010, 09:04 AM

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.
0
Veselin Vasilev
Telerik team
answered on 12 May 2010, 10:30 AM
Hi Stefan Schmiedl,

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.
0
Torben Bach
Top achievements
Rank 1
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,

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

0
Yavor Georgiev
Telerik team
answered on 01 Jun 2010, 11:04 AM
Hello Torben Bach,

The decimal separator depends on the system culture. You can change the system culture for the current thread like so:

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

You can experiment with different cultures until you reach the desired result.

Greetings,
Yavor Georgiev
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.
0
Torben Bach
Top achievements
Rank 1
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
0
Stefan Schmiedl
Top achievements
Rank 2
answered on 01 Jun 2010, 02:16 PM
Torben,

I found that for me the situation was reversed, i.e. the grid exported using German locale, while ExcelML expects US-localized number formatting. So try setting the current thread's culture to en-US (like I posted on May 3) and try again.

HTH,
s.
0
Torben Bach
Top achievements
Rank 1
answered on 01 Jun 2010, 02:32 PM
Thanks Stefan - now I get it

Set the app culture at startup to :

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:

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

0
Yavor Georgiev
Telerik team
answered on 01 Jun 2010, 05:10 PM
Hi Torben Bach,

We have improved our Culture detection mechanism, and have introduced support for manually setting the export culture directly in the GridViewExportOptions, not through the CurrentThread. This functionality will be available in our next internal build (this Friday), and in the Q2 beta, which will be available publicly.

Greetings,
Yavor Georgiev
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.
0
Torben Bach
Top achievements
Rank 1
answered on 01 Jun 2010, 07:40 PM
Hi Yavor

Well I guess I'll be handing out quit a lot of beers here in Århus :-)

Thanks for super quick responses and follow up. Super service :-)

Best regards

Torben
Tags
GridView
Fu Wen
Top achievements
Rank 1
Allan
Top achievements
Rank 1
Asmund Stensrud
Top achievements
Rank 1
Stefan Dobrev
Telerik team
Nils
Top achievements
Rank 1
Telerik team
Noam
Top achievements
Rank 1
Mily
Top achievements
Rank 1
Rodney Foley
Top achievements
Rank 1
Peter Meinl
Top achievements
Rank 1
Stefan Schmiedl
Top achievements
Rank 2
Veselin Vasilev
Telerik team
Torben Bach
Top achievements
Rank 1
Yavor Georgiev
Telerik team