java中使用poi导出数据到EXCEL模板中。

package business.component.check.controller;
import accel.common.core.spring.util.ResourceUtils;
import accel.common.data.jdbc.Dao;
import accel.common.webapp.security.Auth;
import business.commons.util.MapUtils;
import business.component.channel.domain.SecUser;
import business.component.check.domain.QdCheckChannel;
import business.component.check.domain.QdCheckRetailer;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelController{

    public ModelAndView eXcel(@RequestParam Map<String, Object> paramMap,HttpServletRequest
    request ,HttpServletResponse response)throws Exception {


        SecUser secUser = Auth.getUser();
        boolean flag = false;

        String checkType = request.getParameter("checkType");
        byte[] bytes = checkType.getBytes("iso8859-1");//把乱码反向编码得回正确的二进制
        checkType = new String(bytes, "utf-8");//拿着二进制自己去查utf-8, 得出正确的字符
        String checkMonth = MapUtils.getString(paramMap, "checkMonth");
        String pkId = MapUtils.getString(paramMap, "pkId");
        String type = MapUtils.getString(paramMap, "type");

        //数据表
        QdCheckChannel newqd = new QdCheckChannel();
        newqd.setPkId(pkId);
        newqd.setCheckType(checkType);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");//时间格式的转换
        try {
            newqd.setCheckMonth(dateFormat.parse(checkMonth));
        } catch (ParseException e) {
            e.printStackTrace();
        }


        //获取文件名和模板
        String templateName = "check-franchise-template.xlsx";//EXCEL模板的名字
        String fileName = "特许经营考核评分表.xlsx";//导出时下载的EXCEL文件名

        QdCheckChannel qdCheckChannel = Dao.load(QdCheckChannel.class, pkId);
        Map<String, String> map = new HashMap<String, String>();
        map.put("fromId", pkId);
        List<QdCheckRetailer> retailers = Dao.listBySql(QdCheckRetailer.class, 
        "query.clerk.retailer.list", map);
        //从数据中取出所要导出的数据存放在List集合对象中

        InputStream inputStream = new FileInputStream(ResourceUtils.getFile
        ("classpath:template/" + templateName));
       //读取模板文件

        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(inputStream);//根据不同的excel版本采用不同的Workbook

            Sheet sheet = wb.getSheetAt(0);//读取模板中的sheet内容
            //在相应的单元格进行赋值
            for (int i = 0; i < retailers.size(); i++) {//对数据进行循环导出
                org.apache.poi.ss.usermodel.Row row = sheet.getRow(2 + i);
               //模板表头占用两行，i为第一条数据 从第三行开始
                if (row == null) {
                    row = sheet.createRow(2 + i);
                    row.createCell(0).setCellValue(i+1);
                    row.createCell(1).setCellValue(checkMonth);
                    row.createCell(2).setCellValue(retailers.get(i).getRetailerCode());
                    row.createCell(3).setCellValue(retailers.get(i).getAddress());
                } else {
                    row.getCell(0).setCellValue(i+1);
                    row.getCell(1).setCellValue(checkMonth);
                    row.getCell(2).setCellValue(retailers.get(i).getRetailerCode());
                    row.createCell(3).setCellValue(retailers.get(i).getAddress());
                }
            }

        } catch (Exception ex) {
            wb = new HSSFWorkbook(inputStream);
            //读取了模板内所有sheet内容
            HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
            //在相应的单元格进行赋值
            for (int i = 0; i < retailers.size(); i++) {
                HSSFRow row = sheet.getRow(2 + i);
                if (row == null) {
                    row = sheet.createRow(2 + i);
                    row.createCell(0).setCellValue(i+1);
                    row.createCell(1).setCellValue(checkMonth);
                    row.createCell(2).setCellValue(retailers.get(i).getRetailerCode());
                    row.createCell(3).setCellValue(retailers.get(i).getAddress());
                } else {
                    row.getCell(0).setCellValue(i+1);
                    row.getCell(1).setCellValue(checkMonth);
                    row.getCell(2).setCellValue(retailers.get(i).getRetailerCode());
                    row.createCell(3).setCellValue(retailers.get(i).getAddress());
                }
            }
        }


        OutputStream output = response.getOutputStream();
        response.reset();
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("applicationnd/msexcel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + 
         fileName + "\"");//下载文件
        wb.write(output);//写入到Excel模板文件中
        output.close();//关闭


        return null;

    }