RE: Excel Exporter

psychcoder — Tue, 19 Dec 2006 12:32:09 GMT

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

RE: Excel Exporter

Pallav — Mon, 18 Dec 2006 23:29:21 GMT

The idea sounds really good. Thanks for the effort.

Just curious - do you have this hosted online, so that we can see it live?

RE: Excel Exporter

psychcoder — Mon, 18 Dec 2006 15:09:59 GMT

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

psychcoder — Mon, 18 Dec 2006 14:58:03 GMT

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

FusionCharts Forum / FusionCharts v3 / User Showcase / Excel Exporter / Latest Posts

RE: Excel Exporter

RE: Excel Exporter

RE: Excel Exporter

Excel Exporter