0

I am working on an Excel Addin and I need to validate user input to check if it is a valid cell reference or not. I tried to find it in documentation but I have not found anything yet.

Following are simple strings formats I know that address can be, there may be other formats also.

A1 $A1 $A$1 sheet1!A1 'sheet 1'!A1 etc. So this list can grow as excel updates and I don't want to add checks for each format separately.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Kashif
  • 85
  • 8

1 Answers1

1

I've never used exceljs. Like you, reading the docs on GitHub, I don't see anything that provides a validity test of a cell address.

Rolling your own parser might be an okay idea. I think you've probably enumerated the major cases in the OP, the (probably) complete grammar would be:

// where [] means optional
cell_address = [sheet_name!][$]col_name[$]row_number
range = cell_address:cell_address

A quicker-to-try-out idea is to use try / catch with getCell or one of its ilk. Something like...

function isValidAddress(worksheet, string) {
  try {
    worksheet.getCell(string);
    return true;
  } catch (error) {
    return false;
  }
}

If the library has its own unexposed parser (you can leaf thru source and maybe get the best answer that way, via copy paste), it will probably throw on a bad address, and this will work. This will be a little more execution than required, but if you're not doing zillions of these in a tight loop, it might be good enough to use.

danh
  • 62,181
  • 10
  • 95
  • 136