How to Import table from excel file to Listview?

13 posts, 1 answers
  1. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 11 Sep 2019 Link to this post

    Hi. I'm a new user of telerik radlistview control and I'm having some problems importing tables from excel into listview.
    I think I can use loops to do this however I want to use datasource to import once. The problem is that I am not sure what type of data for datasource. Is it the same with listfillrange property (lisbox.list = variant) for listbox in excel like vba?
    Can this work or not:

            Dim import As XlsxFormatProvider = New XlsxFormatProvider
            Dim wb As Workbook = import.Import(File.ReadAllBytes(exportfile))
            Dim ws As Worksheet = wb.Sheets(0)
            Radlistview.DataSource = ws.UsedRange

    Thanks you!
  2. Answer
    Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3997 posts

    Posted 13 Sep 2019 Link to this post

    Hello, Khanh,

    Indeed, the RadListView.DataSource property is the suitable way to bind RadListView with data. You can fill a DataTable with the content of an Excel file and use this DataTable as DataSource for RadListView. Then, specify the RadListView's DisplayMember and ValueMember properties to the fields from your DataSource that you want to see displayed in the items (DisplayMember) and stored as values in the items (ValueMember).

    After some research I have found the following StackOverflow thread which may be suitable for populating the DataTable with data from Excel file: https://social.msdn.microsoft.com/Forums/vstudio/en-US/9a273868-a9c0-48a7-9dd0-03246f3874cf/c-how-to-convert-excel-sheet-to-data-table-dynamically?forum=csharpgeneral

    Additional information about data-binding in RadListView is available in the following help article: https://docs.telerik.com/devtools/winforms/controls/listview/populating-with-data/data-binding

    I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

     

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  3. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 13 Sep 2019 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Hi Dess. Interestingly, I was able to import excel range to radlistview right now, but another problem appears here, when using this command:
    radlistview.DataSource = DtSet.Tables (0)
    It seems that it follows the changing radlistview format, exactly these columns are divided into equal parts, just like paste together value + format
    Can I just fill in data without affecting column formatting?


  4. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3997 posts

    Posted 17 Sep 2019 Link to this post

    Hello, Khanh,

    Could you please specify what formatting exactly is lost when setting the RadListView.DataSource? Note that RadListView offers the CellFormatting event for Details View and the VisualItemFormatting event for the rest of the views which events allow you to format the visual items according to your requirements. Additional information on this topic is available in the following help article: https://docs.telerik.com/devtools/winforms/controls/listview/customizing-appearance/formatting-items

    If you are still experiencing any further difficulties, it would be greatly appreciated if you can provide a sample project demonstrating the undesired behavior that you are facing. Thus, we would be able to think about a suitable solution and assist you further. Thank you in advance.

    Should you have further questions please let me know.

     

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  5. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 17 Sep 2019 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Dear Dess, 

    ** my problem
    From the windows form (width = 640, height = 500) I have added radlistview control
    I have formatted it (w = 630, h = 300) with 4 columns and 10 rows (width of columns 30,200,200,200, horrizonscrollbar = alwayhide)
    From the form load event I used oledbconnection to add excel range to listview and succeeded but after using this method the table format has changed specifically: the width of the columns is divided equally, the size change table makes scroll bar appear itself
    ** new problem
    In addition, I found problems with theme events
    I use BreezeTheme for radlistview, before using it I can edit radlistview normally, however after adding breezetheme: I can only keydown once, radlistview will automatically esc and that box will only contain one price single treatment
    ---------------------------------------
    Currently I have solved problem 1 by reformatting the table after using datasource
    Problems with the theme event breeze I haven't found a solution yet.

  6. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 17 Sep 2019 Link to this post

    hi dess,
    I have this gif, which you can easily imagine than the current problem I'm having

    https://1.bp.blogspot.com/-eOQCmnoUTxo/XYC04iCY9NI/AAAAAAAAAao/4J6R0mC31_4xvZLAJH_XCM-erYBXQmQ0gCLcBGAsYHQ/s1600/ScreenGif.gif

  7. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 17 Sep 2019 Link to this post

    To clarify the problem, I have created a simple file.
    After running the project, on the excel ribbon bar there will be a new tab with 2 buttons.
    Each button will call a form and 1 radlistview comes with the theme
    Here both windows forms are identical. The only small change is the listview size
    However one won't work. : ((
    Looking forward to hearing from you.
    Thanks you.

    my File: Radlistview-with-theme

    Gif: Radlistview-Gif

  8. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3997 posts

    Posted 19 Sep 2019 Link to this post

    Hello, Khanh,  

    When you set the DataSource property to RadListView, the columns in Details view are generated according to the columns in the DataSource. If you need to change any columns' settings, it is necessary to be performed after setting the DataSource

    I have troubles opening the provided project, but after adding the two forms in my sample project I was able to enter multiple characters after entering edit mode with F2 key. Please refer to the attached gif file illustrating the behavior on my end with the specified version. Am I missing something? I have attached my sample project. Could you please specify the exact steps how to reproduce the problem? Thank you in advance. 

    Note that most of the forum threads are reviewed by Telerik representatives and sometimes we address the questions asked by our customers in the forums as well. However, a post in the forum doesn't guarantee you a response from the Telerik support team. Moreover, threads are handled according to license and time of posting, so if it is an urgent problem, we suggest you use a support ticket, which would be handled before a forum thread. Thank you for your understanding.

     

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  9. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 19 Sep 2019 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    hi dess, sorry if i confuse you.
    Almost all the problems I encountered were that radlistview and raddropdownlist control did not work really well in the excel environment.
    the windows forms are all called from the buttons located on the ribbon tab in excel (i'm working on models that can be used directly in excel - vsto office addin, radcontrol is integrated into these projects)
    Your projects are working well and I have no problems.
    If you notice in my gif image, there are 2 buttons on the ribbon tab to call 2 windows forms containing radlistview (with theme) and they work differently.
  10. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 19 Sep 2019 in reply to Khanh Link to this post

    Hi,you can Creat New project vsto for excel addin and you will see the problem I have stated

    mygif: Radlistview-Gif1

  11. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 19 Sep 2019 in reply to Khanh Link to this post

    Exactly if the number of radlistviewitem appears enough to not appear scrollbar then the listview will not work
    I tried it by pressing F2 but it took more than two times for it to work.
  12. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3997 posts

    Posted 20 Sep 2019 Link to this post

     

    Hello, Khanh,  

    Please bear in mind that Office add-in applications have a different application life-cycle and message pump compared to when running the same application in a standard .NET scenario. In this respect, there might be differences when running the application in such an environment. We can't guarantee that RadDropDownList and RadListView will work  as expected in VSTO applications. I believe that the problem with RadDropDownList has been resolved with the suggestion in the other thread that you have opened on the same topic.

    Regarding the question about editing RadListView, I confirm that it is an issue with the Breeze theme. I have logged it in our feedback portal by creating a public thread on your behalf. You can track its progress, subscribe for status changes and add your comments on the following link - feedback item.

     

    I have also updated your Telerik points.

    Currently, the possible solution that I can suggest is to increase the ItemSize

    Me.RadListView1.ItemSize = New Size(200, 30)

     

     

    I hope this information helps.

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  13. Khanh
    Khanh avatar
    25 posts
    Member since:
    Sep 2019

    Posted 20 Sep 2019 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    hi dess, this sub-problem I have temporarily solved by allowing the horizon scrollbar to be displayed and using the label with custom background to obscure it: D so it is not urgent for me for now but I will follow Watch out for the answer
    Thanks for the support from you.
Back to Top