Sign in to follow this  
Pallav

Excel Exporter

Recommended Posts

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;

 

}

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this