java根据excel模版导出数据

package ibp.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.sf.jxls.transformer.XLSTransformer;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * <p>Excel帮助类
 */
public class ExcelExportUtils {

    /**
     * 获取一个wb，导出图片用
     * @param templateFile
     * @param beans
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(String templateFile,
            Map<Object, Object> beans) throws Exception{
        XLSTransformer transformer = new XLSTransformer();
        Workbook wb = transformer.transformXLS(getAbsoluteIn(templateFile), beans);
        return wb;
    }
    /***
     * 得到绝对文件路径流
     * @param path: excel模板路径
     * @return
     */
    public static InputStream getAbsoluteIn(String path){
        File file = new File(path);
        InputStream in = null;
        try {
            in = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return in;
    } 
}

public class testaction{
    /**
     * 导出excel
     * @param t10_page_in_parm
     * @param request
     * @param response
     */
    @RequestMapping(value="downloadT10_report.do",method=RequestMethod.POST)
    public void export(HttpServletRequest request,HttpServletResponse response){
        //获取excel信息
        List list = null;//TODO
        Map<Object, Object> beans = new HashMap<Object, Object>();
        beans.put("list", list);
        beans.put("count", list.size());
        try {
            Workbook wb = ExcelExportUtils.getWorkbook(excelTemplatePath, beans);
            String uplodapath = "D:/temp";
            String downLoadFileName = "testexcel.xlsx";
            //下载图片到excel
            downloadImgToExcel(wb, uplodapath, downLoadFileName, response);
        }catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
        /**
     * 下载图片到excel
     * @param wb
     * @param uplodapath
     * @param downLoadFileName
     * @param response
     */
    private void downloadImgToExcel(XSSFWorkbook wb,String uplodapath,String downLoadFileName,HttpServletResponse response){
        //下载图片到excel
        //begin
        BufferedImage bufferImg = null;
        try {
            //创建作图sheet
            XSSFSheet sheet1 = null;
            if(wb.getSheet("echarts")==null){
                sheet1 = wb.createSheet("echarts");
            }else{
                sheet1 = wb.getSheet("echarts");
            }
            //循环读取图片插入到excel
            String filedir = uplodapath + "\\" +"reporttpl\\"+LoginManager.getUserName();
            File file = new File(filedir);
            if(file.isDirectory()){
                String[] files = file.list();
                //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）  
                XSSFDrawing patriarch = sheet1.createDrawingPatriarch();
                int i = 0;
                int rowbegin = 1;
                int rowend = 18;
                for(String _file : files){
                    if("png".equals(_file.substring(_file.lastIndexOf(".")+1))){
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        bufferImg = ImageIO.read(new File(filedir + "\\" + _file));
                        ImageIO.write(bufferImg, "png", byteArrayOut);
                        //anchor主要用于设置图片的属性
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 1, rowbegin, (short) 11, rowend);
                        //插入图片    
                        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
                        i++;
                        rowbegin = i*18+1;
                        rowend = rowbegin + 18;
                    }
                }
            }
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downLoadFileName, "GBK"));
            OutputStream out = response.getOutputStream();
            // 写入excel文件     
             wb.write(out);
             out.close();
             wb.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        //end
    }
}

根据模版导出数据要求模版必须是excel2007，2003版的不支持

转载于:https://www.cnblogs.com/seeusmile-cnblog/p/8027878.html