C1Dtagrid Excel Export

C1Dtagrid Excel Export

Posted by: sebastian-jn.k on 23 September 2022, 11:51 am EST

• 

  sebastian-jn.k

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 23 September 2022, 11:51 am EST - Updated 4 October 2022, 8:26 am EST

  C1.WPF.DataGrid.Excel.4.5.2 4.5.20222.768

  hello,
  
  i have a c1datagrid with several text,numeric and date columns.
  
  Each column is a custom column of c1- equivalent with overrided getColumText method for printing purpose.
  
  Also a three row column header with multiple merged rows/cols.
  
  Excel extention and advanced filter behavior are also active.

  Problem:
  
  If i save the datagrid over the extension method .Save(…)
  
  only the default header is exported three times not the custom contet that i defined over the custom DateTime/Text/Numeric column GetColumnText() method.
  
  Normally i expect that the datagrid.Save() method calls the GetColumnText() but it doesn’t do that.

  to solve this problem is set the datagrid.DataGridHeadersVisibility to DataGridHeadersVisibility.All befor i call the datagrid.Save(…) method.
  
  The GetColumnText() is called and the exported default header looks as expected.
  
  After the datagrid.Save(…) method i set the datagrid.DataGridHeadersVisibility to DataGridHeadersVisibility.Row.

  Question:
  
  How do i get rid off the top three header rows for the export?

  If if foreach through the top rows and set the DataGridColumnHeaderRow.Visibility = Visibility.Collapsed nothing happened. the top rows are still exported.
  
  greetings from germany.
  
  

  

  1452×648 10.2 KB

  

  1814×648 31.9 KB

  

  1152×648 32.6 KB

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 26 September 2022, 5:37 am EST

  Hi,

  Thanks for providing the snapshots.
  
  JFYI, DataGrid.Save extension doesn’t copies the merge range. If you want to achieve your requirement, then you need to create sheet from C1Excel with Merge ranges as:(see code snippet)

  
   C1XLBook xlBook = new C1XLBook();
              var sheet = xlBook.Sheets[0];
              for(int i=0; i < grid.Rows.Count; i++)
              {
                  for(int j=0;j<grid.Columns.Count;j++)
                  {
                      var cell = grid.GetCell(i,j);
                      sheet[i, j].Value = cell.Value;
                  }
              }
  
              //Merge Excel cells
              foreach(var range in MergeRanges)
              {
                  sheet.MergedCells.Add(new XLCellRange(range.RowFrom, range.RowTo, range.ColumnFrom, range.ColumnTo));
              }
  
  
              //Set TopRow Header Styles  
              XLStyle _headerStyle;
              _headerStyle = new XLStyle(xlBook);
              _headerStyle.BackColor = Colors.LightGray;
              for (int i=0;i<grid.TopRows.Count;i++)
              {
                  for (int j = 0; j < grid.Columns.Count; j++)
                  {
                      sheet.GetCell(i,j).Style = _headerStyle;
                  }
              }
  
              //Save DataGrid as Excel
              xlBook.Save("DataGridExported.xlsx", C1.WPF.Excel.FileFormat.OpenXml);
  
  

  Please refer the attached sample for the same : DataGridExcelExport.zip

  Best Regards,
  
  Nitin