标题:怎样将vs.net中的数据作为Excel数据导出
只看楼主
amypaopao
Rank: 1
等 级:新手上路
帖 子:43
专家分:0
注 册:2007-5-14
 问题点数:0 回复次数:2 
怎样将vs.net中的数据作为Excel数据导出

我想把数据库中统计的结果以excel 的形式导出,请问用编程语句怎么实现?

搜索更多相关主题的帖子: Excel 数据库 excel 语句 形式 
2007-06-06 22:49
taick
Rank: 1
等 级:新手上路
威 望:1
帖 子:71
专家分:0
注 册:2007-7-16
得分:0 

这是我做的系统的一个导出到excel的部分
你参考一下
Private Sub btnToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToExcel.Click
Dim mMINO As String
Try
mMINO = dgvAllMI.CurrentRow.Cells(2).Value.ToString.Trim
Catch ex As Exception
MsgBox("請選擇你要輸出的MI!")
Exit Sub
End Try

Try
Dim dr As DataRow
Dim dsListINV As New DataSet
Dim dsMinServiceFrom As New DataSet
Dim getDS As New U_DataBase
Dim strUse As String = "select QuotationID,CompleteDate,ServiceFrom,ServiceUntil,Total,ServiceFee,MaintenanceRate,MITotal,Summary,Remark,CompanyName,OfficePlace,Connect,ProjectPlace from MIView where MINO='" & mMINO & "'"
Dim strDBT As String = "INV"
dsListINV.Tables.Clear()
dsListINV = getDS.GetDataSet(strUse, strDBT)
strUse = "select Min(ServiceFrom) from MIView where MINO='" & mMINO & "'"
dsMinServiceFrom = getDS.GetDataSet(strUse, "MinServiceFrom")
Dim excelWorksheel As New Microsoft.Office.Interop.Excel.Worksheet
Try
excelWorksheel = excelBook.Sheets.Add
Catch ex As Exception
excelApp = New Microsoft.Office.Interop.Excel.Application
excelBook = excelApp.Workbooks.Add
excelWorksheel = excelBook.Sheets.Add
End Try

Try
excelWorksheel.Name = mMINO

Catch ex As Exception
MsgBox(ex.Message)
End Try

excelApp.Visible = True

With excelWorksheel

.Range("A1").Value = "CompanyName"
.Range("A1").Font.Bold = True
.Range("B1").Value = "Address"
.Range("B1").Font.Bold = True
.Range("C1").Value = "Connection people "
.Range("C1").Font.Bold = True

.Range("A2").Value = dsListINV.Tables(strDBT).Rows(0)(10)
.Range("B2").Value = dsListINV.Tables(strDBT).Rows(0)(11)
.Range("C2").Value = dsListINV.Tables(strDBT).Rows(0)(12)


.Range("A5").Value = "MI编号"
.Range("A5").Font.Bold = True
.Range("A5").ColumnWidth = 11
.Range("B5").Value = "保養收費百份比"
.Range("B5").Font.Bold = True
.Range("B5").ColumnWidth = 26
.Range("C5").Value = "保養由"
.Range("C5").Font.Bold = True
.Range("C5").ColumnWidth = 26
.Range("D5").Value = "保養期直"
.Range("D5").Font.Bold = True
.Range("D5").ColumnWidth = 26
.Range("E5").Value = "工程總額"
.Range("E5").Font.Bold = True
.Range("E5").ColumnWidth = 11
.Range("F5").Value = "服務總收費"
.Range("F5").Font.Bold = True
.Range("F5").ColumnWidth = 11

.Range("A6").Value = mMINO
.Range("B6").Value = dsListINV.Tables(strDBT).Rows(0)(6)
.Range("B6").NumberFormatLocal = "d-mmm-yyyy"
.Range("C6").Value = FormatDateTime(dsMinServiceFrom.Tables("MinServiceFrom").Rows(0)(0), DateFormat.LongDate)
.Range("C6").NumberFormatLocal = "d-mmm-yyyy"
.Range("D6").Value = FormatDateTime(dsListINV.Tables(strDBT).Rows(0)(3), DateFormat.LongDate)
.Range("D6").NumberFormatLocal = "d-mmm-yyyy"
.Range("E6").Value = dsListINV.Tables(strDBT).Rows(0)(7)
.Range("F6").Value = dsListINV.Tables(strDBT).Rows(0)(8)


.Range("A9").Value = "QuotationID"
.Range("A9").Font.Bold = True
.Range("B9").Value = "CompleteDate"
.Range("B9").Font.Bold = True
.Range("C9").Value = "ServiceFrom"
.Range("C9").Font.Bold = True
.Range("D9").Value = "ServiceUntil"
.Range("D9").Font.Bold = True
.Range("E9").Value = "Total"
.Range("E9").Font.Bold = True
.Range("F9").Value = "ServiceFee"
.Range("F9").Font.Bold = True
.Range("G9").Value = "Remark"
.Range("G9").Font.Bold = True
.Range("H9").Value = "ProjectPlace"
.Range("H9").Font.Bold = True

Dim i As Integer = 10
For Each dr In dsListINV.Tables(strDBT).Rows
.Range("A" & i.ToString).Value = dr("QuotationID")
.Range("B" & i.ToString).Value = FormatDateTime(dr("CompleteDate"), DateFormat.LongDate)
.Range("B" & i.ToString).NumberFormatLocal = "d-mmm-yyyy"
.Range("C" & i.ToString).Value2 = FormatDateTime(dr("ServiceFrom"), DateFormat.LongDate)
.Range("C" & i.ToString).NumberFormatLocal = "d-mmm-yyyy"
.Range("D" & i.ToString).Value2() = FormatDateTime(dr("ServiceUntil"), DateFormat.LongDate)
.Range("D" & i.ToString).NumberFormatLocal = "d-mmm-yyyy"
.Range("E" & i.ToString).Value = dr("Total")
.Range("F" & i.ToString).Value = dr("ServiceFee")
.Range("G" & i.ToString).Value = dr("Remark")
.Range("H" & i.ToString).Value = dr("ProjectPlace")
i += 1
Next dr


End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

2007-07-17 13:16
qlong0728
Rank: 3Rank: 3
等 级:新手上路
威 望:8
帖 子:272
专家分:0
注 册:2007-6-15
得分:0 

Try
Dim i As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
Dim j As Integer
xlSheet.Cells(1, 1).Value = "单品编号"
xlSheet.Cells(1, 2).Value = "材料编号"
xlSheet.Cells(1, 3).Value = "材料名称"
xlSheet.Cells(1, 4).Value = "毛重"
xlSheet.Cells(1, 5).Value = "净重"
xlSheet.Cells(1, 6).Value = "损耗率(%)"
xlSheet.Cells(1, 7).Value = "单位"
xlSheet.Cells(1, 8).Value = "备注"
With DataGridView1
For i = 0 To .Rows.Count - 1
For j = 0 To .ColumnCount - 1
xlSheet.Cells(i + 2, j + 1).Value = .Rows(i).Cells(j).Value
Next j
xlSheet.Range("a" & i + 2).Select()
Next i
End With

xlApp.Columns("a:h").EntireColumn.AutoFit()
xlSheet.Range("a1").Select()

xlSheet = Nothing
xlBook = Nothing
xlApp = Nothing
Catch
MessageBox.Show("数据拷贝到Excel失败,可能您没有装Excel或其它未知出错,请找哪帮家伙解决吧!", Message_Title, MessageBoxButtons.OK, MessageBoxIcon.Warning)
End Try


程序人员写程序,又拿程序换酒钱。 奔驰宝马贵者趣,公交自行程序员。 不见满街漂亮妹,哪个归得程序员。
2007-07-18 17:00



参与讨论请移步原网站贴子:https://bbs.bccn.net/thread-145497-1-1.html




关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.127297 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved