POI导出Excel模板工具类(使用jxls)

引入jar:
<dependency>
    <groupId>net.sf.jxls</groupId> 
    <artifactId>jxls-core</artifactId> 
    <version>1.0.6</version> 
</dependency>






工具类:
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.core.io.ClassPathResource;


import net.sf.jxls.transformer.XLSTransformer;


public class SheetUtils {


public static void exportById(OutputStream os,Object obj,String mark){
HSSFWorkbook wb = null;
try {
// 创建工作簿;
wb = new HSSFWorkbook(new ClassPathResource("template/export_emp.xls").getInputStream());
// 数据模型
Map<String,Object> model = new HashMap<String,Object>();
model.put(mark, obj);
XLSTransformer xlsTrans = new XLSTransformer();
xlsTrans.transformWorkbook(wb, model);
wb.write(os);
}catch(Exception e){
e.printStackTrace();
}finally {
if(null != wb){
try {
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static void exportList(OutputStream os,@SuppressWarnings("rawtypes") List list,String mark,String modelPath){
HSSFWorkbook wb = null;
try {
// 创建工作簿;
wb = new HSSFWorkbook(new ClassPathResource(modelPath).getInputStream());
// 数据模型
Map<String,Object> model = new HashMap<String,Object>();
model.put(mark, list);
XLSTransformer xlsTrans = new XLSTransformer();
xlsTrans.transformWorkbook(wb, model);
wb.write(os);
}catch(Exception e){
e.printStackTrace();
}finally {
if(null != wb){
try {
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}


业务层调用:
@Override
public void export(OutputStream os,Emp emp1) throws Exception {
// 查询所有的员工;
List<Emp> emplist = empDao.getList(emp1, null, null);

for (Emp e : emplist) {
if(e.getGender()==0){
e.setSex("男");
}else{
e.setSex("女");
}
}
SheetUtils.exportList(os, emplist, "emplist","template/export_emp.xls");
 //这是未抽取为工具类时的代码
/*HSSFWorkbook wb = null;
try {
// 创建工作簿;
wb = new HSSFWorkbook(new ClassPathResource("template/export_emp.xls").getInputStream());

// 数据模型
Map<String,Object> model = new HashMap<String,Object>();
model.put("emplist", emplist);

XLSTransformer xlsTrans = new XLSTransformer();
xlsTrans.transformWorkbook(wb, model);
wb.write(os);
} finally {
if(null != wb){
wb.close();
}
}*/
}


控制层:
public void export() throws UnsupportedEncodingException{
HttpServletResponse res = ServletActionContext.getResponse();
String filename = new String("员工表.xls".getBytes(),"iso-8859-1");
try {
// 告诉浏览器，有个名称为XXX附件，下载文件， ISO-8859-1转码，否则下载下来的文件名为乱码
res.setHeader("Content-Disposition", "attachment;filename=" + filename);
empBiz.export(res.getOutputStream(),getT1());
}  catch (Exception e) {
log.error("导出文件失败",e);
}
}


excel模板:



<jx:forEach items="${emplist}" var="emp" varStatus="status">
${emp.uuid} ${emp.username} ${emp.name} ${emp.sex} ${emp.email} ${emp.tele} ${emp.address} ${emp.birthday} ${emp.dep.name}

</jx:forEach>

excel自定义时间格式:yyyy/mm/dd hh:mm