|
|
Comments and Discussions
|
|
 |

|
its wonderful and works fine... and simple also... thanks for sharing..
|
|
|
|

|
Can you please suggest me to hide some particular columns in the exported report file.
|
|
|
|
|

|
Hello dear, I have a problem when trying to generate csv file, it generates a NullReferenceException, I can do?
|
|
|
|

|
1. The problem is in line 65, try it with "Try / Catch":
if (pi != null) {
try {
strValue = pi.GetValue(System.data, null).ToString();
} catch (Exception ex) {
}
}
2. In line 70:
if (!string.IsNullOrEmpty(strValue)) {
strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
} else {
strValue = "";
}
3. Line 80:
StreamWriter sw = new StreamWriter(objSFD.OpenFile(), Encoding.UTF8);
4. Use ; not , in line 117
strBuilder.AppendLine(String.Join(";", lstFields.ToArray()));
modified 1 Jul '12 - 15:52.
|
|
|
|

|
Is it possible to show Open,Save,Cancel dialog box when clicked on export to excel in your code.
thanks
|
|
|
|

|
Hi, when I try to use this on my application, the only thing that gets imported is the header and everything else is blank. Any suggestions?
|
|
|
|
|

|
I have Silverlight DataGrid with DataGridTemplateColumn having some image column... I need to export data and images to excel ...
I need to export images in cells using silver light .
Can u help
|
|
|
|

|
Thank you for your perfect codding. but the question is if we have multiple data grids, and plan to have them all in one excel file but in various worksheet with some specific worksheet name, then what is solution? thanks for your help.
|
|
|
|
|
|

|
Very nice solution. I had to make some chnages to handle nullable types and null strings.
if (objBinding != null)
{
bool isNullable = false;
if (objBinding.Path.Path != "")
{
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null)
{
Type dataType = pi.PropertyType;
strValue = string.Empty;
if (pi.GetValue(data, null) != null)
{
strValue = pi.GetValue(data, null).ToString();
}
else if ( dataType == typeof(System.String) ||
(dataType.IsGenericType && dataType.GetGenericTypeDefinition() == typeof(System.Nullable<>)))
{
isNullable = true;
}
}
}
if (objBinding.Converter != null)
{
if (strValue != string.Empty || (strValue == string.Empty && isNullable))
{
strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
else
{
strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}
}
|
|
|
|

|
I want this topic, now i got it.
|
|
|
|

|
I think the current code can't resolve bindings like Binding="{Binding Path=Entity1.Name.FirstName}".
And I think is better to use the Converter over an object and not over a string.
I have done a change in your code using Split function:
foreach (object data in ItemsSource)
{
lstFields.Clear();
foreach (DataGridColumn col in Columns)
{
object objValue = null;
string strValue = "";
Binding objBinding = null;
if (col is DataGridBoundColumn)
objBinding = (col as DataGridBoundColumn).Binding;
if (col is DataGridTemplateColumn)
{
if (col.SortMemberPath != null)
objBinding = new Binding(col.SortMemberPath);
}
if (objBinding != null)
{
if (!string.IsNullOrEmpty(objBinding.Path.Path))
{
if (objBinding.Path.Path.Contains("."))
{
string[] splited = objBinding.Path.Path.Split(new char[] { '.' });
object dataaux = data;
PropertyInfo pi = null;
foreach (string s in splited)
{
if (dataaux == null)
{
objValue = "";
break;
}
else
{
pi = dataaux.GetType().GetProperty(s);
dataaux = pi.GetValue(dataaux, null);
}
}
if (pi != null) objValue = dataaux.ToString();
}
else
{
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null) objValue = pi.GetValue(data, null);
}
}
if (objBinding.Converter != null)
{
if (objValue != null)
strValue = objBinding.Converter.Convert(objValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
else
strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}
lstFields.Add(FormatField(strValue, strFormat, CellType.Cell));
}
BuildStringOfRow(strBuilder, lstFields, strFormat);
}
|
|
|
|

|
Augmented slightly for cases where s is a dictionary:
if (dataaux == null)
{
objValue = "";
break;
}
else
{
if (s.Contains('['))
{
string coll = s.Substring(0, s.IndexOf('['));
string key = s.Substring(s.IndexOf('[') + 1, s.IndexOf(']') - s.IndexOf('[') - 1);
PropertyInfo p = dataaux.GetType().GetProperty(coll);
dynamic dict = p.GetValue(dataaux, null);
dataaux = dict[key];
}
else
{
pi = dataaux.GetType().GetProperty(s);
dataaux = pi.GetValue(dataaux, null);
}
}
|
|
|
|

|
Excellent!
Also - if you want to have your Excel Cells formatted as numbers for any numeric value in the DataGrid, you can replace the current FormatField procedure, with the one below. XML export only.
private static string FormatField(string data, string format)
{
double Num;
bool isNum = double.TryParse(data, out Num);
switch (format)
{
case "XML":
return String.Format(isNum ? "<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>" : "<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", data);
case "CSV":
return String.Format("\"{0}\"", data.Replace("\"", "\"\"\"").Replace("\n", "").Replace("\r", ""));
}
return data;
}
|
|
|
|

|
Is there a way to include the headers into the top cell of each column in the excel worksheet?
|
|
|
|

|
If the Headers in the grid are visible, the column headers would get exported into the excel sheet.
Please see the piece of piece of code that checks for "(dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All)" in the code.
Thanks
Arasu Elango
|
|
|
|

|
Out of the many examples I've review and tried over the past few months, this one works the best.
|
|
|
|
|

|
This looks like it will be a great help if we can get it to work. In my project it crashes on line 66 if (pi != null) strValue = pi.GetValue(data, null).ToString(); with a message that says 'Object reference not set to an instance of an object.' Unfortunately I'm not skilled enough to troubleshoot it. Could it be that some of the cells contain nulls?
|
|
|
|

|
Hi,
It looks like the "data" variable is null and hence the error occurs. Please see the comment from "Noah Wollowick" in which he has provided the code change to address this issue.
Thanks,
R. Arasu Elango
|
|
|
|

|
Great article man. i achieved my first task using this code thanks a lot
|
|
|
|

|
HI All, I have a DataGrid like below. Could you please advise how to change your codes to make it work for my scenario? Appreciate your help!
<slData:DataGrid x:Name="QueryDetailsDataGrid">
<slData:DataGrid.Columns>
<codeBehind:GraphicAttributeColumn Width="50" CanUserSort="False" AttributeName="CODE" Header="Code"/>
</slData:DataGrid.Columns>
</slData:DataGrid>
modified on Tuesday, September 21, 2010 5:15 PM
|
|
|
|

|
Hello,
The post was very use full, but in one scenario iam getting issue.
Iam having two tables data in resultset, and binding goes like this in grid column
Binding="{Binding Department.Name, Mode=OneWay}"
Here PropertyInfo pi having null value so how can i achieve the expected result?
Please help me.
|
|
|
|

|
I'm using a datapager, which limits the datagrid size to 3 records per page (to save real estate on the webpage). Using DataGridExtensions.cs will only export the 3 current records in the datagrid. What can I do to export all records from the entire query in the DomainDataSource?
|
|
|
|

|
i have the same problem,
have you found a solution?
|
|
|
|

|
Sorry, I was not active on the board for a while. In such case, you would have to change the export code such that the datasource of the datapager is used as the object to export. I do not work on silverlight projects and hence, uninstalled silverlight from my system - hence, unable to test.
Sorry about that.
Thanks,
R. Arasu Elangno
|
|
|
|

|
My solution was to add a second grid that is populated with the same query from the first grid. But the second grid is invisible to the user and has no datapager. As a result, all rows in the datagrid get exported.
It's a messy kludge but I'm not a real software developer and this was the best I could do until a real developer comes up with a classy solution.
|
|
|
|

|
Here is the updated class. Made a really minor change to get that working.
I am using the datapager along with the datagrid object, and looping through all the pages in the datapager. Look fof "/// PAGE LOOP BEGIN ///" below
using System;
using System.Windows;
using System.Windows.Data;
using System.Windows.Media;
using System.Windows.Controls;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using System.Xml.Linq;
public static class DataGridExtensions
{
public static void Export(this DataGrid dg, DataPager dp)
{
ExportDataGrid(dg, dp);
}
public static void ExportDataGrid(DataGrid dGrid, DataPager dPager)
{
SaveFileDialog objSFD = new SaveFileDialog() { DefaultExt = "csv", Filter = "CSV Files (*.csv)|*.csv|Excel XML (*.xml)|*.xml|All files (*.*)|*.*", FilterIndex = 1 };
if (objSFD.ShowDialog() == true)
{
string strFormat = objSFD.SafeFileName.Substring(objSFD.SafeFileName.IndexOf('.') + 1).ToUpper();
StringBuilder strBuilder = new StringBuilder();
if (dPager.Source == null) return;
List<string> lstFields = new List<string>();
if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
{
foreach (DataGridColumn dgcol in dGrid.Columns)
lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
BuildStringOfRow(strBuilder, lstFields, strFormat);
}
//////////////////////// PAGE LOOP BEGIN ////////////////////////////////////////
for (int i = 0; i < dPager.PageCount ; i++)
{
dPager.PageIndex = i;
foreach (object data in dPager.Source)
{
lstFields.Clear();
foreach (DataGridColumn col in dGrid.Columns)
{
string strValue = "";
Binding objBinding = null;
if (col is DataGridBoundColumn)
objBinding = (col as DataGridBoundColumn).Binding;
if (col is DataGridTemplateColumn)
{
//This is a template column... let us see the underlying dependency object
DependencyObject objDO = (col as DataGridTemplateColumn).CellTemplate.LoadContent();
FrameworkElement oFE = (FrameworkElement)objDO;
FieldInfo oFI = oFE.GetType().GetField("TextProperty");
if (oFI != null)
{
if (oFI.GetValue(null) != null)
{
if (oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)) != null)
objBinding = oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)).ParentBinding;
}
}
}
if (objBinding != null)
{
if (objBinding.Path.Path != "")
{
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null) strValue = pi.GetValue(data, null).ToString();
}
if (objBinding.Converter != null)
{
if (strValue != "")
strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
else
strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}
lstFields.Add(FormatField(strValue, strFormat));
}
BuildStringOfRow(strBuilder, lstFields, strFormat);
}
}
//////////////////////// PAGE LOOP END ////////////////////////////////////////
StreamWriter sw = new StreamWriter(objSFD.OpenFile());
if (strFormat == "XML")
{
//Let us write the headers for the Excel XML
sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\">");
sw.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
sw.WriteLine("<Author>Arasu Elango</Author>");
sw.WriteLine("<Created>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</Created>");
sw.WriteLine("<LastSaved>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</LastSaved>");
sw.WriteLine("<Company>Atom8 IT Solutions (P) Ltd.,</Company>");
sw.WriteLine("<Version>12.00</Version>");
sw.WriteLine("</DocumentProperties>");
sw.WriteLine("<Worksheet ss:Name=\"Silverlight Export\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
sw.WriteLine("<Table>");
}
sw.Write(strBuilder.ToString());
if (strFormat == "XML")
{
sw.WriteLine("</Table>");
sw.WriteLine("</Worksheet>");
sw.WriteLine("</Workbook>");
}
sw.Close();
}
}
private static void BuildStringOfRow(StringBuilder strBuilder, List<string> lstFields, string strFormat)
{
switch (strFormat)
{
case "XML":
strBuilder.AppendLine("<Row>");
strBuilder.AppendLine(String.Join("\r\n", lstFields.ToArray()));
strBuilder.AppendLine("</Row>");
break;
case "CSV":
strBuilder.AppendLine(String.Join(",", lstFields.ToArray()));
break;
}
}
private static string FormatField(string data, string format)
{
switch (format)
{
case "XML":
return String.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", data);
case "CSV":
return String.Format("\"{0}\"", data.Replace("\"", "\"\"\"").Replace("\n", "").Replace("\r", ""));
}
return data;
}
}
|
|
|
|

|
Hi,
Thank a lot for the tool.
I have one suggestion, so that the class will handle NULL data columns:
Change the following:
// Original
strValue = pi.GetValue(data, null).ToString();
// End Original
// to:
// New
if (pi != null)
{
if (pi.GetValue(data, null) != null)
{
strValue = pi.GetValue(data, null).ToString();
}
else
{
strValue = "";
}
}
// End New
I'm sure there's a more elegant way to make the change, but I have a deadline ... Thanks for the module, it was a help...
Best,
Noah Wollowick
Scopus IT
Microsoft Consulting and IT Staffin
noah at scopusit dot com
|
|
|
|

|
which version of SL is this example? I'm currently on SL2 and SL2 does not support both SaveFileDialog and bindingExpression.
|
|
|
|
|

|
Hi! nice solution on exporting datagrid to excel.
For anyone having problems with exporting datagrids that have special characters in it,
adding following lines in FormatField() Method will replace the escape characters.
case "XML":
data = data.Replace("&", "&").Replace("<", "<").Replace(">", ">")
.Replace("\"", """).Replace("'", "'");
return String.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", data);
|
|
|
|

|
Many thanks for the time saving code.
-- David
|
|
|
|

|
You are very welcome.
|
|
|
|
|

|
hola, quiero decirte que he hecho tu codigo pero no me esta grabando informacion de las columnas, solo los titulos de estas,
como hago para que me capture la informacion que se ingresa en los controles del datagrid¿?
asi es como me muestra el xml:
<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Arasu Elango</Author>
<Created>jueves, 22 de abril de 2010</Created>
<LastSaved>jueves, 22 de abril de 2010</LastSaved>
<Company>Atom8 IT Solutions (P) Ltd.,</Company>
<Version>12.00</Version>
</DocumentProperties>
<Worksheet ss:Name="Silverlight Export" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Table>
<Row>
<Cell><Data ss:Type="String">Fase</Data></Cell>
<Cell><Data ss:Type="String">Tarea</Data></Cell>
<Cell><Data ss:Type="String">Responsable Principal</Data></Cell>
<Cell><Data ss:Type="String">Grupo de Trabajo</Data></Cell>
<Cell><Data ss:Type="String">Tarea Resumen</Data></Cell>
<Cell><Data ss:Type="String">Entregable</Data></Cell>
<Cell><Data ss:Type="String">Hito</Data></Cell>
<Cell><Data ss:Type="String">Duracion / Dias</Data></Cell>
<Cell><Data ss:Type="String">Fecha Inicio</Data></Cell>
<Cell><Data ss:Type="String">Fecha Final</Data></Cell>
<Cell><Data ss:Type="String">Calendario</Data></Cell>
<Cell><Data ss:Type="String">Descripcion</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
</Row>
que puedo hacer¿?
|
|
|
|

|
I am sorry.. I do not understand Spanish. Is it possible for you to repost the message in English
Translated using online tranlator:
Lo siento.. Yo no comprendo español. Es posible para usted al repost el mensaje en inglés
Arasu Elango
|
|
|
|

|
if (objBinding != null)
{
if (objBinding.Path.Path != "")
{
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null) strValue = pi.GetValue(data, null).ToString(); //HERE IF pi is nullable and getValue is null. .toString() throws exception
}
if (objBinding.Converter != null)
{
if (strValue != "")
strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
else
strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}
Francesco Umani
|
|
|
|

|
Nice catch. Thanks for pointing. I missed that.
R. Arasu Elango
|
|
|
|

|
Hi There,
thank you very much you saved me quite a headache here. This worked perfectly for me using ria sevices as the datasource.
Just one question, is it possible to give the file a name in the code rather than having the user enter a name?
Cheers,
Rocky
|
|
|
|

|
I am glad it helped you.
Regarding your question, I do not think that you can hardcode using a name. Such a feature would expose a security threat (websites can hack into local computer to write/overwrite contents without the user knowledge)...
Such a feature would be good though. If you come across a way to accomplish it, kindly post it so that it can be useful for the rest of us.
Thanks
R. Arasu Elango
|
|
|
|

|
below code not work when bind to a dataview, it is only work when bind to a entity class.
////////////////////////////////////////////////////////////////////////////
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null) strValue = pi.GetValue(data, null).ToString();
////////////////////////////////////////////////////////////////////////////
|
|
|
|

|
Sorry - I have not tested the code with a dataview as source. If you can email me the code that you are working (with dataview), I can take a look at that.
|
|
|
|

|
i have the same problem.
did you find a solution ?
tks very much
|
|
|
|

|
First of all, thank you for posting this. For the most part it worked great for me. One thing that didn't work though was where I made use of DataGridTemplateColumn in my project. I had some such as:
<data:DataGridTemplateColumn Header="Size" HeaderStyle="{StaticResource BlueColumnHeader}"
IsReadOnly="True" Width="Auto" CellStyle="{StaticResource NoFocusDataGridCellStyle}">
<data:DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<ContentControl Content="{Binding Path=SizeFormatted, Converter={StaticResource SizeConverter}}"
HorizontalContentAlignment="Stretch" VerticalContentAlignment="Stretch" Padding="0,0,0,0"/>
</DataTemplate>
</data:DataGridTemplateColumn.CellTemplate>
</data:DataGridTemplateColumn>
My DataGridTemplateColumn has a ContentControl in it, which I use to display some custom controls. In any case, your implementation for DataGridTemplateColumn didn't work for me, so I rewrote it as follows which now works in my case. Just posting this in case others run into that issue...
foreach (object data in dGrid.ItemsSource)
{
lstFields.Clear();
foreach (DataGridColumn col in dGrid.Columns)
{
string strValue = String.Empty;
if (col is DataGridBoundColumn)
{
Binding objBinding = (col as DataGridBoundColumn).Binding;
if (objBinding != null)
{
if (objBinding.Path.Path != "")
{
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null)
strValue = pi.GetValue(data, null).ToString();
}
if (objBinding.Converter != null)
{
if (strValue != "")
strValue = objBinding.Converter.Convert(strValue,
typeof(string), objBinding.ConverterParameter,
objBinding.ConverterCulture).ToString();
else
strValue = objBinding.Converter.Convert(data,
typeof(string), objBinding.ConverterParameter,
objBinding.ConverterCulture).ToString();
}
}
}
else if (col is DataGridTemplateColumn)
{
FrameworkElement frameworkElement = col.GetCellContent(data);
if (frameworkElement is ContentControl)
{
ContentControl contentControl = frameworkElement as ContentControl;
PropertyInfo propertyInfo = contentControl.Content.GetType().GetProperty("Text");
if (propertyInfo != null)
strValue = propertyInfo.GetValue(contentControl.Content, null) as string;
}
}
lstFields.Add(FormatField(strValue, strFormat));
}
BuildStringOfRow(strBuilder, lstFields, strFormat);
}
|
|
|
|

|
Thanks for posting the corrected code. I will incorporate the changes and update the code (after doing some testing).
Thanks,
R. Arasu Elango
|
|
|
|

|
To be clear, not saying my code is the correct way for all scenarios where DataGridTemplateColumn is used. It is just a change I had to make for it to work for my situation. I am not sure if there is a good generic solution for when DataGridTemplateColumn is used because you may put whatever kind of content you want in such datagrid cells.
|
|
|
|
 |
|
|
General News Suggestion Question Bug Answer Joke Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
|
This article explains how to export the contents of a DataGrid to Excel/CSV.
| Type | Article |
| Licence | CPOL |
| First Posted | 1 Dec 2009 |
| Views | 75,869 |
| Downloads | 3,416 |
| Bookmarked | 46 times |
|
|