Exporting Excel and PDF files from EXTJS grid.

Hi All,

Sencha EXTJS as you know is a Rich Javascript platform which supports amazing functionalities with ease. It’s really fun to work on this technology.

But one of the most challenging functionality I have faced is to provide a UI soln. for exporting Excel and PDF files on the go from EXTJS grid on the client side using Javascript only. Well I know some of you might be aware of this feature but this post might be helpful for many.

Well to begin this tutorial will include simplest ways of achieving the desired output (i.e. generation of Excel and a PDF file.)

A> Generation of Excel file from the grid.

I will assume you have already configured your EXTJS project and everything is working fine.

For genaration of excel from EXTJS grid we would be using     —-    Ext.ux.exporter  with adobe Downloadify  for downloading the file .

  1. Download my version of Exporter from SourceForge-> https://sourceforge.net/projects/extjsexcelexport/          >              Files
  2. Once you download it, unpack the entire contents in “/extjs/ux”  folder.
  3. If you have a parent JSP file or HTML file where you already have included the ext-all-debug.js, include the following entries.              <script type=”text/javascript” src=”extjs/ux/exporter/Exporter.js”></script>
    <script type=”text/javascript” src=”extjs/ux/exporter/swfobject.js”></script>
    <script type=”text/javascript” src=”extjs/ux/exporter/downloadify.min.js”></script>
  4. Now, if you have a parent JS file like Application.js include  the following lines at the top                                                             Ext.Loader.setConfig({ enabled : true });
    Ext.Loader.setPath(‘Ext.ux’, ‘extjs/ux’);
  5. Once that is done, you can directly use the  Exporter button widget in the docked items of the Grid.{
    xtype: ‘pagingtoolbar’,
    store: ‘MyProject.CountryStore’,
    margin:’0 0 0 10′,
    id :’countryPaging’,
    cls :’paginationToolbar’,
    items: [
    {
    xtype: ‘exporterbutton’
    }]
    }

We are done…Please modify the Worksheet.js file which I have customized as per my project requirements, where in we had to include the search criteria. If you dont need that you can eliminate the buildSearchParams, method. Instead write your own logic here.

 

 B> Generation of PDF file from the grid.

We will be using JSPDF for this. JSPDF is a third party tool, which is developed by parallax software labs. Though it is stable I could not get it to working for generation of PDF from HTML. It is still in development phase.

JSPDF works with JQuery, EXTJs and Jqeuery usage simultaneously is not recommended in my project.

The only feature of JSPDF that works for me, is normal text generation and drawing lines.

What I did is I converted the Grid data into Arrays, i.e.      gridData = [{[cell1],[cell2]…}, {[cell1],[cell2].. }, … ]  likewise.

Now, iterate the array and manually position the elements into PDF.

var doc = new jsPDF(‘p’, ‘pt’, ‘a1’, true);

Ext.each(data, function(row, i){
       if(countRow%(maxRowsOnPage) == 0 && i != 0 ){

    //adding a new pager after max no. of rows on page         

            doc.addPage();
             pageNo ++;
            countRow = 0;
            doc.setDrawColor(0,0,0);
            doc.line(35, 125, 250*(cols), 125);
       }
       Ext.each(row, function(cell, j){
             doc.text(45+(j*200), 145+(countRow*30), cell ) ;

            //vertical lines
          doc.line(35+(j*200), 125+(countRow*30), 35+(j*200), 155+(countRow*30) );

        }

                      //Last vertical lines
         doc.line(50+((cols+1)*200), 125+(countRow*30), 50+((cols+1)*200), 155+(countRow*30) );
         //row horizontal lines
         doc.line(35, 155+(countRow*30), 250*cols , 155+(countRow*30));
});

 doc.save(‘GridExport.pdf’);

 

You can put this piece of code inside the listener   handler : function() {} of the PDF icon in your paging toolbar.

One more thing for this to work, download jsPDF from gitHub –> https://github.com/MrRio/jsPDF and  only include these two files in your parent JSP, thats it.

 <script type=”text/javascript” src=”extjs/jsPDF/dist/jspdf.debug.js”></script>
<script type=”text/javascript” src=”extjs/jsPDF/jspdf.js”></script>

 

 

Hope this solves your problems…keep posting your comments and let me know if this post is of any use to you.

For any queries please write to me on subhadip0608@gmail.com

Advertisements

One thought on “Exporting Excel and PDF files from EXTJS grid.

  1. function _getFieldText(fieldData) {
    var text;
    if(fieldData == '' ||
    fieldData == '' ||
    fieldData == '' ||
    fieldData == ''){
    text = '\u2714'
    }
    if (fieldData == null || fieldData == undefined) {
    text = '';

    } else if (fieldData._refObjectName && !fieldData.getMonth) {
    text = fieldData._refObjectName;

    } else if (fieldData instanceof Date) {
    text = Ext.Date.format(fieldData, this.dateFormat);

    } else if (!fieldData.match) { // not a string or object we
    // recognize...bank it out
    text = '';

    } else {
    text = fieldData;
    }

    return text;
    }

    function getCSV(grid) {
    var cols = grid.columns;
    var store = grid.store.snapshot;
    var data = [];
    var textData = [];
    var columns = [];
    var CSV = '';
    var colData = '';
    //var ReportTitle = "Report";
    // CSV += ReportTitle + '\r\n\n';
    var that = this;
    Ext.Array.each(cols, function(col, index) {
    if (col.hidden != true) {
    columns.push(that._getFieldTextAndEscape(col.text));
    }
    });
    textData.push(columns);
    store.each(function(record) {
    var entry = record.getData();
    var data = [];
    Ext.Array.each(cols, function(col, index) {
    if (col.hidden != true) {
    var fieldName = col.dataIndex;
    var text = entry[fieldName];
    if(fieldName == 'dfltSecLdgrAccnTyp' && text==true) {
    text = 'Y';
    }
    if(fieldName == 'dfltSecClntAccTyp' && text==true) {
    text = 'Y';
    }
    if(fieldName == 'dfltCshLdgrAccnTyp' && text==true) {
    text = 'Y';
    }
    if(fieldName == 'dfltCshClntAccTyp' && text==true) {
    text = 'Y';
    }
    data.push(that._getFieldTextAndEscape(text));
    }
    });
    textData.push(data);
    });

    return textData;
    }

    function _getFieldTextAndEscape(fieldData) {
    var string = this._getFieldText(fieldData);

    return this._escapeForCSV(string);
    }
    function _ieGetGridData(grid, sheet) {
    var that = this;
    var resourceItems = grid.store.data.items;
    var cols = grid.columns;

    Ext.Array.each(cols, function(col, colIndex) {
    if (col.hidden != true) {
    //console.log('header: ', col.text);
    sheet.cells(1, colIndex + 1).value = col.text;
    }
    });

    var rowIndex = 2;
    grid.store.each(function(record) {
    var entry = record.getData();

    Ext.Array.each(cols, function(col, colIndex) {
    if (col.hidden != true) {
    var fieldName = col.dataIndex;
    var text = entry[fieldName];
    var value = that._getFieldText(text);

    sheet.cells(rowIndex, colIndex + 1).value = value;
    }
    });
    rowIndex++;
    });
    }

    function _escapeForCSV(string) {
    if (string.match(/,/)) {
    if (!string.match(/"/)) {
    string = '"' + string + '"';
    } else {
    string = string.replace(/,/g, ''); // comma's and quotes-- sorry,
    // just loose the commas
    }
    }
    return string;
    }

    function _ieToExcel(grid) {
    //console.log(window.ActiveXObject);
    if (window.ActiveXObject) {
    var xlApp, xlBook;
    try {
    xlApp = new ActiveXObject("Excel.Application");
    xlBook = xlApp.Workbooks.Add();
    } catch (e) {
    Ext.Msg
    .alert(
    'Error',
    'For the export to work in IE, you have to enable a security setting called "Initialize and script ActiveX control not marked as safe" from Internet Options -> Security -> Custom level..."');
    return;
    }

    xlBook.worksheets("Sheet1").activate;
    var XlSheet = xlBook.activeSheet;
    xlApp.visible = true;

    this._ieGetGridData(grid, XlSheet);
    XlSheet.columns.autofit;
    }
    }
    function convertParamForPresentation(param) {
    var paramMap = param.split(":");
    var paramKey = getSearchKey(paramMap[0]);
    var value = paramMap[1];
    if (NWMRuntimeVariables.currentGridId == 'providersGrid') {
    if (value == 'A') {
    value = 'Active';
    } else if (value == 'I') {
    value = 'Inactive';
    } else if (value == 'D') {
    value = 'Discard';
    }
    } else if (NWMRuntimeVariables.currentGridId == 'accountGrid') {
    if (value == 'A') {
    value = 'Active';
    } else if (value == 'I') {
    value = 'Inactive';
    } else if (value == 'D') {
    value = 'Dormant';
    } else if (value == 'O') {
    value = 'Others';
    }

    else if (value == 'CE') {
    value = 'Cash External';
    } else if (value == 'CL') {
    value = 'Cash Ledger';
    }
    }
    var excelSearchCiteria = paramKey + ' : ' + value;
    return excelSearchCiteria;
    }

    function getSearchKey(param) {
    //console.log('getSearchKey > param: '+param);
    var form = Ext.getCmp(NWMRuntimeVariables.runTimeFormName);
    var eleLabel = '';
    form.query('.field').forEach(function(c) {
    if (param == c.name) {
    eleLabel = c.fieldLabel;
    }
    });
    return eleLabel;
    }

    function generatePdf(grid, extraParamName) {
    var store = grid.store.snapshot;
    var data = getCSV(grid);
    var doc = new jsPDF(‘p’, ‘pt’, ‘a1’, true);
    var searchParams = JSON.stringify(extraParamName);
    searchParams = searchParams.split(“{“).join(“”).replace(/[“]/g, “”)
    .split(“}”).join(“”).split(“\\”).join(“”);
    var searchParamsArray = [];
    searchParamsArray = searchParams.split(“,”);
    doc.setFontType(“bold”);
    doc.text(40, 90, “Search Criteria”);
    doc.text(40, 110, “———————“);
    var lastSearchParamYCoordinate = 0;
    for (var k = 0; k < searchParamsArray.length; k++) {
    var paramMap = searchParamsArray[k].split(":");
    var paramKey = getSearchKey(paramMap[0]);
    var value = paramMap[1];
    gridName = grid.itemId;
    if (gridName == 'providersGrid') {
    if (value == 'A') {
    value = 'Active';
    } else if (value == 'I') {
    value = 'Inactive';
    } else if (value == 'D') {
    value = 'Discard';
    }
    } else if (gridName == 'accountGrid') {
    if (value == 'A') {
    value = 'Active';
    } else if (value == 'I') {
    value = 'Inactive';
    } else if (value == 'D') {
    value = 'Dormant';
    } else if (value == 'O') {
    value = 'Others';
    }

    else if (value == 'CE') {
    value = 'Cash External';
    } else if (value == 'CL') {
    value = 'Cash Ledger';
    }
    }

    var pdfSearchCiteria = paramKey + ' : ' + value;
    doc.setFontType("bold");
    lastSearchParamYCoordinate = 130 + (k * 18);
    doc.text(40, lastSearchParamYCoordinate, pdfSearchCiteria);
    }
    doc.setFontType("bold");
    doc.setFontSize(22);
    doc.setTextColor(0, 0, 0);
    doc.text(650, 70, NWMRuntimeVariables.currentGridName);
    var countRow = 0;
    var maxCount = 70;
    doc.setFontType("normal");
    doc.setFontSize(12);
    doc.setDrawColor(0, 0, 0);
    var cols = (data[0].length) – 1;
    var multiplyer = parseInt(1550 / (cols + 1));
    var posX = 50;
    var posY = 55;
    var posYOnPage2 = 145;
    var rowSeparation = 40;
    var cellWidth = multiplyer;
    //top horizontal line
    doc.line(40, 30 + lastSearchParamYCoordinate, 1600, 30
    + lastSearchParamYCoordinate);

    var pageRow = 55;
    var pageNo = 1;
    var maxRowsOnFirstPage = pageRow – searchParamsArray.length – 2;

    Ext.each(data, function(row, i) {
    if (pageNo == 1) {
    if (countRow % (maxRowsOnFirstPage) == 0 && i != 0) {
    doc.addPage();
    pageNo++;
    countRow = 0;
    doc.setDrawColor(0, 0, 0);
    doc.line(40, 125, 1600, 125);
    }
    } else {
    if (countRow % pageRow == 0 && i != 0) {
    doc.addPage();
    pageNo++;
    countRow = 0;
    doc.setDrawColor(0, 0, 0);
    doc.line(40, 125, 1600, 125);
    }
    }
    doc.setTextColor(0, 0, 255);
    Ext.each(row, function(cell, j) {
    if (i == 0) {
    doc.setFontType("bold");
    doc.setFontSize(14);
    doc.setTextColor(0, 0, 255);
    } else {
    doc.setFontType("normal");
    doc.setFontSize(12);
    doc.setTextColor(0, 0, 0);
    }
    cell = addNewlines(cell, cellWidth, cols);
    if (pageNo == 1) {
    doc.text(posX + (j * multiplyer),
    (posY + lastSearchParamYCoordinate – 5)
    + (countRow * rowSeparation),
    cell);
    //vertical line
    doc.line((posX – 10) + (j * multiplyer),
    (posY – 25)
    + lastSearchParamYCoordinate
    + (countRow * rowSeparation),
    (posX – 10) + (j * multiplyer),
    (posY + 15)
    + lastSearchParamYCoordinate
    + (countRow * rowSeparation));
    } else {
    doc.text(posX + (j * multiplyer),
    (posYOnPage2 – 5)
    + (countRow * rowSeparation),
    cell);
    //vertical lines
    doc.line((posX – 10) + (j * multiplyer),
    (posYOnPage2 – 25)
    + (countRow * rowSeparation),
    (posX – 10) + (j * multiplyer),
    (posYOnPage2 + 20)
    + (countRow * rowSeparation));
    }
    });
    doc.setDrawColor(0, 0, 0);
    if (pageNo == 1) {
    //end vertical lines
    doc.line(posX + ((cols + 1) * multiplyer), 30
    + lastSearchParamYCoordinate
    + (countRow * rowSeparation), posX
    + ((cols + 1) * multiplyer), 75
    + lastSearchParamYCoordinate
    + (countRow * rowSeparation));
    //row horizontal lines
    doc.line((posX – 10), 70 + lastSearchParamYCoordinate
    + (countRow * rowSeparation), 1600, 70
    + lastSearchParamYCoordinate
    + (countRow * rowSeparation));
    } else {
    //end vertical lines
    doc.line(posX + ((cols + 1) * multiplyer),
    (posYOnPage2 – 20) + (countRow * rowSeparation),
    posX + ((cols + 1) * multiplyer),
    (posYOnPage2 + 20) + (countRow * rowSeparation));
    //row horizontal lines
    doc.line((posX – 10), (posYOnPage2 + 20)
    + (countRow * rowSeparation), 1600,
    (posYOnPage2 + 20) + (countRow * rowSeparation));
    }
    countRow++;
    });
    fileName = NWMRuntimeVariables.currentGridName + '.pdf';
    doc.save(fileName);

    }

    function setExportSpecificVariables(grid, currentGridName, runTimeFormName) {
    NWMRuntimeVariables.currentGridName = currentGridName;
    NWMRuntimeVariables.runTimeFormName = runTimeFormName;
    // NWMRuntimeVariables.currentGridId = "countryGrid";

    var searchParams = JSON.stringify(grid.store.proxy.extraParams);
    searchParams = searchParams.split("{").join("").replace(/["]/g, "")
    .split("}").join("");
    params = searchParams.split(",");
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s