Spring Web MVC – Create Excel File (Episode 4)

Persiapan:

1. Sudah membaca artikel Spring Web MVC – Create Log File (Episode 3). Jika belum, silahkan baca artikel ini

File-file yang dibutuhkan:

1. jxl-2.6.3.jar — silahkan download disini.

Pada artikel ini saya kan memberikan contoh sederhana – bagaimana cara membuat file excel melalui Spring MVC. Silahkan ikuti langkah2 berikut:

1. Download library di atas, lalu diinclude ke dalam project firstProgram
2. Buat file FirstProgramExcell.java pada package com.firstProgram.report.java :

    package com.firstProgram.report;
   
   import jxl.write.*;
   import jxl.format.Alignment;
   
   import java.util.Map;
   
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;
   
   import org.slf4j.Logger;
   import org.slf4j.LoggerFactory;
   import org.springframework.web.servlet.view.document.AbstractJExcelView;
   
   public class FirstProgramExcell extends AbstractJExcelView {
   
    private static final Logger logger = LoggerFactory.getLogger(FirstProgramExcell.class);
   
    protected void buildExcelDocument(Map model,
    WritableWorkbook workbook,
    HttpServletRequest request,
    HttpServletResponse response) throws Exception {
   
    WritableFont normal = new WritableFont(WritableFont.ARIAL, 9);
    WritableFont bolder = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
   
    WritableCellFormat bolderCenterFormat = new WritableCellFormat(bolder);
    bolderCenterFormat.setAlignment(Alignment.CENTRE);
    bolderCenterFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
   
    WritableCellFormat normalLeftFormat = new WritableCellFormat(normal);
    normalLeftFormat.setAlignment(Alignment.LEFT);
    normalLeftFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
   
    WritableSheet sheet;
   
    sheet = workbook.createSheet("Example", 1);
   
    sheet.setColumnView(0, 20);
    sheet.setColumnView(1, 30);
   
    /*Ini bagian datanya*/
    String firstName = (String) model.get("firstName");
    String lastName = (String) model.get("lastName");
    String speciality = (String) model.get("speciality");
   
    int row = 0;
    int column = 0;
    sheet.addCell(new Label(column, row, "First Name", bolderCenterFormat ));
    sheet.addCell(new Label(column+1, row, firstName, normalLeftFormat ));
   
    row = row + 1;
    sheet.addCell(new Label(column, row, "Last Name", bolderCenterFormat ));
    sheet.addCell(new Label(column+1, row, lastName, normalLeftFormat ));
   
    row = row + 1;
    sheet.addCell(new Label(column, row, "Speciality", bolderCenterFormat ));
    sheet.addCell(new Label(column+1, row, speciality, normalLeftFormat ));
   
    }
   }
   
   

3. Edit file ExcellExampleController.java di package com.firstProgram.web

    package com.firstProgram.web;
   
   import org.slf4j.Logger;
   import org.slf4j.LoggerFactory;
   import org.springframework.stereotype.Controller;
   import org.springframework.web.bind.annotation.RequestMapping;
   import org.springframework.web.bind.annotation.RequestMethod;
   import org.springframework.web.bind.annotation.RequestParam;
   
   import org.springframework.web.servlet.ModelAndView;
   
   import javax.servlet.ServletException;
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;
   
   import java.io.IOException;
   import java.util.HashMap;
   import java.util.Map;
   
   @Controller
   public class ExcellExampleController{
   
    private static final Logger logger = LoggerFactory.getLogger(ExcellExampleController.class);
   
    @RequestMapping (value="/excell.asik", method=RequestMethod.GET)
    public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    return new ModelAndView("pages/excell");
    }
   
    @RequestMapping (value="/excell.xls", method=RequestMethod.POST)
    public ModelAndView generateExcell( @RequestParam(value="firstName",required=false) String firstName
    ,@RequestParam(value="lastName",required=false) String lastName
    ,@RequestParam(value="speciality",required=false) String speciality
    ,HttpServletRequest request
    ,HttpServletResponse response)
    throws ServletException, IOException {
   
    logger.info("First Name : '{}'", firstName);
    logger.info("Last Name : '{}'", lastName);
    logger.info("Speciality : '{}'", speciality);
   
    Map model = new HashMap();
    model.put("firstName", firstName);
    model.put("lastName", lastName);
    model.put("speciality", speciality);
   
    return new ModelAndView("pages/excellReport", model);
    }
   
   }
   
   

4. Edit view.properties

    pages/hello.url = /WEB-INF/pages/hello.jsp
   pages/hello.class= org.springframework.web.servlet.view.JstlView
   
   pages/excell.url = /WEB-INF/pages/excell.jsp
   pages/excell.class= org.springframework.web.servlet.view.JstlView
   
   pages/excellReport.class = com.firstProgram.report.FirstProgramExcell
   

5. Edit excell.jsp

   <!DOCTYPE html PUBLIC
    "-//W3C//DTD XHTML 1.1 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   
   <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
   <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
   
   <html>
    <head>
    <title>Episode 3</title>
    <link href="<c:url value="/css/style.css"/>" rel="stylesheet" type="text/css" />
    </head>
    <body>
    <h1>Please Input:</h1>
    <form:form action="excell.xls" target="_blank">
    <div>
    <label for="firstName">First Name</label>
    <input type="text" id="firstName" name="firstName" value="${firstName}"/>
    </div>
    <div>
    <label for="lastName">Last Name</label>
    <input type="text" id="lastName" name="lastName" value="${lastName}"/>
    </div>
    <div>
    <label for="speciality">Speciality</label>
    <input type="text" id="speciality" name="speciality" value="${speciality}"/>
    </div>
    <div>
    <label>&nbsp;</label>
    <input type="submit" value="Next"/>
    <input type="reset" value="Cancel"/>
    </div>
    </form:form>
    </body>
   </html>
   

6. Testing aplikasi anda. http://localhost:8080/firstProgram/excell.asik
7. Coba masukkan inputan lalu di submit. Jika muncul page save download link seperti dibawah, berarti anda sudah sukses menjalankan nya, jika tidak berarti ada yang salah.

Advertisement