msgraph - download xlsx file from sharepoint using msgraph service

Question

I need to download all xlsx files from myFolder I have two methods getExcelSheets to get all excel sheets list

then using this information to download the file using @microsoft.graph.downloadUrl by calling getFileContentById method but I failed to get and convert it to xlsx format

import { Client } from "@microsoft/microsoft-graph-client";
import axios from "axios";
import * as MicrosoftGraph from "@microsoft/microsoft-graph-types";

export async function getExcelSheets(
  template_name: string,
  msgraph_client: Client,
): Promise<MicrosoftGraph.DriveItem[]> {
  const result = await msgraph_client
    .api(
      `drives/{driver_id}/root:/myFolder/${template_name}:/children`
    )
    .get();
  return result.value as MicrosoftGraph.DriveItem[];
}

export async function getFileContentById(download_url: string): Promise<any> {
  const response = await axios.get(download_url);
  return response;
}

any hint on how I get the file then convert it to xlsx I was using this method to convert the buffer to xlsx

xlsx.read(file, { type: "buffer" })

Answer 1

The Axios call you are making in getFileContentById will receive a stream that you can write to a file or convert to xlsx like below,

export async function getFileContentById(download_url: string): Promise<any> {
   const writer = fs.createWriteStream('file.xlsx');
   return axios({
      method: 'get',
      url: download_url,
      responseType: 'stream',
   }).then(response => {
      // Save the file and read later
      response.data.pipe(writer);
      
      // OR Convert the binary to xlsx usibng the library
      const sheet = XLSX.read(response.data, { type: "buffer" });
      console.log(sheet)
      /*
      {
        SheetNames: [ 'Sheet1' ],
        Sheets: { Sheet1: { A1: [Object], '!ref': 'A1' } }
      }
      */
   });

}