我想把数据库中统计的结果以excel 的形式导出,请问用编程语句怎么实现?
这是我做的系统的一个导出到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