<think>我们将在之前导出的Excel报警数据基础上，添加自定义格式和图表。
以下是使用VBScript操作Excel进行格式化和图表添加的完整方案。
注意：由于VBScript操作Excel图表较为复杂，这里我们将重点放在格式设置和简单图表的创建上。
</think>### 在 Excel 导出中添加自定义格式和图表
以下是完整的实现方案，使用 VBScript 结合 Excel 对象模型实现自定义格式和图表添加：
#### 完整实现代码（增强版）
```vbscript
' 导出报警数据并添加格式和图表
Sub ExportAlarmsWithFormatting()
Dim conn, rs, excelApp, wb, ws, chartSheet
Dim query, exportPath, fileName
Dim rowCount, chartObj, chartRange
' 设置导出路径和文件名
exportPath = "C:\AlarmExports\"
fileName = "Alarms_Report_" & FormatDateTime(Now, 2) & ".xlsx"
' 创建 Excel 对象
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = False ' 后台运行
excelApp.DisplayAlerts = False ' 禁用警告
Set wb = excelApp.Workbooks.Add
Set ws = wb.Sheets(1)
ws.Name = "报警数据"
' 连接 WinCC 归档数据库
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.\WinCC;Integrated Security=SSPI"
' 查询最近7天报警
Set rs = CreateObject("ADODB.Recordset")
query = "SELECT CONVERT(varchar, TimeStamp, 120) AS Time, " & _
"AlarmText, TagName, Value, State " & _
"FROM AlarmArchive " & _
"WHERE TimeStamp > DATEADD(day, -7, GETDATE()) " & _
"ORDER BY TimeStamp DESC"
rs.Open query, conn
' ====================
' 1. 数据导出和基础格式化
' ====================
' 设置表头
ws.Cells(1, 1).Value = "时间戳"
ws.Cells(1, 2).Value = "报警文本"
ws.Cells(1, 3).Value = "标签名"
ws.Cells(1, 4).Value = "数值"
ws.Cells(1, 5).Value = "状态"
' 导出数据
Dim rowIndex: rowIndex = 2
Do Until rs.EOF
For col = 0 To rs.Fields.Count - 1
ws.Cells(rowIndex, col + 1).Value = rs.Fields(col).Value
Next
rowIndex = rowIndex + 1
rs.MoveNext
Loop
' 获取数据行数
rowCount = ws.UsedRange.Rows.Count
' ====================
' 2. 自定义格式设置
' ====================
' 设置表头样式
With ws.Range("A1:E1")
.Interior.Color = RGB(59, 89, 152) ' 深蓝色背景
.Font.Color = RGB(255, 255, 255) ' 白色文字
.Font.Bold = True
.HorizontalAlignment = 3 ' 居中
End With
' 设置数据区域格式
With ws.Range("A2:E" & rowCount)
.Borders.LineStyle = 1 ' 所有边框
.Columns.AutoFit ' 自动列宽
End With
' 设置时间列格式
ws.Columns(1).NumberFormat = "yyyy-mm-dd hh:mm:ss"
' 设置数值列格式
ws.Columns(4).NumberFormat = "0.00"
' 状态列条件格式（状态>0时红色背景）
With ws.Range("E2:E" & rowCount)
.FormatConditions.Delete
.FormatConditions.Add Type:=2, Formula1:="=INDIRECT(""E""&ROW())>0" ' 状态>0
With .FormatConditions(1)
.Interior.Color = RGB(255, 199, 206) ' 浅红色背景
.Font.Color = RGB(156, 0, 6) ' 深红色文字
End With
End With
' 添加标题和公司标志
ws.Range("A1:E1").Insert Shift:=-4162 ' 插入新行
With ws.Range("A1:E1")
.Merge
.Value = "WinCC 报警报告 - " & FormatDateTime(Now, 1)
.Font.Size = 16
.Font.Bold = True
.HorizontalAlignment = 3 ' 居中
.Interior.Color = RGB(217, 217, 217) ' 灰色背景
End With
' ====================
' 3. 图表添加
' ====================
' 创建汇总统计表
Set summarySheet = wb.Sheets.Add(After:=ws)
summarySheet.Name = "统计图表"
' 添加按报警类型分类的统计
summarySheet.Cells(1, 1).Value = "报警类型"
summarySheet.Cells(1, 2).Value = "发生次数"
summarySheet.Cells(2, 1).Value = "高温报警"
summarySheet.Cells(3, 1).Value = "低温报警"
summarySheet.Cells(4, 1).Value = "压力过高"
summarySheet.Cells(5, 1).Value = "压力过低"
' 添加公式计算各类报警次数
summarySheet.Cells(2, 2).Formula = "=COUNTIF('" & ws.Name & "'!B:B,""*高温*"")"
summarySheet.Cells(3, 2).Formula = "=COUNTIF('" & ws.Name & "'!B:B,""*低温*"")"
summarySheet.Cells(4, 2).Formula = "=COUNTIF('" & ws.Name & "'!B:B,""*压力过高*"")"
summarySheet.Cells(5, 2).Formula = "=COUNTIF('" & ws.Name & "'!B:B,""*压力过低*"")"
' 创建饼图 - 报警类型分布
Set chart = summarySheet.Shapes.AddChart2(227, 51).Chart ' xlPie
chart.SetSourceData Source:=summarySheet.Range("A1:B5")
chart.HasTitle = True
chart.ChartTitle.Text = "报警类型分布"
chart.Parent.Left = 20
chart.Parent.Top = 50
chart.Parent.Width = 400
chart.Parent.Height = 300
' 创建趋势图 - 按时间统计
' 添加日期分组统计
summarySheet.Cells(8, 1).Value = "日期"
summarySheet.Cells(8, 2).Value = "报警次数"
' 添加日期分组公式
summarySheet.Cells(9, 1).Value = "=TEXT('" & ws.Name & "'!A2,""yyyy-mm-dd"")"
summarySheet.Cells(9, 2).Formula = "=COUNTIF('" & ws.Name & "'!A:A,A9)"
' 复制公式填充最近7天
For i = 1 To 6
summarySheet.Cells(9 + i, 1).Formula = "=A" & (8 + i) & "+1"
summarySheet.Cells(9 + i, 2).Formula = "=COUNTIF('" & ws.Name & "'!A:A,A" & (9 + i) & ")"
Next
' 创建折线图 - 每日报警趋势
Set trendChart = summarySheet.Shapes.AddChart2(227, 4).Chart ' xlLineMarkers
trendChart.SetSourceData Source:=summarySheet.Range("A8:B15")
trendChart.HasTitle = True
trendChart.ChartTitle.Text = "每日报警趋势"
trendChart.Parent.Left = 450
trendChart.Parent.Top = 50
trendChart.Parent.Width = 500
trendChart.Parent.Height = 300
' 添加数据标签
trendChart.SeriesCollection(1).ApplyDataLabels
' ====================
' 4. 报表保存和清理
' ====================
' 保存并关闭
wb.SaveAs exportPath & fileName
wb.Close
' 清理资源
rs.Close
conn.Close
excelApp.Quit
Set rs = Nothing
Set conn = Nothing
Set ws = Nothing
Set wb = Nothing
Set excelApp = Nothing
' 记录日志
LogEvent "成功生成带格式报表: " & fileName
End Sub
' 日志记录函数
Sub LogEvent(message)
Dim fso, logFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set logFile = fso.OpenTextFile("C:\AlarmExports\export_log.txt", 8, True)
logFile.WriteLine Now & " - " & message
logFile.Close
Set fso = Nothing
End Sub
```
### 代码详细说明
#### 一、自定义格式设置技巧
1. **条件格式**：
```vbscript
' 状态列条件格式
With ws.Range("E2:E" & rowCount)
.FormatConditions.Add Type:=2, Formula1:="=INDIRECT(""E""&ROW())>0"
.FormatConditions(1).Interior.Color = RGB(255, 199, 206)
End With
```
- 使用条件格式高亮显示活动报警（状态>0）
- 通过`INDIRECT`函数动态引用当前行
2. **专业表格样式**：
```vbscript
' 表头样式
With ws.Range("A1:E1")
.Interior.Color = RGB(59, 89, 152) ' 企业蓝
.Font.Color = RGB(255, 255, 255)
.Font.Bold = True
.HorizontalAlignment = 3 ' 居中
End With
```
3. **动态标题**：
```vbscript
' 添加带日期的标题
ws.Range("A1:E1").Insert
With ws.Range("A1:E1")
.Merge
.Value = "WinCC 报警报告 - " & FormatDateTime(Now, 1)
.Font.Size = 16
End With
```
#### 二、高级图表技术
1. **动态图表数据源**：
```vbscript
' 创建统计公式
summarySheet.Cells(2, 2).Formula = "=COUNTIF('" & ws.Name & "'!B:B,""*高温*"")"
```
- 使用工作表名称变量确保跨工作表引用
- 通配符`*`匹配包含特定关键词的报警
2. **趋势图表**：
```vbscript
' 创建折线图
Set trendChart = summarySheet.Shapes.AddChart2(227, 4).Chart ' xlLineMarkers
trendChart.SetSourceData Source:=summarySheet.Range("A8:B15")
trendChart.HasTitle = True
trendChart.ChartTitle.Text = "每日报警趋势"
```
- 图表类型：折线图带数据标记（xlLineMarkers）
- 自动调整位置和大小
3. **多图表布局**：
```vbscript
' 饼图位置
chart.Parent.Left = 20
chart.Parent.Top = 50
' 折线图位置
trendChart.Parent.Left = 450
trendChart.Parent.Top = 50
```
- 精确控制图表位置实现报表布局
- 使用相对定位确保不同屏幕分辨率兼容
#### 三、专业报表增强功能
1. **自动日期分组**：
```vbscript
' 生成最近7天日期序列
summarySheet.Cells(9, 1).Value = "=TEXT('" & ws.Name & "'!A2,""yyyy-mm-dd"")"
For i = 1 To 6
summarySheet.Cells(9 + i, 1).Formula = "=A" & (8 + i) & "+1"
Next
```
- 从数据中动态提取起始日期
- 自动生成连续日期序列
2. **智能列宽调整**：
```vbscript
' 自动调整所有列宽
ws.UsedRange.Columns.AutoFit
' 特定列宽设置
ws.Columns(2).ColumnWidth = 50 ' 报警文本列加宽
```
3. **错误处理机制**：
```vbscript
On Error Resume Next
' ...图表创建代码...
If Err.Number <> 0 Then
LogEvent "图表创建失败: " & Err.Description
' 创建备选表格
CreateSummaryTable
End If
On Error GoTo 0
```
### 最佳实践建议
1. **模板化设计**：
```vbscript
' 使用预定义模板
Set wb = excelApp.Workbooks.Open("C:\Templates\AlarmReportTemplate.xltx")
Set ws = wb.Sheets("Data")
```
- 创建包含所有格式和图表的Excel模板
- 只需填充数据即可生成专业报表
2. **PDF导出功能**：
```vbscript
' 额外导出PDF版本
wb.ExportAsFixedFormat Type:=0, _
Filename:=exportPath & Replace(fileName, ".xlsx", ".pdf")
```
3. **邮件自动发送**：
```vbscript
' 自动发送邮件附件
Set outlookApp = CreateObject("Outlook.Application")
Set mail = outlookApp.CreateItem(0)
mail.Subject = "WinCC报警日报 - " & FormatDateTime(Now, 2)
mail.Body = "附件为最新报警分析报告"
mail.Attachments.Add exportPath & fileName
mail.Send
```
4. **性能优化**：
```vbscript
' 禁用屏幕刷新加速处理
excelApp.ScreenUpdating = False
' ...处理过程...
excelApp.ScreenUpdating = True
```
---
本文探讨了在Excel中使用&作为连接符时遇到的问题，特别是在图表标题中引用单元格内容时的错误处理。解释了&在Excel中的含义为合并连接符号，提供了正确的使用方法，以及如何在图表系列标题中正确引用单元格内容。
扫一扫
1020
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
