JQuery 导入导出 Excel

正在做一个小项目, 从数据库中查询数据放在 HTML Table 中. 现在想要从这个 table 中导出数据来. 另外用户需要选择导出的列. 使用jQuery的导出插件可以完成这个需求.

jQuery Plugin to Export HTML Tables

例子:

导入插件:

[javascript]view plaincopy

1. <scriptsrc="jquery-tableexport/tableExport.js"></script>
2. <scriptsrc="jquery-tableexport/jquery.base64.js"></script>

html:

<ahref="#"onClick="$('#table-name').tableExport({type:'excel',separator:';',escape:'false'});"id="buttonExportData"class="ui-btn ui-btn-inline ui-mini ui-shadow ui-corner-all">Export XLS</a>

插件还有以下这些参数选项:

separator:','
ignoreColumn:[2,3],
tableName:'yourTableName'
type:'csv'
pdfFontSize:14
pdfLeftMargin:20
escape:'true'
htmlContent:'false'
consoleLog:'false'

通过 ignoreColumn 可以指定哪几列不被导出.

JS-XLSX

导入 excel 2007 以上版本, 可以使用 JS-XLSX 插件. 首先导入 js 包:

[javascript]view plaincopy

1. <!--https://github.com/SheetJS/js-xlsx/blob/master/jszip.js-->
2. <scriptsrc="/path/to/jszip.js"></script>
3. <!--https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js-->
4. <scriptsrc="/path/to/xlsx.js"></script>

Node.js 安装:

然后可以使用这个插件把 XLSX 文件转为 JSON, CSV, Formula 输出.

[javascript]view plaincopy

1. functionget_radio_value(radioName){
2. varradios=document.getElementsByName(radioName);
3. for(vari=0;i<radios.length;i++){
4. if(radios[i].checked){
5. returnradios[i].value;
6. }
7. }
8. }
10. functionto_json(workbook){
11. varresult={};
12. workbook.SheetNames.forEach(function(sheetName){
13. varroa=XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
14. if(roa.length>0){
15. result[sheetName]=roa;
16. }
17. });
18. returnresult;
19. }
21. functionto_csv(workbook){
22. varresult=[];
23. workbook.SheetNames.forEach(function(sheetName){
24. varcsv=XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
25. if(csv.length>0){
26. result.push("SHEET:"+sheetName);
27. result.push("");
28. result.push(csv);
29. }
30. });
31. returnresult.join(" ");
32. }
34. functionto_formulae(workbook){
35. varresult=[];
36. workbook.SheetNames.forEach(function(sheetName){
37. varformulae=XLSX.utils.get_formulae(workbook.Sheets[sheetName]);
38. if(formulae.length>0){
39. result.push("SHEET:"+sheetName);
40. result.push("");
41. result.push(formulae.join(" "));
42. }
43. });
44. returnresult.join(" ");
45. }
47. vartarea=document.getElementById('b64data');
48. functionb64it(){
49. varwb=XLSX.read(tarea.value,{type:'base64'});
50. process_wb(wb);
51. }
53. functionprocess_wb(wb){
54. varoutput="";
55. switch(get_radio_value("format")){
56. case"json":
57. output=JSON.stringify(to_json(wb),2,2);
58. break;
59. case"form":
60. output=to_formulae(wb);
61. break;
62. default:
63. output=to_csv(wb);
64. }
65. if(out.innerText===undefined)out.textContent=output;
66. elseout.innerText=output;
67. }
69. vardrop=document.getElementById('drop');
70. functionhandleDrop(e){
71. e.stopPropagation();
72. e.preventDefault();
73. varfiles=e.dataTransfer.files;
74. vari,f;
75. for(i=0,f=files[i];i!=files.length;++i){
76. varreader=newFileReader();
77. varname=f.name;
78. reader.onload=function(e){
79. vardata=e.target.result;
80. //varwb=XLSX.read(data,{type:'binary'});
81. vararr=String.fromCharCode.apply(null,newUint8Array(data));
82. varwb=XLSX.read(btoa(arr),{type:'base64'});
83. process_wb(wb);
84. };
85. //reader.readAsBinaryString(f);
86. reader.readAsArrayBuffer(f);
87. }
88. }
90. functionhandleDragover(e){
91. e.stopPropagation();
92. e.preventDefault();
93. e.dataTransfer.dropEffect='copy';
94. }
96. if(drop.addEventListener){
97. drop.addEventListener('dragenter',handleDragover,false);
98. drop.addEventListener('dragover',handleDragover,false);
99. drop.addEventListener('drop',handleDrop,false);
100. }

插件作者地址:author

不使用 HTML5 的话, 就要上传文件到服务器端, 服务器再来解析处理文件.例子如下:

[html]view plaincopy

1. @using(Html.BeginForm("Index","Home",FormMethod.Post,new{enctype="multipart/form-data"}))
2. {
3. <inputtype="file"name="file"/>
4. <inputtype="submit"value="OK"/>
5. }

publicclassHomeController:Controller
{
    // This action renders the form
    publicActionResultIndex()
    {
        returnView();
    }
    // This action handles the form POST and the upload
    [HttpPost]
    publicActionResultIndex(HttpPostedFileBasefile)
    {
        // Verify that the user selected a file
        if(file !=null&&file.ContentLength>0)
        {
            // extract only the fielname
            varfileName =Path.GetFileName(file.FileName);
            // store the file inside ~/App_Data/uploads folder
            varpath =Path.Combine(Server.MapPath("~/App_Data/uploads"),fileName);
            file.SaveAs(path);
        }
        // redirect back to the index action to show the form once again
        returnRedirectToAction("Index");
    }
}

深圳一朋友说使用jquery.base64.js时发现对于中文直接抛出异常，作者压根不处理汉字的情况，因此

对其进行修正，关键函数为：

jQuery.base64 = (function ($) {

var _PADCHAR = "=",
_ALPHA = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
_VERSION = "1.1"; //Mr. Ruan fix to 1.1 to support asian char(utf8)

function _getbyte64(s, i) {
// This is oddly fast, except on Chrome/V8.
// Minimal or no improvement in performance by using a
// object with properties mapping chars to value (eg. 'A': 0)

var idx = _ALPHA.indexOf(s.charAt(i));

if (idx === -1) {
throw "Cannot decode base64";
}

return idx;
}

function _decode_chars(y, x) {
while (y.length > 0) {
var ch = y[0];
if (ch < 0x80) {
y.shift();
x.push(String.fromCharCode(ch));
} else if ((ch & 0x80) == 0xc0) {
if (y.length < 2) break;
ch = y.shift();
var ch1 = y.shift();
x.push(String.fromCharCode(((ch & 0x1f) << 6) + (ch1 & 0x3f)));
} else {
if (y.length < 3) break;
ch = y.shift();
var ch1 = y.shift();
var ch2 = y.shift();
x.push(String.fromCharCode(((ch & 0x0f) << 12) + ((ch1 & 0x3f) << 6) + (ch2 & 0x3f)));
}
}
}

function _decode(s) {
var pads = 0,
i,
b10,
imax = s.length,
x = [],
y = [];

s = String(s);

if (imax === 0) {
return s;
}

if (imax % 4 !== 0) {
throw "Cannot decode base64";
}

if (s.charAt(imax - 1) === _PADCHAR) {
pads = 1;

if (s.charAt(imax - 2) === _PADCHAR) {
pads = 2;
}

// either way, we want to ignore this last block
imax -= 4;
}

for (i = 0; i < imax; i += 4) {
var ch1 = _getbyte64(s, i);
var ch2 = _getbyte64(s, i + 1);
var ch3 = _getbyte64(s, i + 2);
var ch4 = _getbyte64(s, i + 3);

b10 = (_getbyte64(s, i) << 18) | (_getbyte64(s, i + 1) << 12) | (_getbyte64(s, i + 2) << 6) | _getbyte64(s, i + 3);
y.push(b10 >> 16);
y.push((b10 >> 8) & 0xff);
y.push(b10 & 0xff);
_decode_chars(y, x);
}
switch (pads) {
case 1:
b10 = (_getbyte64(s, i) << 18) | (_getbyte64(s, i + 1) << 12) | (_getbyte64(s, i + 2) << 6);
y.push(b10 >> 16);
y.push((b10 >> 8) & 0xff);
break;

case 2:
b10 = (_getbyte64(s, i) << 18) | (_getbyte64(s, i + 1) << 12);
y.push(b10 >> 16);
break;
}
_decode_chars(y, x);
if (y.length > 0) throw "Cannot decode base64";
return x.join("");
}

function _get_chars(ch, y) {
if (ch < 0x80) y.push(ch);
else if (ch < 0x800) {
y.push(0xc0 + ((ch >> 6) & 0x1f));
y.push(0x80 + (ch & 0x3f));
} else {
y.push(0xe0 + ((ch >> 12) & 0xf));
y.push(0x80 + ((ch >> 6) & 0x3f));
y.push(0x80 + (ch & 0x3f));
}
}

function _encode(s) {
if (arguments.length !== 1) {
throw "SyntaxError: exactly one argument required";
}

s = String(s);
if (s.length === 0) {
return s;
}

//s = _encode_utf8(s);
var i,
b10,
y = [],
x = [],
len = s.length;
i = 0;
while (i < len) {
_get_chars(s.charCodeAt(i), y);
while (y.length >= 3) {
var ch1 = y.shift();
var ch2 = y.shift();
var ch3 = y.shift();
b10 = (ch1 << 16) | (ch2 << 8) | ch3;
x.push(_ALPHA.charAt(b10 >> 18));
x.push(_ALPHA.charAt((b10 >> 12) & 0x3F));
x.push(_ALPHA.charAt((b10 >> 6) & 0x3f));
x.push(_ALPHA.charAt(b10 & 0x3f));
}
i++;
}

switch (y.length) {
case 1:
var ch = y.shift();
b10 = ch << 16;
x.push(_ALPHA.charAt(b10 >> 18) + _ALPHA.charAt((b10 >> 12) & 0x3F) + _PADCHAR + _PADCHAR);
break;

case 2:
var ch1 = y.shift();
var ch2 = y.shift();
b10 = (ch1 << 16) | (ch2 << 8);
x.push(_ALPHA.charAt(b10 >> 18) + _ALPHA.charAt((b10 >> 12) & 0x3F) + _ALPHA.charAt((b10 >> 6) & 0x3f) + _PADCHAR);
break;
}

return x.join("");
}

return {
decode: _decode,
encode: _encode,
VERSION: _VERSION
};

} (jQuery));