xlsx.njs 8.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252
  1. #!/usr/bin/env node
  2. /* xlsx.js (C) 2013-present SheetJS -- http://sheetjs.com */
  3. /* eslint-env node */
  4. /* vim: set ts=2 ft=javascript: */
  5. var n = "xlsx";
  6. var X = require('../');
  7. require('exit-on-epipe');
  8. var fs = require('fs'), program = require('commander');
  9. program
  10. .version(X.version)
  11. .usage('[options] <file> [sheetname]')
  12. .option('-f, --file <file>', 'use specified workbook')
  13. .option('-s, --sheet <sheet>', 'print specified sheet (default first sheet)')
  14. .option('-N, --sheet-index <idx>', 'use specified sheet index (0-based)')
  15. .option('-p, --password <pw>', 'if file is encrypted, try with specified pw')
  16. .option('-l, --list-sheets', 'list sheet names and exit')
  17. .option('-o, --output <file>', 'output to specified file')
  18. .option('-B, --xlsb', 'emit XLSB to <sheetname> or <file>.xlsb')
  19. .option('-M, --xlsm', 'emit XLSM to <sheetname> or <file>.xlsm')
  20. .option('-X, --xlsx', 'emit XLSX to <sheetname> or <file>.xlsx')
  21. .option('-I, --xlam', 'emit XLAM to <sheetname> or <file>.xlam')
  22. .option('-Y, --ods', 'emit ODS to <sheetname> or <file>.ods')
  23. .option('-8, --xls', 'emit XLS to <sheetname> or <file>.xls (BIFF8)')
  24. .option('-5, --biff5','emit XLS to <sheetname> or <file>.xls (BIFF5)')
  25. //.option('-4, --biff4','emit XLS to <sheetname> or <file>.xls (BIFF4)')
  26. //.option('-3, --biff3','emit XLS to <sheetname> or <file>.xls (BIFF3)')
  27. .option('-2, --biff2','emit XLS to <sheetname> or <file>.xls (BIFF2)')
  28. .option('-i, --xla', 'emit XLA to <sheetname> or <file>.xla')
  29. .option('-6, --xlml', 'emit SSML to <sheetname> or <file>.xls (2003 XML)')
  30. .option('-T, --fods', 'emit FODS to <sheetname> or <file>.fods (Flat ODS)')
  31. .option('-S, --formulae', 'emit list of values and formulae')
  32. .option('-j, --json', 'emit formatted JSON (all fields text)')
  33. .option('-J, --raw-js', 'emit raw JS object (raw numbers)')
  34. .option('-A, --arrays', 'emit rows as JS objects (raw numbers)')
  35. .option('-H, --html', 'emit HTML to <sheetname> or <file>.html')
  36. .option('-D, --dif', 'emit DIF to <sheetname> or <file>.dif (Lotus DIF)')
  37. .option('-U, --dbf', 'emit DBF to <sheetname> or <file>.dbf (MSVFP DBF)')
  38. .option('-K, --sylk', 'emit SYLK to <sheetname> or <file>.slk (Excel SYLK)')
  39. .option('-P, --prn', 'emit PRN to <sheetname> or <file>.prn (Lotus PRN)')
  40. .option('-E, --eth', 'emit ETH to <sheetname> or <file>.eth (Ethercalc)')
  41. .option('-t, --txt', 'emit TXT to <sheetname> or <file>.txt (UTF-8 TSV)')
  42. .option('-r, --rtf', 'emit RTF to <sheetname> or <file>.txt (Table RTF)')
  43. .option('-F, --field-sep <sep>', 'CSV field separator', ",")
  44. .option('-R, --row-sep <sep>', 'CSV row separator', "\n")
  45. .option('-n, --sheet-rows <num>', 'Number of rows to process (0=all rows)')
  46. .option('--codepage <cp>', 'default to specified codepage when ambiguous')
  47. .option('--req <module>', 'require module before processing')
  48. .option('--sst', 'generate shared string table for XLS* formats')
  49. .option('--compress', 'use compression when writing XLSX/M/B and ODS')
  50. .option('--read', 'read but do not generate output')
  51. .option('--book', 'for single-sheet formats, emit a file per worksheet')
  52. .option('--all', 'parse everything; write as much as possible')
  53. .option('--dev', 'development mode')
  54. .option('--sparse', 'sparse mode')
  55. .option('-q, --quiet', 'quiet mode');
  56. program.on('--help', function() {
  57. console.log(' Default output format is CSV');
  58. console.log(' Support email: dev@sheetjs.com');
  59. console.log(' Web Demo: http://oss.sheetjs.com/js-'+n+'/');
  60. });
  61. /* flag, bookType, default ext */
  62. var workbook_formats = [
  63. ['xlsx', 'xlsx', 'xlsx'],
  64. ['xlsm', 'xlsm', 'xlsm'],
  65. ['xlam', 'xlam', 'xlam'],
  66. ['xlsb', 'xlsb', 'xlsb'],
  67. ['xls', 'xls', 'xls'],
  68. ['xla', 'xla', 'xla'],
  69. ['biff5', 'biff5', 'xls'],
  70. ['ods', 'ods', 'ods'],
  71. ['fods', 'fods', 'fods']
  72. ];
  73. var wb_formats_2 = [
  74. ['xlml', 'xlml', 'xls']
  75. ];
  76. program.parse(process.argv);
  77. var filename = '', sheetname = '';
  78. if(program.args[0]) {
  79. filename = program.args[0];
  80. if(program.args[1]) sheetname = program.args[1];
  81. }
  82. if(program.sheet) sheetname = program.sheet;
  83. if(program.file) filename = program.file;
  84. if(!filename) {
  85. console.error(n + ": must specify a filename");
  86. process.exit(1);
  87. }
  88. if(!fs.existsSync(filename)) {
  89. console.error(n + ": " + filename + ": No such file or directory");
  90. process.exit(2);
  91. }
  92. if(program.req) program.req.split(",").forEach(function(r) {
  93. require((fs.existsSync(r) || fs.existsSync(r + '.js')) ? require('path').resolve(r) : r);
  94. });
  95. var opts = {}, wb/*:?Workbook*/;
  96. if(program.listSheets) opts.bookSheets = true;
  97. if(program.sheetRows) opts.sheetRows = program.sheetRows;
  98. if(program.password) opts.password = program.password;
  99. var seen = false;
  100. function wb_fmt() {
  101. seen = true;
  102. opts.cellFormula = true;
  103. opts.cellNF = true;
  104. if(program.output) sheetname = program.output;
  105. }
  106. function isfmt(m/*:string*/)/*:boolean*/ {
  107. if(!program.output) return false;
  108. var t = m.charAt(0) === "." ? m : "." + m;
  109. return program.output.slice(-t.length) === t;
  110. }
  111. workbook_formats.forEach(function(m) { if(program[m[0]] || isfmt(m[0])) { wb_fmt(); } });
  112. wb_formats_2.forEach(function(m) { if(program[m[0]] || isfmt(m[0])) { wb_fmt(); } });
  113. if(seen) {
  114. } else if(program.formulae) opts.cellFormula = true;
  115. else opts.cellFormula = false;
  116. var wopts = ({WTF:opts.WTF, bookSST:program.sst}/*:any*/);
  117. if(program.compress) wopts.compression = true;
  118. if(program.all) {
  119. opts.cellFormula = true;
  120. opts.bookVBA = true;
  121. opts.cellNF = true;
  122. opts.cellHTML = true;
  123. opts.cellStyles = true;
  124. opts.sheetStubs = true;
  125. opts.cellDates = true;
  126. wopts.cellStyles = true;
  127. wopts.bookVBA = true;
  128. }
  129. if(program.sparse) opts.dense = false; else opts.dense = true;
  130. if(program.codepage) opts.codepage = +program.codepage;
  131. if(program.dev) {
  132. opts.WTF = true;
  133. wb = X.readFile(filename, opts);
  134. } else try {
  135. wb = X.readFile(filename, opts);
  136. } catch(e) {
  137. var msg = (program.quiet) ? "" : n + ": error parsing ";
  138. msg += filename + ": " + e;
  139. console.error(msg);
  140. process.exit(3);
  141. }
  142. if(program.read) process.exit(0);
  143. if(!wb) { console.error(n + ": error parsing " + filename + ": empty workbook"); process.exit(0); }
  144. /*:: if(!wb) throw new Error("unreachable"); */
  145. if(program.listSheets) {
  146. console.log((wb.SheetNames||[]).join("\n"));
  147. process.exit(0);
  148. }
  149. /* full workbook formats */
  150. workbook_formats.forEach(function(m) { if(program[m[0]] || isfmt(m[0])) {
  151. wopts.bookType = m[1];
  152. X.writeFile(wb, program.output || sheetname || ((filename || "") + "." + m[2]), wopts);
  153. process.exit(0);
  154. } });
  155. wb_formats_2.forEach(function(m) { if(program[m[0]] || isfmt(m[0])) {
  156. wopts.bookType = m[1];
  157. X.writeFile(wb, program.output || sheetname || ((filename || "") + "." + m[2]), wopts);
  158. process.exit(0);
  159. } });
  160. var target_sheet = sheetname || '';
  161. if(target_sheet === '') {
  162. if(program.sheetIndex < (wb.SheetNames||[]).length) target_sheet = wb.SheetNames[program.sheetIndex];
  163. else target_sheet = (wb.SheetNames||[""])[0];
  164. }
  165. var ws;
  166. try {
  167. ws = wb.Sheets[target_sheet];
  168. if(!ws) {
  169. console.error("Sheet " + target_sheet + " cannot be found");
  170. process.exit(3);
  171. }
  172. } catch(e) {
  173. console.error(n + ": error parsing "+filename+" "+target_sheet+": " + e);
  174. process.exit(4);
  175. }
  176. if(!program.quiet && !program.book) console.error(target_sheet);
  177. /* single worksheet file formats */
  178. [
  179. ['biff2', '.xls'],
  180. ['biff3', '.xls'],
  181. ['biff4', '.xls'],
  182. ['sylk', '.slk'],
  183. ['html', '.html'],
  184. ['prn', '.prn'],
  185. ['eth', '.eth'],
  186. ['rtf', '.rtf'],
  187. ['txt', '.txt'],
  188. ['dbf', '.dbf'],
  189. ['dif', '.dif']
  190. ].forEach(function(m) { if(program[m[0]] || isfmt(m[1])) {
  191. wopts.bookType = m[0];
  192. if(program.book) {
  193. /*:: if(wb == null) throw new Error("Unreachable"); */
  194. wb.SheetNames.forEach(function(n, i) {
  195. wopts.sheet = n;
  196. X.writeFile(wb, (program.output || sheetname || filename || "") + m[1] + "." + i, wopts);
  197. });
  198. } else X.writeFile(wb, program.output || sheetname || ((filename || "") + m[1]), wopts);
  199. process.exit(0);
  200. } });
  201. function outit(o, fn) { if(fn) fs.writeFileSync(fn, o); else console.log(o); }
  202. function doit(cb) {
  203. /*:: if(!wb) throw new Error("unreachable"); */
  204. if(program.book) wb.SheetNames.forEach(function(n, i) {
  205. /*:: if(!wb) throw new Error("unreachable"); */
  206. outit(cb(wb.Sheets[n]), (program.output || sheetname || filename) + "." + i);
  207. });
  208. else outit(cb(ws), program.output);
  209. }
  210. var jso = {};
  211. switch(true) {
  212. case program.formulae:
  213. doit(function(ws) { return X.utils.sheet_to_formulae(ws).join("\n"); });
  214. break;
  215. case program.arrays: jso.header = 1;
  216. /* falls through */
  217. case program.rawJs: jso.raw = true;
  218. /* falls through */
  219. case program.json:
  220. doit(function(ws) { return JSON.stringify(X.utils.sheet_to_json(ws,jso)); });
  221. break;
  222. default:
  223. if(!program.book) {
  224. var stream = X.stream.to_csv(ws, {FS:program.fieldSep, RS:program.rowSep});
  225. if(program.output) stream.pipe(fs.createWriteStream(program.output));
  226. else stream.pipe(process.stdout);
  227. } else doit(function(ws) { return X.utils.sheet_to_csv(ws,{FS:program.fieldSep, RS:program.rowSep}); });
  228. break;
  229. }