Results 1 to 9 of 9

Thread: LibXL - Auto Fit Column Width?

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Mar 2009
    Posts
    256

    Default LibXL - Auto Fit Column Width?

    Using LibXL to create an Excel file, is it possible to AutoFit the column Widths?

  2. #2
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    10,869

    Default Re: LibXL - Auto Fit Column Width?

    Alspaugh,

    Reading the documentation I would think it is xlFormatShrinkToSize that should be used.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  3. #3
    Join Date
    Mar 2009
    Posts
    256

    Default Re: LibXL - Auto Fit Column Width?

    Not what I was looking for It does what it said. Shrinks to fit but it shrinks the text not the cell

    Click image for larger version. 

Name:	2019-05-11_8-39-36.jpg 
Views:	65 
Size:	32.1 KB 
ID:	12784

  4. #4
    Join Date
    Mar 2009
    Posts
    256

    Default Re: LibXL - Auto Fit Column Width?

    I found the procedure AutofitColumnWidth but it doesn't seem to work.

  5. #5
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    10,869

    Default Re: LibXL - Auto Fit Column Width?

    Alspaugh,

    According to a post at stackoverflow the Excel file format does not have an AutoFit option so it can't be done automatically

    The AutoFitColumnWidth method calls the LibXL xlSheetSetCol function and as documented passes -1 which means "Value -1 is used for autofit column widths". Did you call this before or after filling the sheet with data? Not sure what is expected by LibXL.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  6. #6
    Join Date
    Mar 2009
    Posts
    256

    Default Re: LibXL - Auto Fit Column Width?

    Using the terms from the libXL demo program that comes with LibXl in order for me to use xlSheetSetCol I have to use the Low Level function and procedures. I had built my program around the higher level function. I just tried using the low level function like in example DemoGenerateReceipt_LL_BP and it works. So I am re-writing my program to use the low level functions.

    Thanks for your help

  7. #7
    Join Date
    Mar 2009
    Posts
    256

    Default Re: LibXL - Auto Fit Column Width?

    Correction it didn't work. I forgot to remove some kludgy code before I updated using xlSheetSetCol. After removing the kludge xlSheetSetCol didn't work. If you can show me how to make it work in any of the Demo files that come with LibXL that would be great.

  8. #8
    Join Date
    Feb 2009
    Posts
    5,470

    Default Re: LibXL - Auto Fit Column Width?

    I came across this problem some time ago and was never able to resolve it successfully

    Fundamentally Excel does not expose a way to do this accurately and various internet formulas are close but no cigar

    Initially it seems like such a simple thing but unless Excel gives you a way to call it's engine to get an answer you can't do it because there are so many different factors including angled text/mixed fonts etc in a cell that would dictate how autofit wide it should be

    When importing via an ODF format file Excel does not honour the BestFit attribute
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  9. #9
    Join Date
    Mar 2009
    Posts
    256

    Default Re: LibXL - Auto Fit Column Width?

    Thanks for the reply. It is a big help knowing it is not possible. I can stop trying What I ended up doing is using the piColumnWidth and checking the number of characters in the data for the column. If it is bigger then the previous length then I update the column size. Kludgy but works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •