JQuery 导入导出 Excel

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

 jQuery Plugin to Export HTML Tables

例子: 

导入插件: 

<script src="jquery-tableexport/tableExport.js"></script>
<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 包:

<!-- https://github.com/SheetJS/js-xlsx/blob/master/jszip.js -->
<script src="/path/to/jszip.js"></script>
<!-- https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js -->
<script src="/path/to/xlsx.js"></script>

Node.js 安装:

$ npm install xlsx

$ node

> require( 'xlsx' ).readFile( 'excel_file.xlsx' );

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

function get_radio_value( radioName ) {
    var radios = document.getElementsByName( radioName );
    for( var i = 0; i < radios.length; i++ ) {
        if( radios[i].checked ) {
            return radios[i].value;
        }
    }
}
 
function to_json(workbook) {
    var result = {};
    workbook.SheetNames.forEach(function(sheetName) {
        var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
        if(roa.length > 0){
            result[sheetName] = roa;
        }
    });
    return result;
}
 
function to_csv(workbook) {
    var result = [];
    workbook.SheetNames.forEach(function(sheetName) {
        var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
        if(csv.length > 0){
            result.push("SHEET: " + sheetName);
            result.push("");
            result.push(csv);
        }
    });
    return result.join("\n");
}
 
function to_formulae(workbook) {
    var result = [];
    workbook.SheetNames.forEach(function(sheetName) {
        var formulae = XLSX.utils.get_formulae(workbook.Sheets[sheetName]);
        if(formulae.length > 0){
            result.push("SHEET: " + sheetName);
            result.push("");
            result.push(formulae.join("\n"));
        }
    });
    return result.join("\n");
}
 
var tarea = document.getElementById('b64data');
function b64it() {
    var wb = XLSX.read(tarea.value, {type: 'base64'});
    process_wb(wb);
}
 
function process_wb(wb) {
    var output = "";
    switch(get_radio_value("format")) {
        case "json":
        output = JSON.stringify(to_json(wb), 2, 2);
            break;
        case "form":
            output = to_formulae(wb);
            break; 
        default:
        output = to_csv(wb);
    }
    if(out.innerText === undefined) out.textContent = output;
    else out.innerText = output;
}
 
var drop = document.getElementById('drop');
function handleDrop(e) {
    e.stopPropagation();
    e.preventDefault();
    var files = e.dataTransfer.files;
    var i,f;
    for (i = 0, f = files[i]; i != files.length; ++i) {
        var reader = new FileReader();
        var name = f.name;
        reader.onload = function(e) {
            var data = e.target.result;
            //var wb = XLSX.read(data, {type: 'binary'});
            var arr = String.fromCharCode.apply(null, new Uint8Array(data));
            var wb = XLSX.read(btoa(arr), {type: 'base64'});
            process_wb(wb);
        };
        //reader.readAsBinaryString(f);
        reader.readAsArrayBuffer(f);
    }
}
 
function handleDragover(e) {
    e.stopPropagation();
    e.preventDefault();
    e.dataTransfer.dropEffect = 'copy';
}
 
if(drop.addEventListener) {
    drop.addEventListener('dragenter', handleDragover, false);
    drop.addEventListener('dragover', handleDragover, false);
    drop.addEventListener('drop', handleDrop, false);
}

插件作者地址:  author

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

@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <input type="file" name="file" />
    <input type="submit" value="OK" />
}

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");        
    }
}