How to autofit column in NAV using Excel Buffer?

chandrurec

Hi all,

I am trying to export to excel using excel buffer.

When I try to export, I found that columns are not getting autofit.

For example, I am having a field named % Contribution which is not getting autofit in excel.

If anyone knows how to make this column autofit in excel , kindly let me know.

Thanks in advance.

Regards,
chandru.

StLi

Afaik the default behaviour of ExcelBuffer should actually make the columns autofit. So, i'd look into changes regarding this.

But just in case you don't want to:
Use the functions "CreateRange" and "AutoFit" in the Excel Buffer table. To force autofitting.

I haven't used those. But assuming from the code, you need to use those commands after creating the sheet, but before giving usercontrol.

bbrown

Define your range and call AutoFit

XlWrkSht.Range('A' + FORMAT(1) + ':' + CustExcelBuf.xlColID + CustExcelBuf.xlRowID).Columns.AutoFit;

chandrurec

Hi bbrown,

I am using Excebuffer to do export to excel. So where I need to put this code after creating sheet or before creating sheet.

Thanks & Regards,
chandru

bbrown

chandrurec wrote:

Hi bbrown,

I am using Excebuffer to do export to excel. So where I need to put this code after creating sheet or before creating sheet.

Thanks & Regards,
chandru

It goes in your CREATESHEET function. After you have written out all the worksheet cells from your Excel Buffer records. Something like this...

    IF CustExcelBuf.FIND('-') THEN BEGIN
      XlWrkSht := XlWrkBk.Worksheets.Add();
      // code for layout goes here.
      REPEAT
          // Code to write out each cell goes here
      UNTIL CustExcelBuf.NEXT = 0;
      XlWrkSht.Range('A' + FORMAT(1) + ':' + CustExcelBuf.xlColID + CustExcelBuf.xlRowID).Columns.AutoFit;
    END;

chandrurec

Hi bbrown ,

Thank you got it.

If we need to forcecully make left align or right align for the cells , can you tell me how to do it.

Thanks & Regards,
chandru.

bbrown

The "HorizontalAlignment" function will handle that. I don't have an example of its use.

A good trick to sorting out these commands is to use the Excel Macro Recorder. Turn on the recorder,then make the formattign or other changes. Look at the code written by the recorder and use that as a guide to design your NAV code. You'll have to translate the constants to their numberic values.