1

I want to download an excel file from azure blob and process it's data using the 'xlsx' npm module. I have achieved this with saving the file to local directory on my node.js server.

But I have to Implement this without needing to save the file locally on server.

How do I achieve this ?

Following is my js file using - download to local directory method.

const xlsx = require('xlsx');

  const azureStorageConfig = {
  accountName: "",
  accountKey: "",
  blobURL: "",
  containerName: "test-container"
};

let fileName = "test_blob.xlsx";

const downloadBlob = async (blobName, downloadFilePath) => {
  return new Promise((resolve, reject) => {
    const name = path.basename(blobName);
    const blobService = azureStorage.createBlobService(azureStorageConfig.accountName,azureStorageConfig.accountKey); 
    blobService.getBlobToLocalFile(azureStorageConfig.containerName,blobName,`${downloadFilePath}${name}`, function(error, serverBlob) {
        if (error) {
            reject(error);
        } else {
            resolve(downloadFilePath);
        }
    });
  });
};

downloadBlob(fileName,'./local_dir/').then((downloadFilePath)=>{
  parseExcel(downloadFilePath + fileName);
});

const parseExcel = function(downloaded_file_path){
  let workbook = xlsx.readFile(downloaded_file_path);
  // Parse further
}

How this code will change when following a process which does not require saving the file to local directory ?

Piyush Upadhyay
  • 177
  • 1
  • 12

1 Answers1

1

As reference for you, here is my idea with sample code for your needs, as below.

  1. Generate a blob url with SAS token

    var azure = require('azure-storage');
var accountName = '<your account name>';
var accountKey = '<your account key>';
var blobService = azure.createBlobService(accountName, accountKey);

var containerName = 'test-container';
var blobName = 'test_blob.xlsx';

var startDate = new Date();
var expiryDate = new Date(startDate);
expiryDate.setMinutes(startDate.getMinutes() + 100);
startDate.setMinutes(startDate.getMinutes() - 100);

var sharedAccessPolicy = {
  AccessPolicy: {
    Permissions: azure.BlobUtilities.SharedAccessPermissions.READ,
    Start: startDate,
    Expiry: expiryDate
  }
};

var token = blobService.generateSharedAccessSignature(containerName, blobName, sharedAccessPolicy);
var sasUrl = blobService.getUrl(containerName, blobName, token);

  2. Read blob body bytes via request, then to use XLSX.read(data, read_opts) to parse blob body as Uint8Array.

    var request = require('request');
var XLSX = require('xlsx');
request(sasUrl, {encoding: null}, function (error, response, body) {
  var workbook = XLSX.read(body, {type:"buffer"});
  console.log(workbook.Sheets.Sheet1);
});

Hope it helps.

Peter Pan
  • 23,476
  • 4
  • 25
  • 43
  • Hi , for some reason I am not able to access cell values from the excel file using this method. Please check and revert. console.log(workbook.SheetNames); shows Sheet1 but is a false output. Cannot actually access sheet and read cell values. – Piyush Upadhyay Jun 11 '19 at 05:50
  • @PiyushUpadhyay Oh, as you said, there is an issue in my code to read xlsx from blob url with sas token via `request`. I fix it and update my post for the code of part 2. Please try. – Peter Pan Jun 11 '19 at 07:00
  • @PiyushUpadhyay Meanwhile, I found the offical sample code of `xlsx` package https://github.com/SheetJS/js-xlsx/tree/4109caeadd95baca58c7c2481aebf2014ae462bd/demos/server#node-buffer. – Peter Pan Jun 11 '19 at 07:02