/// <summary>生成直方图</summary> public static HtmlChart BuildChart(string excelFile) { var sheet = ExcelUtil.OpenFirst(excelFile); var chart = new HtmlChart(); chart.Init(); var setting = chart.Setting; setting.Init(); setting.Smooth = true; setting.DefautChart = "bar"; var colIndex = 0; setting.Title = sheet.GetCellValueAsString(ChartTilteRow - 1, colIndex); setting.YAxisCaption = sheet.GetCellValueAsString(YAxisCaptionRow - 1, colIndex); //setting.XAxisCaption = sheet.GetCellValueAsString(LegendTilteRow - 1, colIndex); var firstColIndex = colIndex; BuildChartLegend(sheet, chart, setting, LegendTilteRow - 1, firstColIndex); if (chart.Columns.Count == 0) { throw new Exception("图表图例不存在!"); } setting.XAxisField = chart.Columns[0].Field; setting.XAxisCaption = chart.Columns[0].Title; var lastColIndex = setting.Legend.Count; var firstRowIndex = LegendTilteRow; BuildChartData(sheet, chart, firstRowIndex, firstColIndex, lastColIndex); if (chart.Rows.Count == 0) { throw new Exception("图表数据不存在!"); } return(chart); }
/// <summary>生成图例</summary> private static void BuildChartLegend(ISheet sheet, HtmlChart chart, HtmlChartSettings setting, int rowIndex, int firstColIndex) { var row = sheet.GetRow(rowIndex); if (row == null) { return; } for (int i = firstColIndex; i <= row.LastCellNum; i++) { var cell = row.GetCell(i); if (cell == null) { break; } var title = cell.GetCellValue(cell.CellType).ToString(); if (title.IsNullOrEmpty()) { break; } var column = new HtmlTableColumn(); column.Field = "field" + i.ToString(); column.Title = title; if (i == firstColIndex) { column.Type = JsonDataType.String; } else { column.Type = JsonDataType.Number; setting.Legend.Add(title); } chart.Columns.Add(column); } }
/// <summary>生成数据</summary> private static void BuildChartData(ISheet sheet, HtmlChart chart, int firstRowIndex, int firstColIndex, int lastColIndex) { var lastRowIndex = sheet.LastRowNum; for (int i = firstRowIndex; i <= lastRowIndex; i++) { var row = sheet.GetRow(i); if (row == null) { break; } var values = sheet.GetRowValues(row, firstColIndex, lastColIndex); if (values.Count == 0) { break; } if (values[0] == null) { break; } var xAxisTitle = values[0].ToString(); if (xAxisTitle.Length == 0) { break; } values[0] = xAxisTitle; for (int j = 1; j < values.Count; j++) { var value = values[j]; if (value == null) { continue; } if (value is bool) { continue; } if (value is double) { continue; } var sValue = value.ToString(); if (sValue.Length == 0) { values[j] = null; } else { values[j] = double.Parse(sValue); } } chart.Rows.Add(values); } }