ExcelUtils User's Guide

User Guide

Create a ExcelUtils template in excel sheet:

${printDate}
${model.name}
#foreach detail in ${list}
${detail.name}  ${detail.qty} ...
#end
#formula SUM(C${detailStartRowNo}:C${detailEndRowNo})
#each ${model}
#each ${model} on ${keys}
#each ${model} ${width1},${width2}... on ${keys}
#sum qty on ${list} where name=test
#sum qty on ${list} where name like test
#sum qty on ${list} where name like ${value}
#call service.getStr("str",${aaa})
#call service.getModel("str",${aaa}).name
#formula SUM(C${currentRowNo}:F${currentRowNo})

Create a java file extend strtus action:

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import net.sf.excelutils.ExcelUtils;
import net.sf.excelutils.demo.bo.Model;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;


public class ExcelReportAction extends Action {

public ActionForward execute(ActionMapping mapping, ActionForm form,
javax.servlet.http.HttpServletRequest request,
javax.servlet.http.HttpServletResponse response)
throws java.lang.Exception { 

    Model model = new Model();
    model.setUser("aaa");
    model.setName("bbb");
    model.setQty(123.234);

    List details = new ArrayList();
    ...
    ExcelUtils.addValue("printDate", getCurrentDate("yyyy-MM-dd"));
    ExcelUtils.addValue("model", model);
    ExcelUtils.addValue("list", list);
    ExcelUtils.addSerivce("service", service);

    String config = "/WEB-INF/xls/demo.xls";
    response.reset();
    response.setContentType("application/vnd.ms-excel");
    ExcelUtils.export(getServlet().getServletContext(),
                          config,response.getOutputStream());
    return null;
  }

  protected String getCurrentDate(String pattern) {
    SimpleDateFormat format = new SimpleDateFormat(pattern);
    return format.format(new Date());
  }
}

Or create a java file extend webwork action:
ĄĄ

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import net.sf.excelutils.demo.bo.Model;
import com.opensymphony.xwork.ActionSupport;

public class ExcelResultAction extends ActionSupport {

  private Model model;
  private List list;
  private Date printDate;
  private Object service;

  public String execute() {
    model = new Model();
    model.setUser("aaa");
    model.setQty(123.234);

    List details = new ArrayList();
    ...
    return SUCCESS;
  }

  public List getList() {
    return list;
  }

  public void setList(List list) {
    this.list = list;
  }

  public Model getModel() {
    return model;
  }

  public void setModel(Model model) {
    this.model = model;
  }
  public Date getPrintDate() {
    return printDate;
  }
  public void setPrintDate(Date printDate) {
    this.printDate = printDate;
  }
  public Object getService() {
    return yourservice;
  }
}

Your result type of the webwork action must be defined excel Type in xwork.xml to export excel by a template. Template is definde in xwork.xml.

<result-types>
  <result-type name="excel" 
               class="net.sf.excelutils.webwork.ExcelResult"/>
</result-types>

<action name="excelResult" 
          class="net.sf.excelutils.demo.action.ExcelResultAction">
  <result name="success" type="excel">
    <param name="location">/WEB-INF/xls/demo.xls</param>
  </result></action>pre>

Set your classpath include jar below:
ĄĄ
excelutils.jar

poi-2.5.1.jar

commons-logging.jar

commons-digester.jar

commons-beanutils.jar

You should see:

  Access the struts action, you will see a excel report.

You can custom your own tag for ExcelUtils:

 implements ITag interface
 call ExcelUtils.registerTagPackage to register the package name of your CustomTag.

ExcelUtils

an excel report template engine for Java

User Guide