Add Rows Number to DataGridView

Private Sub ExDataGrid1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles ExDataGrid1.CellPainting
Dim sf As New StringFormat
sf.Alignment = StringAlignment.Center

If e.ColumnIndex < 0 AndAlso e.RowIndex >= 0 AndAlso e.RowIndex < ExDataGrid1.Rows.Count – 1 Then
e.PaintBackground(e.ClipBounds, True)
e.Graphics.DrawString((e.RowIndex + 1).ToString, Me.Font, Brushes.Black, e.CellBounds, sf)
e.Handled = True
End If

End Sub

Save DataGridView to Text file,CSV file

Public Sub SaveGridDataInFile(ByRef fName As String, ByVal ExDataGrid As DataGridView)
Dim I As Integer = 0
Dim j As Integer = 0
Dim cellvalue As String
Dim rowLine As String = “”
Try
Dim objWriter As New System.IO.StreamWriter(fName, True, System.Text.Encoding.Default)

For I = 0 To ExDataGrid.Columns.Count – 1
rowLine = rowLine & ExDataGrid.Columns(I).Name & “,”
Next
rowLine = rowLine.Remove(rowLine.Length – 1, 1)
objWriter.WriteLine(rowLine)
rowLine = “”
For j = 0 To (ExDataGrid.Rows.Count – 1)
For I = 0 To (ExDataGrid.Columns.Count – 1)
cellvalue = ExDataGrid.Item(I, j).Value.ToString
rowLine = rowLine + Chr(34) & cellvalue & Chr(34) + “,”
Next
rowLine = rowLine.Remove(rowLine.Length – 1, 1)
objWriter.WriteLine(rowLine)
rowLine = “”
Next
objWriter.Close()
objWriter.Dispose()
ExDataGrid.AllowUserToAddRows = True
MsgBox(“保存ファイル成功”)
Catch
End Try

End Sub

Read Text file, CSV file to DataGridView

Public Sub ReadCSVFile(ByVal sFolder As String, ByVal sFileName As String, ByVal MyDGV As DataGridView)
Dim ConnectionString, CommandText As String
Dim Command As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter(Command)
Dim myMainDataset As New DataSet
Dim BindSource As New BindingSource
ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & sFolder & “;Extended Properties=’text;HDR=Yes;FMT=Delimited’”
Dim Conn As New OleDb.OleDbConnection(ConnectionString)
CommandText = “select * from ” & “[" & sFileName & "]“
Conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
Command = New System.Data.OleDb.OleDbCommand(CommandText, Conn)
Conn.Open()
da = New OleDb.OleDbDataAdapter(CommandText, Conn)
myMainDataset = New DataSet
‘ fill dataset
da.Fill(myMainDataset, “MyTable”)
Conn.Close()
BindSource.DataSource = myMainDataset.Tables(“MyTable”)
‘datatabel(1) = myMainDataset.Tables(“MyTable”)
MyDGV.DataSource = BindSource
End Sub

Get XLS Sheet Name (VB2005)

Public Sub GetSheetNames(ByVal path As String, ByRef str() As String)
If IO.File.Exists(path) AndAlso IO.Path.GetExtension(path) = “.xls” Then
Dim app As New Excel.Application
Dim i As Integer = 0
Dim WB As Excel.Workbook
Try
WB = app.Workbooks.Open(Filename:=path)
If WB IsNot Nothing Then
For Each ws As Excel.Worksheet In WB.Sheets
str(i) = ws.Name
i = i + 1
Next
WB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(WB)
WB = Nothing
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
End Try
End If
End Sub