Radgrid with Dynamic Columns bound to excelworkbook dropping columns

5 posts, 1 answers
  1. Wired_Nerve
    Wired_Nerve avatar
    163 posts
    Member since:
    Oct 2007

    Posted 21 Nov 2013 Link to this post

    I have an excelworkbook the user has saved to the server.
    I have a radgrid that is being databound to this excel workbook.
    This workbook can have varying number of columns so I cannot hardcode the columns

    Each column will have various datatypes in them.. For example
    Column 1 will be mixed alphanumeric values
    Column 2 will be dates
    Column 3 will be mixed alphanumerics

    When I bind the excelworkbook to the radgrid all of the alpha numeric columns (Letters and numbers) do not show up.. 

    ANy idea on how to fix this issue?

    I am using the following code plex project to translate the excel workbook into a dataset with tables for each tab in the excel workbook:
    http://exceldatareader.codeplex.com/

    The code that actual binds the radgrid:
    protected DataSet Data
           {
               get
               {
                   if (Application["data"] == null)
                   {
                       return null;
                   }
                   return Application["data"] as DataSet;
               }
           }


    private void ImportFromExcel()
         {
             // Bind Data and populate Dropdown
     
             string filePath = String.Format("~/ImportProduction/{0}", FileName);
             FileStream stream = File.Open(Server.MapPath(filePath), FileMode.Open, FileAccess.Read);
             IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
     
             excelReader.IsFirstRowAsColumnNames = firstRowIsHeaderRowCheckBox.Checked;
     
             Application["data"] = excelReader.AsDataSet();
             excelReader.Close();
     
     
     
             foreach (DataTable t in Data.Tables)
             {
                 var name = t.TableName;
                 var index = Data.Tables.IndexOf(t);
                 RadComboBoxItem item = new RadComboBoxItem(name, index.ToString());
                 RadComboBox1.Items.Add(item);
             }
             RadGridImport.Rebind();
             
         }

    protected void RadGridImport_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
          {
              if (Data != null)
              {
                  var selectedWorkSheet = RadComboBox1.SelectedIndex;
                  if (selectedWorkSheet != -1) (sender as RadGrid).DataSource = Data.Tables[selectedWorkSheet]; ValidateButton.Enabled = true;
              }
          }

  2. Wired_Nerve
    Wired_Nerve avatar
    163 posts
    Member since:
    Oct 2007

    Posted 21 Nov 2013 Link to this post

    A bit more info:
    The general idea here is for the radgrid to render all the datasets table columns as strings and to not worry about converting them to the correct datatype...

  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Wired_Nerve
    Wired_Nerve avatar
    163 posts
    Member since:
    Oct 2007

    Posted 22 Nov 2013 Link to this post

    Still having issues with this.

    I confirmed the Dataset tables has the columns that the dynamic radgrid is not displaying...
  5. Answer
    Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 26 Nov 2013 Link to this post

    Hello Warren,

    The problem is that the AsDataSet method returns columns of an "object" type when there is mixed content inside the Excel columns. The only workaround I can think of (apart from messing with the source code) is to create another table and import the data from the old one. This might be needed because you won't be able to change the columns' datatype when the corresponding cells and rows are filled with data.

    Regards,
    Daniel
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  6. Laura
    Laura avatar
    6 posts
    Member since:
    May 2010

    Posted 26 Nov 2013 Link to this post

    Thanks, you confirmed what I thought I was going to have to do.

    You folks rock btw!  

    Happy holidays and all that good stuff.  ;)

Back to Top
UI for ASP.NET Ajax is Ready for VS 2017