Export Datagrid to Excel

Jan 25, 2009 at 4:13 PM
Hi!
I am trying to export datagrid to Excel in WPF but I keep failing. The problem is that Data grid controller in WPF toolkit dosen´t have
The following function Datagrid.RenderControl(htmlWriter);


This is my code!

 

protected void ExportToExcelFile(DataGrid gvData) {

 

 

 

DateTime date = new DateTime();

 

date =

DateTime.Now;

 

 

string strDate = date.ToString().Replace("-", "").Replace(":", "").Replace("_", "").Replace(" ", "");

 

 

string strFilename = "TsPro_" + strDate + ".xls";

 

 

try {

 

 

HttpContext.Current.Response.Clear();

 

 

HttpContext.Current.Response.ClearContent();

 

 

HttpContext.Current.Response.ClearHeaders();

 

 

HttpContext.Current.Response.Buffer = true;

 

 

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //"text/plain"; /

 

 

HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + strFilename);

 

 

HttpContext.Current.Response.Charset = "";

 

 

StringWriter stringWrite = new StringWriter();

 

 

HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWrite);

 

gvData.RenderControl(htmlWriter);

 

HttpContext.Current.Response.Write(gvData);

 

 

HttpContext.Current.Response.Flush();

 

 

HttpContext.Current.Response.Close();

 

}

catch(Exception e) {

 

 

// LOGGER.ErrorException("Could not export a gridview to a file.", e);

 

}

}

Coordinator
Jan 26, 2009 at 12:26 PM
WPF's DataGrid is a bit different than ASP.net's DataGrid.  One way you can export to excel with WPF's DG is to use the DataGrid.CopyToClipboard command which makes a copy in a CSV, html, and text format.  From there you can either just create a csv file or use the excel APIs to create an excel file.  Hope that helps.
Feb 13, 2009 at 7:01 PM
Edited Feb 13, 2009 at 7:01 PM
Hi,

I might be in dire need of lunch but I can't find a CopyToClipboard command or anything like it for  the WPF datagrid. Would it be possible for you to provide a brief code snippet because I'm scratching my head and clearly missing something obvious.

Thanks!
Coordinator
Feb 14, 2009 at 2:16 PM
Sorry, I think what I meant to say was the built in copy functionality of DataGrid.  DataGrid has a ClipboardCopyMode and does use a command to do a copy but the only way you can customize it is by overriding one of the protected copy methods.  The default copy functionality does create the formats that I describe above so you should be able to export that to excel.
Feb 26, 2009 at 6:45 PM
Edited Feb 26, 2009 at 6:58 PM
Vinsibal, Can you expand on this a little more?  What do you mean by "default copy funcationality", because I haven't found any.

Spitting out a DataGrid to CSV/Excel/HTML is probably the most common feature request I've seen, and while people say it can be done, I've yet to see one example of how to do it.  Any guidance would be most apprciated.

 
Coordinator
Mar 4, 2009 at 3:37 AM
When you do a "CTRL + C" on selected data in the DataGrid, it should copy it to the different formats on the clipboard which should be pastable in excel.
Mar 4, 2009 at 4:12 PM
I don't know if this will be helpful but I am on a project where I will soon be facing the same problem, namely exporting from a DataGrid to Excel.  Therefore I have given it a little thought.

I know that Excel supports data mapping using XML.

http://msdn.microsoft.com/en-us/office/bb872411.aspx

I also know that many DataGrids use object collections for data binding.  You can use LINQ to XML to generate your XML.  If you are getting the data from a database you can also do a similar thing.  Then you use the methodology described in the link above to populate your Excel spreadsheet.

-jupiter

 
Mar 4, 2009 at 5:03 PM
Vinsibal,

That's not really a good answer though, because if I have content that scrolls, it'll be off the page and not loaded (if Virtualization is turned on).  Even if that weren't the case, writing some code that automatically selected all the rows so I can fake copy it, so I can then convert it to another format just seems like a hack to me.

Jupiter :Mapping the ItemSource is also not a viable solution either since you might not be using all the properties/members as columns and it will lose any sort of formatting you have on the grid.

Basically, without a out of the box export (or at least a real solution which is documented on MSDN or somewhere similar), there's no way I could advise using this DataGrid to my client.  Whether we agree with it, a client's number one expectation of any sort of Grid is that they can get that data into Excel.  While I appreciate all the hard work that everyone has undertaken to get this grid out there, without a valid export it's just not a viable solution.



Coordinator
Mar 5, 2009 at 4:40 PM
There is only item-container virtualization which is different than data virtualization.  So all items will be available at your disposal regardless of virtualization.  I do agree that it would be a hack to select all then execute the clipboardcopy command.  The WPF DataGrid does not have oob functionality to export to the excel format but it does have the functionality to copy it's content to the clipboard as a CSV, Text, or HTML format.  What's a little unfortunate is that the only way access this function is by selecting cells.  A bug has been logged in our database.
Apr 15, 2009 at 9:50 AM
You can manualy copy the content of your DataGrid to the Clipboard using this

grid.SelectionMode = DataGridSelectionMode.Extended;
grid.SelectAllCells();
grid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, grid);

Then you can paste the data to Excel or if you need to create a file you can call Clipboard.GetData(DataFormats.CommaSeparatedValue)
to get the data from the clipboard in CSV.
Nov 5, 2010 at 5:29 AM

Hi,

This code does not require Office libraries and end opens Microsoft Excel           

 try
            {
                if (this.Contactos == null || this.Contactos.Count == 0)
                    return;

                StringBuilder sb = new StringBuilder();
                //
                // Titulos
                foreach (var item in typeof(ConsultarContactoSesionesResult).GetProperties())
                    sb.AppendFormat(CultureInfo.CurrentUICulture, "{0}\t", item.Name);
                sb.AppendLine();

                //
                // Contenido
                foreach (var item in this.Contactos)
                {
                    foreach (var propiedad in typeof(ConsultarContactoSesionesResult).GetProperties())
                        sb.AppendFormat(CultureInfo.CurrentUICulture, "{0}\t", propiedad.GetValue(item, null));

                    sb.AppendLine();
                }
                //
                // Grabar Archivo
                string path = string.Format("{0}\\Contactos_{1:yyyyMMdd}.xls",
                                        Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
                                        DateTime.Today);
                File.WriteAllText(path, sb.ToString());

                ProcessStartInfo obInfo = new ProcessStartInfo(path);
                obInfo.UseShellExecute = true;
                obInfo.WindowStyle = ProcessWindowStyle.Normal;

                using (Process obJob = new Process())
                {
                    obJob.StartInfo = obInfo;
                    obJob.Start();
                }
            }
            catch (Exception ex)
            {
                Internas.MostrarError(ex);
            }

Nov 10, 2011 at 8:19 PM

pikul

 

Thanks, your posting was the best of all. Simple and plain. Now i simply pass the CSV file to Excel.  AMAZINGLY SIMPLE!!!!

 

Thanks,

 

Doc

Jan 21, 2012 at 6:23 AM

For Export To Excel,Pdf,CSv (With rich formatting ) , I have extended WPF Tool Kit DataGrid and posted my solution here. http://wpfextendeddatagrid.codeplex.com/ .

 

And if you want full version of the extended datagrid you can get it from here 

 

http://interviewforit.firm.in/WpfControlkit/wpfcontrolkit.application Or Visit http://www.wpfcontrolkit.com for more detail on the product.

 

Regards,

Bhuhan Poojary

CEO WPF Control Kit