打开excel文件.xls.xlsx和.csv结尾的文件并且把里面的数据导入到DataGridViewvb.net语言代码
工具/原料
MicrosoftVisualBasic2010学习版
方法/步骤
1、打开MicrosoftVisualBasic2010Express
2、文件-新建项目-Windows窗体应用程序-确定
3、工具箱-所有windows窗体-Button，Combox，Label1和O圬桦孰礅penFileDialog1，在Form1上贴加1个B锇栀劐箨utton1按钮和Combox，Label1，OpenFileDialog1，并且设置DataGridView1的Anchor属性为Bottom,Left,Right
4、在代码的地方写入ImportsSystem.Data.OleDbImports讵症慧鹱System.IOPublicClassForm1Private_FileP锾攒揉敫athAsString=""Private_DSAsDataSetPrivateConst_SelectData="Select*from[<TableName>$]"PrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickMe.OpenFileDialog1.FileName=_FilePathOpenFileDialog1.Filter="AllFiles(*.*)|*.*|ExcelFiles(*.xls;*.xlsx)|*.xls;*.xlsx|CSVFiles(*.csv)|*.csv"OpenFileDialog1.FilterIndex=2IfNotMe.OpenFileDialog1.ShowDialog(Me)=vbOKThenExitSubEndIf_FilePath=Me.OpenFileDialog1.FileNameMe.Label1.Text=_FilePathDimfileAsFileInfo=NewFileInfo(_FilePath)DimextensionAsString=file.ExtensionDim_ConnectstringAsString=""Ifextension=".xlsx"Then_Connectstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=<FilePath>;ExtendedProperties=""Excel12.0Xml;HDR=NO;IMEX=1;READONLY=TRUE"""ElseIfextension=".xls"Then_Connectstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=<FilePath>;ExtendedProperties=""Excel8.0;HDR=NO;IMEX=1;READONLY=TRUE"""ElseIfextension=".csv"Then_Connectstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=<FilePath>;ExtendedProperties=""Text;HDR=NO;FORMAT=Delimited;READONLY=TRUE"""EndIfTryUsingcnAsOleDb.OleDbConnection=NewOleDbConnection(_Connectstring.Replace("<FilePath>",_FilePath))cn.Open()_DS=NewDataSetDimtbAsDataTable=cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,Nothing)Me.ComboBox1.Items.Clear()ForEachrAsDataRowIntb.RowsIfr("TABLE_TYPE")="TABLE"ThenMe.ComboBox1.Items.Add(r("TABLE_NAME").ToString.Replace("$",""))EndIfNextIfMe.ComboBox1.Items.Count>0ThenMe.ComboBox1.SelectedIndex=0EndIfEndUsingCatchexAsExceptionMsgBox(ex.Message)EndTryEndSubPrivateSubComboBox1_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBox1.SelectedIndexChangedTryDimTBNameAsString=Me.ComboBox1.SelectedItem.ToStringIf_DS.Tables.Contains(TBName)ThenMe.DataGridView1.DataSource=_DS.Tables(TBName)ExitSubEndIfDim_ConnectstringAsString=""'HDR=Yes，这代表第一行是标题，不做为数据使用'如果用HDR=No，则表示第一行不是标题，做为数据来使用IfMicrosoft.VisualBasic.Right(Me.Label1.Text,5)=".xlsx"Then_Connectstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=<FilePath>;ExtendedProperties=""Excel12.0Xml;HDR=NO;IMEX=1;READONLY=TRUE"""ElseIfMicrosoft.VisualBasic.Right(Me.Label1.Text,4)=".xls"Then_Connectstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=<FilePath>;ExtendedProperties=""Excel8.0;HDR=NO;IMEX=1;READONLY=TRUE"""ElseIfMicrosoft.VisualBasic.Right(Me.Label1.Text,4)=".csv"Then_Connectstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=<FilePath>;ExtendedProperties=""Text;HDR=NO;FORMAT=Delimited;READONLY=TRUE"""EndIfUsingcnAsOleDb.OleDbConnection=NewOleDbConnection(_Connectstring.Replace("<FilePath>",_FilePath))cn.Open()DimsqlAsString=_SelectData.Replace("<TableName>",TBName)UsingadAsOleDbDataAdapter=NewOleDbDataAdapter(sql,cn)ad.Fill(_DS,TBName)Me.DataGridView1.DataSource=_DS.Tables(TBName)EndUsingEndUsingCatchexAsExceptionMsgBox(ex.Message)EndTryEndSubEndClass
5、写完以上代码后，点击调试-启动调试。就可以打开excell把数据导入到datagridview中了。
