Java实现Excel导出(二)

上一篇写了当导出模板是规则的excel时的实现，此种方式就是在给定首行和首列之后，以每个对象为一行数据逐行往excel表中插入数据，但要导出模板为下面这种情况的excel,则此种方式就行不通了，这篇可看作是上一篇的补充

下面的例子是以该excel表为模板，将数据库表中的数据导出到excel
数据库表

代码实现
entity实体 PersonInf.java

package com.env.entity;

import javax.persistence.Entity;
import javax.persistence.Table;

import com.env.common.entity.BaseBO;

@Entity
@Table(name="p_person")
public class PersonInf extends BaseBO{
    
    private static final long serialVersionUID = 1L;

    //人员姓名
    private String pname;
    
    //性别
    private String gender;
    
    //年龄
    private String age;
    
    //手机号
    private String phonenum;
    
    //QQ
    private String qqnum;
    
    //微信
    private String weichat;
    
    //微博
    private String weibo;
    
    //备注
    private String remarks;

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getPhonenum() {
        return phonenum;
    }

    public void setPhonenum(String phonenum) {
        this.phonenum = phonenum;
    }

    public String getQqnum() {
        return qqnum;
    }

    public void setQqnum(String qqnum) {
        this.qqnum = qqnum;
    }

    public String getWeichat() {
        return weichat;
    }

    public void setWeichat(String weichat) {
        this.weichat = weichat;
    }

    public String getWeibo() {
        return weibo;
    }

    public void setWeibo(String weibo) {
        this.weibo = weibo;
    }

    public String getRemarks() {
        return remarks;
    }

    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }
    
}

dao层 PersonInfMapper.java

package com.env.dao;

import org.apache.ibatis.annotations.Mapper;
import com.env.entity.PersonInf;

@Mapper
public interface PersonInfMapper {

    PersonInf selectByName(String pname);
}

mybatis文件 PersonInfMapper.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.PersonInfMapper">
  <select id="selectByName" parameterType="java.lang.String" resultType="com.env.entity.PersonInf">
    select
    pname,age,gender,phonenum,qqnum,weichat,weibo,remarks
    from p_person
    where pname = #{pname,jdbcType=VARCHAR}
  </select>
</mapper>

Controller层 PersonInfController.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.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
@RequestMapping(value = "/personInf")
public class PersonInfController {

    @Autowired
    private PersonInfService personInfService;
    
    /**
     *
     * @Title: exportPersonInfByName
     * @Description: 获取人员信息导出Excel
     */
//后面的参数{json}为人员姓名，必须给出
    @RequestMapping(method = RequestMethod.GET, value = "/exportPersonInfByName" + "/{json}")
    public Result exportPersonInfByName(@PathVariable String json, HttpServletRequest request,
            HttpServletResponse response) {
        try {
            json = "{"+json+"}";
            String result = personInfService.exportPersonInfByName(json, request, response);
            return Result.success(result);
        } catch (Exception e) {
            e.printStackTrace();
            LoggerUtil.error(e.getMessage());
            return Result.error("异常，生产日报数据详情导出Excel失败");
        }
    }
}

Service层接口 PersonInfService.java

package com.env.service;

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

public interface PersonInfService {

    public String exportPersonInfByName(String json, HttpServletRequest request, HttpServletResponse response)
            throws Exception;
}

Service层实现类 PersonInfServiceImpl.java

package com.env.serviceImpl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

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

import org.apache.commons.beanutils.DynaBean;
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.Autowired;
import org.springframework.stereotype.Service;

import com.env.common.utils.ExcelPoiPluginsUtil;
import com.env.common.utils.FileToolsUtil;
import com.env.common.utils.JsonPluginsUtil;
import com.env.common.utils.UUIDToolsUtil;
import com.env.common.utils.Util;
import com.env.dao.PersonInfMapper;
import com.env.entity.Pdaydata;
import com.env.entity.PersonInf;
import com.env.service.PersonInfService;

@Service
public class PersonInfServiceImpl implements PersonInfService {

    @Autowired
    private PersonInfMapper personInfMapper;

    // 根据人员姓名获取人员信息导出Excel
    @Override
    public String exportPersonInfByName(String json, HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        DynaBean bean = JsonPluginsUtil.json2bean(json);
        // 参数
        String name = Util.toString(bean.get("pname"));
        PersonInf personInf = personInfMapper.selectByName(name);

        // Excel模板
        String frompath = FileToolsUtil.ROOT + "resource/excel/PersonTemplate.xlsx";
        // 文件名称
        String filename = name + "信息表";
        // response输出流
        ServletOutputStream out = response.getOutputStream();
        // 文件名外加当前时间
        DateFormat format = new SimpleDateFormat("yyyyMMdd");
        String timeFileName = format.format(new Date());
        timeFileName = ExcelPoiPluginsUtil.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");
        String toPath = FileToolsUtil.TMPPATH + UUIDToolsUtil.getUUID("excel")
                + frompath.substring(frompath.lastIndexOf("."), frompath.length());
        if (FileToolsUtil.copyFile(frompath, toPath)) {
            // 初始化
            Workbook wb = null;
            InputStream inp = new FileInputStream(toPath);
            try {
                wb = WorkbookFactory.create(inp);
                Sheet sheet = wb.getSheetAt(0);
                
                // 将人员姓名插入第二行第一列
                sheet.getRow(1).getCell(0).setCellValue("人员：" + personInf.getPname());
                
                // 基本信息
                // 将姓名插入第四行第二列
                sheet.getRow(3).getCell(1).setCellValue(personInf.getPname());
                // 将性别插入第四行第四列
                sheet.getRow(3).getCell(3).setCellValue(personInf.getGender());
                // 将年龄插入第五行第二列
                sheet.getRow(4).getCell(1).setCellValue(personInf.getAge());

                // 其他信息
                //将 手机号插入第八行第一列
                sheet.getRow(7).getCell(0).setCellValue(personInf.getPhonenum());
                //将 QQ号插入第八行第二列
                sheet.getRow(7).getCell(1).setCellValue(personInf.getQqnum());
                //将 微信插入第八行第三列
                sheet.getRow(7).getCell(2).setCellValue(personInf.getWeichat());
                //将 微博插入第八行第四列
                sheet.getRow(7).getCell(3).setCellValue(personInf.getWeibo());

                //将 备注信息插入第九行第二列
                sheet.getRow(8).getCell(1).setCellValue(personInf.getRemarks());
                wb.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                out.close();
            }
        }
        return "导出Excel成功";
    }
}

里面相关的主要工具类已在上一篇给出，在此不多做赘述，实现效果如下图
浏览器地址栏输入

回车

导出的excel表

至此，使用Poi的方式导出Excel就介绍完成了，若有指正或补充，还望不吝赐教