VB.NET中DataGridView相關

DataGridView相關

• • 查詢資料庫資料顯示到DataGridView
  • excel檔案資料導入到DataGridView
  • DataGridView全選CheckBox
  • DataGridView中資料導出到Excel

查詢資料庫資料顯示到DataGridView

// 彈出提示框
Dim CheckU As DataTable
SQL = "select b.UserNo,b.UserName,a.deptName as deptName,a.deptId as deptId from SYS_Dept a inner join SYS_UserInfo b on a.deptid=b.deptid where  b.userno ='" & TextBox1.Text & "'"
CheckU = New DataTable
CheckU = ConnectDB.dataReadSelect(SQL)
If (CheckU.Rows.Count > 0) Then  ' 如果有資料 
    TextBox2.Text = CheckU.Rows(0)("UserNo").ToString
    TextBox3.Text = CheckU.Rows(0)("UserName").ToString
    TextBox4.Text = CheckU.Rows(0)("deptName").ToString
    TextBox5.Text = CheckU.Rows(0)("deptId").ToString
End If
           

excel檔案資料導入到DataGridView

// EXCEL導入按鈕
Dim excel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim fileDialog As OpenFileDialog = New OpenFileDialog()
Dim FileName As String
Dim row_count As Integer 'excel檔案中資料共多少行

'fileDialog.Filter = "Microsoft Excel files (*.xlsm)|*.xlsm"
fileDialog.Filter = "所有檔案(* .*)|*.*"
If fileDialog.ShowDialog = Windows.Forms.DialogResult.Cancel Then 
	Exit Sub
If fileDialog.FileName = Nothing Then
    MsgBox("請選擇要導入的excel檔案", , "提示")
End If

FileName = fileDialog.FileName
xlBook = excel.Application.Workbooks.Open(FileName)
xlSheet = xlBook.Application.Worksheets(2)
row_count = xlSheet.UsedRange.Rows(xlSheet.UsedRange.Rows.Count).Row
If (row_count = 2) Then
	MessageBox.Show("excel中沒有資料") '目前Excel中資料從第二行開始
    Exit Sub
End If

Dim col As Integer = 0
Dim i As Integer = 3

Me.DataGridView1.Rows.Clear()'清除DataGridView表格資料

Do While i <= row_count
	DataGridView1.Rows.Add()
	DataGridView1.Rows(col).Cells(1).Value = xlSheet.Cells(i, 1).value
	DataGridView1.Rows(col).Cells(2).Value = xlSheet.Cells(i, 2).value
	DataGridView1.Rows(col).Cells(3).Value = xlSheet.Cells(i, 3).value
	DataGridView1.Rows(col).Cells(4).Value = xlSheet.Cells(i, 4).value
	DataGridView1.Rows(col).Cells(5).Value = xlSheet.Cells(i, 5).value
	i = i + 1
	col = col + 1
Loop
excel.ActiveWorkbook.Close(False)
xlSheet = Nothing
xlBook = Nothing
excel = Nothing

If DataGridView1.Rows.Count >= 1 Then
	MessageBox.Show("導入成功")
Else
	MessageBox.Show("導入失敗")
End If
           

DataGridView全選CheckBox

// 添加一個CheckBox
// CheckBox的點選事件
// DataGridView第一列的ColumnType設定為DataGridViewCheckBoxColumn
Dim i As Integer
Dim count As Integer
count = DataGridView1.Rows.Count
If (CheckBox1.Checked) Then '全選
    For i = 0 To count - 1
        DataGridView1.Rows(i).Cells(0).Value = True
    Next
End If
If (CheckBox1.Checked = False) Then '取消全選
    For i = 0 To count - 1
        DataGridView1.Rows(i).Cells(0).Value = False
    Next
End If
           

DataGridView中資料導出到Excel

// DataGridView中右鍵導出按鈕
// 1.添加ContextMenuStrip控件
// 2.控件上添加導出按鈕
// 3.DataGridView的ContextMenuStrip屬性中添加此控件
// ContextMenuStrip控件點選事件：
Dim Xls As New Microsoft.Office.Interop.Excel.Application() '定義excel應用程式
Dim Xlsbook As Microsoft.Office.Interop.Excel.Workbook '定義工作簿
Dim Xlssheet As Microsoft.Office.Interop.Excel.Worksheet '定義工作表

'Xls.Visible = True '顯示excel程式
'Xlsbook = Xls.Application.Workbooks.Add '添加新工作簿 或exbook=exapp.workbooks.open("路徑\檔案名")'打開已存在工作薄
Xlssheet = Xlsbook.Sheets.Add(After:=Xlsbook.Sheets(Xlsbook.Sheets.Count)) '添加到最後
Xlssheet = Xlsbook.Sheets(1) '第1個工作表的控制句柄

Dim Cols As Integer
For Cols = 1 To DataGridView1.Columns.Count
	Xlssheet.Cells(1, Cols) = DataGridView1.Columns(Cols - 1).HeaderText
Next
Dim i As Integer
For i = 0 To DataGridView1.RowCount - 1
	Dim j As Integer
    For j = 0 To DataGridView1.ColumnCount - 1
    	If Me.DataGridView1(j, i).Value Is System.DBNull.Value Then
            Xlssheet.Cells(i + 2, j + 1) = ""
        Else
        	Xlssheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString
        End If
    Next j
Next i
Dim Sdlg As SaveFileDialog = New SaveFileDialog '定義一個儲存對話框
Sdlg.FileName = "導出檔案" & Format(DateTime.Now, "yyyyMMdd") '儲存對話框的預設檔案名
Sdlg.Filter = "Excel files (*.xlsx)|*.xlsx|Excel files (*.xls)|*.xls" '過濾器設定
Sdlg.RestoreDirectory = True '儲存對話框是否記憶上次打開的目錄
Sdlg.CreatePrompt = False '不提示是否建立檔案
Sdlg.Title = "導出Excel檔案到"
Sdlg.AddExtension = True
Sdlg.OverwritePrompt = False
Sdlg.FilterIndex = 1 '設定預設檔案類型顯示順序（可以不設定）
If Sdlg.ShowDialog = Windows.Forms.DialogResult.OK Then '如果儲存對話框的确認按鈕被觸發
	'Xlsbook.DisplayAlerts = False
	'需要Imports System.IO
    If (File.Exists(Sdlg.FileName)) Then
    	If (MessageBox.Show("檔案已存在，要替換它嗎？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) = Windows.Forms.DialogResult.Yes) Then
            File.Delete(Sdlg.FileName)
        Else
        	Exit Sub
        End If
    End If
    Xlsbook.SaveAs(Sdlg.FileName) '儲存檔案
End If
Xlssheet = Nothing '釋放資源
Xlsbook.Close() '關閉工作簿
Xlsbook = Nothing '釋放資源
Xls.Quit() '退出excel應用程式
Xls = Nothing
MessageBox.Show("導出成功")