Sheetjs: How to read excel template file and replace a text finded

[nvcken]

How can I implement this flow when got workbook read from excel file.
Next, find cell with text "" and modify it to become TextA, update to workbook
Write new excel file .

[notatestuser]

you read the docs

[SheetJSDev]

@nvcken It was discussed in a previous issue: https://github.com/SheetJS/js-xlsx/issues/82

Here is a sample that should find all cells with text "OLD" and replace it with "NEW" (I left some comments that should hopefully show how you do certain things like loop through all of the cells):

var oldtext = "OLD";
var newtext = "NEW";

var XLSX = require('xlsx'); // require the module

/* read the file */
var workbook = XLSX.readFile('original.xlsx'); // parse the file
var sheet = workbook.Sheets[workbook.SheetNames[0]]; // get the first worksheet

/* loop through every cell manually */
var range = XLSX.utils.decode_range(sheet['!ref']); // get the range
for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    /* find the cell object */
    var cellref = XLSX.utils.encode_cell({c:C, r:R}); // construct A1 reference for cell
    if(!sheet[cellref]) continue; // if cell doesn't exist, move on
    var cell = sheet[cellref];

    /* if the cell is a text cell with the old string, change it */
    if(!(cell.t == 's' || cell.t == 'str')) continue; // skip if cell is not text
    if(cell.v === oldtext) cell.v = newtext; // change the cell value
  }
}
XLSX.writeFile(workbook, 'newfile.xlsx');

[nvcken]

@SheetJSDev
It's great.
Thanks so much

[abelmagana88920]

if i read an excel file with format(eg: color, border), the outfile displays a plain with data without styles

[kaizenjegan]

@abelmagana88920 did you ever get an answer for this? Im experiencing the same thing

[abelmagana88920]

no, I haven't receive any response from them. I'm currently using xlsx-populate which can inherit the styles of the template but has a limited features. It cannot style programatically but can populate data.

[reviewher]

@abelmagana88920 @TheJegan if you could share a sample template, it would help in terms of understanding what features would need to be read and written from the files.

There are a few libraries that specialize in the particular use case of reading a file and performing a find/replace. Full disclosure: I have not used either module in a serious way and cannot vouch for their applicability and fitness:

• xlsx-populate gives more direct cell-level access to the workbook.
• xlsx-template lets you make a template and populate it with data

They do the obvious thing: unzip the xlsx file, search cells until it finds the placeholder token, manually replace the placeholder string with the substitution, then save the xlsx file. This approach generally preserves the rest of the original file, but is restricted to XLSX templates.

js-xlsx reads the entire file and produces a JS representation of the data. Partially due to the general approach and partially due to the full spectrum of formats supported, preserving the original file features is limited to features that are currently parsed and features that are currently written.

The approach in https://github.com/SheetJS/js-xlsx/issues/121#issuecomment-56834484 is the generally recommended way of manipulating the JS object. As more features are implemented, the output workbook will slowly converge to what you expect. In the meanwhile, if features aren't represented, we recommend using another library like the aforementioned examples.

ping @dtjohnson for posterity sake can you provide some sample code using xlsx-populate for the case of a single find/replace?

ping @optilude for posterity sake can you provide some sample code using xlsx-template for the case of a single find/replace?

[dtjohnson]

@reviewher, you are spot on about how xlsx-populate works. The intention is not to support the full XLSX spec nor a variety or formats. The idea is to modify the existing workbook to set values without impacting the styling or anything else in the workbook.

There is no support in xlsx-populate for manipulating styles at this point. It is on the roadmap though if I can ever get time to work on it. There is also no support for searching for a particular value. It should be possible but it is tricky with shared strings and formulas.

People use xlsx-populate to manipulate cell values by cell address or name:

var Workbook = require('xlsx-populate');
var workbook = Workbook.fromFileSync("./workbook.xlsx");
var sheet = workbook.getSheet("Sheet1");

sheet.getCell("A1").setValue("hi");
workbook.getNamedCell("foo").setValue("ho");

workbook.toFileSync("./out.xlsx");

[kaizenjegan]

@abelmagana88920 xlsx-populate might be what I need then. I only need to manipulate cell value while preserving the style. Thanks will look into that.

[reviewher]

@dtjohnson thanks for chiming in! Can you give a complete example function worksheet_find_and_replace using xlsx-populate? Following the example from https://github.com/SheetJS/js-xlsx/issues/121#issuecomment-56834484 , I'd like to write:

var Workbook = require('xlsx-populate');
var workbook = Workbook.fromFileSync("./workbook.xlsx");
var sheet = workbook.getSheet("Sheet1");

worksheet_find_and_replace(sheet, "OLD", "NEW"); // this will change all of the text cells with value "OLD"

workbook.toFileSync("./out.xlsx");

[abelmagana88920]

The features og xlsx-populate can inherit the the style of excel template but It cannot set the styles (borders, color) programatically.
The features of js-xlsx can do the styles programatically but I don't know if this package can used an excel template to preserved the style.

It is very nice if the both features combine.

@TheJegan your welcome. I'm glad that I share it with you.
@dtjohnson xlsx-populate is good. Tnx for this package
@reviewher Tnx for helping for giving us a reference

[kaizenjegan]

@dtjohnson I know this isnt the thread for this, but how do u read a cell value using xlsx-populate.

sheet.getCell("A1") just returns a giant object

[dtjohnson]

I responded to the ticket for this here:
https://github.com/dtjohnson/xlsx-populate/issues/15

But I wanted to share the same info here. Currently xlsx-populate does not support reading the value of a cell. It was designed for population. However, I've started work on the next version of the API which would allow for getting values (as well as a number of other features like styling and search/replace). It'll be a bit before I get it released though :(.

[dtjohnson]

I just released v1.0.0 of xlsx-populate. It now supports reading cell values, styles, etc. It also supports find/replace.

@reviewher, @abelmagana88920, here is an example of find and replace:

const XlsxPopulate = require('xlsx-populate');
XlsxPopulate.fromFileAsync("./workbook.xlsx")
    .then(workbook => {
        // Find and replace in the entire workbook
        workbook.find("OLD", "NEW");

        // Find and replace only in a particular sheet
        workbook.sheet("Sheet1").find("OLD", "NEW");

        // Find and replace only in a particular cell
        workbook.sheet(0).cell("A5").find("OLD", "NEW");

        // Write to file
        return workbook.toFileAsync("./out.xlsx");
    });

The find method also supports RegExp patterns and function replacements just like String.prototype.replace.

[abelmagana88920]

@dtjohnson, Thanks for updating your package xlsx-populate. I will test it. I'm sure it helps a lot. Thanks again

[vedant05]

@dtjohnson,Hii can you look into this method.I have to call this method from another page and i have to return the contents of execute array from this method
exports.RetriveValue= function( filepath){

    const XlsxPopulate = require('xlsx-populate');
    var workbook = XlsxPopulate.fromFileAsync(filepath);
    //var sheet = workbook.getSheet("Sheet1");
           const rowValue =[];
           var execute =[];
           var xlRange = workbook.sheet("Sheet1").usedRange();
           var rowCount =  workbook.sheet("Sheet1").usedRange(). _numRows;
           var colCount =  workbook.sheet("Sheet1").usedRange(). _numColumns;

         for (var i = 2; i <= rowCount; i++)
            {
             rowValue.length= 0;

                for (var j = 1; j <= colCount; j++)
                {
                try
                    {
                      rowValue.push(workbook.sheet("Sheet1").cell(i, j).value().toString());
                    }
                    catch ( ex)
                    {
                        break;
                    }
                }
                if (rowValue[4] == "Yes") 
                {
                    var testexec = rowValue[2].toString();
                      execute.push(testexec);

                }

             }
         console.log( execute);
        return execute;

}

Error i m geting:TypeError: workbook.sheet is not a function

[dtjohnson]

This isn't the right repo for questions about xlsx-populate, but the issue is that fromFileAsync returns a promise.