Convert CSV and TXT data into a fillable combo box with ONLYOFFICE macro

ONLYOFFICE products are very powerful solutions that cover all the demands of our digital age. Starting from version 7.0, the team of ONLYOFFICE experts extended the functionality of our editors with ONLYOFFICE forms. Designed to make working with electronic documents easy, ONLYOFFICE forms can alleviate a lot of mundane tasks. One of the good examples is using fillable forms for creating a sales agreement template. But what if you have your clients’ information stored in separate CSV/TXT files? In this blog post, we will show you how to build a macro that imports CSV and TXT data and converts it into a fillable combo box.

About ONLYOFFICE forms

The extended functionality brings formats that allow users to effortlessly create and operate with fillable forms in their documents. DOCXF format is a file extension based on DOCX that offers a wide range of tools for creating and editing forms. It allows building forms from scratch or from an existing DOCX file. OFORM is an OOXML extension for online ready-to-fill-out forms. It makes a form template fillable and restricts editing by other users.

ONLYOFFICE forms inherit the structure of a text document and include several major classes:

• text field
• combo box
• checkbox
• picture form
• complex form

These classes contain methods that incorporate creating text fields, complex fields, combo boxes, drop-down lists, radio buttons, image forms, phone and email address fields.

We will use a combo box class for our macro. The combo box element incorporates a drop-down list of values that we will import from the external CSV/TXT file.

Building a macro

The Form section of our API documentation includes the Api.CreateComboBoxForm method. It creates a combo box with specified parameters:

 var oComboBoxForm = Api.CreateComboBoxForm({"key": "Personal information", "tip": "Choose your country", "required": true, "placeholder": "Country", "editable": false, "autoFit": false, "items": ["Latvia", "USA", "UK"]});

The items parameter contains the array of the values displayed in the drop-down list. So our course of action is to replace this array with the array of values obtained from the external CSV/TXT file.

But first, we need to fetch the external file and process the data. We used a similar strategy with the Import CSV/TXT data macro. ONLYOFFICE macros are JavaScript based which makes them extremely versatile and allows us to use AJAX requests:

function LoadFile() {
        $.ajax({
            url: 'file:///C:/Users/LEOPARD/Desktop/Book1.csv',
            dataType: 'text',https://wpblog.onlyoffice.com//wp-admin/post-new.php?lang=en#
        }).done(successFunction);
    }
        LoadFile();

Now that we have fetched the file, we need to process this raw data and format it. So we add the succesFunction. It will take the data and break the text into an array of items by lines:

 function successFunction(data) {
      var arrAllItems = data.split(/\r?\n|\r/);
}

Then we target the active document and create the combo box with the arrAllItems array in the items parameter:

 var oDocument = Api.GetDocument();
 var oComboBoxForm = Api.CreateComboBoxForm({ "key": "Personal information1", "tip": "Choose your country", "required": true, "placeholder": "Country", "editable": false, "autoFit": false, "items": arrAllItems });

After that we create a paragraph and add the combo box to it:

  var oParagraph = Api.CreateParagraph();
oParagraph.AddElement(oComboBoxForm);

Now we need to insert our combo box into the document. There are several ways to tackle this task, but we suggest using the InsertContent method. It inserts an element right where the cursor is positioned. We also suggest specifying the text and paragraph proprieties in the parameters. It will prevent possible mistakes and macro malfunctioning:

oDocument.InsertContent([oParagraph], { "KeepTextOnly": true });

The entire macro code is the following:

(function()
{
   function LoadFile() {
      $.ajax({
          url: 'file:///filePath,
          dataType: 'text',
      }).done(successFunction);
    }
        LoadFile();

   function successFunction(data) {
        var arrAllItems = data.split(/\r?\n|\r/);
        var oDocument = Api.GetDocument();
        var oComboBoxForm = Api.CreateComboBoxForm({ "key": "Personal information", "tip": "Choose your option", "required": true, "placeholder": "Option", "editable": false, "autoFit": false, "items": arrAllItems });
        var oParagraph = Api.CreateParagraph();
oParagraph.AddElement(oComboBoxForm);
oDocument.InsertContent([oParagraph], { "KeepTextOnly": true });
    }
})();

Now, let’s run our macro and see how it works!

Note!

• To insert a combo box, press space after running a macro.
• In order to insert multiple combo boxes into the document use different key parameters of the Api.CreateComboBoxForm method.

This macro is just one of the many examples of what you can do by implementing our API methods. We kindly encourage you to experiment and hope that you will utilize the concepts outlined in this post to build your own macros.

Our macros are JavaScript based which makes them extremely versatile and practical. Don’t hesitate to ask questions or share your ideas with us. We are open to discussion and cooperation. Best of luck in your exploratory endeavors!