- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助]同时生成两个图表问题 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=176844)
-- 发布时间：2022/4/28 16:15:00
-- [求助]同时生成两个图表问题
事例：
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:管理项目3.rar |
要求导出结果：
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:2017-2019级分析对照表.xls |
窗口1中的“导出并生成图表”按钮。
Dim tjlb1 As String = e.Form.Controls("类别").text \'统计类别
Dim tjlb2 As String = e.Form.Controls("类别2").text \'对照类别
Dim tjlbs() As String = {tjlb1,tjlb2}
Dim tjnj1 As String = e.Form.Controls("年级").text \'统计年级
Dim tjnj2 As String = tjnj1 - 1 \'对照年级
Dim tjnj3 As String = tjnj1 - 2 \'对照年级
Dim dlg As New SaveFileDialog \'定义一个新的SaveFileDialog
dlg.Filter= "Excel文件|*.xls" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then \'如果用户单击了确定按钮
\'生成图表
Dim xms() As String = {"特殊类分数线","特殊类上线人数","600分及以上","最高分","最高分排名"}
Dim nms2() As String = {"历史类","物理类","合计"}
Dim nms3() As String = {tjnj1,tjnj2,tjnj3}
Dim t As Table = Tables(tjnj3 & "-" & tjnj1 &"级分析对照表") \'定义一个变量t引用数据表
Dim Chart As New ChartBuilder \'定义一个图表变量
Dim Series As WinForm.ChartSeries \'定义一个图系变量
Chart.ChartType = ChartTypeEnum.Bar \'图表类型改为Bar(条形)
Chart.SeriesList.Clear() \'清除图表原来的图系
\'Dim idx As Integer = 0
For r As Integer = 0 To nms3.Length - 1 \'横坐标开始列位置
Series = Chart.SeriesList.Add() \'增加一个图系
Series.Text = nms3(r) \'设置图系的标题
series.length = xms.Length * nms2.Length
For k As Integer = 0 To xms.Length - 1
For i As Integer = 0 To nms2.Length - 1
For j As Integer = 0 To tjlbs.Length - 1
Series.X(k*nms2.Length+i) = k*nms2.Length+i
Series.Y(k*nms2.Length+i) = val(t.Compute("sum(" & nms2(i) & nms3(r) & ")", "统计项目 = \'" & xms(k) & "\'And 类别 = \'" & tjlb1 & "\'"))
Chart.AxisX.SetValueLabel(k*nms2.Length+i, xms(k) & nms2(i) )
Next
Next
Next
series.DataLabelCompass = LabelCompassEnum.North
Series.DataLabelText = "{#YVAL}"
Next
Chart.ChartType2 = ChartTypeEnum.Bar \'图表类型改为Bar(条形)
Chart.SeriesList2.Clear() \'清除图表原来的图系
Series = Chart.SeriesList2.Add() \'增加第二个图系
For r As Integer = 0 To nms3.Length - 1 \'横坐标开始列位置
Series.Text = nms3(r) \'设置图系的标题
series.length = xms.Length * nms2.Length
For k As Integer = 0 To xms.Length - 1
For i As Integer = 0 To nms2.Length - 1
For j As Integer = 0 To tjlbs.Length - 1
Series.X(k*nms2.Length+i) = k*nms2.Length+i
Series.Y(k*nms2.Length+i) = val(t.Compute("sum(" & nms2(i) & nms3(r) & ")", "统计项目 = \'" & xms(k) & "\'And 类别 = \'" & tjlb2 & "\'"))
Chart.AxisX.SetValueLabel(k*nms2.Length+i, xms(k) & nms2(i) )
Next
Next
Next
series.DataLabelCompass = LabelCompassEnum.North
Series.DataLabelText = "{#YVAL}"
Next
Chart.AxisX.AnnoWithLabels = True \'启用字符标示
Chart.AxisX.AnnoRotation = - 45 \'X轴标示逆时针旋转45度
Chart.LegendVisible = True \'显示图列
Chart.LegendCompass= CompassEnum.South \'图列显示在南方(底端)
For Each tjlb As String In tjlbs
Chart.AxisX.Text = "类别:" & tjlb
Next
chart.LegendText = "图例:年级"
Chart.BarClusterWidth = 80
Chart.AxisY.Text = "分数线 重点人数"
Chart.PrintWidth = 360
Chart.PrintHeight = 180
\'保存文件
t.SaveExcel(dlg.FileName, tjnj3 & "-" & tjnj1 &"级分析对照表") \'保存文件
Dim b As New XLS.Book(dlg.FileName)
Dim s As XLS.Sheet = b.Sheets(tjnj3 & "-" & tjnj1 &"级分析对照表")
s(t.rows.Count + 2,1).Value = New XLS.Picture(Chart.Image)
Dim Style As Xls.Style = b.NewStyle \'新建一个样式
\'单元格内容自动换行
Style.WordWrap = XLS.LineStyleEnum.Thin \'单元格内容自动换行
\'加网格线
Style.AlignHorz = XLS.AlignHorzEnum.Center \'居中
Style.AlignVert = XLS.AlignVertEnum.Center \'居中
Style.BorderTop = XLS.LineStyleEnum.Thin \'上边框
Style.BorderBottom = XLS.LineStyleEnum.Thin \'下边框
Style.BorderLeft = XLS.LineStyleEnum.Thin \'左边框
Style.BorderRight = XLS.LineStyleEnum.Thin \'右边框
\'上下左右边框颜色
Style.BorderColorTop = Color.Black \'黑色
Style.BorderColorBottom = Color.Black
Style.BorderColorLeft = Color.Black
Style.BorderColorRight = Color.Black
For i As Integer = 0 To s.Rows.count -2
For n As Integer = 0 To s.Cols.count -1
s(i,n).Style = Style
Next
Next
\'符合条件的行字体变红加粗
Style = b.NewStyle
Style.ForeColor = Color.Red \'样式的字体颜色设为红色
Style.Font = new font("宋体",9,FontStyle.Bold) \'宋体,9号,加粗
Style.AlignHorz = XLS.AlignHorzEnum.Center \'居中
Style.AlignVert = XLS.AlignVertEnum.Center \'居中
Style.BorderTop = XLS.LineStyleEnum.Thin \'上边框
Style.BorderBottom = XLS.LineStyleEnum.Thin \'下边框
Style.BorderLeft = XLS.LineStyleEnum.Thin \'左边框
Style.BorderRight = XLS.LineStyleEnum.Thin \'右边框
\'上下左右边框颜色
Style.BorderColorTop = Color.Black \'黑色
Style.BorderColorBottom = Color.Black
Style.BorderColorLeft = Color.Black
Style.BorderColorRight = Color.Black
For i As Integer = 0 To s.Rows.count -1
Dim Hjs() As String = {"特殊类分数线"}
For Each Hj As String In Hjs
If s(i,0).Text.EndsWith(Hj) Then \'如果指定列(sheet(i,1)第2列单位中含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) \'将生成的图表保存为文件.
End If
e.Form.Controls("Label1").Text = "近三届分析对照表已导出并删除!"
我想“一诊”、“二诊”各生成一个图表，如何修改增加第二个图系的代码？请指教。谢谢！
[此贴子已经被作者于2022/4/28 16:19:27编辑过]
-- 发布时间：2022/4/28 16:22:00
--
2个图表使用2个ChartBuilder 分别处理
Dim Chart As New ChartBuilder
第一个图表的代码
Dim Chart2 As New ChartBuilder
第二个图表的代码
-- 发布时间：2022/4/28 16:39:00
--
修改后的代码：
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:修改后的代码.txt |
导出结果只生成了第二个图表。请指教。谢谢！
-- 发布时间：2022/4/28 17:01:00
--
添加图片也要添加2次啊
s(t.rows.Count + 2,1).Value = New XLS.Picture(Chart.Image)
s(t.rows.Count + 20,1).Value = New XLS.Picture(Chart2.Image)
-- 发布时间：2022/4/28 17:39:00
--
第二个图表只有2017级的条形图并且增加了18行空表格线。
此主题相关图片如下：第二个图表.png
此主题相关图片如下：增加18行空表网格线.png
[此贴子已经被作者于2022/4/28 17:50:44编辑过]
-- 发布时间：2022/4/28 20:12:00
--
仔细对比第一个图表代码，看哪里用错了。
空白格的问题下面20改为合适的值测试
s(t.rows.Count + 20,1).Value = New XLS.Picture(Chart2.Image)
[此贴子已经被作者于2022/4/28 20:12:13编辑过]
-- 发布时间：2022/4/29 10:42:00
--
调整s(t.rows.Count + 20,1).Value = New XLS.Picture(Chart2.Image)中“20”的值始终有空白格。如果仍为“2”，则与第一个图表重合。只有不加网格线才能解决此问题。
但第二个图表的纵坐标标识在右边，能否显示在左边，如何修改代码？请指教。谢谢!
现在的导出结果是：
| 下载信息 [文件大小： 下载次数： ] | |
| 点击浏览该文件:导出结果29.xls |
-- 发布时间：2022/4/29 10:47:00
--
1、设置完样式，再添加第2个图表
2、第2个图表也是使用SeriesList，不是使用SeriesList2
Chart2.SeriesList.Add
-- 发布时间：2022/4/29 16:58:00
--
导出表格：
此主题相关图片如下：qq图片20220429.png
\'保存文件
t.SaveExcel(dlg.FileName, tjnj3 & "-" & tjnj1 &"级分析对照表") \'保存文件
Dim b As New XLS.Book(dlg.FileName)
Dim s As XLS.Sheet = b.Sheets(tjnj3 & "-" & tjnj1 &"级分析对照表")
s(t.rows.Count + 4,1).Value = New XLS.Picture(Chart.Image) \'添加第一个图表
s(t.rows.Count + 25,1).Value = New XLS.Picture(Chart2.Image) \'添加第二个图表
Dim Style As Xls.Style = b.NewStyle \'新建一个样式
\'单元格内容自动换行
Style.WordWrap = XLS.LineStyleEnum.Thin \'单元格内容自动换行
\'加网格线
Style.AlignHorz = XLS.AlignHorzEnum.Center \'居中
Style.AlignVert = XLS.AlignVertEnum.Center \'居中
Style.BorderTop = XLS.LineStyleEnum.Thin \'上边框
Style.BorderBottom = XLS.LineStyleEnum.Thin \'下边框
Style.BorderLeft = XLS.LineStyleEnum.Thin \'左边框
Style.BorderRight = XLS.LineStyleEnum.Thin \'右边框
\'上下左右边框颜色
Style.BorderColorTop = Color.Black \'黑色
Style.BorderColorBottom = Color.Black
Style.BorderColorLeft = Color.Black
Style.BorderColorRight = Color.Black
For i As Integer = 0 To s.Rows.count -25
For n As Integer = 0 To s.Cols.count -1
s(i,n).Style = Style
Next
Next
\'符合条件的行字体变红加粗
Style = b.NewStyle
Style.ForeColor = Color.Red \'样式的字体颜色设为红色
Style.Font = new font("宋体",9,FontStyle.Bold) \'宋体,9号,加粗
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,0).Text.EndsWith(Hj) Then \'如果指定列(sheet(i,0)第1列单位中含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) \'将生成的图表保存为文件
空白表格线、纵坐标标识问题已解决，但字体变红加粗单元格无边框了，如何修改此代码？请指教。谢谢！
-- 发布时间：2022/4/29 17:00:00
--
把边框的设置也加到红色代码里