Flex to Excel or Excel to Flex, now that is the question!

Greetings,

Since my last challenge of getting Flex to create a PDF file using Flash Player 10 went so well, it was decided that I should tackle Excel files. This proved to be a challenge, since the library I decided to use doesn't play well with itself and I thought I was doing something wrong.

Being the lazy person I am, I tried the Google search again and came across a number of different ways to handle Excel files. I came across Sasa Radovanovic' Blog, where Sasa created an HTML file that could be imported into Excel. CFLEX.NET had an example for importing the DataGrid to Excel via the Clipboard. And finally, I found an example on the Adobe site that imported and exported data out. So I used the from Adobe as my example with a few tweaks here and there.

The example from Adobe used the as3xls Excel Library hosted on code.google.com. After doing some research, I found that the library has some issues with writing Excel files out. Excel can read the exported files, but the files are just not in the correct format. So to read the files back in, you have to open the file you exported in Excel, save the file in Excel (Excel was nice enough to correct the formatting errors), and then you can import the file back in using the library. Looking at some of the dates on the project, it does not look like the original author is updating the library. I did find Dan Wilson's site, where he has taken a fork from the original library and updated it to fix a few problems. You can either use the original or Dan 's version. Either version will work for the sample below.

If the Excel file is not in the correct format when you try to read it in, you will get an error like this one. "TypeError: Error #1009: Cannot access a property or method of a null object reference."  Open the file you are trying to import in Excel, save it, and try re-importing and see if that fixes your problem.

Enjoy,

William Lorfing

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute">
    <mx:Script>
    <![CDATA[
        // Taken from http://cookbooks.adobe.com/post_Import_Export_data_in_out_of_a_Datagrid_in_Flex-17223.html
        //
        import com.as3xls.xls.Cell;
        import mx.collections.ArrayCollection;
          
        import com.as3xls.xls.Sheet;
        import com.as3xls.xls.ExcelFile;
        import mx.controls.dataGridClasses.DataGridColumn;
                 
        private var fileReference:FileReference;
        private var sheet:Sheet;
             
        [Bindable]
        private var fields:Array = new Array(); 
        private var ItemDGDataProvider:ArrayCollection = new ArrayCollection([
            {name:"Item1",value:"21",qty:"3",cost:"12.21"},
            {name:"Item2",value:"20",qty:"4",cost:"12.22"},
            {name:"Item3",value:"22",qty:"5",cost:"12.23"},
            {name:"Item4",value:"23",qty:"2",cost:"12.24"}
            ]);
          
        [Bindable]
        private var rebateDGDataProvider:ArrayCollection = new ArrayCollection();
     
        private function browseAndUpload():void
        {
            fileReference = new FileReference();
            fileReference.addEventListener(Event.SELECT,fileReference_Select);
            fileReference.addEventListener(Event.CANCEL,fileReference_Cancel);
            fileReference.browse();
        }
        private function fileReference_Select(event:Event):void
        {
            fileReference.addEventListener(ProgressEvent.PROGRESS,fileReference_Progress);
            fileReference.addEventListener(Event.COMPLETE,fileReference_Complete);
            fileReference.addEventListener(IOErrorEvent.IO_ERROR, onLoadError);
            fileReference.load();  
        }
        private function fileReference_Cancel(event:Event):void
        {
            fileReference = null;
        }
        private function fileReference_Progress(event:ProgressEvent):void
        {
            progressBar.visible = true;
            progressBar.includeInLayout = true;
        }
        private function onLoadError():void
        {
               /*body not implemented*/
        }
        private function fileReference_Complete(event:Event):void
        {
            var fileData:ByteArray  = fileReference.data;
            var excelFile:ExcelFile = new ExcelFile();
            var noOfRows:int;
            var noOfColumns:int;
            if(fileData!=null && fileData.length > 0){
                    excelFile.loadFromByteArray(fileData);
                    var sheet:Sheet = excelFile.sheets[0];
                    if(sheet!=null)
                       {
                        noOfRows=sheet.rows;
                        noOfColumns = sheet.cols;
                        for(var row:int = 0; row<noOfRows;row++)
                        {
                            var cellObject:Object ={};
                            for(var col:int=0;col<noOfColumns;col++)
                            {
                                var cell:Cell = new Cell();
                                var cellValue:String = new String();
                                cell = sheet.getCell(row,col);
                                if(cell!=null)
                                {
                                    cellValue =(cell.value).toString();
                                    addProperty(cellObject,col,cellValue);
                                }
                            }// inner for loop ends
                         
                            rebateDGDataProvider.addItem(cellObject);
                        } //for loop ends
                    } //if sheet  
                } //if filedata
            progressBar.visible = false;
            progressBar.includeInLayout =false;
            rebateScheduleDG.includeInLayout = true;
            rebateScheduleDG.visible = true;
            fileReference = null;
        }
        private function addProperty(cellObject:Object,index:int,cellValue:String):void
        {
            if(index == 0)
                cellObject.cost = cellValue;
            else if(index == 1)
                cellObject.name = cellValue;
            else if(index == 2)
                cellObject.qty = cellValue;
            else if(index == 3)
                cellObject.value = cellValue;
        }
     
        private function exportToExcel():void
        {
            sheet = new Sheet();
            var dataProviderCollection:ArrayCollection = rebateByItemDG.dataProvider as ArrayCollection;
            var rowCount:int =  dataProviderCollection.length;
            sheet.resize(rowCount+4,10);
            sheet.setCell(0,0,"Item Name");
            sheet.setCell(0,1,"Item Cost");
            sheet.setCell(0,2,"Item Qty");
            sheet.setCell(0,3,"Item Price");
            var columns:Array = rebateByItemDG.columns; 
            var i:int = 0; 
            for each (var field:DataGridColumn in columns){ 
                fields.push(field.dataField.toString());
                sheet.setCell(0,i,field.dataField.toString()); 
                i++; 
            }
                                   
            for(var r:int=0;r<rowCount;r++)
            {
                var record:Object = dataProviderCollection.getItemAt(r);
                /*insert record starting from row no 2 else
                    headers will be overwritten*/
                insertRecordInSheet(r+2,sheet,record);
            }
            var xls:ExcelFile = new ExcelFile();
            xls.sheets.addItem(sheet);
           
            var bytes: ByteArray = xls.saveToByteArray();
            var fr:FileReference = new FileReference();
            fr.save(bytes,"SampleExport.xls");
        }
     
        private function insertRecordInSheet(row:int,sheet:Sheet, record:Object):void
        { 
            var colCount:int = rebateByItemDG.columnCount; 
            for(var c:int; c < colCount; c++) 
            { 
                var i:int = 0; 
                for each(var field:String in fields){ 
                    for each (var value:String in record){ 
                        if (record[field].toString() == value) 
                            sheet.setCell(row,i,value); 
                        } // if 
                        i++; 
                    } // for record 
                } // for fields 
            } // for colCount 
        
    ]]>
    </mx:Script>
   
        <mx:FormItem label="Do you want to import your items from Excel?" fontWeight="bold">
                <mx:Form>
                    <mx:FormItem label="Browse you excel file" fontWeight="bold">
                        <mx:Button label="Browse" click="browseAndUpload()"/>
                    <mx:HBox>
                        <mx:ProgressBar id="progressBar" includeInLayout="false" visible="false"
                            indeterminate="true"/>
                    </mx:HBox>
                    </mx:FormItem>
                    <mx:DataGrid id="rebateScheduleDG" includeInLayout="false" visible="false"
                            dataProvider="{rebateDGDataProvider}" width="100%"/>
                </mx:Form>
                            <mx:FormItem label="Export Datagrid items to Excel?" fontWeight="bold">
                <mx:Form>
                    <mx:HBox width="100%" verticalAlign="middle">
                        <mx:DataGrid id="rebateByItemDG" includeInLayout="true" visible="true"
                            dataProvider="{ItemDGDataProvider}" width="100%" editable="true"/>
                        <mx:Button label="Export To Excel" click="exportToExcel();"/>
                    </mx:HBox>
                </mx:Form>
            </mx:FormItem>

            </mx:FormItem>

</mx:Application>

Comments

[Bill]

Great walk through! I'm using it primarily for export to excel only. One issue that I am running into is the fact that the first time I export, things export just fine (right number of columns and rows) but each subsequent time I run it, additional columns for the data gets added. For example if my datagrid and provider has 5 columns and 5 rows, first time I run it, I get an excel file with a 5 by 5 data that I'm expecting. The second time I run it, I get an excel file with 10 columns and 5 rows(only first 5 have headings), 3rd time I run it, I get 15 columns and 5 rows... so on and so on. Any suggestions on what may be causing the issue? I've gone in and set the variables to null first and them set them to the length of the arrays but same issue.

[William Lorfing]

Bill, I am not sure.

Does this only happen when you try it multiple times without closing the program or does it happen after closing the program and restarting?

If the program is still running, it sounds like something isn't getting cleared or reset before the next round.

[Bill]

Thanks for the reply William. It only happens when I try multiple times without closing out the program. If I hit refresh on my browser to reload the flex app, the first time I run the function, it works as expected. Just when I export again without reloading the app is when the duplication occurs.

[Bill]

William - think I found the culprit. Looks like the fields array isn't cleared each time the exportToExcel() is called so with each additional run, it appends the data to the array. I added a fields = new Array() in the exportToExcel() function and seems to work properly now.

[Shaning]

I tried but got failed at

excelFile.loadFromByteArray(fileData); // in fileReference_Complete(event:Event):void

(handlers[r.type] as Function).call(this, r, currentSheet); // in ExcelFiles.loadFromByteArray()

and

// in ExcelFiles
private function builtinfmtcount(r:Record, s:Sheet):void {
var numBuildInFormats:uint = r.data.readUnsignedShort();
}

[William Lorfing]

Can you explain more about what the problem is or what error message you are getting?

William

[Amar]

Will Export to excel work for multiple sheets?

[William Lorfing]

You should be able to.

You should be able to change the
var sheet:Sheet = excelFile.sheets[0];

to the next sheet.

William

[ivan jacobs]

Hey thx for this. I have issue with multiple sheets.
When I save to excel only one sheet is visible. In the debug there is a array of sheets but in the excel file only the first shows
var xls:ExcelFile = new ExcelFile();
xls.sheets.addItemAt(sheet,0);
xls.sheets.addItemAt(sheet2,1);
xls.sheets.addItemAt(sheet3,2);
//var bytes: ByteArray = xls.saveToByteArray();
var fr:FileReference = new FileReference();
fr.save(xls.saveToByteArray(),"SampleExport.xls");
}

[madhu]

hi,

i am exporting and importing with above code and geting the error while importing exported file.