Datagrid > DataTable > Excel
-
Hi I have a form with 7 Datagrids. I populate DataTables from a database. I create a Dataview of the tables, and apply various filters based on user selections. I then use the DataVeiw as the Datagrid DataSource. I have also used Tablestyles to give the Datagrid Columns 'Readable' columnnames, and have adjusted the width of certain columns and set the columnwidth to 0 on some columns in order to hide them. This all works great. Now I want to add the option to Export what displayed by the datgrids to Excel. This is easy with a Datatable, since I can use the Column Names as headings, and then use a loop
For Each Col as Datacoumn in dtMyTable.Columns For each Row as datarow in dtMyTable.Rows cells(Row,Col) = ... Next 'Row Next 'ColI'm battling to get the datagrid back into a table format - and cannot Ctype(Datagrid, DataTable) directly. Any ideas how I can do this. (I'd like to be able to use the column headings and column widths of the datagrid to format the Excel sheet) Thanx Richard -
Hi I have a form with 7 Datagrids. I populate DataTables from a database. I create a Dataview of the tables, and apply various filters based on user selections. I then use the DataVeiw as the Datagrid DataSource. I have also used Tablestyles to give the Datagrid Columns 'Readable' columnnames, and have adjusted the width of certain columns and set the columnwidth to 0 on some columns in order to hide them. This all works great. Now I want to add the option to Export what displayed by the datgrids to Excel. This is easy with a Datatable, since I can use the Column Names as headings, and then use a loop
For Each Col as Datacoumn in dtMyTable.Columns For each Row as datarow in dtMyTable.Rows cells(Row,Col) = ... Next 'Row Next 'ColI'm battling to get the datagrid back into a table format - and cannot Ctype(Datagrid, DataTable) directly. Any ideas how I can do this. (I'd like to be able to use the column headings and column widths of the datagrid to format the Excel sheet) Thanx RichardI wish I knew an answer. I've been watching this message since you posted it, hoping someone would have the answer. I've ran into the same problem recently. If you figure out how I hope you'll followup and let me know what you find. Thanks :) Lost in the vast sea of .NET
-
Hi I have a form with 7 Datagrids. I populate DataTables from a database. I create a Dataview of the tables, and apply various filters based on user selections. I then use the DataVeiw as the Datagrid DataSource. I have also used Tablestyles to give the Datagrid Columns 'Readable' columnnames, and have adjusted the width of certain columns and set the columnwidth to 0 on some columns in order to hide them. This all works great. Now I want to add the option to Export what displayed by the datgrids to Excel. This is easy with a Datatable, since I can use the Column Names as headings, and then use a loop
For Each Col as Datacoumn in dtMyTable.Columns For each Row as datarow in dtMyTable.Rows cells(Row,Col) = ... Next 'Row Next 'ColI'm battling to get the datagrid back into a table format - and cannot Ctype(Datagrid, DataTable) directly. Any ideas how I can do this. (I'd like to be able to use the column headings and column widths of the datagrid to format the Excel sheet) Thanx Richard -
Have you tried: Dim dSource As Object dSource = DataGrid.DataSource If TypeOf dSource Is DataView Then Dim tbl As DataTable = CType(DataGrid.DataSource, DataView).Table 'Export to Excel... End If Dean
Hi Dean Thanks 4 your reply. That works, but the problem is that I have a table with 6000 records. This Table is that datasource for a DataView. I then apply filters to the dataview. (So for example now the Dataview only has 100 Records) It is these 100 records that I want to put in a new table for export. Using your method, it returns the full source table with all 6000 records. I'd be happy to loop through the Dataview and adding those rows to a table, but can't seem to get that right. Richard
-
Hi Dean Thanks 4 your reply. That works, but the problem is that I have a table with 6000 records. This Table is that datasource for a DataView. I then apply filters to the dataview. (So for example now the Dataview only has 100 Records) It is these 100 records that I want to put in a new table for export. Using your method, it returns the full source table with all 6000 records. I'd be happy to loop through the Dataview and adding those rows to a table, but can't seem to get that right. Richard
You can pull data directly from the dataview which would allow you to only use the filtered data: Dim i As Integer = 0 Dim dr As DataRowView For Each dr In MydataView ExcelWs.Cells(i, 1).Value = dr("Field1") ExcelWs.Cells(i, 2).Value = dr("Field2") ......... ExcelWs.Cells(i, n).Value = dr("Fieldn") etc i += 1 Next Dean