11 个解决方案
#1
主要看你引用DLL的是哪个版本
#2
如何把datagridview数据导出到excel中,请教代码
#3
我一般使用createobject方式操作excel,这样的文档2003版本可以阅读,用引用的方式无法控制这个软件的使用者电脑里面安装的是哪个版本的excel,不够灵活
#4
Public Sub SendToExcel(ByVal Table As DataTable, ByVal SheetName As String)
Dim oExcel As Object = CreateObject("Excel.Application")
Dim obook As Object = oExcel.Workbooks.Add()
Dim oSheet As Object = obook.Worksheets(1)
Try
oSheet.Name = SheetName
Dim r As Integer, c As Integer
Dim rCount As Integer
Dim cCount1 As Integer
rCount = Table.Rows.Count
cCount1 = Table.Columns.Count()
oSheet.range("A1:Z1").font.bold = True
For c = 1 To cCount1
oSheet.Cells(1, c) = Table.Columns(c - 1).Caption '设置列标题
Next
c = 0 : r = 0
主页面.ProgressBar1.Value = 20
For r = 1 To rCount
For c = 1 To cCount1
oSheet.Cells(r + 1, c) = CStr("'" & Table.Rows(r - 1)(c - 1).ToString)
主页面.ProgressBar1.Value = c * r * 80 / rCount / cCount1 + 20
Next
Next
oExcel.Visible = True
Catch ex As Exception
MsgBox(ex.Message)
Finally
oExcel = Nothing
obook = Nothing
oSheet = Nothing
End Try
End Sub
#5
调用:
Dv2Excel(DataGridView1, "test.xls")
#Region " '把datagridview中的数据导出到excel函数"
Public Sub Dv2Excel(ByVal DGV As DataGridView, ByVal excelname As String)
Dim wapp As New Microsoft.Office.Interop.Excel.Application
Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
Dim wbook As Microsoft.Office.Interop.Excel.Workbook
On Error Resume Next
wapp.Visible = True
wbook = wapp.Workbooks.Add()
wsheet = wbook.ActiveSheet
Dim iX As Integer
Dim iY As Integer
Dim iC As Integer
For iC = 0 To DGV.Columns.Count - 1
wsheet.Cells(1, iC + 1).Value = DGV.Columns(iC).HeaderText
wsheet.Cells(1, iC + 1).Font.Bold = True
wsheet.Cells(1, iC + 1).Font.Size = 14
Next
wsheet.Rows(2).select()
For iX = 0 To DGV.Rows.Count - 1
For iY = 0 To DGV.Columns.Count - 1
wsheet.Cells(iX + 2, iY + 1).value = DGV(iY, iX).Value.ToString
Next
Next
wsheet.SaveAs(Application.StartupPath & "\" & excelname)
wapp.Quit()
End Sub
#End Region
#6
Dim wapp As New Microsoft.Office.Interop.Excel.Application
显示这个出错,是不是需要引用一下啊 如何做啊
显示这个出错,是不是需要引用一下啊 如何做啊
#7
自己录一下宏,就知道了,我记是,是用 .SaveAs, 然后,参数设置成xlformat8吧,
moExcelSheet.SaveAs("aaa.xls", Excel.XlFileFormat.xlExcel8)
moExcelSheet.SaveAs("aaa.xls", Excel.XlFileFormat.xlExcel8)
#8
用nopi吧,干净整洁一点,否则你的程序发布的时候还要考虑对方的excel版本。
#9
datagridview 導出EXCEL
Private Sub BC()
' On Error GoTo over
' Dim R As String = Format(Now, "yyMMdd")
' Dim FileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\TopFile" & R & "\ZL\BOSSzl.xls " ' + ".xls"
' If File.Exists(FileName) Then
' File.Delete(FileName)
' End If
' Dim objFileStream As FileStream
' Dim objStreamWriter As StreamWriter
' Dim strLine As String = ""
' objFileStream = New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write)
' objStreamWriter = New StreamWriter(objFileStream, System.Text.Encoding.Unicode)
' For i As Integer = 0 To DGV1.Columns.Count - 1
' If DGV1.Columns(i).Visible = True Then
' strLine = strLine + DGV1.Columns(i).HeaderText.ToString() + Convert.ToChar(9)
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' For i As Integer = 0 To DGV1.Rows.Count - 1
' If DGV1.Columns(0).Visible = True Then
' If DGV1.Rows(i).Cells(0).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' strLine = strLine + DGV1.Rows(i).Cells(0).Value.ToString() + Convert.ToChar(9)
' End If
' End If
' For j As Integer = 1 To DGV1.Columns.Count - 1
' If DGV1.Columns(j).Visible = True Then
' If DGV1.Rows(i).Cells(j).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' Dim rowstr As String = ""
' rowstr = DGV1.Rows(i).Cells(j).Value.ToString()
' If rowstr.IndexOf(vbCr & vbLf) > 0 Then
' rowstr = rowstr.Replace(vbCr & vbLf, " ")
' End If
' If rowstr.IndexOf(vbTab) > 0 Then
' rowstr = rowstr.Replace(vbTab, " ")
' End If
' strLine = strLine + rowstr + Convert.ToChar(9)
' End If
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' Next
'over:
' objStreamWriter.Close()
' objFileStream.Close()
' End Sub
Private Sub BC()
' On Error GoTo over
' Dim R As String = Format(Now, "yyMMdd")
' Dim FileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\TopFile" & R & "\ZL\BOSSzl.xls " ' + ".xls"
' If File.Exists(FileName) Then
' File.Delete(FileName)
' End If
' Dim objFileStream As FileStream
' Dim objStreamWriter As StreamWriter
' Dim strLine As String = ""
' objFileStream = New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write)
' objStreamWriter = New StreamWriter(objFileStream, System.Text.Encoding.Unicode)
' For i As Integer = 0 To DGV1.Columns.Count - 1
' If DGV1.Columns(i).Visible = True Then
' strLine = strLine + DGV1.Columns(i).HeaderText.ToString() + Convert.ToChar(9)
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' For i As Integer = 0 To DGV1.Rows.Count - 1
' If DGV1.Columns(0).Visible = True Then
' If DGV1.Rows(i).Cells(0).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' strLine = strLine + DGV1.Rows(i).Cells(0).Value.ToString() + Convert.ToChar(9)
' End If
' End If
' For j As Integer = 1 To DGV1.Columns.Count - 1
' If DGV1.Columns(j).Visible = True Then
' If DGV1.Rows(i).Cells(j).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' Dim rowstr As String = ""
' rowstr = DGV1.Rows(i).Cells(j).Value.ToString()
' If rowstr.IndexOf(vbCr & vbLf) > 0 Then
' rowstr = rowstr.Replace(vbCr & vbLf, " ")
' End If
' If rowstr.IndexOf(vbTab) > 0 Then
' rowstr = rowstr.Replace(vbTab, " ")
' End If
' strLine = strLine + rowstr + Convert.ToChar(9)
' End If
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' Next
'over:
' objStreamWriter.Close()
' objFileStream.Close()
' End Sub
#10
导出为xml的表格最方便,完全不用考虑客户的office版本,也不需要应用任何库
#11
你这样保存太慢,给一个极速保存方法
Dim ABC(100, 10) As Object
For I = 0 To 100
For J = 0 To 10
Select Case I
Case 0
ABC(I, J) = "P" & J
Case Else
ABC(I, J) = Rnd() * 1000
End Select
Next
Next
Xlsbook = Xls.Application.Workbooks.Add
Xlssheet = Xlsbook.Sheets(1)
Xlssheet.Range("A1").Resize(100, 10).Value = ABC
Xlsbook.SaveAs("C:\1.xls", Excel.XlFileFormat.xlExcel8)
Xlsbook.Close()
If Xls IsNot Nothing Then Xls.Quit()
Dim ABC(100, 10) As Object
For I = 0 To 100
For J = 0 To 10
Select Case I
Case 0
ABC(I, J) = "P" & J
Case Else
ABC(I, J) = Rnd() * 1000
End Select
Next
Next
Xlsbook = Xls.Application.Workbooks.Add
Xlssheet = Xlsbook.Sheets(1)
Xlssheet.Range("A1").Resize(100, 10).Value = ABC
Xlsbook.SaveAs("C:\1.xls", Excel.XlFileFormat.xlExcel8)
Xlsbook.Close()
If Xls IsNot Nothing Then Xls.Quit()
#1
主要看你引用DLL的是哪个版本
#2
如何把datagridview数据导出到excel中,请教代码
#3
我一般使用createobject方式操作excel,这样的文档2003版本可以阅读,用引用的方式无法控制这个软件的使用者电脑里面安装的是哪个版本的excel,不够灵活
#4
Public Sub SendToExcel(ByVal Table As DataTable, ByVal SheetName As String)
Dim oExcel As Object = CreateObject("Excel.Application")
Dim obook As Object = oExcel.Workbooks.Add()
Dim oSheet As Object = obook.Worksheets(1)
Try
oSheet.Name = SheetName
Dim r As Integer, c As Integer
Dim rCount As Integer
Dim cCount1 As Integer
rCount = Table.Rows.Count
cCount1 = Table.Columns.Count()
oSheet.range("A1:Z1").font.bold = True
For c = 1 To cCount1
oSheet.Cells(1, c) = Table.Columns(c - 1).Caption '设置列标题
Next
c = 0 : r = 0
主页面.ProgressBar1.Value = 20
For r = 1 To rCount
For c = 1 To cCount1
oSheet.Cells(r + 1, c) = CStr("'" & Table.Rows(r - 1)(c - 1).ToString)
主页面.ProgressBar1.Value = c * r * 80 / rCount / cCount1 + 20
Next
Next
oExcel.Visible = True
Catch ex As Exception
MsgBox(ex.Message)
Finally
oExcel = Nothing
obook = Nothing
oSheet = Nothing
End Try
End Sub
#5
调用:
Dv2Excel(DataGridView1, "test.xls")
#Region " '把datagridview中的数据导出到excel函数"
Public Sub Dv2Excel(ByVal DGV As DataGridView, ByVal excelname As String)
Dim wapp As New Microsoft.Office.Interop.Excel.Application
Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
Dim wbook As Microsoft.Office.Interop.Excel.Workbook
On Error Resume Next
wapp.Visible = True
wbook = wapp.Workbooks.Add()
wsheet = wbook.ActiveSheet
Dim iX As Integer
Dim iY As Integer
Dim iC As Integer
For iC = 0 To DGV.Columns.Count - 1
wsheet.Cells(1, iC + 1).Value = DGV.Columns(iC).HeaderText
wsheet.Cells(1, iC + 1).Font.Bold = True
wsheet.Cells(1, iC + 1).Font.Size = 14
Next
wsheet.Rows(2).select()
For iX = 0 To DGV.Rows.Count - 1
For iY = 0 To DGV.Columns.Count - 1
wsheet.Cells(iX + 2, iY + 1).value = DGV(iY, iX).Value.ToString
Next
Next
wsheet.SaveAs(Application.StartupPath & "\" & excelname)
wapp.Quit()
End Sub
#End Region
#6
Dim wapp As New Microsoft.Office.Interop.Excel.Application
显示这个出错,是不是需要引用一下啊 如何做啊
显示这个出错,是不是需要引用一下啊 如何做啊
#7
自己录一下宏,就知道了,我记是,是用 .SaveAs, 然后,参数设置成xlformat8吧,
moExcelSheet.SaveAs("aaa.xls", Excel.XlFileFormat.xlExcel8)
moExcelSheet.SaveAs("aaa.xls", Excel.XlFileFormat.xlExcel8)
#8
用nopi吧,干净整洁一点,否则你的程序发布的时候还要考虑对方的excel版本。
#9
datagridview 導出EXCEL
Private Sub BC()
' On Error GoTo over
' Dim R As String = Format(Now, "yyMMdd")
' Dim FileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\TopFile" & R & "\ZL\BOSSzl.xls " ' + ".xls"
' If File.Exists(FileName) Then
' File.Delete(FileName)
' End If
' Dim objFileStream As FileStream
' Dim objStreamWriter As StreamWriter
' Dim strLine As String = ""
' objFileStream = New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write)
' objStreamWriter = New StreamWriter(objFileStream, System.Text.Encoding.Unicode)
' For i As Integer = 0 To DGV1.Columns.Count - 1
' If DGV1.Columns(i).Visible = True Then
' strLine = strLine + DGV1.Columns(i).HeaderText.ToString() + Convert.ToChar(9)
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' For i As Integer = 0 To DGV1.Rows.Count - 1
' If DGV1.Columns(0).Visible = True Then
' If DGV1.Rows(i).Cells(0).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' strLine = strLine + DGV1.Rows(i).Cells(0).Value.ToString() + Convert.ToChar(9)
' End If
' End If
' For j As Integer = 1 To DGV1.Columns.Count - 1
' If DGV1.Columns(j).Visible = True Then
' If DGV1.Rows(i).Cells(j).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' Dim rowstr As String = ""
' rowstr = DGV1.Rows(i).Cells(j).Value.ToString()
' If rowstr.IndexOf(vbCr & vbLf) > 0 Then
' rowstr = rowstr.Replace(vbCr & vbLf, " ")
' End If
' If rowstr.IndexOf(vbTab) > 0 Then
' rowstr = rowstr.Replace(vbTab, " ")
' End If
' strLine = strLine + rowstr + Convert.ToChar(9)
' End If
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' Next
'over:
' objStreamWriter.Close()
' objFileStream.Close()
' End Sub
Private Sub BC()
' On Error GoTo over
' Dim R As String = Format(Now, "yyMMdd")
' Dim FileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\TopFile" & R & "\ZL\BOSSzl.xls " ' + ".xls"
' If File.Exists(FileName) Then
' File.Delete(FileName)
' End If
' Dim objFileStream As FileStream
' Dim objStreamWriter As StreamWriter
' Dim strLine As String = ""
' objFileStream = New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write)
' objStreamWriter = New StreamWriter(objFileStream, System.Text.Encoding.Unicode)
' For i As Integer = 0 To DGV1.Columns.Count - 1
' If DGV1.Columns(i).Visible = True Then
' strLine = strLine + DGV1.Columns(i).HeaderText.ToString() + Convert.ToChar(9)
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' For i As Integer = 0 To DGV1.Rows.Count - 1
' If DGV1.Columns(0).Visible = True Then
' If DGV1.Rows(i).Cells(0).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' strLine = strLine + DGV1.Rows(i).Cells(0).Value.ToString() + Convert.ToChar(9)
' End If
' End If
' For j As Integer = 1 To DGV1.Columns.Count - 1
' If DGV1.Columns(j).Visible = True Then
' If DGV1.Rows(i).Cells(j).Value Is Nothing Then
' strLine = (strLine & " ") + Convert.ToChar(9)
' Else
' Dim rowstr As String = ""
' rowstr = DGV1.Rows(i).Cells(j).Value.ToString()
' If rowstr.IndexOf(vbCr & vbLf) > 0 Then
' rowstr = rowstr.Replace(vbCr & vbLf, " ")
' End If
' If rowstr.IndexOf(vbTab) > 0 Then
' rowstr = rowstr.Replace(vbTab, " ")
' End If
' strLine = strLine + rowstr + Convert.ToChar(9)
' End If
' End If
' Next
' objStreamWriter.WriteLine(strLine)
' strLine = ""
' Next
'over:
' objStreamWriter.Close()
' objFileStream.Close()
' End Sub
#10
导出为xml的表格最方便,完全不用考虑客户的office版本,也不需要应用任何库
#11
你这样保存太慢,给一个极速保存方法
Dim ABC(100, 10) As Object
For I = 0 To 100
For J = 0 To 10
Select Case I
Case 0
ABC(I, J) = "P" & J
Case Else
ABC(I, J) = Rnd() * 1000
End Select
Next
Next
Xlsbook = Xls.Application.Workbooks.Add
Xlssheet = Xlsbook.Sheets(1)
Xlssheet.Range("A1").Resize(100, 10).Value = ABC
Xlsbook.SaveAs("C:\1.xls", Excel.XlFileFormat.xlExcel8)
Xlsbook.Close()
If Xls IsNot Nothing Then Xls.Quit()
Dim ABC(100, 10) As Object
For I = 0 To 100
For J = 0 To 10
Select Case I
Case 0
ABC(I, J) = "P" & J
Case Else
ABC(I, J) = Rnd() * 1000
End Select
Next
Next
Xlsbook = Xls.Application.Workbooks.Add
Xlssheet = Xlsbook.Sheets(1)
Xlssheet.Range("A1").Resize(100, 10).Value = ABC
Xlsbook.SaveAs("C:\1.xls", Excel.XlFileFormat.xlExcel8)
Xlsbook.Close()
If Xls IsNot Nothing Then Xls.Quit()