目前,写了一个将Grid的数据,导出到EXCEL的简单应用,本文中的一些常量,来自我的前一篇:《JS中,操作EXCEL时,常用的常量定义》,本代码需要EXT2.1支持,以下是具体代码实现:
excel_app.js:
- /**
- *
- * Microsoft Excel 应用,导出功能
- * @author 张尧伟 zhangyw@icss.com.cn
- */
- Ext.namespace("Ext.icss");
- Ext.icss.ExcelApp = function() {
- return {
- excelApp : null,
- EXCEL_APP : "Excel.Application",
- /**
- * 创建Excel应用程序对象
- * @param visible{} true or false 表示主程序窗口对象是否可见
- * @param displayAlerts{} true or false 如果不想在宏运行时被无穷无尽的提示和警告消息所困扰,
- * 请将本属性设置为 False;这样每次出现需用户应答的消息时,Microsoft Excel 将选择默认应答。
- * @return Application 对象
- */
- createApp : function(visible, displayAlerts) {
- if (!this.excelApp) {
- this.excelApp = new ActiveXObject(this.EXCEL_APP);
- }
- if (this.excelApp) {
- this.excelApp.Visible = visible;
- this.excelApp.DisplayAlerts = displayAlerts;
- }
- return this.excelApp;
- },
- /**
- * 关闭指定的应用程序对象
- * @param exApp 需要关闭的EXCEL应用程序对象
- */
- closeApp : function(exApp) {
- if (exApp) {
- exApp.Quit();
- }
- },
- /**
- * 在指定的应用程序对象中,创建一个新的工作簿
- * @param exApp EXCEL应用程序对象
- * @return Workbook 对象
- */
- addWorkbook : function(exApp) {
- if (!exApp) {
- return;
- }
- var workbook = exApp.Workbooks.Add();
- if (workbook) {
- workbook.Activate();
- }
- return workbook;
- },
- /**
- * 在指定的workbook对象中,查找索引为index的工作表对象
- * @param workbook Workbook 对象
- * @param index 工作表的索引
- * @return Worksheet 对象
- */
- findWorksheet : function(workbook, index) {
- var worksheet = null;
- if (index) {
- worksheet = workbook.Worksheets(index);
- } else {
- worksheet = workbook.ActiveSheet;
- }
- return worksheet;
- },
- /**
- * 设置指定的单元格值
- * @param cell 单元格对象,取Worksheet.Cells属性
- * @param row cell的行
- * @param col cell的列
- * @param value 值
- */
- setCellValue:function(cell,row,col,value){
- cell(row,col).Value = value;
- },
- /**
- * 返回一个指定开始与结束表格的Range对象
- * @param worksheet Worksheet对象
- * @param startCell eg. cell(1,1)
- * @param endCell eg. cell(10,1)
- * @return Range对象
- */
- getRange:function(worksheet,startCell,endCell){
- return worksheet.Range(startCell, endCell);
- },
- printPreview : function() {
- var visible = this.excelApp.Visible;
- if (this.excelApp.ActiveSheet) {
- if (false == visible) {
- this.excelApp.Visible = true;
- }
- this.excelApp.ActiveSheet.PrintPreview();
- this.excelApp.Visible = visible;
- }
- },
- printOut : function() {
- if (this.excelApp.ActiveSheet) {
- this.excelApp.ActiveSheet.PrintOut();
- }
- },
- close : function() {
- this.closeApp(this.excelApp);
- this.excelApp = null;
- },
- getWorksheet:function(index){
- if(!this.excelApp){
- this.createApp(true,false);
- }
- var workbook = null;
- if(!this.excelApp.ActiveWorkbook){
- workbook = this.addWorkbook(this.excelApp);
- }
- return this.findWorksheet(workbook,index);
- }
- };
- }();
- ExcelApp = Ext.icss.ExcelApp;
- //excel.js:
-
- /**
- * @include "../js/Ext2.1/icss/excel/excel_const.js"
- * @include "../js/Ext2.1/icss/excel/excel_app.js"
- */
- Ext.namespace("Ext.icss");
- /**
- * 数据工具类的基类
- * @author 张尧伟 zhangyw@icss.com.cn
- * @param {} config
- */
- Ext.icss.DataTool = function(config) {
- config = config || {};
- this.initialConfig = config;
- Ext.icss.DataTool.superclass.constructor.call(this);
- };
- Ext.extend(Ext.icss.DataTool,Ext.util.Observable, {
- });
- /**
- * 数据工具类,此类主要实现将表格数据导出到Microsoft Excel功能
- * @param {} config
- */
- Ext.icss.Data2ExcelTool = function(config) {
- config = config || {};
- this.excelApp = Ext.icss.ExcelApp;
- Ext.icss.Data2ExcelTool.superclass.constructor.call(this);
- };
- Ext.extend(Ext.icss.Data2ExcelTool,Ext.icss.DataTool,{
- export2Excel:function(grid,filename){
- this.excelApp.close();
- var worksheet = this.excelApp.getWorksheet(1);
- /*
- var worksheet = {};
- worksheet.Cells = {};
- */
- var cells = this.fillWorksheet(worksheet,grid,filename);
- },
- fillWorksheet : function(worksheet,grid,filename) {
- var cells = worksheet.Cells;
- var cm = grid.getColumnModel();
- var ds = grid.getStore();
- var cols = cm. getColumnCount();
- var rows = ds.getCount();
- this.buildHeader(grid,cells,1,1);
- this.buildSumRow(grid,cells,2,1);
- this.buildBody(grid,cells,3,1);
- rows += 2;
- worksheet.Range(cells(1, 1), cells(rows,cols)).Borders.LineStyle = Excel.XlBorderWeight.xlHairline;
- worksheet.Range(cells(2, 1), cells(2,cols)).Font.Bold = Excel.XlBoolean.True;
- worksheet.Range(cells(1, 1), cells(rows,cols)).Columns.AutoFit();
- return cells;
- },
- /**
- * 根据grid的cm,生成表格表头
- * @param {} grid
- * @param {} cells excel cell对象
- * @param {} row 开始行
- * @param {} col 开始列
- */
- buildHeader:function(grid,cells,row,col){
- var cm = grid.getColumnModel();
- var cfg = null;
- for(var i=0; i<cm.config.length; ++i){
- cfg = cm.config[i];
- if(cfg.id == 'checker')
- {
- this.excelApp.setCellValue(cells,row,col+i,'');
- }else{
- this.excelApp.setCellValue(cells,row,col+i,cfg.header);
- }
- }
- },
- /**
- * 根据GRID的store,导出数据
- * @param {} grid
- * @param {} cells
- * @param {} row
- * @param {} col
- */
- buildBody : function(grid, cells, row, col) {
- var cm = grid.getColumnModel();
- var ds = grid.getStore();
- var cfg = null;
- ds.each(function(rec) {
- for (var i = 0; i < cm.config.length; ++i) {
- cfg = cm.config[i];
- if (cfg.id == 'checker') {
- this.excelApp.setCellValue(cells, row, col + i, '');
- } else {
- var val = rec.get(cfg.dataIndex);
- if(cfg.renderer){
- val = cfg.renderer(val);
- }
- if(val){
- this.excelApp.setCellValue(cells, row, col + i, val );
- }
- }
- }
- row++;
- },this);
- },
- /**
- * 增加合计行
- * @param {} grid
- * @param {} cells
- * @param {} row
- * @param {} col
- */
- buildSumRow:function(grid,cells,row,col){
- var cm = grid.getColumnModel();
- var cfg = null;
- for (var i = 0; i < cm.config.length; ++i) {
- cfg = cm.config[i];
- if(cfg.sumcaption){
- this.excelApp.setCellValue(cells,row,col+i,cfg.sumcaption);
- }
- if(cfg.sumvalue){
- this.excelApp.setCellValue(cells,row,col+i,cfg.sumvalue);
- }
- }
- }
- });
调用示例:
-
- /**
- * 将表格的数据导出到Excel文件
- * @param {} btn
- * @param {} event
- */
- function export2Excel(btn,event)
- {
- var filename = '预计划';
- var data2Excel = new Ext.icss.Data2ExcelTool();
- data2Excel.export2Excel(getGrid(),filename);
- //var ExcelApp.export2Excel(getGrid(),filename);
- }