从VB填充的excel数据单元格中删除前导0

本文关键字：删除 单元格 数据 VB 填充 excel | 更新日期: 2023-09-27 18:04:51

在我的vb Windows应用程序中，我使用以下代码创建excel报表:

Public Sub CreateExcelFile(ByVal InputDataTable As DataTable, ByVal FileName As String)
    Dim ExcelApp As New Microsoft.Office.Interop.Excel.ApplicationClass
    Dim ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
    Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
    Dim ColumnIndex As Integer = 0
    Dim RowIndex As Integer = 1
    Try
        ExcelWorkbook = ExcelApp.Workbooks.Add()
        ExcelWorkSheet = ExcelWorkbook.ActiveSheet()
        For Each c As DataColumn In InputDataTable.Columns
            ColumnIndex += 1
            ExcelApp.Cells(RowIndex, ColumnIndex) = c.ColumnName
        Next
        For Each r As DataRow In InputDataTable.Rows
            RowIndex += 1
            ColumnIndex = 0
            For Each c As DataColumn In InputDataTable.Columns
                ColumnIndex += 1
                ExcelApp.Cells(RowIndex, ColumnIndex) = r(c.ColumnName).ToString
            Next
        Next
        ExcelWorkSheet.Columns.AutoFit()
        ExcelWorkbook.SaveAs(FileName)
        ExcelWorkbook.Close()
        ExcelApp.Quit()
    Catch ex As Exception
        MsgBox("Err", MsgBoxStyle.Information + MsgBoxStyle.MsgBoxRtlReading)
    Finally
        ExcelApp = Nothing
        ExcelWorkbook = Nothing
        ExcelWorkSheet = Nothing
        ColumnIndex = Nothing
        RowIndex = Nothing
    End Try
End Sub

如果我有一个代码或电话号码，第一个字符是0，它不会显示在excel文件中。我用下面的代码来解决这个问题:

 ExcelWorkSheet.Activate()
      ExcelWorkSheet.Cells().Columns.NumberFormat = "@"
      ExcelWorkSheet.Cells().EntireColumn.NumberFormat = "@"

但它不起作用。我读了这个问题，但不能解决我的问题:

将Excel列(或单元格)格式化为c#中的文本?

使用Microsoft.Office.Interop.Excel在c#中设置数据类型，如数字、文本和日期

使用Microsoft Access Database Engine通过OleDb插入数据表

从VB填充的excel数据单元格中删除前导0

在单元格中有数据后设置其格式并不总是有效，因为在转换为原始数据格式时可能会丢失一些数据。请确保先设置单元格的格式，这样任何前导零都不会被截断。

相关文章：