node-xlrd

node.js's module to extract data from Microsoft Excel™ File(.xls)

node-xlrd

node.js's module to extract data from Microsoft Excel™ File(.xls)

  • supported file format : Excel 2 ~ 2003 File(.xls), not .xlsx file
  • only cell data without a formula, format.
  • used lowerCamelCase for inner variables, properties and function names.
    moved to javascript naming conventions from python.
  • added 'toCountryName' function
  • added 'lastUser' property
  • fixed bugs
  • fixed bugs
  • added 'onDemand' option
  • add api workbook.cleanUp()
  • fixed bugs
  • changed api
BeforeAfter
workbook.nsheetsworkbook.sheet.count
workbook.codepageworkbook.codePage
workbook.sheets() functionworkbook.sheets property
workbook.sheetByIndex()workbook.sheet.byIndex()
workbook.sheetByName()workbook.sheet.byName()
workbook.sheetNames() functionworkbook.sheetNames property
workbook.sheetListremoved, recommand workbook.sheetNames property
workbook.getSheets()removed, inner function
workbook.fakeGlobalsGetSheet()removed, inner function
workbook.getBOF()removed, inner function
workbook.datemodeworkbook.dateMode
sheet.ncolssheet.column.count
sheet.nrowssheet.row.count
sheet.numbersheet.index
sheet.cellValue()sheet.cell.getRaw()
sheet.cellObj()removed
sheet.cellType()sheet.cell.getType()
sheet.cellXFIndex()sheet.cell.getXFIndex()
sheet.row()sheet.row.getValues()
sheet.rowLength()sheet.row.getCount()
sheet.rowTypes()sheet.row.getTypes()
sheet.rowValues()sheet.row.getValues()
  • fixed bugs
  • fixed bugs
  • first commit

This description is based on python xlrd function's comments and document.
note : It is possible to use not yet announced api, but it may be modified in future versions.

open a workbook.

  • file : file path
  • options :
    • onDemand : option to load worksheets on demand.
      it allows saving memory and time by loading only those selected sheets , and releasing sheets when no longer required.
      • onDemand = false (default),
        xlrd.open() loads global data and all sheets, releases resources no longer required
      • onDemand = true (only BIFF version >= 5.0)
        • xlrd.open() loads global data and returns without releasing resources. At this stage, the only information available about sheets is workbook.sheet.count and workbook.sheet.names.
        • workbook.sheet.byName() and workbook.sheet.byIndex() will load or reload the requested sheet if it is not already loaded or unloaded .
        • workbook.sheets will load all/any unloaded sheets.
        • The caller may save memory by calling workbook.sheet.unload() when finished with the sheet. This applies irrespective of the state of onDemand.
        • workbook.sheet.loaded() checks whether a sheet is loaded or not.
        • workbook.cleanUp() should be called at end of node-xlrd.open() callback.
    • callback : function(err, workbook)

convert column name to zero-based index.

return the corresponding string for BIFF version number

0:  "(not BIFF)"
20: "2.0"
21: "2.1"
30: "3"
40: "4S"
45: "4W"
50: "5"
70: "7"
80: "8"
85: "8X"

returns the corresponding country name for a country code
refer workbook.countries property

Version of BIFF (Binary Interchange File Format) used to create the file.
Latest is 8.0 (represented here as 80), introduced with Excel 97.
Earliest supported by this module: 2.0 (represented as 20).

the name of the user who last created, opened, or modified the file.

An integer denoting the character set used for strings in this file.
For BIFF 8 and later, this will be 1200, meaning Unicode; more precisely, UTF16LE.

The encoding that was derived from the codepage.

Array containing the (telephone system) country code for:

[0]: the user-interface setting when the file was created.  
[1]: the regional settings.  

Example:

(1, 61) meaning (USA, Australia)  
(82, 82) meaning (South Korea, South Korea)  

This information may give a clue to the correct encoding for an unknown codepage.
For a long list of observed values, refer to the OpenOffice.org documentation for the COUNTRY record or List of country calling codes

refer node-xlrd.common.toCountryName(countryCode)

return : A list of all sheets in the book.
All sheets not already loaded will be loaded.

all resources( file descriptor, large caches) released.
Once cleanUp() called, no more reload or parse sheets.

  • onDemand == true,
    Call this function ,if don't neet to load or parse sheets.

  • onDemand == false (default),
    It is possible to omit workbook.cleanUp(), because xlrd.open() implicit calls it after the callback finished.

  • if any error raised in xlrd.open callback, workbook.cleanUp() implicit call by xlrd.open()

Zero-based index of sheet in workbook.

return A list of the names of all the worksheets in the workbook file.
This information is available even when no sheets have yet been loaded.

sheetIndex : Sheet index
return : An object of the sheet class

sheetName : Name of sheet required
return : An object of the sheet class

sheetId : sheet name or index
return : true if sheet is loaded, false otherwise

sheetId : sheet name or index to be unloaded.

Name of sheet

Index of sheet

return : javascript value ( string, number, Date) converting from raw of the cell in the given row and column.

getValue() is equal to .cell()

return : Raw value of the cell in the given row and column.

return : Type of the cell in the given row and column.

do not use, because a cell style parsing not implemented yet.
return : XF index of the cell in the given row and column.

return : Number of rows in sheet.

return : a sequence of the cell values in the given row.

return : a slice of the types of the cells in the given row.

return : a slice of the values of the cells in the given row.

Nominal number of columns in sheet. It is 1 + the maximum column index found, ignoring trailing empty cells.

var xl = require('node-xlrd');
 
xl.open('./test.xls', function(err,bk){
    if(err) {console.log(err.name, err.message); return;}
    
    var shtCount = bk.sheet.count;
    for(var sIdx = 0; sIdx < shtCount; sIdx++ ){
        console.log('sheet "%d" ', sIdx);
        console.log('  check loaded : %s', bk.sheet.loaded(sIdx) );
        var sht = bk.sheets[sIdx],
            rCount = sht.row.count,
            cCount = sht.column.count;
        console.log('  name = %s; index = %d; rowCount = %d; columnCount = %d', sht.name, sIdx, rCount, cCount);
        for(var rIdx = 0; rIdx < rCount; rIdx++){
            for(var cIdx = 0; cIdx < cCount; cIdx++){
                try{
                    console.log('  cell : row = %d, col = %d, value = "%s"', rIdx, cIdx, sht.cell(rIdx,cIdx));
                }catch(e){
                    console.log(e.message);
                }
            }
        }
        
        //save memory 
        //console.log('  try unloading : index %d', sIdx ); 
        //if(bk.sheet.loaded(sIdx)) 
        //  bk.sheet.unload(sIdx); 
        //console.log('  check loaded : %s', bk.sheet.loaded(sIdx) ); 
    }
    // if onDemand == false, allow function 'workbook.cleanUp()' to be omitted, 
    // because it is called by caller 'node-xlrd.open()' after callback finished. 
    //bk.cleanUp(); 
});

BSD