/// <summary> /// 添加图表 /// </summary> /// <param name="p_range"></param> SpreadChart AddChart(CellRange p_range) { Worksheet sheet = _excel.ActiveSheet; Rect rc = sheet.GetRangeLocation(p_range); string rangeFormula = sheet.Cells[p_range.Row, p_range.Column, p_range.RowCount + p_range.Row - 1, p_range.ColumnCount + p_range.Column - 1].ToString(this.Excel.ActiveSheet.Cells[0, 0]); rangeFormula = "'" + sheet.Name + "'!" + rangeFormula; SpreadChart chart = sheet.AddChart("table" + Kit.NewGuid, SpreadChartType.ColumnClustered, rangeFormula, rc.Left, rc.Top + rc.Height + 8, 600, 300); chart.Legend.Orientation = Orientation.Vertical; StringBuilder builder = new StringBuilder("chart"); builder.Append(sheet.Charts.Count.ToString()).Append("!"); builder.Append(p_range.Row.ToString()).Append(",").Append(p_range.Column.ToString()).Append(","); builder.Append(p_range.RowCount.ToString()).Append(",").Append(p_range.ColumnCount.ToString()); chart.Name = builder.ToString(); chart.PropertyChanged += OnPropertyChanged; chart.IsSelected = true; sheet.SetActiveCell(0, 0, true); return(chart); }
/// <summary> /// 输出图表 /// </summary> /// <param name="p_ws"></param> /// <param name="p_row"></param> /// <param name="p_col"></param> public void Render(Worksheet p_ws, int p_row, int p_col) { if (!ValidFilds()) { return; } Rect rc = p_ws.GetRangeLocation(new CellRange(p_row, p_col, RowSpan, ColSpan)); var chartType = GetChartType(); SpreadChart c = p_ws.AddChart("chart" + p_ws.Charts.Count.ToString(), chartType, rc.Left, rc.Top, rc.Width, rc.Height); // 锁定图表,禁止拖动缩放 c.Locked = true; string title = _data.Str("title"); if (!string.IsNullOrEmpty(title)) { c.ChartTitle = new ChartTitle { Text = title } } ; title = _data.Str("titlex"); if (!string.IsNullOrEmpty(title)) { c.AxisX.Title = new ChartTitle { Text = title } } ; title = _data.Str("titley"); if (!string.IsNullOrEmpty(title)) { c.AxisY.Title = new ChartTitle { Text = title } } ; if (ShowLegend) { c.Legend.Text = _data.Str("legtitle"); if (Enum.TryParse <Dt.Charts.LegendPosition>(_data.Str("legpos"), out var pos)) { c.Legend.Alignment = GetLegendAlignment(pos); } if (Enum.TryParse <Orientation>(_data.Str("legorientation"), out var ori)) { c.Legend.Orientation = ori; } } else { c.Legend = null; } if (string.IsNullOrEmpty(FieldSeries)) { LoadTable(c); } else { LoadMatrix(c); } } void LoadMatrix(SpreadChart p_chart) { // Build()中已判断空的情况 RptData data = _part.Inst.Info.GetData(Tbl).Result; var tbl = data.Data.CreateMatrix(FieldX, FieldSeries, FieldY); for (int i = 0; i < tbl.Columns.Count; i++) { string colName = tbl.Columns[i].ID; if (colName == FieldX) { continue; } SpreadDataSeries ser = new SpreadDataSeries(); ser.Name = colName; DoubleSeriesCollection vals = new DoubleSeriesCollection(); foreach (var row in tbl) { try { double val = row.Double(i); vals.Add(val); } catch { vals.Add(0); } } ser.Values = vals; p_chart.DataSeries.Add(ser); } // 添加系列数据时已创建x轴Items,只能重新设置x轴 p_chart.AxisX.Items.Clear(); for (int i = 0; i < tbl.Count; i++) { p_chart.AxisX.Items.Add(tbl[i].Str(FieldX)); } } void LoadTable(SpreadChart p_chart) { RptData data = _part.Inst.Info.GetData(Tbl).Result; SpreadDataSeries ser = new SpreadDataSeries(); DoubleSeriesCollection vals = new DoubleSeriesCollection(); foreach (var row in data.Data) { try { double val = row.Double(FieldY); vals.Add(val); } catch { vals.Add(0); } } ser.Values = vals; p_chart.DataSeries.Add(ser); p_chart.AxisX.Items.Clear(); foreach (var row in data.Data) { p_chart.AxisX.Items.Add(row.Str(FieldX)); } } /// <summary> /// 判断数据表字段是否完整 /// </summary> /// <returns></returns> bool ValidFilds() { if (string.IsNullOrEmpty(Tbl)) { Kit.Msg("数据源不可为空。"); return(false); } string type = _data.Str("type"); if (type == "Gantt") { if (string.IsNullOrEmpty(FieldZ) || string.IsNullOrEmpty(FieldX) || string.IsNullOrEmpty(FieldY)) { Kit.Msg("任务字段、起始时间字段及终止时间字段均不可为空,图表生成失败。"); return(false); } } else { if (string.IsNullOrEmpty(FieldX) || string.IsNullOrEmpty(FieldY)) { Kit.Msg("分类字段和值字段不可为空,图表生成失败。"); return(false); } } return(true); } SpreadChartType GetChartType() { if (Enum.TryParse <ChartType>(_data.Str("type"), out var tp)) { switch (tp) { case ChartType.Bar: return(SpreadChartType.BarClustered); case ChartType.BarStacked: return(SpreadChartType.BarStacked); case ChartType.Column: return(SpreadChartType.ColumnClustered); case ChartType.ColumnStacked: return(SpreadChartType.ColumnStacked); case ChartType.Line: return(SpreadChartType.Line); case ChartType.LineSmoothed: return(SpreadChartType.LineSmoothed); case ChartType.LineStacked: return(SpreadChartType.LineStacked); case ChartType.LineSymbols: return(SpreadChartType.LineWithMarkers); case ChartType.LineSymbolsSmoothed: return(SpreadChartType.LineWithMarkersSmoothed); case ChartType.LineSymbolsStacked: return(SpreadChartType.LineStackedWithMarkers); case ChartType.Pie: return(SpreadChartType.Pie); case ChartType.PieExploded: return(SpreadChartType.PieExploded); case ChartType.PieDoughnut: return(SpreadChartType.PieDoughnut); case ChartType.PieExplodedDoughnut: return(SpreadChartType.PieExplodedDoughnut); case ChartType.Area: return(SpreadChartType.Area); case ChartType.AreaStacked: return(SpreadChartType.AreaStacked); case ChartType.AreaStacked100pc: return(SpreadChartType.AreaStacked); case ChartType.Radar: return(SpreadChartType.Radar); case ChartType.RadarSymbols: return(SpreadChartType.RadarWithMarkers); case ChartType.RadarFilled: return(SpreadChartType.RadarFilled); case ChartType.XYPlot: return(SpreadChartType.Scatter); case ChartType.Bubble: return(SpreadChartType.Bubble); case ChartType.Candle: return(SpreadChartType.StockHighLowOpenClose); } } return(SpreadChartType.ColumnClustered); } LegendAlignment GetLegendAlignment(Charts.LegendPosition pos) { switch (pos) { case Charts.LegendPosition.TopLeft: return(LegendAlignment.TopLeft); case Charts.LegendPosition.TopRight: return(LegendAlignment.TopRight); case Charts.LegendPosition.TopCenter: return(LegendAlignment.TopCenter); case Charts.LegendPosition.Left: return(LegendAlignment.MiddleLeft); case Charts.LegendPosition.Right: return(LegendAlignment.MiddleRight); case Charts.LegendPosition.BottomLeft: return(LegendAlignment.BottomLeft); case Charts.LegendPosition.BottomCenter: return(LegendAlignment.BottomCenter); case Charts.LegendPosition.BottomRight: return(LegendAlignment.BottomRight); } return(LegendAlignment.BottomCenter); }