ExtJS:Grid数据导出至excel实例

导出函数ExportExcel()

var config={ store: alldataStore,
	 title: ‘测试标题‘
 };

var tab=tabPanel.getActiveTab();//当前活动状态的Panel

ExportExcel(tab,config);//调用导出函数

ExportGridToExcel.js

var Base64 = {

    // private property
    _keyStr: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",

    // public method for encoding
    encode: function(input) {
        var output = "";
        var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
        var i = 0;

        input = Base64._utf8_encode(input);

        while (i < input.length) {

            chr1 = input.charCodeAt(i++);
            chr2 = input.charCodeAt(i++);
            chr3 = input.charCodeAt(i++);

            enc1 = chr1 >> 2;
            enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
            enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
            enc4 = chr3 & 63;

            if (isNaN(chr2)) {
                enc3 = enc4 = 64;
            } else if (isNaN(chr3)) {
                enc4 = 64;
            }

            output = output +
            this._keyStr.charAt(enc1) + this._keyStr.charAt(enc2) +
            this._keyStr.charAt(enc3) + this._keyStr.charAt(enc4);

        }

        return output;
    },

    // public method for decoding
    decode: function(input) {
        var output = "";
        var chr1, chr2, chr3;
        var enc1, enc2, enc3, enc4;
        var i = 0;

        input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");

        while (i < input.length) {

            enc1 = this._keyStr.indexOf(input.charAt(i++));
            enc2 = this._keyStr.indexOf(input.charAt(i++));
            enc3 = this._keyStr.indexOf(input.charAt(i++));
            enc4 = this._keyStr.indexOf(input.charAt(i++));

            chr1 = (enc1 << 2) | (enc2 >> 4);
            chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);
            chr3 = ((enc3 & 3) << 6) | enc4;

            output = output + String.fromCharCode(chr1);

            if (enc3 != 64) {
                output = output + String.fromCharCode(chr2);
            }
            if (enc4 != 64) {
                output = output + String.fromCharCode(chr3);
            }

        }

        output = Base64._utf8_decode(output);

        return output;

    },

    // private method for UTF-8 encoding
    _utf8_encode: function(string) {
        string = string.replace(/\r\n/g, "\n");
        var utftext = "";

        for (var n = 0; n < string.length; n++) {

            var c = string.charCodeAt(n);

            if (c < 128) {
                utftext += String.fromCharCode(c);
            }
            else if ((c > 127) && (c < 2048)) {
                utftext += String.fromCharCode((c >> 6) | 192);
                utftext += String.fromCharCode((c & 63) | 128);
            }
            else {
                utftext += String.fromCharCode((c >> 12) | 224);
                utftext += String.fromCharCode(((c >> 6) & 63) | 128);
                utftext += String.fromCharCode((c & 63) | 128);
            }

        }

        return utftext;
    },

    // private method for UTF-8 decoding
    _utf8_decode: function(utftext) {
        var string = "";
        var i = 0;
        var c = c1 = c2 = 0;

        while (i < utftext.length) {

            c = utftext.charCodeAt(i);

            if (c < 128) {
                string += String.fromCharCode(c);
                i++;
            }
            else if ((c > 191) && (c < 224)) {
                c2 = utftext.charCodeAt(i + 1);
                string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
                i += 2;
            }
            else {
                c2 = utftext.charCodeAt(i + 1);
                c3 = utftext.charCodeAt(i + 2);
                string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
                i += 3;
            }

        }

        return string;
    }

};

Ext.override(Ext.grid.Panel,{
    getExcelXml: function(includeHidden, config) {

        var worksheet = this.createWorksheet(includeHidden, config);
        //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
        var innertitle = ‘‘;

        if (config && config.title) {
            innertitle = config.title;
        } else {
            innertitle = this.title;
        }
        return ‘<xml version="1.0" encoding="utf-8">‘ +
            ‘<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">‘ +
            ‘<o:DocumentProperties><o:Title>‘ + innertitle + ‘</o:Title></o:DocumentProperties>‘ +
            ‘<ss:ExcelWorkbook>‘ +
                ‘<ss:WindowHeight>‘ + worksheet.height + ‘</ss:WindowHeight>‘ +
                ‘<ss:WindowWidth>‘ + worksheet.width + ‘</ss:WindowWidth>‘ +
                ‘<ss:ProtectStructure>False</ss:ProtectStructure>‘ +
                ‘<ss:ProtectWindows>False</ss:ProtectWindows>‘ +
            ‘</ss:ExcelWorkbook>‘ +
            ‘<ss:Styles>‘ +
                ‘<ss:Style ss:ID="Default">‘ +
                    ‘<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />‘ +
                    ‘<ss:Font ss:FontName="arial" ss:Size="10" />‘ +
                    ‘<ss:Borders>‘ +
                        ‘<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />‘ +
                        ‘<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />‘ +
                        ‘<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />‘ +
                        ‘<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />‘ +
                    ‘</ss:Borders>‘ +
                    ‘<ss:Interior />‘ +
                    ‘<ss:NumberFormat />‘ +
                    ‘<ss:Protection />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:ID="title">‘ +
                    ‘<ss:Borders />‘ +
                    ‘<ss:Font />‘ +
                    ‘<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />‘ +
                    ‘<ss:NumberFormat ss:Format="@" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:ID="headercell">‘ +
                    ‘<ss:Font ss:Bold="1" ss:Size="10" />‘ +
                    ‘<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />‘ +
                    ‘<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:ID="even">‘ +
                    ‘<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:Parent="even" ss:ID="evendate">‘ +
        //                    ‘<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />‘ +
                    ‘<ss:NumberFormat ss:Format="yyyy\-m\-d;@" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:Parent="even" ss:ID="evenint">‘ +
                    ‘<ss:NumberFormat ss:Format="0" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:Parent="even" ss:ID="evenfloat">‘ +
                    ‘<ss:NumberFormat ss:Format="0.00" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:ID="odd">‘ +
                    ‘<ss:Interior ss:Pattern="Solid" ss:Color="#CCCCFF" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:Parent="odd" ss:ID="odddate">‘ +
                    ‘<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:Parent="odd" ss:ID="oddint">‘ +
                    ‘<ss:NumberFormat ss:Format="0" />‘ +
                ‘</ss:Style>‘ +
                ‘<ss:Style ss:Parent="odd" ss:ID="oddfloat">‘ +
                    ‘<ss:NumberFormat ss:Format="0.00" />‘ +
                ‘</ss:Style>‘ +
            ‘</ss:Styles>‘ +
            worksheet.xml +
            ‘</ss:Workbook>‘;
    },
    createWorksheet: function(includeHidden, config) {
        // Calculate cell data types and extra class names which affect formatting
        var cellType = [];
        var cellTypeClass = [];
        //var cm = this.getColumnModel();
        var totalWidthInPixels = 0;
        var colXml = ‘‘;
        var headerXml = ‘‘;
        var visibleColumnCountReduction = 0;
        var innertitle = ‘‘;
        var innerstore = null;

        if (config && config.title) {
            innertitle = config.title;
        } else {
            innertitle = this.title;
        }
        if (!innertitle || innertitle == ‘‘) {
            innertitle = ‘Main Title‘;
        }

        if (config && config.store) {
            innerstore = config.store;
        } else {
            innerstore = this.store;
        }
        for (var i=1;i< this.columns.length;i++) {
            if (includeHidden || !this.columns[i].isHidden()) {
                //debugger;
                var w = this.columns[i].getWidth();
                totalWidthInPixels += w;

                if ((this.columns[i].text === "") || (this.columns[i].getId() === "") ) {
                    cellType.push("None");
                    cellTypeClass.push("");
                    ++visibleColumnCountReduction;
                }
                else {
                    colXml += ‘<ss:Column ss:AutoFitWidth="1" ss:Width="‘ + w + ‘" />‘;
                    headerXml += ‘<ss:Cell ss:StyleID="headercell">‘ +
                        ‘<ss:Data ss:Type="String">‘ + this.columns[i].text + ‘</ss:Data>‘ +
                        ‘<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>‘;

                	var fld = innerstore.model.prototype.fields.items[i-1].type;
                    switch (fld.type) {
                        case "int":
                            cellType.push("Number");
                            cellTypeClass.push("int");
                            break;
                        case "float":
                            cellType.push("Number");
                            cellTypeClass.push("float");
                            break;
                        case "bool":
                        case "boolean":
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                        case "date":
                            cellType.push("DateTime");
                            cellTypeClass.push("date");
                            break;
                        default:
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;

                    }

                }
            }
        }
        var visibleColumnCount = cellType.length - visibleColumnCountReduction;

        var result = {
            height: 9000,
            width: Math.floor(totalWidthInPixels*30)+50
        };

        // Generate worksheet header details.
        var t = ‘<ss:Worksheet ss:Name="‘ + innertitle + ‘">‘ +
            ‘<ss:Names>‘ +
                ‘<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\‘‘ + innertitle + ‘\‘!R1:R2" />‘ +
            ‘</ss:Names>‘ +
            ‘<ss:Table x:FullRows="1" x:FullColumns="1"‘ +
                ‘ ss:ExpandedColumnCount="‘ + (visibleColumnCount) +
                ‘" ss:ExpandedRowCount="‘ + (innerstore.getCount() + 2) + ‘">‘ +
                colXml +
                ‘<ss:Row ss:Height="38">‘ +
                    ‘<ss:Cell ss:StyleID="title" ss:MergeAcross="‘ + (visibleColumnCount - 1) + ‘">‘ +
                      ‘<ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">‘ +
                        ‘<html:B>‘ + innertitle + ‘</html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" />‘ +
                    ‘</ss:Cell>‘ +
                ‘</ss:Row>‘ +
                ‘<ss:Row ss:AutoFitHeight="1">‘ +
                headerXml +
                ‘</ss:Row>‘;

        // Generate the data rows from the data in the Store
        for (var i = 0, it = innerstore.data.items, l = it.length; i < l; i++) {
            t += ‘<ss:Row>‘;
            var cellClass = (i & 1) ? ‘odd‘ : ‘even‘;
            r = it[i].data;
            var k = 0;
            for (var j=1;j< this.columns.length;j++) {
                if (includeHidden || !this.columns[j].isHidden()) {
                    //debugger;
                    var v = r[this.columns[j].dataIndex];
                    if (typeof this.columns[j].renderer == ‘function‘ && cellType[k] != ‘DateTime‘) {
                        var m = {};
                        v = this.columns[j].renderer(v, m, it[i], i, j, innerstore);
                        var re = /<[^>]+>/g;
                        if (v) {
                            v = v.toString().replace(re, ‘‘);
                        } else {
                            v = ‘‘;
                        }
                    }
                    if (cellType[k] !== "None") {

                        if (!v) {
                            t += ‘<ss:Cell ss:StyleID="‘ + cellClass + ‘"></ss:Cell>‘;
                        } else {
                            t += ‘<ss:Cell ss:StyleID="‘ + cellClass + cellTypeClass[k] + ‘"><ss:Data ss:Type="‘ + cellType[k] + ‘">‘;
                            if (cellType[k] == ‘DateTime‘) {
                                t += v.format(‘Y-m-d\\TH:i:s.000‘); // no space betwen  i: s
                            } else {
                                v = EncodeValue(v);

                                t += v;
                            }
                            t += ‘</ss:Data></ss:Cell>‘;
                        }
                    }
                    k++;
                }
            }
            t += ‘</ss:Row>‘;
        }

        result.xml = t + ‘</ss:Table>‘ +
            ‘<x:WorksheetOptions>‘ +
                ‘<x:PageSetup>‘ +
                    ‘<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />‘ +
                    ‘<x:Footer x:Data="Page &P of &N" x:Margin="0.5" />‘ +
                    ‘<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />‘ +
                ‘</x:PageSetup>‘ +
                ‘<x:FitToPage />‘ +
                ‘<x:Print>‘ +
                    ‘<x:PrintErrors>Blank</x:PrintErrors>‘ +
                    ‘<x:FitWidth>1</x:FitWidth>‘ +
                    ‘<x:FitHeight>32767</x:FitHeight>‘ +
                    ‘<x:ValidPrinterInfo />‘ +
                    ‘<x:VerticalResolution>600</x:VerticalResolution>‘ +
                ‘</x:Print>‘ +
                ‘<x:Selected />‘ +
                ‘<x:DoNotDisplayGridlines />‘ +
                ‘<x:ProtectObjects>False</x:ProtectObjects>‘ +
                ‘<x:ProtectScenarios>False</x:ProtectScenarios>‘ +
            ‘</x:WorksheetOptions>‘ +
        ‘</ss:Worksheet>‘;

        //Add function to encode value,2009-4-21
        function EncodeValue(v) {
            var re = /[\r|\n]/g; //Handler enter key
            v = v.toString().replace(re, ‘
‘);

            return v;
        };

        return result;
    }

});

function ExportExcel(gridPanel,config) {
    if (gridPanel) {
        var tmpExportContent = ‘‘;
        tmpExportContent=gridPanel.getExcelXml(true,config);
            if (Ext.isIE || Ext.isSafari || Ext.isSafari2 || Ext.isSafari3 ) {//在这几种浏览器中才需要
                var fd = Ext.get(‘frmDummy‘);
                if (!fd) {
                    fd = Ext.DomHelper.append(
                            Ext.getBody(), {
                                tag : ‘form‘,
                                method : ‘post‘,
                                id : ‘frmDummy‘,
                                action : ‘exportdata.jsp‘,
                                target : ‘_blank‘,
                                name : ‘frmDummy‘,
                                cls : ‘x-hidden‘,
                                cn : [ {
                                    tag : ‘input‘,
                                    name : ‘exportContent‘,
                                    id : ‘exportContent‘,
                                    type : ‘hidden‘
                                } ]
                            }, true);

                }
                fd.child(‘#exportContent‘).set( {
                    value : tmpExportContent
                });
                fd.dom.submit();
            } else {
                document.location = ‘data:application/vnd.ms-excel;base64,‘ + Base64.encode(tmpExportContent);
            }
    }
}
时间: 12-24

ExtJS:Grid数据导出至excel实例的相关文章

Qt中将QTableView中的数据导出为Excel文件

如果你在做一个报表类的程序,可能将内容导出为Excel文件是一项必须的功能.之前使用MFC的时候我就写过一个类,用于将grid中的数据导出为Excel文件.在使用了QtSql模块后,我很容易的将这个类改写应用在Qt程序中.类的名字叫“ExportExcelObject”.使用起来很简单: [cpp] view plaincopy // 1. declare an object // – fileName Excel 文件路径 // – sheetName Excel 工作表(sheet)名称 /

extjs+Aspose.Cells导出到Excel

1.&=DataSource.Field,&=[DataSource].[Field]是对DataTable和几何类型的引用,将会从当前行开始竖直向下生成多行数据. 2.&=$data:是对变量或数组的引用.数组存在skip,horizontal等属性,具体参见官方网站 3.&=&=动态公式计算:{r}当前行,{c}当前列,{-n},{n}当前行或列的偏移量前n或后n. 4.&==是动态计算,如excel,if等语句.(if(logic_test,true_

支持将数据导出到Excel文档的时候设置单元格格式的.NET控件Spire.DataExport

Spire.DataExport for .NET是e-iceblue公司推出的一款数据导出类.NET控件.作为一款专业的数据导出控件,Spire.DataExport for .NET可以帮助开发人员轻松快速的从各种主流数据库中导出数据并存储于各种文件格式中.他支持从SQL Command, DataTable,ListView中导出数据并存储于MS Excel,MS Word, HTML, XML, PDF, MS Access, DBF, SQL Script, SYLK, DIF, CS

数据导出至Excel文件--好库编程网http://code1.okbase.net/codefile/SerializeHelper.cs_2012122018724_118.htm

using System; using System.IO; using System.Data; using System.Collections; using System.Data.OleDb; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; /// <summary> /// Excel操作类 /// </summary> /// Microsoft Excel 11.0 Obj

tablib把数据导出为Excel、JSON、CSV等格式的Py库(写入数据并导出exl)

#tablib把数据导出为Excel.JSON.CSV等格式的Py库 #python 3 import tablib #定义列标题 headers = ('1列', '2列', '3列', '4列', '5列') #需写入的数据,按照一行一行的输入 #元组数据的个数必须和列数一致 data = [('23','23','34','23','34'),('sadf','23','sdf','23','fsad')] #写入数据 mylist = tablib.Dataset(*data, head

Delphi 数据导出到Excel

好多办公软件特别是财务软件,都需要配备把数据导出到Excel,下面就来介绍两种数据导出方法 1.ADODB导出查询结果(此方法需要安装Excel) 2.二维表数据导出(根据Excel文件结构生成二进制文件流,不需要安装Excel) 3.如果涉及到复杂表头的(例如合并字段等),未做研究,下面也没介绍相关资料,请止步. 第一种,ADODB导出查询结果(此方法需要安装Excel) //uses DB, ADODB, ComObj, ComCtrls, excel2000, StdCtrls, adoi

将数据库的数据导出到excel中

这篇文章主要介绍了asp中把数据导出为excel的2种方法:分别用excel组件.文件组件实现,需要的朋友可以参考下.我们在做项目的时候经常要将数据库的数据导出到excel中,很多asp用户并不知道怎么写.这里总结了两种方法来导出excel,希望能帮到大家. 方法一:用excel组件 方法二:使用文件组件 可以看出,第一种方法是直接导出的是excel文件,而第二张方法是到处的是文本文件,只不过后缀名改成了xls. 然后看起来就是excel了. 经过对比第一种方法的效率没有第二种方法的效率高,而且

C#创建Excel文件并将数据导出到Excel文件

C#创建Excel文件,这里实际上是从资源中提取一个事先创建好的Excel文件,文件提取成功后,使用OleDb方法连接Excel,向Excel文件中写入数据. 创建解决方案 菜单>新建>项目>Windows窗体应用程序: 添加相关组件: 添加两个DataGridView,一个TextBox,两个按钮 ,如下图: 添加Excel资源: 先在文件夹中新建一个Excel文件,在Sheet1表的第一行设置列名: 双击"Resources.resx"文件打开资源文件视图: 添加

使用原生php将数据库数据导出到excel文件中

最近在工作中遇到一个需求,需要将数据库中的数据导出到excel文件中,并下载excel文件.因为以前没做过,所以就百度了一下, 网上说的大多是使用PHPExcel类来操作excel文件,这还要去下载这个类才能使用,而我只想使用原生的php,不想那么麻烦,好在 也有网友说到关于原生php生成excel文件的方法,其实很简单,下面把我结合网上资料自己实践的代码分享一下. 一般我们这种导数据的操作都是通过用户在网页页面上点击某个按钮触发相应js方法,然后请求php接口来实现的,所以主要有两种 方法来完