locked
parsing Excel (xlsx) spreadsheet in silverlight RRS feed

  • Question

  • Hi,

      I would like to use a openFileDialoge to upload an excel Spreadsheet to be parsed.  Does anyone know of any good tool available in silverlight to do this?  I've been looking at GemBox( I think this will work), but I would be restricted to 150 rows in the free version.  Does this look like my only option? 

        Thanks for any feedback.

    -Ray

    Wednesday, July 28, 2010 12:37 PM

Answers

All replies

  • Hi Ray,

    You can upload spreadsheet to server and parse it with ADO.NET. 

    Wednesday, July 28, 2010 12:40 PM
  • Yeah, I would like to parse it on the client side if possible.   Dang, it actually looks like GemBox won't work with silverlight on the client.  Perhaps it is not possible to parse xlsx SL client side.

    Wednesday, July 28, 2010 12:53 PM
  •  I haven't seen any libraries for do that at Silverlight application.

    You can do it in Trusted Application mode via COM object (Silverlight 4).

    Wednesday, July 28, 2010 2:44 PM
  • What would be the best way to Send the file to the Server?  I tried opening the raw spread sheet, reading it into a string, sending that string to the server(via my wcf web service), and then parsing that file with my libraries( I tried 2: gembox and open XML sdk). both give me that error that the file has been corrupted.   The Libraries require a stream, so on the server I convert the string to a memory stream tried reading it in and that is when I'm getting the error.

      If I could get the following code working I think the server version would work:

            private void Button_Click(object sender, RoutedEventArgs e)
            {
                string _fileContent = string.Empty;
                string _fileExtension = ".xlsx";
    
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Sources (*" + _fileExtension + ")|*" + _fileExtension;
    
                if ((bool)ofd.ShowDialog())
                {
                    ss(ofd.OpenFiles());
                    StreamReader reader = new StreamReader(ofd.OpenFile());
                    _fileContent = reader.ReadToEnd();
    
                    
                }
                
                // on server
                Stream stream = new MemoryStream(ASCIIEncoding.Default.GetBytes(_fileContent));
    
                List<string> dd = new List<string>();
                ExcelFile ef = new ExcelFile();
                ef.LoadXlsx(stream, XlsxOptions.None);   //   I GET FILE CORRUPT ERROR HERE  :(



    Friday, July 30, 2010 4:18 PM
  • What would be the best way to Send the file to the Server?
     

    Well there is no the best way. You can use WCF/Web service, ASP.NET Handler to upload file to server. For example look http://www.c-sharpcorner.com/UploadFile/nipuntomar/FileUploadsilverlight03182009030537AM/FileUploadsilverlight.aspx.

    Also you can use usual ADO.NET to load data from xslt file(sample Read Data From an Excel File (.xslt) in ASP.NET).

     

    Friday, July 30, 2010 5:08 PM
  • Thanks,  The below code is what I needed to do to get my solution working.  Hope post helps others.  Thanks again.

    private void Button_Click(object sender, RoutedEventArgs e)
            {
                string _fileExtension = ".xlsx";
                byte[] buffer = null;
    
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Sources (*" + _fileExtension + ")|*" + _fileExtension;
    
                if ((bool)ofd.ShowDialog())
                {                                
                    ss(ofd.OpenFiles());
    
                    Stream rStream = ofd.OpenFile();
                    buffer = new byte[rStream.Length];
                    rStream.Read(buffer, 0, buffer.Length);
    
    
    
                }
                
                // SEND BUFFER TO SERVER HERE:
    
                // On server:
                Stream stream = new MemoryStream(buffer);
    
                List<string> dd = new List<string>();
                ExcelFile ef = new ExcelFile();
                ef.LoadXlsx(stream, XlsxOptions.None);
    
                string cellString = ef.Worksheets[1].Rows[4].Cells[1].Value.ToString();








    private void Button_Click(object sender, RoutedEventArgs e)
            {
                string _fileContent = string.Empty;
                string _fileExtension = ".xlsx";
                byte[] buffer = null;

                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Sources (*" + _fileExtension + ")|*" + _fileExtension;

                if ((bool)ofd.ShowDialog())
                {                                
                    ss(ofd.OpenFiles());

                    Stream rStream = ofd.OpenFile();
                    buffer = new byte[rStream.Length];
                    rStream.Read(buffer, 0, buffer.Length);



                    // Open a SpreadsheetDocument for read-only access based on a stream.
                    //using (SpreadsheetDocument spreadsheetDocument =
                    //    SpreadsheetDocument.Open(ofd.OpenFile(), false))
                    //{
                    //    List<string> dd = new List<string>();

                    //    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                    //    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                    //    foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
                    //    {
                    //        foreach (Row r in sheetData.Elements<Row>())
                    //        {
                    //            string text = string.Empty;
                    //            foreach (Cell c in r.Elements<Cell>())
                    //            {
                    //                if (c.CellValue != null)
                    //                    text += c.CellValue.Text + ",";
                    //            }
                    //            dd.Add(text);
                    //        }
                    //    }
                    //}


                }
                
                // on server
                Stream stream = new MemoryStream(buffer);

                List<string> dd = new List<string>();
                ExcelFile ef = new ExcelFile();
                ef.LoadXlsx(stream, XlsxOptions.None);

                string cellString = ef.Worksheets[1].Rows[4].Cells[1].Value.ToString();
    Tuesday, August 3, 2010 11:57 AM