Excel Exporter

[psychcoder]

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;

 

}

[psychcoder]

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

[Pallav]

The idea sounds really good. Thanks for the effort.

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

[psychcoder]

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

[sunil233]

Hi,

This is sunil working on Fusion charts.One of my clients required the fusion charts to be exported to excel.I have seen your code .Great work.Can u send the sample apllication for exporting the chart data to the Excel file

my email:[email protected]

Thanx in Advance,

Sunil

[Guest Rajroop]

Hi Sunil,

 

 

 

Welcome to the FusionCharts Forum!

 

 

 

I'm afraid FusionCharts cannot export images to Excel directly, as of now.

 

 

 

Thanks you for your suggestion.