Databases
StackOverflow2013
Postsdbo
POXtraGrid - Export To Excel
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POXtraGrid - Export To Excel
    text
    copied!<p>I'am using Developer Express <code>XtraGrid</code> Component to show some data. I have 2 <code>XtraGrid</code> on my Windows Application Form. Both grids have more than 200k+ lines, and 8 columns of data, and I have export to excel button. There are two ways (as I know) for exporting grid data to excel.</p> <p>1- <code>grid.ExportToXls();</code> or <code>grid.ExportToXlsx();</code> </p> <p>2- Using Office Interop, and <code>OpenXML Utilities</code></p> <p>If I use <code>grid.ExportToXls();</code> or <code>grid.ExportToXlsx();</code>, the process time is faster than Office Interop Codes (for arround 2k lines of data). But, this method can be used for just 1 grid. So result appears on 2 different <code>Excel</code> files. So, I'am using Office Interop to merge workbooks after process completed. Here is the problem occurs. With both these ways, I am always getting <code>System.OutOfMemory</code> Exception. (See the memory graph below) <img src="https://i.stack.imgur.com/3Wjhe.jpg" alt="enter image description here"></p> <p>I'am stuck here, because the ways I know to export excel are throwing <code>System.OutOfMemory</code> Exception. Do you have any suggestion, how can I export more than 200k - 300k+ lines of data to <code>Excel</code>? I'am using <code>.Net Framework 3.5</code> on <code>Visual Studio 2010</code>. And you can find my Interop, and <code>Document.Format OpenXML Utility</code> codes below.</p> <pre><code>try { SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Title = SaveAsTitle; saveDialog.Filter = G.Instance.MessageManager.GetResourceMessage("EXCEL_FILES_FILTER"); saveDialog.ShowDialog(); if (string.IsNullOrEmpty(saveDialog.FileName)) { // Showing Warning return; } List&lt;GridControl&gt; exportToExcel = new List&lt;GridControl&gt;(); exportToExcel.Add(dataGrid); exportToExcel.Add(summaryGrid); ExportXtraGridToExcel2007(saveDialog.FileName, exportToExcel); } catch (Exception ex) { // Showing Error } </code></pre> <p>And this is my <code>ExportXtraGridToExcel2007();</code> function codes</p> <pre><code>public void ExportXtraGridToExcel2007(string path, List&lt;GridControl&gt; grids) { try { DisableMdiParent(); string tmpPath = Path.GetTempPath(); List&lt;string&gt; exportedFiles = new List&lt;string&gt;(); for (int i = 0; i &lt; grids.Count; i++) { string currentPath = string.Format(@"{0}\document{1}.xlsx", tmpPath, i); GridControl grid = grids[i]; grid.MainView.ExportToXlsx(currentPath); exportedFiles.Add(currentPath); } if (exportedFiles.Count &gt; 0) { OpenXmlUtilities.MergeWorkbooks(path, exportedFiles.ToArray()); foreach (string excel in exportedFiles) { if (File.Exists(excel)) { try { File.Delete(excel); } catch (Exception ex) { EventLog.WriteEntry("Application", ex.Message); } } } } } catch (Exception ex) { // showing error } finally { EnableMdiParent(); } } </code></pre> <p>and this is the OpenXML Merge Work Books Codes</p> <pre><code>public static void MergeWorkbooks(string path, string[] sourceWorkbookNames) { WorkbookPart mergedWorkbookPart = null; WorksheetPart mergedWorksheetPart = null; WorksheetPart childWorksheetPart = null; Sheets mergedWorkbookSheets = null; Sheets childWorkbookSheets = null; Sheet newMergedSheet = null; SheetData mergedSheetData = null; SharedStringTablePart mergedSharedStringTablePart = null; SharedStringTablePart childSharedStringTablePart = null; // Create the merged workbook package. using (SpreadsheetDocument mergedWorkbook = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook)) { // Add the merged workbook part to the new package. mergedWorkbookPart = mergedWorkbook.AddWorkbookPart(); GenerateMergedWorkbook().Save(mergedWorkbookPart); // Get the Sheets element in the merged workbook for use later. mergedWorkbookSheets = mergedWorkbookPart.Workbook.GetFirstChild&lt;Sheets&gt;(); // Create the Shared String Table part in the merged workbook. mergedSharedStringTablePart = mergedWorkbookPart.AddNewPart&lt;SharedStringTablePart&gt;(); GenerateSharedStringTablePart().Save(mergedSharedStringTablePart); // For each source workbook to merge... foreach (string workbookName in sourceWorkbookNames) { // Open the source workbook. The following will throw an exception if // the source workbook does not exist. using (SpreadsheetDocument childWorkbook = SpreadsheetDocument.Open(workbookName, false)) { // Get the Sheets element in the source workbook. childWorkbookSheets = childWorkbook.WorkbookPart.Workbook.GetFirstChild&lt;Sheets&gt;(); // Get the Shared String Table part of the source workbook. childSharedStringTablePart = childWorkbook.WorkbookPart.SharedStringTablePart; // For each worksheet in the source workbook... foreach (Sheet childSheet in childWorkbookSheets) { // Get a worksheet part for the source worksheet using it's relationship Id. childWorksheetPart = (WorksheetPart)childWorkbook.WorkbookPart.GetPartById(childSheet.Id); // Add a worksheet part to the merged workbook based on the source worksheet. mergedWorksheetPart = mergedWorkbookPart.AddPart&lt;WorksheetPart&gt;(childWorksheetPart); // There should be only one worksheet that is set as the main view. CleanView(mergedWorksheetPart); // Create a Sheet element for the new sheet in the merged workbook. newMergedSheet = new Sheet(); // Set the Name, Id, and SheetId attributes of the new Sheet element. newMergedSheet.Name = GenerateWorksheetName(mergedWorkbookSheets, childSheet.Name.Value); newMergedSheet.Id = mergedWorkbookPart.GetIdOfPart(mergedWorksheetPart); newMergedSheet.SheetId = (uint)mergedWorkbookSheets.ChildElements.Count + 1; // Add the new Sheet element to the Sheets element in the merged workbook. mergedWorkbookSheets.Append(newMergedSheet); // Get the SheetData element of the new worksheet part in the merged workbook. mergedSheetData = mergedWorksheetPart.Worksheet.GetFirstChild&lt;SheetData&gt;(); // For each row of data... foreach (Row row in mergedSheetData.Elements&lt;Row&gt;()) { // For each cell in the row... foreach (Cell cell in row.Elements&lt;Cell&gt;()) { // If the cell is using a shared string then merge the string // from the source workbook into the merged workbook. if (cell.DataType != null &amp;&amp; cell.DataType.Value == CellValues.SharedString) { ProcessCellSharedString(mergedWorksheetPart, cell, mergedSharedStringTablePart, childSharedStringTablePart); } } } } } } //Save the changes to the merged workbook. mergedWorkbookPart.Workbook.Save(); } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload