Export Named Range to CSV

[wongtatseng]

Hi there,

I see there are functions to convert sheet to csv, but how do I convert a named range to csv?

Thanks

[harmon25]

Here is an example function returning the necessary information from a named range to iterate that range of a workbook.

Their may be a more elegant way, but works pretty well for me.

const XLSX = require("xlsx");
const find = require("lodash.find"); 

/**
 * @param {Workbook} workbook 
 * @param {String} defined_name 
 * @return {?object}
 */
function named_range(workbook, defined_name) {
  let range = find(workbook.Workbook.Names, r => r.Name === defined_name);
  if (range) {
    // "SheetName!$A$2:$B$5" = {sheet: "SheetName", range: "A2:B5"}
    let split_range = range.Ref.split("!");
    return { sheet: split_range[0], range: split_range[1].replace(/\$/g, "") };
  } else {
    return null;
  }
}

// example usage
let workbook = XLSX.readFile("some_xlsx_file");
let some_named_range = named_range(wb, "defined_name");

let worksheet = workbook .Sheets[some_named_range.sheet];
let data = XLSX.utils.sheet_to_json(worksheet , { range: some_named_range.range});

data.forEach((row)=>{
 console.log(row)
})

[reviewher]

@harmon25 That looks really nice! The 3-d reference parsing (where you split on the sheet name) probably should be part of the decode_range utility function. There are a few small corner cases like worksheets with spaces in the name (so you'd have to check if the first character is a double-quote and parse accordingly)

[harmon25]

Thanks! @reviewher - tidied it up a bit more.

Yea some corner cases will break this for sure - also if the named range is not scoped to the workbook and instead a sheet - pretty sure the range.Ref.split("!") will fail...

[heshmmorcy]

let workbook = XLSX.readFile("some_xlsx_file");
let some_named_range = named_range(wb, "defined_name");

mast be change to

let workbook = XLSX.readFile("some_xlsx_file");
let some_named_range = named_range(workbook , "defined_name");