Hi Team,
I have an issue in export to excel with group by from ultragrid. I did the following steps, after that Collapse (+)/ Expand (-) missing.
First Time Export: Export with Grouping
Second Time: Export without Grouping
Third Time: Export To Excel With Group by ( Collapse (-)/ Expand (-)) missing
Export to Excel Code:
private void(object sender, EventArgs e) { gridResults.Visible = false; gridResults.Enabled = false; gridResults.DataSource = null; LoadAllChildData(); gridResults.DataSource = dsKPI; }
private void newExcelExportToolStripMenuItem_Click(object sender, EventArgs e) { ExportToExcelForm etef = new ExportToExcelForm(excelExporter, filterRowCheck(), gridResults.Rows[0].HasExpansionIndicator); if (etef.ShowDialog() == DialogResult.OK) { gridResults.InitializeRow -= gridResults_InitializeRow; gridResults.BeforeRowExpanded -= gridResults_BeforeRowExpanded; gridResults.BeforeRowCollapsed -= gridResults_BeforeRowCollapsed;
ExportCheckedRowsOnly = false; ExcelExportFormat = etef.format; ExcelExportSuccesful = true;
foreach (UltraGridRow gridRow in gridResults.Rows) gridRow.Appearance.BackColor = Color.White;
Infragistics.Win.UltraWinGrid.AsynchronousExportManager.AsynchronousExportDuration = 1000; Infragistics.Win.UltraWinGrid.AsynchronousExportManager.AsynchronousExportInterval = 10;
string maxRows = "65,535"; string maxCols = "256";
gridResults.Rows.CollapseAll(true);
// If the grouping on is checked on the main form then expand everything. (SM:2-5-2019) if (etef.groupingOn == true) { gridResults.Rows.ExpandAll(true); LoadAllChildData(); }
//gridResults.UpdateData();
string extension = ".xls";
if (ExcelExportFormat == Infragistics.Documents.Excel.WorkbookFormat.Excel2007) { extension = ".xlsx"; maxRows = "1,048,576"; maxCols = "16,384"; }
dlgSaveAs.FileName = ""; Regex re = new Regex("[^A-Za-z0-9_]");
string selectedTemplate = GetCurrentTemplateName(); ; if ((selectedTemplate == "No Template") || (selectedTemplate == "No Existing Templates")) { dlgSaveAs.FileName = re.Replace(DataObject.DataObjectName.Replace(" ", "_"), ""); } else { dlgSaveAs.FileName = re.Replace(selectedTemplate.Replace(" ", "_"), ""); }
dlgSaveAs.FileName += "_" + String.Format("{0:yyyyMMddHHmmss}", DateTime.Now); dlgSaveAs.Filter = "Microsoft Excel|*" + extension;
isExcelExportToEmail = false;
if (dlgSaveAs.ShowDialog() == DialogResult.OK) { Application.DoEvents(); excelExporter.ExportAsync(gridResults, dlgSaveAs.FileName); gridResults.Rows.CollapseAll(true); gridResults.InitializeRow += gridResults_InitializeRow; gridResults.BeforeRowExpanded += gridResults_BeforeRowExpanded; gridResults.BeforeRowCollapsed += gridResults_BeforeRowCollapsed; } } }
private void excelCheckExporter_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e) { if (!ExportAllDrilldowns) e.Layout.Bands[0].Override.HeaderPlacement = HeaderPlacement.FixedOnTop; else { e.Layout.Bands[0].Override.HeaderPlacement = HeaderPlacement.OncePerRowIsland; e.Layout.Rows.CollapseAll(true); e.Layout.ViewStyle = Infragistics.Win.UltraWinGrid.ViewStyle.MultiBand; }
foreach (var band in e.Layout.Bands) { band.Indentation = 5;
if (band.Columns.Exists("FirstColumnCheckBox")) { band.Columns["FirstColumnCheckBox"].Hidden = true; band.Columns["FirstColumnCheckBox"].Header.VisiblePosition = band.Columns.Count; } } }
private void excelExporter_InitializeRow(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.ExcelExportInitializeRowEventArgs e) {
if (e.Row.IsGroupByRow) { //if don't export groups if (excelExporter.OutliningStyle == Infragistics.Win.UltraWinGrid.ExcelExport.OutliningStyle.None) { e.SkipRow = true; } if (!e.Row.IsExpanded) { e.SkipRow = true; }
} else { if (!ExportAllDrilldowns) { if (!e.Row.IsExpanded) { e.SkipDescendants = true; } } } }
private void excelExporter_HeaderRowExporting(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.HeaderRowExportingEventArgs e) { e.Band.Indentation = 0;
if (e.HeaderType == Infragistics.Win.UltraWinGrid.ExcelExport.HeaderTypes.ColumnHeader) { return; }
if (!ExportAllDrilldowns) {
//if it's NOT expanded and it's NOT the first if (!e.GridRow.IsExpanded && e.CurrentRowIndex != 0) { //if it has a previous sibling if (e.GridRow.HasPrevSibling()) { //if that previous sibling is expanded if (e.GridRow.GetSibling(SiblingRow.Previous).IsExpanded) { //create new header return; } } //else if it has no previou sibling, and has a parent row, then it is the first in a child band else { if (e.GridRow.ParentRow != null) { return; } } //Otherwise, skip it e.Cancel = true; } } }
private void excelExporter_InitializeColumn(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.InitializeColumnEventArgs e) { if (e.Column.DataType == typeof(DateTime)) { if (e.Column.Format != null) { string excelFormat = e.Column.Format; if (excelFormat.Contains("tt")) excelFormat = excelFormat.Replace("tt", "AM/PM"); //if (excelFormat.Contains("\"")) //excelFormat = excelFormat.Replace("\"", ""); excelFormat = "[$-409]" + excelFormat + ";@";
e.ExcelFormatStr = excelFormat; } }
else { if (e.Column.Format != null) { e.ExcelFormatStr = e.Column.Format; } }
}
private void excelExporter_ExportEnding(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.ExportEndingEventArgs e) { foreach (var row in e.CurrentWorksheet.Rows) { foreach (var cell in row.Cells) { cell.CellFormat.VerticalAlignment = Infragistics.Documents.Excel.VerticalCellAlignment.Center; } } }
private void excelExporter_ExportEnded(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.ExportEndedEventArgs e) { if (e.Canceled) return; if (ExcelExportSuccesful) { if (ExportAllDrilldowns) { foreach (UltraGridRow row in gridResults.Rows) { if (!ExpandedIndexes.Contains(row.Index)) row.Expanded = false; } }
Application.DoEvents();
if (isExcelExportToEmail) { MemoryStream stream = new MemoryStream();
if (ExcelExportFormat == Infragistics.Documents.Excel.WorkbookFormat.Excel2007) { extension = ".xlsx"; }
FileStream streamReadFile = File.OpenRead(ExcelExportTempPath); streamReadFile.CopyTo(stream); streamReadFile.Close();
File.Delete(ExcelExportTempPath); stream.Seek(0, SeekOrigin.Begin);
frmEmailReport frmEmailReport = new frmEmailReport(this, stream, DataObject.DataObjectName, extension); frmEmailReport.ShowDialog();
} else { if (ShowMessage("Export Complete! Would you like to open the exported file?", "Open File?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { System.Diagnostics.Process.Start(dlgSaveAs.FileName); }
} }
private void excelExporter_AsynchronousExportError(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.AsynchronousExportErrorEventArgs e) { ExcelExportSuccesful = false; }
Hello MLDav,
I have been investigating into the behavior you are seeing with the code that you have provided, but unfortunately, there is a lot of missing logic in the code that you have provided. I have picked out the pieces that I can use, but in doing so, I cannot seem to reproduce the behavior you are seeing. Each time I am exporting the UltraGrid, it looks the same as in the Windows Forms application.
I am attaching the sample project that I used to test this. My tests were made using specific version 19.2.20192.302 in Infragistics for Windows Forms 2019 Volume 2. If this version differs from the version you are using, please let me know.
It would be very helpful if you can either modify the attached sample project such that it reproduces the behavior you are seeing. Alternatively, if you have an isolated sample project that you can attach here that reproduces the behavior, I can take a look at that as well.
Please let me know if you have any other questions or concerns on this matter.
UltraGridExcelExportIssue.zip
Hi Andrew,
Thank you for response. I am able to export into excel. but few issues are there which I need to fix.
1. Extra Row Added between Group Grids Which I need to take off
2. Formatting is not all working when I export.
ExcelExporter.ExportFormattingOptions = Infragistics.Win.UltraWinGrid.ExcelExport.ExportFormattingOptions.All;
Hi Andrew, How can I set 2. Modify Excel’s Error Handling: from c# code without manually modifying the excel after export.
The error handling in Excel is an application-level setting in Excel, whereas the Infragistics Excel Library handles things specific to the Workbook and the Worksheets to be loaded into Excel. There does not exist a way to change the error handling in Excel using the Infragistics Excel Library.
With the above said, setting this error handling once in Excel appears to apply it for all Workbooks opened with your Excel application until the decision is made to turn that error handling back on – assuming that decision is ever made. The error handling is not Workbook-specific.
I need to export the same settings and appearance of the Ultra grid to excel. how to achieve that.
Can you please be a little more specific on what settings or appearances are not currently being exported from the UltraGrid to Excel on your end? What settings have you set that you would like to see exported to Excel that currently aren’t being exported?
-> I need to perform Export to Excel in asynchronous way with Drilldowns.-> I have drilldown which will be loaded dynamically more than 60000 thousand rows asynchronous way first and Export all the Rows in asynchronous way second.
Please share the solution code.