Friday, December 10, 2010

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

19 comments:

  1. thanks great example

    ReplyDelete
  2. hi.. nice work..:D and how i can load all datas in a Dataset using that library if i could use a LINQ with that library..:D thk..:D

    ReplyDelete
  3. your solution is leading me to a Exception. The details are "System.IO.FileNotFoundException: Could not load file or assembly 'Lite.ExcelLibrary,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified."

    can you please help me out??
    My Email ID is amibush@gmail.com

    ReplyDelete
  4. Hi,
    In a click of a silverlight button event can I bring an excel sheet created in server into the browser to save locally.

    ReplyDelete
  5. Hi sheet.Cells[i, j].StringValue its only for string values.
    how can i do when on the left side its an int declared.
    i tried with convertions but it didnt worked.
    thank you

    ReplyDelete
  6. Muhammad if filestream fs.length is quite large like 500000 for exapmle it throws an beyond filestream exception in
    Woorkbook book=Woorkbook.open(fs);
    Do u have any advice?

    ReplyDelete
  7. Hi,
    Will it support Excel 2010?

    ReplyDelete
  8. its not working for .xlsx files.it gives outofmemory exception in file stream

    ReplyDelete
    Replies
    1. I have this same issue and solutions?

      Delete
  9. Sweet! Great work!
    As others has asked: Any chance it will support Excel 2010 any time soon?

    ReplyDelete
  10. Hi,
    Really very nice work...It is very useful
    If it supports 2010 Excel then it will be of great help
    Shall we expect ExcelLite to support Excel 2010 any time soon???

    ReplyDelete
  11. Hello Muhammad !
    Please show how I can use your library for drawing,
    for example drawing : Line, rectangle, circle or elips.

    ReplyDelete
  12. Those Who face following error:
    "System.IO.FileNotFoundException: Could not load file or assembly 'Lite.ExcelLibrary,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies.
    or
    "System.IO.FileNotFoundException: Could not load file or assembly 'Lite.Library,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies.

    Need to check their reference for :
    Lite.ExcelLibrary.dll
    Lite.Library.dll

    It will work once correct the reference for these files.

    ReplyDelete
    Replies
    1. Still getting a System.IO.FileNotFoundException error. Worked fine in a Silverlight app; trying to use it in a WPF app. .NET 4.5 and even changed the target to earlier versions with no luck.

      Both Lite.Library and Lite.ExcelLibrary are referenced in the project and have tried different combinations of using statements for Lite.Library, Lite.ExcelLibrary, and Lite.ExcelLibrary.SpreadSheet with the last obviously being required.

      Have even tried creating the filestream inside the Lite.ExcelLibrary.SpreadSheet.Workbook.Open method.

      Other suggestions?

      Delete
  13. Great Job!
    I'm working in VB and i'm having an "ArgumentOutOfRangeException" in the line:

    Dim book As Workbook = Workbook.Open(fs)

    please heeelp!!

    ReplyDelete
  14. it gives me error System.Out of memory Exception

    ReplyDelete
  15. Great Job,
    Exactly what I was looking for. Could you help me figure out how to hide columns and rows during export and read the same during import.

    Thanks,
    Damo...

    ReplyDelete
  16. Still getting a System.IO.FileNotFoundException error. Worked fine in a Silverlight app; trying to use it in a WPF app. .NET 4.5 and even changed the target to earlier versions with no luck.

    Both Lite.Library and Lite.ExcelLibrary are referenced in the project and have tried different combinations of using statements for Lite.Library, Lite.ExcelLibrary, and Lite.ExcelLibrary.SpreadSheet with the last obviously being required.

    Have even tried creating the filestream inside the Lite.ExcelLibrary.SpreadSheet.Workbook.Open method.

    Other suggestions?

    ReplyDelete