将弹性图表和数据网格导出到 excel [英] Export flex chart and datagrid to excel
本文介绍了将弹性图表和数据网格导出到 excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要将 flex 4.5 中的饼图和数据网格导出为 Microsoft Excel 格式.我能够使用 as3xls 导出数据网格.但它不允许导出任何图表，甚至不允许将图像添加到 Excel 文件中.任何人都可以推荐我这样做的方法吗?
I need to export a pie chart and a datagrid in flex 4.5 to Microsoft Excel format. I was able to export the datagrid using as3xls. But it doesn't allow to export any chart or even add an image to excel file. Can anyone recommend me a way of doing this?
推荐答案
据我所知，无法通过现有库将图表导出到 Excel.
As far as I know there is no way to export a chart to Excel by means of existing libraries.
我想向您推荐一种具体的方法.我们都知道当前的 Office 文件存储为 XML 结构的集合.这些文件被打包为 zip 存档.如果你看一看这些东西，你会发现只要操作这个文件中的一些行就可以得到你想要的任何表格、任何图表、任何图片.
I would like to suggest you one specific way to do it. We all well know that current Office files are stored as collections of XML structures. The files are packed as a zip-archive. If you have a look at this stuff, you can see that it is possible just to manipulate some lines in this files to get any table, any chart, any picture you'd like to have.
要在 Flex 中执行此操作，您需要基本的 XML 结构和一些能够压缩结果的库.
To do it in Flex you need basic XML-structures and some library to be able to zip the result.
我使用了 Nochump 组件来访问 zip 功能.
I have used Nochump component to have access to zip-functions.
在这里您可以阅读有关 Excel 文件结构的信息.
Here you can read about the structure of Excel-files.
以下是一些图片，向您展示了使用此方法可能实现的结果.
Here are some pictures which show you a possible result you can achieve with this method.
关于应用程序，您应该知道必须修补哪些文件:
Regarding the application, you should know which files have to be patched:
- sheet1.xml 包含有关工作表单元格的信息
- sharedStrings.xml 是文件中所有字符串的字典
- chart1.xml 是图表的描述 - 您应该只更改数据范围.
为了让它工作，我创建了一个包含 Excel 文件树描述的 XML 结构.这个文件树的实际 XML 文件我放在我的项目文件夹中.然后我将所有文件读取到一个 ArrayCollection 并处理其中三个的数据.
To get it working I created an XML-structure with description of the Excel-file-tree. The actual XML-files of this file-tree I put to my projects folder. Then I read all the files to an ArrayCollection and manipulated data of three of them.
毕竟我将它们打包到一个 zip 存档中并让用户将其保存到 PC.
After all I packed them to a zip-archive and let user save it to the PC.
这是源代码:
<?xml version="1.0" encoding="utf-8"?>
<s:Application xmlns:fx="http://ns.adobe.com/mxml/2009"
xmlns:s="library://ns.adobe.com/flex/spark"
xmlns:mx="library://ns.adobe.com/flex/mx"
minWidth="955" minHeight="600"
creationComplete="onCreationComplete(event)">
<fx:Declarations>
<fx:XML id="fileTree" xmlns="">
<root id=''>
<folder id='_rels'>
<file id='.rels'/>
</folder>
<folder id='docProps'>
<file id='app.xml'/>
<file id='core.xml'/>
</folder>
<folder id='xl'>
<folder id='_rels'>
<file id='workbook.xml.rels'/>
</folder>
<folder id='charts'>
<file id='chart1.xml'/>
</folder>
<folder id='drawings'>
<folder id='_rels'>
<file id='drawing1.xml.rels'/>
</folder>
<file id='drawing1.xml'/>
</folder>
<folder id='theme'>
<file id='theme1.xml'/>
</folder>
<folder id='worksheets'>
<folder id='_rels'>
<file id='sheet1.xml.rels'/>
</folder>
<file id='sheet1.xml'/>
</folder>
<file id='sharedStrings.xml'/>
<file id='styles.xml'/>
<file id='workbook.xml'/>
</folder>
<file id='[Content_Types].xml'/>
</root>
</fx:XML>
</fx:Declarations>
<fx:Script>
<![CDATA[
import flash.utils.ByteArray;
import mx.collections.ArrayCollection;
import mx.collections.XMLListCollection;
import mx.controls.Alert;
import mx.controls.dataGridClasses.DataGridColumn;
import mx.events.FlexEvent;
import mx.rpc.xml.SimpleXMLDecoder;
import nochump.util.zip.*;
private const CELL_LETTERS:String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
private var xmlLoader:URLLoader = new URLLoader();
private var loaderItemId:int = 0;
private var rootPath:String = "com/excelchart/xmlsource/";
private var xlsxFiles:ArrayCollection = new ArrayCollection();
[Bindable]private var dp:ArrayCollection = new ArrayCollection([
{party:"SPD", y2009:23, y2005:34.2 },
{party:"CDU", y2009:27.3, y2005:27.8 },
{party:"FDP", y2009:14.6, y2005:9.8 },
{party:"The Left Party", y2009:11.9, y2005:8.7 },
{party:"A90/The Greens", y2009:10.7, y2005:8.1 },
{party:"CSU", y2009:6.5, y2005:7.4 },
{party:"Others", y2009:6, y2005:3.9 }]);
protected function onCreationComplete(event:FlexEvent):void
{
init();
traverseXMLList(fileTree, 0, "");
processNextNode();
}
private function traverseXMLList(xml:XML, depth:int, parentPath:String):void
{
var path:String = (parentPath == "") ? xml.@id : parentPath + "/" + xml.@id;
var nodeType:String = xml.name().localName;
xlsxFiles.addItem({name:xml.@id.toString(), type:nodeType, path:path});
for each (var item:XML in xml.children())
traverseXMLList(item, depth + 1, path);
}
private function packZip():void
{
var zipEntry:ZipEntry;
var fileName:String;
var fileData:ByteArray = new ByteArray();
var zipOut:ZipOutput = new ZipOutput();
for (var i:int = 0; i < xlsxFiles.length; i++)
{
var obj:Object = xlsxFiles.getItemAt(i);
if (obj.type == "file")
{
fileName = obj.path;
zipEntry = new ZipEntry(fileName);
fileData.clear();
fileData.writeUTFBytes(obj.content);
zipOut.putNextEntry(zipEntry);
zipOut.write(fileData);
zipOut.closeEntry();
}
}
// end the zip
zipOut.finish();
var file:FileReference = new FileReference();
file.save(zipOut.byteArray, "chart.xlsx");
}
private function init():void
{
xmlLoader.addEventListener(Event.COMPLETE, onXmlLoaderComplete);
xmlLoader.addEventListener(IOErrorEvent.IO_ERROR, onXmlLoaderIOError);
}
private function loadXML():void
{
xmlLoader.load(new URLRequest(rootPath + xlsxFiles.getItemAt(loaderItemId).path));
}
private function onXmlLoaderComplete(event:Event):void
{
xlsxFiles.getItemAt(loaderItemId).content = (event.currentTarget as URLLoader).data;
processNextNode();
}
private function processNextNode():void
{
if (xlsxFiles.length > loaderItemId + 1)
{
loaderItemId++;
if (xlsxFiles.getItemAt(loaderItemId).type == "file")
loadXML();
else
processNextNode();
}
else
this.btnMakeZip.enabled = true;
}
private function onXmlLoaderIOError(evt:IOErrorEvent):void
{
Alert.show("error!");
}
protected function onMakeZipClick(event:MouseEvent):void
{
parseDataGrid();
packZip();
}
protected function isString(input:String):Boolean
{
return isNaN(Number(input));
}
protected function parseDataGrid():void
{
function getStringId(str:String):int
{
var result:int = -1;
for (var i:int = 0; i < stringDictionary.length; i++)
if (stringDictionary.getItemAt(i) == str)
{
result = i;
break;
}
if (result == -1)
{
stringDictionary.addItem(str);
result = stringDictionary.length - 1;
}
return result;
}
//find sheet1 xml
var sheet1XML:XML;
var xlsxFilesItemId:int;
for (i = 0; i < xlsxFiles.length; i++)
if (xlsxFiles.getItemAt(i).name == "sheet1.xml")
{
sheet1XML = new XML(xlsxFiles.getItemAt(i).content);
xlsxFilesItemId = i;
break;
}
//define the size of the DG
var dgHeight:int = myGrid.dataProvider.length;
var dgWidth:int = myGrid.columns.length;
//namespaces for elements
var mainNS:Namespace = new Namespace("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
var x14acNS:Namespace = new Namespace("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
default xml namespace = mainNS;
sheet1XML.addNamespace(x14acNS);
sheet1XML.dimension.@ref = "A1:" + CELL_LETTERS.charAt(dgWidth) + (dgHeight + 1).toString();
//delete nodes from sheet1XML.sheetData
var sheetDataRowCollection:XMLListCollection = new XMLListCollection(sheet1XML.sheetData.row);
sheetDataRowCollection.removeAll();
//create a String dictionary
var stringDictionary:ArrayCollection = new ArrayCollection();
var stringId:int = 0;
var cellNode:XML, rowNode:XML, value:String, cellAddress:String;
var i:int, j:int;
//add head information
rowNode = new XML();
rowNode.addNamespace(mainNS);
rowNode.addNamespace(x14acNS);
rowNode = <row r="1" spans="1:3"/>;
rowNode.@x14acNS::dyDescent = "0.25";
for (j = 0; j < dgWidth; j++)
{
value = (((myGrid.columns as ArrayList).getItemAt(j) as GridColumn).headerText).toString();
cellAddress = CELL_LETTERS.charAt(j) + "1";
cellNode = new XML();
cellNode = <c r={cellAddress} t="s"><v>{getStringId(value).toString()}</v></c>;
rowNode = rowNode.appendChild(cellNode);
}
sheetDataRowCollection.addItem(rowNode);
//traverse through DG
for (i = 0; i < dgHeight; i++)
{
rowNode = new XML();
rowNode.addNamespace(mainNS);
rowNode.addNamespace(x14acNS);
rowNode = <row r={i + 2} spans="1:3"/>;
rowNode.@x14acNS::dyDescent = "0.25";
for (j = 0; j < dgWidth; j++)
{
value = (myGrid.dataProvider.getItemAt(i)[((myGrid.columns as ArrayList).getItemAt(j) as GridColumn).dataField]).toString();
cellAddress = CELL_LETTERS.charAt(j) + (i+2).toString();
cellNode = new XML();
if (isString(value))
cellNode = <c r={cellAddress} t="s"><v>{getStringId(value).toString()}</v></c>;
else
cellNode = <c r={cellAddress}><v>{value}</v></c>;
rowNode = rowNode.appendChild(cellNode);
}
sheetDataRowCollection.addItem(rowNode);
}
//save sheet1 to xlsxFiles
xlsxFiles.getItemAt(xlsxFilesItemId).content = sheet1XML;
//sharedStrings
var sharedStringsXML:XML;
for (i = 0; i < xlsxFiles.length; i++)
if (xlsxFiles.getItemAt(i).name == "sharedStrings.xml")
{
sharedStringsXML = new XML(xlsxFiles.getItemAt(i).content);
xlsxFilesItemId = i;
break;
}
//delete nodes from sharedStrings.xml
var sharedStringsCollection:XMLListCollection = new XMLListCollection(sharedStringsXML.si);
sharedStringsCollection.removeAll();
//fill the sharedStrings XML
sharedStringsXML.@count = stringDictionary.length;
sharedStringsXML.@uniqueCount = stringDictionary.length;
//var siNode:XML;
for each (var str:String in stringDictionary)
sharedStringsXML.appendChild(<si><t>{str}</t></si>);
//save sharedStrings to xlsxFiles
xlsxFiles.getItemAt(xlsxFilesItemId).content = sharedStringsXML;
//chart1
var chart1XML:XML;
for (i = 0; i < xlsxFiles.length; i++)
if (xlsxFiles.getItemAt(i).name == "chart1.xml")
{
chart1XML = new XML(xlsxFiles.getItemAt(i).content);
xlsxFilesItemId = i;
break;
}
var catLetter:String = CELL_LETTERS.charAt(cbCategories.selectedIndex);
var catAddress:String = "Tabelle1!$" + catLetter + "$2:$" + catLetter + "$" + (dgHeight + 1).toString();
var valLetter:String = CELL_LETTERS.charAt(cbValues.selectedIndex);
var valAddress:String = "Tabelle1!$" + valLetter + "$2:$" + valLetter + "$" + (dgHeight + 1).toString();
default xml namespace = new Namespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
chart1XML.chart.plotArea.pieChart.ser.cat.strRef.f = catAddress;
chart1XML.chart.plotArea.pieChart.ser.val.numRef.f = valAddress;
xlsxFiles.getItemAt(xlsxFilesItemId).content = chart1XML;
//switch back to the default namespace
default xml namespace = new Namespace("");
}
private function onBtnRefresh():void
{
this.mySeries.nameField = cbCategories.selectedItem.dataField;
this.mySeries.field = cbValues.selectedItem.dataField;
}
]]>
</fx:Script>
<s:HGroup x="100" y="50">
<s:VGroup>
<s:DataGrid id="myGrid" width="360" dataProvider="{dp}">
<s:columns>
<s:ArrayList>
<s:GridColumn dataField="party" headerText="Party"/>
<s:GridColumn dataField="y2005" headerText="2005" width="90"/>
<s:GridColumn dataField="y2009" headerText="2009" width="90"/>
</s:ArrayList>
</s:columns>
</s:DataGrid>
<s:HGroup verticalAlign="bottom">
<s:Label text="Categories:" width="70"/>
<s:ComboBox id="cbCategories" dataProvider="{myGrid.columns}" labelField="headerText" selectedIndex="0"/>
</s:HGroup>
<s:HGroup verticalAlign="bottom">
<s:Label text="Values:" width="70"/>
<s:ComboBox id="cbValues" dataProvider="{myGrid.columns}" labelField="headerText" selectedIndex="1"/>
</s:HGroup>
<s:HGroup>
<s:Button id="btnRefresh" label="Bild Chart" click="onBtnRefresh()"/>
<s:Button id="btnMakeZip" label="Export" enabled="false" click="onMakeZipClick(event)"/>
</s:HGroup>
</s:VGroup>
<mx:PieChart id="myChart" width="281" height="277" dataProvider="{dp}" showDataTips="true">
<mx:series>
<mx:PieSeries id="mySeries" field="y2005" nameField="party" labelPosition="inside" explodeRadius=".12" />
</mx:series>
</mx:PieChart>
<mx:Legend dataProvider="{myChart}"/>
</s:HGroup>
</s:Application>
希望能帮到你.感谢您提出有趣的问题！
I hope it can help you. Thanks for the interesting question!
这篇关于将弹性图表和数据网格导出到 excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
