static void Main(string[] args) { SpreadsheetGear.IWorkbook gear = SpreadsheetGear.Factory.GetWorkbook(); SpreadsheetGear.IWorksheet worksheet = gear.Worksheets.Add(); SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo; // Load some sample data. SpreadsheetGear.IRange dataRange = worksheet.Cells["A1:B6"]; dataRange.Value = new string[, ] { { "A", "$7,923" }, { "B", "$5,954" }, { "C", "$5,522" }, { "D", "$3,701" }, { "E", "$5,522" }, { "F", "$3,701" } }; SpreadsheetGear.Shapes.IShape shape = worksheet.Shapes.AddChart(0, 0, 100, 100); SpreadsheetGear.Charts.IChart chart = shape.Chart; chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns); chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnStacked; chart.ChartGroups[0].GapWidth = 50; chart.HasTitle = false; chart.HasLegend = false; chart.PlotVisibleOnly = true; chart.ChartArea.Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178); chart.SeriesCollection[0].HasDataLabels = false; chart.SeriesCollection[0].HasLeaderLines = false; chart.SeriesCollection[0].MarkerStyle = SpreadsheetGear.Charts.MarkerStyle.Automatic; shape = worksheet.Shapes.AddChart(500, 500, 600, 600); chart = shape.Chart; chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns); chart.ChartType = SpreadsheetGear.Charts.ChartType.Pie; SpreadsheetGear.Charts.ISeries series = chart.SeriesCollection[0]; series.XValues = dataRange; // Add series data labels and change to show percentage only. series.HasDataLabels = true; series.DataLabels.ShowPercentage = true; series.DataLabels.ShowValue = false; series.DataLabels.ShowCategoryName = false; worksheet.Cells["F3"].NumberFormat = @"_-* #,##0.00_-;-* #,##0.00_-;_-@_-"; worksheet.Cells["F3"].Value = 3553654566.641; worksheet.Cells["F6"].Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178); worksheet.Cells["F6"].Font.Name = "Webdings"; worksheet.Cells["F6"].Value = "a"; worksheet.Cells["F9"].Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178); worksheet.Cells["F9"].Font.Name = "Webdings"; worksheet.Cells["F9"].Value = "r"; gear.SaveAs(@"D:\Excels.xls", SpreadsheetGear.FileFormat.OpenXMLWorkbook); }
private void BuildCharts(ExcelWriter writer) { writer.NewSheet("Charts"); int chartCount = 0; int left = 0; int top = 0; // Run through all left variables. foreach (ReportDefinitionVariable leftVariable in this.ReportDefinition.LeftVariables) { if (leftVariable.NestedVariables.Count > 0) { continue; } // Run through all top variables. foreach (ReportDefinitionVariable topVariable in this.ReportDefinition.TopVariables) { if (topVariable.NestedVariables.Count > 0) { continue; } SpreadsheetGear.Charts.IChart chart = writer.ActiveSheet.Shapes.AddChart(left, top, 500, 300).Chart; chartCount++; // Set the chart type. switch (this.ReportDefinition.Settings.DisplayType) { case DisplayType.Column: chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnClustered; break; case DisplayType.Line: chart.ChartType = SpreadsheetGear.Charts.ChartType.Line; break; case DisplayType.Bar: chart.ChartType = SpreadsheetGear.Charts.ChartType.BarClustered; break; case DisplayType.Area: chart.ChartType = SpreadsheetGear.Charts.ChartType.Area; break; default: chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnClustered; break; } chart.Axes[0].MaximumScale = 1.0; chart.HasTitle = true; chart.Axes[SpreadsheetGear.Charts.AxisType.Category].HasTitle = true; //chart.Axes[SpreadsheetGear.Charts.AxisType.Value].HasTitle = true; chart.ChartTitle.Text = leftVariable.Label; chart.Axes[SpreadsheetGear.Charts.AxisType.Category].AxisTitle.Text = topVariable.Label; Color _color = System.Drawing.ColorTranslator.FromHtml(this.ColorScheme["Color1"]["Color"]); SpreadsheetGear.Color color = SpreadsheetGear.Color.FromArgb(_color.A, _color.R, _color.G, _color.B); chart.ChartTitle.Font.Color = color; chart.Axes[SpreadsheetGear.Charts.AxisType.Category].AxisTitle.Font.Color = color; string axisFormula = "=Sheet1!"; if (topVariable.Scores.Count > 0) { axisFormula += this.ScoreNameReferences[topVariable.Scores[0].XmlNode.GetXPath(true)].Split('!')[1]; } if (topVariable.Scores.Count > 1) { axisFormula += ":" + this.ScoreNameReferences[topVariable.Scores[topVariable.Scores.Count - 1].XmlNode.GetXPath(true)].Split('!')[1]; } // Run through all scores of the left variable. foreach (ReportDefinitionScore leftScore in leftVariable.Scores) { string valuesFormula = "=Sheet1!"; // Create a new chart series for the left score. SpreadsheetGear.Charts.ISeries series = chart.SeriesCollection.Add(); //series.Name = "=Sheet1!B6"; series.Name = "=" + this.ScoreNameReferences[leftScore.XmlNode.GetXPath(true)]; if (topVariable.Scores.Count > 0) { Guid idTopScore = topVariable.Scores[0].Identity; if (this.ValueReferences[leftScore.Identity].ContainsKey(idTopScore)) { valuesFormula += this.ValueReferences[leftScore.Identity][idTopScore].Split('!')[1]; } } if (topVariable.Scores.Count > 1) { Guid idTopScore = topVariable.Scores[topVariable.Scores.Count - 1].Identity; if (this.ValueReferences[leftScore.Identity].ContainsKey(idTopScore)) { valuesFormula += ":" + this.ValueReferences[leftScore.Identity][idTopScore].Split('!')[1]; } } try { series.Values = valuesFormula; } catch { } for (int i = 0; i < topVariable.Scores.Count; i++) { series.Points[i].HasDataLabel = true; series.Points[i].DataLabel.Text = "=" + this.ValueReferences[leftScore.Identity][topVariable.Scores[i].Identity]; } series.XValues = axisFormula; } //chart.Axes[SpreadsheetGear.Charts.AxisType.Value].AxisTitle.Text = axisFormula; //chart.SetSourceData(writer.ActiveSheet.Cells[axisFormula], SpreadsheetGear.Charts.RowCol.Columns); left += 500; } left = 0; top += 300; } if (chartCount == 0) { writer.Workbook.Worksheets[0].Select(); } }