DataGridView - How to sort columns by row?

Question

Hi

I would like to know how can I sort columns in datagridview using vb.net (not rows in the columns). Please see example below

unsorted:

A    C    B    E    G     F
1    2    5    6    3     5
3    5    1    4    7 2

sorted (by first row):

A    B    C    E    F     G
1    5    2    6    5     3
3    1    5    4    2 7

Maybe there is a better way to do it than using datagidview, please advise. your help would be much appreciated!

regards
a

Sam Hobbs · Answer

I am sorry that I did not see your latest reply but this forum's software makes it difficult to know when someone posts clarifications such as yours. I have complained about that but so far they have not fixed the problem. I don't care what is in the data but my questions are intended to get information so I con answer more explicitly and clearly. If you want an explicit answer you must be explicit about the requirements. It is very frustrating when I try to guess at what a person needs and then I guess wrong and the person says that my help is wrong because of some reason yet they did not tell me the additional information before then. Don't blame me if I can't be clear if you are not clear. I am sorry that I did not understand until just now that you need to sort just the first row (the header?) and to show the data correspondingly. I should have seen that but it would have helped if you had emphasized that. So the solution seems easy. You create an ADO table from the spreadsheet data. You can create a separate table that contains only the first row data. The first row data needs to include something indicating what column that each item is from. Sort that table. Read that table in and create a mapping of spreadsheet column to DataGridView column. Then when the main data is read simply put the data from each spreadsheet column into it's DataGridView column.

Arek · Answer

Thanks Sam I am not really sure why do you need to see what is in the excel sheet ? It is not fixed sheet information inside that sheet can change. All I can say is that the number of columns will be the same for each row . To check number of columns is easy using dataset but again I can't se the point of doing this to complete my task (please be more specific mybe I just can't foolow your logic) Why do you need identifier ? again I don't know why would you nedd it..(and again can be done easily using dataset (looping through the rows and adding one) but can't see the point). The method I have used to import excel file does not give you a lot of room for data manipulation before you place it in the dataset table (basically it will create a dataset table which is exactly the same as your excel sheet). I could use Excell library (COM reference) and loop through the cells in my excel sheet and find out all the information you require at the very begining but the problem with this method is that if someone else is using app has to have the same excel version as reference library used. Could you please provide an examlpe (does not have to be code it can be similar example to my previous one) using your idea with SQL (I don't know how I could use SQL to sort columns by row ?). Data from my excel sheet will have to be always sorted by first row. I think that the quickes way to do it would be: 1. cerate another table in dataset. 2. add first row to arralist (array) and sort it. 3. copy data from my attenence table to newly created table based on the sorted information in array. 4. show new sorted table in DataGridView. Let me know what do you think and of course please advise if you think that this is a way to go. All other help regarding to this matter would be much appreciated! Thanks A

Sam Hobbs · Answer

Thank you for the clarification. There are a couple of things I am still not sure of. I don't see what the fields are of the Attendence table. I am also not sure whether the number of columns will always the the same for each row of a sheet and I am not sure how you will determine how many columns there are. So if the following is incompatible with your needs then perhaps it is due to the absence of that information. I also don't see anything indicating there is data identifying each row; a row identifier. If the Attendence table is for student attenance the the identifier might be the student's id. The identifier could be the row id from the sheet so I will assume you have something that can be used as a row identifier. Then the Attendence table could consist of the two fields the row identifier value and the value from one cell in the row. Then you can use SQL to sort the data by row id and by cell value. Understand? Then you can use that data to build the grid.

Arek · Answer

Hi Sam Thank you for your reply. I am sorry that you found my previous post not descriptive enough I will try again: 1. Desription of the task: I am trying to develop a small application which will open excel file and place data from excel sheet into datagridview (there could be up to 60 columns x 1000 rows in .xls sheet). Data in the excel sheet is unsorted so I have to do something to sort it. Does not really matter if this will be done using datagridview sorting dataset sorting dataviev sorting/filtering placing data to array first sorting and then showing in dtagridview (or any other method). So looking once again on my previous example we have: unsorted data in excel sheet: A C B E G F 1 2 5 6 3 5 3 5 1 4 7 2 final result - sorted data(by first row): A B C E F G 1 5 2 6 5 3 3 1 5 4 2 7 2. Current state of the application: I have import excel sheet doneI have created dataset and shown data in DatagridView. the only thing I am missing is sorting. Below is the code I have for importing excel sheet OpenFileDialog = New System.Windows.Forms.OpenFileDialog() OpenFileDialog.Filter = Excel Files (*.xls)|*.xls OpenFileDialog.ShowDialog() Dim Con As System.Data.OleDb.OleDbConnection Dim Comm As System.Data.OleDb.OleDbDataAdapter Con = New System.Data.OleDb.OleDbConnection(provider=Microsoft.Jet.OLEDB.4.0;  & _ data source=' & OpenFileDialog.FileName &  ';  & Extended Properties='Excel 8.0;HDR=Yes;IMEX=1') Comm = New System.Data.OleDb.OleDbDataAdapter(select * from [sheet1$] Con) Comm.TableMappings.Add(Table Attendence) DtSet = New System.Data.DataSet Comm.Fill(DtSet) Me.DataGridView1.DataSource = DtSet.Tables(0) Con.Close() Catch ex As Exception MessageBox.Show(Err.ToString) End Try regards a

Sam Hobbs · Answer

What do you need help with? Do you need help with the actual sorting or do you need help putting the data into a DataGridView or do you need help getting the data from somewhere? Please be more specific. I don't like it when someone says my answer is wrong when the problem is that the person did not explain what they need.