Mutil Language
- Create New project
- Add two file Resoure ( English and japanese ) Name Resource.resx Resource.ja-JP.resx
Public Class Form1
Public Sub New()
System.Threading.Thread.CurrentThread.CurrentUICulture = Threading.Thread.CurrentThread.CurrentCulture
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Label1.Text = My.Resources.Resource.Name
Label2.Text = My.Resources.Resource.Adress
Label3.Text = My.Resources.Resource.Room
End Sub
End Class
DataGridView Export to XLS File
Project -> Brown -> add -> COM Microsoft Excel 11.0 Object Library
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Data.OleDb
Imports System.Text
Imports System.Runtime.InteropServices
Public Class Form1
Const con As String = “Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Persist Security Info=False”
Const SQLExpression As String = “SELECT CustomerID AS ID, CompanyName AS Company,City, Region, Country FROM Customers”
Dim adp As SqlDataAdapter
Dim ds As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim cn As New SqlConnection(con)
Dim cmd As New SqlCommand(SQLExpression, cn)
cmd.CommandType = CommandType.Text
cn.Open()
adp = New SqlDataAdapter(cmd)
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adp)
ds = New DataSet
adp.Fill(ds, “XL”)
Me.DataGridView1.DataSource = ds.Tables(“XL”).DefaultView
cn.Close()
cn = Nothing
End Sub
Private Sub Update_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Update_Btn.Click
With Me
.Validate()
.adp.Update(Me.ds.Tables(“XL”))
.ds.AcceptChanges()
End With
End Sub
Private Sub Close_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Close_Btn.Click
ds.Dispose()
adp.Dispose()
ds = Nothing
adp = Nothing
Me.Close()
End Sub
Private Sub Export_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Export_Btn.Click
Dim RowsCount As Int32 = Me.DataGridView1.SelectedRows.Count – 1
If RowsCount > -1 Then
‘The array for field names.
Dim FldNames() As String = {“ID”, “Company”, “City”, “Region”, “Country”}
‘The array for the selected records.
Dim DataArr(RowsCount, 4) As Object
Dim ColsCounter As Int32 = 0
‘Populate the data array – The list is sorted in ascending order.
For RowsCounter As Int32 = 0 To RowsCount
For Each cell As DataGridViewCell In Me.DataGridView1 _
.SelectedRows(RowsCount – RowsCounter) _
.Cells
DataArr(RowsCounter, ColsCounter) = cell.Value
ColsCounter = ColsCounter + 1
Next
ColsCounter = 0
Next
‘Variables for Excel.
Dim xlApp As New Excel.Application
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _
Excel.XlWBATemplate.xlWBATWorksheet)
Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)
Dim xlCalc As Excel.XlCalculation
‘Save the present setting for Excel’s calculation mode and turn it off.
With xlApp
xlCalc = .Calculation
.Calculation = Excel.XlCalculation.xlCalculationManual
End With
‘Write the field names and the data to the targeting worksheet.
With xlWSheet
.Range(.Cells(1, 1), .Cells(1, 5)).Value = FldNames
.Range(.Cells(2, 1), .Cells(RowsCount + 2, 5)).Value = DataArr
.UsedRange.Columns.AutoFit()
End With
With xlApp
.Visible = True
.UserControl = True
‘Restore the calculation mode.
.Calculation = xlCalc
End With
‘Relase objects from memory.
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing
GC.Collect()
End If
End Sub
End Class
Thread in VB NET 2005
Imports System.Threading
Public Class Form1
Dim tCounter As New Thread(AddressOf RunCounter)
Private Sub RunCounter()
Dim Counter As Integer
Do
setlabel(Counter & “/” & 10000)
Counter += 1
Loop While Counter < 10000
End Sub
Public Delegate Sub setlabeldelegate(ByVal text As String)
Private Sub setlabel(ByVal text As String)
If InvokeRequired Then
Invoke(New setlabeldelegate(AddressOf set_label), New Object() {text})
End If
set_label(text)
End Sub
Private Sub set_label(ByVal text As String)
Label1.Text = text
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If tCounter.IsAlive = False Then
tCounter.Start()
End If
End Sub
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
tCounter.Abort()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
End Class
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
Imports Data from XLS file to DataGridView VB 2005
I have XLS file which have format
| Adress | Name | Adress | Name | Adress | Name |
| Adr1 | Name1 | Adr2 | Name2 | Adr3 | Name3 |
| Adr4 | Name4 | Adr5 | Name5 | Adr6 | Name6 |
this data is read and input DataGridView which have format
| Adress | Name |
| Adr1 | Name1 |
| Adr2 | Name2 |
| Adr3 | Name3 |
| Adr4 | Name4 |
| Adr5 | Name5 |
| Adr6 | Name6 |
Dim strConnectionString As String = String.Empty
strConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FILES\Test.xls;Extended Properties=”"Excel 8.0;HDR=No;IMEX=1″”"
Dim cnCSV As New OleDbConnection(strConnectionString)
cnCSV.Open
Dim cmdSelect As New OleDbCommand(“SELECT F1, F2 FROM [Sheet1$] union (SELECT F3, F4 FROM [Sheet1$] union SELECT F5, F6 FROM [Sheet1$])“, cnCSV)
Dim daCSV As New OleDbDataAdapter
daCSV.SelectCommand = cmdSelect
Dim dtCSV As New DataTable
daCSV.Fill(dtCSV)
cnCSV.Close
Me.dataGridView1.DataSource = dtCSV
Select SQL Column name contains “.” characters
Select [Co#lumn] from myTable
