Friday, December 10, 2010

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);
 }



15 comments:

  1. Hi I'm Alvaro

    You are exporting the image in a fixed range of cells in Excel.

    In this lines (yours):
    pic.TopLeftCorner = new CellAnchor(1, 1, 10, 10);
    pic.BottomRightCorner = new CellAnchor(8, 5, 10, 10);
    they are a fixed range of cells.

    Could you please give some tips in order to export the Image but with its original size?

    Thanks in advance
    Alvaro Carpio

    ReplyDelete
  2. Alvaro,
    An excel cell is 65x20(width x height) pixels, you can calculate range of cells required according to your image size :).

    ReplyDelete
  3. Hi Muhammad, same days ago I wrote a post about this error:
    "WriteableBitmap has protected content. Pixel access is not allowed."
    This error appear when the app reference an image with external url for example http://www.site.com/images/img1.png the problem is that for security reason, silverlight cannot access to this file. So the solution to this problem it was put images in the same domain where is published the silverlight app (http://forums.silverlight.net/forums/p/202038/471690.aspx).
    Thanks and excelent work... i hope this help somebody.
    Daniel.

    ReplyDelete
  4. Hi,

    I was trying to export 2 pictures and excel cannot open the resulting file. I am positioning them one after another with a blank row.

    Can you help,

    Thanks,

    Val

    ReplyDelete
  5. Hi,

    I just tried to use your library and there is one thing which is not working: If I write a value which contains one or more newline characters, this will not be displayed correctly in Excel.
    Is there a way to set the 'Wrap Text' property with your library?
    Many thanks,
    David

    ReplyDelete
  6. I placed code for adding image to worksheet into loop with 100 iterations and get next error:
    Specified argument was out of the range of valid values.
    Parameter name: SATSectorIndex

    ReplyDelete
  7. I have the same probleme mentionned below, how can I add 2 image in the same worksheet? When I try, the excel file told me "cannot open file".

    Thanks.
    Cube

    ReplyDelete
  8. Dear Abubakar Dar, Happy New Year.
    I use your library. It's so good. Thank you. But I have one problem: I can read Cyrillic chars from xls. Please, help me.
    With best regards, Julia

    ReplyDelete
  9. salam I'm Elham,
    please i have a datagrid of value and picture i don't know how i could mixed the 2 exampl of you proposition MR Muhammad Abubakar
    thank you

    ReplyDelete
  10. Hi,
    How to apply back color to the cells. if you give some sample code it would be great.

    Thanks

    ReplyDelete
  11. I am getting an error from .Net telling me that windows has flagged the Lite.Library as a web download and i cannot include it in my project. I tried to unblock the dll but there is no option to do so on the file properties window. Any idea why this would happen?

    ReplyDelete
  12. I you add more than one picture file gets corrupted

    ReplyDelete
  13. Yeap. Impossible to add more than one image. Can you fix that?

    ReplyDelete