hallo
ik wil mijn gegevens uit een datagridview expoteren naar excel.
alles verloopt goed , maar de 0 die voor sommige getallen staat valt weg
vb 044856 wordt 44856
dit is mijn code
ik wil mijn gegevens uit een datagridview expoteren naar excel.
alles verloopt goed , maar de 0 die voor sommige getallen staat valt weg
vb 044856 wordt 44856
dit is mijn code
Code:
Private Sub ExportCategorie()
Dialogexport.Show()
'verfying the datagridview having data or not
If ((CataloogDataGridView.Columns.Count = 0) Or (CataloogDataGridView.Rows.Count = 0)) Then
Exit Sub
End If
'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To CataloogDataGridView.ColumnCount - 1
dset.Tables(0).Columns.Add(CataloogDataGridView.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To CataloogDataGridView.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To CataloogDataGridView.Columns.Count - 1
dr1(j) = CataloogDataGridView.Rows(i).Cells(j).Value
Next
dset.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 = dset.Tables(0)
Dim dc As System.Data.DataColumn
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
Dim row As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each ItemR In dt.Rows
Dim Columns As Integer = 0
For Each ItemC In dt.Columns
excel.Cells(row + 2, Columns + 1) = dt.Rows(row).Item(Columns).ToString
Columns = Columns + 1
Next
Columns = 0
row = row + 1
'Resttijd in sec en min
Dim Starttijd As Integer = 0
Dim Tijdloop As Integer = 0
Dim Seconden As Integer = 0
Dim Minuten As Integer = 0
Starttijd = (dt.Rows.Count).ToString
Tijdloop = (row).ToString
Seconden = FormatNumber(((Starttijd - Tijdloop) / 60 Mod 59), 0)
Minuten = FormatNumber(((Starttijd - Tijdloop) / (60) / (60) - (Seconden / 60)), 0)
Dialogexport.Label1.Text = "Export data to Excel Resterende tijd ongeveer " & Minuten & " Min " & Seconden & " Sec"
Try
If Minuten <= 0 Then
Dialogexport.Label1.Text = "Export data to Excel Resterende tijd ongeveer " & Seconden & " Sec"
End If
Catch ex As Exception
Seconden = FormatNumber(((Starttijd - Tijdloop) / 60 Mod 59), 0)
End Try
'Progresbar aansturing en label info
Dialogexport.ProgressBar1.Value = row.ToString / dt.Rows.Count.ToString * 100
Dialogexport.ProgressBar1.MarqueeAnimationSpeed = 100
'Dialog6.Label3.Text = "Moment geduld UW artikelen data word naar Excel gekopieerd"
Dialogexport.copiedlabel.Text = "Copied " & FormatNumber((row.ToString / dt.Rows.Count.ToString * 100), 0) & " %"
Dialogexport.labelcounting.Text = "Copied Data So Far :- " & row.ToString() - 1
Dialogexport.ProgressBar1.PerformStep() 'increment your bar with each record
Next
wSheet.Columns.AutoFit() 'Let op pas je pat aan
Dim strFileName As String = "C:\cataloog\export.xls"
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, FileFormat:=-4143)
excel.Workbooks.Open(strFileName)
excel.Visible = False
MsgBox("Export Cataloog gereed", MsgBoxStyle.Information, "Export Cataloog")
Dialogexport.ProgressBar1.Value = 0
Dialogexport.labelcounting.Text = Nothing
Dialogexport.copiedlabel.Text = Nothing
Me.Cursor = Cursors.Default
Dialogexport.Close()
Dim pProcess() As Process = System.Diagnostics.Process.GetProcessesByName("Excel")
For Each p As Process In pProcess
p.Kill()
Next
End Sub
