Not writing multiple sheets.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create new Excel file and add 2 or more sheets to it:

var excelFile:ExcelFile = new ExcelFile();
var sheet1:Sheet = new Sheet();
sheet1.resize(10,10);
sheet1.setCell(0,0,"Blah");
excelFile.sheets.addItem(sheet1);
var sheet2:Sheet = new Sheet();
sheet2.resize(10,10);
sheet2.setCell(0,0,"Whatever");
excelFile.sheets.addItem(sheet2);

2. Save the Excel file to a byte array and save as an .xls file:

var byteArray:ByteArray = excelFile.saveToByteArray();
Var fileRef:FileReference = new FileReference();
fileRef.save(byteArray,"test.xls"); 

3. Open the new .xls file in Excel.

What is the expected output? What do you see instead?

Expect to see an Excel spreadsheet with 2 sheets. See an Excel spreadsheet 
with one sheet (with first cell containing "Blah");

What version of the product are you using? On what operating system?

Version of as3xls.swc downloaded on March 26 2009.
Adobe Flex Bulder version 3.0
Flash Player version 10.0.22
Windows Server 2003 version 5.2 R2
Please provide any additional information below.

Original issue reported on code.google.com by leazes...@gmail.com on 30 Mar 2009 at 5:15

[GoogleCodeExporter]

Hi 

We are using the library to export xls files and we need to export more than 
one 
sheet only. It's a shame that a wonderful library like this has this defect so 
I'm 
trying to solve it.

The problem seems to be there in the ExcelFile.as

public function saveToByteArray():ByteArray {
            var s:Sheet = _sheets[0] as Sheet;

            var br:BIFFWriter = new BIFFWriter();

            // Write the BOF and header records
            var bof:Record = new Record(Type.BOF);
            bof.data.writeShort(BIFFVersion.BIFF2);
            bof.data.writeByte(0);
            bof.data.writeByte(0x10);
            br.writeTag(bof);

....etc

It seems that the method saveToByteArray() only exports the first sheet (var 
s:Sheet 
= _sheets[0] as Sheet;) in the excel file you have defined, wich has all the 
sheets 
in the_ sheets Array.

I'm playing with the output data looping the _sheets array but however the file 
size 
is the double with two pages that with one I'm getting an error of the excel 
program 
saying maybe I lost some data and outputing only one page.

I'm not experienced with ByteArray and I don't know if I could fix it. 

Some one helps with this??

Thank you in advance.           

Original comment by carcelen...@yahoo.es on 27 Aug 2009 at 9:16

[GoogleCodeExporter]

My try:

public function saveToByteArray():ByteArray {
            var br:BIFFWriter = new BIFFWriter();

            for(var v:int=0;v<_sheets.length;v++){
                var s:Sheet = _sheets[v] as Sheet;

                // Write the BOF and header records
                var bof:Record = new Record(Type.BOF);
                bof.data.writeShort(BIFFVersion.BIFF2);
                bof.data.writeByte(0);
                bof.data.writeByte(0x10);
                br.writeTag(bof);

                // Date mode
                var dateMode:Record = new Record(Type.DATEMODE);
                dateMode.data.writeShort(1);
                br.writeTag(dateMode);

                // Store built in formats
                var formats:Array = ["General", 
                    "0", "0.00", "#,##0", "#,##0.00", 
                    "", "", "", "",
                    "0%", "0.00%", "0.00E+00",
                    "#?/?", "#??/??",
                    "M/D/YY", "D-MMM-YY", "D-MMM", "MMM-YY"];

                var numfmt:Record = new Record(Type.BUILTINFMTCOUNT);
                numfmt.data.writeShort(formats.length);
                br.writeTag(numfmt);

                for(var n:uint = 0; n < formats.length; n++) {
                    var fmt:Record = new Record(Type.FORMAT);
                    fmt.data.writeByte(formats[n].length);
                    fmt.data.writeUTFBytes(formats[n]);
                    br.writeTag(fmt);
                }

                var dimensions:Record = new Record(Type.DIMENSIONS);
                dimensions.data.writeShort(0);
                dimensions.data.writeShort(s.rows+1);
                dimensions.data.writeShort(0);
                dimensions.data.writeShort(s.cols+1);
                br.writeTag(dimensions);

                for(var r:uint = 0; r < s.rows; r++) {
                    for(var c:uint = 0; c < s.cols; c++) {
                        var value:* = s.getCell(r, c).value;
                        var cell:Record = new Record(1);
                        cell.data.writeShort(r);
                        cell.data.writeShort(c);

                        if(value is Date) {
                            var dateNum:Number = 
(value.time / 86400000) + 24106.667;
                            cell.type = Type.NUMBER;
                            cell.data.writeByte(0);
                            cell.data.writeByte(15);
                            cell.data.writeByte(0);

cell.data.writeDouble(dateNum);
                        } else if(isNaN(Number(value)) == 
false && String(value) != "") {
                            cell.type = Type.NUMBER;
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeDouble(value);
                        } else if(String(value).length > 0) {
                            cell.type = Type.LABEL;
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            var len:uint = 
String(value).length;
                            cell.data.writeByte(len);

cell.data.writeUTFBytes(value);
                        } else {
                            cell.type = Type.BLANK;
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                        }

                        br.writeTag(cell);
                    }
                }

                // Finally, the closing EOF record
                if(v==_sheets.length-1){
                    var eof:Record = new Record(Type.EOF);
                    br.writeTag(eof);
                }
            }

            br.stream.position = 0;
            return br.stream;
        }

Result: File with double size and the data in the binary form of the second 
sheet is 
there but excel don't recognize second sheet.

Original comment by carcelen...@yahoo.es on 27 Aug 2009 at 11:59

[GoogleCodeExporter]

I found this open office pdf where explains how you must create several streams 
for a 
compound excel document. The woorkbook stream is diferent from the sheets 
stream.

I wonder if someone who controls BIFF could take a look of it.

Bye

Original comment by carcelen...@yahoo.es on 27 Aug 2009 at 12:21

Attachments:

• excelfileformat.pdf

[GoogleCodeExporter]

Hi carcelen...@yahoo.es.

I try your solution and dont work. When i open the Export file Excels say: 
"File Error: may have lost some formats".

could someone fix it?

Regards

Original comment by giancarlosereni on 19 Aug 2010 at 6:04

[GoogleCodeExporter]

Guys,

Notice that the BIFF version used  (bof.data.writeShort(BIFFVersion.BIFF2);

DOES NOT SUPPORT MULTIPLE SHEETS. You'll have to change it to a version that 
supports WORKBOOK (several sheets in one file) versions are  (BIFF5 to BIFF8). 
I'm working in a solution to export an excel file with multiple sheets.

I'll post it here once im done. 

Original comment by pdelt...@gmail.com on 16 Sep 2010 at 2:09

[GoogleCodeExporter]

Is there any update on support for multiple sheets?

Original comment by jaydeep....@gmail.com on 15 Oct 2010 at 5:27

[GoogleCodeExporter]

Is anyone knows about latest in as3xls to multiple worksheet work? Isn't 
supporting yet?

Original comment by kas...@gmail.com on 3 Aug 2011 at 3:56

[GoogleCodeExporter]

I have taken that example of carcelen's and change the BIFF2 to BIFF% and BIFF8 
and still getting the aforementioned error. "File Error: data my have been lost"

Original comment by parker...@gmail.com on 4 Aug 2011 at 4:44

[GoogleCodeExporter]

Hi,

Could you fix the multiple sheet issue

Regards

Original comment by ranibark...@gmail.com on 9 Dec 2011 at 11:32

[GoogleCodeExporter]

I took example of Carcelen and made some changes. Now I get an excel file with 
one sheet but that has data of both the input sheet. However, header of second 
sheet is missing. So now the data is present but need to get that in two 
sheets. Below is the changed code. Somebody please help. 

public class ExcelFile extends com.as3xls.xls.ExcelFile
    {
        public function ExcelFile()
        {
            super();
        }

        override public function saveToByteArray():ByteArray {
            var br:BIFFWriter = new BIFFWriter();
            var _sheets:ArrayCollection = super.sheets;

            for(var v:int=0;v<_sheets.length;v++){
                var s:Sheet = _sheets[v] as Sheet;

                // Write the BOF and header records
                var bof:Record = new Record(Type.BOF);
                bof.data.writeShort(BIFFVersion.BIFF4);
                bof.data.writeByte(0);
                bof.data.writeByte(0x10);
                br.writeTag(bof);

                // Date mode
                var dateMode:Record = new Record(Type.DATEMODE);
                dateMode.data.writeShort(1);
                br.writeTag(dateMode);

                // Store built in formats
                var formats:Array = ["General", 
                    "0", "0.00", "#,##0", "#,##0.00", 
                    "", "", "", "",
                    "0%", "0.00%", "0.00E+00",
                    "#?/?", "#??/??",
                    "M/D/YY", "D-MMM-YY", "D-MMM", "MMM-YY"];

                var numfmt:Record = new Record(Type.BUILTINFMTCOUNT);
                numfmt.data.writeShort(formats.length);
                br.writeTag(numfmt);

                for(var n:uint = 0; n < formats.length; n++) {
                    var fmt:Record = new Record(Type.FORMAT);
                    fmt.data.writeByte(formats[n].length);
                    fmt.data.writeUTFBytes(formats[n]);
                    br.writeTag(fmt);
                }

                var dimensions:Record = new Record(Type.DIMENSIONS);
                dimensions.data.writeShort(0);
                dimensions.data.writeShort(s.rows+1);
                dimensions.data.writeShort(0);
                dimensions.data.writeShort(s.cols+1);
                br.writeTag(dimensions);

                for(var r:uint = 0; r < s.rows; r++) {
                    for(var c:uint = 0; c < s.cols; c++) {
                        var value:* = s.getCell(r, c).value;
                        var cell:Record = new Record(1);
                        cell.data.writeShort(r);
                        cell.data.writeShort(c);

                        if(value is Date) {
                            var dateNum:Number = (value.time / 86400000) + 24106.667;
                            cell.type = Type.NUMBER;
                            cell.data.writeByte(0);
                            cell.data.writeByte(15);
                            cell.data.writeByte(0);
                            cell.data.writeDouble(dateNum);
                        } else if(isNaN(Number(value)) == false && String(value) != "") {
                            cell.type = Type.NUMBER;
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeDouble(value);
                        } else if(String(value).length > 0) {
                            cell.type = Type.LABEL;
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            var len:uint = String(value).length;
                            cell.data.writeByte(len);
                            cell.data.writeUTFBytes(value);
                        } else {
                            cell.type = Type.BLANK;
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                            cell.data.writeByte(0);
                        }

                        br.writeTag(cell);
                    }
                }

                // Finally, the closing EOF record
                if(v==_sheets.length-1){
                    var eof:Record = new Record(Type.EOF);
                    br.writeTag(eof);
                }
            }

            br.stream.position = 0;
            return br.stream;
        }
    }

Original comment by ranibark...@gmail.com on 9 Dec 2011 at 12:00

[GoogleCodeExporter]

Any news on this ?

Original comment by federico...@gmail.com on 14 May 2012 at 9:34

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Is there any progress for this issue ?
If not, do you have any other library suggestion ?

Original comment by ozme...@gmail.com on 5 Oct 2012 at 6:33