Application Developmentpublic

Visual Builder

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Lanke Dibora
    How to read .XLSX file data in VBCSAnswered
    Topic posted August 30, 2019 by Lanke DiboraBlue Ribbon: 750+ Points 
    41 Views, 11 Comments
    Title:
    How to read .XLSX file data in VBCS
    Summary:
    I need to upload .xlsx file and read data into the VBCS and insert in to the Business Object.
    Content:

    I need to upload .xlsx file and read data into the VBCS and insert in to the Business Object.

    Please let me the sample code to achieve this functionality.

    Version:
    19.1.3

    Best Comment

    Lanke Dibora

    Thank you ,

    will try the above solution.

    Comment

     

    • Shay Shmeltzer

      You can use the data manager to load data into BOs -

      Work with the Data Manager                         

      There is also a command line that can be invoked from outside the UI to load data.

      • Lanke Dibora
        Hi, Thank you for your response. My requirement is to read xlsx file file and insert those records on button click in UI. Please let me know how to acquire that functionality. Thanks in advance Debora.
    • Shay Shmeltzer
      This might help https://blogs.oracle.com/groundside/uploading-and-reading-a-text-file-in-visual-builder
      • Lanke Dibora

        HI I used the below method on File picker select event .

         

        PageModule.prototype.getFile = function(event) {
            var file = null;
            var binaryString = null;
            var base64String = null;
            var filesArr = event.detail.files;
            for (var i = 0; i < filesArr.length; i++) {
              file = filesArr[i];
            }
            var self = this;
            self.fileContent = ko.observable("");
            console.log("Nav");

            var reader = new FileReader();
            base64String = PageModule.prototype.parseUploadedFile1(event,
              reader, file);

            return base64String;
          }

         

        PageModule.prototype.parseUploadedFile1 = function(event, reader, file) {

            var readDataPromise = new Promise(function(resolve) {
              var baseString = reader.addEventListener("loadend", function() {
                var file = null;
                var binaryString = null;
                var base64String = null;
                var filesArr = event.detail.files;
                resolve((reader.result));
                var listObj = [];
                var arrayObj = [];
                var obj = {};
                obj.A = "a";

                var content = reader.result;
                var lines = content.split("\r");

                for (var count = 0; count < lines.length; count++) {
                  var rowContent = lines[count].split(",");

                  for (var i = 0; i < rowContent.length; i++) {
                    if (count != null && count == 0) {
                      listObj.push(rowContent[i]);

                    }
                    if (count != null && count > 0) {
                      var cloName = listObj[i];
                      var cloVal = rowContent[i];
                    }
                  }
                  arrayObj.push(obj);

                }
        console.log("reader.result ---------------->", reader.result);
                return reader.result;
              });

            reader.readAsBinaryString(file);

            });

            return readDataPromise;
          }
          return PageModule;
        });

         

        I'm getting reader.result in the below format.

        Format:-- For Output format , please verify in the attachments.

         

        Please let me know hoe to to get in the text format.

        Thanks In Advance.

        Deborah.

      • Lanke Dibora

        HI I used the below method on File picker select event .

         

        PageModule.prototype.getFile = function(event) {
            var file = null;
            var binaryString = null;
            var base64String = null;
            var filesArr = event.detail.files;
            for (var i = 0; i < filesArr.length; i++) {
              file = filesArr[i];
            }
            var self = this;
            self.fileContent = ko.observable("");
            console.log("Nav");

            var reader = new FileReader();
            base64String = PageModule.prototype.parseUploadedFile1(event,
              reader, file);

            return base64String;
          }

         

        PageModule.prototype.parseUploadedFile1 = function(event, reader, file) {

            var readDataPromise = new Promise(function(resolve) {
              var baseString = reader.addEventListener("loadend", function() {
                var file = null;
                var binaryString = null;
                var base64String = null;
                var filesArr = event.detail.files;
                resolve((reader.result));
                var listObj = [];
                var arrayObj = [];
                var obj = {};
                obj.A = "a";

                var content = reader.result;
                var lines = content.split("\r");

                for (var count = 0; count < lines.length; count++) {
                  var rowContent = lines[count].split(",");

                  for (var i = 0; i < rowContent.length; i++) {
                    if (count != null && count == 0) {
                      listObj.push(rowContent[i]);

                    }
                    if (count != null && count > 0) {
                      var cloName = listObj[i];
                      var cloVal = rowContent[i];
                    }
                  }
                  arrayObj.push(obj);

                }
        console.log("reader.result ---------------->", reader.result);
                return reader.result;
              });

            reader.readAsBinaryString(file);

            });

            return readDataPromise;
          }
          return PageModule;
        });

         

        I'm getting reader.result in the below format.

        Format:-- For Output format , please verify in the attachments.

         

        Please let me know hoe to to get in the text format.

        Thanks In Advance.

        Deborah.

    • Duncan Mills

      If you are going to read a proprietory file format like Excels then you'll need to use an additional library to decode the contents, Visual Builder can'f do that for you. 

      You might want to look at the ExcelJS project on GITHub - this has functions to read XLSX documents, but we can't help you with the specific usage of that code, it's outside of our scope here. 

      • Lanke Dibora

        Hi ,

         

        I have used external js file called xlsx.full.min.js and imported under the resources folder and given the path in the js section of the page as shown for extracting the xlsx file data.

        define(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojfilepicker' , "resources/xlsx.full.min", "flows/jszip", "resources/xlsx"], function(oj,
          ko, $, xlsfile, szipfile, xlsxfile) {
          'use strict';

          var PageModule = function PageModule() {
            var data =  null;
              var workbook = xlsxfile.XLSX.read(data, {
                    type: 'binary'
                });
              console.log("workbook---------------->", workbook);
            
            };

         

        I'm Facing the below error that : XLSX not found.

        Can you please help me out in this.

        • Duncan Mills

          I'm not familiar with that library but a quick read of the GitHub docs would suggest that you have made an error in your code. I can spot a couple of problems in the sample above:

          1. Your define list and associated function parameters are mismatched - at the moment the object loaded by the ojs/ojfilepicker reference is being passed as xlsfile and all the other parameters are off by one from what you expect, so xlsxfile does not contain the object that you expect

          2.  The read line should probably be:

          var workbook = xlsxfile.read(data ...
          • Lanke Dibora

            Thank you ,

            will try the above solution.

            • Lanke Dibora

              Hi,

              I have followed the below link.

              https://blogs.oracle.com/vbcs/importing-and-using-javascript-libraries-in-your-visual-builder-custom-code

              I tried the below code.please verify attachment.

              I got the below error.

              {}
              log.js:312 [VB 5:11:16 PM (ERROR), /vb/action/action/CallModuleAction]: CallModuleAction (callModuleFunction1_hn8cfh41f) error running custom action: testFun(undefined) TypeError: Cannot read property 'read' of undefined
                  at PageModule.testFun (main-test-page.js:7)
                  at CallModuleAction.perform (callModuleFunctionAction.js:90)
                  at action.js:40
              _logMessage @ log.js:312
              groupMessagesIfNeeded @ log.js:189
              _groupLogMessages @ log.js:202
              log @ log.js:103
              error @ log.js:361
              perform @ callModuleFunctionAction.js:93
              (anonymous) @ action.js:40
              Promise.then (async)
              start @ action.js:40
              (anonymous) @ actionChain.js:376
              Promise.then (async)
              runActionStep @ actionChain.js:340
              (anonymous) @ actionChain.js:111
              Promise.then (async)
              (anonymous) @ actionChain.js:99
              start @ actionChain.js:97
              startChain @ actionChain.js:264
              callActionChain @ container.js:581
              (anonymous) @ container.js:537
              callEventListeners @ container.js:531
              invokeComponentEvent @ container.js:788
              (anonymous) @ container.js:677
              (anonymous) @ ojcorebundle.js:22776
              domListener @ ojcorebundle.js:6372
              _triggerCustomEvent @ ojcorebundle.js:10021
              (anonymous) @ oj3rdpartybundle.js:17265
              _trigger2 @ ojcorebundle.js:9931
              (anonymous) @ oj3rdpartybundle.js:17265
              _trigger @ ojcorebundle.js:9909
              (anonymous) @ oj3rdpartybundle.js:17265
              _ojActionClickHandler @ ojcommoncomponentsbundle.js:1119
              log.js:312 [VB 5:11:16 PM (FINE), /vb/action/action/CallModuleAction]: Ending action CallModuleAction (callModuleFunction1_hn8cfh41f) with outcome 'failure' and result:  (completed in 1 ms) {message: {…}, error: TypeError: Cannot read property 'read' of undefined
                  at PageModule.testFun (https://gbldev-lendl…, payload: undefined}

               

               

               

              Error.PNG (42KB)
              • Lanke Dibora

                Also the js file code in shown like below.

                define(["resources/xlsx.full.min","resources/xlsx"], function(xlsxfull, xlsxjs) {
                  'use strict';

                  var PageModule = function PageModule() {};
                 PageModule.prototype.testFun = function() {
                          var filesArr = [];  
                        var workbook = xlsxfull.read(filesArr[0], {
                            type: 'binary'
                        });
                   
                  }
                  return PageModule;
                });

                 

                JS_File_Code.PNG (44KB)

     

    community forum