What are the steps to transform a blob into an xlsx or csv file?

Question

What are the steps to transform a blob into an xlsx or csv file?

An interesting feature of the application is the ability to download files in various formats such as xlsx, csv, and dat. To implement this, I have utilized a library called fileSaver.js.

While everything works smoothly for the dat/csv format, there seems to be an issue with the xlsx files being corrupted.

To troubleshoot this problem, I experimented with different conversion formats including:

utf8
base64
binary

Here's a snippet of how I implemented this:

// /* SERVER */ //
// Read data from file
fs.readFile(filePath, (err, data) {...})

// The API responds with the important parts "filename" & "data"
{"status":"ok","context":"writing the intermediate file","target":"/temp/","fileName":"name.xlsx","data":{"type":"Buffer","data":[72,82,65,67,67,69,83,83,32,10]}}

// /* CLIENT */ //
let json = JSON.stringify(data)
let buffer = Buffer.from(JSON.parse(json).data)
let read = buffer.toString('utf8')
let blob = new Blob([read])
FileSaver.saveAs(blob, fileName)

javascript csv dataset blob xlsx

Answer 1

Answer №1

For those delving into this topic, here is my solution:

(Note that a more efficient solution for downloading a file would be to send the file in the API response with the header 'Content-disposition' or utilize Express for this purpose like this)

The backend (Node) operates as follows:

 fs.readFile(filePath, (err, data) => {
    if (err) {
      console.log(`-------- whoops error - read --------`)
      console.log(err)
      res.send({ status: `error`, context: `read the source file`, id: err.errno, code: err.code, message: err.message.replace(/\\/g, '/') })
    } else {
      res.send({ status: `ok`, context: `send data file`, target: target, fileName: fileName, data: data })
    } 
  })

Details:

target represents the path for the front end including the file name and extension (/path/name.ext)
fileName simply contains the name and extension (name.ext)

data is the information sent by readFile (

{"type":"Buffer","data":[72,82,65,67,67,69,83,83,32,10]}

)

The frontend (React) functions like this:

fetch(targetUrl)
    .then(res => res.json())
    .then(res => {
      if (res.status !== `ok`) {
        this.setState({
          errorDlFile: true,
          errorDlFile_context: res.context,
          errorDlFile_id: res.id,
          errorDlFile_code: res.code,
          errorDlFile_message: res.message
        })
      } else {  
        const target = res.target
        const fileName = res.fileName
        const data = res.data
        const splitName = res.fileName.split('.')
        const format = splitName[splitName.length-1]

        // file saver blob solution
        let json = JSON.stringify(data)
        let buffer = Buffer.from(JSON.parse(json).data)
        let readUTF8 = buffer.toString('utf8')
        let blob = ''

        if (format === 'xlsx') {
          blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
        } else if (format === 'csv') {
          blob = new Blob([readUTF8], { type: 'application/vnd.ms-excel' })
        } else {
          blob = new Blob([readUTF8])
        }

        FileSaver.saveAs(blob, fileName)

      }
    })

Answer 2

For those delving into this topic, here is my solution:

(Note that a more efficient solution for downloading a file would be to send the file in the API response with the header 'Content-disposition' or utilize Express for this purpose like this)

The backend (Node) operates as follows:

 fs.readFile(filePath, (err, data) => {
    if (err) {
      console.log(`-------- whoops error - read --------`)
      console.log(err)
      res.send({ status: `error`, context: `read the source file`, id: err.errno, code: err.code, message: err.message.replace(/\\/g, '/') })
    } else {
      res.send({ status: `ok`, context: `send data file`, target: target, fileName: fileName, data: data })
    } 
  })

Details:

target represents the path for the front end including the file name and extension (/path/name.ext)
fileName simply contains the name and extension (name.ext)

data is the information sent by readFile (

{"type":"Buffer","data":[72,82,65,67,67,69,83,83,32,10]}

)

The frontend (React) functions like this:

fetch(targetUrl)
    .then(res => res.json())
    .then(res => {
      if (res.status !== `ok`) {
        this.setState({
          errorDlFile: true,
          errorDlFile_context: res.context,
          errorDlFile_id: res.id,
          errorDlFile_code: res.code,
          errorDlFile_message: res.message
        })
      } else {  
        const target = res.target
        const fileName = res.fileName
        const data = res.data
        const splitName = res.fileName.split('.')
        const format = splitName[splitName.length-1]

        // file saver blob solution
        let json = JSON.stringify(data)
        let buffer = Buffer.from(JSON.parse(json).data)
        let readUTF8 = buffer.toString('utf8')
        let blob = ''

        if (format === 'xlsx') {
          blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
        } else if (format === 'csv') {
          blob = new Blob([readUTF8], { type: 'application/vnd.ms-excel' })
        } else {
          blob = new Blob([readUTF8])
        }

        FileSaver.saveAs(blob, fileName)

      }
    })

Answer 3

Answer №2

@Hadock To download a file with a .csv extension, you must specify the type of file like this:

let blob = new Blob([csv], { type: 'application/vnd.ms-excel' });

instead of

let blob = new Blob([read])

And remember to include the filename with the extension (test.csv). I have also used a different plugin for excel files called exceljs. You can see a demo of it here: demo.

Answer 4

@Hadock To download a file with a .csv extension, you must specify the type of file like this:

let blob = new Blob([csv], { type: 'application/vnd.ms-excel' });

instead of

let blob = new Blob([read])

And remember to include the filename with the extension (test.csv). I have also used a different plugin for excel files called exceljs. You can see a demo of it here: demo.

Answer 5

Answer №3

To convert JSON data to an Excel (.xlsx) file, you cannot directly save it in that format. However, you can achieve this by using a library such as 'sheetjs' ().

var sheet_name = filename;//"SheetJS";
var workbook = new Workbook(), worksheet = sheet_from_array_of_arrays(data);
/* add the worksheet to the workbook */
workbook.SheetNames.push(sheet_name);
workbook.Sheets[sheet_name] = worksheet;
var workbook_output = XLSX.write(workbook, { bookType: 'xlsx', bookSST: true, type: 'binary' });
saveAs(new Blob([s2ab(workbook_output)], { type: "application/octet-stream" }), filename + ".xlsx")

Answer 6

To convert JSON data to an Excel (.xlsx) file, you cannot directly save it in that format. However, you can achieve this by using a library such as 'sheetjs' ().

var sheet_name = filename;//"SheetJS";
var workbook = new Workbook(), worksheet = sheet_from_array_of_arrays(data);
/* add the worksheet to the workbook */
workbook.SheetNames.push(sheet_name);
workbook.Sheets[sheet_name] = worksheet;
var workbook_output = XLSX.write(workbook, { bookType: 'xlsx', bookSST: true, type: 'binary' });
saveAs(new Blob([s2ab(workbook_output)], { type: "application/octet-stream" }), filename + ".xlsx")

What are the steps to transform a blob into an xlsx or csv file?

Answer №1

The backend (Node) operates as follows:

The frontend (React) functions like this:

Answer №2

Answer №3

Similar questions

The error message that you are seeing is indicating that the `contracts` property of `this.state

Extract the last word from a string, filter out any special characters, and store it in an array

Unable to interpret encoded json information

Creating multiple versions of a website based on the user's location can be achieved by implementing geotargeting techniques

Component not being returned by function following form submission in React

Iterating through the sorted list in reverse order, retrieving the text of each list item

JavaScript-based tool for extracting content from Sketch file

How can I obtain the coordinates when the mouse enters Vue?

What causes JavaScript image to stop loading while displaying a prompt dialog?

Unexpected reduce output displayed by Vuex

Setting Authorization with username, password, and domain in Angular 2 HTTP Request

The Axios patch method encounters an issue where the baseURL is not retrieved

What is the best way to loop through a group of WebElements, and only log the results that contain a specific substring?

Clicking outside of a focused div does not trigger a jQuery function

ExpressJS, defining routes, locating controllers, and documenting APIs

Unravel the encoded string to enable JSON parsing

The API response in JSON format is displaying as "undefined"

Access your account using Google authentication within an Angular framework

Top method for organizing and filtering tables in Laravel on the client side?

Ajax request returns a 500 error, yet the request successfully executes