Home

Index

How to use the utils function from xlsx

Find comprehensive JavaScript xlsx.utils code examples handpicked from public code repositorys.

xlsx.utils is a JavaScript object in the SheetJS library that provides various utility functions for parsing, formatting, and manipulating spreadsheet data in Excel and other formats.

node-xlsx.js

17
18
19
20
21
22
23
24
25
26
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: data[R][C] };
if(cell.v === null) continue;
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {

270

+ 5 other calls in file

json2xlsx.js

GitHub: digplan/json2xlsx

118
119
120
121
122
123
124
125
126
if (range.e.c < C) range.e.c = C;
var cell = {
  v: data[R][C]
};
if (cell.v == null) continue;
var cell_ref = xlsx.utils.encode_cell({
  c: C,
  r: R
});

+ 3 other calls in file

How does xlsx.utils work?

xlsx.utils works by providing a set of utility functions for parsing, formatting, and manipulating spreadsheet data in Excel and other formats. When included as part of the SheetJS library, xlsx.utils can be used to perform various tasks related to spreadsheet data, such as converting between different data formats, validating data types and formats, and extracting or manipulating specific cells or ranges of cells. Some of the key utility functions provided by xlsx.utils include: sheet_to_csv: converts a worksheet object to a CSV-formatted string. aoa_to_sheet: creates a worksheet object from an array of arrays (AOA). sheet_to_formulae: converts a worksheet object to an array of formula strings. format_cell: formats a cell value and style based on a number format string. encode_col: converts a column number to its Excel column name (e.g., 1 to "A"). decode_col: converts an Excel column name to its column number (e.g., "A" to 1). These functions can be called as part of a larger JavaScript program or application, which may use them to perform various data-related tasks, such as importing or exporting data from a spreadsheet, validating user input, or generating reports or charts. Note that xlsx.utils is part of the SheetJS library, which provides a set of tools and utilities for working with spreadsheet data in JavaScript, and supports various Excel and other file formats, such as XLSX, CSV, and JSON. The library also provides various classes and functions for manipulating cell data, formatting styles, and workbook structures, as well as reading and writing data to and from files or streams.

index.js

GitHub: datopian/data-api

153
154
155
156
157
158
159
160
161
162
const gqlRes = await request(`${process.env.HASURA_URL}/v1/graphql`, query)

// // capture graphql response
if (ext != 'json') {
  // any spreadsheet supported by [js-xlsx](https://github.com/SheetJS/sheetjs)
  let wb = XLSX.utils.book_new()
  // TODO control the column/field order:
  // https://stackoverflow.com/questions/56854160/sort-and-filter-columns-with-xlsx-js-after-json-to-sheet
  // https://github.com/SheetJS/sheetjs/issues/738
  // it needs to receive the header parameter with the desired column order

+ 5 other calls in file

excel.js

GitHub: ucd-cws/calvin-network-app

92
93
94
95
96
97
98
99
100
101
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: data[R][C] };
if(cell.v == null) continue;
var cell_ref = xlsx.utils.encode_cell({c:C,r:R});

if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {

+ 3 other calls in file

Ai Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
const XLSX = require("xlsx");

const data = [
  ["Name", "Age", "Email"],
  ["John", 25, "john@example.com"],
  ["Jane", 30, "jane@example.com"],
  ["Bob", 35, "bob@example.com"],
];

const worksheet = XLSX.utils.aoa_to_sheet(data);

const csv = XLSX.utils.sheet_to_csv(worksheet);

console.log(csv);
// Output:
// "Name,Age,Email\nJohn,25,john@example.com\nJane,30,jane@example.com\nBob,35,bob@example.com\n"

In this example, we first import the SheetJS library, and define a 2D array data containing some sample data for a spreadsheet, with headers and rows of data. We then use the XLSX.utils.aoa_to_sheet function to create a worksheet object from the array data. The resulting worksheet object represents the data as a table of cells, with each row and column corresponding to an array element. We then use the XLSX.utils.sheet_to_csv function to convert the worksheet object to a CSV-formatted string. The resulting CSV string contains the data in a comma-separated format, with each row on a new line. The resulting CSV string is then printed to the console. Note that this is just a simple example, and xlsx.utils can be used to perform various other tasks, such as converting data between different formats, manipulating cells or ranges, or formatting cell values and styles, based on the needs of the application or program.

xls-writer.js

GitHub: HugoCapocci/xslx-extended

45
46
47
48
49
50
51
52
53
54
  let asc1, end1;
  const row = matrixValues[r];
  for (c = 0, end1 = row.length - 1, asc1 = 0 <= end1; asc1 ? c <= end1 : c >= end1; asc1 ? c++ : c--) {
    checkRange(range, r, c);
    const cell = {v: (row[c] != null) ? row[c] : ''};
    const cellRef = xlsx.utils.encode_cell({c, r});
    checkCellType(cell);
    sheet[cellRef] = cell;
  }
}

+ 3 other calls in file

index.js

GitHub: flomair/gulp-excelsheets2json

9
10
11
12
13
14
15
16
17
18
19
 */


var toJson = function (workbook) {
      var result = {};
      workbook.SheetNames.forEach(function(sheetName) {
        var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName],{"header":0});
         if(roa.length > 0){
            var re = {"id":sheetName};
            for (var i = 0; i < roa.length; i++) {
                try {

FileExport.js

GitHub: ShreoshreeSingha/AnticounterFeit-Host-Repos

34
35
36
37
38
39
40
41
42
43
  flatten(item, flatRow);
  return flatRow;
}));
const workbook = XLSX.utils.book_new();
//const sheetrr= flatten(worksheet, flatRow);
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
// XLSX.writeFileXLSX(workbook,"MYTABLE.xlsx");
const fileBuffer = XLSX.write(workbook, {
  bookType: "xlsx",
  type: "array"

+ 5 other calls in file

FileExport.js

GitHub: ShreoshreeSingha/AnticounterFeit-Host-Repos

35
36
37
38
39
40
41
42
43
44

console.log("csvData ::" + csvData);

function exportToExcel() {
  console.log("exportExcel:" + data);
  const worksheet = XLSX.utils.json_to_sheet(
    data.map((item) => {
      const flatRow = {};
      flatten(item, flatRow);
      return flatRow;

+ 5 other calls in file

export.js

GitHub: eksponent/metadatabase

120
121
122
123
124
125
126
127
128
129
            } else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

function datenum(v, date1904) {

+ 3 other calls in file

rsdController.js

GitHub: Rodrigonesko/api-backend-lpl

485
486
487
488
489
490
491
492
493
494

const firstSheetName = workbook.SheetNames[0]

const worksheet = workbook.Sheets[firstSheetName]

let result = xlsx.utils.sheet_to_json(worksheet)

result.forEach((e, key) => {

    let conc = `${e[' Valor Apresentado'].replace('R$ ', '').replace('.', '').replace(',', '.')}`

+ 8 other calls in file

route.js

GitHub: yunisuthn/Project-Tools-Solumada

6386
6387
6388
6389
6390
6391
6392
6393
6394
6395

    //return data

    return Object.keys(excelData.Sheets).map(name => ({
        name,
        data: XLSX.utils.sheet_to_json(excelData.Sheets[name]),
    }));
};

var liste = []

+ 12 other calls in file

menu.js

GitHub: julioCROS/Ferias-CEFETMG

65
66
67
68
69
70
71
72
73
74
    console.log(" [MENU.JS] ======================================================================");
    console.log(" [MENU.JS] ========= Lendo o arquivo: " + fileName + " ===========");
    const excelData = xlsx.readFile(fileName);
    return Object.keys(excelData.Sheets).map(name => ({
        name,
        data: xlsx.utils.sheet_to_json(excelData.Sheets[name])
    }));
}

let excelFile;

reports.js

GitHub: pengpa0012/mern-product-management

8
9
10
11
12
13
14
15
16
17
// Find all documents in the YourModel collection
await Product.find({username}).then(data => {
 console.log(data)
   // Convert the Mongoose documents to plain JavaScript objects
   const jsonData = JSON.parse(JSON.stringify(data))
   const worksheet = XLSX.utils.json_to_sheet(jsonData);
   const workbook = XLSX.utils.book_new();
   XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
   const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });
   res.set('Content-Type', 'application/octet-stream');

+ 2 other calls in file

initbyfile.js

GitHub: bobkutter/signin-admin-console

101
102
103
104
105
106
107
108
109
110
  let workbook = XLSX.readFile(fileName);

  // EveryAction export file only has one sheet
  let sheetName = workbook.SheetNames[0];
  let xlsxSheet = workbook.Sheets[sheetName];
  sheet = XLSX.utils.sheet_to_json(xlsxSheet,{defval:""});
} catch (e) {
  Tables.showResults([e.message]);
  return false;
}

+ 3 other calls in file

students.js

GitHub: Rahul862725/Training-and-Placements

21
22
23
24
25
26
27
28
29
30
  ans += st;
  return ans;
}
const obj1 = {};
sheet_namelist.forEach((el) => {
  var clData = xlsx.utils.sheet_to_json(workbook.Sheets[sheet_namelist[x]]);
  for (let z in clData) {
    //  console.log(JSON.stringify(clData[z]))
    var keys = Object.keys(clData[z]);
    var values = Object.values(clData[z]);

+ 14 other calls in file

staffing-details.service.js

GitHub: tech-university-india/staffing-pipeline-backend

33
34
35
36
37
38
39
40
41
42
43
};
const parse_xlsx_sheets = fname => {
  const file = reader.readFile(fname);
  const sheets = file.SheetNames;


  var temp = reader.utils.sheet_to_json(file.Sheets[sheets[0]]);
  return Promise.all(
    temp.map(async res => {
      const user = JSON.parse(
        JSON.stringify(

sertificate.controller.js

GitHub: golibdev/certificate

108
109
110
111
112
113
114
115
116
117
const fileBase = await File.findById(file).lean();
const filePath = fileBase.file;
const pathName = fileBase.fileName.split('_')[0];
const workbook = xlsx.readFile(`public${filePath}`);
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
const rows = xlsx.utils.sheet_to_json(worksheet);

if (rows !== undefined) {
   for (let i = 0; i < rows.length; i++) {
      let item = rows[i];

+ 7 other calls in file

backend.js

GitHub: GrolauxDonatien/easyOMR

256
257
258
259
260
261
262
263
264
265
},
"file-users": async function (path) {
    let users = {};
    let workbook = XLSX.readFile(formatFilename(path));
    let sheet_name_list = workbook.SheetNames;
    let xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
    for (let i = 0; i < xlData.length; i++) {
        let keys = Object.keys(xlData[i]);
        let noma;
        let v = [];

+ 6 other calls in file

userServices.js

GitHub: yashodeepk/Sachivalayam-Backend

231
232
233
234
235
236
237
238
239
240
241
};


exports.excelToJson = async (filePath) => {
  const wb = XLSX.readFile(filePath);
  const ws = wb.Sheets["Sheet1"];
  const data = XLSX.utils.sheet_to_json(ws);
  // console.log(data);
  return data;
};

+ 2 other calls in file

api_gym_population_data.js

GitHub: Vaniljbulle/XpertGym

5
6
7
8
9
10
11
12
13
14
15
16
    res.json({currentPop: getGymPopData()});
});


// Get data from xlsx file
const workbook = xlsx.readFile('./server/database/occupation-data-xpert-gym.xlsx');
const data = xlsx.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);


function getGymPopData() {
    // Get current hour
    const date = new Date();

Other functions in xlsx

Sorted by popularity

xlsx.utils is the most popular function in xlsx (1624 examples)

Other popular packages:

Home

About