|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/4/2007 1:39:36 PM
Posts: 6,
Visits: 4
|
|
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;
}
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/4/2007 1:39:36 PM
Posts: 6,
Visits: 4
|
|
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
|
|
|
|
|
FusionCharts Team
Group: Administrators
Last Login: 7/2/2008 7:57:52 PM
Posts: 1,956,
Visits: 468
|
|
| The idea sounds really good. Thanks for the effort. Just curious - do you have this hosted online, so that we can see it live?
Thanks,
Pallav Nadhani
FusionCharts Team
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/4/2007 1:39:36 PM
Posts: 6,
Visits: 4
|
|
| 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
|
|
|
|