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