Excel Column Header

[keremgeliz]

Hello all.

I am creating 7 columns data for every itteration from for loop.

My materialnumbers isnt constant. So`its can be 10 or 3. I can write all datas to spreadsheet without headers.

I need save my datas like attachment to excel. 

In this excel file headers creating automaticly.

Every for itteration i creating 1 material and 7 values (array values).

Thanks.

Kerem.

[RTSLVU]

Without one of the add-on toolkits you can not merge cells like the first row of your headers or color cells.

I believe the XLR8 add on will do that, maybe the NI MS Office Reports Toolkit  will too but I don't know.

Your second row is easy just write the headers to your file as strings once before you write any data. 

[keremgeliz]

Thanks for your reply.

I can do second row with MS-Office Report Tools. 

But first row is problem still.

[Ken_Naylor · Accepted solution]

This can be done but needs to use Invoke and property nodes to access the Excel Object Model.

Once you have a reference to the Workseet use the Range Method to define the cells to merge then use the Merge method on the Range object to merge the cells

I have also used the range property to set the value and alignment (I dont know why but -4108 centres the text) and create an interior object in order to set the colour

Result

[Bob_Schor]

Unfortunately, ActiveX is no longer so well documented -- I had to struggle to get the ActiveX Help document to do anything in Windows 7 (worked much better in XP).  The Horizontal Alignment Property, according to Microsoft, can be any of the following xlHAlign constants:

   xlHAlignCenter

   xlHAlignCenterAcrossSelection

   xlHAlignDistributed

   xlHAlignFill

   xlHAlignGeneral

   xlHAlignJustify

   xlHAlignLeft

   xlHAlignRight

I couldn't get values for these.  However, I did find, on the Web, a reference for Office 2007 with values (and no explanation):

   xlHAlignCenter                           -4108

   xlHAlignCenterAcrossSelection          7

   xlHAlignDistributed                     -4117

   xlHAlignFill                                       5

   xlHAlignGeneral                                1

   xlHAlignJustify                           -4130

   xlHAlignLeft                               -4131

   xlHAlignRight                             -4152

Note that the numbers are either three-bit positive numbers or 6 bit numbers with the higher 10 bits all set (I'm assuming this is intended to be a U16 quantity).  On the other hand, it really only needs the lower six bits -- I wonder what would happen if you tried 52 instead of -4108 ...  Ahh, the wonders of Microsoft Abandonware ...

Bob Schor

[ben64]

---

@Bob_Schor wrote:

Unfortunately, ActiveX is no longer so well documented -- I had to struggle to get the ActiveX Help document to do anything in Windows 7 (worked much better in XP).  The Horizontal Alignment Property, according to Microsoft, can be any of the following xlHAlign constants:

   xlHAlignCenter

   xlHAlignCenterAcrossSelection

   xlHAlignDistributed

   xlHAlignFill

   xlHAlignGeneral

   xlHAlignJustify

   xlHAlignLeft

   xlHAlignRight

I couldn't get values for these.  However, I did find, on the Web, a reference for Office 2007 with values (and no explanation):

   xlHAlignCenter                           -4108

   xlHAlignCenterAcrossSelection          7

   xlHAlignDistributed                     -4117

   xlHAlignFill                                       5

   xlHAlignGeneral                                1

   xlHAlignJustify                           -4130

   xlHAlignLeft                               -4131

   xlHAlignRight                             -4152

Note that the numbers are either three-bit positive numbers or 6 bit numbers with the higher 10 bits all set (I'm assuming this is intended to be a U16 quantity).  On the other hand, it really only needs the lower six bits -- I wonder what would happen if you tried 52 instead of -4108 ...  Ahh, the wonders of Microsoft Abandonware ...

Bob Schor

---

You can find the values here.

Ben64

[keremgeliz]

Thanks for your help.

Its seems complicated.