Export to existing Excel template

Tiwaz · 2016-10-07

Hi everyone. How can I export data from my page, subpage etc. to Excel template that I have gotten for that purpose. I knpw that I should use Excel buffer table but do not know how. Practical steps could be useful since I looked online and found nothing useful.
Thanks!

ishwarsharma016 · 2016-10-07

Hi Tiwaz, just in case you didn't know, there send to excel action available in pages in RTC. But if you want to use Excel buffer, please check the links below,
https://community.dynamics.com/nav/f/34/t/181214
http://saurav-nav.blogspot.in/2014/01/nav-2013-r2-export-to-excel-report.html

These links help with Reports, but if you check the logic behind and try to understand the code in detail, you will be able to export to excel the data in your page using excel buffer.

Hope it helps.

archer89 · 2016-10-09

exporting data with table excel buffer does not support usage of excel templates.
for that case i wrote a blog post https://moxie4nav.wordpress.com/2016/08/26/simple-readwrite-excel-data/

Tiwaz · 2016-10-11

Hi guys. THanks fro answering. Unfortunately that is not what I'm looking for.
This is what I have done.
I created ProcessingOnly report where I created this function:

LOCAL EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean;Format : Text[30];CellType : Option)
ExcelBuffer.INIT;
ExcelBuffer.VALIDATE("Row No.",RowNo);
ExcelBuffer.VALIDATE("Column No.",ColumnNo);
ExcelBuffer."Cell Value as Text" := CellValue;
ExcelBuffer.Formula := '';
ExcelBuffer.Bold := Bold;
ExcelBuffer.Italic := Italic;
ExcelBuffer.Underline := UnderLine;
ExcelBuffer.NumberFormat := Format;
ExcelBuffer."Cell Type" := CellType;
ExcelBuffer.INSERT;

Then in OnAfterGetRecord I placed this

Travel Order - OnAfterGetRecord()
FileName := 'D:\Putni nalog.xlsx';
SheetName := 'Sheet1';
ExcelBuffer.OpenBook(FileName,SheetName);
EnterCell(2,2,'TravelOrder',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
//ExcelBuffer.CreateBookAndOpenExcel('Sheet1','Report',COMPANYNAME,USERID);
//ExcelBuffer.OverwriteAndOpenExistingExcel(FileName);

I want to write on my file Putni nalog.xlsx using
EnterCell(2,2...);
EnterCell(2,5...); etc, depending what I need and in which field in my file do I want to write it.
But when I try using OverwriteAndOpenExistingExcel it gives me error 'The file does not exist'.
What should I use to write on my existing file?
And how can I write some value from my table "Travel Order" since
EnterCell(2,2,TravelOrder."No",FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text); for example, simply does not work?

THANK YOU!

rmv_RU · 2016-11-14

Hello. Long time ago I used to paint pictures with code similar to:

Line (1,1, 5, 5); 
Circle (1, 5)

And i don't like it.
That's why i use Excel templates and custom Nav functionality to work with it.
The idea is simple:
1. Nav must not work with format properties - it's responsibility of Excel Template and key user.
2. Use names ranges to define layout (HEADER, LINE_HEADER, LINE, LINE_FOOTER, FOOTER)
3. Use marks to define fields and agree on it with key users - key users should provide it for you.

The sample code below

TemplateMgt.OpenTemplate(Filename);
TemplateMgt.SelectTemplateSheet('INVOICE');
window.OPEN(Text001);

Header - OnAfterGetRecord()
window.UPDATE(1, "Document No.");
//rmv_151105_MT-8484+{
WO.GET(Header."Inventory Wo No.");
WO.TESTFIELD("Customer Code");
Cust.GET(WO."Customer Code");
//rmv_151105_MT-8484+}
TemplateMgt.CreateNewSheet("Document No.");
TemplateMgt.CopyPageSetupFromTemplate();
TemplateMgt.CopyNamedRange('HEADER');
TemplateMgt.xlReplaceRange1('#=DocumentNo', "Document No.");
TemplateMgt.xlReplaceRange1('#=DT', FORMAT(CURRENTDATETIME));
TemplateMgt.xlReplaceRange1('#=USERID', USERID);
TemplateMgt.xlReplaceRange1('#=CUSTOMER_NAME', Cust.Name);


Line - OnPreDataItem()
TemplateMgt.CopyNamedRange('LINE_HEADER');

Line - OnAfterGetRecord()
TemplateMgt.CopyNamedRange('LINE');
TemplateMgt.xlReplaceRange1('#=LineNumber', FORMAT("Line Number"));
.....
Header - OnPostDataItem()
TemplateMgt.FinishBook;
window.CLOSE;

if you're interested don't hesitate to contact me

jonathanmiggs · 2016-11-14

Learned a lot from here. Thanks. nurse call systems

Pachi · 2018-01-30

Only one question...

please, how is declared variable "TemplateMgt"?

Export to existing Excel template

Answers

Categories