|
看到用空行补齐我忽然就觉得……呃,这是个什么需求?
这是典型的报表输出需求,每个人需要的报表格式不一样,所以几乎每次都需要手工定制。
最烦就是用excel做报表了,真希望excel能够增加一种报表文件类型。
做出来的效果和楼主要的不一样,仅供参考。
成绩汇总表.rar
(32.12 KB, 下载次数: 15)
- Sub 打印()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim conn As Object, rs As Object
- Set conn = CreateObject("ADODB.connection")
- Set rs = CreateObject("ADODB.recordset")
- Dim connStr$, sqlStr$
- connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
- " Extended Properties=Excel 12.0;" & _
- " Data Source=" & ThisWorkbook.FullName
- conn.Open connStr
-
- For Each sht In Worksheets '删除无用表
- If sht.Name <> "原表" Then sht.Delete
- Next
-
- arr = Array(1, 2, 3) '有几个班级
- For i = 0 To UBound(arr)
- sqlStr = "select 序号,班级,姓名,语文,数学,英语,物理,化学,总分 from [原表$] where 班级 = '" & arr(i) & "班'"
- Set rs = conn.Execute(sqlStr)
- Worksheets.Add after:=Worksheets(Worksheets.Count) '为每个班增加一张表
- With Worksheets(Worksheets.Count)
- .Name = arr(i) & "班成绩表"
- For j = 0 To rs.Fields.Count - 1 '写标题
- .Cells(1, j + 1) = rs.Fields(j).Name
- Next
- .Range("A2").CopyFromRecordset rs '写入查询结果
-
- '下面全是格式美化
- With .UsedRange
- .HorizontalAlignment = xlCenter '对齐
- .VerticalAlignment = xlCenter
- .Font.Name = "等线" '字体
- .Font.Size = 10.5
- .RowHeight = 23 '行高,23刚刚好,每页打印30个学生的成绩,基本上每个班2页纸打完
- '美化标题行
- With .Cells(1, 1).Resize(1, rs.Fields.Count)
- With .Font
- .Name = "黑体"
- .Size = 11
- End With
- With .Interior
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = -4.99893185216834E-02
- End With
- End With
- '设置边框
- With .Borders
- .LineStyle = xlContinuous
- .Weight = xlHairline
- End With
- '设置外边框
- .BorderAround xlContinuous, xlThin
- End With
-
- '设置页眉页脚,打印标题行,居中
- With .PageSetup
- .CenterHeader = "&""黑体,常规""&16 " & Year(Date) & "年" & arr(i) & "班成绩表"
- .CenterFooter = "&10 第 &P 页 共 &N 页"
- .CenterHorizontally = True
- .PrintTitleRows = "$1:$1"
- End With
- End With
- Set rs = Nothing
- Next
- conn.Close
- Set conn = Nothing
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|