vb.net - 在通过 vb.net 导出到 Excel(从 MS Access)期间将 TRUE FALSE 转换为 1 和 0

Question

在尝试通过 VB.NET 将我的表从 Access 导出到 Excel 时，我希望 TRUE 和 FALSE 值在我的 Excel 工作表中显示为 1 和 0。下面是我在excel中导出和写入数据的代码。
If(.Cells(d, e).value = True, 1, 0)引发错误“conversion from type string to Boolean is not valid '。我想这是因为我的 Access 表中也有“字符串”数据。有人可以帮忙吗。

  Dim e As Integer = 1
            For col = 0 To ComDset2.Tables(0).Columns.Count - 1
                d = 2
                For row = 0 To ComDset2.Tables(0).Rows.Count - 1
                    .Cells(d, e).Value = ComDset2.Tables(0).Rows(row).ItemArray(col)
                    IIf(.Cells(d, e).value = True, 1, 0)

                    d += 1
                Next
                e += 1
            Next

Answer 1

正如@varocarbas 在您问题下方的评论中所提到的，您将意识到代码中的错误是什么。

The IIf(.Cells(d, e).value = True, 1, 0) line assumes that the Cell value is of Boolean type what is not the case (Excel cells are always String). The way to avoid this problem is either treating the cell as a string (IIf(.Cells(d, e).value.ToString().ToLower() = "true", 1, 0)) or converting the cell into Boolean (via Convert.ToBoolean

但是，不要在循环中替换它，而是在 ONE GO 中的循环之外进行。

在 VB.NET 2010 + EXCEL 2010 中尝试和测试(叹气)

 rng.Replace(What:="TRUE", Replacement:="1", LookAt:=Excel.XlLookAt.xlWhole, _
 SearchOrder:=Excel.XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False, _
 ReplaceFormat:=False)

 rng.Replace(What:="FALSE", Replacement:="0", LookAt:=Excel.XlLookAt.xlWhole, _
 SearchOrder:=Excel.XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False, _
 ReplaceFormat:=False)

来自评论的更多跟进

看这个例子

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        '~~> Define your Excel Objects
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")
        xlWorkSheet = xlWorkBook.Sheets(1)

        Dim i As Integer = 1
        Dim d As Integer = 0
        Dim startRow As Integer = 0

        With xlWorkSheet
            For col = 0 To ComDset2.Tables(0).Columns.Count - 1
                d = 2
                startRow = d

                For Row = 0 To ComDset2.Tables(0).Rows.Count - 1
                    .Cells(d, e).Value = ComDset2.Tables(0).Rows(Row).ItemArray(col)
                    d += 1
                Next

                '~~> Create your range here
                Dim rng As Excel.Range = .Range(.Cells(startRow, i), .Cells(d - 1, i))

                rng.Replace(What:="TRUE", Replacement:="1", LookAt:=Excel.XlLookAt.xlWhole, _
                SearchOrder:=Excel.XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False)

                i += 1
            Next
        End With

    End Sub
End Class