问题描述
我使用Developer Express XtraGrid 组件来显示一些数据。我的Windows应用程序表单上有2个 XtraGrid 。两个网格都有超过200k +行和8列的数据，我已经导出到excel按钮。有两种方式（我知道）将网格数据导出到excel。
I'am using Developer Express XtraGrid Component to show some data. I have 2 XtraGrid 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.
1- grid.ExportToXls（）; 或 grid.ExportToXlsx（）;
2-使用Office Interop和 OpenXML实用程序
2- Using Office Interop, and OpenXML Utilities
如果我使用 grid.ExportToXls（）; 或 grid.ExportToXlsx（）; ，处理时间比Office Interop代码快（用于arround 2k行数据）。但是，这种方法只能用于1个网格。所以结果会出现在两个不同的 Excel 文件中。所以，我在工作完成后使用Office Interop合并工作簿。这是发生的问题。通过这两种方式，我总是得到 System.OutOfMemory 异常。 （见下面的记忆图）
If I use grid.ExportToXls(); or grid.ExportToXlsx();, 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 Excel 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 System.OutOfMemory Exception. (See the memory graph below)
我被困在这里，因为我知道导出excel的方式是抛出 System.OutOfMemory 异常。你有什么建议，如何将超过200k - 300k +数据的数据导出到 Excel ？我在 Visual Studio 2010 中使用 .Net Framework 3.5 。
您可以在下面找到我的Interop和 Document.Format OpenXML实用程序代码。
I'am stuck here, because the ways I know to export excel are throwing System.OutOfMemory Exception. Do you have any suggestion, how can I export more than 200k - 300k+ lines of data to Excel? I'am using .Net Framework 3.5 on Visual Studio 2010.And you can find my Interop, and Document.Format OpenXML Utility codes below.
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<GridControl> exportToExcel = new List<GridControl>();
exportToExcel.Add(dataGrid);
exportToExcel.Add(summaryGrid);
ExportXtraGridToExcel2007(saveDialog.FileName, exportToExcel);
}
catch (Exception ex)
{
// Showing Error
}
这是我的 ExportXtraGridToExcel2007（）; 功能代码
public void ExportXtraGridToExcel2007(string path, List<GridControl> grids)
{
try
{
DisableMdiParent();
string tmpPath = Path.GetTempPath();
List<string> exportedFiles = new List<string>();
for (int i = 0; i < 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 > 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();
}
}
这是OpenXML合并工作书籍代码
and this is the OpenXML Merge Work Books Codes
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<Sheets>();
// Create the Shared String Table part in the merged workbook.
mergedSharedStringTablePart = mergedWorkbookPart.AddNewPart<SharedStringTablePart>();
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<Sheets>();
// 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<WorksheetPart>(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<SheetData>();
// For each row of data...
foreach (Row row in mergedSheetData.Elements<Row>())
{
// For each cell in the row...
foreach (Cell cell in row.Elements<Cell>())
{
// If the cell is using a shared string then merge the string
// from the source workbook into the merged workbook.
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
ProcessCellSharedString(mergedWorksheetPart, cell,
mergedSharedStringTablePart, childSharedStringTablePart);
}
}
}
}
}
}
//Save the changes to the merged workbook.
mergedWorkbookPart.Workbook.Save();
}
}
推荐答案
将使用XtraGrid的内置方法导出到excel，因为你说的更快。
I would use the built-in method from XtraGrid to export to excel as you say it's even faster.
生成两个excel文件后，我将使用Excel Interop程序集将两个生成的文件合并在相同文件中的两个分离的Excel表格中的相同文件中。
After having generated the two excel files I would use Excel Interop assemblies only to merge the two resulting files in the same file on two separated Excel Sheets in same workbook.
所以你的问题不会与XtraGrid不一样，只是如何合并两个文件在一个分离的工作表中，已经讨论了很多次，你会在线找到解决方案，例如：
so your problem would not be with XtraGrid anymore but simply with how to merge two files into one in separated worksheets, discussed many times already and you will find solutions online, for example here: How do I merge 2 Excel files into one excel file with separated sheets?
这篇关于XtraGrid - 导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！