在 jquery 或 jqGrid 中导出到 excel

2022-01-11 00:00:00 excel jquery jquery-plugins javascript jqgrid

我有一个 jqGrid，我可以在其中一次从服务器 (java) 获取数据JSON 格式.我希望将 jqGrid 中的数据导出为 Excel 格式.

I have a jqGrid where I get data at once from server (java) in JSON format. I want the data in the jqGrid to be exported into Excel format.

到目前为止，我看到了这个 页面，它在 IE 中给了我一个错误 'o.url 为空或不是对象'grid.import.js

Till now I saw this page which gives me an error in IE 'o.url is null or not an object' grid.import.js

我还看到了 这个演示导出按钮的工具提示它说 Export To Excel 但保存的文件是 xml 格式.

Also I saw this demo where on the tool tip of export button it says Export To Excel but the file saved is in xml format.

所以我想要任何可以使用 javascript 或 jquery 插件或使用 jqgrid 的内置功能将我的 JSON 字符串转换为 excel 的建议.

So I would like any suggestions that can either transform my JSON string into excel using javascript or jquery plugin or using jqgrid's inbuilt feature.

我的 jqGrid

我的 jqGrid 代码

My jqGrid Code

grid = jQuery("#list2");
                grid.jqGrid({
                    datastr : comparePatchData,
                    datatype: 'jsonstring',
                    colNames:['Name',starheader, header1, header2],
                    colModel:[
                        {name:'elementName',index:'elementName', width:90},
                        {name:'isPrasentinXml1',index:'isPrasentinXml1', width:100, align:'center', formatter: patchPresent},
                        {name:'isPrasentinXml2',index:'isPrasentinXml2', width:100, align:'center', formatter: patchPresent},
                        {name:'isPrasentinXml3',index:'isPrasentinXml3', width:100, align:'center', formatter: patchPresent}
                    ],
                    pager : '#gridpager2',
                    rowNum:12,
                    scrollOffset:0,
                    height: 320,
                    autowidth:true,
                    viewrecords: true,
                    gridview: true,
                    loadonce:true,
                    jsonReader: {
                        repeatitems: false,
                        page: function() { return 1; },
                        root: "response"
                    },
                    subGrid: true,
                    // define the icons in subgrid
                    subGridOptions: {
                        "plusicon"  : "ui-icon-triangle-1-e",
                        "minusicon" : "ui-icon-triangle-1-s",
                        "openicon"  : "ui-icon-arrowreturn-1-e",
                        //expand all rows on load
                        "expandOnLoad" : false
                    },

                    subGridRowExpanded: function(subgrid_id, row_id) {
                        //console.info(subgrid_id+", "+row_id);
                        var subgrid_table_id, pager_id, iData = -1;
                        subgrid_table_id = subgrid_id+"_t";
                        //pager_id = "p_"+subgrid_table_id;
                        $("#"+subgrid_id).html("<table id='"+subgrid_table_id+"' style='overflow-y:auto' class='scroll'></table><div id='"+pager_id+"' class='scroll'></div>");

                        $.each(comparePatchData.response,function(i,item){
                            if(item.id === row_id) {
                                iData = i;
                                return false;
                            }

                        });
                        if (iData == -1) {
                            return; // no data for the subgrid
                        }

                        jQuery("#"+subgrid_table_id).jqGrid({
                            datastr : comparePatchData.response[iData],
                            datatype: 'jsonstring',
                            colNames: ['Name','Value1','Value2','Value3'],
                            colModel: [
                                {name:"name",index:"name",width:90},
                                {name:"firstValue",index:"firstValue",width:100},
                                {name:"secondValue",index:"secondValue",width:100},
                                {name:"thirdValue",index:"thirdValue",width:100}
                            ],
                            rowNum:10,
                            //pager: pager_id,
                            sortname: 'name',
                            sortorder: "asc",
                            height: 'auto',
                            autowidth:true,
                            jsonReader: {
                                repeatitems: false,
                                //page: function() { return 1; },
                                root: "attribute"
                            }
                        });

                        jQuery("#"+subgrid_table_id).jqGrid('navGrid',{edit:false,add:false,del:false});
                    }
                });
                grid.jqGrid('navGrid','#gridpager2',{add:false,edit:false,del:false});
                grid.jqGrid('navButtonAdd','#gridpager2',{
                    caption:"Export to Excel", 
                    onClickButton : function () { 
                        jQuery("#list2").excelExport();
                    } 
                });

我的 Json 的一部分

Part of my Json

{
"response": [
    {
        "id": "1",
        "elementName": "libgtop2-devel-2.14.4-3.el5",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": false,
        "isPrasentinXml2": false,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "thirdValue": "libgtop2-devel-2.14.4-3.el5"
            }
        ]
    },
    {
        "id": "2",
        "elementName": "ifd-egate-0.05-15",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": false,
        "isPrasentinXml2": false,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "thirdValue": "ifd-egate-0.05-15"
            }
        ]
    },
    {
        "id": "3",
        "elementName": "libXScrnSaver-devel-1.1.0-3.1",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": false,
        "isPrasentinXml2": false,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "thirdValue": "libXScrnSaver-devel-1.1.0-3.1"
            }
        ]
    },
    {
        "id": "4",
        "elementName": "kde-i18n-Chinese-Big5-3.5.4-1",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": true,
        "isPrasentinXml2": true,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "firstValue": "kde-i18n-Chinese-Big5-3.5.4-1",
                "secondValue": "kde-i18n-Chinese-Big5-3.5.4-1"
            }
        ]
    },
    {
        "id": "5",
        "elementName": "cpio-2.6-20",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": true,
        "isPrasentinXml2": true,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "firstValue": "cpio-2.6-20",
                "secondValue": "cpio-2.6-20",
                "thirdValue": "cpio-2.6-20"
            }
        ]
    },
    {
        "id": "6",
        "elementName": "grep-2.5.1-54.2.el5",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": true,
        "isPrasentinXml2": true,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "firstValue": "grep-2.5.1-54.2.el5",
                "secondValue": "grep-2.5.1-54.2.el5",
                "thirdValue": "grep-2.5.1-54.2.el5"
            }
        ]
    },
    {
        "id": "7",
        "elementName": "avahi-compat-libdns_sd-0.6.16-1.el5",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": true,
        "isPrasentinXml2": true,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "firstValue": "avahi-compat-libdns_sd-0.6.16-1.el5",
                "secondValue": "avahi-compat-libdns_sd-0.6.16-1.el5",
                "thirdValue": "avahi-compat-libdns_sd-0.6.16-1.el5"
            }
        ]
    },
    {
        "id": "8",
        "elementName": "gpm-devel-1.20.1-74.1",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": true,
        "isPrasentinXml2": true,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "firstValue": "gpm-devel-1.20.1-74.1",
                "secondValue": "gpm-devel-1.20.1-74.1",
                "thirdValue": "gpm-devel-1.20.1-74.1"
            }
        ]
    },
    {
        "id": "9",
        "elementName": "esc-1.0.0-39.el5",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": false,
        "isPrasentinXml2": false,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "thirdValue": "esc-1.0.0-39.el5"
            }
        ]
    },
    {
        "id": "10",
        "elementName": "kde-i18n-Spanish-3.5.4-1",
        "subCategory": "patch",
        "isEqual": false,
        "isPrasentinXml1": true,
        "isPrasentinXml2": true,
        "isPrasentinXml3": true,
        "attribute": [
            {
                "name": "name",
                "firstValue": "kde-i18n-Spanish-3.5.4-1",
                "secondValue": "kde-i18n-Spanish-3.5.4-1"
            }
        ]
    }
]
}

推荐答案

您不必使用 Excel 格式导出文件即可将数据导入 Excel.导出到 CSV 通常要容易得多.默认情况下，CSV 文件应与 Excel 关联，因此它应具有 Excel 图标和所有内容.我认为 XML 会以同样的方式工作，但 CSV 格式要轻得多，并且在这种情况下做同样的工作.将 JSON 转换为 CSV 很简单:

You don't have to export a file using the Excel format in order to get the data into Excel. It is generally much easier to export to CSV. CSV files should be associated with Excel by default, so it should have the Excel icon by it and everything. XML would work the same way, I think, but the CSV format is much lighter, and does the same job in this case. Converting JSON to CSV is simple:

var response = JSON.parse(responseJSON).response;
var csv = arrayToCSV(response);

function arrayToCSV(arr) {
    var columnNames = [];
    var rows = [];
    for (var i=0, len=arr.length; i<len; i++) {
        // Each obj represents a row in the table
        var obj = arr[i];
        // row will collect data from obj
        var row = [];
        for (var key in obj) {
            // Don't iterate through prototype stuff
            if (!obj.hasOwnProperty(key)) continue;
            // Collect the column names only once
            if (i === 0) columnNames.push(prepareValueForCSV(key));
            // Collect the data
            row.push(prepareValueForCSV(obj[key]));
        }
        // Push each row to the main collection as csv string
        rows.push(row.join(','));
    }
    // Put the columnNames at the beginning of all the rows
    rows.unshift(columnNames.join(','));
    // Return the csv string
    return rows.join('
');
}

// This function allows us to have commas, line breaks, and double 
// quotes in our value without breaking CSV format.
function prepareValueForCSV(val) {
    val = '' + val;
    // Escape quotes to avoid ending the value prematurely.
    val = val.replace(/"/g, '""');
    return '"' + val + '"';
}

相关文章