Basic xslx import/export functionality through a DataSet

2 posts, 0 answers
  1. Scott
    Scott avatar
    31 posts
    Member since:
    Aug 2011

    Posted 17 Apr 2013 Link to this post

    I have a project that is currently using the WPF control suite. What I would like to do is import data from an xlsx spreadsheet into a DataSet.

    There's really two aspects to the solution. First is a discovery-type service where I can get worksheet names, row by column counts, and then column data types. Secondly, I would like to import the data from a worksheet into a DataSet. Ideally without displaying a spreadsheet control as the process would be automated.

    Of secondary importance is the reverse. E.g., open/create a workbook, create a new or clear an existing worksheet, and (append) data to a given worksheet.

    Rather than include another vendor's library, I would like to do this with the currently used Telerik suite. I don't see any significant discussion in the product descriptions or forums so I am raising the question here.
  2. Andrew
    Andrew avatar
    140 posts

    Posted 22 Apr 2013 Link to this post

    Hi Scott,

    Importing data from RadSpreadsheet to a DataSet is possible. All you have to do is enumerate all cells in a row and write each value in a field. You can get the value of a cell using the following approach:

    ICellValue value = this.radSpreadsheet.ActiveWorksheet.Cells[0, 1].GetValue().Value;
    After you perform this for every cell in the current row you will be able to fill one row in the data set.

    The reverse operation works almost the same. You enumerate the dataset's rows and then for each column you set the corresponding cell. Here is some code for setting the value:
    this.radSpreadsheet.ActiveWorksheet.Cells[0, 1].SetValue("Some Value");

    While doing this displaying the RadSpreadsheet is not necessary. You can manipulate the spreadsheet in code behind and then export it to .xlsx. You will need references to the following assemblies to do that:
    • Telerik.Windows.Documents.Core.dll
    • Telerik.Windows.Documents.Spreadsheet.dll
    • Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.dll
    • Telerik.Windows.Zip.dll

    the Telerik team

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

Back to Top