How To: Export images from the XamDataGrid to Excel Worksheet

John Doe / Wednesday, July 28, 2010

The DataPresenterExcelExporter component is used to export the values of the cells into an Excel document. If you have an Image column in your XamDataGrid, the path of the Image will be exported rather than the image itself.

One way to export the actual image rather than its path is to handle the cell exporting events of the Excel exporter and add the image in the Worksheet. The Image in the Worksheet is represented as a WorkSheetImage object inserted in the WorkSheet.Shapes collection. As Excel cells do not support image content, the actual image has to be positioned over them. This is achievable by setting the TopLeftCornerCell and BottomRightCornerCell properties of the WorkSheetImage.

Sample Setup

1. Create a bind a XamDataGrid with an Image column (sample data source from Blend 4 - included in the sample):

      <Grid x:Name="LayoutRoot" DataContext="{Binding Source={StaticResource SampleDataSource}}">

 		<Grid.RowDefinitions>

 			<RowDefinition Height="0.8*"/>

 			<RowDefinition Height="0.1*"/>

 		</Grid.RowDefinitions>

         <igDP:XamDataGrid Name="xamDataGrid1" DataContext="{Binding}" DataSource="{Binding Collection}">

             <igDP:XamDataGrid.FieldSettings>

                 <igDP:FieldSettings CellHeight="60" CellWidth="60"/>

             </igDP:XamDataGrid.FieldSettings>

         </igDP:XamDataGrid>

         <Button Content="Export" Grid.Row="1" Click="Button_Click" />

     </Grid>

2. Export the XamDataGrid

         private  void  Button_Click(object  sender, RoutedEventArgs  e)

             DataPresenterExcelExporter  exporter = new  DataPresenterExcelExporter ();

             exporter.CellExported += new  EventHandler <CellExportedEventArgs >(exporter_CellExported);

             exporter.Export(this .xamDataGrid1, file.FullName, WorkbookFormat .Excel2007);

3. Export the Images from the Image Column :

         private  void  exporter_CellExported(object  sender, CellExportedEventArgs  e)

             if  (e.Field.DataType == typeof (ImageSource ))

                 // currently exported cell

                 WorksheetCell  cell = e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex];

                 WorksheetCell  diagonalCell = e.CurrentWorksheet.Rows[e.CurrentRowIndex + 1].Cells[e.CurrentColumnIndex + 1];

                 // current worksheet

                 Worksheet  sheet = e.CurrentWorksheet;

                 // 1. Create Image

                 // resolve your path image

                 string  imagePath = ResolveImagePathFromCellValue(e.Value);

                 System.Drawing.Image  image = new  System.Drawing.Bitmap (imagePath);

                 WorksheetImage  workImage = new  WorksheetImage ((image));

                 // 2. Position the Image

                 workImage.TopLeftCornerCell = cell;

                 workImage.BottomRightCornerCell = diagonalCell;

                 // 3. Insert Image

                 sheet.Shapes.Add(workImage);

                 cell.Value = null ;

The ResolveImagePathFromCellValue() method depends on the way you have setup the image field in the XamDataGrid - via local path, embedded resource, stream, etc.

Result

Full sample you can find here :