Excel-buffer and Autofit of column-width

jensthomsen
jensthomsen Member Posts: 173
in NAV Three Tier
Hi
With help from Saurav Dhyani's blog (http://saurav-nav.blogspot.dk/2015/07/nav-2013-r2-multiple-sheets-in-excel.html) i've created an excel-fil containing several sheets by use of the Excel-buffer. I will save the created files directly to a folder without opening it, and that all works fine. But my problem now is that I want to implement 'Autofit' of the columns. When just opening the excel file (by calling
ExcelBuf.OpenExcel();
ExcelBuf.GiveUserControl();
) it works fine, but if I just save the files the changes in autofit doesn't seem to be saved?? If the solution would be some kind op post-processing of the excel files, that would be ok to:-)

I'm using Navision 2015 (build 42951)

Best Answer

Answers

  • Kishorm
    Kishorm Member Posts: 921
    Have a look in table 370 Excel Buffer, you'll see this code in PostOpenExcel()...
    // autofit all columns on all worksheets
XlHelper.AutoFitColumnsInAllWorksheets(XlWrkBk);
    ...create your own function with the above code and then call it before saving the workbook.
  • jensthomsen
    jensthomsen Member Posts: 173
    I've allready puzzled around with this function, but couldn't get it working...How should the dotnet variables 'XlWrkBk' and 'XlHelper' (and perhaps 'XlApp' ) be instantiated???
  • Kishorm
    Kishorm Member Posts: 921
    Actually just try calling the PostOpenExcel() function before calling CloseBook(), all the necessary dotnet variables should be instantiated at that point
  • jensthomsen
    jensthomsen Member Posts: 173
    Doesn't seem to be working...XLWrkBk isn't instantiated correct. In the standard function 'OpenExcel()' this line is called before 'PostOpenExcel()':
    XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClient);
    But I don't want to open the Excel-book but just saving it.
  • jensthomsen
    jensthomsen Member Posts: 173
    Hov about loading the Excel file in some Excel-DotNet InterOp...? Has anyone any experience with that?
  • Kishorm
    Kishorm Member Posts: 921
    Can you post the code you use to create the Excel spreadsheet, don't need the stuff that populates the data on the spreadsheet just all the framework stuff e.g. creating the workbook/worksheet, saving/closing it etc...
  • jensthomsen
    jensthomsen Member Posts: 173
    I'm pretty much just using the standard functionality on table 370. I have added this function:

    PROCEDURE CreateBookOnly@1160180001(SheetName@1160180003 : Text[250];ReportHeader@1160180002 : Text[80];CompanyName@1160180001 : Text[30];UserID2@1160180000 : Text;BookCreated@1160180004 : Boolean);
    BEGIN

    ExcelBookCreated := BookCreated;
    NewSheetName := SheetName;

    IF NOT BookCreated THEN
    CreateBook(SheetName);

    CurrentRow := 0;
    CurrentCol := 0;

    WriteSheet(ReportHeader, CompanyName, UserID2);
    END;

    Where 'ExcelBookCreated' and 'NewSheetName' are global variables. In function 'Writesheet' I've added this code:


    CRLF := 10;
    RecNo := 1;
    TotalRecNo := COUNT + InfoExcelBuf.COUNT;
    RecNo := 0;

    //Code added >>
    IF ExcelBookCreated THEN
    IF ActiveSheetName <> NewSheetName THEN
    XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(NewSheetName);
    //Code added <<

    XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape);



    For witting the excel-book I fill the excel-buffer for every sheet, call my new function 'CreateBookOnly' and delete the buffer (important!). When finished writing the excel-book I call a new custom-function on table 370:

    PROCEDURE EV_SaveExcel@1160180002(FilePath@1160180001 : Text);
    VAR
    FileNameClient@1160180000 : Text;
    BEGIN
    CloseBook;

    IF OpenUsingDocumentService('') THEN
    EXIT;

    IF NOT PreOpenExcel THEN
    EXIT;

    FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
    FileManagement.MoveAndRenameClientFile(FileNameClient,GetFriendlyFilename,FilePath);
    END;
  • jensthomsen
    jensthomsen Member Posts: 173
    Hi Kishorm

    It seems to be working. To prevent being prompted for overwriting the file, I'm working on TEMP-files and uses only XlHelper.CallSaveAs to finalize the file. My code ended up like this:


    CloseBook;

    IF OpenUsingDocumentService('') THEN
    EXIT;

    IF NOT PreOpenExcel THEN
    EXIT;

    FileNameClient := FileManagement.DownloadTempFile(FileNameServer);

    XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClient);

    PostOpenExcel;

    FileNameClient_OpenedAndAutofitted := FileManagement.ClientTempFileName('xlsx');
    FileManagement.CopyClientFile(FileNameClient,FileNameClient_OpenedAndAutofitted,TRUE);
    FileNameClient_OpenedAndAutofitted := FileManagement.MoveAndRenameClientFile(FileNameClient_OpenedAndAutofitted,GetFriendlyFilename,FilePath);
    FileManagement.DeleteClientFile(FileNameClient_OpenedAndAutofitted);

    XlHelper.CallSaveAs(XlWrkBk,FileNameClient_OpenedAndAutofitted);
    QuitExcel;

    I,ve experienced problems with 'Excel' "holding" on to the file, but I think/hope I've solved it by calling the function 'QuitExcel' at the end:-)

    MANY THX for helping me out!
  • Kishorm
    Kishorm Member Posts: 921
    Great @jensthomsen, In that case please select "Yes" on the "Did this answer your question? yes / no" option on my previous post so that others can quickly find the answer :)

Categories