返回目录:excel表格制作
这里是我用的代码,估计对你有用:
'On Error Resume Next
Dim fileadd As String
CommonDialog1.ShowOpen
CommonDialog1.Filter = "xls文件(*.xls)|*.xls" '选择你要的文件
fileadd = CommonDialog1.FileName
If fileadd = "" Then Exit Sub
Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open(fileadd) '打开已经存在的EXCEL工件簿文件
xlApp.Visible = False ' = True '设置EXCEL对象可见(或不可见)
Set xlSheet = xlBook.Worksheets(1) '设置活动工作表
For R = 1 To 99999 '行循环
If LTrim(RTrim(xlBook.Worksheets(1).Cells(R, 1))) <> "" Then
sybw.Adodc3.Refresh
sybw.Adodc3.Recordset.Find "ShiGongBuWei_Name='" & LTrim(RTrim(xlBook.Worksheets(1).Cells(R, 1))) & "'"
If sybw.Adodc3.Recordset.EOF Then
sybw.Adodc3.Recordset.AddNew
sybw.Adodc3.Recordset!ShiGongBuWei_Name = LTrim(RTrim(xlBook.Worksheets(1).Cells(R, 1)))
sybw.Adodc3.Recordset!FenXiangGongCheng_ID = bb
sybw.Adodc3.Recordset.Update
sybw.Adodc3.Refresh
' Call log(MM_Users_NameTrue, "增加了施工部位", MM_Companys_ID)
Else
' MsgBox " 施工部位重复! ", vbOKOnly, "用户信息"
End If
Else
R = 99999 + 1
End If
Next R
xlApp.DisplayAlerts = False '不进e79fa5e98193e4b893e5b19e338行安全提示 '
Set xlSheet = Nothing '
Set xlBook = Nothing '
xlApp.Quit '
Set xlApp = Nothing
private void GetDataFromExcelWithAppointSheetName(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//返回7a64e59b9ee7ad94333Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
dataGridView2.DataSource = dt; //绑定到界面
}
其中,Path是Excel文件的路径
把dataGridView2换成listview试试
Option Explicit
Dim data As New ADODB.Connection
Dim db As New ADODB.Recordset
Dim xlsApp As Excel.Application
Dim xlsBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Private Sub Command1_Click()
On Error GoTo ErrHandler
CommonDialog1.DialogTitle = "Open files"
CommonDialog1.Filter = "mdb files(*.mdb)|*.mdb"
CommonDialog1.Flags = 4 '取消 “以只读方式打开” 复选框
CommonDialog1.ShowOpen
CommonDialog1.CancelError = True
If Len(CommonDialog1.FileName) <= 4 Then
Exit Sub
Else
Text1.Text = CommonDialog1.FileName
End If
ErrHandler:
Exit Sub
End Sub
Private Sub Command2_Click()
Dim NoExistF As New FileSystemObject
Dim i, j, k As Double
'Excel行i 列j,从第二行开始,去掉标题行
i = 2
j = 1
k = 1 'Access列号,第0列留着放主键
If NoExistF.FileExists(Text1.Text) = False Or NoExistF.FileExists(Text2.Text) = False Then
MsgBox "文件不存在7a64e4b893e5b19e361!", 16, "错误提示"
Exit Sub
Else
'打开Access数据库
data.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Text1.Text & ";Persist Security Info=False"
db.Open "select * From sheet", data, adOpenKeyset, adLockOptimistic '数据库表的名字sheet
'打开Excel数据表
Set xlsApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlsBook = xlsApp.Workbooks.Open(Text2.Text) '打开已经存在的EXCEL工件簿文件
Set xlsSheet = xlsBook.Worksheets("Sheet1") '设置活动工作表
Do
If xlsSheet.Cells(i, j) = "" Then '姓名=空 的时候,结束循环
Exit Do
End If
db.AddNew
db.Fields(k) = xlsSheet.Cells(i, j)
db.Fields(k + 1) = xlsSheet.Cells(i, j + 1)
db.Fields(k + 2) = xlsSheet.Cells(i, j + 2)
db.MoveNext
i = i + 1
Loop
End If
db.MovePrevious
db.Update
db.Close
data.Close
MsgBox "数据传输完毕!", , "提示"
Set xlsSheet = Nothing
xlsBook.Close
Set xlsBook = Nothing
xlsApp.Quit
Set xlsApp = Nothing
End Sub
Private Sub Command3_Click()
On Error GoTo ErrHandler
CommonDialog1.DialogTitle = "Open files"
CommonDialog1.Filter = "xls files(*.xls)|*.xls"
CommonDialog1.Flags = 4 '取消 “以只读方式打开” 复选框
CommonDialog1.ShowOpen
CommonDialog1.CancelError = True
If Len(CommonDialog1.FileName) <= 4 Then
Exit Sub
Else
Text2.Text = CommonDialog1.FileName
End If
ErrHandler:
Exit Sub
End Sub
Private Sub Form_Load()
Text1.Text = ""
Text2.Text = ""
End Sub
百度一zd下大把例子,非要来这内里提问。
VB 读取Excel表格数据并容在ListView控件中显示
http:///Code/VB/WJCZ/Excel_616.html