Demo of Excel buffer usage

Administrator

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.

Miklos_Hollender

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

Savatage

after that review - how can I NOT download it and check it out! =P~

nadnerb

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

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

DenSter

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.

Miklos_Hollender

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

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

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.

Miklos_Hollender

Khm... "is an instance of CU Common Dialog Management"

Marije_Brummel

Oops, my bad :oops:

Must be my jetlag

At least we follow the same path

Miklos_Hollender

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

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

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.

spade

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

EnterCell(Row, 4, FORMAT(Amount), FALSE, FALSE, FALSE);

spade

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

I think you are exporting flowfields. These should be calculated,

Try adding this code before the export

CALCFIELDS(Amount, Payments);

spade

I think I found it, it is because these are FlowFields I think, is that correct? Then how do I solve this?

spade

ah sorry, I just missed your response thank you

spade

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

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.

alexjensen

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

I think you are calling Add(fileStr) but it is not a valid file location?

uyenly

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

How can I use the function SelectSheetName to select the right sheet to fill it with the appropriate DATA. Thanks for help. :oops: