psychcoder

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!

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

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.

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

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

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".