WinForms Data Grid - Copy selected cell values to the clipboard in BIFF8 format using Excel Export API
This example demonstrates how to use the DevExpress Excel Export API to export the values in selected grid cells in BIFF8 format and copy them to the clipboard. This allows you to paste clipboard data to an MS Excel document maintaining appearance and formatting settings (text alignment, borders, background color, and font settings).
See the implementation of the CopyToClipboardBIFF8Helper
class for details:
- Create an
IXlDocument
document and populate it with columns and rows. The Excel Export API writes a document directly to the stream. Note that you should first add all the columns to the sheet and then add the rows.MemoryStream CreateBIFF8DataStream() { IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xls); MemoryStream dataStream = new MemoryStream(); using(IXlDocument document = exporter.CreateDocument(dataStream)) { using(IXlSheet sheet = document.CreateSheet()) { ExportColumns(sheet); ExportRows(sheet); this.sheetName = sheet.Name; this.dataRange = sheet.DataRange; } } dataStream.Position = 0; return dataStream; }
- When the BIFF8 stream is ready, add the path to the data in the workbook to the clipboard. The
CreateLinkDataStream
method creates a memory stream.MemoryStream CreateLinkDataStream() { string link = string.Format("Excel\0[Book1]{0}\0{1}:{2}\0\0", sheetName, GetR1C1(this.dataRange.TopLeft), GetR1C1(this.dataRange.BottomRight)); byte[] linkData = DXEncoding.Default.GetBytes(link); return new MemoryStream(linkData); }
- Handle the
GridControl.ProcessGridKey
event to copy selected grid cells to the clipboard in BIFF8 format:void gridControl1_ProcessGridKey(object sender, KeyEventArgs e) { if(e.Control && e.KeyCode == Keys.C) { _copyToClipboardBIFF8Helper.CopySelectionToClipboard(gridView1); e.Handled = true; } }
(you will be redirected to DevExpress.com to submit your response)