Exporting Kendo UI GRID to Excel with Java

This is a simple method of creating an Excel export function on your Kendo UI grid with a Java EE server.

JS function to trigger Excel export

In your web-page with the grid - insert this JS function. You should create a button or similar that calls this when you want to perform the export from the UI.

 function exportGridToExcel() {  
     var grid = $("#grid").data("kendoGrid");    
     var currentPage = grid.dataSource.page();  
     var allPages = new Array();  
     for(var n=1;n<=grid.dataSource.totalPages();n++) {  
       grid.dataSource.page(n);  
       var view = grid.dataSource.view();  
       for(var x=0;x<view.length;x++) {  
         allPages.push(view[x]);  
       }  
     }  
     $("#excelExportGridData").val(JSON.stringify({"rows": allPages, "cols": grid.columns}));  
     $("#exportToExcelForm").submit();  
     grid.dataSource.page(currentPage);  
   }  

Hidden form and iframe

You also need a hidden form and iframe on your page. This is posted to the servlet below - and the hidden iframe acts as a target for the form.

 <form id="exportToExcelForm" action="KendoGridExcelServlet" method="POST" target="exportToExcelHiddenFrame">  
       <input type="hidden" id="excelExportGridData" name="griddata" />  
     </form>  
     <iframe name="exportToExcelHiddenFrame" style="display: none;">  </iframe>  

Java servlet

The Java servlet is using Apache POI and Java JSON libraries from json.org. You should name it KendoGridExcelServlet or what you've set for the action for the hidden form above. The code below is only the method for processing the request, you should insert this into your servlet code template.

 protected void processRequest(HttpServletRequest request, HttpServletResponse response)  
       throws ServletException, IOException {  
     request.setCharacterEncoding("UTF-8");      
     OutputStream out = response.getOutputStream();  
     try {  
       JSONObject gridobj = new JSONObject((String)request.getParameter("griddata"));  
       JSONArray rowsArr = gridobj.getJSONArray("rows");      
       JSONArray columnsArr = gridobj.getJSONArray("cols");     
       ArrayList<String> shownFieldsList = new ArrayList<String>();  
       ByteArrayOutputStream baos = new ByteArrayOutputStream();  
       Workbook wb = new XSSFWorkbook();  
       CellStyle headerStyle = wb.createCellStyle();  
       Font font = wb.createFont();  
       font.setBoldweight(Font.BOLDWEIGHT_BOLD);  
       headerStyle.setFont(font);  
       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");  
       CreationHelper createHelper = wb.getCreationHelper();  
       CellStyle dateCellStyle = wb.createCellStyle();  
       dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm"));  
       Sheet sh = wb.createSheet();  
       Row headerRow = sh.createRow(0);  
       headerRow.setHeight((short)(headerRow.getHeight()*2));  
       int colIndex = 0;  
       for(int n=0;n<columnsArr.length();n++) {  
         JSONObject o = columnsArr.getJSONObject(n);  
         if(!o.has("hidden") || o.getBoolean("hidden")==Boolean.FALSE) {  
           shownFieldsList.add(o.getString("field"));       
           Cell cell = headerRow.createCell(colIndex);  
           cell.setCellValue(o.getString("title"));  
           cell.setCellStyle(headerStyle);  
            if(o.has("width") && !"null".equals(o.getString("width"))) {  
             sh.setColumnWidth(colIndex,32*Integer.parseInt(o.getString("width")));  
           } else {  
             sh.setColumnWidth(colIndex,256*15);  
           }  
           colIndex++;  
         }  
       }  
       Pattern datePattern = Pattern.compile("[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]Z");  
       SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
       int rowIndex = 1;  
       for(int n=0;n<rowsArr.length();n++) {  
         JSONObject rowObj = rowsArr.getJSONObject(n);  
         Row row = sh.createRow(rowIndex++);  
         colIndex = 0;  
         for(String field : shownFieldsList) {  
           Cell cell = row.createCell(colIndex++);  
           if(rowObj.has(field)) {  
             Object obj = rowObj.get(field);  
             if(String.class.isInstance(obj)) {  
               if(datePattern.matcher((String)obj).find()) {  
                 cell.setCellValue(dateFormat.parse(((String)obj).replace("T", " ").substring(0,"yyyy-MM-dd HH:mm:ss".length())));  
                 cell.setCellStyle(dateCellStyle);  
               } else {  
                 cell.setCellValue((String)obj);  
               }  
             } else if(Number.class.isInstance(obj)) {  
               cell.setCellValue(((Number)obj).doubleValue());  
             } else if(Date.class.isInstance(obj)) {  
               cell.setCellValue((Date)obj);  
               cell.setCellStyle(dateCellStyle);  
             }  
           }  
         }  
       }  
       wb.write(baos);  
       String filename = "KendoGrid.xlsx";  
       response.setHeader("Content-Disposition", "attachment; filename=\""+filename+"\"");  
       response.setContentType(URLConnection.guessContentTypeFromName(filename));  
       response.setDateHeader("Last-Modified",new Date().getTime());  
       response.setContentLength((int) baos.size());  
       out.write(baos.toByteArray());    
     } catch(Exception e) {  
       throw new ServletException(e);  
     } finally {  
       out.close();  
     }  
   }