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



Import excel data in Silverlight with ExcelLite

In this sample we will read an excel file using ExcelLite library and display file contents in a Textbox control. Column values will be separated by COMMA and rows will be separated by NEW LINE.
Add a button to open file dialog for selecting excel file.  To display file contents add a textbox control.
Add reference to both of the library DLLS, refer “Lite.ExcelLibrary.SpreadSheet” on the top of your page
using Lite.ExcelLibrary.SpreadSheet;

Write the following code in the click event of your button. Code is commented heavily  for demonstration.
 //To access file system we need to use Silverlight file dialogs
  OpenFileDialog oFile = new OpenFileDialog();
 // .xls filter specified to select only .xls file.
 oFile.Filter = "Excel (*.xls)|*.xls";
 if (oFile.ShowDialog() == true)
 {
 // Get the stream of the selected file
 FileStream fs = oFile.File.OpenRead();
 // Simply call the Open method of Workbook and you are done
 Workbook book = Workbook.Open(fs);
 // All of the worksheet will be populated with data 
 // currently we will read only first for this sample
 Worksheet sheet = book.Worksheets[0];
 /// itrating through worksheet object to get values
 /// Worksheet.Cells.FirstRowIndex tells the First row index of data
 /// Worksheet.Cells.LastRowIndex tells the last of data
 /// Worksheet.Cells.FirstColIndex has first index of column value
 /// Worksheet.Cells.LastColIndex has last index of column
 /// So itrating using these properties will traverse all data of the sheet
 /// 
 for (int i = sheet.Cells.FirstRowIndex; i < sheet.Cells.LastRowIndex; i++)
 {
         for (int j = sheet.Cells.FirstColIndex; j < sheet.Cells.LastColIndex; j++)
        {
        /// value of each cell is separated by a coma 
        this.textBox1.Text += sheet.Cells[i, j].StringValue;
        this.textBox1.Text += ",";
        }
 // New row will be displayed on next line
 this.textBox1.Text += Environment.NewLine;
 }
 }

ExcelLite a Silverlight Library for MS Excel

I have posted ExcelLite an open source C# library for Silverlight applications for manipulating MS excel without COM interaction or OLEDB driver.You can manipulate MS Excel (97-2003) files totally in silverlight client as this library using Binary excel format to read and write data.
So you don’t need to force your client to install Silverlight application “out of browser” as well as no COM interaction required with office DDLS.

Download ExcelLite from codeplex.


Following is the series of posts that describing various features of the library with sample applications.

Exports Silverlight data to MS excel
        Example code and description for writing excel files with
ExcelLite
Imports excel data in Silverlight application.
        Example dode and description for reading excel files into Silverlight with
ExcelLite
Export Silverlight image to excel file
        Example , how to export silverlight image to an excel file with ExcelLite