Friday, December 10, 2010

Exports Silverlight data to MS excel

Download LiteExcel from codeplex ( my open source silverlight library for manipulating MS excel without  COM interaction).
Add reference of “Lite.Library.dll” and “Lite.ExcelLibrary” to your Silverlight project.

Refere “Lite.ExcelLibrary.SpreadSheet” on the top of your Silverlight page.
using Lite.ExcelLibrary.SpreadSheet;
 
Writing an excel file
// open file dialog to select an export file.   
SaveFileDialog sDialog = new SaveFileDialog();
sDialog.Filter = "Excel Files(*.xls)|*.xls";
if (sDialog.ShowDialog() == true)
{
    // create an instance of excel workbook
     Workbook workbook = new Workbook();
     // create a worksheet object
     Worksheet worksheet = new Worksheet("Friends");
     // write data in worksheet cells
     worksheet.Cells[0, 0] = new Cell("Column1");
     worksheet.Cells[0, 1] = new Cell("Column2");
     worksheet.Cells[0, 2] = new Cell("Column3");
     worksheet.Cells[1, 0] = new Cell("string value");                    
     worksheet.Cells[1, 1] = new Cell(478574.5, "#,###.00");
     worksheet.Cells[1, 2] = new Cell(4);
     workbook.Worksheets.Add(worksheet);
     Stream sFile = sDialog.OpenFile();
     // save method needs a stream object to write an excel file. 
     workbook.Save(sFile);
}

Export Silverlight image to MS excel

In this post I will demonstrate you, how to export a Silverlight image in excel file using ExcelLite library
Download ExcelLite for silverlight from codeplex ( my open source library for manipulating excel without  COM interaction).
Add reference of “Lite.Library.dll” and “Lite.ExcelLibrary” to your Silverlight project

Add reference of both “Lite.Library”  and “Lite.ExcelLibrary.SpreadSheet” on the top of your page
using Lite.Library;
using Lite.ExcelLibrary.SpreadSheet;

Add a Silverlight page in your application and add two control , a button and an image control
Name your button control  “btnExport” and image control as “imgExport”
Set data source of the image control and add following code in the click handler of button control
 // open file dailog for selecting export file
 SaveFileDialog sDialog = new SaveFileDialog();
 sDialog.Filter = "Excel Files(*.xls)|*.xls";

 if (sDialog.ShowDialog() == true)
 {
 // create a workbook object
 Workbook workbook = new Workbook();
 //Create a worksheet object 
 Worksheet worksheet1 = new Worksheet("SheetWithImage");

 // create a spreadsheet picture object
 Lite.ExcelLibrary.SpreadSheet.Picture pic = new Lite.ExcelLibrary.SpreadSheet.Picture();
 //set its image property from silverlight image control
 // image formats 0xF01E=png,0xF01D=jpeg
 //ImageTranslator.TranslateImageToBytes translate an image control to byte array
 // that will be used by excel picture object to plot picture in excel file.
 pic.Image = new Lite.ExcelLibrary.SpreadSheet.Image(ImageTranslator.TranslateImageToBytes(this.imgExport), 0xF01E);
 //set picture size
 pic.TopLeftCorner = new CellAnchor(1, 1, 10, 10);
 pic.BottomRightCorner = new CellAnchor(8, 5, 10, 10);
 // add picture to spreadsheet
 worksheet1.AddPicture(pic);
 /// add worksheet to workbook
 workbook.Worksheets.Add(worksheet1);
 // get the stream of selected file
 Stream sFile = sDialog.OpenFile();
 // save excel file 
 workbook.Save(sFile);
 }