VB.net 读取或写入 Excel

c999000 2017-06-03 08:55:06





'根据电脑已安装office版本,先引用

'下面的代码为VB.net 读取或写入 Excel
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ExcelApp As Excel.Application '定义Excel的Application
Dim wkBook As Excel.Workbook '定义Excel的Application
Dim wkSheet As Excel.Worksheet
On Error Resume Next
ExcelApp = GetObject(, "Excel.application") '"Excel.application"
wkBook = ExcelApp.ActiveWorkbook '"workbooks"
wkSheet = wkBook.ActiveSheet
If ExcelApp.Workbooks.Count = 0 Then '注:如果新建未保存的工作簿不算打开
MsgBox("没有工作簿打开")
'Exit Sub
ExcelApp = CreateObject("Excel.application")
wkBook = ExcelApp.Workbooks.Add
wkSheet = wkBook.Worksheets.Add
ExcelApp.Visible = True
End If
With wkSheet
For i = 1 To 10
For j = 1 To 10
.Cells(i, j) = i
Next j
Next i
.Range("A1").Value = "Excel 表格控制,好玩吗"
.Cells(6, 6).Value = "Excel 表格控制"
End With
'ExcelSheet.SaveAs("F:\test.xlsx")
ExcelApp.ScreenUpdating = True
wkSheet = Nothing
wkBook = Nothing
ExcelApp = Nothing
End Sub
End Class



...全文
1889 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
zjcat35 2018-08-30
  • 打赏
  • 举报
回复
Imports System.IO
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports System.Windows.Forms

Public Class DataToExcel
Public Shared Sub ExportXLs(ByVal data As DataGridView) '导出数据至excel
Dim MyfilePath As String '定义保存路径变量
Dim saveFileDialog As New SaveFileDialog '另存为对话框
If data.Rows.Count = 0 Then '判断窗口是否有可导出数据
MsgBox("无可导数据!")
'Return 0
Exit Sub
Else
With saveFileDialog
.DefaultExt = "*.xls"
.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls"
.FilterIndex = 1
.FileName = "新建文件夹"
If .ShowDialog() = DialogResult.OK Then
MyfilePath = .FileName '赋值文件保存路径
Else
'Return 0
Exit Sub
End If
End With
End If
Try
Dim workbook As HSSFWorkbook = New HSSFWorkbook() '声明工作簿变量
Dim sht As HSSFSheet = workbook.CreateSheet("Sheet1") '声明工作表变量
With data
Dim row As HSSFRow = sht.CreateRow(0)
For j = 0 To .Columns.Count - 1
row.CreateCell(j).SetCellValue(.Columns(j).HeaderText.ToString) '将Datagridview表头写入工作表
Next
For i As Integer = 1 To .Rows.Count '将datagridview的数据一行行的写入工作表
row = sht.CreateRow(i)
For j As Integer = 0 To .Columns.Count - 1
If IsNothing(data(j, i - 1).Value) = False Then
row.CreateCell(j).SetCellValue(data(j, i - 1).Value.ToString)
End If
Next j
Next i
End With
Dim fs As FileStream = New FileStream(MyfilePath, FileMode.Create)
workbook.Write(fs)
fs.Close()
workbook.Close()
MsgBox("保存成功")
Catch Exc As Exception
MsgBox(Exc.Message)
End Try
End Sub


这是我导出的一个例子,可供你参考
zjcat35 2018-08-30
  • 打赏
  • 举报
回复
使用npoi,不受是否安装office限制
nicholasvb 2018-08-29
  • 打赏
  • 举报
回复
用npoi for.net很方便!
良朋 2018-08-29
  • 打赏
  • 举报
回复
如果你用的是Office 2007,你需要装SP3.
智造引领未来 2018-08-28
  • 打赏
  • 举报
回复
在引用里,添加了Microsoft Excel 16.0 Object Library 后可以运行
智造引领未来 2018-08-28
  • 打赏
  • 举报
回复
新建了一个VB.net程序

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'根据电脑已安装office版本,先引用
'下面的代码为VB.net 读取或写入 Excel
Dim ExcelApp As Excel.Application '定义Excel的Application
Dim wkBook As Excel.Workbook '定义Excel的Application
Dim wkSheet As Excel.Worksheet
On Error Resume Next
ExcelApp = GetObject(, "Excel.application") '"Excel.application"
wkBook = ExcelApp.ActiveWorkbook '"workbooks"
wkSheet = wkBook.ActiveSheet
If ExcelApp.Workbooks.Count = 0 Then '注:如果新建未保存的工作簿不算打开
MsgBox("没有工作簿打开")
'Exit Sub
ExcelApp = CreateObject("Excel.application")
wkBook = ExcelApp.Workbooks.Add
wkSheet = wkBook.Worksheets.Add
ExcelApp.Visible = True
End If

With wkSheet
For i = 1 To 10
For j = 1 To 10
.Cells(i, j) = i
Next j
Next i
.Range("A1").Value = "Excel 表格控制,好玩吗"
.Cells(6, 6).Value = "Excel 表格控制"
End With
'ExcelSheet.SaveAs("F:\test.xlsx")
ExcelApp.ScreenUpdating = True
wkSheet = Nothing
wkBook = Nothing
ExcelApp = Nothing
End Sub
End Class



报错如下:
错误 3 未定义类型“Excel.Application”。 C:\Users\GSZ\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 7 25 WindowsApplication1
错误 4 未定义类型“Excel.Workbook”。 C:\Users\GSZ\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 8 23 WindowsApplication1
错误 5 未定义类型“Excel.Worksheet”。 C:\Users\GSZ\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 9 24 WindowsApplication1


××××
系统WIn7 64位旗舰版 ,VS2012,Office2013

16,722

社区成员

发帖
与我相关
我的任务
社区描述
VB技术相关讨论,主要为经典vb,即VB6.0
社区管理员
  • VB.NET
  • 水哥阿乐
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧