Import and Export using Excel Buffers

Import and Export using Excel Buffers

olisterr 2 comments
NAVISION

Introduction:

Using XMLPort to do the Import and Export using CSV or Tab Delimited .xls can be inconvenient at times. For example when the field value contains a COMMA (First Name, Last Name) or if there are QUOTES(“”) added by Excel when saving the data. Thus the best way I found was using Excel Buffer.

Pre-requisite:

  1. Microsoft Dynamics NAV 2013 R2 and above
  2. Microsoft Office 2010 and above

Theory:

Excel Buffer Table(370) is a table that stores the data from each cell as Row,Column, TextValue, Formula and other fields in a single record. So if Excel Sheet contains 5×5 grid i.e 5 rows and 5columns then the whole data is stored in the form 10 records in Excel Buffer. The Excel sheets can have various types of visual formatting such as Bold, Italic, Underline, Font Color, Background Colour etc.

Demonstration:

1. Exporting using Excel Buffer:

Export Excel Buffer Sample Code

                 Export Excel Buffer Sample Code

i. Var ExcelBufferTable – Type Record -ID 370

ii. ExcelBufferTable.AddColumn(<Value:Text>,<IsFormula:Boolean>,<CommentText:Text>,<IsBold:Boolean>,<IsItalics:Boolean>,<IsUnderlined:Boolean>,<NumFormat:Text>,<CellType:Option>) – adds a colum of required type.

iii. ExcelBufferTable.NewRow – adds a new row to the sheet.

iv. ExcelBufferTable.CreateBookAndOpenExcel(<FileName:Text>,<SheetName:Text>,<ReportHeader:Text>,<CompanyName:Text>,<User:Text>) – creates the Excel Sheet and the Excel Program.

 2. Importing using Excel Buffer:

 

Import1 Excel Buffer

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

i. FileMgmt.UploadFile(<WindowTitle:Text>,<FileExtension:Text>) – Uploaded the file using File Management codeunit function

ii. ExcelBufferTable.OpenBook(<Filename:Text>,<SheetName:Text>) – Open the Excel Sheet passed as Excel File(.xlsx) and read the sheet with name ‘Gift Cards’.

iii. Read the sheet using ExcelBuffer

iv. Count total Rows and Columns in the Excel sheet

v. GetValueAtCell(<RowNo:Integer>,<ColumnNo:Integer>) – Read each cell using function GetValueAtCell

Conclusion:

Thus, this is how I’ve implemented Excel Buffer for Importing and Exporting Excel Sheets. The import export was performed based on the Action Buttons as required and no special objects were created. Thanks for reading and stay in touch 🙂

2 Comments

Marek

September 21, 2018 at 8:48 pm

Hej, szczerze polecam oferte biura projektowego Sztuka Ogrodowa z Warszawy. Bardzo podoba mi sie ich profesjonalizm.
U mnie zrobili super robote i jestem bardzo zadowolony z rezultatu (moge podeslac kilka zdjec mojego ogrodu)
link do ich strony: http://sztuka-ogrodowa.com.pl/

 Reply

    olisterr

    November 25, 2018 at 3:57 pm

    Thanks a lot Marek. I’ll do check out their site!
    Appreciate your help 🙂

     Reply

Leave a Reply

www.000webhost.com