SheetJS / sheetjs

📗 SheetJS Spreadsheet Data Toolkit -- New home https://git.sheetjs.com/SheetJS/sheetjs
https://sheetjs.com/
Apache License 2.0
35.07k stars 8k forks source link

PC and Salesforce: Non printable characters populate cells when new headers added to .csv in Lightning Web Component #2512

Closed TNT-c closed 2 years ago

TNT-c commented 2 years ago

I inherited code for a Salesforce Lightning Web Component. In the JS, I added two string values to an array to append 2 additional column headers. When a PC user clicks on the LWC to download the .csv file, non printable characters populate the cells underneath the new headers and it looks like they are empty but they are not.

Sample .csv file, please see cell E2 and F2. Update Account Template (7).csv

LWC JS below:

import { LightningElement, track, api } from 'lwc';
import {ShowToastEvent} from 'lightning/platformShowToastEvent';
import csvFileRead from '@salesforce/apex/CsvFileReader.csvFileRead';
import updateFileTitle from '@salesforce/apex/CsvFileReader.updateFileTitle';
import cleanupFiles from '@salesforce/apex/CsvFileReader.cleanupFiles';
import workbook from '@salesforce/resourceUrl/sheetJsPro';
import { loadStyle, loadScript } from 'lightning/platformResourceLoader';
//import updateAccounts from '@salesforce/apex/CsvFileReader.updateAccounts';

 const columnsAccount = [{
    label: 'Account Id',
    fieldName: 'accId',
    type: 'text',
    sortable: false,
    wrapText: true
},
{
    label: 'Current Assignment Owner',
    fieldName: 'accCurrentAssignmentOwnerId',
    type: 'text',
    sortable: false,
    wrapText: true
},
{
    label: 'New Assignment Owner',
    fieldName: 'accNewAssignmentOwnerId',
    type: 'text',
    sortable: false,
    wrapText: true
},
{
    label: 'Failed Reason',
    fieldName: 'failedReason',
    type: 'text',
    sortable: false,
    wrapText: true
}];

/* CSV File Fields
AccountID, Current Assignment Owner Id, New Assignemnt owner Id, Assignment Reason, Deal Owner Id, Assigned To Id 
*/

export default class CSVFileReadLWC extends LightningElement {

    error;
    data;
    showMessage = false;  
    isLoading = false;
    columnsAccount = columnsAccount;
    recsToUpdate = false;
    numRecsToUpdate = 0;
    numFailedRecs = 0;

    // accepted parameters
    get acceptedCSVFormats() {
        return ['.csv'];
    }

    librariesLoaded = false;
    renderedCallback() {
        if (this.librariesLoaded) return;
        this.librariesLoaded = true;
        Promise.all([loadScript(this, workbook + "/sheetjs/xlsx.full.min.js")])
        .then(() => {
            //console.log("success");
        })
        .catch(error => {
            console.log("failure loading static resource sheetJsPro");
        });
    }

    uploadFileHandler(event) {
        // Get the list of records from the uploaded files
        const uploadedFiles = event.detail.files;
        this.error = undefined;
        this.showMessage = false;
        this.isLoading = true;
        this.fileTitleUpdate(uploadedFiles); //update the file name so we can clean them up
        this.readCsvFile(uploadedFiles); 
        this.fileCleanup();    
    } 

    fileTitleUpdate(uploadedFiles) {
        //console.log(uploadedFiles[0].documentId);
        updateFileTitle({documentId : uploadedFiles[0].documentId})
        .then(() => {
            //console.log('infile update');
        })
        .catch(error => {
            this.isLoading = false;
            // handle error
            if (Array.isArray(error.body)) {
                this.error = error.body.map(e => e.message).join(',');
            } else if(typeof error.body.message === 'string') {
                this.error = error.body.message;
            }
            // this.error = JSON.stringify(error);
            this.dispatchEvent(
                new ShowToastEvent({
                    title: 'Error',
                    message: error.body.message,
                    variant: 'error',
                }),
            );     
        });
    }

    readCsvFile(uploadedFiles) {

        csvFileRead({contentDocumentId : uploadedFiles[0].documentId})
        .then(result => {
            //console.log('result ===> ',result);

            this.data = result.failedRecords;

            if (!result.accounts || this.numRecsToUpdate.length === 0 ) {
                this.recsToUpdate = false;
                this.numRecsToUpdate = 0;
            } else {
                this.recsToUpdate = true;
                this.numRecsToUpdate = result.accounts.length;
            }

            if (!this.data || this.data.length === 0) {
                this.numFailedRecs = 0;                
            } else {
                this.numFailedRecs = this.data.length;
            }
            this.showMessage = true;
            this.isLoading = false;

            /*  Keep this here for Chunk Processing reference 
            let accounts = JSON.parse(JSON.stringify(result.accounts));
            let recordsChunks= this.splitArrayIntoChunksOfLen(accounts,100); //split into chunks to process so it helps with cpu limits
            console.log(recordsChunks);
            this.uploadChunks(recordsChunks,0);

            this.isLoading = false;
            this.dispatchEvent(
                new ShowToastEvent({
                    title: 'Accounts processed',
                    message: 'Any failed records will show on this page.',
                    variant: 'Info',
                }),
            );  */
        })
        .catch(error => {
            this.isLoading = false;
            // handle error
            if (Array.isArray(error.body)) {
                this.error = error.body.map(e => e.message).join(',');
            } else if(typeof error.body.message === 'string') {
                this.error = error.body.message;
            }
            // this.error = JSON.stringify(error);
            this.dispatchEvent(
                new ShowToastEvent({
                    title: 'Error',
                    message: error.body.message,
                    variant: 'error',
                    mode: 'sticky'
                }),
            );     
        })
    }

    fileCleanup() {
        // cleanup any files that are older than the value in the Number_Of_Files_To_Keep
        cleanupFiles()
        .then(() => {
            //console.log('infile update');
        })
        .catch(error => {
            this.isLoading = false;
            // handle error
            if (Array.isArray(error.body)) {
                this.error = error.body.map(e => e.message).join(',');
            } else if(typeof error.body.message === 'string') {
                this.error = error.body.message;
            }
            // this.error = JSON.stringify(error);
            this.dispatchEvent(
                new ShowToastEvent({
                    title: 'Error in file cleanup',
                    message: error.body.message,
                    variant: 'error',
                }),
            );     
        });
    }

    /* uploadChunks(chunks,index) {
        if(chunks.length > index) {
            console.log('Processing #chunk '+index+'of size '+chunks[index].length);
            this.processingRec = index * 100;
            updateAccounts({alist : chunks[index]})
            .then(result => {
                this.uploadChunks(chunks,(index+1)); 
            })
            .catch(error => {
                this.isLoading = false;
                this.error = JSON.stringify(error);
                this.dispatchEvent(
                    new ShowToastEvent({
                        title: 'Error!!',
                        message: JSON.stringify(error),
                        variant: 'error',
                    }),
                );     
            })
        }  
    }

    splitArrayIntoChunksOfLen(arr, len) {
        var chunks = [], i = 0, n = arr.length;
        while (i < n) {
          chunks.push(arr.slice(i, i += len));
        }
        return chunks;
    } */

    exportTemplate(event) {
        const XLSX = window.XLSX;
        var wscols = [
            { width: 25, },  // first column
            { width: 25 },
            { width: 25 },
            { width: 80 },
            { width: 80 },
            { width: 80 }
          ];

        var ws = XLSX.utils.aoa_to_sheet([[]]);  //create a blank sheet
        XLSX.utils.sheet_add_aoa(ws, [
            ["Account Id", "Current Assigned Owner Id", "New Assigned Owner Id", "Assignment Reason", "Deal Owner Id", "Assigned To Id"]
        ], {origin: "A1"});   

        ws["!cols"] = wscols;

        var wb = XLSX.utils.book_new();

        /* Write Excel and Download */
        XLSX.utils.book_append_sheet(wb, ws, 'Account Template');
        XLSX.writeFile(wb, 'Update Account Template.csv', {cellStyles: true});

    }

}
reviewher commented 2 years ago

Is this the right file? The actual bytes:

$ xxd Update.Account.Template.7.csv 
00000000: 4163 636f 756e 7420 4964 2c43 7572 7265  Account Id,Curre
00000010: 6e74 2041 7373 6967 6e65 6420 4f77 6e65  nt Assigned Owne
00000020: 7220 4964 2c4e 6577 2041 7373 6967 6e65  r Id,New Assigne
00000030: 6420 4f77 6e65 7220 4964 2c41 7373 6967  d Owner Id,Assig
00000040: 6e6d 656e 7420 5265 6173 6f6e 2c44 6561  nment Reason,Dea
00000050: 6c20 4f77 6e65 7220 4964 2c41 7373 6967  l Owner Id,Assig
00000060: 6e65 6420 546f 2049 640d 0a30 3031 3132  ned To Id..00112
00000070: 3030 3030 3144 7968 6755 4141 522c 3030  00001DyhgUAAR,00
00000080: 3533 7530 3030 3030 334a 4b4d 6a41 414f  53u000003JKMjAAO
00000090: 2c30 3035 3132 3030 3030 3035 7459 4d5a  ,00512000005tYMZ
000000a0: 4141 322c 5465 7374 696e 672c 2c0d 0a    AA2,Testing,,..

All of the characters are printable. 0d is CR and 0a is LF in windows parlance.