Excel-buffer and Autofit of column-width

jensthomsen

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)

Kishorm

Try the following changes to the EV_SaveExcel function...

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);
  FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,GetFriendlyFilename,FilePath);

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

  PostOpenExcel;

  XlHelper.CallSaveAs(XlWrkBk,FileNameClient)

  CloseBook;
END;

Kishorm

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

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

Actually just try calling the PostOpenExcel() function before calling CloseBook(), all the necessary dotnet variables should be instantiated at that point

jensthomsen

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

Hov about loading the Excel file in some Excel-DotNet InterOp...? Has anyone any experience with that?

Kishorm

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

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;

Kishorm

Try the following changes to the EV_SaveExcel function...

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);
  FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,GetFriendlyFilename,FilePath);

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

  PostOpenExcel;

  XlHelper.CallSaveAs(XlWrkBk,FileNameClient)

  CloseBook;
END;

jensthomsen

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

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