CODEWITHSUNDEEP
javascriptexcelcsvsharepointxlsx
LaLaLottie
LaLaLottie

Reputation: 403

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

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.

Upvotes: 13

Views: 38260

Answers (4)

Lukas
Lukas

Reputation: 83

a very easy solution is the https://github.com/netas-ch/csv2xlsx library.

Example:

async function downloadAsExcel() {
    const cvrt = await import('src/Csv2Xlsx.js');

    // Meta-Data of the xlsx
    const metaData = {
        title: 'My Demo Spreadsheet',
        subject: 'A Demo for CSV to XLSX',
        creator: 'John Doe',
        company: 'Super Doe Ltd.',
        lastModifiedBy: 'John Doe',
        created: new Date(2020, 0, 1, 11, 21),
        modified: null // now
    };

    // download the csv and convert it to a excel file
    const aTag = await cvrt.Csv2Xlsx.convertCsv('my/demo/data.csv', 'mynewfilename.xlsx', metaData);

    // return value is a <a> element, add it to the DOM to start the download
    document.body.appendChild(aTag);

    // start the download automatically
    aTag.click();
}

runs in every modern browser (Chrome, Firefox, Safari).

Upvotes: 2

AnandShiva
AnandShiva

Reputation: 1339

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'))
}

Upvotes: 1

Erik
Erik

Reputation: 92

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

Upvotes: 4

tekman13
tekman13

Reputation: 31

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.

Upvotes: 3

Related Questions