Excel wrap along with background colors not working

soundar

I am trying to customize excel file with header color, wrap content and table border. But only last command is working other two are not working.

             $('row:first c', sheet).attr( 's', '55' );
             $('row:first c', sheet).attr( 's', '40' );
             $('row c', sheet).attr('s', '25');

Here is the link https://jsfiddle.net/ssoundar619/thr24rp6/

soundar

And also need help to merge cells in excel file.

HPB

Line 1: Change attribute s to 55 for all c elements in first row element of sheet
Line 2: Change attribute s to 40 for all c elements in first row element of sheet
Line 3: Change attribute s to 25 for all c elements in all row elements of sheet

s refers to the CellXfs styles in styles.xml

In your fiddle that is.
55: <xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1"><alignment wrapText="1"/></xf>
40: <xf numFmtId="0" fontId="0" fillId="4" borderId="1" applyFont="1" applyFill="1" applyBorder="1"/>
25: <xf numFmtId="0" fontId="0" fillId="0" borderId="1" applyFont="1" applyFill="1" applyBorder="1"/>

Line 3 will set all your cells, even headers, to style 25. And style 25 is nothing more than a border.

For testing purposes you can extract the excel file as a zip and look at the exported xml-files to see what it actually looks like.

It's not possible to add multiple styles, there is only 1 style attribute. If none of the default styles suit you, you should look into editing styles.xml and add your own styles and refer to the id of those.
Here is a testcase from an earlier discussion where I added extra styles.
live.datatables.net/qokabeve/17/edit

soundar

Ok Sir. I reorderd my code like this.

             $('row c', sheet).attr('s', '25');
             $('row:first c', sheet).attr( 's', '40' );
             $('row:first c', sheet).attr( 's', '55' );

Now I am getting the borders and my headers are wrapping but colors not working.

HPB

Style 40 holds the color, style 55 holds the wrapping.
You're overwrting the style that holds the color. If you need a custom style with wrapping AND color you need to create one yourself in styles.xml
Reread my earlier comment for more information on that.

soundar

Very thank you HPB. I did it with your help. And I need one more help. I need to merge cells and center the content. Please guide to do this.

HPB

add a mergedCells element to your sheet1.xml
Example code for merged cells A1 and B1:

    <sheetData>
        <row r="1">
            <c r="A1" s="1"/>
            <c r="B1" s="1"/>
        </row>
    </sheetData>
    <mergeCells count="1">
        <mergeCell ref="A1:B1"/>
    </mergeCells>

If you need more help you would be better off showcasing what you have done so far with a new testcase.

soundar

Hi HPB,
I tried to add mergecell element and it is not working. I not sure which part I am missing or wrong.

Please refer this link for the test case https://jsfiddle.net/ssoundar619/6jnLerzm/

I want to merge first rows cells sir.

HPB

You're adding mergedCells to the styles document instead of the worksheet.
Also you copied a lot of irrelevant code from my other thread into your new testcase.
Please make an effort, I won't look into it if it comes down to 'i need this, give me code' comments.

soundar

Definitely I will put my maximum effort. Thanks for your help HPB.