Thursday, November 17, 2011

Setting Excel Column Width with AS3XLS

In my previous posts we talked about exporting to excel and formatting using AS3XLS. Here we go furthur and add a feature into AS3XLS. We'll extend it to support custom column width. Let's see how we can achieve that. Onward ho!!!

Since AS3XLS does not give the capability of adding custom column width, we'll need to extend the saveToByteArray method of the AS3XLS. What I did was to create a new function in my code and passed the Sheet and an array of the custom column widths that I collected from the user. I have placed the code below. Hope it helps.


private function saveToByteArray(s:Sheet, columnWidths:Array):ByteArray 
{
        ....... same code as in the AS3XLS saveToByteArray()
        // Custom code starts
for (var i:uint = 0; i < columnWidths.length; i++) 
{
var colwidth:Record = new Record(Type.COLINFO);
colwidth.data.writeShort(i); // start index 
colwidth.data.writeShort(i); // end index
colwidth.data.writeShort(columnWidths[i] * 256);
br.writeTag(colwidth);
}

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

// Check for value types and add to the cell bytes as in AS3XLS and finally write it in the    BiffRecord
}
}
// Custom code ends
// Finally, the closing EOF record
var eof:Record = new Record(Type.EOF);
br.writeTag(eof);

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

10 comments :

Sharma'z said...

Hi, The example looks nice.

But I didn't get which class needs to extend, since you didn't override existing class methods.

Thanks

Pramod Rao said...

Actually you don't extend any class. You'll need to add the function "saveToByteArray" in your own class. There are some additions to the function that needs to be done to allow the custom widths. I have commented that piece of code. The other parts of the function are same as in the "saveToByteArray" function in the AS3XLS code.
-Thanks,
Pramod Rao.

Soni said...

Thanks a lot!!!
I have tried out this and really works..
Thank you once again :)

soni said...

Can you suggest something to make font bold?

Pramod Rao said...

Hey soni!! Glad that it was useful for you. I'll see what I can do

Soni said...

Hello Pramod,
Got any clue regarding this??
I did some search.. and found like there is kind of XF record which is used to style the cells.
And your data cells point to that XF record.
Now i am struggling with how to write that XF record and how to point the data cells to that XF record.. (Not sure if my understanding is correct..)

Pramod Rao said...

Hi Soni,

Try out the code below,

var font:Record = new Record(Type.FONT);
font.data.writeShort(300);
font.data.writeShort(1);
font.data.writeUTFBytes("bold");
br.writeTag(font);

Add this after setting the Dimensions in the record. It might work. I have not tested it, so I am not really sure.

You can also play around with BIFFRecords. More info at http://www.openoffice.org/sc/excelfileformat.pdf

soni said...

Hi Pramod,
Thank you so much for your guidance...
By referring to the PDF you mentioned.. I could make the fonts bold.
For reference pasting the code below.
var boldFont:Record = new Record(Type.FONT);
boldFont.data.writeShort(240);
boldFont.data.writeByte(0x01);
boldFont.data.writeByte(0);
boldFont.data.writeByte(String("Calibri").length);
boldFont.data.writeUTFBytes("Calibri");
br.writeTag(boldFont);

And you have to write this record before formatcount record.
Thanks a lot once again....

Natasha Osipenko said...

Hi, thanks for your examples.
How can I add specific format like "dd.mm.yy" to cell with date value?

Jeetendra Ahuja said...

Not working for Me.
I just did like you said...
Please suggest