|
I looked at my database again. Corrected some errors. The sub tables are one-to-many relationships with the main table. It does not look as if what i want to achieve will work. I do not want the datagridview to display more than one record per item.
What i am also doing is exporting my datagrid to excel. Here is the code (It exports the main table perfectly). If you could perhaps help me modify my code to ADD the additional columns from the sub table records to the excel spread sheet, that will also solve my problem because it will be used mainly for pivots and printing.
The vb is merely a front-end for capturing and displaying the data and i thought it would be easy enough to just add the extra columns to the datagridview, because my export to excel already works very well.
Thanks very much for your help so far.
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
'Verfying the datagridview has data or not
If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If
'Creating dataset to export
ds = New System.Data.DataSet
'Add table to dataset
ds.Tables.Add()
'Add column to the table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
ds.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
'Add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = ds.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value
Next
ds.Tables(0).Rows.Add(dr1)
Next
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()
Dim dt As System.Data.DataTable = ds.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
Dim strFileName As String = "C:\AAA\MemberList.xls" 'This will become a textbox later
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
End Sub
|
|
|
|
|
You still end up with the same problem, trying to convert row data into column data and matching it to the main table data.
What version of MS Access are you using for your database?
If it's 2003 or later then one course of action could be to create a Crosstab Query in MS Access for each of the tables that need the data rows converting into columns. You could then create a new Select Query to combine the data from the main table with that from the crosstab queries and then reference the Select query in your vb.net app instead of the main table. You would then not need to change your code to export to Excel.
|
|
|
|
|
please help...
how can i open or activate window to launch excel using vb.net??
thanks...
jyn
|
|
|
|
|
|
Do you want to automate Excel or just start it as a separate process/application?
|
|
|
|
|
thanks for replies..
i need is to make the excel application visible to the user after populate the data on it..
or can download it after clicking the export button...
any idea?
thanks again...
jyn
|
|
|
|
|
You mentioned 'download it after clicking', so are you creating a web page/web application or a windows forms application?
|
|
|
|
|
Hi Guys...
i'm new to vs2008 express, after creating a simple app for testing, i wanted to create the setup package for installing....
i dont see the option for creating the setup package as in the case of vs2005...
does anyone know how to create this package or maybe the menu option that starts the wizard?
thanks in advance
Anoop Brijmohun
|
|
|
|
|
VS 2008 Express does not have setup projects. You will have to create your own (from scratch), find a third party solution, or dish out for a paid version of VS 2008.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Check out Inno Setup[^]. It's small, fast, free, and easy to learn.
|
|
|
|
|
I'll second the use of Inno Setup
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
thanks, much appreciated!
|
|
|
|
|
In Winforms, a TextBox has a MultiLine Property as well as an AcceptsReturn property.
But when used in a DataGridView via the DataGridViewTextBoxColumn, there are no such properties.
So at the moment, my DataGridView has a label, informing users to press Shift+Enter for a new line.
There must be a better way to accept the Return key for a new line in a textbox within a DataGridView.
modified on Thursday, October 2, 2008 7:09 AM
|
|
|
|
|
Assuming a datagridview control named dgvRows:
Private Sub dgvRows_KeyDown( _
ByVal sender As Object, _
ByVal e As System.Windows.Forms.KeyEventArgs) _
Handles dgvRows.KeyDown
' Detect the Enter key
If e.KeyCode = Keys.Return Then
' Determine the currently selected column
Select Case dgvRows.SelectedCells(0).ColumnIndex
Case 0
' Determine the currently selected row
Select Case dgvRows.SelectedCells(0).RowIndex
Case 0
' Code to manipulate the selected cell at (0,0)
Case 1
' Code to manipulate the selected cell at (0,1)
'
'
Case 99
' Etc
End Select
Case 1
'
'
Case 99
End Select
End If
End Sub
|
|
|
|
|
Hey George
Thanks for the answer but that doesn't work, I've tried it with the Keypress event as well.
The problem is that that event only fires when the entire cell is selected, but if the focus is on the control inside the cell, - in this case, the TextBox - the event doesn't fire for any keys pressed.
If you got this to work I would appreciate some more help.
Kind Regards
|
|
|
|
|
I love a worthwhile challenge. It's taken all morning, but, I think I've got it. The key is overriding the ProcessCmdKey method in the DataGridView class so that the Enter key can be detected and Ctrl^Enter substituted.
Step 1 is to add an extended DataGridView to your project where the override takes place.
Public Class myDataGridView
Inherits DataGridView
Protected Overrides Function ProcessCmdKey( _
ByRef msg As System.Windows.Forms.Message, _
ByVal keyData As System.Windows.Forms.Keys) _
As Boolean
'** When the Enter key is pressed in the textbox column,
'** substitute Ctrl^Enter
If keyData = Keys.Enter Then
SendKeys.SendWait("^{ENTER}")
Return True
Else
Return MyBase.ProcessDialogKey(keyData)
End If
End Function
End Class
Unfortunately, this means the DataGridView control will have to be completely built and positioned on the form at runtime to take advantage of the override. Here's the simple code I used for testing. You will have to flesh it out for your purposes.
Public Class Form1
Dim dgv As New myDataGridView
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
'** Add the datagridview to the form
Controls.Add(dgv)
dgv.Left = 10
dgv.Top = 10
'** Add a textbox column
Dim dgvCol As New DataGridViewTextBoxColumn
With dgvCol
.HeaderText = "Text Column"
.Name = "chdrTextCol"
.Width = 130
End With
dgv.Columns.Add(dgvCol)
dgvCol = Nothing
'** Add a row
dgv.Rows.Add()
End Sub
End Class
|
|
|
|
|
Hi
Thanks for all your trouble.
I tried your code, had to set the ".DefaultCellStyle.WrapMode = True".
But when I press enter, the focus remains on the current cell, and the cell becomes selected, instead of adding a line into the textbox.
It does however fire on the key event within the textbox, which is by far the closest I have come to a solution so far.
How would I go about substituting the Enter key with a Shift^Enter, instead of a Ctrl^Enter? Seeing that when using the datagridview, pressing Shift+Enter gives the desired result, but not pressing Ctrl+Enter.
I don't totally understand the "^{ENTER}" syntax and how it works...
|
|
|
|
|
|
Im new to VB.NET. Working on developing Windows Application using Visual Studio 2003 platform. I have a target from which I read data and show it in a datagrid. I need to convert this datagrid into a spreadsheet which can either be a MS .xls file or OpenOffice .xls file.
Thanx in advance
|
|
|
|
|
|
Ashfield is right, there are plenty of articles on this site and on the net, but here is one possible approach (which is working for me, while you read this):
1. make an excel file with some demo data and make it look the way you want your app to make it look.
2. save it as xml
3. open this xml file in vs2003, so you can see what it looks like
4. use some loops to write the values from your dataset and the xml tags into a big string.
5. write the string to an xls file with a StreamWriter
6. Bob's your uncle.
Because you wrote the file as an xml file, it can easily be opened with Excel, OpenOffice or any other program that can usually read Excel files.
Good luck.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thanx Ashfield and Johan for replying..
I got the problem fixed. What Iv done is that I write the data from Datagrid into CSV file format which then can be opened in both MS Office and Open Office Spreadsheet.
|
|
|
|
|
Hi
I am having a little bit of trouble understanding how to read property values from objects in the Active Directory that are not included in the standard DirectoryServices.AccountManagement namespace which is new in .NET 3.5 ..
At current this is the only reference I have which I am not following.. not a very good C# guy!
http://costoda.blogspot.com/2008/01/getting-information-from-active.html[^]
If someone could suggest some examples on how to do the same as that link above in VB instead that would be awesome!
Kind Regards,
Tim
|
|
|
|
|
Does anyone know anything on this topic? I don't want to sound impatient!
Tim
-------------------------
Visit my Blog:
http://tim.raimes.com.au/
|
|
|
|
|