This is a migrated thread and some comments may be shown as answers.

Radgrid with Dynamic Columns bound to excelworkbook dropping columns

4 Answers 31 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Wired_Nerve
Top achievements
Rank 2
Wired_Nerve asked on 21 Nov 2013, 08:28 PM
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;
          }
      }

4 Answers, 1 is accepted

Sort by
0
Wired_Nerve
Top achievements
Rank 2
answered on 21 Nov 2013, 09:01 PM
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...

0
Wired_Nerve
Top achievements
Rank 2
answered on 22 Nov 2013, 09:58 PM
Still having issues with this.

I confirmed the Dataset tables has the columns that the dynamic radgrid is not displaying...
0
Accepted
Daniel
Telerik team
answered on 26 Nov 2013, 01:07 PM
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.
0
Kunal
Top achievements
Rank 1
answered on 26 Nov 2013, 01:59 PM
Thanks, you confirmed what I thought I was going to have to do.

You folks rock btw!  

Happy holidays and all that good stuff.  ;)

Tags
Grid
Asked by
Wired_Nerve
Top achievements
Rank 2
Answers by
Wired_Nerve
Top achievements
Rank 2
Daniel
Telerik team
Kunal
Top achievements
Rank 1
Share this question
or