-
May 20th, 2019, 04:05 PM
#1
Thread Starter
Fanatic Member
Write DataGridView Headers to excel
Hello:
My code is below. I have no trouble reading in the first row as a header for a datagridview, but when I write it out it keeps truncating them.
Why can't I just somehow say the first row is a header that needs to be written??
Code:
Private Sub WriteDB(UserDB As String, dgv As DataGridView)
Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i, j As Integer
xlApp = New Microsoft.Office.Interop.Excel.Application
xlBook = xlApp.Workbooks.Add(misValue)
xlSheet = xlBook.Sheets("Sheet1")
For h As Integer = 0 To dgv.ColumnCount - 1
xlSheet.Cells(0, h) = dgv.Columns(h).HeaderText
' MessageBox.Show(dgv.Columns(h).HeaderText)
Next
For i = 1 To dgv.RowCount - 1
For j = 0 To dgv.ColumnCount - 1
xlSheet.Cells(i + 1, j + 1) = dgv(j, i).Value.ToString()
Next
Next
xlSheet.SaveAs(dbf)
xlBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlBook)
releaseObject(xlSheet)
End Sub
Last edited by ssabc; May 20th, 2019 at 04:27 PM.
-
May 20th, 2019, 06:20 PM
#2
Re: Write DataGridView Headers to excel
Why can't I just somehow say the first row is a header that needs to be written??
you could if you were using ADO.Net /OleDb
-
May 21st, 2019, 08:29 AM
#3
Re: Write DataGridView Headers to excel
Hello,
There is a free Excel library named SpreadSheetLight which has a method to export a DataTable to Excel with or without column headers. In the following code sample I show how to get a DataGridView rows into a DataTable then pass the DataTable to SpreadSheetLight which will import the DataTable into Excel and allow you to provide the sheet name. The best part is there is no worries for cleaning up automation objects as SpreadSheetLight is not based on automation and will even work without Excel being installed.
DataGridView unbound to either Excel or Text file (VB.NET)
See this section in the code sample about for setting up: Setting up for using SpreadSheetLight (and note SpreadSheetLight can be installed via NuGet package manager in Visual Studio).
Downside, more code, upside the code is very easy and pain free.
-
May 21st, 2019, 10:29 AM
#4
Thread Starter
Fanatic Member
Re: Write DataGridView Headers to excel
Hello:
I am converting this to ADODB as suggested.
It is frustrating because I date a datagridview that I wish to update to an Excel spreadsheet. Whenever I change the size of the dgv, the spreadsheet seems to want the same number of records. So I tried an rs.AddNew() if the recordcount was not the same. There are also options to delete, which affect things, as I'm sure is intended.
I would be fine with wiping out the entire spread and rewriting the datagridview every time. This has to be simple??
Code below:
Code:
Private Sub WriteDB(UserDB As String, dgv As DataGridView)
Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
Dim cn As String = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source= " & dbf & "; Extended Properties = " & Chr(34) & "Excel 12.0; HDR = Yes; IMEX = 1" & Chr(34) & "; "
Dim sql As String = "SELECT * FROM [Sheet1$]"
Dim rs As ADODB.Recordset = New ADODB.Recordset
rs.Open(sql, cn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)
rs.MoveFirst()
For i As Integer = 0 To dgv.Rows.Count - 2
If rs.EOF Then
rs.AddNew()
End If
rs.Fields(0).Value = dgv.Rows(i).Cells(0).Value
rs.Fields(1).Value = dgv.Rows(i).Cells(1).Value
rs.Fields(2).Value = dgv.Rows(i).Cells(2).Value
rs.Update()
rs.MoveNext()
Next
rs.Close()
End Sub
-
May 21st, 2019, 10:39 AM
#5
Re: Write DataGridView Headers to excel
Nono, not ADODB... ADO.Net
it looks more like this
Code:
Dim XLSFile As String = "C:\DATA\xlfile.xlsx"
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=" & XLSFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
Using XLSXConn As New OleDbConnection(connstring)
Using XlsDA As New OleDbDataAdapter("SELECT * FROM [sheet1$]", XLSXConn)
Dim XlDtable As New DataTable
XlsDA.Fill(XlDtable)
DataGridView1.DataSource = XlDtable
End Using
End Using
-
May 21st, 2019, 11:26 AM
#6
Thread Starter
Fanatic Member
Re: Write DataGridView Headers to excel
Thanks, Reading it that way doesn't lock down the file when trying to wrote back!
The difficulty is it binds the data... I want to keep things unbound because I am adding rows to the datagrids and updating. This, this may not be a good solution for me.
At this point, I want to just write out the dgv like this, but it thinks the file is in use from the read, and nothing seems to kill the process.
Code:
Private Sub WriteDB2(UserDB As String, dgv As DataGridView)
Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
My.Computer.FileSystem.DeleteFile(dbf)
Dim xlapp As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
Dim xlbook As Microsoft.Office.Interop.Excel._Workbook = xlapp.Workbooks.Add(Type.Missing)
Dim xlsheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
xlapp.Visible = True
xlsheet = xlbook.Sheets("Sheet1")
xlsheet = xlbook.ActiveSheet
For i As Integer = 1 To dgv.Columns.Count + 1 - 1
xlsheet.Cells(1, i) = dgv.Columns(i - 1).HeaderText
Next
For i As Integer = 0 To dgv.Rows.Count - 1 - 1
For j As Integer = 0 To dgv.Columns.Count - 1
xlsheet.Cells(i + 2, j + 1) = dgv.Rows(i).Cells(j).Value.ToString()
Next
Next
xlbook.SaveAs(dbf)
xlapp.Quit()
End Sub
Any other thoughts?
Last edited by ssabc; May 21st, 2019 at 11:31 AM.
-
May 21st, 2019, 01:43 PM
#7
Re: Write DataGridView Headers to excel
I am pretty confused by what your problem is. I am going to use the keyword "unbound" and ignore the rest of what you said.
Here is a working sample how to add data from an XLSX to a datagridview without a datatable or datagridview.datasource
Code:
Public Class FormExcel
Private Sub ButtonLoadXLSX_Click(sender As Object, e As EventArgs) Handles ButtonLoadXLSX.Click
Dim XLSFile As String = "C:\DATA\test.xlsx"
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=" & XLSFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
Using XLSXConn As New OleDb.OleDbConnection(connstring)
XLSXConn.Open()
Using XLSXRdr As OleDb.OleDbDataReader = New OleDb.OleDbCommand("SELECT * FROM [sheet1$]", XLSXConn).ExecuteReader
If XLSXRdr.HasRows Then
For Each DtRow As DataRow In XLSXRdr.GetSchemaTable.Rows
DataGridView1.Columns.Add(New DataGridViewTextBoxColumn With {
.Name = DtRow("ColumnName").ToString,
.ValueType = Type.GetType(DtRow("DataType").ToString)})
Next
While XLSXRdr.Read
Dim Vals(XLSXRdr.FieldCount - 1) As String
For i As Integer = 0 To XLSXRdr.FieldCount - 1
Vals(i) = Convert.ToString(XLSXRdr(i))
Next
DataGridView1.Rows.Add(Vals)
End While
End If
End Using
End Using
End Sub
End Class
Last edited by kpmc; May 21st, 2019 at 02:42 PM.
-
May 22nd, 2019, 08:20 AM
#8
Thread Starter
Fanatic Member
Re: Write DataGridView Headers to excel
The problem is in the writing of data back to excel, not reading it in.
If it is not read a certain way, the write excludes the headers.
Code to ReadXL:
Code:
Private Sub ReadDB3(UserDB As String, dgv As DataGridView)
Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
Dim cn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & dbf & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
Using xlsxCn As New OleDb.OleDbConnection(cn)
xlsxCn.Open()
Using xlsxRdr As OleDb.OleDbDataReader = New OleDb.OleDbCommand("SELECT * FROM [sheet1$]", xlsxCn).ExecuteReader
If xlsxRdr.HasRows Then
For Each DtRow As DataRow In xlsxRdr.GetSchemaTable.Rows
dgv.Columns.Add(New DataGridViewTextBoxColumn With {
.Name = DtRow("ColumnName").ToString,
.ValueType = Type.GetType(DtRow("DataType").ToString)})
Next
While xlsxRdr.Read
Dim Vals(xlsxRdr.FieldCount - 1) As String
For i As Integer = 0 To xlsxRdr.FieldCount - 1
Vals(i) = Convert.ToString(xlsxRdr(i))
Next
dgv.Rows.Add(Vals)
End While
End If
End Using
End Using
End Sub
Code to WriteXL:
Code:
Private Sub WriteDB2(UserDB As String, dgv As DataGridView)
Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
' KillExcel()
My.Computer.FileSystem.DeleteFile(dbf)
Dim xlapp As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
Dim xlbook As Microsoft.Office.Interop.Excel._Workbook = xlapp.Workbooks.Add(Type.Missing)
Dim xlsheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
xlapp.Visible = True
xlsheet = xlbook.Sheets("Sheet1")
xlsheet = xlbook.ActiveSheet
For i As Integer = 1 To dgv.Columns.Count + 1 - 1
xlsheet.Cells(1, i) = dgv.Columns(i - 1).HeaderText
Next
For i As Integer = 0 To dgv.Rows.Count - 1 - 1
For j As Integer = 0 To dgv.Columns.Count - 1
xlsheet.Cells(i + 2, j + 1) = dgv.Rows(i).Cells(j).Value.ToString()
Next
Next
xlbook.SaveAs(dbf)
xlapp.Quit()
End Sub
Last edited by ssabc; May 22nd, 2019 at 08:29 AM.
-
May 22nd, 2019, 08:30 AM
#9
Re: Write DataGridView Headers to excel
You can use the OledbConnection to write changes back to the excel file. The only thing you cant do is delete.
-
May 22nd, 2019, 09:44 AM
#10
Thread Starter
Fanatic Member
Re: Write DataGridView Headers to excel
Thanks for the reply. I got it working. I'm just deleting and rewriting the whole thing, its not that big. The initial issue, for whatever reason had to do with the write removing the column headers. It seems depending on how it's read in, this can happen.
Code:
Private Sub WriteDB3(UserDB As String, ByVal dgv As DataGridView)
Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
Dim rowsTotal, colsTotal As Short
Dim I, j, iC As Short
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Try
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
xlApp.Visible = True
rowsTotal = dgv.RowCount
colsTotal = dgv.Columns.Count - 1
With xlSheet
.Cells.Select()
.Cells.Delete()
For iC = 0 To colsTotal
.Cells(1, iC + 1).Value = dgv.Columns(iC).HeaderText
Next
For I = 0 To rowsTotal - 1
For j = 0 To colsTotal
.Cells(I + 2, j + 1).value = dgv.Rows(I).Cells(j).Value
Next j
Next I
.Rows("1:1").Font.FontStyle = "Bold"
.Rows("1:1").Font.Size = 12
.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1, 1).Select()
End With
xlBook.SaveAs(dbf)
xlApp.Quit()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
'RELEASE ALLOACTED RESOURCES
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
xlApp = Nothing
End Try
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|