Multi Series Chart Error: Small datasets

[Multi Series Chart Error: Small datasets]

Thanks for the fast reply and sending me that file and script so quick. You guys are awesome. Can't wait till you build the true 3d charts for Flash 9!

[Excel Exporter]

Definitely online, the application launches in a few weeks when the main website is done.  If you would like to see it in action in the meantime feel free to email me and setup a meeting, would love to show you guys what we did with your charts. This is for a academic research tool for qualatative and quantative data analysis. 

- J

[In need of a unique type of interactive chart in Flash?]

Would love to see a full blown 3d chart with the ability to rotate, zoom, support for lines, surfaces, points, color shifting via z axis. This may have to wait till flash 9 is fully deployed to use the VM, but certainly possible in flash 7/8.

[Excel Exporter]

so for some reason the forum butchered all the for loops, replace ":" with ";"

 

 

 

here they are

 

 

 

1) x - iterate from 0 to xmlRoot.ChildNodes.Count

 

 

 

2) r iterate from 0 to chartData.Count

 

 

 

3) x - iterate from 0 to xmlRoot.ChildNodes.Count

 

 

 

4) k - k iterate from 0 to valCount

 

 

 

5) k - iterate from 0 to xmlRoot.ChildNodes.Count

 

 

 

6) x - iterate from 0 to eriesNames.Count

 

 

 

7) x - iterate from 0 to eriesNames.Count

[Excel Exporter]

So i have a flash app with a c# webservice backend, client wanted to be able to export the charts. Wrote this to export to excel with table data and chart. This code is messy as all hell as I haven't begun cleaning it up, but here we go.

 

 

 

make sure you have a reference to "Microsoft Excel 11.0 Object Library" in your project.

 

 

 

Again, this code needs to be cleaned and refactered, was written in 6 hrs with no previous experiance dealing with Excel automation. If you need help with this send me a msg. Need to ask to checks if caption or axisnames / labels left blank.

 

 

 

using System;

 

using System.Data;

 

using System.Collections;

 

using System.IO;

 

using System.Text;

 

using System.Data;

 

using System.Reflection;

 

using System.Xml;

 

using Excel = Microsoft.Office.Interop.Excel;

 

 

 

namespace DataExporter

 

{

 

public class ChartItem

 

{

 

public string xItem;

 

public string yItem;

 

public ChartItem(string i_xItem, string i_yItem)

 

{

 

xItem = i_xItem;

 

yItem = i_yItem;

 

}

 

}

 

 

 

public class ChartExporter

 

{

 

Excel.Application xlApp;

 

Excel._Workbook xlBook;

 

Excel.Worksheet xlSheet;

 

 

 

private object missing;

 

 

 

public string ExportSingleSeries(string xmlData, string fullFileName)

 

{

 

InitExcel();

 

 

 

if(xmlData.Length>1)

 

{

 

XmlDocument xmlDoc = new XmlDocument();

 

xmlDoc.InnerXml = xmlData;

 

XmlElement xmlRoot = xmlDoc.DocumentElement;

 

 

 

//Get Chart Info from XML

 

string chartName, xAxisName, yAxisName, numberPrefix, showValues;

 

 

 

chartName = xmlRoot.GetAttribute("caption");

 

xAxisName = xmlRoot.GetAttribute("xAxisName");

 

yAxisName = xmlRoot.GetAttribute("yAxisName");

 

numberPrefix = xmlRoot.GetAttribute("numberPrefix");

 

showValues = xmlRoot.GetAttribute("showValues");

 

 

 

ArrayList chartData = new ArrayList();

 

XmlNode curNode;

 

for(int x=0; x

 

{

 

curNode = xmlRoot.ChildNodes[x];

 

string curNodeName = curNode.Name;

 

switch(curNodeName)

 

{

 

case "set":

 

chartData.Add(new ChartItem(GetNodeVal(curNode,"label"), GetNodeVal(curNode,"value")));

 

break;

 

}

 

}

 

//rename the sheet

 

xlSheet.Name = chartName;

 

xlSheet.get_Range("A1", missing).Font.Size = 12;

 

xlSheet.get_Range("A1", missing).Font.Bold = true;

 

xlSheet.get_Range("A1","I1").Merge(missing);

 

xlSheet.get_Range("A1", missing).Value2 = chartName;

 

xlSheet.get_Range("A1", missing).EntireColumn.AutoFit();

 

//format headings;

 

 

 

Excel.Range myRange = xlSheet.get_Range("A3","B3");

 

myRange.Font.ColorIndex = 2;

 

myRange.Interior.ColorIndex = 5;

 

myRange.Font.Bold = true;

 

myRange.Font.Size = 10;

 

 

 

//columns heading

 

xlSheet.get_Range("A3", missing).Value2 = xAxisName;

 

xlSheet.get_Range("A3", missing).BorderAround(missing, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

xlSheet.get_Range("B3", missing).Value2 = yAxisName;

 

xlSheet.get_Range("B3", missing).BorderAround(missing, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

 

 

//pump data

 

ChartItem curChartItem;

 

int cellRow;

 

for(int r=0; r

 

{

 

cellRow = (3+chartData.Count)-r;

 

curChartItem = (ChartItem)chartData[r];

 

xlSheet.get_Range("A"+cellRow,missing).Value2 = curChartItem.xItem;

 

xlSheet.get_Range("A"+cellRow,missing).BorderAround(missing, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

xlSheet.get_Range("B"+cellRow,missing).Value2 = numberPrefix + curChartItem.yItem;

 

xlSheet.get_Range("B"+cellRow,missing).BorderAround(missing, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

}

 

 

 

//Create the chart

 

Excel.ChartObjects oCharts = (Excel.ChartObjects)xlSheet.ChartObjects(missing);

 

Excel.ChartObject myChart = oCharts.Add(150,30,400,400);

 

Excel.Chart xlChart = myChart.Chart;

 

myRange = xlSheet.get_Range("A3","B"+(chartData.Count+3));

 

xlChart.SetSourceData(myRange, Excel.XlRowCol.xlColumns);

 

xlChart.ChartType = Excel.XlChartType.xlBarClustered;

 

xlChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone,missing, missing, missing, missing, missing, missing, missing, missing, missing);

 

xlChart.HasLegend = false;

 

//xlChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;

 

xlChart.HasTitle = true;

 

Excel.ChartGroup xlChartGroup = (Excel.ChartGroup) xlChart.ChartGroups(1);

 

xlChartGroup.VaryByCategories = true;

 

 

 

xlChart.ChartTitle.Text = chartName;

 

Excel.Axes xlAxisCategory, xlAxisValue;

 

 

 

xlAxisCategory = (Excel.Axes)xlChart.Axes(missing, Excel.XlAxisGroup.xlPrimary);

 

xlAxisCategory.Item(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = true;

 

xlAxisCategory.Item(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = xAxisName;

 

xlAxisValue = (Excel.Axes)xlChart.Axes(missing, Excel.XlAxisGroup.xlPrimary);

 

xlAxisValue.Item(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = true;

 

xlAxisValue.Item(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = yAxisName;

 

}

 

FinishExport(fullFileName);

 

return fullFileName;

 

}

 

 

 

 

 

public string ExportMultiSeries(string xmlData, string fullFileName)

 

{

 

InitExcel();

 

if(xmlData.Length>1)

 

{

 

XmlDocument xmlDoc = new XmlDocument();

 

xmlDoc.InnerXml = xmlData;

 

XmlElement xmlRoot = xmlDoc.DocumentElement;

 

 

 

//Get Chart Info from XML

 

string chartName, xAxisName, yAxisName, numberPrefix, showValues;

 

 

 

chartName = xmlRoot.GetAttribute("caption");

 

xAxisName = xmlRoot.GetAttribute("xAxisName");

 

yAxisName = xmlRoot.GetAttribute("yAxisName");

 

numberPrefix = xmlRoot.GetAttribute("numberPrefix");

 

showValues = xmlRoot.GetAttribute("showValues");

 

 

 

//rename the sheet

 

xlSheet.Name = chartName;

 

xlSheet.get_Range("A1", missing).Font.Size = 12;

 

xlSheet.get_Range("A1", missing).Font.Bold = true;

 

xlSheet.get_Range("A1","I1").Merge(missing);

 

xlSheet.get_Range("A1", missing).Value2 = chartName;

 

xlSheet.get_Range("A1", missing).EntireColumn.AutoFit();

 

 

 

ArrayList seriesNames = new ArrayList();

 

int seriesCount=0;

 

int valCount =0;

 

int cellRow;

 

 

 

XmlNode curNode, catNode, dataNode;

 

for(int x=0; x

 

{

 

curNode = xmlRoot.ChildNodes[x];

 

string curNodeName = curNode.Name;

 

switch(curNodeName)

 

{

 

case "categories":

 

valCount = curNode.ChildNodes.Count;

 

for(int k=0; k

 

{

 

catNode = curNode.ChildNodes[k];

 

cellRow = (3+valCount)-k;

 

xlSheet.get_Range("A"+cellRow,missing).Value2 = GetNodeVal(catNode,"label");

 

xlSheet.get_Range("A"+cellRow,missing).BorderAround(missing, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

}

 

 

 

break;

 

case "dataset":

 

seriesCount++;

 

seriesNames.Add(GetNodeVal(curNode, "seriesName"));

 

for(int k=0; k

 

{

 

dataNode = curNode.ChildNodes[k];

 

string colName = GetColName(seriesCount+1);

 

cellRow = (3+valCount)-k;

 

xlSheet.get_Range(colName+cellRow,missing).Value2 = GetNodeVal(dataNode,"value");

 

xlSheet.get_Range(colName+cellRow,missing).BorderAround(missing, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

}

 

break;

 

}

 

}

 

 

 

 

 

//format headings;

 

Excel.Range myRange = xlSheet.get_Range("A3",GetColName(seriesCount+1)+"3");

 

myRange.Font.ColorIndex = 2;

 

myRange.Interior.ColorIndex = 5;

 

myRange.Font.Bold = true;

 

myRange.Font.Size = 10;

 

 

 

//columns heading

 

xlSheet.get_Range("A3", missing).Value2 = xAxisName;

 

xlSheet.get_Range("A3", missing).BorderAround(missing, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

for(int x=0; x

 

{

 

xlSheet.get_Range(GetColName(x+2)+"3", missing).Value2 = (string) seriesNames[x];

 

xlSheet.get_Range(GetColName(x+2)+"3", missing).BorderAround(missing, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

 

}

 

 

 

//Create the chart

 

Excel.ChartObjects oCharts = (Excel.ChartObjects)xlSheet.ChartObjects(missing);

 

Excel.ChartObject myChart = oCharts.Add(150,30,400,400);

 

Excel.Chart xlChart = myChart.Chart;

 

myRange = xlSheet.get_Range("A4", GetColName(seriesCount+1) + (valCount+3));

 

xlChart.SetSourceData(myRange, Excel.XlRowCol.xlColumns);

 

xlChart.ChartType = Excel.XlChartType.xlBarClustered;

 

xlChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone,missing, missing, missing, missing, missing, missing, missing, missing, missing);

 

xlChart.HasLegend = true;

 

xlChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;

 

xlChart.HasTitle = true;

 

 

 

xlChart.ChartTitle.Text = chartName;

 

 

 

Excel.Series mySeries;

 

for(int x=0; x

 

{

 

mySeries = (Excel.Series)xlChart.SeriesCollection(x+1);

 

mySeries.Name = (string) seriesNames[x];

 

}

 

}

 

FinishExport(fullFileName);

 

return fullFileName;

 

}

 

 

 

private void InitExcel()

 

{

 

missing = System.Reflection.Missing.Value;

 

xlApp = new Excel.Application();

 

xlApp.DisplayAlerts = true;

 

xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

 

xlBook = xlApp.Workbooks.Add(missing);

 

while(xlBook.Worksheets.Count>1)

 

{

 

Excel.Worksheet tmpSheet = (Excel.Worksheet) xlBook.Worksheets[1];

 

tmpSheet.Delete();

 

}

 

xlApp.Visible = false;

 

xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];

 

}

 

private void FinishExport(string fullFileName)

 

{

 

xlBook.SaveAs(fullFileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

 

xlApp.Quit();

 

}

 

private string GetNodeVal(XmlNode theNode, string valueName)

 

{

 

return theNode.Attributes.GetNamedItem(valueName).Value;

 

}

 

private string GetColName(int colIndex) // takes positive, non zero, colIndex (1-x)

 

{

 

string colChars = "ABCDEFGHIJKLMNOPQRSTUV";

 

string returnStr = "";

 

if(colIndex>26)

 

{

 

int firstInt = (int) Math.Floor((colIndex*1.000)/26);

 

int seconInt = colIndex%26;

 

char firstChar = colChars[firstInt-1];

 

char seconChar = colChars[seconInt-1];

 

returnStr += firstChar + seconChar;

 

}

 

else

 

{

 

returnStr += colChars[colIndex-1];

 

 

 

}

 

return returnStr;

 

}

[Multi Series Chart Error: Small datasets]

When using multi series set, only first bar gets drawn if using only one set item per dataset. For example.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This data draws only the dataset "Male", and not the second dataset "Female".