Silverlight / WPF - Import data from excel to silverlight datagrid

Asked By Toral on 25-Aug-11 04:02 AM
Hello Everybody,

Can anybody help me?How to import data from excel file to silverlight datagrid?

Thanks & Regards
Toral Shah
dipa ahuja replied to Toral on 25-Aug-11 04:11 AM

private string ReadUserXMLFile()
      {
      OpenFileDialog dlg = new OpenFileDialog();
        dlg.Multiselect = false;
        dlg.Filter = "Excel XML Files (*.xml)|*.xml";
      bool bResult = (bool)dlg.ShowDialog();
      if (!bResult)
        return "";

      FileInfo info = dlg.File;
        StatusText.Text = info.Name;

// open the stream for reading

      Stream s = info.OpenRead();

      StreamReader reader = new StreamReader(s);

      var xml = reader.ReadToEnd();
        return xml;
      }

http://www.c-sharpcorner.com/UploadFile/mgold/1133/

Reena Jain replied to Toral on 25-Aug-11 04:14 AM
Hi,

Silverlight cannot visit excel files directly.  If you want to show the excel data to Grid/DataGrid, we'd better first get the data from WCF.  WCF/WebService can fetch the data and organize them into a return object.   Now we can call the WCF on Silverlight and bind the return object.

or

try this 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.
Index Page for the code example posts using this library

http://abubakar-dar.blogspot.com/2010/12/excellite-silverlight-library-for-ms.html

Reading Excel file in silverlight totaly on client side

http://abubakar-dar.blogspot.com/2010/12/import-excel-data-in-silverlight-with.html
Cos mos replied to Toral on 25-Aug-11 04:17 AM

Hi,

1. Save the worksheet data in xml file.

2.  For example :

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="74.25"/>
   <Column ss:Width="84.75"/>
   <Column ss:Index="4" ss:Width="130.5"/>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Address</Data></Cell>
    <Cell><Data ss:Type="String">State</Data></Cell>
    <Cell><Data ss:Type="String">Tool</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Cmys</Data></Cell>
    <Cell><Data ss:Type="String">201, 2nd Cross</Data></Cell>
    <Cell><Data ss:Type="String">Banglore</Data></Cell>
    <Cell><Data ss:Type="String">Web</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Mike Gold</Data></Cell>
    <Cell><Data ss:Type="String">100 Lamar Drive</Data></Cell>
    <Cell><Data ss:Type="String">TX</Data></Cell>
    <Cell><Data ss:Type="String">Google</Data></Cell>
   </Row>
  </Table>

3. Reading in the XML file

private string ReadUserXMLFile()
      {
        OpenFileDialog dlg = new OpenFileDialog();
        dlg.Multiselect = false;
        dlg.Filter = "Excel XML Files (*.xml)|*.xml";
        bool bResult = (bool)dlg.ShowDialog();
        if (!bResult)
          return "";

        FileInfo info = dlg.File;
        StatusText.Text = info.Name;

// open the stream for reading

        Stream s = info.OpenRead();

        StreamReader reader = new StreamReader(s);

        var xml = reader.ReadToEnd();
        return xml;
      }

4. Parsing the xml string into an XDocument
  var doc = XDocument.Parse(xml);

5. Creating the columns of our data grid from the spreadsheet data
  

static  ObservableCollection<List<string>> items = new ObservableCollection<List<string>>();

     private void SetupDataGridFromSpreadsheetColumnNames(XDocument doc)
     {
       // get a list of column names
       var columnNames = doc.Descendants().Where(x => x.Name.LocalName == "Row").First().Descendants().Where(y => y.Name.LocalName == "Data").Select(q => q.Value).ToList();

       int count = 0;
       // create the columns in the datagrid and set the bindings to use 
       // a value converter that can process the array of strings
       foreach (var name in columnNames)
       {
         var column = new DataGridTextColumn() { Header = name };
         dataGrid1.Columns.Add(column);
         column.Binding = new Binding() { Converter = (IValueConverter)this.Resources["arrayIndexToValueConverter"], ConverterParameter = count };
         count++;
       }
       // set the data source of the data grid
       dataGrid1.ItemsSource = items;
     }


6. Extract the Data from the XML file into the Data Grid

  

 private static void PopulateExcelDataToDataGrid(XDocument doc)
     {
       var rows = doc.Descendants().Where(x => x.Name.LocalName == "Row");

       int rowCount = 0;
       foreach (var row in rows)
       {
         // skip the data in the first row since it is the header
         if (rowCount > 0)
         {
           var data = row.Descendants().Where(y => y.Name.LocalName == "Data").Select(q => q.Value).ToList();
           items.Add(data);
         }

         rowCount++;
       }
     }



7. Using the ValueConverter to pull data from a List into each Data Grid Cell

public class ArrayIndexToValueConverter : IValueConverter
    {

      #region IValueConverter Members

      public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
      {
        var index = (int)parameter;
        var list = value as List<string>;
        if (index >= list.Count) return "";
        return list[index];
      }

      public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
      {
        throw new NotImplementedException();
      }

      #endregion
    }

6. Extract the Data from the XML file into the Data Grid  7. Using the ValueConverter to pull data from a List into each Data Grid Cell
Jitendra Faye replied to Toral on 25-Aug-11 04:37 AM
Silverlight cannot visit excel files directly.  If you want to show the excel data to Grid/DataGrid, we'd better first get the data from WCF.  WCF/WebService can fetch the data and organize them into a return object.   Now we can call the WCF on Silverlight and bind the return object.

or

try this 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.
Index Page for the code example posts using this library

http://abubakar-dar.blogspot.com/2010/12/excellite-silverlight-library-for-ms.html

Reading Excel file in silverlight totaly on client side

http://abubakar-dar.blogspot.com/2010/12/import-excel-data-in-silverlight-with.html

Anoop S replied to Toral on 25-Aug-11 04:43 AM
A main question is where the files come from. Are they e.g. selected by the user on the client side and need to be parsed there? Or are they part of the server side, i.e. retrieved or located on the server and then passed on to the client?

For the latter one there is a huge amount of libraries and frameworks you can use to work with those files. For example, here is a Linq to CSV library for that. This shorter article quickly explains how to use it. It's a pretty elegant solution based on attributes and strongly typed entity classes.