北京 | 上海 | 天津 | 重庆 | 广州 | 深圳 | 珠海 | 汕头 | 佛山 | 中山 | 东莞 | 南京 | 苏州 | 无锡 | 常州 | 南通 | 扬州 | 徐州 | 杭州 | 温州 | 宁波 | 台州 | 福州 | 厦门 | 泉州 | 龙岩 | 合肥 | 芜湖 | 成都 | 遂宁 | 长沙 | 株洲 | 湘潭 | 武汉 | 南昌 | 济南 | 青岛 | 烟台 | 潍坊 | 淄博 | 济宁 | 太原 | 郑州 | 石家庄 | 保定 | 唐山 | 西安 | 大连 | 沈阳 | 长春 | 昆明 | 兰州 | 哈尔滨 | 佳木斯 | 南宁 | 桂林 | 海口 | 贵阳 | 西宁 | 乌鲁木齐 | 包头 |
导出EXCEL怎么增加,自动筛选、冻结、加双线
实现样式:
现代码:求修改在明细表加冻结,加双线加自动筛选!
N = N + 1
Loop
'汇总表
F = 5
rst3.MoveFirst
Do While Not rst3.EOF
.Range("G" & F) = rst3("批销中心")
.Range("H" & F) = "=SUMIFS(D:D,A:A,G:G,B:B," & """" & "报纸" & """" & ")"
.Range("I" & F) = "=SUMIFS(D:D,A:A,G:G,B:B," & """" & "杂志" & """" & ")"
.Range("j" & F) = "=SUM(H" & F & ": I" & F & ")"
.Range("G" & F + 1) = "合计"
.Range("H" & F + 1) = "=suM(H5:H" & F & ")"
.Range("I" & F + 1) = "=suM(I5:I" & F & ")"
.Range("j" & F + 1) = "=SUM(J5:J" & F & ")"
'汇总表循环格式
.Range("H" & F & ":" & "J" & F).NumberFormatLocal = "0.00" '显示格式
.Range("H" & F + 1 & ":" & "J" & F + 1).NumberFormatLocal = "0.00" '显示格式
rst3.MoveNext
F = F + 1
Loop
'-------------------------------------------------------------设置格式
'明细表
With .Range("A:E")
.ColumnWidth = 15 '调整列宽
.RowHeight = 20 '调整行高
.Font.Size = 11 '字体大小
.HorizontalAlignment = xlCenter '水平对齐
.VerticalAlignment = xlCenter '垂直对齐
.Font.ThemeFont = 0
.Range("b:b").ColumnWidth = 15 '调整列宽
End With
.Range("A1:e1").MergeCells = True '合并列表
' .Range("A1:e1").HorizontalAlignment = xlCenter '水平对齐
' .Range("A1:e1").VerticalAlignment = xlCenter '垂直对齐 xlBottom关闭
.Range("A1:e1").Font.Size = 18 '字体大小
.Range("A1:e2").Font.Bold = True '字体加粗
.Range("A1:e1").RowHeight = 30 '调整行高
' .Range("B" & L + 1 & "," & "D" & L + 1).HorizontalAlignment = 2 '水平对齐线条
'.Range("A" & L + 1 & "," & "B" & L + 1 & "," & "C" & L + 1).HorizontalAlignment = xlCenter居中对齐 'xlRight右对齐 '2左对齐
'.Range("A2:E2").Borders.LineStyle = xlContinuous '边框样式
'汇总表
With .Range("G:J")
.ColumnWidth = 13 '调整列宽
.Font.Size = 11 '字体大小
.HorizontalAlignment = xlCenter '水平对齐
.VerticalAlignment = xlCenter '垂直对齐
.Font.ThemeFont = 0
End With
.Range("G3:J3").Font.Size = 16 '字体大小
.Range("G3:J3").Font.Bold = True '字体加粗
.Range("g3:j3").MergeCells = True '合并列表
.Range("G4" & ":" & "J" & F).Borders.LineStyle = xlContinuous '边框样式
End With
'-------------------------------------------------------------设置格式
With objxls.Sheets("Sheet1").PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
.LeftMargin = 28.346457
.RightMargin = 28.346457
.TopMargin = 28.346457
.BottomMargin = 28.346457
.HeaderMargin = 28.346457
.FooterMargin = 28.346457
.CenterHorizontally = True
.CenterFooter = "第 &P 页"
.ActiveWindow.SplitRow = 2
.ActiveWindow.FreezePanes = True
End With
'--------------------------------------------------------------工作薄设置信息
objxls.ActiveWorkbook.SaveAs FileName:=CurrentProject.Path & "\" & strid & Forms![操作窗口]![结算周期] & "结算单.xls" ' 导出名称"
objxls.ThisWorkbook.Close '关闭工作簿
objxls.Visible = False '是否打开EXCEL'true开,FALSE关
Set objxls = Nothing
Set rst2 = Nothing
rst1.MoveNext '移到下一条记录
Loop
rst1.MoveFirst
Set objxls = Nothing
Set rst1 = Nothing
err: Exit Sub
End Sub