Demo of Excel buffer usage

Administrator
Administrator Member, Moderator, Administrator Posts: 2,506
edited 2011-06-06 in Download section
Demo of Excel buffer usage
This small report shows the easy usage of the excel buffer table to create excel exports.

This report can be modified by end users who have the report and dataport designer granule.

http://www.mibuso.com/dlinfo.asp?FileID=596

Discuss this download here.

Comments

  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Mark, this is just amazing. This is exactly that kind of idea that has always been right befoure our fingertips, but they idea to actually use, for some mysterious way, never really occured.

    Honestly, it's the best reporting solution out there, because it's free (as compared to Jet Reports), it's 200% customizable, and it's faster and a lot more trustable than f***ing around with SQL queries into Excel, which I usually did.

    If we use OpenWorkbook instead of CreateWorkbook (code can nicely copied from Accounting Schedule / Export to Excel), the Excel file preserves all Excel functions, formatting and whatever. It means we will never ever will write all those stupid reports that always plagued my life, but tell clients to design themselves whatever report they want it Excel with all the SUMs and conditional formatting and whatever, and we just populate it with data.

    Actually, I currently have a client that needs a real cash-flow, adding together everything from bank accounts to open orders, with different percentage weights. I wanted to do it with SQL queries in Excel, but know will just use this. Hell, it's even faster than writing reports the normal way, because I don't have to mess around with field sizes anymore.

    Thanks a lot. I just don't understand why didn't it occur to me :D
  • Savatage
    Savatage Member Posts: 7,142
    after that review - how can I NOT download it and check it out! =P~
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • nadnerb
    nadnerb Member Posts: 1
    Hi,

    Down loaded the example not bad at all do you know if there are plans to include this on a wizard in a later release as i belive that it could be simplified but as i am a new user i believe this is a relatievely new option?

    thanks,

    BMCK
  • Savatage
    Savatage Member Posts: 7,142
    nadnerb wrote:
    Hi,

    Down loaded the example not bad at all do you know if there are plans to include this on a wizard in a later release as i belive that it could be simplified but as i am a new user i believe this is a relatievely new option?

    thanks,

    BMCK

    the excel buffer is not that new, if that's what you're talking about, but documentation on how to use it wisely hasn't exactly been great
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • DenSter
    DenSter Member Posts: 8,307
    Imagine that you could have an Excel template with functions already in there. All you'd need to do is fill up a data sheet using the buffer table and it should work real nicely. I don't know if it's in the sample, but you can also code functions in the buffer table in C/AL code.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Exactly that's the way I am using it.

    To choose Excel file, FileName := FileOpenDialog.OpenFile('Excel file','',2,'*.xls',0); in the OnLookUp of a text field where FileOpenDialiog is an instance of CU Common Dialog Management. To open Sheet,
    SheetName := TempExcelBuffer.SelectSheetsName(FileName); in another textbox - OnLookUp.

    Then it's the same, and the OnPostDataItem is:

    TempExcelBuffer.OpenBook(FileName,SheetName);
    TempExcelBuffer.CreateSheet(SheetName,'Inventory List',COMPANYNAME,USERID);
    TempExcelBuffer.GiveUserControl;
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    What would be really nice is something like a free Jet by specifying table, filters etc. in an Excel template, but I think it would be too much for a hobby project...
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Please also remember NAV has a codeunit 412 Common Dialog Management.

    This saves you the trouble of defining the COMDLG ocx yourself with having the risk of recompiling on a non-visuals studio pc without the apropriate license.
    Marije Brummel | Freelance Business Central Specialist
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Khm... "is an instance of CU Common Dialog Management" :)
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Oops, my bad :oops:

    Must be my jetlag :mrgreen:

    At least we follow the same path :wink:
    Marije Brummel | Freelance Business Central Specialist
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Yes, we follow the same path: you from Boston to Appeldorn and I from my chair to the kitchen to get a coffe: both goes eastward :):):)
  • spade
    spade Member Posts: 6
    Hi, I am a beginning developer in Navision. I tried this download and it works fine, but I would like to add more fields to it. If I use a TextField then all works great, but if I use another type like Amount (which is decimal) I get the expected error. How do I correct this in the code? Also, is it possible to change this report to get the following: An overview of all sales (invoices minus credit memo) and is it possible to group this result by Campaign for example? Thanks for your idea's in advance

    Spade
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You can add decimal fields or integer fields by using the FORMAT(Variable) statement.

    If you want to export other information, just make a report like you do for normal reports and instead of creating secions you export the information to excel. If you use the excel code in groupfooters you can even use the grouping functionality for your excelexport.
    Marije Brummel | Freelance Business Central Specialist
  • spade
    spade Member Posts: 6
    Thanks for your quick reply

    It may sound stupid but where exactly do I place the FORMAT?

    This is a snippet of the concerning code:


    Row := Row + 1;
    EnterCell(Row, 1, "No.", FALSE, FALSE, FALSE);
    EnterCell(Row, 2, Name, FALSE, FALSE, FALSE);
    EnterCell(Row, 3, Address, FALSE, FALSE, FALSE);
    EnterCell(Row, 4, Amount, FALSE, FALSE, FALSE);

    The last line has to be formatted to decimal
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262 
    EnterCell(Row, 4, FORMAT(Amount), FALSE, FALSE, FALSE);
    Marije Brummel | Freelance Business Central Specialist
  • spade
    spade Member Posts: 6
    Thanks but I still get 0 for all customers in the decimal field, while these are filled with large amounts when I run the table... that's why I thought the length parameter had to be added...

    This is my code:


    Row := Row + 1;
    EnterCell(Row, 1, "No.", FALSE, FALSE, FALSE);
    EnterCell(Row, 2, Name, FALSE, FALSE, FALSE);
    EnterCell(Row, 3, Address, FALSE, FALSE, FALSE);
    EnterCell(Row, 4, FORMAT(Payments), FALSE, FALSE, FALSE);
    EnterCell(Row, 5, FORMAT(Amount), FALSE, FALSE, FALSE);
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I think you are exporting flowfields. These should be calculated,

    Try adding this code before the export
    CALCFIELDS(Amount, Payments);
    Marije Brummel | Freelance Business Central Specialist
  • spade
    spade Member Posts: 6
    I think I found it, it is because these are FlowFields I think, is that correct? Then how do I solve this?
  • spade
    spade Member Posts: 6
    ah sorry, I just missed your response thank you
  • spade
    spade Member Posts: 6
    It works and it makes sense, Navision rules! I can experiment with complexer reports now. Would it be possible to automatically create a second Sheet in excel where a graphic is shown?
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    spade wrote:
    Navision rules!

    =D>
    spade wrote:
    Would it be possible to automatically create a second Sheet in excel where a graphic is shown?

    Not with this example, but you can do it with NAV. I think there are examples in the download section for this.
    Marije Brummel | Freelance Business Central Specialist
  • alexjensen
    alexjensen Member Posts: 41
    Hi Mark

    I have just tested You tool in NAV 4.0 SP2 and NAV 5.0 Beta using Office 2007. I get this error:

    "Could not invoke the member Add. The OLE control or Automation server returned an unknown error code."

    Any ideas?

    Alex
  • Reinhard
    Reinhard Member Posts: 249
    I think you are calling Add(fileStr) but it is not a valid file location?
    Oztera
  • uyenly
    uyenly Member Posts: 1
    Thanks so much for your code; it's really helpful and excellent. But could you please help me how to merge column or row. I really need your help. Thanks so much.
  • Hanen
    Hanen Member Posts: 281
    How can I use the function SelectSheetName to select the right sheet to fill it with the appropriate DATA. Thanks for help. :oops:
    Regards

    Hanen TALBI

Categories