DataGrid for Excel?

Question

I have a program created in WPF. It displays a DataGrid with data from a MySQL database. I want the user to be able to export the contents of the DataGrid to an Excel file. Is this possible with WPF?

When I use the method shown here: https://www.outcoldman.ru/en/blog/show/201

The only line that is imported into the xls file is the header.

Answer 1

I also searched for something simillar to help export data to datagrid in excel, but didn't find anything that works. Atlast I just converted the contents of the DataGrid to a two-dimensional array of rows and exported it using dll interop.

The code looks something like this:

  Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Excel.Range rangeToHoldHyperlink; Excel.Range CellInstance; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlApp.DisplayAlerts = false; //Dummy initialisation to prevent errors. rangeToHoldHyperlink = xlWorkSheet.get_Range("A1", Type.Missing); CellInstance = xlWorkSheet.get_Range("A1", Type.Missing); for (int i = 0; i < NumberOfCols; i++) { for (int j = 0; j <= NumberOfRows; j++) { xlWorkSheet.Cells[j + 1, i + 1] = DataToWrite[j][i]; } } 

If you are looking for some form, they are also supported in this. I wanted to add a hyperlink, and the following code does this:

  CellInstance = xlWorkSheet.Cells[j + 1, i + 1]; xlWorkSheet.Hyperlinks.Add( CellInstance, DataToWrite[j][i], Type.Missing, "Hover Text Comes Here", "Text to be displayed"); 

If you want the first line to be the headline, you can select them as follows:

 Excel.Range Range1 = xlWorkSheet.get_Range("A1"); Range1.EntireRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); Range1.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue); Range1.EntireRow.Font.Size = 14; Range1.EntireRow.AutoFit(); 

Finally, to save excel along the desired path:

 xlWorkBook.SaveAs(@FilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(); 

The interop link is added as follows:

 Right Click on the Project name -> Click "Add reference" -> Goto "COM" tab -> Search for "Microsoft Excel Object Library" click "OK" to add the reference. 

You must use the following namespace:

 using Excel = Microsoft.Office.Interop.Excel; using System.Runtime.InteropServices; 

Answer 2

This link may be useful to you -

Export data to Excel from Silverlight / WPF DataGrid

Answer 3

You can also export the contents of the DataGrid to a CSV file.

I do not know if you use data binding, and here I assume that you are doing this.

 var items = LoadItemsFromUnderlyingDataStore(); itemsDataGrid.ItemsSource = items; private void ItemsDataGridExportButton_Click(object sender, EventArgs e) { var filename = AutoGenerateDateTimedFileName(); using (var csv = new FileStream(filename)) { // if you want any column header in your Excel file, uncomment line below. //csv.WriteLine("Column1,Column2,Column3,Column4"); items.ForEach(item => csv.WriteLine("{0},{1},{2},{3}" , item.PropertyFromFirstColumn , item.PropertyFromSecondColumn , item.PropertyFromThirdColumn , item.PropertyFromFourthColumn); try { csv.Flush(); } catch(IOException ex) { // Handle error (log, whatever...) throw; // If you need to let the exception bubble up... } } } private string AutoGenerateDateTimedFileName() { return string.Format("DataGridExport_{0}_{1}.csv" , DataTime.Today.ToShortDateString() , DateTime.Now.ToLongTimeString()); } 

Thus, this sample code should do this with CSV files, and you can open it using Excel afterwards. You can even start Excel when creating the file.

 private void ItemsDataGridExportButton_Click(object sender, EventArgs e) { // here the upper code sample, and add the following: Process.Start(filename) } 

Now using Excel Interop.

 object[,] values = BuildTwoDimensionalMatrixFromDataGridContent(); Worksheet.Cells.Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault) = values; 

This is perhaps the fastest way to set values ​​in an Excel file, and there is no iteration for addressing.

The first proposed solution using a CSV file is probably the easiest to understand and work with.

Denial of responsibility

This code was created right from my head and as such has not been tested. Some changes may be required for this code to work properly with your programming context. In addition, you may need a little analysis of the differences between the code with the interaction shown here and the one that you can use with WPF.

The best way is perhaps to write yourself a class library that will be deployed with your solution, which will be written in simple C # so that you can use simple Interop instructions.

Answer 4

The sample code extracts a list of all directories from C: \ Windows \ WinSxS, binds it to a DataGrid, and exports the DataGrid to Excel. The main thing here is to know that the fastest way to export data to Excel is to assign an array to a worksheet. But the problem is that we cannot get this array directly from the DataGrid. Therefore, we must first fill it out. Using a little reflection, we can get the values ​​of the properties and fill the array. Export data is one line. The WinSxS folder on my computer contains 14880 directories. The code runs for about 3 seconds (filling DataGrid + export).

  using Excel = Microsoft.Office.Interop.Excel; private void ExportDataGrid() { // Fetch directories var dirInfo = new DirectoryInfo(@"C:\Windows\WinSxS\"); var items = dirInfo.EnumerateDirectories(); dataGrid.ItemsSource = items; var source = dataGrid.ItemsSource; // Create Excel app var excel = new Excel.Application { Visible = true }; excel.ScreenUpdating = false; //Some speed-up var book = excel.Workbooks.Add(); var sheet = (Excel.Worksheet)book.Sheets[1]; int row = -1; //The row in array Type type = null; // Create array to hold data int rows = items.Count(), cols = dataGrid.Columns.Count; var arr = new object[rows, cols]; foreach (DirectoryInfo dir_info in dataGrid.ItemsSource) { ++row; // You can also use GetType().GetTypeInfo() as of .NET 4.5+. // The return type will be TypeInfo. type = dir_info.GetType(); for (int col = 0; col < cols; ++col) { var column_name = (string)dataGrid.Columns[col].Header; var value = type.GetProperty(column_name).GetValue(dir_info); arr.SetValue(value, row, col); } } // Create header for (int col = 0; c < cols; ++c) { sheet.Cells[1, col + 1].Value = dataGrid.Columns[col].Header; } // Dump array - the fastest way sheet.Range["A2"].Resize[rows, cols].Value = arr; // Restore screen updating - otherwise Excel will not response to actions excel.ScreenUpdating = true; } 

Answer 5

 private void data2Exel(DataGrid dataGrid) { Excel.Application excel = new Excel.Application(); excel.Visible = true; //www.yazilimkodlama.com Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value); Worksheet sheet1 = (Worksheet)workbook.Sheets[1]; for (int j = 0; j < dataGrid.Columns.Count; j++) //Başlıklar için { Range myRange = (Range)sheet1.Cells[1, j + 1]; sheet1.Cells[1, j + 1].Font.Bold = true; //Başlığın Kalın olması için sheet1.Columns[j + 1].ColumnWidth = 15; //Sütun genişliği ayarı myRange.Value2 = dataGrid.Columns[j].Header; } for (int i = 0; i < dataGrid.Columns.Count; i++) { for (int j = 0; j < dataGrid.Items.Count; j++) { TextBlock b = dataGrid.Columns[i].GetCellContent(dataGrid.Items[j]) as TextBlock; Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[j + 2, i + 1]; myRange.Value2 = b.Text; } } }