VB.net Excel 导出保留数据前面的0不被清除
'导入命名空间
Imports Microsoft.Office.Interop
’
Dim xlsApp As New Excel.Application
Dim xlsBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Try
xlsApp = CreateObject("Excel.Application") '生成Excel
xlsBook = xlsApp.Workbooks.Add
xlsSheet = xlsBook.Worksheets(1)
xlsSheet.Columns.AutoFit() '自动换行
xlsSheet.Name = "Sheet1"
Dim RowCount As Integer
Dim ColCount As Integer
Dim k As Integer
RowCount = DataGridView3.Rows.Count
ColCount = DataGridView3.Columns.Count
For k = 0 To ColCount - 1 'DataGridView所有列数
xlsSheet.Cells(1, k + 1) = DataGridView3.Columns(k).HeaderText
xlsSheet.Cells(1, k + 1).Font.Size = 12
xlsSheet.Cells(1, k + 1).Font.FontStyle = "加粗"
xlsSheet.Cells(1, k + 1).HorizontalAlignment = 1
Next
Dim i As Integer, j As Integer
Me.ProgressBar1.Value = 0
Me.ProgressBar1.Minimum = 0
Me.ProgressBar1.Maximum = RowCount - 2
For i = 0 To RowCount - 2 '向Excel填充数据
Me.ProgressBar1.Value = i
For j = 0 To ColCount - 1
If IsDBNull(DataGridView3.Item(j, i).Value.ToString) Then
xlsSheet.Cells(i + 2, j + 1) = "〔空值〕"
ElseIf Len(DataGridView3.Item(j, i).Value.ToString.trim) = 4 And mid(DataGridView3.Item(j, i).Value.ToString, 1, 1) = "0" Then
xlsSheet.Cells(i + 2, j + 1).NumberFormatLocal = "@" ‘将单元格格式化为文本型
xlsSheet.Cells(i + 2, j + 1) = IIf(IsDBNull(DataGridView3.Item(j, i).Value.ToString), 0, "" & DataGridView3.Item(j, i).Value.ToString.Trim & "")
Else
xlsSheet.Cells(i + 2, j + 1) = IIf(IsDBNull(DataGridView3.Item(j, i).Value.ToString), 0, DataGridView3.Item(j, i).Value.ToString)
End If
'/////////////////
Next
Next
MsgBox("共导出" & RowCount & "条数据" & vbCrLf & "已经成功导出到:" & strFName & "", MsgBoxStyle.OkOnly, "温馨提示")
Me.ProgressBar1.Value = 0
xlsBook.SaveAs(strFName) '保存上面生成excel
xlsSheet = Nothing '释放资源
xlsBook.Close() '关闭工作簿
xlsBook = Nothing '释放资源
xlsApp.Quit() '退出excel应用程序
xlsApp = Nothing
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End If