急！！！vb.net 下输出excel文件， 高手救我
我的问题可简化至如下
将数据库内的数据导出后，输入至EXCEL文件，怎么做？？
-------有人知道spread这个插件吗？
我就是把数据库里东西导入到spread里面，然后再用excel文件导出
问题点数：20、回复次数：13Top
1 楼johnny_mcoc（）回复于 2005-03-03 09:52:50 得分 0
这是我现在的代码
Private Sub btn_File_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_File.Click
Dim exlApp As Excel.Application
Dim exlChart As Excel.Chart
Dim exlWkbk As Excel.Workbook
Dim exlWksht As Excel.Worksheet
Dim dtExcel As DataTable
Dim dsExcel As DataSet
Dim strTmpFileName As String
Dim intCol As Integer
Dim intVle As Integer
Dim IntRow As Integer
Dim intColAry(6) As Integer
exlApp = New Excel.Application
exlChart = New Excel.Chart
strTmpFileName = strFileName.Substring(0, strFileName.LastIndexOf("") + 1) + "井戸マスタ検索.xls"
exlWkbk = exlApp.Workbooks.Open(strTmpFileName)
exlWksht = CType(exlWkbk.Worksheets("井戸マスタ検索"), Excel.Worksheet)
intVle = 0
For intCol = 1 To 200
If (exlWksht.Cells(7, intCol).value <> Nothing) Then
intColAry(intVle) = 0
intColAry(intVle) = intCol
intVle = intVle + 1
End If
Next
For intRow = 0 To dtExcel.Rows.Count - 1
exlWksht.Cells(8 + intRow, intColAry(0)).value = intRow + 1
exlWksht.Cells(8 + intRow, intColAry(1)).value = "'" + dtExcel.Rows(intRow).Item("市区町村コード").ToString()
exlWksht.Cells(8 + intRow, intColAry(2)).value = "'" + dtExcel.Rows(intRow).Item("学区コード").ToString()
exlWksht.Cells(8 + intRow, intColAry(3)).value = "'" + dtExcel.Rows(intRow).Item("井戸番号").ToString()
exlWksht.Cells(8 + intRow, intColAry(4)).value = dtExcel.Rows(intRow).Item("井戸名称").ToString()
exlWksht.Cells(8 + intRow, intColAry(5)).value = dtExcel.Rows(intRow).Item("井戸所在地").ToString()
exlWksht.Cells(8 + intRow, intColAry(6)).value = dtExcel.Rows(intRow).Item("処理結果").ToString()
Next
exlWkbk.Saved = True
exlWkbk.SaveAs(strTmpFileName)
exlWkbk.Close()
exlApp.Quit()
If Not exlApp Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlApp)
End If
exlApp = Nothing
If Not exlWksht Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWksht)
End If
exlWkbk = Nothing
exlWksht = Nothing
GC.Collect()
End SubTop
2 楼wangweixy（千年一叹）回复于 2005-03-03 09:53:31 得分 0
应该有控件可用啊，VB6中有控件直接输出到EXCEL，.net没用过，关注Top
3 楼hamadou（闵峰--为了理想而奋斗）回复于 2005-03-03 09:53:54 得分 0
vb.net可以用代码将数据从datatable导入excel啊！
spread没用过，帮你顶！Top
4 楼johnny_mcoc（）回复于 2005-03-03 09:54:51 得分 0
hamadou(闵峰) 你是上海杰通的？Top
5 楼johnny_mcoc（）回复于 2005-03-03 09:56:57 得分 0
hamadou(闵峰) ：给我一段代码看看，就是将数据从datatable导入excel
道理应该差不多的Top
6 楼youlongzaitian（游龙在天）回复于 2005-03-03 09:57:19 得分 0
我现在是先将数据读出到DATATABLE，然后再导到EXCEL，效率还可以！Top
7 楼youlongzaitian（游龙在天）回复于 2005-03-03 09:57:50 得分 0
要代码的话，可以贴出来看看！Top
8 楼johnny_mcoc（）回复于 2005-03-03 09:58:37 得分 0
将奏折（代码）呈上来Top
9 楼johnny_mcoc（）回复于 2005-03-03 10:02:03 得分 0
各位请注意：我代码中的一段。。。。
strTmpFileName = strFileName.Substring(0, strFileName.LastIndexOf("") + 1) + "井戸.xls"
//命名文件名
exlWkbk = exlApp.Workbooks.Open(strTmpFileName)
//打开excel文件
exlWksht = CType(exlWkbk.Worksheets("井戸検索"), Excel.Worksheet)
对控件操作
Top
10 楼hamadou（闵峰--为了理想而奋斗）回复于 2005-03-03 10:03:14 得分 10
我不是上海杰通的。
我在广东！
#Region "转换成Excel表,返回0,成功；返回-1,文件无法建立"
Private Function ExportExcel(ByVal FN As String, ByVal xlApp As Excel.Application, ByRef xlWkb As Excel.Workbook, ByVal xlSheet As Excel.Worksheet) As Integer
'参数分别为：文件路径，工作区，文件，文件页
Dim i As Integer
Dim count As Integer = Me.dgdmaterial.VisibleRowCount
'从指定路径处的模板复制一个文件过来，然后向该文件填充数据
xlWkb = CreateNewWorkbookFromTemplet(xlApp, Application.StartupPath + "\material_templet.xls", FN)
If xlWkb Is Nothing Then
Return -1
Else
xlSheet = xlWkb.Sheets(1) '指定位置是模板文件的第一页
With xlSheet
.Range("B2").Value = "'" + Me.Lblto.Text 'to
.Range("B3").Value = "'" + Me.txtfrom.Text 'from
.Range("B4").Value = "'" + Me.Dtp1.Value.Date 'date
.Range("J2").Value = "'" + Me.Lblorderno.Text '单号
.Range("J3").Value = "'" + Me.Lblclient.Text '客户
.Range("J4").Value = "'" + Me.Lbloutdate.Text '出货日期
For i = 0 To count - 1
.Range("A" + (6 + i).ToString).Value = "'" + CStr(Me.dgdmaterial.Item(i, 0)) '序号
.Range("B" + (6 + i).ToString).Value = "'" + Me.dgdmaterial.Item(i, 1) '材料编号
.Range("C" + (6 + i).ToString).Value = "'" + Me.dgdmaterial.Item(i, 2) '材料封度
.Range("D" + (6 + i).ToString).Value = "'" + Me.dgdmaterial.Item(i, 3) '材料名称
.Range("E" + (6 + i).ToString).Value = "'" + Me.dgdmaterial.Item(i, 4) '单位
If checkdbnull(Me.dgdmaterial.Item(i, 5)) = 0 Then
.Range("F" + (6 + i).ToString).Value = "" '用料数量
Else
.Range("F" + (6 + i).ToString).Value = "'" + CStr(Me.dgdmaterial.Item(i, 5))
End If
If checkdbnull(Me.dgdmaterial.Item(i, 6)) = 0 Then
.Range("G" + (6 + i).ToString).Value = "" '欠料数量
Else
.Range("G" + (6 + i).ToString).Value = "'" + CStr(Me.dgdmaterial.Item(i, 6))
End If
If checkdbnull(Me.dgdmaterial.Item(i, 7)) = 0 Then
.Range("H" + (6 + i).ToString).Value = "" '买料数量
Else
.Range("H" + (6 + i).ToString).Value = "'" + CStr(Me.dgdmaterial.Item(i, 7))
End If
'.Range("I" + (6 + i).ToString).Value = "'" + Me.dgdmaterial.Item(i, 6) 'p.o.n.o
'.Range("J" + (6 + i).ToString).Value = "'" + Me.dgdmaterial.Item(i, 7)'备注
Next
End With
End If
Return 0
End Function
#End RegionTop
11 楼johnny_mcoc（）回复于 2005-03-03 10:13:34 得分 0
这个基本很难，我还是找不到答案。休息一下，减轻麻烦，跟女秘书斗嘴寻欢Top
12 楼Night_Elf（风暴虫）回复于 2005-03-03 13:29:22 得分 10
可以把数据取出后放到DataTable中或是DataGrid中，然后再导出到EXCEL,没使用过Spread,下面是一段DataGrid导出EXECL的函数，
只用传入DataGrid和标题名称就可以了，速度很快，几万条的数据瞬间导出
Public Function ExportXLsD(ByVal datagrid As DataGrid, ByVal Title As String)
Dim Mytable As New DataTable
Mytable = CType(datagrid.DataSource, DataTable)
If Mytable Is Nothing Then
MessageBox.Show("没有记录不能导出数据", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Function
End If
If Mytable.Rows.Count > 0 Then
Dim MyFileName As String
Dim FileName As String
SaveFileDialog1.AddExtension = True '如果用户忘记添加扩展名，将自动家上
SaveFileDialog1.DefaultExt = "xls" '默认扩展名
SaveFileDialog1.FileName = Title + Date.Now.ToString("yyMMddHHmmss")
SaveFileDialog1.Filter = "Excel文件|*.xls"
SaveFileDialog1.Title = "文件保存到"
SaveFileDialog1.ShowDialog()
FileName = SaveFileDialog1.FileName
'= InputBox("请输入文件名", "导出数据到Excel", Title + Date.Now.ToString("yyyyMMddHHmmss") + ".xls")
MyFileName = Microsoft.VisualBasic.Right(FileName, 4)
If MyFileName = "" Then
Exit Function
End If
If MyFileName = ".xls" Or MyFileName = ".XLS" Then
Dim FS As FileStream = New FileStream(FileName, FileMode.Create)
Dim sw As StreamWriter = New StreamWriter(FS, System.Text.Encoding.Default)
sw.WriteLine(vbTab & vbTab & vbTab & vbTab & Title & vbTab & vbTab & vbTab & Date.Now)
Dim i, j As Integer
Dim str As String = ""
For i = 0 To Mytable.Columns.Count - 1
str = Mytable.Columns(i).Caption
sw.Write(str & vbTab)
Next
sw.Write(vbCrLf)
For j = 0 To CType(datagrid.DataSource, DataTable).Rows.Count - 1
For i = 0 To CType(datagrid.DataSource, DataTable).Columns.Count - 1
Dim strColName, strRow As String
strRow = IIf(Mytable.Rows(j).Item(i) Is DBNull.Value, "", Mytable.Rows(j).Item(i))
sw.Write(strRow & vbTab)
Next
sw.Write(vbLf)
Next
sw.Close()
FS.Close()
MessageBox.Show("数据导出成功！", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Exit Function
End If
Else
MessageBox.Show("没有记录不能导出数据", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End FunctionTop
13 楼kylinzeng（唐人）回复于 2005-03-03 14:57:48 得分 0
用Aspose.Excel控件，轻松搞定！
搜索一下到处都有这个控件。Top
