Can we read data Column Wise in XLSX File using SheetJs? #1729

Closed
opened 2020-01-31 11:15:50 +00:00 by danifaiz · 2 comments
danifaiz commented 2020-01-31 11:15:50 +00:00 (Migrated from github.com)
No description provided.
SheetJSDev commented 2020-02-05 20:25:04 +00:00 (Migrated from github.com)

Data is stored row-wise in most formats (Some of the older formats are column-wise but all contemporary formats are row-wise).

You can convert a worksheet to an array of arrays using sheet_to_json with the option header:1 and transpose it for column-wise access. See https://github.com/SheetJS/sheetjs/issues/1085#issuecomment-384472147 for an example:

var tpose = [];
for(var i = 0; i < data.length; ++i) {
  for(var j = 0; j < data[i].length; ++j) {
    if(!tpose[j]) tpose[j] = [];
    tpose[j][i] = data[i][j];
  }
}
Data is stored row-wise in most formats (Some of the older formats are column-wise but all contemporary formats are row-wise). You can convert a worksheet to an array of arrays using `sheet_to_json` with the option `header:1` and transpose it for column-wise access. See https://github.com/SheetJS/sheetjs/issues/1085#issuecomment-384472147 for an example: ```js var tpose = []; for(var i = 0; i < data.length; ++i) { for(var j = 0; j < data[i].length; ++j) { if(!tpose[j]) tpose[j] = []; tpose[j][i] = data[i][j]; } } ```
danifaiz commented 2020-02-06 04:15:31 +00:00 (Migrated from github.com)

Thanks a lot for the response!! , transpose solution works great! Earlier I tried iterating through column first and then in the inner loop for rows, so it also worked out.

function readColumnWiseData(range,ws){
        var columnWiseData = [];
        for(var C = range.s.c; C <= range.e.c; ++C) {
        columnWiseData[C] = [];
        for(var R = range.s.r; R <= range.e.r; ++R) {

            var cellref = XLSX.utils.encode_cell({c:C, r:R}); // construct A1 reference for cell
            if(!ws[cellref]) continue; // if cell doesn't exist, move on
            var cell = ws[cellref];
            columnWiseData[C].push(cell.v);

        };
    }
    return columnWiseData;
}

const wb = XLSX.read(bstr, {type:'binary'});
const wsname = wb.SheetNames[0];
const ws = wb.Sheets[wsname];
var range = XLSX.utils.decode_range(ws['!ref']); // get the range
var columnWiseData = readColumnWiseData(range,ws);
Thanks a lot for the response!! , transpose solution works great! Earlier I tried iterating through column first and then in the inner loop for rows, so it also worked out. ``` function readColumnWiseData(range,ws){ var columnWiseData = []; for(var C = range.s.c; C <= range.e.c; ++C) { columnWiseData[C] = []; for(var R = range.s.r; R <= range.e.r; ++R) { var cellref = XLSX.utils.encode_cell({c:C, r:R}); // construct A1 reference for cell if(!ws[cellref]) continue; // if cell doesn't exist, move on var cell = ws[cellref]; columnWiseData[C].push(cell.v); }; } return columnWiseData; } const wb = XLSX.read(bstr, {type:'binary'}); const wsname = wb.SheetNames[0]; const ws = wb.Sheets[wsname]; var range = XLSX.utils.decode_range(ws['!ref']); // get the range var columnWiseData = readColumnWiseData(range,ws);
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#1729
No description provided.