JavaScript - Convert CSV to XLSX (Preferably Without Use of Library(s))

Question

As the title says, I currently have a CSV file created from SharePoint list data and in order to display this information as a spreadsheet, I want to convert it to an Excel XLSX file. I prefer to do this without relying on a third-party library. At first, I started to use ActiveX objects to try to recreate and/or save the CSV as XLSX, but there's a limitation with that since I can't really use it in other browsers besides IE. I was thinking using Blob to somehow convert it? That's where I'm stuck.

function createCsv(data) {
    var result = "";

    if (data == null || data.length == 0) {
        return;
    }

    var columnDelimiter = ',';
    var lineDelimiter = '\n';

    var keys = Object.keys(data[0]);

    // spreadsheet header

    result += keys.join(columnDelimiter);
    result += lineDelimiter;

    // spreadsheet data

    data.forEach(function (obj) {
        var count = 0;

        keys.forEach(function (key) {
            if (count > 0) {
                result += columnDelimiter;
            }

            result += obj[key];
            count++;               
        });

        result += lineDelimiter;
    });

    return result;
}

function downloadCsv(csv) {
    if (csv == null) {
        return;
    }

    var filename = "test.csv";

    csv = "data:text/csv;charset=utf-8," + csv;

    var data = encodeURI(csv);

    console.log(data);

    var link = document.getElementById('csv');
    link.setAttribute('href', data);
    link.setAttribute('download', filename);

    console.log(link);

    //displayCsv(csv);
}

function displayCsv() {
    // using test csv here
    var message = "data:text/csv;charset=utf-8, yo, hey, lol";
    //var fileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var fileType = "application/msexcel";

    var csvFile = new Blob([message], {type: fileType});
    var csvUrl = URL.createObjectURL(csvFile);

    console.log(csvFile);
    console.log(csvUrl);

}

CSV works fine with using the spreadsheet (by downloading and opening it in Excel), but I really need a way to display it as a spreadsheet on a webpage and not as text, so that's why I'm looking to convert it over. Since I'm using this within SharePoint then I can use a Excel web part to display the XLSX - it won't open CSV files like this though. Thanks in advance.

Solution 1

It would be quite the undertaking to try to manually try to do this without libraries. While OpenXML files are XML based at their core, they are also bundled/zipped.

I would recommend take a look at SheetJS. https://sheetjs.com/

You can take CSV as input, and write it back out immediately as XSLX.

Solution 2

I'm not sure that this will solve your issues but if a xls file will suffice you can create a xls file simply by adding a separator tag to the first line of the csv and rename it to xls. Quotes around the values has also been important.

Eg:

"sep=,"
"Service","Reported","Total","%"
"a service","23","70","32.86%"
"yet_a_service","27","70","38.57%"
"more_services","20","70","28.57%"

Solution 3

If you are fine with using a third-party library (which I strongly recommend considering the complexity involved in conversion ), this solution will suit your needs if it needs to be done in nodejs.

If you want to use it in the browser, convertCsvToExcel function needs to be modified to transform the buffer to a blob object, then converting that blob to an XLS file.

// Convert a CSV string to XLXS buffer 
// change from xlxs/xls and other formats by going through sheetsjs documentation.
import * as XLSX from 'xlsx';

export const convertCsvToExcelBuffer = (csvString: string) => {
  const arrayOfArrayCsv = csvString.split("\n").map((row: string) => {
    return row.split(",")
  });
  const wb = XLSX.utils.book_new();
  const newWs = XLSX.utils.aoa_to_sheet(arrayOfArrayCsv);
  XLSX.utils.book_append_sheet(wb, newWs);
  const rawExcel = XLSX.write(wb, { type: 'base64' })
  return rawExcel
}

// Express request handler for sending the excel buffer to response.

export const convertCsvToExcel = async (req: express.Request, res: express.Response) => {
    const csvFileTxt = fileBuffer.toString()
    const excelBuffer = convertCsvToExcelBuffer(csvFileTxt)
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    res.status(200).send(Buffer.from(excelBuffer, 'base64'))
}

Comments

[Brian Burns]

Tried this with Excel 365 for Mac - said "The file format and extension of 'Book1.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"

[ADyson]

CSV != XLS. Just giving it a different extension doesn't actually change the file format, it simply causes confusion. XLS is a binary format entirely different from text-based CSV.