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();
}
}
