Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 13-Feb-12 22:37pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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;
            }
        }
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 8,149
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 29 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100