index.html 7.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241
  1. <!DOCTYPE html>
  2. <html lang="zh-cn">
  3. <head>
  4. <meta charset="UTF-8">
  5. <title>课表导入导出</title>
  6. <meta name="description" content="使用sheetjs读取和导出excel">
  7. <style type="text/css">
  8. table {
  9. border-collapse: collapse;
  10. }
  11. th, td {
  12. border: solid 1px #6D6D6D;
  13. padding: 5px 10px;
  14. }
  15. .mt-sm {margin-top: 8px;}
  16. body {
  17. background: #f4f4f4;
  18. padding: 0;
  19. margin: 0;
  20. }
  21. .container {
  22. width: 1024px;
  23. margin: 0 auto;
  24. background: #fff;
  25. padding: 20px;
  26. min-height: 100vh;
  27. }
  28. </style>
  29. </head>
  30. <body>
  31. <div class="container">
  32. <h2>读取excel</h2>
  33. <div class="mt-sm">
  34. <input type="file" id="file" style="display:none;" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"/>
  35. <a href="javascript:selectFile()">加载本地excel文件</a>
  36. </div>
  37. <p>结果输出:(下面表格可直接编辑导出)</p>
  38. <div id="result" contenteditable></div>
  39. <h2>导出excel</h2>
  40. <div class="mt-sm" style="padding-bottom:40px;">
  41. <input type="button" onclick="exportExcel()" value="保存"/> 上面读取的表格您可以直接编辑,编辑后点击保存即可导出excel文件。
  42. </div>
  43. <h2>导出带单元格合并的excel</h2>
  44. <input type="button" value="导出" onclick="exportSpecialExcel()"/>
  45. </div>
  46. <script type="text/javascript" src="./js/jquery.min.js"></script>
  47. <script type="text/javascript" src="./js/xlsx.core.min.js"></script>
  48. <script type="text/javascript">
  49. function selectFile() {
  50. document.getElementById('file').click();
  51. }
  52. // 读取本地excel文件
  53. function readWorkbookFromLocalFile(file, callback) {
  54. var reader = new FileReader();
  55. reader.onload = function(e) {
  56. var data = e.target.result;
  57. var workbook = XLSX.read(data, {type: 'binary'});
  58. if(callback) callback(workbook);
  59. };
  60. reader.readAsBinaryString(file);
  61. }
  62. // 读取 excel文件
  63. function outputWorkbook(workbook) {
  64. var sheetNames = workbook.SheetNames; // 工作表名称集合
  65. sheetNames.forEach(name => {
  66. var worksheet = workbook.Sheets[name]; // 只能通过工作表名称来获取指定工作表
  67. for(var key in worksheet) {
  68. // v是读取单元格的原始值
  69. console.log(key, key[0] === '!' ? worksheet[key] : worksheet[key].v);
  70. }
  71. });
  72. }
  73. function readWorkbook(workbook) {
  74. var sheetNames = workbook.SheetNames; // 工作表名称集合
  75. var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
  76. var csv = XLSX.utils.sheet_to_csv(worksheet);
  77. document.getElementById('result').innerHTML = csv2table(csv);
  78. }
  79. // 将csv转换成表格
  80. function csv2table(csv)
  81. {
  82. var html = '<table>';
  83. var rows = csv.split('\n');
  84. rows.pop(); // 最后一行没用的
  85. rows.forEach(function(row, idx) {
  86. var columns = row.split(',');
  87. columns.unshift(idx+1); // 添加行索引
  88. if(idx == 0) { // 添加列索引
  89. html += '<tr>';
  90. for(var i=0; i<columns.length; i++) {
  91. html += '<th>' + (i==0?'':String.fromCharCode(65+i-1)) + '</th>';
  92. }
  93. html += '</tr>';
  94. }
  95. html += '<tr>';
  96. columns.forEach(function(column) {
  97. html += '<td>'+column+'</td>';
  98. });
  99. html += '</tr>';
  100. });
  101. html += '</table>';
  102. return html;
  103. }
  104. function table2csv(table) {
  105. var csv = [];
  106. $(table).find('tr').each(function() {
  107. var temp = [];
  108. $(this).find('td').each(function() {
  109. temp.push($(this).html());
  110. })
  111. temp.shift(); // 移除第一个
  112. csv.push(temp.join(','));
  113. });
  114. csv.shift();
  115. return csv.join('\n');
  116. }
  117. // csv转sheet对象
  118. function csv2sheet(csv) {
  119. var sheet = {}; // 将要生成的sheet
  120. csv = csv.split('\n');
  121. csv.forEach(function(row, i) {
  122. row = row.split(',');
  123. if(i == 0) sheet['!ref'] = 'A1:'+String.fromCharCode(65+row.length-1)+(csv.length-1);
  124. row.forEach(function(col, j) {
  125. sheet[String.fromCharCode(65+j)+(i+1)] = {v: col};
  126. });
  127. });
  128. return sheet;
  129. }
  130. // 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
  131. function sheet2blob(sheet, sheetName) {
  132. sheetName = sheetName || 'sheet1';
  133. var workbook = {
  134. SheetNames: [sheetName],
  135. Sheets: {}
  136. };
  137. workbook.Sheets[sheetName] = sheet;
  138. // 生成excel的配置项
  139. var wopts = {
  140. bookType: 'xlsx', // 要生成的文件类型
  141. bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
  142. type: 'binary'
  143. };
  144. var wbout = XLSX.write(workbook, wopts);
  145. var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
  146. // 字符串转ArrayBuffer
  147. function s2ab(s) {
  148. var buf = new ArrayBuffer(s.length);
  149. var view = new Uint8Array(buf);
  150. for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  151. return buf;
  152. }
  153. return blob;
  154. }
  155. /**
  156. * 通用的打开下载对话框方法,没有测试过具体兼容性
  157. * @param url 下载地址,也可以是一个blob对象,必选
  158. * @param saveName 保存文件名,可选
  159. */
  160. function openDownloadDialog(url, saveName)
  161. {
  162. if(typeof url == 'object' && url instanceof Blob)
  163. {
  164. url = URL.createObjectURL(url); // 创建blob地址
  165. }
  166. var aLink = document.createElement('a');
  167. aLink.href = url;
  168. aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
  169. var event;
  170. if(window.MouseEvent) event = new MouseEvent('click');
  171. else
  172. {
  173. event = document.createEvent('MouseEvents');
  174. event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
  175. }
  176. aLink.dispatchEvent(event);
  177. }
  178. $(function() {
  179. document.getElementById('file').addEventListener('change', function(e) {
  180. var files = e.target.files;
  181. if(files.length == 0) return;
  182. var f = files[0];
  183. if(!/\.xlsx$/g.test(f.name)) {
  184. alert('仅支持读取xlsx格式!');
  185. return;
  186. }
  187. readWorkbookFromLocalFile(f, function(workbook) {
  188. readWorkbook(workbook);
  189. });
  190. });
  191. loadRemoteFile('./sample/test.xlsx');
  192. });
  193. function loadRemoteFile(url) {
  194. readWorkbookFromRemoteFile(url, function(workbook) {
  195. readWorkbook(workbook);
  196. });
  197. }
  198. function exportExcel() {
  199. var csv = table2csv($('#result table')[0]);
  200. var sheet = csv2sheet(csv);
  201. var blob = sheet2blob(sheet);
  202. openDownloadDialog(blob, '2018级1班班级课表.xlsx');
  203. }
  204. function exportSpecialExcel() {
  205. var aoa = [
  206. ['主要调查内容', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
  207. ['姓名', '性别', '成绩', '完成时间'],
  208. ['小三', '男', 88, new Date()],
  209. ['小四', '女', 99, new Date()],
  210. ['八年级1班/2015班级课表'],
  211. ['节次','星期一','星期二','星期三','星期四','星期五','星期六','星期天'],
  212. ['第一节','08:35~09:20','08:00~08:45','08:00~08:45','08:00~08:45','08:00~08:45'],
  213. ['数学(八下)','英语(八下)','生物(八下)','英语(八下)','语文(八下)'],
  214. ];
  215. var sheet = XLSX.utils.aoa_to_sheet(aoa);
  216. sheet['!merges'] = [
  217. // 设置A1-C1的单元格合并
  218. {s: {r: 0, c: 0}, e: {r: 0, c: 2}}
  219. ];
  220. openDownloadDialog(sheet2blob(sheet), '单元格合并示例.xlsx');
  221. }
  222. </script>
  223. </body>
  224. </html>