Thanks for the response Mike.

I am already hiding some of the columns on the grid. Do I have to keep track of those columns to restore the grid to its previous state or there is an easier way to do that?

It would be nice in a future version of the grid to have the option to define the start and end source columns to export. I know that Office 2007 increased the limit to 16K but it would still be useful.

I've fixed a couple of issues with my previous code so I am posting the solution I got, in case someone else faces the same problem.

The main issue fixed is that with more than 256 columns the exported excel file didn't maintain the same order on the columns as the grid on the screen. That was because I was using column.Index instead of column.Header.VisiblePosition to the the column position on the grid.

Here is the code:

public void ExportToExcel( string worksheetName )
  {
   using( DataGridExportDlg exportDialog = new DataGridExportDlg() )
   {
    DialogResult result = exportDialog.ShowDialog();
    if( result == DialogResult.OK )
    {
     string fileName = exportDialog.FileName;

     UltraGridExcelExporter ultraGridExcelExporter = new UltraGridExcelExporter();

     Workbook workbook = new Workbook();
     int columnsCount = m_dataGrid.DisplayLayout.Bands[ 0 ].Columns.Count;
     if( columnsCount <= 256 )
     {
      Worksheet worksheet = workbook.Worksheets.Add( worksheetName );
      ultraGridExcelExporter.Export( m_dataGrid, worksheet );
     }
     else
     {
      int startColumnIndex = 0;
      int endColumnIndex = 255;
      int worksheetNumber = 1;

      ultraGridExcelExporter.BeginExport += delegate( object sender, BeginExportEventArgs e )
      {
       ColumnsCollection columns = e.Layout.Bands[ 0 ].Columns;

       foreach( UltraGridColumn column in columns )
       {
        int columnIndex = column.Header.VisiblePosition;
        if( (columnIndex > 0) && ((columnIndex < startColumnIndex) || (columnIndex > endColumnIndex)) )
        {
         column.Hidden = true;
        }
       }
      };

      while( startColumnIndex < columnsCount )
      {
       string worksheetNameWithNumber = worksheetName;
       if( worksheetNumber > 1 )
        worksheetNameWithNumber = String.Format( "{0}_{1}", worksheetName, worksheetNumber.ToString() );

       Worksheet worksheet = workbook.Worksheets.Add( worksheetNameWithNumber );

       ultraGridExcelExporter.Export( m_dataGrid, worksheet );
       startColumnIndex += 255;
       endColumnIndex += 255;
       worksheetNumber++;
      }
     }
     BIFF8Writer.WriteWorkbookToFile( workbook, fileName );

     OpenSpreadsheet( fileName );
    }
   }
  } 

Iam using VB in VS210 and the Infragistiscs4 version of the control in 11.2. There is no BeginExport only Export Started. I thought that I could do something like the following:

 If e.Layout.Bands(0).Columns.Count >= 256 Then
 
            Dim columnindex As Integer
            Dim startColumnIndex As Integer = 0
            Dim endColumnIndex As Integer = 256
            Dim worksheetNumber As Integer = 0
            Dim columnsCount As Integer
 
            Dim worksheetNameWithNumber As String = String.Empty
 
            columnsCount = e.Layout.Bands(0).Columns.Count
 
            While (startColumnIndex < columnsCount)
                columnindex = 0
                worksheetNameWithNumber = worksheetName
                If (worksheetNumber >= 1) Then worksheetNameWithNumber = String.Format("{0}_{1}", worksheetName, worksheetNumber.ToString())
 
                Dim oColumns As ColumnsCollection = e.Layout.Bands(0).Columns
                For Each oCol As UltraGridColumn In oColumns
                    columnindex = oCol.Header.VisiblePosition
                    'If ((columnindex > 0) And ((columnindex > startColumnIndex) Or (columnindex < endColumnIndex))) Then
                    If columnindex > startColumnIndex And columnindex < endColumnIndex Then
                        oCol.Hidden = False
                    Else
                        oCol.Hidden = True
                    End If
                Next
 
                worksheetNumber = worksheetNumber + 1
                Dim ws As Worksheet = e.CurrentWorksheet.Workbook.Worksheets.Add(worksheetNameWithNumber)
                e.CurrentWorksheet = ws
                startColumnIndex += 255
                endColumnIndex += 255

            End While          
        End If
  e.CurrentWorksheet.Workbook.Save(sfilename)

But while I get the grid to export to the file, all but the very last sheet int the workbook is empty.

Please help.

Hi,

I'm afraid I do not know what you are asking. Your last sentence appears to have been clipped or something.

Hi Mike

Thank you for your prompt follow up. The ultragrid methods seem to have been updated somewhat since this thread started. I am using VB.net in VS2010. I have Infragistics 11.2. and I am using the CLR4 version of the controls.

There is no BeginExport method. I am trying to export a grid that has approx 100 rows but typically > 256 columns in to Excel 2003. I tried using the Hide column feature to export into  > 1 spreadsheet is a single woorkbook but I cannot get it to work. All but the last spreadsheet is blank. Any suggests / examples please?

Thank you in advance for your help

BeginExport is now obsolete. It was replaced with ExportStarted.This was done in order to support asynchronous exporting.

skalyniuk said:

All but the last spreadsheet is blank. Any suggests / examples please?

I'm afraid I don't have any guesses as to why this is happening without seeing your code. Something you are doing in one of the event must be hiding the columns or something. It's impossible for me to guess.

Can you post a small sample project here which demonstrates the behavior so we can check it out?