使用jxls导出Excel报表
导入jar包:
<dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-core</artifactId> <version>1.0.5</version> </dependency>
工具类:
import net.sf.jxls.transformer.XLSTransformer; import org.apache.poi.ss.usermodel.Workbook; import org.jxls.common.Context; import org.jxls.expression.JexlExpressionEvaluator; import org.jxls.transform.Transformer; import org.jxls.transform.poi.PoiTransformer; import org.jxls.util.JxlsHelper; import java.io.*; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashMap; import java.util.Map; /** * @author klguang */ public class JxlsUtils { public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException{ Context context = PoiTransformer.createInitialContext(); if (model != null) { for (String key : model.keySet()) { context.putVar(key, model.get(key)); } } JxlsHelper jxlsHelper = JxlsHelper.getInstance(); Transformer transformer = jxlsHelper.createTransformer(is, os); //获得配置 JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator(); //设置静默模式,不报警告 //evaluator.getJexlEngine().setSilent(true); //函数强制,自定义功能 Map<String, Object> funcs = new HashMap<String, Object>(); funcs.put("utils", new JxlsUtils()); //添加自定义功能 evaluator.getJexlEngine().setFunctions(funcs); //必须要这个,否者表格函数统计会错乱 jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer); } public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException { exportExcel(new FileInputStream(xls), new FileOutputStream(out), model); } public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception { File template = getTemplate(templatePath); if(template != null){ exportExcel(new FileInputStream(template), os, model); } else { throw new Exception("Excel 模板未找到。"); } } //获取jxls模版文件 public static File getTemplate(String path){ File template = new File(path); if(template.exists()){ return template; } return null; } // 日期格式化 public String dateFmt(Date date, String fmt) { if (date == null) { return ""; } try { SimpleDateFormat dateFmt = new SimpleDateFormat(fmt); return dateFmt.format(date); } catch (Exception e) { e.printStackTrace(); } return ""; } // if判断 public Object ifelse(boolean b, Object o1, Object o2) { return b ? o1 : o2; } public static void exportExcelByJxls(InputStream is, OutputStream os,Map<String , Object> model) { //模拟数据 Map<String,Object> beans = model; XLSTransformer transformer = new XLSTransformer(); InputStream in=is; OutputStream out=os; try { Workbook workbook=transformer.transformXLS(in, beans); //将内容写入输出流并把缓存的内容全部发出去 workbook.write(out); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { if (in!=null){try {in.close();} catch (IOException e) {}} if (out!=null){try {out.close();} catch (IOException e) {}} } } }
map模板:
Map<String , Object> model=new HashMap<String , Object>(); model.put("maps", listExportMap);
注解:
jx:each(items="maps" var="map" lastCell="I4")
map行格式:
${maps.get("serial")} ${maps.get("wardName")} ${maps.get("pushNum")} ${maps.get("readNum")} ${maps.get("readRate")} ${maps.get("pushPatientNum")} ${maps.get("readPatientNum")} ${maps.get("perCapitaPushNum")} ${maps.get("perCapitaReadNum")}
普通格式:${nowDate}
浏览器下载格式代码:
public void export(HttpServletRequest request,HttpServletResponse response){ String templateFileName= request.getServletContext().getRealPath("/") + "/resources/templateFileName.xls"; String destFileName= "destFileName.xls"; //模拟数据 List<Employee> staff = new ArrayList<Employee>(); staff.add(new Employee("Derek", 35, 3000, 0.30)); staff.add(new Employee("Elsa", 28, 1500, 0.15)); staff.add(new Employee("Oleg", 32, 2300, 0.25)); Map<String,Object> beans = new HashMap<String,Object>(); beans.put("employees", staff); XLSTransformer transformer = new XLSTransformer(); InputStream in=null; OutputStream out=null; //设置响应 response.setHeader("Content-Disposition", "attachment;filename=" + destFileName); response.setContentType("application/vnd.ms-excel"); try { in=new BufferedInputStream(new FileInputStream(templateFileName)); Workbook workbook=transformer.transformXLS(in, beans); out=response.getOutputStream(); //将内容写入输出流并把缓存的内容全部发出去 workbook.write(out); out.flush(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (in!=null){try {in.close();} catch (IOException e) {}} if (out!=null){try {out.close();} catch (IOException e) {}} } } }
类模板: