SSM POI根据预定好的模版导出execle

建立模版文件，xls格式。打包为template.zip，放入项目根目录的zip包下

Controller

import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import com.jk.danganweihu.entity.Dqxx;import com.jk.danganweihu.entity.JbXX;import com.jk.danganweihu.service.RensdataService;import com.jk.util.POIExportExcel;import com.jk.util.PagerEntity;

@RequestMapping("out2Execle")@ResponseBodypublic boolean out2Execle(HttpServletRequest request,String id){//FilesImg fileImg=filesImgService.findBinaryByFid("5b049e95bece499086439ad38ba49118");JbXX jbxx = rensdataService.queryJbData(id);Map<String,Object> map=new HashMap<String,Object>();map.put("zwbh", jbxx.getcBh());//职位编号map.put("name", jbxx.getcName());//姓名map.put("sex", jbxx.getcXb());//性别map.put("gzdw", jbxx.getcGzdw());//工作单位map.put("oldName", jbxx.getcCym());//曾用名map.put("mz", jbxx.getcMz());//民族map.put("bmlb", jbxx.getcBmlb());//部门类别map.put("gblb", jbxx.getcGblb());//干部类别//map.put("age", "26");//注意模版一定要是xls格式Map<String, String> impOrSaveExcel = POIExportExcel.ImpOrSaveExcel("basicPersonalInfo.xls", "基本人员信息.xls", map, request);if (impOrSaveExcel!=null) {return true;}else {return false;}}

Util

package com.jk.util;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.sql.Blob;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import javax.servlet.http.HttpServletRequest;import org.apache.commons.lang3.StringUtils;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import com.thunisoft.summer.component.exportoexcel.ExportToExcel;import com.thunisoft.summer.component.exportoexcel.IDataApplier;import com.thunisoft.summer.component.exportoexcel.applier.NamesApplier;import com.thunisoft.summer.component.exportoexcel.template.TemplateManager;/** * tools. *  * @author Shangfeng * ████需要引入华宇的summer-cmpt-exporttoexcel-1.0.7████ */public class POIExportExcel  {    /**     * 日志.     */    private static final Log LOG = LogFactory.getLog(POIExportExcel.class);    /**     * 获得文件路径.     *      * @return     * @throws Exception     */    public String getExcelUrl(String key) throws Exception {        InputStream inputStream = null;        String url = "";        try {            inputStream = this.getClass().getResourceAsStream(                "/props/config.properties");            Properties p = new Properties();            p.load(inputStream);            url = p.getProperty(key);        } catch (IOException io) {            throw io;        } finally {            try {                inputStream.close();            } catch (Exception e) {                throw e;            }        }        return url;    }    /**     * 读取报表Excel文档.     *      * @return 页面     */    public String getExcelDocument(HttpServletRequest request) {        String returnName = "";        returnName = "openReport";        String httpUrl = request.getProtocol()                .substring(0, Integer.valueOf("4")).toLowerCase()                + "://"                + request.getLocalAddr()                + ":"                + request.getLocalPort()                + request.getContextPath();        returnName = httpUrl + "/excel/";        return returnName;    }    /**     * @param rows     *            Excel要装载的数据     * @param cellNum     *            Excel的列数     * @param modelName     *            加载模板的名称带文件格式     * @param excelName     *            所要存储的模板名称带文件格式     * @param res     *            要填充cell的map对象     * @return 返回Object对象     */    @SuppressWarnings("unchecked")    public static Map<String, String> ImpOrSaveExcel(String modelName,            String excelName, Map res,HttpServletRequest request) {        Map<String, String> result = new HashMap();        // 装载excel        if (excelName == null || "".equals(excelName)) {            excelName = "temp.xls";        }        //获取项目的访问路径        String contextPath = request.getContextPath();         String url=request.getScheme()+"://"+request.getServerName()+":"+                request.getServerPort()+contextPath+"/";        String filePath = "";        try {        //生成Excel要存放的服务器路径//          filePath =  request.getSession().getServletContext().getRealPath("/fkjgExcel");            filePath = "F:/";            File tempDir = new File(filePath);            if (!tempDir.exists()) {                tempDir.mkdirs();            }        } catch (Exception e1) {            LOG.error("文件路径未找到", e1);            result.put("success", "false");            result.put("msg", "文件路径未找到");            return result;        }        // 拼装后Excel保存服务器路径        String excUrl = filePath + "\\" + excelName;        // 加载模板，只支持xls格式，自定义模版路径████████        HSSFWorkbook wb = TemplateManager.getInstance().getWorkbook(            "/zip/template.zip", modelName);        HSSFSheet hssfSheet = wb.getSheetAt(0);//        HSSFPatriarch patriarch = hssfSheet.createDrawingPatriarch();        //(0,0,0,0设置图片据边框线的距离)        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 7, 0, (short) 11, 4);             anchor.setAnchorType(3);           //插入图片    //        patriarch.createPicture(anchor, wb.addPicture(tu, HSSFWorkbook.PICTURE_TYPE_JPEG));         NamesApplier na = new NamesApplier(res);        IDataApplier[] daa = new IDataApplier[] { na };        // 执行导出        ExportToExcel.exportTo(wb, daa);        // 结果输出        try {        url = url+"/fkjgExcel/" + excelName;            FileOutputStream fs = new FileOutputStream(excUrl);            wb.write(fs);            fs.close();            result.put("success", "true");            result.put("url", url);            return result;        } catch (IOException e) {            LOG.error("文件加载失败", e);            result.put("success", "false");            result.put("msg", "文件加载失败");            return result;        }    }    /**     * 生成PDF文件     * @param jl     *///    public void getPdf(TRsglJlryxx jl) {//        ByteArrayOutputStream bos = new ByteArrayOutputStream();//        try {//            String url = null;//            try {//                url = getExcelUrl("rsgl.zpry.pdfurl");//            } catch (Exception e) {//                LOG.error("获取文件路径出错--", e);//            }//            String TemplatePDF = getExcelUrl("rsgl.pdf.mb");//            try {//                PdfReader reader = new PdfReader(TemplatePDF);//                PdfStamper stamp = new PdfStamper(reader, bos);//                AcroFields form = stamp.getAcroFields();//                form.setField("xm", jl.getCName());//                form.setField("sfz", jl.getCZjhm());//                form.setField("zkz", jl.getCZkzh());//                if (jl.getCZp() != null && !"".equals(jl.getCZp())) {//                    TRsglFjxx fjxx = (TRsglFjxx) this.getHibernateTemplate()//                            .get(TRsglFjxx.class, jl.getCZp());//                    if (fjxx != null) {//                        Blob zp = fjxx.getLContent();//                        if (zp != null) {//                            Image img = Image.getInstance(zp.getBytes(1,//                                (int) zp.length()));//                            img.setDpi(100, 100);//                            img.setBorderWidth(200);//                            img.scaleAbsolute(80, 100);//                            img.setAbsolutePosition(442, 630);//                            PdfContentByte over = stamp.getOverContent(1);//                            over.addImage(img);//                        }//                    }//                }//                stamp.setFormFlattening(true);//                stamp.close();//                FileOutputStream fos = new FileOutputStream(url + "\\"//                        + jl.getCZkzh() + "-" + jl.getCName() + ".pdf");//                fos.write(bos.toByteArray());//                fos.close();//            } catch (Exception e) {//                LOG.error("生成PDF出错--", e);//            }//        } catch (Exception e) {//            LOG.error("生成PDF出错----", e);//        }//    }    /**     * 生成和下载zip文件      * @param jl     *///    public String getZip(List<TRsglJlryxx> jl) {//        String url = null;//        System.setProperty("sun.zip.encoding",//            System.getProperty("sun.jnu.encoding"));//        String resurl = null;//        try {//            url = getExcelUrl("rsgl.zpry.pdfurl");//            File file = new File(url);//            resurl = url + File.separator + "test001.zip";//            File zipFile = new File(resurl);//            InputStream input = null;//            ZipOutputStream zipOut = new ZipOutputStream(new FileOutputStream(//                    zipFile));//            zipOut.setComment(file.getName());//            zipOut.setEncoding(System.getProperty("sun.jnu.encoding"));//            if (file.isDirectory()) {//                for (int i = 0; i < jl.size(); ++i) {//                    TRsglJlryxx jlxx = jl.get(i);//                    String filename = jlxx.getCZkzh() + "-" + jlxx.getCName();//                    input = new FileInputStream(url + "\\" + filename + ".pdf");//                    zipOut.putNextEntry(new ZipEntry(file.getName()//                            + File.separator + filename + ".pdf"));//                    int temp = 0;//                    while ((temp = input.read()) != -1) {//                        zipOut.write(temp);//                    }//                    input.close();//                }//            }//            zipOut.closeEntry();//            zipOut.close();//            return resurl;////        } catch (Exception e) {//            LOG.error("生成PDF文件压缩包失败出错--", e);//            return null;//        }//    }    /**     * 下载文件     * @return 文件流     */    @SuppressWarnings("all")    public Object download(String url, String name) {        if (StringUtils.isEmpty(url)) {            return null;        }        Map object = new HashMap();        FileInputStream is = null;        try {            object.put("name", name);            is = new FileInputStream(url);            object.put("data", is);        } catch (FileNotFoundException ef) {            LOG.error("下载文件出错：storeException", ef);            return null;        } catch (Exception e) {            LOG.error("下载文件出错:", e);            return null;        }        return object;    }}

JSP点击按钮触发

function out2Execle(id){$.ajax({url:"<%=request.getContextPath()%>/out2Execle.do",type:"post",dataType:"json",data:{id:id},success:function (data){if (data) {alert("导出成功，进入F盘根目录查看")}},error:function (){alert("out2Execle 系统错误");}})}function out2Execle(id){$.ajax({url:"<%=request.getContextPath()%>/out2Execle.do",type:"post",dataType:"json",data:{id:id},success:function (data){if (data) {alert("导出成功，进入F盘根目录查看")}},error:function (){alert("out2Execle 系统错误");}})}

注意：工具类中指定了模版所在zip位于项目的路径默认项目根目录zip包下，模版格式为xls