Not writing multiple sheets.

[morgoth2]

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);

1. 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");

1. 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.

Comment 1 by [email protected], Aug 27, 2009
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.

Comment 2 by [email protected], Aug 27, 2009
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.

[sweetcelia]

how to read ExcelFile?

[morgoth2]

Sorry, but I don't understand what are you asking for...