Friday, December 10, 2010

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

24 comments:

  1. thnak you for posting this great silverlight application. I have encountered the following error when running the 'reading excel files' example project. I'm using SL4, VS2010 and IE8.
    the project compiles within SL w/o errors, but generates the following error when run. any help would be really appreciated! thanks again


    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.2; .NET4.0C; .NET4.0E)
    Timestamp: Fri, 28 Jan 2011 21:28:28 UTC


    Message: Unhandled Error in Silverlight Application invalid size of rich text formatting information at Lite.ExcelLibrary.BinaryFileFormat.StringDecoder.ReadString(Int32 lengthbits, RichTextFormat& rtf)
    at Lite.ExcelLibrary.BinaryFileFormat.SST.Decode()
    at Lite.ExcelLibrary.BinaryFileFormat.WorkbookDecoder.DecodeRecords(List`1 records, SharedResource& sharedResource)
    at Lite.ExcelLibrary.BinaryFileFormat.WorkbookDecoder.Decode(Stream stream)
    at Lite.ExcelLibrary.SpreadSheet.Workbook.Open(FileStream stream)
    at ReadingExcelSample.MainPage.btnSelectFile_Click(Object sender, RoutedEventArgs e)
    at System.Windows.Controls.Primitives.ButtonBase.OnClick()
    at System.Windows.Controls.Button.OnClick()
    at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
    at System.Windows.Controls.Control.OnMouseLeftButtonUp(Control ctrl, EventArgs e)
    at MS.Internal.JoltHelper.FireEvent(IntPtr unmanagedObj, IntPtr unmanagedObjArgs, Int32 argsTypeIndex, Int32 actualArgsTypeIndex, String eventName)
    Line: 1
    Char: 1
    Code: 0
    URI: http://localhost:59511/Silverlight.js


    Message: Unhandled Error in Silverlight Application invalid size of rich text formatting information at Lite.ExcelLibrary.BinaryFileFormat.StringDecoder.ReadString(Int32 lengthbits, RichTextFormat& rtf)
    at Lite.ExcelLibrary.BinaryFileFormat.SST.Decode()
    at Lite.ExcelLibrary.BinaryFileFormat.WorkbookDecoder.DecodeRecords(List`1 records, SharedResource& sharedResource)
    at Lite.ExcelLibrary.BinaryFileFormat.WorkbookDecoder.Decode(Stream stream)
    at Lite.ExcelLibrary.SpreadSheet.Workbook.Open(FileStream stream)
    at ReadingExcelSample.MainPage.btnSelectFile_Click(Object sender, RoutedEventArgs e)
    at System.Windows.Controls.Primitives.ButtonBase.OnClick()
    at System.Windows.Controls.Button.OnClick()
    at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
    at System.Windows.Controls.Control.OnMouseLeftButtonUp(Control ctrl, EventArgs e)
    at MS.Internal.JoltHelper.FireEvent(IntPtr unmanagedObj, IntPtr unmanagedObjArgs, Int32 argsTypeIndex, Int32 actualArgsTypeIndex, String eventName)
    Line: 1
    Char: 1
    Code: 0
    URI: http://localhost:59511/Silverlight.js

    ReplyDelete
  2. @Anonymous
    Which version of MS Office you are using?
    Also please send me the data file (MS Excel) file you are trying to read , i will try to reproduce and fix if prompted.

    abubkr.dar@gmail.com

    Thanks

    ReplyDelete
  3. Question: I would like this DLL to be outside my Main xap file. Do you "ASSEMBLY".extmap.xml mapping file?

    ReplyDelete
  4. Do you have any samples of how to format text and manipulate Size

    ReplyDelete
  5. Thanks for your sharing this library.
    But I found that it don't support Unicode character and I modified method public static String GetStringUnicode(byte[] value) in ConversionHelper.cs
    return new System.Text.UnicodeEncoding().GetString(value, 0, value.Length);

    ReplyDelete
  6. 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
  7. Hi all,
    is it possible to merge cells (colspan)?

    thx
    Claude

    ReplyDelete
  8. Hi, I was checking your own example, then I export to Excel and open the file, but when I try to print from inside the sheet (once exported) it throws me a message like "not found data to print", this happens in the example of export an image, I tried this example in different PC's, but it shows the same error.
    Is it a known issue? do you have the solution?
    Thanks in advance

    ReplyDelete
  9. The same problem is with me, I can not print.
    Hopefully there is a solution to print the exported excel files.

    Thanks

    ReplyDelete
  10. When I write a date column to excel it adds 1 day to the date.

    Can you point me to more documentation about CellFormat?

    ReplyDelete
    Replies
    1. It looks like perhaps there's a bug in Lite.ExcelLibrary.BinaryFileFormat.SharedResource.EncodeDateTime. It's calculating the number of days between your date and the base date (which is set to 1/1/1904 in WorkbookEncoder.EncodeWorkbook). If your date is more than 365 days after that, then EncodeDateTime adds one day.

      As the author states, ExcelList is based on excellibrary (http://code.google.com/p/excellibrary/). The original author made an interesting change on Feb 24 2009 (See http://code.google.com/p/excellibrary/source/detail?r=37 and http://www.cpearson.com/excel/datetime.htm).

      So it seems to me that our choices are to either (A) modify the source of ExcelLite accordingly or (B) just send it days that are one less than what we really want (as long as they're after 1/1/1905).

      Delete
  11. Hi,
    Good example and as mentioned it is working fine for 97-2003 excel but i need to import 2010 excel.Could u please help

    ReplyDelete
  12. Hello!!! are you alive?????

    ReplyDelete
  13. Hi,

    I have the same problem ! When I export a data on a XLS File all is fine but when i want to print it, with excel 2007, I have an error message 'not found data to print' !
    Have you find a solution ?
    Thanks a lot !

    Nicolas

    ReplyDelete
  14. Hi,
    This looks like a very promising, easy to use tool. Is it possible to set the column width?

    ReplyDelete
  15. Hi Muhammad,

    The library is really awesome. Thanks for the great thing to export data in excel.

    Could you please let me know how to bold, color or set fontface for a cell or row???

    Thanks,
    Kashmir Singh

    ReplyDelete
  16. Where I can get the reference for
    System.Windows.Controls.Tookit, System.Windows.Controls.Theming.Tookit, System.Windows.Controls.Theming.BureauBlue
    from your code.

    ReplyDelete
  17. Hi Muhammad,
    is it possible to set cell format as "Currency" to diplay Currency symbol in saved Excel File cells.
    also Performed Sum of columns or rows like excel functionality in saved Excel File.

    ReplyDelete
  18. Intersting and beautiful blog lovely presentation thanks for sharing your views...microsoft excel support|Excel Support|microsoft excel 2010

    ReplyDelete
  19. Do you have any procedures/methodologies in place for when exporting data from a Silverlight datagrid to Excel to convert:
    System.Windows.Style
    to:
    Lite.ExcelLibrary.Spreadsheet.CellStyle

    ReplyDelete
  20. Hello Muhammad,

    I tried to Import an Excel with Arabic Text, it is reading the Arabic text as Junk Value, can you please help me on how to read Arabic Text using Excel Lite.

    ReplyDelete
  21. Hello Muhammad,

    Life saver! 3 Cheers.

    ReplyDelete
  22. Hi,

    I tried to Import an Excel with Arabic Text, it is reading the Arabic text as Junk Value, can you please help me on how to read Arabic Text using Excel Lite.

    ReplyDelete
  23. Hi,

    I tried to open a XLS , which cells are formatted, and save the XLS using your Lib, the format are gone, how to retain the format ?

    Here is the code.
    Worksheet worksheet = null;
    void exportExcel_Click(object sender, RoutedEventArgs e)
    {

    SaveFileDialog sDialog = new SaveFileDialog();

    sDialog.Filter = "Excel Files(*.xls)|*.xls";
    if (sDialog.ShowDialog() == true)
    {

    using (var stream = sDialog.OpenFile())
    {
    book.Save(stream);

    }
    }

    }
    Workbook book;
    private void import_Click(object sender, RoutedEventArgs e)
    {
    OpenFileDialog openFileDialog = new OpenFileDialog();
    openFileDialog.Filter = "Excel (*.xls)|*.xls";
    if (openFileDialog.ShowDialog() == true)
    {
    try
    {
    FileStream fs = openFileDialog.File.OpenRead();
    book = Workbook.Open(fs);
    worksheet = book.Worksheets[1];
    }
    catch(Exception ex)
    {

    }
    }

    }

    ReplyDelete