有2个table
table a 存放用户基本信息id name ...
A001 Apple ...
A002 Bill ...
...table b 存放发票号,付款日期,金额,一个月一个用户可以有多张发票,对应的也有多个金额id bill_num payment_date amount
A001 b_bj_07/0201 02/01/2007 500
A001 b_bj_07/0202 02/02/2007 600
A001 b_bj_07/0301 03/01/2007 700
A002 b_sy_07/0101 01/01/2007 800
...现在要把这里两个表的数据导入到excel里,每一年的数据放入一个worksheet,每一年每一个客户一行,
bill_num输入该用户该年所有发票号,接下来的每个月显示该客户该月的所有金额,每个worksheet格式如下:id name bill_num Jan Feb Mar...(一共12个月)
----------------------------------------------------------------
A001 Apple b_bj_07/0201 0 1100 700 ...
b_bj_07/0202
----------------------------------------------------------------
A002 Bill b_sy_07/0101 800 0 0 ...
table a 存放用户基本信息id name ...
A001 Apple ...
A002 Bill ...
...table b 存放发票号,付款日期,金额,一个月一个用户可以有多张发票,对应的也有多个金额id bill_num payment_date amount
A001 b_bj_07/0201 02/01/2007 500
A001 b_bj_07/0202 02/02/2007 600
A001 b_bj_07/0301 03/01/2007 700
A002 b_sy_07/0101 01/01/2007 800
...现在要把这里两个表的数据导入到excel里,每一年的数据放入一个worksheet,每一年每一个客户一行,
bill_num输入该用户该年所有发票号,接下来的每个月显示该客户该月的所有金额,每个worksheet格式如下:id name bill_num Jan Feb Mar...(一共12个月)
----------------------------------------------------------------
A001 Apple b_bj_07/0201 0 1100 700 ...
b_bj_07/0202
----------------------------------------------------------------
A002 Bill b_sy_07/0101 800 0 0 ...
1.是把不同的年份分到不同的sheet,数据导入到不同的sheet怎样实现?
2.bill_num和月份那块不知道如何实现.
如果只是把数据用一句sql查出来然后把他导入到一个sheet这个我倒会.
是对sql不熟悉
不明白怎么select来实现想要的报表结果如果只是对excel不熟悉的话可以按下面这样做
新建一个workbook后,多开几个worksheet
比如:dim sheet2006 as object
set sheet2006=xlsapp.worksheets.add
dim sheet2007 as object
set sheet2007=xlsapp.worksheets.add
这样就可以把你做好的recordset利用mysheet.Cells.CopyFromRecordset Adodc1.Recordset
方法导入到excel中
至于你的sql怎么写,我要看一下
titles.title,
sales.ord_num,
isnull((select sum(qty) from sales where month(ord_date)=1 and titles.title_id=sales.title_id),0) as Jan,
isnull((select sum(qty) from sales where month(ord_date)=1 and titles.title_id=sales.title_id),0) as Jan,
isnull((select sum(qty) from sales where month(ord_date)=2 and titles.title_id=sales.title_id),0) as Feb,
isnull((select sum(qty) from sales where month(ord_date)=3 and titles.title_id=sales.title_id),0) as Mar,
isnull((select sum(qty) from sales where month(ord_date)=4 and titles.title_id=sales.title_id),0) as Apr,
isnull((select sum(qty) from sales where month(ord_date)=5 and titles.title_id=sales.title_id),0) as May,
isnull((select sum(qty) from sales where month(ord_date)=6 and titles.title_id=sales.title_id),0) as Jun,
isnull((select sum(qty) from sales where month(ord_date)=7 and titles.title_id=sales.title_id),0) as Jul,
isnull((select sum(qty) from sales where month(ord_date)=8 and titles.title_id=sales.title_id),0) as Aug,
isnull((select sum(qty) from sales where month(ord_date)=9 and titles.title_id=sales.title_id),0) as Sep,
isnull((select sum(qty) from sales where month(ord_date)=10 and titles.title_id=sales.title_id),0) as Oct,
isnull((select sum(qty) from sales where month(ord_date)=11 and titles.title_id=sales.title_id),0) as Nov,
isnull((select sum(qty) from sales where month(ord_date)=12 and titles.title_id=sales.title_id),0) as Decefrom titles left join sales on titles.title_id=sales.title_id
where year(sales.ord_date)=1994
其中titles相当于你的表A
sales相当于你的表B
自己看看吧
你只要把那个sql执行生成的recordset来导入到对应的年对应的sheet中就可以了
sql上最后的1994就是年
你的SQL我试了下,好像会有问题
在sales表里title_id只有一行,而在我的表b里,一个id有多个bill_num,也就是同一个id有多行(有几个bill_num就有几行),如果distinct后数就不对了
我需要的结果是:一个id只显示一行,这行显示十二个月的数目,把同一个月的不同bill_num的数目加起来放到对应的月份下.
把left join 改成right join
能实现的跟你要的差不多
但是也不太一样
具体怎么实现跟你的一样,我也不太清楚了
(第二行开始只把单号放那里)比较困难
先把right join导入到excel后
再操作excel来达到你的样式,如下:
因为导入后第一列是title_id,
则判断该单元格和上一个是否内容一样
如果一样,则清空除该行中单号以外的其他内容
如果不一样,则不操作
这样就可以了
做完以上操作再把excel保存或者visible为true显示出来
能明白不?
要引用excel和adodb对象Private Sub Form_Load()
Dim myexcel As New Excel.Application
Dim mybook As New Excel.Workbook
Dim mysheet As New Excel.Worksheet
Set mybook = myexcel.Workbooks.Add '添加一个新的BOOK
Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET'定义一个connection和recordset
Dim strconn As String
Dim strsql As String
strconn = "Provider = SQLOLEDB.1;Password='';Persist Security Info=True;User ID=sa;Initial Catalog=pubs;Data Source=GAVIN"
strsql = "SELECT titles.title_id," & _
"titles.title," & _
"sales.ord_num," & _
"isnull((select sum(qty) from sales where month(ord_date)=1 and titles.title_id=sales.title_id),0) as Jan," & _
"isnull((select sum(qty) from sales where month(ord_date)=1 and titles.title_id=sales.title_id),0) as Jan," & _
"isnull((select sum(qty) from sales where month(ord_date)=2 and titles.title_id=sales.title_id),0) as Feb," & _
"isnull((select sum(qty) from sales where month(ord_date)=3 and titles.title_id=sales.title_id),0) as Mar," & _
"isnull((select sum(qty) from sales where month(ord_date)=4 and titles.title_id=sales.title_id),0) as Apr," & _
"isnull((select sum(qty) from sales where month(ord_date)=5 and titles.title_id=sales.title_id),0) as May," & _
"isnull((select sum(qty) from sales where month(ord_date)=6 and titles.title_id=sales.title_id),0) as Jun," & _
"isnull((select sum(qty) from sales where month(ord_date)=7 and titles.title_id=sales.title_id),0) as Jul," & _
"isnull((select sum(qty) from sales where month(ord_date)=8 and titles.title_id=sales.title_id),0) as Aug," & _
"isnull((select sum(qty) from sales where month(ord_date)=9 and titles.title_id=sales.title_id),0) as Sep," & _
"isnull((select sum(qty) from sales where month(ord_date)=10 and titles.title_id=sales.title_id),0) as Oct," & _
"isnull((select sum(qty) from sales where month(ord_date)=11 and titles.title_id=sales.title_id),0) as Nov," & _
"isnull((select sum(qty) from sales where month(ord_date)=12 and titles.title_id=sales.title_id),0) as Dece" & _
" from titles right join sales on titles.title_id=sales.title_id" & _
" order by sales.title_id"
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
conn.ConnectionString = strconn
conn.Open
rs.ActiveConnection = conn
rs.Open strsql, conn, adOpenStatic '打开一个recordset
mysheet.Cells.CopyFromRecordset rs '导出recordset到excel'填充第一行
mysheet.Rows(1).Insert
mysheet.Cells(1, 1) = "标题号"
mysheet.Cells(1, 2) = "标题"
mysheet.Cells(1, 3) = "单号"
mysheet.Cells(1, 4) = "一月"
mysheet.Cells(1, 5) = "二月"
mysheet.Cells(1, 6) = "三月"
mysheet.Cells(1, 7) = "四月"
mysheet.Cells(1, 8) = "五月"
mysheet.Cells(1, 9) = "六月"
mysheet.Cells(1, 10) = "七月"
mysheet.Cells(1, 11) = "八月"
mysheet.Cells(1, 12) = "九月"
mysheet.Cells(1, 13) = "十月"
mysheet.Cells(1, 14) = "十一月"
mysheet.Cells(1, 15) = "十二月"Dim ord_id As String
Dim i As Integer '行数
i = 3 '因为第一行要填充标题,所以从第三行开始判断
For i = 3 To rs.RecordCount
If mysheet.Cells(i, 1) = mysheet.Cells(i - 1, 1) Then
'跟第二行的title_id比较是否一样 ord_id = mysheet.Cells(i, 3) '保存单号
mysheet.Rows(i).Delete '删除该行
mysheet.Rows(i).Insert '再重新插入一空行
mysheet.Cells(i, 3) = ord_id '填充单号
End If
Next
myexcel.Visible = True
End Sub