Java实现Excel导出(一)

最新推荐文章于 2024-09-30 09:36:06 发布

L一刀G_

最新推荐文章于 2024-09-30 09:36:06 发布

阅读量377

点赞数 2

分类专栏： Java 文章标签： Java

本文链接：https://blog.csdn.net/CROSSFIT/article/details/82901464

版权

Java 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

不同于excel导入，下面例子的excel导出是在后台java代码中实现，在页面上点击导出按钮，所有的工作都在后台进行，下面是使用POI的方式导出excel。
由于时间有限，所以就没写前端页面，下面的例子是通过在浏览器中输入导出方法的地址来弹出窗口
主要实现将下面数据库表导出至excel

Java文件
entity实体 StudentGrade.java

package com.env.entity;
import javax.persistence.Entity;
import javax.persistence.Table;
import com.env.common.entity.BaseBO;

@Entity
@Table(name="s_grade")
public class StudentGrade extends BaseBO {

    private static final long serialVersionUID = 1L;

    //成绩id
    private String gid;
    
    //学生姓名
    private String studentname;
    
    //课程java
    private String java;
    
    //课程Oracle
    private String oracle;
    
    //课程数据结构
    private String struct;
    
    //总分
    private String total;

    public String getGid() {
        return gid;
    }

    public void setGid(String gid) {
        this.gid = gid;
    }

    public String getStudentname() {
        return studentname;
    }

    public void setStudentname(String studentname) {
        this.studentname = studentname;
    }

    public String getJava() {
        return java;
    }

    public void setJava(String java) {
        this.java = java;
    }

    public String getOracle() {
        return oracle;
    }

    public void setOracle(String oracle) {
        this.oracle = oracle;
    }

    public String getStruct() {
        return struct;
    }

    public void setStruct(String struct) {
        this.struct = struct;
    }

    public String getTotal() {
        return total;
    }

    public void setTotal(String total) {
        this.total = total;
    }    
}

dao层 SgradeMapper.java

package com.env.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.env.entity.StudentGrade;

public interface SgradeMapper {
    List<StudentGrade> getStudentGradeList(@Param("studentname")String studentname);
}

mapper文件 SgradeMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.env.dao.SgradeMapper">
    <select id="getStudentGradeList"   resultType="com.env.entity.StudentGrade">
        select
        gid,studentname,struct,java,oracle,total
        from s_grade
        <if test="studentname!=null and studentname!=''">
        where studentname = #{studentname}
        </if>
    </select>
</mapper>

Controller层 SgradeController.java

package com.env.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.env.result.Result;
import com.env.service.SgradeService;
import com.env.utils.LoggerUtil;

@RestController
@RequestMapping(value="/sGradeController")
public class SgradeController {

    @Autowired
    private SgradeService sgradeService;
    
/*
* 导出学生成绩列表至excel
*/
//导出的方法是用GET请求方式访问，后面的参数{json}用来传学生姓名，可根据学生姓名查询该学生成绩，仅导出该学生的成绩到excel，若参数为空，则导出所有学生的成绩到
//工具类Result方法
/*public static Result success(Object data){
		Result result=new Result(1,"success",data);
		return result;
}
public static Result error(String msg){
		Result result=new Result(0,msg,null);
		return result;
}*/
    @RequestMapping(method = RequestMethod.GET, value="/exportSgrade2Excel/{json}")
    public Result exportSgrade2Excel(
            @PathVariable String json,HttpServletRequest request, HttpServletResponse response) {
        try {
            json = "{"+json+"}";
            String result = sgradeService.exportSgrade2Excel(json,
                    request, response);
            return Result.success(result);
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("异常，导出学生成绩到Excel失败");
        }
    }
}

Service层 SgradeService.java

package com.env.service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public interface SgradeService {
    /*
     * 导出学生成绩至excel
     */
    String exportSgrade2Excel(String json, HttpServletRequest request, HttpServletResponse response) throws Exception;
}

ServiceImpl SgradeServiceImpl.java

package com.env.serviceImpl;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.DynaBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.env.common.entity.PoiDataBean;
import com.env.common.entity.PoiTitleBean;
import com.env.common.utils.DynaBeanPluginsUtil;
import com.env.common.utils.ExcelPoiPluginsUtil;
import com.env.common.utils.FileToolsUtil;
import com.env.common.utils.JsonPluginsUtil;
import com.env.common.utils.Util;
import com.env.dao.SgradeMapper;
import com.env.entity.StudentGrade;
import com.env.service.SgradeService;

@Service
public class SgradeServiceImpl implements SgradeService {

    @Autowired
    private SgradeMapper sgradeMapper;
    
    /*
     * 导出学生成绩数据至excel
     */
    @Override
    public String exportSgrade2Excel(String json, HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        //json字符串转换为bean对象,也可用其他工具类转换，下面的工具类中不会给出
        DynaBean bean = JsonPluginsUtil.json2bean(json);
        // 参数,可为空，为空则导出所有学生的成绩，Util.toString()是转字符串的工具类方法，下面的工具类中不会给出
        String studentname = Util.toString(bean.get("studentname"));
        
        //查询学生成绩
        List<StudentGrade> list = sgradeMapper.getStudentGradeList(studentname);
        
        // Excel模板,导出的excel文件以此为模板
        String frompath = FileToolsUtil.ROOT + "resource/excel/studentGrade.xlsx";
        // 导出的文件名称
        String filename = "学生成绩表";
        //在此处定义excel列名，须与上面所说的excel模板的一一对应
        String columnname[] = {
            "ordernum","studentname","java","oracle","数据结构","总分"
        };
        //导出到excel的所有数据保存在该list中
        List<DynaBean> list1 = new ArrayList<DynaBean>();
        //每一个DynaBean相当于一行数据
        DynaBean bean1 = null;
        
        //从第四行开始插入数据
        int row = 3;
        for(int i=0;i<=list.size()-1;i++){
            String studentname1 = list.get(i).getStudentname();
            String java = list.get(i).getJava();
            String oracle = list.get(i).getOracle();
            String struct = list.get(i).getStruct();
            String total = list.get(i).getTotal();
            bean1 = DynaBeanPluginsUtil.invoke();
            bean1.set("ordernum", i+1);
            bean1.set("studentname", studentname1);
            bean1.set("java", java);
            bean1.set("oracle", oracle);
            bean1.set("数据结构", struct);
            bean1.set("总分", total);
            list1.add(bean1);
            row++;
        }
        //在导出的excel表中显示查询条件(导出的是哪个学生的数据)
        String studentname2 = null;
        if (!Util.isNull(studentname) && !"-1".equals(studentname)) {
            studentname2 = studentname;
        }else{
            studentname2 = "全部";
        }
        //0和3分别表示从第一列第四行开始插入数据，list1是要插入表格的数据集合，columnname为上面定义的表的列名，为String类型数组
        PoiDataBean dataBean = new PoiDataBean(0, 3, list1, columnname);
        // 导出Excel的头部信息
        List<PoiTitleBean> titleBean = new ArrayList<PoiTitleBean>();
        String title = "学生：" + studentname2;
        PoiTitleBean selectBean = new PoiTitleBean(1, Util.toString(title));
        titleBean.add(selectBean);    
        //导出Excel工具类
        //frompath为导出excel的模板文件地址
        //filename为将导出excel文件的名称
        //titleBean为导出Excel文件的头部信息
        ExcelPoiPluginsUtil.wirte(frompath, filename, dataBean, titleBean, request, response);    
        return null;
    }
}

涉及到的主要工具类
DynaBeanPluginsUtil.java

package com.env.common.utils;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaProperty;

public class DynaBeanPluginsUtil {   
    /**
     * 创建 DynaBean
     * @return
     */
    public static DynaBean invoke(){
        return new CommonDynaBean();
    }
    /**
     * 判断是否有此属性
     * @param _dynaBean 对象
     * @param _property   属性名称
     * @return  【true 有】【 false 无】
     */
    public static boolean IsProperty(DynaBean _dynaBean,String _property){
        return getPropertyStr(_dynaBean).indexOf(_property)!=-1?true:false;
    }    
    /**
     * 获取此对象中所含有的属性
     * @param _dynaBean
     * @param _propertys
     * @return
     */
    public static String[] havePropertys(DynaBean _dynaBean,String[] _propertys){
        
        String _tempStr = getPropertyStr(_dynaBean);
        List<String> _list = new ArrayList<String>();
        for(String _property : _propertys){
            if(_tempStr.indexOf(_property)!=-1){
                _list.add(_property);
            }
        }
        return _list.toArray(new String[]{});
    }    
    /**
     * 复制生成新的bean
     *
     * @param _bean         复制的对象
     * @param copyPropertys 复制某些属性
     * @param copy2Propertys复制为对应属性
     * @return
     */
    public static DynaBean copyBean(DynaBean _bean,String[] copyPropertys,String[] copy2Propertys){
        DynaBean _tempBean = invoke();
        String _tempStr = getPropertyStr(_bean);
        if(copy2Propertys!=null&&copyPropertys.length!=copy2Propertys.length){
            System.out.println("复制属性与复制到的属性不匹配！");
        }else{
            String _property = null;
            if(copy2Propertys==null){
                for(int _i=0;_i<copyPropertys.length;_i++){
                    _property = copyPropertys[_i];
                    if(_tempStr.indexOf(_property)!=-1){
                        _tempBean.set(_property, _bean.get(_property));
                    }else{
                        _tempBean.set(_property, null);
                    }
                }
            }else{
                for(int _i=0;_i<copyPropertys.length;_i++){
                    _property = copyPropertys[_i];
                    if(_tempStr.indexOf(_property)!=-1){
                        _tempBean.set(copy2Propertys[_i], _bean.get(_property));
                    }else{
                        _tempBean.set(copy2Propertys[_i], null);
                    }
                }
            }         
        }     
        return _tempBean;
    }    
    /**
     * 将_bean的属性复制到的2bean 如果有数据则覆盖 但类型必须保持一致
     *
     * @param _bean         复制的对象
     * @param copyPropertys 复制某些属性
     * @param copy2Propertys复制为对应属性
     * @return
     */
    public static DynaBean copy2Bean(DynaBean _bean,DynaBean _tempBean ,String[] copyPropertys,String[] copy2Propertys){       
        String _tempStr = getPropertyStr(_bean);
        if(copy2Propertys!=null&&copyPropertys.length!=copy2Propertys.length){
            System.out.println("复制属性与复制到的属性不匹配！");
        }else{
            String _property = null;
            if(copy2Propertys==null){
                for(int _i=0;_i<copyPropertys.length;_i++){
                    _property = copyPropertys[_i];
                    if(_tempStr.indexOf(_property)!=-1){
                        _tempBean.set(_property, _bean.get(_property));
                    }else{
                        _tempBean.set(_property, null);
                    }
                }
            }else{
                for(int _i=0;_i<copyPropertys.length;_i++){
                    _property = copyPropertys[_i];
                    if(_tempStr.indexOf(_property)!=-1){
                        _tempBean.set(copy2Propertys[_i], _bean.get(_property));
                    }else{
                        _tempBean.set(copy2Propertys[_i], null);
                    }
                }
            }        
        }     
        return _tempBean;
    }
    /**
     * 复制生成新的bean
     *
     * @param _bean         复制的对象
     * @param copyPropertys 复制某些属性
     * @param copy2Propertys复制为对应属性
     * @return
     */
    public static DynaBean copy2Bean(DynaBean _bean,DynaBean _temp2Bean ,String[] copyPropertys){
        return copy2Bean( _bean, _temp2Bean , copyPropertys, null);
    }
    /**
     * 复制生成新的bean
     *
     * @param _bean         复制的对象
     * @param copyPropertys 复制某些属性
     * @return
     */
    public static DynaBean copyBean(DynaBean _bean,String[] copyPropertys){        
        return copyBean(_bean, copyPropertys,null);
    }    
    private static String getPropertyStr(DynaBean _dynaBean){
        DynaProperty[] _dynaPropertys = _dynaBean.getDynaClass().getDynaProperties();
        StringBuffer _buffer = new StringBuffer(",");
        for(DynaProperty _dynaProperty: _dynaPropertys){
            _buffer.append(_dynaProperty.getName()+",");
        }
        return _buffer.toString();
    }
}

PoiDataBean.java

package com.env.common.entity;
import java.util.List;

import org.apache.commons.beanutils.DynaBean;

public class PoiDataBean {
   
    public PoiDataBean(int columnstart, int rowstart, List<DynaBean> data,
            String[] columnname) {
        super();
        this.columnstart = columnstart;
        this.rowstart = rowstart;
        this.data = data;
        this.columnname = columnname;
    }
    private int columnstart;//开始列0
    private int rowstart;//开始行0
    private List<DynaBean> data;//数据集合
    private String[] columnname;//数据项
    private boolean tieleWirte;//是否已经写入
    
    public int getColumnstart() {
        return columnstart;
    }
    public void setColumnstart(int columnstart) {
        this.columnstart = columnstart;
    }
    public int getRowstart() {
        return rowstart;
    }
    public void setRowstart(int rowstart) {
        this.rowstart = rowstart;
    }
    public List<DynaBean> getData() {
        return data;
    }
    public void setData(List<DynaBean> data) {
        this.data = data;
    }
    public String[] getColumnname() {
        return columnname;
    }
    public void setColumnname(String[] columnname) {
        this.columnname = columnname;
    }
    public boolean isTieleWirte() {
        return tieleWirte;
    }
    public void setTieleWirte(boolean tieleWirte) {
        this.tieleWirte = tieleWirte;
    }
}

PoiTitleBean.java

package com.env.common.entity;
/**
* 导出excel的头部信息
*/
public class PoiTitleBean {

    private int row;//行数
    private String title;//标题
    private int call = 0;//列数
    public int getRow() {
        return row;
    }
    public PoiTitleBean(int row, String title) {
        super();
        this.row = row;
        this.title = title;
    }
    public void setRow(int row) {
        this.row = row;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public int getCall() {
        return call;
    }
    public void setCall(int call) {
        this.call = call;
    }
}

ExcelPoiPluginsUtil.java

package com.env.common.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Value;

import com.env.common.entity.ExcelPicBean;
import com.env.common.entity.PoiDataBean;
import com.env.common.entity.PoiTitleBean;

/**
* Excel 解析、读写工具类
*
* 需要
* poi-3.9-20121203.jar
* poi-ooxml-3.9-20121203.jar
* poi-ooxml-schemas-3.9-20121203.jar
* xmlbeans-2.3.0.jar
* 支持
* @author
*
*/
public class ExcelPoiPluginsUtil {

    private static final int EXCELMAXCOUNT = 5000;
    private static final String EXCEL_ROOT = "resource\\excel\\";
    private static final String EXCEL_SUB = "excel";
    
    public static final int HSSFCell_CELL_TYPE_STRING = HSSFCell.CELL_TYPE_STRING;

    public static List<PoiTitleBean> inikPoiTitleBean(int _start, String[] _titles){
        List<PoiTitleBean> _poiTitleBeanList = new ArrayList<PoiTitleBean>();
        PoiTitleBean _poiTitleBean = null;
        for(int _i=0;_i<_titles.length;_i++){
            _poiTitleBean = new PoiTitleBean(_start+_i, _titles[_i]);
            _poiTitleBeanList.add(_poiTitleBean);
        }
        return _poiTitleBeanList;
    }
    /**
     *
     * @param data            数据
     * @param frompath        模板路径
     * @param filename        文件名称
     * @param columnstart    开始列0
     * @param rowstart        开始行0
     * @return
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static DynaBean wirte(List<List<String>> data,String frompath ,String filename , int columnstart, int rowstart) throws IOException
    {
        DynaBean _dynaBean = DynaBeanPluginsUtil.invoke();
        int fileCount = getFileCount(data.size());
        
        String downFile = null;
        if(fileCount==1){
            downFile =  EXCEL_ROOT + UUIDToolsUtil.getUUID(EXCEL_SUB) + frompath.substring(frompath.lastIndexOf("."),frompath.length());
            String toPath = FileToolsUtil.ROOT + downFile;
            wirte( data, frompath, toPath, columnstart,  rowstart, 0,  data.size());
        }else{
            String felie = UUIDToolsUtil.getUUID(EXCEL_SUB);
            String sourceDir = EXCEL_ROOT  + felie;
            for(int _h=1;_h<=fileCount;_h++){
                String filePath =  sourceDir +"\\" + filename + "_" + _h +frompath.substring(frompath.lastIndexOf("."),frompath.length());
                String toPath = FileToolsUtil.ROOT + filePath;
                wirte( data, frompath, toPath, columnstart,  rowstart, (_h-1)*EXCELMAXCOUNT,  _h==fileCount?(data.size()-(_h-1)*EXCELMAXCOUNT):EXCELMAXCOUNT);
            }
            
            downFile = sourceDir + ".zip";
            String zipFile = FileToolsUtil.ROOT + downFile;
            ZipPluginsUtil.zip(FileToolsUtil.ROOT + sourceDir + "\\", zipFile);
        }
        
        _dynaBean.set("fileCount", fileCount);
        _dynaBean.set("downFile", downFile);
        _dynaBean.set("filename", filename);
        
        return _dynaBean;
    }
    
    public static DynaBean  wirte(List<List<String>> data,String frompath , int columnstart, int rowstart) throws IOException
    {
        return wirte(data, frompath , UUIDToolsUtil.getUUID(EXCEL_SUB) ,  columnstart,  rowstart);
    }
    
    private static void  wirte(List<List<String>> data,String frompath,String topath, int columnstart, int rowStart, int dataStart, int dataCount) throws IOException
    {
        
        FileToolsUtil.copyFile(frompath, topath);
        
//        初始化
        Workbook wb = null;
        InputStream inp = new FileInputStream(topath);
        
//
        int rowCount = 0;
        rowCount = rowStart + dataCount;
        
        int i = dataStart;
        try {
            
            wb = WorkbookFactory.create(inp);
            
            Sheet sheet = wb.getSheetAt(0);
            
             for(int _i = rowStart; _i <rowCount; _i++) {
                   
                   Row _row = sheet.getRow(_i);
                  
                   List<String> _i_data=data.get(i);
                   if(_row==null){
                       _row= sheet.createRow(_i);
                   }
                   _row.setHeight((short) 500);
                   int columnCount = _i_data.size()+columnstart;//需要加上开始的列数
                   int j = 0;//需要额外的计数
                   for(int _j = columnstart; _j <columnCount; _j++) {
                       Cell cell = _row.getCell(_j);
                       if(cell==null){
                           cell = _row.createCell(_j);
                       }
                       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                       cell.setCellValue(_i_data.get(j));
                       j++;
                   }
                   i++;
               }
               
               FileOutputStream out=new FileOutputStream(topath);
               
               wb.write(out);
               out.flush();
               out.close();
            
        } catch (InvalidFormatException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   
        
    }
    
    private static int getFileCount(int dataSize){

        int fileCount = (dataSize%EXCELMAXCOUNT==0?dataSize:(dataSize-dataSize%EXCELMAXCOUNT+EXCELMAXCOUNT))/EXCELMAXCOUNT;
        
        return fileCount;
    }
    /**
     * @descri 使用该方法进行excel导出
     * @param frompath    模板路径
     * @param filename  文件名称
     * @param _poiDataBean    数据
     * @param _poiTitleBean 标题
     * @return
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static void  wirte(String frompath ,String filename, PoiDataBean _poiDataBean, List<PoiTitleBean> _poiTitleBean,
            HttpServletRequest request, HttpServletResponse response) throws IOException {
        String toPath = FileToolsUtil.TMPPATH + UUIDToolsUtil.getUUID(EXCEL_SUB) +
                frompath.substring(frompath.lastIndexOf("."),frompath.length());
        wirte(frompath, toPath, filename, 0, _poiDataBean.getData().size(), _poiDataBean, _poiTitleBean, request, response);
    }
    
    private static void  wirte(String frompath, String topath, String filename, int dataStart, int dataCount, PoiDataBean _poiDataBean, List<PoiTitleBean> _poiTitleBean,
            HttpServletRequest request, HttpServletResponse response) throws IOException {
        
        // response输出流
        ServletOutputStream out = response.getOutputStream();
        // 文件名外加当前时间
        DateFormat format = new SimpleDateFormat("yyyyMMdd");
        String timeFileName = format.format(new Date());
        timeFileName =encodingFileName(filename + "_" + timeFileName);
        // 设置response必要参数
        response.reset();
        response.setContentType("application/octet-stream; charset=iso-8859-1");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment; filename=" + timeFileName +".xlsx" );    
        
       List<DynaBean> data = _poiDataBean.getData();
       int columnstart = _poiDataBean.getColumnstart();
       int rowStart = _poiDataBean.getRowstart();
       String[] columnname = _poiDataBean.getColumnname();
        
        if(FileToolsUtil.copyFile(frompath, topath)){
            // 初始化
            Workbook wb = null;
            InputStream inp = new FileInputStream(topath);
            
            int rowCount = 0;
            rowCount = rowStart + dataCount;
            
            int i = dataStart;
            
            try {
                
                wb = WorkbookFactory.create(inp);
                
                Sheet sheet = wb.getSheetAt(0);
                
                 Row _row = sheet.getRow(_poiDataBean.getRowstart());
                 int columnCount = columnname.length+columnstart;//需要加上开始的列数
                 Cell _cell = null;
                 Map<Integer,CellStyle > _callTypeMap0 = new HashMap<Integer,CellStyle >();
                 Cell cell = _row.getCell(_poiDataBean.getColumnstart());

                  for(int _j = columnstart; _j <columnCount; _j++){
                      _cell = _row.getCell(_j);
                      if(_cell!=null){
                          _callTypeMap0.put(_j,  _cell.getCellStyle());
                      }else{
                          _callTypeMap0.put(_j,  cell.getCellStyle());
                      }
                  }
                
                 _row = sheet.getRow(_poiDataBean.getRowstart()+1);
                  Map<Integer,CellStyle > _callTypeMap1 = new HashMap<Integer,CellStyle >();
                  cell = _row.getCell(_poiDataBean.getColumnstart());

                  for(int _j = columnstart; _j <columnCount; _j++){
                      _cell = _row.getCell(_j);
                      if(_cell!=null){
                          _callTypeMap1.put(_j,  _cell.getCellStyle());
                      }else{
                          _callTypeMap1.put(_j,  cell.getCellStyle());
                      }
                  }
                
                short _rowHeight = _row.getHeight();
                if(!_poiDataBean.isTieleWirte()){
                    if(_poiTitleBean != null && _poiTitleBean.size()>0){
                        
                        for(PoiTitleBean _title : _poiTitleBean){
                            _row = sheet.getRow(_title.getRow());
                            cell = _row.getCell(_title.getCall());
                            cell.setCellValue(_title.getTitle());
                        }
                    }
                }
                
                
                 for(int _i = rowStart; _i <rowCount; _i++) {
                       
                       _row = sheet.getRow(_i);
                      
                       DynaBean _i_data=data.get(i);
                       if(_row==null){
                           _row= sheet.createRow(_i);
                       }
                       _row.setHeight(_rowHeight);
                       Map<Integer,CellStyle > _callTypeMap = null;
                       if(_i%2!=0){
                           _callTypeMap = _callTypeMap0;
                       }else{
                           _callTypeMap = _callTypeMap1;
                       }
                       int j = 0;//需要额外的计数
                       for(int _j = columnstart; _j <columnCount; _j++) {
                           cell = _row.getCell(_j);
                           
                           if(cell==null){
                               cell = _row.createCell(_j);
                           }

                           cell.setCellStyle(_callTypeMap.get(_j));
                           dynaBeanVal2CellVal(cell,_i_data.get(columnname[_j-columnstart]));
                           j++;
                       }
                       i++;
                   }
                   
                   wb.write(out);
                
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                out.close();
            }   
        }
    }
    
    public static String encodingFileName(String fileName) {
        String returnFileName = "";
        try {
            returnFileName = new String(fileName.getBytes("GB2312"), "ISO8859-1");
            returnFileName = StringUtils.replace(returnFileName, "+", "%20");
                
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            
        }
        return returnFileName;
    }
    
    private static void dynaBeanVal2CellVal(Cell cell,Object _dynaBeanVal){

        if(_dynaBeanVal instanceof String){
            cell.setCellValue(Util.toString(_dynaBeanVal));
        }else if(_dynaBeanVal instanceof Date){
            cell.setCellValue((Date)_dynaBeanVal);
        }else if(_dynaBeanVal instanceof Boolean){
            cell.setCellValue((Boolean)_dynaBeanVal);
        }else if(_dynaBeanVal instanceof Integer ){
            cell.setCellValue((Integer)_dynaBeanVal);
        }else if(_dynaBeanVal instanceof Float ){
            cell.setCellValue((Float)_dynaBeanVal);
        }else if(_dynaBeanVal instanceof Double ){
            cell.setCellValue((Double)_dynaBeanVal);
        }else {
            cell.setCellValue(Util.toString(_dynaBeanVal));
        }
    }
    /**
     *
     * @param _excelPath     Excel文件路径
     * @param _picPath        图片路径
     * @param columnstart    从第几列开始
     * @param rowstart        从第几行开始
     * @param columnCount    占用几列
     * @param rowCount        占用几行
     *
     *  rowstart<0 则会自动追加在最后 |rowstart|行
     *
     * @return
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static boolean  wirtePic(String _excelPath , ExcelPicBean _excelPicBean) throws IOException{
        
        String _picPath = _excelPicBean.getPicPath();
        int columnstart = _excelPicBean.getColumnstart();
        int rowstart = _excelPicBean.getRowstart();
        int columnCount = _excelPicBean.getColumnCount();
        int rowCount = _excelPicBean.getRowCount();
        
        Workbook wb = null;
        InputStream inp = new FileInputStream(_excelPath);
        ByteArrayOutputStream byteArrayOut = null;
        FileOutputStream out = null;
        
        int PICTURE_TYPE = 0;
        
        try {
            
            wb = WorkbookFactory.create(inp);
            File pic = new File(_picPath);
            String filename = pic.getName();
            String sux = filename.substring(filename.lastIndexOf(".")+1, filename.length()).toLowerCase();

            if("png".equals(sux)){
                PICTURE_TYPE = Workbook.PICTURE_TYPE_PNG;
            }else if("jpg".equals(sux)){
                PICTURE_TYPE = Workbook.PICTURE_TYPE_JPEG;
            }
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        byteArrayOut = new ByteArrayOutputStream();
        BufferedImage bufferImg = ImageIO.read(new File(_picPath));
        
        ImageIO.write(bufferImg,"png",byteArrayOut);
        //创建工作薄

        Sheet sheet = wb.getSheetAt(0);
        
        if(rowstart<0){
            
            rowstart = sheet.getLastRowNum()-rowstart;
        }
        
        
        Drawing patriarch = sheet.createDrawingPatriarch();
        ClientAnchor anchor = patriarch.createAnchor(
                10,10,10,10,
                columnstart            ,  rowstart,
                columnstart+columnCount,rowstart+rowCount);

        patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),PICTURE_TYPE));

//        参数说明：
//        row  起始行 cell 起始单元格  row1 终止行  cell2 终止单元格
            if(inp!=null){
                inp.close();
            }
            out = new FileOutputStream(_excelPath);
            wb.write(out);
            out.flush();
            out.close();
        
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }finally{
            if(inp!=null){
                inp.close();
            }
            if(out!=null){
                out.close();
            }
            if(byteArrayOut!=null){
                byteArrayOut.close();
            }
            
        }
        return true;
    }
}

Excel模板文件 studentGrade.xlsx

实现效果
studentname参数为空的情况
浏览器地址栏
地址1
回车
弹框1
导出的数据

studentname参数不为空的情况
浏览器地址栏
地址2
导出的数据

至此，导出规则的excel就搞定了