djw
Not writing multiple sheets.
What steps will reproduce the problem?
- 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);
- 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");
- 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.
