《用做好的Excel報表模板來做報表》
来源:互联网 发布:唯一网络 私募 编辑:程序博客网 时间:2024/05/01 15:20
小弟獻醜一回了,各位大俠請不要砸我,要砸輕點砸,這兩天感冒了
--2005、07、14
《用做好的Excel報表模板來做報表》
首先需要有個已經做好的excel報表模板,本例的是:tabOrder.xls
裡面技術不多,就是復制模板的時候有點技巧
Private Sub Command1_Click()
'訂貨單報表
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strPubConnect As New ADODB.Connection
With strPubConnect
.ConnectionString = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=A382"
.CommandTimeout = 0
.Open
End With
Dim rsQuery As New ADODB.Recordset
Dim strSql As String
Dim strPathName As String
Dim strPathExcel As String
Dim intCountNum As Integer
strPathExcel = "D:/Product Skill/ToExcel/tabOrder.xls" 'excel報表模板路徑(一般放在網絡盤,因為公司的報表會集中在一塊)
DialogReport.DefaultExt = "*.xls"
DialogReport.Filter = "Excel(*.xls)|*.xls"
DialogReport.ShowSave
strPathName = DialogReport.FileName
If strPathName = strName Then
Screen.MousePointer = 0
Exit Sub
End If
If strPathName = "" Then
Screen.MousePointer = 0
Exit Sub
End If
Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(strPathExcel)
Set xlSheet = xlBook.Worksheets(1)
xlApp.DataEntryMode = xlOff
Dim intPages As Integer
Dim intOrderI As Integer
'**********************統計有多少頁********************
Set rsQuery = New ADODB.Recordset
strSql = "select OrderID,CustomerID,EmployeeID,ShipVia,Freight,ShipName,ShipCity,ShipPostalcode from orders"
rsQuery.Open strSql, strPubConnect, adOpenStatic
If Not rsQuery.EOF Then
If (rsQuery.RecordCount / 23) - Int(rsQuery.RecordCount / 23) > 0 Then
intPages = Int(rsQuery.RecordCount / 23) + 1
Else
intPages = Int(rsQuery.RecordCount / 23)
End If
If intPages = 0 Then
intPages = 1
End If
rsQuery.Close
Else
rsQuery.Close
End If
'**********************統計有多少頁********************
'**********************根據統計出來的頁數進行復制******
For intOrderI = 1 To intPages - 1
xlSheet.Range("A1:R52").Copy Destination:=xlSheet.Range("A" & Trim(Str(52 * intOrderI + 1)))
Next
'**********************根據統計出來的頁數進行復制******
'父表
' Dim intCount As Integer
Dim intJ As Integer
Set rsQuery = New ADODB.Recordset
strSql = "select top 1 EmployeeID,LastName,FirstName,Title,HireDate,City,Region,PostalCode,Country,HomePhone,Address from Employees"
rsQuery.Open strSql, strPubConnect, adOpenStatic
If rsQuery.EOF = False Then
For intJ = 0 To intPages - 1
xlSheet.Cells(52 * intJ + 6, 3) = rsQuery("EmployeeID") & ""
xlSheet.Cells(52 * intJ + 7, 3) = rsQuery("FirstName") & ""
xlSheet.Cells(52 * intJ + 8, 3) = rsQuery("HomePhone") & ""
xlSheet.Cells(52 * intJ + 9, 3) = rsQuery("HomePhone") & ""
xlSheet.Cells(52 * intJ + 10, 3) = rsQuery("LastName") & ""
xlSheet.Cells(52 * intJ + 11, 3) = rsQuery("Address") & ""
xlSheet.Cells(52 * intJ + 10, 7) = rsQuery("PostalCode") & ""
' xlSheet.Cells(52 * intJ + 8, 7) = rsQuery("sum_money") & ""
xlSheet.Cells(52 * intJ + 7, 7) = rsQuery("city") & ""
xlSheet.Cells(52 * intJ + 9, 7) = rsQuery("title") & ""
Next
End If
Set rsQuery = Nothing
'子表
Set rsQuery = New ADODB.Recordset
strSql = "select OrderID,CustomerID,EmployeeID,ShipVia,Freight,ShipName,ShipCity,ShipPostalcode from orders"
rsQuery.Open strSql, strPubConnect, adOpenStatic
If (rsQuery.RecordCount / 23) - Int(rsQuery.RecordCount / 23) > 0 Then
intPages = Int(rsQuery.RecordCount / 23) + 1
Else
intPages = Int(rsQuery.RecordCount / 23)
End If
If intPages = 0 Then
intPages = 1
End If
For intOrderI = 1 To intPages - 1
xlSheet.Range("A1:R52").Copy Destination:=xlSheet.Range("A" & Trim(Str(52 * intOrderI + 1)))
Next
Dim n As Integer
Dim i As Integer
Dim dboMoney As Double
i = 0
If rsQuery.EOF = False Then
For n = 1 To rsQuery.RecordCount
If n = 23 * (i + 1) + 1 Then
i = i + 1
End If
' xlSheet.Cells(52 * i + 13 + n - (23 * i), 1) = n
xlSheet.Cells(52 * i + 13 + n - (23 * i), 2) = rsQuery("CustomerID") & ""
xlSheet.Cells(52 * i + 13 + n - (23 * i), 3) = rsQuery("OrderID") & ""
xlSheet.Cells(52 * i + 13 + n - (23 * i), 4) = rsQuery("ShipName") & ""
xlSheet.Cells(52 * i + 13 + n - (23 * i), 5) = rsQuery("Freight") & ""
xlSheet.Cells(52 * i + 13 + n - (23 * i), 6) = rsQuery("OrderID") & ""
xlSheet.Cells(52 * i + 13 + n - (23 * i), 7) = rsQuery("ShipVia") & ""
xlSheet.Cells(52 * i + 13 + n - (23 * i), 8) = rsQuery("ShipPostalcode") & ""
' dboMoney = dboMoney + rsQuery("money") & ""
If n = rsQuery.RecordCount Then
xlSheet.Cells(52 * i + 13 + n - (23 * i) + 1, 3) = "( 以下空白 )"
End If
rsQuery.MoveNext
Next n
End If
Set rsQuery = Nothing
strCompanyID = ""
strOutDate = ""
xlBook.SaveAs strPathName
strName = strPathName
xlApp.Visible = True
'' xlBook.Close
Set xlApp = Nothing
Screen.MousePointer = 0
End Sub
這個報表被分成了兩部分,第一部分是供應商的基本資料,下一部分產品資料。(一個供應商對應N多的產品資料)
這樣方便於根據每個供應商來做成各自的報表,其中本例中所引用的數據是Sql數據庫Employees表和orders表
中的資料,不過我是為了能夠體現出這個報表,而自己手邊又沒有完善的數據,sql語句自己可以靈活的寫,
可以自己寫成些統計之和等都可以實現。
相關EXCEL圖片如下:
- 《用做好的Excel報表模板來做報表》
- 上传图片时button按钮隐藏,用做好的样式
- 收集一些写的好文章博客地址,调用Excel模板
- php 用做脚本时的一些技巧
- 用做个人网站的心态进行SEO
- excel导出模板带有下拉的模板
- 基于模板的excel导出
- 简单的下载excel模板
- bootstrap的一些好用的模板
- rn的组件textinput用做searchbar的坑
- jxl 操作 excel 比较好的例子
- GridView导出Excel的超好例子
- GridView导出Excel的超好例子
- GridView导出Excel的超好例子
- Excel真是写代码的好帮手
- GridView导出Excel的超好例子
- excel的几个好用快捷键
- GridView导出Excel的超好例子
- ArcGis Geodatabase 学习笔记
- 人心深处
- 一些经典言论
- 如何用asp调用dll组件里边的函数??
- 女儿,是父亲上辈子不能在一起的情人
- 《用做好的Excel報表模板來做報表》
- 和老婆生气了
- 我的疑问
- hello blog
- 论技术与职业的关系
- C#开发终端式短信的原理和方法(转自 蚕蛹)
- 7月14日
- 项目管理经验之75条
- 一些心得