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.

I tried using `iframes` and the problem I'm having is that the browser automatically downloads the link I set as the `src` for the `iframe` instead of displaying it. Only when the type is set to text/plain will it display the CSV as expected. — LaLaLottie, May 24 '16 at 14:51
@ValayDesai Unfortunately, I didn't. I just made do with a CSV. There was a lot of special encoding that came with the XLSX file format, which made conversion not worth it. But there probably is a third party app that could do this. — LaLaLottie, May 15 '17 at 20:54

score 3 · Answer 1 · answered May 18 '20 at 14:28

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.

score 3 · Answer 2 · edited Sep 23 '20 at 15:04

3

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%"

edited Sep 23 '20 at 15:04

yudhiesh

6,383
3
16
49

answered Sep 23 '20 at 12:40

Erik

82
4

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?" – Brian Burns Apr 05 '21 at 21:17
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. – ADyson Apr 07 '21 at 17:49

score 1 · Answer 3 · edited Dec 17 '22 at 16:54

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 XLSX buffer 
// change from xlsx/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'))
}

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

3 Answers3