Export Named Range to CSV #708

New Issue

2017-06-28T08:24:34Z

wongtatseng commented

2017-06-28 08:24:34 +00:00

(Migrated from github.com)

Hi there,

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

Thanks

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

harmon25 commented

2017-08-01 17:56:47 +00:00

(Migrated from github.com)

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)
})

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. ```js 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) }) ```

❤️ 1 🎉 1

reviewher commented

2017-08-01 18:06:11 +00:00

(Migrated from github.com)

@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 commented

2017-08-01 19:09:46 +00:00

(Migrated from github.com)

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...

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 commented

2022-09-26 10:38:41 +00:00

(Migrated from github.com)

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");

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");

Sign in to join this conversation.