Excel-buffer and Autofit of column-width

Answers

• Kishorm Member Posts: 921

  2016-10-25

  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.

  0
• jensthomsen Member Posts: 173

  2016-10-25

  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???

  0
• Kishorm Member Posts: 921

  2016-10-25

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

  0
• jensthomsen Member Posts: 173

  2016-10-26

  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.

  0
• jensthomsen Member Posts: 173

  2016-10-28

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

  0
• Kishorm Member Posts: 921

  2016-10-28

  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...

  0
• jensthomsen Member Posts: 173

  2016-10-28

  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;

  0
• Kishorm Member Posts: 921

  2016-10-28 Answer ✓

  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;
  

  5
• jensthomsen Member Posts: 173

  2016-10-31

  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!
  

  0
• Kishorm Member Posts: 921

  2016-10-31

  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

  0