Export To excel in NAV using Excel Buffer

Simple way to write export to excel in NAV

Name                 DataType             Subtype                Length
ExportToExcel Boolean
ExcelBuffer         Record              Excel Buffer
i                         Integer

Copy Entercell function from Standard Reports

1. Write a Code on Report -OnPostReport trigger
 
IF ExportToExcel THEN BEGIN
  ExcelBuffer.CreateBook;
  ExcelBuffer.CreateSheet('NAME','',' ',USERID);
  ExcelBuffer.AutoFit('A:K');
  ExcelBuffer.GiveUserControl;
END;

2.Write following code in First Data item -onPreDataItem
IF ExportToExcel THEN BEGIN
  i:=1;
  EnterCell(i,1,'Name',TRUE,FALSE,'');
  EnterCell(i,12,'Date',TRUE,FALSE,'');
End;
Continue like how many header fields are there.

3. Write the code in onAfterget trigger where You will get data to display.
 IF ExportToExcel THEN BEGIN
  i+=1;
  EnterCell(i,1,Item."No.",FALSE,FALSE,'');
  EnterCell(i,2,Item."No. 2",FALSE,FALSE,'@');
End;

Ecom process

Name DataType Subtype Length
OutgoingComLogEntry Record E-Commerce Communication Log
TempBlob Record TempBlob
InStream InStream
OutStream OutStream
XMLVersion Text 250
ProcessWSMethodL Codeunit Process E-Commerce WS Method
ExcutionStatusL Option
bTextHeader BigText
bTextBody BigText
XMLFileProcessed Record XML File Processed
NoofFilesCreatedL Integer
XMLRequestL DotNet System.Xml.XmlDocument.'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
XMLResponseL DotNet System.Xml.XmlDocument.'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
XMLHTTPRequestL DotNet System.Net.HttpWebRequest.'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
XMLHTTPResponseL DotNet System.Net.HttpWebResponse.'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
CredentialL DotNet System.Net.NetworkCredential.'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
MemoryStreamL DotNet System.IO.MemoryStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'




TempBlob.Blob.CREATEINSTREAM(InStream);
TempBlob.Blob.CREATEOUTSTREAM(OutStream);

IF EComWSConnection."Request Header XMLPort ID" <> 0 THEN BEGIN
  ProcessWSMethodL.RequestHeader(EComWSConnection."Request Header XMLPort ID",OutStream,ExcutionStatusL);
  IF ExcutionStatusL = ExcutionStatusL::"RecordFound Error" THEN BEGIN
    CLEARALL;
    EXIT;
  END;

  InStream.READTEXT(XMLVersion);
  IF STRPOS(XMLVersion,'<?xml') > 0 THEN BEGIN
    bTextHeader.READ(InStream);
    CLEAR(TempBlob.Blob);
    CLEAR(ExcutionStatusL);
    TempBlob.Blob.CREATEOUTSTREAM(OutStream);
    TempBlob.Blob.CREATEINSTREAM(InStream);
  END;
END;

ProcessWSMethodL.RequestBody(EComWSConnection."Request Body XMLPort ID",EComWSConnection."Request Web Service Method",
                             EComWSConnection."Request Web Service Object",OutStream,ExcutionStatusL);
IF ExcutionStatusL = ExcutionStatusL::"RecordFound Error" THEN BEGIN
  CLEARALL;
  EXIT;
END;

InStream.READTEXT(XMLVersion);
IF STRPOS(XMLVersion,'<?xml') > 0 THEN BEGIN
  bTextBody.READ(InStream);
  CLEAR(TempBlob.Blob);
  TempBlob.Blob.CREATEOUTSTREAM(OutStream);
  TempBlob.Blob.CREATEINSTREAM(InStream);
END;

OutStream.WRITETEXT(XMLVersion);
OutStream.WRITETEXT();

IF EComWSConnection."SOAP Envelop Node" <> '' THEN BEGIN
  OutStream.WRITETEXT('<' + EComWSConnection."SOAP Envelop Node");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Envelop Namespace");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Envelop Attribute 1");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Envelop Attribute 2");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Envelop Attribute 3");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Envelop Attribute 4");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Envelop Attribute 5");
  OutStream.WRITETEXT('>');
  OutStream.WRITETEXT();
END;

IF EComWSConnection."SOAP Header Node" <> '' THEN BEGIN
  OutStream.WRITETEXT('<' + EComWSConnection."SOAP Header Node" + '>');
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Header Namespace");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Header Attribute 1");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Header Attribute 2");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Header Attribute 3");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Header Attribute 4");
  OutStream.WRITETEXT();
  bTextHeader.WRITE(OutStream);
  OutStream.WRITETEXT('</' + EComWSConnection."SOAP Header Node" + '>');
  OutStream.WRITETEXT();
END;

IF EComWSConnection."SOAP Body Node" <> '' THEN BEGIN
  OutStream.WRITETEXT('<' + EComWSConnection."SOAP Body Node" + '>');
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Body Namespace");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Body Attribute 1");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Body Attribute 2");
  OutStream.WRITETEXT();
  OutStream.WRITETEXT(EComWSConnection."SOAP Body Attribute 3");
  OutStream.WRITETEXT();
  bTextBody.WRITE(OutStream);
  OutStream.WRITETEXT('</' + EComWSConnection."SOAP Body Node" + '>');
  OutStream.WRITETEXT();
  OutStream.WRITETEXT('</' + EComWSConnection."SOAP Envelop Node" + '>');
END;

IF ISNULL(XMLRequestL) THEN
  XMLRequestL := XMLRequestL.XmlDocument;
XMLRequestL.Load(InStream);


XMLHTTPRequestL := XMLHTTPRequestL.Create(EComWSConnection."Web Service URL");
XMLHTTPRequestL.Timeout := 50000;
XMLHTTPRequestL.UseDefaultCredentials := FALSE;
CredentialL := CredentialL.NetworkCredential;
CredentialL.UserName := EComWSConnection."HTTP Connection UserName";
CredentialL.Password := EComWSConnection."HTTP Connection Password";
XMLHTTPRequestL.Credentials := CredentialL;
XMLHTTPRequestL.Method := EComWSConnection."HTTP Request Method";
XMLHTTPRequestL.Host := EComWSConnection."HTTP Connection Host Name";
XMLHTTPRequestL.Headers.Add('SOAPAction',EComWSConnection."Web Service Namespace" + EComWSConnection."Request Web Service Method");
XMLHTTPRequestL.ContentType := EComWSConnection."HTTP Request Content-Type";
XMLHTTPRequestL.Accept := 'text/xml';
MemoryStreamL := XMLHTTPRequestL.GetRequestStream;
XMLRequestL.Save(MemoryStreamL);

//To store outgoining file
/XMLRequestL.Save(path);

MemoryStreamL.Flush;
MemoryStreamL.Close;

XMLHTTPResponseL := XMLHTTPRequestL.GetResponse;
MemoryStreamL := XMLHTTPResponseL.GetResponseStream;

// To maintain resoponse status and log info

IF XMLHTTPResponseL.StatusCode = 200 THEN BEGIN
  IF ISNULL(XMLResponseL) THEN
    XMLResponseL := XMLResponseL.XmlDocument;
  XMLResponseL.Load(MemoryStreamL);

  MemoryStreamL.Flush;
  MemoryStreamL.Close;
  XMLHTTPResponseL.Close;
  ProcessWSMethodL.Response(EComWSConnection."Request Web Service Method",EComWSConnection."Request Web Service Object",
                            XMLResponseL,EComWSConnection."Web Service URL",EComWSConnection."Response Web Service Method");
END ELSE BEGIN
  OutgoingComLogEntry."Is Error Occured" := TRUE;
  OutgoingComLogEntry."Error Code" := FORMAT(XMLHTTPResponseL.StatusCode);
  OutgoingComLogEntry."Error Description" := XMLHTTPResponseL.StatusDescription;
END;
CLEARALL;