Привет!
выгружаю данные из DataTable в Excel с помощью ADO.Net (OleDB провайдером)
Числовые поля в Экселе выгружаются как текст...
как исправить???
Private Sub ReportBuilder(ByVal pathExcel As String, ByVal dtReportSource As DataTable)
Using dataCnn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathExcel & ";Extended Properties='Excel 12.0 Xml;HDR=YES';")
dataCnn.Open()
Dim dataDA As New OleDb.OleDbDataAdapter("select * from [Отчёт$]", dataCnn)
Dim dataCmdB As New OleDb.OleDbCommandBuilder(dataDA) With {.ConflictOption = ConflictOption.OverwriteChanges, .QuotePrefix = "[", .QuoteSuffix = "]"}
Dim dataCmd As OleDb.OleDbCommand = dataCmdB.GetInsertCommand()
''устанавливаем правильные типы
'For Each cmdParam As OleDb.OleDbParameter In dataCmd.Parameters
' Dim targetColumn As DataColumn = dtReportSource.Columns(cmdParam.SourceColumn)
' If targetColumn.DataType Is GetType(Decimal) Then
' cmdParam.ResetDbType()
' cmdParam.OleDbType = OleDb.OleDbType.Decimal
' ElseIf targetColumn.DataType Is GetType(Integer) Then
' cmdParam.ResetDbType()
' cmdParam.OleDbType = OleDb.OleDbType.Integer
' End If
'Next
For Each drTarget As DataRow In dtReportSource.Rows
For Each cmdParam As OleDb.OleDbParameter In dataCmd.Parameters
cmdParam.Value = drTarget.Item(cmdParam.SourceColumn)
Next
Try
dataCmd.ExecuteNonQuery()
Catch ex As Exception
End Try
Next
dataCnn.Close()
End Using
End Sub