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

How to Import table from excel file to Listview?

12 Answers 930 Views
ListView
This is a migrated thread and some comments may be shown as answers.
Khanh
Top achievements
Rank 1
Iron
Khanh asked on 11 Sep 2019, 11:24 PM

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!

12 Answers, 1 is accepted

Sort by
0
Accepted
Dess | Tech Support Engineer, Principal
Telerik team
answered on 13 Sep 2019, 07:07 AM
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.
0
Khanh
Top achievements
Rank 1
Iron
answered on 13 Sep 2019, 07:54 AM

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?


0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 17 Sep 2019, 09:55 AM
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.
0
Khanh
Top achievements
Rank 1
Iron
answered on 17 Sep 2019, 10:16 AM

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.

0
Khanh
Top achievements
Rank 1
Iron
answered on 17 Sep 2019, 10:29 AM

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

0
Khanh
Top achievements
Rank 1
Iron
answered on 17 Sep 2019, 12:07 PM

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

0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 19 Sep 2019, 08:08 AM
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.
0
Khanh
Top achievements
Rank 1
Iron
answered on 19 Sep 2019, 10:14 AM
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.
0
Khanh
Top achievements
Rank 1
Iron
answered on 19 Sep 2019, 10:57 AM

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

mygif: Radlistview-Gif1

0
Khanh
Top achievements
Rank 1
Iron
answered on 19 Sep 2019, 11:16 AM
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.
0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 20 Sep 2019, 08:41 AM

 

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.
0
Khanh
Top achievements
Rank 1
Iron
answered on 20 Sep 2019, 02:25 PM
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.
Tags
ListView
Asked by
Khanh
Top achievements
Rank 1
Iron
Answers by
Dess | Tech Support Engineer, Principal
Telerik team
Khanh
Top achievements
Rank 1
Iron
Share this question
or