Problem with Excel files, must recover

[MusicDev33]

This is an ExcelJS problem, but it's still a problem so I'll keep the issue open. Basically, if you read and write to an Excel file, it gets corrupted somehow, here's the issue: https://github.com/exceljs/exceljs/issues/106

[cgonzalezp91]

I am trying to change the bgColor form an existing file, first i read the file, then i change the bgColor of all the rows then i try to write a new file but i get a similar error of Excel, in this case says

Repaired Records: Format from /xl/styles.xml part (Styles)

So right now we can't read a file and use the information in this file to create a new file?

[MusicDev33]

You can, and if you let Windows repair it, everything works as it should. I just don't know how to get rid of the error in the first place. But other than that showing up, you should be able to use the information to write a new file

[cgonzalezp91]

Sorry i forgot to say that the problem is that the bgColor is no being apply to the cells, I was thinking that this error was because this message, but if everything works, even ,apply some style in that case my error is in the code.

Can you make a quick review and tell me if you see some error?

First i read the file, i check the column 30 to check some color values, after i have the array of all the values in this column i iterate over all the rows and if in the array of my second row i have 'yellow', i assign all the row with the color FF0000FF like the example in npm.

const editExcel = (file) => {
    return new Promise((response,reject) => {
    var workbook = new Excel.Workbook();
    workbook.xlsx.readFile(file)
    .then(function() {          
        const worksheet = workbook.getWorksheet(1);
        const arrValues = worksheet.getColumn(30).values;
        let count = 1
        worksheet.eachRow(function(row, rowNumber) {
            console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
            if (rowNumber==1)
                console.log('Frist Row');
            else{
                switch(arrValues[rowNumber]){
                     case 'yellow':
                            row.fill= {
                                bgColor:{argb:'FF0000FF'}
                                }
                            row.commit();
                    break;                                
                }
            }
        });
        var workbooknew = new Excel.Workbook();
        workbooknew.worksheet = workbook.getWorksheet(1);
        workbook.xlsx.writeFile(`${file}-1.xlsx`)
            .then(()=>{
                console.log(`File ${file}-1 created`)
                response()
            })    
    }). catch((error) =>{
        reject(error)
    })
})

}

Maybe i need to iterate over all the cells

[MusicDev33]

So I don't have my dev machine on me at the moment, but this comment might help: https://github.com/exceljs/exceljs/issues/596#issuecomment-434937770 Also, have you been able to color it without any logic around it?

const editExcel = (file) => {
    return new Promise((response,reject) => {
    const workbook = new Excel.Workbook();
    workbook.xlsx.readFile(file)
    .then(() => {          
        const worksheet = workbook.getWorksheet(1);
        const arrValues = worksheet.getColumn(30).values;
        let count = 1
        worksheet.eachRow((row, rowNumber) => {
            console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
            if (rowNumber === 1)
                console.log('First Row');
            else {

                // Instead of this if statement/switch, try to just color that row white 
                // (or red or something easy). See if you can get it working alone

                if (arrValues[rowNumber] === 'yellow') {
                    row.fill = { bgColor: {argb:'FF0000FF'} }
                    row.commit();
                }
            }
        });
        const workbooknew = new Excel.Workbook();
        workbooknew.worksheet = workbook.getWorksheet(1);
        workbook.xlsx.writeFile(`${file}-1.xlsx`)
            .then(() => {
                console.log(`File ${file}-1 created`)
                response()
            })    
    }).catch((error) =>{
        reject(error)
    })
})

}

Also, you can do JavaScript code highlighting by using

```javascript code here ```

[cgonzalezp91]

Thank you for the tip 🚀 I tried what you suggest but is still not working 😢

This is the way to give the bgColor to the rows?

const editExcel = (file) => {
    return new Promise((response,reject) => {
    var workbook = new Excel.Workbook();
    workbook.xlsx.readFile(file)
    .then(function() {
    // use workbook        
        const worksheet = workbook.getWorksheet(1);
        const arrValues = worksheet.getColumn(30).values;
        let count = 1
        worksheet.eachRow(function(row, rowNumber) {
            console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
            if (rowNumber==1)
                console.log('Frist Row');
            else{
                row.fill= {
                    bgColor:{argb: 'FF0000'} // Is this the way to do it? Is Red Color in Hex
                };
                row.commit();

            }
        });
        var workbooknew = new Excel.Workbook();
        workbooknew.worksheet = workbook.getWorksheet(1);

        workbook.xlsx.writeFile(`${file}-1.xlsx`)
            .then(()=>{
                console.log(`File ${file}-1 created`)
                response()
            })    
    }). catch((error) =>{
        reject(error)
    })
})

}

Thank you very much for your help and quick response

[MusicDev33]

Yep, that should be red in hex. Honestly, I don't see anything wrong, but then again, I don't have my dev machine with my ExCelery environment on it. Try asking on Stack or opening an issue on the ExcelJS GitHub page. I've only used the library, I can't say I'm very good with it, as I only used a certain portion of it

Edit: I opened my phone and saw that this field was still full, I definitely meant to send it, sorry about that

[cgonzalezp91]

Don't worry and thanks for your time and the help, i really appreciate, let me check a little bit more before opening an issue

[ReneRam]

I have a similar problem and I'm sure it's a question of internationalization, localization of the platform. In fact I have "template" files on the server that are read with npoi; in the file there is a single precompiled sheet that is cloned in a new workbook and filled with data coming from elaborations or DB2/SQL Server databases. The process is fully functional and when I download the file resulting from the server to my client I have no problem whatsoever. When I publish the project on a webserver of the public administration to the download I get this error. Searching on Google I found that the problem is in the formatting of the date: the time separator I get from the download server uses the dot "." instead of the colon ":" that's used in Italy (renaming the file aszip and exploring the "core.xml" you see the difference ). I tried to insert a culture change in the Global.asax.cs and it works. I did tests with a virtual server in English and it still works. The problem occurs again when I deploy to the public administration server. Continuing to do research and tests, I noticed that it is enough to change a parameter of the file properties from the operating system, even in the wrong way, and when the Excel file is opened it corrects the wrong parameter and opens without errors or messages. it's a pretty dirty workaround and not an elegant solution for a production release. Someone has an idea what to change to solve. In fact my code works until it ends up on an external server. This is an excerpt from my code ...

            using (MemoryStream tmpStream = new MemoryStream())
            {
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.ContentEncoding = Encoding.GetEncoding("Windows-1252");//= Encoding.UTF8;//(IBM280)
                Response.AddHeader("content-disposition", "attachment;filename=" + fileName);

                workbook.Write(tmpStream);

                Response.BinaryWrite(tmpStream.ToArray());
                Response.End();

[cgonzalezp91]

Well for me after researching a little bit more reserch in the documentation of ExcelJS I noticed that I have and error in the code. The way that we need to change the BG color was not the correct in my previous code that i posted here. The correct way is the next one.

const editExcel = (file) => { //Receive the file url
    return new Promise((response,reject) => {
    var workbook = new Excel.Workbook();
    workbook.xlsx.readFile(file) //Read the file
    .then(function() {
        const worksheet = workbook.getWorksheet(1);
        const arrValues = worksheet.getColumn(30).values; //Get some values from some column
        let count = 1
        worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {   // Iterate through all the rows
            if (rowNumber>1){ //Skip first value
                row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
                    switch (arrValues[count]){
                        case 'green':
                            row.getCell(colNumber).fill = { //Use function getCell form ExcelJS
                                type: "pattern",
                                pattern: "solid",
                                fgColor: {
                                    argb: "90EE90"
                                }
                            }
                        break
                  ......... // All the switch case statements
                  .........    
                }
                });
            }
            count++
            row.commit(); // Commit the changes for all the rows
        });
    }).then(function() {
        return workbook.xlsx.writeFile(file) //Rewrite in the same file
    }) .then(function() {
        response() //If everything's goes okay send response method to an async function
    }).catch(error => {
        console.log(error)
        reject()
    })
})

}

So for me was an incorrect use of the ExcelJS library. In your case if you can solve the problem with internationalization make sure to use the same as the production server (in your testing environment) or try to make some test in another servers (free trial or something), just to be sure is not an special configuration in your server.