vb.net中从SQL数据表生成Excel文档

Question

在我的项目中，我有一个汇总数据表，我想将其导出到 Excel 文档中，为此我部署了以下代码：

Public Shared Function Main() As Integer
        Dim exc As New Application

    exc.Visible = True
        Dim workbooks As Excel.Workbooks = exc.Workbooks
        Dim workbook As Excel._Workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet)
        Dim sheets As Sheets = workbook.Worksheets
        Dim worksheet As _Worksheet = CType(sheets.Item(1), _Worksheet)
        exc.DataEntryMode = True
        Dim range1 As Range = worksheet.Range("C1", Missing.Value)
        Const nCells As Integer = 5
        Dim args1(1) As [Object]
        args1(0) = nCells
        range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range1, args1)
        Return 100
    End Function

当我尝试执行该行时：

range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range1, args1)

我收到以下错误： 调用目标已引发异常
此时我不知道问题出在哪里。有人可以帮助我吗？

Answer 1

不要担心尝试使用调用成员。

要将一系列值填充到 Excel 文档中，您可以使用类似的方法

dim row as integer = 1
dim column as integer = 1


Worksheet.Cells(row,column) = "My value here"
'or this
Worksheet.Cells("A1").Value = "my Value Here"
' or this
Worksheet.Range("A1") = "My value here"

尝试从在 Excel 内部执行此操作的角度来查看它，然后遵循相同的模式。

编辑
对于文本对齐选项，例如自动换行等。

dim rng as Range = Worksheet.Range("A1")
With rng
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
end With