Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,
I have a UltraWinGrid in my windows application.
my application user can modify the WinGrid (for example select columns that he want to see in grid and also change sequence of them) and then when he press 'Print' button the current WinGrid with current columns and sequence and data should send to a class as a parameter in DataTable type.

but the problem is that the sequence and number of columns in the DataSource does not match to current WinGrid that is shown on screen.
Posted

1 solution

Late response.... But might help if some one is looking for this.


/// <summary>
     /// Exports all the Visible Columns from an UltraGrid to Excel
     /// If you are looking at above C# 3.5 then refer Microsoft.CSharp Assembly in your project references
     /// </summary>
     /// <param name="Grid">UltraGrid</param>
     public static void ExportToExcel(this UltraGrid Grid)
     {
         if (Grid == null || Grid.Rows.Count == 0) return;

         var xlApp = new Excel.Application();
         try
         {
             System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;

             List<String> Columns = new List<string>();

             ///Setting the Current Culture...Can remove this
             Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

             Excel.Workbooks workbooks = xlApp.Workbooks;
             Excel.Range range;
             Excel.Workbook workbook = workbooks.Add();
             Excel.Worksheet worksheet = workbook.Worksheets[1];

             long totalCount = Grid.Rows.Count;
             long rowRead = 0;

             UltraGridColumn column = Grid.DisplayLayout.Bands[0].Columns[0];

             /// Get the next visible column by passing in VisibleRelation.Next.
             column = column.GetRelatedVisibleColumn(VisibleRelation.First);

             while (null != column)
             {
                 Columns.Add(column.Key);

                 /// Get the next visible column by passing in VisibleRelation.Next.
                 column = column.GetRelatedVisibleColumn(VisibleRelation.Next);
             }


             ///Create Columns Object and Select those Columns only
             for (var i = 0; i < Columns.Count; i++)
             {
                 UltraGridColumn Column = Grid.DisplayLayout.Bands[0].Columns[Columns[i]];

                 worksheet.Cells[1, i + 1] = Column.Header.Caption;
                 range = (Excel.Range)worksheet.Cells[1, i + 1];
                 range.Interior.ColorIndex = 20;
                 range.Font.Bold = true;
             }

             ///Now fill in the Rows to Excel
             for (var r = 0; r < Grid.Rows.Count; r++)
             {
                 for (var i = 0; i < Columns.Count; i++)
                 {
                     UltraGridColumn Column = Grid.DisplayLayout.Bands[0].Columns[Columns[i]];

                     if (Column.Format != null)
                     {
                         ///If you have defined Specific formats and would like them to be part of the excel
                         ///then you can customize as below
                         if (Column.Format.Equals("##,###") || Column.Format.Equals("#,##"))
                         {
                             worksheet.Cells[1, i + 1].EntireColumn.NumberFormat = "#,###;(#,###);-";
                             worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
                         }
                         else if (Column.Format.StartsWith("##,###.##") || Column.Format.Equals("#,##.##"))
                         {
                             worksheet.Cells[1, i + 1].EntireColumn.NumberFormat = "#,##0.00;(#,##0.00);-";
                             worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
                         }
                         else if (Column.Format.StartsWith("MM/dd/yyyy"))
                         {
                             worksheet.Cells[1, i + 1].EntireColumn.NumberFormat = "MM/dd/yyyy";
                             worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
                         }
                         else
                         {
                             worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
                         }

                     }
                     else
                     {
                         worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value.ToString();
                     }
                 }

                 rowRead++;
             }

             Microsoft.Office.Interop.Excel.Range columns = worksheet.UsedRange.Columns;
             columns.AutoFit();

             ///Display the Excel Component to User
             xlApp.Visible = true;
         }
         catch (Exception ex)
         {
             System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
             System.Windows.Forms.MessageBox.Show("Exception occured while exporting rows to Excel. Exception Details : " + ex.Message.ToString(), "Excel Export", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
         }
         finally
         {
             ///Force Clean up the Com Component to remove any traces
             GC.Collect();
             GC.WaitForPendingFinalizers();
             foreach (Excel.Workbook wb in xlApp.Workbooks)
             {
                 foreach (Excel.Worksheet ws in wb.Worksheets)
                 {
                     Marshal.FinalReleaseComObject(ws);
                 }
                 wb.Close(false, Type.Missing, Type.Missing);

                 Marshal.FinalReleaseComObject(wb);
             }
             xlApp.Quit();
             Marshal.FinalReleaseComObject(xlApp);

             System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
         }
     }
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900