public class LineChartHelp
{
#region 折线图
/// <summary>
/// 创建折线图
/// </summary>
public void CreateChart(ChartControl chart, DataTable dt, int j, int k)
{
chart.Series.Clear();//清除Series
List<Series> list = new List<Series>();
list.Add(CreateSeries(dt.Columns[j].ColumnName, ViewType.Line, dt, j));
list.Add(CreateSeries(dt.Columns[j + 1].ColumnName, ViewType.Line, dt, j + 1));
list.Add(CreateSeries(dt.Columns[k].ColumnName, ViewType.Line, dt, k));
list.Add(CreateSeries(dt.Columns[k + 1].ColumnName, ViewType.Line, dt, k + 1));
SetChart(chart, dt, list, true);
}
/// <summary>
/// 创建折线图
/// </summary>
public void CreateChart(ChartControl chart, DataTable dt, int j = 1)
{
#region Series 创建几个图形的对象
chart.Series.Clear();//清除Series
List<Series> list = new List<Series>();
for (int i = j; i < dt.Columns.Count; i++)
{
list.Add(CreateSeries(dt.Columns[i].ColumnName, ViewType.Line, dt, i));
}
#endregion
SetChart(chart, dt, list);
}
private void SetChart(ChartControl chart, DataTable dt, List<Series> list, bool legend = false)
{
chart.Series.AddRange(list.ToArray());
chart.Legend.Visible = false;
chart.SeriesTemplate.LabelsVisibility = DefaultBoolean.True;
SetX(chart);
List<Color> colorList = new List<Color> { Color.Red, Color.YellowGreen, Color.Green, Color.Blue, Color.Tomato, Color.BlueViolet };
if (((XYDiagram)chart.Diagram).SecondaryAxesY != null)//清空y轴
((XYDiagram)chart.Diagram).SecondaryAxesY.Clear();
for (int i = 0; i < list.Count; i++)
{
list[i].View.Color = colorList[i];
if(!legend)
CreateAxisY(chart, list[i]);
}
chart.Legend.Visible = legend;
}
/// <summary>
/// 设置x轴可缩放
/// </summary>
/// <param name="chart1"></param>
private void SetX(ChartControl chart)
{
XYDiagram diagram = (XYDiagram)chart.Diagram;
diagram.EnableAxisXScrolling = true;
diagram.EnableAxisXZooming = true;
}
/// <summary>
/// 根据数据创建一个图形展现
/// </summary>
/// <param name="caption">图形标题</param>
/// <param name="viewType">图形类型</param>
/// <param name="dt">数据DataTable</param>
/// <param name="rowIndex">图形数据的行序号</param>
private Series CreateSeries(string caption, ViewType viewType, DataTable dt, int columnsIndex)
{
Series series = new Series(caption, viewType);
for (int i = 0; i < dt.Rows.Count; i++)
{
string argument = dt.Rows[i][0].ToString();//参数名称
if (string.IsNullOrEmpty(argument)) argument = "未分组";//当x轴参数为空，显示未分组
var value = dt.Rows[i][columnsIndex];//参数值
series.Points.Add(new SeriesPoint(argument, value));
}
//必须设置ArgumentScaleType的类型，否则显示会转换为日期格式，导致不是希望的格式显示
//也就是说，显示字符串的参数，必须设置类型为ScaleType.Qualitative
series.ArgumentScaleType = ScaleType.Qualitative;
series.LabelsVisibility = DevExpress.Utils.DefaultBoolean.True;//显示标注标签
return series;
}
/// <summary>
/// 创建图表的第二坐标系
/// </summary>
/// <param name="series">Series对象</param>
private SecondaryAxisY CreateAxisY(ChartControl chart, Series series)
{
SecondaryAxisY myAxis = new SecondaryAxisY(series.Name);
((XYDiagram)chart.Diagram).SecondaryAxesY.Add(myAxis);
((LineSeriesView)series.View).AxisY = myAxis;
myAxis.Title.Text = series.Name;
myAxis.Title.Alignment = StringAlignment.Far; //顶部对齐
myAxis.Title.Visible = true; //显示标题
myAxis.Title.Font = new Font("宋体", 9.0f);
Color color = series.View.Color;//设置坐标的颜色和图表线条颜色一致
myAxis.Title.TextColor = color;
myAxis.Label.TextColor = color;
myAxis.Color = color;
return myAxis;
}
#endregion
使用aspose导出
public void ToExcelData(DataTable dt, ChartControl chtData, string title)
{
string path = GetPath();
if (string.IsNullOrEmpty(path)) return;
try
{
Workbook workbook = new Workbook();
workbook.Worksheets.Clear();
Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式
style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style2.Font.Name = "宋体";
style2.Font.Size = 14;
style2.Font.IsBold = true;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style2.ForegroundColor = System.Drawing.Color.GreenYellow;
style2.Pattern = BackgroundType.Solid;
workbook.Worksheets.Add(title);
int startRow = 0;//写入数据开始行的下标
Worksheet ws0 = workbook.Worksheets[0];
//写入到Excel
SetTitlt(workbook, ws0, startRow, title, dt.Columns.Count);
SetCell(startRow + 2, ws0, dt, style2);
//写入图注
startRow = dt.Rows.Count + 5;
AddStream(startRow, ws0, chtData);
workbook.Save(path);
if (DevExpress.XtraEditors.XtraMessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
System.Diagnostics.Process.Start(path);//打开指定路径下的文件
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 填充字段
/// </summary>
private void SetCell(int startRow, Worksheet sheet, DataTable dt, Style style2)
{
sheet.Cells[startRow, 0].PutValue("统计报表详细列表如下：");
//生成字段名称
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[startRow + 1, i].PutValue(dt.Columns[i].ToString());
sheet.Cells[startRow + 1, i].SetStyle(style2);
}
//填充数据
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column < dt.Columns.Count; column++)
{
if (dt.Rows[row][column].GetType() == typeof(string))
{
sheet.Cells[row + startRow + 2, column].PutValue("'" + dt.Rows[row][column].ToString());
}
else
{
sheet.Cells[row + startRow + 2, column].PutValue(dt.Rows[row][column].ToString());
}
}
}
sheet.AutoFitColumns();
}
/// <summary>
/// 设置标题
/// </summary>
private void SetTitlt(Workbook workbook, Worksheet sheet, int startRow, string titlt, int columnsCount)
{
Style style1 = workbook.Styles[workbook.Styles.Add()];//新增样式
style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style1.Font.Name = "宋体";
style1.Font.Size = 20;
style1.Font.IsBold = true;
//style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;//应用边界线 左边界线
//style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
//style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;//应用边界线 上边界线
//style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界线
sheet.Cells.Merge(startRow, 0, 2, columnsCount);//合并单元格
//sheet.Cells.SetRowHeight(0, 38);//设置行高
//sheet.Cells.SetColumnWidth(0, 25);//设置列宽
sheet.Cells[0, 0].PutValue(titlt);//添加内容
sheet.Cells[0, 0].SetStyle(style1);
}
/// <summary>
/// 添加图片
/// </summary>
private void AddStream(int rows, Worksheet worksheet, ChartControl charts)
{
worksheet.Cells[rows, 0].PutValue("以折线图展示如下：");
//插入图片到Excel里面
using (MemoryStream stream = new MemoryStream())
{
stream.Position = 0;
ChartControl chart = (ChartControl)charts.Clone();
chart.Size = new Size(1200, 400);
chart.ExportToImage(stream, ImageFormat.Png);
worksheet.Pictures.Add(rows + 1, 0, stream);
}
}
/// <summary>
/// 获取导出路劲
/// </summary>
private string GetPath()
{
string path = "";
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel文件|*.xlsx";
if (sfd.ShowDialog() == DialogResult.Cancel)
{
return null;
}
path += sfd.FileName;
}
return path;
}