Dim dlg As New SaveFileDialog '定义一个新的SaveFileDialog
dlg.Filter= "Excel文件|*.xls" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
Dim Values() As String = {"总分"}
If e.Form.Controls("年级").value Is Nothing And e.Form.Controls("类别").value Is Nothing Then
MessageBox.Show("请选择统计年级和类别再执行此操作","中止操作",MessageBoxButtons.OK,MessageBoxIcon.Exclamation)
Else
Dim tjnj As String = e.Form.Controls("年级").text
Dim tjlb As String = e.Form.Controls("类别").text
For Each Value As String In Values
'生成第一个图表显示人数
Dim Chart As new ChartBuilder '定义一个图表变量
Dim Series As WinForm.ChartSeries '定义一个图系变量
Dim t As Table = Tables( "高" & tjnj & "级" & tjlb & "重点有效分和贡献率统计表") '定义一个变量t引用数据表
Chart.ChartType = ChartTypeEnum.Bar '图表类型改为Bar(条形)
Chart.ChartType2 = ChartTypeEnum.XYPlot '图表2类型该为线形
Chart.SeriesList.Clear() '清除图表原来的图系
Dim xms() As String = {"重庆市","全县合计","梁平中学","红旗中学","第一中学","屏锦中学","实验中学","袁驿中学","福禄中学"}
Dim hms() As String = {"人数"}
For Each xm As String In xms
For Each hm As String In hms
Series = Chart.SeriesList.Add() '增加第一个图系
Series.Text = xm '设置图系的标题
series.length = t.Cols.count-3
For r As Integer = 0 To t.Cols.count-8 '横坐标开始列位置
Series.X(r) = r
Series.Y(r) = val(t.Compute("sum(" & t.Cols(r+7).name & ")", "单位 = '" & xm & "'And 项目 = '" & hm & "'"))
Chart.AxisX.SetValueLabel(r, t.Cols(r+7).caption) '指定字符表示
Next
series.DataLabelCompass = LabelCompassEnum.North
Series.DataLabelText = "{#YVAL}"
Next
Next
Chart.SeriesList2.Clear() '清除图表原来的图系
Dim hm1s() As String = {"贡献率%"}
For Each xm As String In xms
For Each hm1 As String In hm1s
Series = Chart.SeriesList2.Add() '增加批二个图系
Series.Text = xm '设置图系的标题
series.length = t.Cols.count-3
For r As Integer = 0 To t.Cols.count-8 '横坐标开始列位置
Series.X(r) = r
Series.Y(r) = val(t.Compute("sum(" & t.Cols(r+7).name & ")", "单位 = '" & xm & "'And 项目 = '" & hm1 & "'"))
Chart.AxisX.SetValueLabel(r, t.Cols(r+7).caption) '指定字符表示
Next
series.DataLabelCompass = LabelCompassEnum.North
Series.DataLabelText = "{#YVAL}"
Next
Next
Chart.AxisX.AnnoWithLabels = True '启用字符标示
Chart.AxisX.AnnoRotation = -45 'X轴标示逆时针旋转45度
Chart.LegendVisible = True '显示图列
Chart.LegendCompass= CompassEnum.South '图列显示在南方(底端)
Chart.AxisX.Text = "文 理 科"
chart.LegendText = "图例:单位"
Chart.BarClusterWidth = 100
Chart.AxisY.Text = "人数"
Chart.AxisY2.Text = "贡献率%"
Chart.PrintWidth = 360
Chart.PrintHeight = 180
'保存文件
t.SaveExcel(dlg.FileName, "高" & tjnj & "级" & tjlb & "重点有效分和贡献率统计表") '保存文件
Dim b As New XLS.Book(dlg.FileName)
Dim s As XLS.Sheet = b.Sheets( "高" & tjnj & "级" & tjlb & "重点有效分和贡献率统计表")
s(t.rows.Count + 2,1).Value = New XLS.Picture(Chart.Image)
Dim Style As Xls.Style = b.NewStyle '新建一个样式
Style.ForeColor = Color.Red '样式的背景颜色设为红色
Style.Font = new font("宋体",9,FontStyle.Bold)
Style.AlignHorz = XLS.AlignHorzEnum.Center '居中
Style.AlignVert = XLS.AlignVertEnum.Center '居中
For i As Integer = 0 To s.Rows.count -1
Dim Hjs() As String = {"重庆市","全县合计"}
For Each Hj As String In Hjs
If s(i,3).Text.EndsWith(Hj) Then '如果指定列(第3列)中含Hj字符存在
For n As Integer = 0 To s.Cols.count -1
s(i,n).Style = Style
Next
End If
Next
Next
b.Save(dlg.FileName)
'DataTables.Delete( "高" & tjnj & "级" & tjlb & "重点有效分和贡献率统计表") '删除原文件
Next
End If
End If
e.Form.Controls("Label1").Text = "有效分及贡献率统计表已导出并删除!"