Options

How to autofit column in NAV using Excel Buffer?

chandrurecchandrurec Member Posts: 560
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.

Comments

  • Options
    StLiStLi Member Posts: 83
    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.
  • Options
    bbrownbbrown Member Posts: 3,268
    Define your range and call AutoFit
    XlWrkSht.Range('A' + FORMAT(1) + ':' + CustExcelBuf.xlColID + CustExcelBuf.xlRowID).Columns.AutoFit;
    There are no bugs - only undocumented features.
  • Options
    chandrurecchandrurec Member Posts: 560
    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
  • Options
    bbrownbbrown Member Posts: 3,268
    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;
    
    There are no bugs - only undocumented features.
  • Options
    chandrurecchandrurec Member Posts: 560
    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.
  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
Sign In or Register to comment.