JQuery 导入导出 Excel

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

 jQuery Plugin to Export HTML Tables

例子: 

导入插件: 

[javascript] view plain copy
 
 在CODE上查看代码片派生到我的代码片
  1. <script src="jquery-tableexport/tableExport.js"></script>  
  2. <script src="jquery-tableexport/jquery.base64.js"></script>  

html: 

 

 

<a href="#" 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 plain copy
 
 在CODE上查看代码片派生到我的代码片
  1. <!-- https://github.com/SheetJS/js-xlsx/blob/master/jszip.js -->  
  2. <script src="/path/to/jszip.js"></script>  
  3. <!-- https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js -->  
  4. <script src="/path/to/xlsx.js"></script>  


Node.js 安装:

 

 

$ npminstall xlsx
$ node
> require('xlsx').readFile('excel_file.xlsx');

 

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

 

[javascript] view plain copy
 
 在CODE上查看代码片派生到我的代码片
  1. function get_radio_value( radioName ) {  
  2.     var radios = document.getElementsByName( radioName );  
  3.     for( var i = 0; i < radios.length; i++ ) {  
  4.         if( radios[i].checked ) {  
  5.             return radios[i].value;  
  6.         }  
  7.     }  
  8. }  
  9.    
  10. function to_json(workbook) {  
  11.     var result = {};  
  12.     workbook.SheetNames.forEach(function(sheetName) {  
  13.         var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);  
  14.         if(roa.length > 0){  
  15.             result[sheetName] = roa;  
  16.         }  
  17.     });  
  18.     return result;  
  19. }  
  20.    
  21. function to_csv(workbook) {  
  22.     var result = [];  
  23.     workbook.SheetNames.forEach(function(sheetName) {  
  24.         var csv = 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.     return result.join("\n");  
  32. }  
  33.    
  34. function to_formulae(workbook) {  
  35.     var result = [];  
  36.     workbook.SheetNames.forEach(function(sheetName) {  
  37.         var formulae = 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("\n"));  
  42.         }  
  43.     });  
  44.     return result.join("\n");  
  45. }  
  46.    
  47. var tarea = document.getElementById('b64data');  
  48. function b64it() {  
  49.     var wb = XLSX.read(tarea.value, {type: 'base64'});  
  50.     process_wb(wb);  
  51. }  
  52.    
  53. function process_wb(wb) {  
  54.     var output = "";  
  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.     else out.innerText = output;  
  67. }  
  68.    
  69. var drop = document.getElementById('drop');  
  70. function handleDrop(e) {  
  71.     e.stopPropagation();  
  72.     e.preventDefault();  
  73.     var files = e.dataTransfer.files;  
  74.     var i,f;  
  75.     for (i = 0, f = files[i]; i != files.length; ++i) {  
  76.         var reader = new FileReader();  
  77.         var name = f.name;  
  78.         reader.onload = function(e) {  
  79.             var data = e.target.result;  
  80.             //var wb = XLSX.read(data, {type: 'binary'});  
  81.             var arr = String.fromCharCode.apply(null, new Uint8Array(data));  
  82.             var wb = XLSX.read(btoa(arr), {type: 'base64'});  
  83.             process_wb(wb);  
  84.         };  
  85.         //reader.readAsBinaryString(f);  
  86.         reader.readAsArrayBuffer(f);  
  87.     }  
  88. }  
  89.    
  90. function handleDragover(e) {  
  91.     e.stopPropagation();  
  92.     e.preventDefault();  
  93.     e.dataTransfer.dropEffect = 'copy';  
  94. }  
  95.    
  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 plain copy
 
 在CODE上查看代码片派生到我的代码片
  1. @using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))  
  2. {  
  3.     <input type="file" name="file" />  
  4.     <input type="submit" value="OK" />  
  5. }  

 

 

public class HomeController : Controller
{
    // This action renders the form
    public ActionResult Index()
    {
        return View();
    }

    // This action handles the form POST and the upload
    [HttpPost]
    public ActionResult Index(HttpPostedFileBase file)
    {
        // Verify that the user selected a file
        if (file != null && file.ContentLength > 0) 
        {
            // extract only the fielname
            var fileName = Path.GetFileName(file.FileName);
            // store the file inside ~/App_Data/uploads folder
            var path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
            file.SaveAs(path);
        }
        // redirect back to the index action to show the form once again
        return RedirectToAction("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));

posted on 2017-02-06 20:46  大西瓜3721  阅读(13989)  评论(0)  编辑  收藏  举报

导航