出口弹性的图表和数据网格创先争优 [英] Export flex chart and datagrid to excel
本文介绍了出口弹性的图表和数据网格创先争优的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要导出一个饼图和柔性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 组件来获得拉链的功能。
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是图表的描述 - 你应该只改变 您的数据的范围。
- sheet1.xml has information about cells of your sheet
- sharedStrings.xml is a dictionary of all strings in the file
- chart1.xml is a description of your chart - you should change only ranges of your data.
要得到它的工作，我创建了一个XML结构与Excel的文件树的描述。这个文件树的实际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.
下面是源$ C $ C：
Here is the source code:
<?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!
这篇关于出口弹性的图表和数据网格创先争优的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
