利用VB.NET实现导出DataTable数据到excel中，各位大侠帮帮忙，最好有源码，谢谢，江湖救急

刚好写了个Helper类，你试验一下DataTable2Exce(这个方法代码如下：

员工经过长期磨合与沉淀，具备了协作精神，得以通过团队的力量开发出优质的产品。成都创新互联公司坚持“专注、创新、易用”的产品理念，因为“专注所以专业、创新互联网站所以易用所以简单”。公司专注于为企业提供成都网站制作、做网站、微信公众号开发、电商网站开发，小程序开发，软件定制开发等一站式互联网企业服务。

Imports System.IO

Imports System.Data

Imports System.Data.OleDb

Public MustInherit Class ExcelHelper

Private Shared Function buildConnStr(excelFilePath As String) As String

Dim excelFileInfo As New System.IO.FileInfo(excelFilePath)

Dim constr As String

If excelFileInfo.Extension = ".xlsx" Then

constr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'", excelFilePath)

Else

constr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", excelFilePath)

End If

Return constr

End Function

'将datatable导入到excel

Public Shared Function DataTable2Excel(dt As DataTable, excelFilePath As String) As Boolean

If File.Exists(excelFilePath) Then

Throw New Exception("该文件已经存在！")

End If

If dt.TableName.Trim.Length = 0 Or dt.TableName.ToLower = "table" Then

dt.TableName = "Sheet1"

End If

Dim colCount As Integer = dt.Columns.Count

Dim pa(colCount - 1) As OleDb.OleDbParameter

Dim tableStructStr As String = "Create Table " dt.TableName "("

Dim connString As String = buildConnStr(excelFilePath)

Dim objconn As New OleDbConnection(connString)

Dim objcmd As New OleDbCommand

objcmd.Connection = objconn

Dim dataTypeList As New ArrayList

dataTypeList.Add("System.Decimal")

dataTypeList.Add("System.Double")

dataTypeList.Add("System.Int16")

dataTypeList.Add("System.Int32")

dataTypeList.Add("System.Int64")

dataTypeList.Add("System.Single")

Dim i As Integer = 0

For Each col As DataColumn In dt.Columns

If dataTypeList.IndexOf(col.GetType.ToString) 0 Then

pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.Double)

objcmd.Parameters.Add(pa(i))

If i + 1 = colCount Then

tableStructStr += col.ColumnName + " double)"

Else

tableStructStr += col.ColumnName + " double,"

End If

Else

pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.VarChar)

objcmd.Parameters.Add(pa(i))

If i + 1 = colCount Then

tableStructStr += col.ColumnName + " VarChar)"

Else

tableStructStr += col.ColumnName + " VarChar,"

End If

End If

i += 1

Next

Try

objcmd.CommandText = tableStructStr

If objconn.State = ConnectionState.Closed Then objconn.Open()

objcmd.ExecuteNonQuery()

Catch ex As Exception

Throw ex

End Try

Dim InsertSql_1 As String = "Insert into " + dt.TableName + " ("

Dim InsertSql_2 As String = " Values ("

Dim InsertSql As String = ""

For colID As Integer = 0 To colCount - 1 Step 1

If colID + 1 = colCount Then

InsertSql_1 += dt.Columns(colID).ColumnName ")"

InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ")"

Else

InsertSql_1 += dt.Columns(colID).ColumnName + ","

InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ","

End If

Next

InsertSql = InsertSql_1 + InsertSql_2

For rowID As Integer = 0 To dt.Rows.Count - 1 Step 1

For colID = 0 To dt.Columns.Count - 1

If pa(colID).DbType = DbType.Double And dt.Rows(rowID)(colID).ToString.Trim = "" Then

pa(colID).Value = 0

Else

pa(colID).Value = dt.Rows(rowID)(colID).ToString.Trim

End If

Next

Try

objcmd.CommandText = InsertSql

objcmd.ExecuteNonQuery()

Catch ex As Exception

Throw ex

End Try

Next

Try

If objconn.State = ConnectionState.Open Then objconn.Close()

Catch exp As Exception

Throw exp

End Try

Return True

End Function

' 获取Excel文件数据表列表Sheets

Public Shared Function GetExcelTables(ExcelFileName As String) As ArrayList

'Dim sheets As New List(Of String)

'conn.Open()

'Dim dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

'For Each r In dt.Rows

' sheets.Add(r("TABLE_NAME"))

'Next

'conn.Close()

'Return sheets

Dim dt As DataTable

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

Return Nothing

End If

Dim tableList As New ArrayList

Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))

Try

conn.Open()

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

Catch ex As Exception

Throw ex

End Try

For i As Integer = 0 To dt.Rows.Count - 1

Dim tableName As String = dt.Rows(i)(2).ToString.Trim.TrimEnd("$")

If tableList.IndexOf(tableName) 0 Then tableList.Add(tableName)

Next

End Using

Return tableList

End Function

'将Excel文件导出至DataTable(第一行作为表头)

Public Shared Function InputFromExcel(ExcelFileName As String, TableName As String) As DataTable

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

End If

Dim tableList As ArrayList = GetExcelTables(ExcelFileName)

If tableList.IndexOf(TableName) 0 Then

TableName = tableList(0).ToString.Trim

End If

Dim dt As New DataTable

Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))

Dim cmd As New OleDbCommand("select * from [" TableName "$]", conn) '调试是否需要$

Dim adapter As New OleDbDataAdapter(cmd)

Try

If conn.State = ConnectionState.Closed Then conn.Open()

adapter.Fill(dt)

Catch ex As Exception

Throw ex

Finally

If conn.State = ConnectionState.Open Then conn.Close()

End Try

Return dt

End Function

'查询excel文件中的一个数据

Public Shared Function ReadOneDataFromExcel(ExcelFileName As String, TableName As String, sql As String) As Object

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

End If

Dim tableList As ArrayList = GetExcelTables(ExcelFileName)

If tableList.IndexOf(TableName) 0 Then

TableName = tableList(0).ToString.Trim

End If

Dim dt As New DataTable

Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))

Dim cmd As New OleDbCommand(sql, conn) '调试是否需要$

Dim ret As Object

Try

If conn.State = ConnectionState.Closed Then conn.Open()

ret = cmd.ExecuteScalar()

Catch ex As Exception

Throw ex

Finally

If conn.State = ConnectionState.Open Then conn.Close()

End Try

Return ret

End Function

'获取Excel文件指定数据表的数据列表columnNames

Public Shared Function GetExcelTableColumns(ExcelFileName As String, TableName As String) As ArrayList

Dim dt As DataTable

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

Return Nothing

End If

Dim ColList As New ArrayList

Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))

Try

conn.Open()

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName, Nothing})

Catch ex As Exception

Throw ex

End Try

For i As Integer = 0 To dt.Rows.Count - 1

Dim ColName = dt.Rows(i)("Column_Name").ToString().Trim()

ColList.Add(ColName)

Next

End Using

Return ColList

End Function

End Class

我用VB.net做了个注册表连接到SQL的数据库，可是有问题说conn.open()打不开，谁能帮我解决一下，急急急 …

你这明显是与数据库连接出的错！

1.确认数据库名是否是buy？

2.将连接字符串中的Data Source改为127.0.0.1或者local。

3.去掉 Integrated Security = true 这句，加入user=用户名; pwd=密码; 。

.net 有一个数组 怎么转换成 datatable 然后插入数据库

var numbers = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,22,23 };

//numbers = new int[] { 1, 2, 3, 4, 5, 6,7};

var len = 7;        //截取列

var dtTable = new DataTable();

//动态创建列

for (int i = 0; i  len; i++)

{

dtTable.Columns.Add("num"+(i+1));

}

DataRow dr1 = dtTable.NewRow();

for (int i = 0; i  numbers.Length; i++)

{

int numIndex = (i + 1) % len;

if (numIndex == 0)

{

numIndex = len;

}

var dtColumnsName = "num" + numIndex;

dr1[dtColumnsName] = numbers[i].ToString();

if ((i + 1) % len == 0)

{

dtTable.Rows.Add(dr1);

dr1 = dtTable.NewRow();

}

}

if(numbers.Length% len != 0)

dtTable.Rows.Add(dr1);

//DataTable 插入 数据库略

vb.net 查询数据库中一字段中二个字符中间的值

DataTable dt;//假设 dt的第一列存储的就是这个str1_1，str2_1，str3_1，str4_1,则

foreach(DataRow dr in dt.Rows)

{

String s=dr[0].ToString();

String[] strs=s.split('_');//得到 str1,1 形式的数组(str2,1 str3,1 str4 ,1 )

String s2=strs[0];//取数组第一个，如 str1,str2,str3,str4 等

String[] strs2=s2.split('r');//得到st,1形式的数字

String 结果=strs2[1];//取第一个，得到如 1,2,3,4等结果就是lz需要的

}

网页名称：vb.netdr转dt vb60转net
文章出自：http://scgulin.cn/article/hgghdi.html