JAVA-POI导入数据到excel并弹出保存框

1. 准备工作，jar包的maven依赖如下：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>

2.spring mvc层代码

 @RequestMapping("/excelExport")
    public void excelExport(NumberEvt evt,HttpServletResponse res) {
         numberService.exportExcel(evt, res);
    }

3.service接口层代码

    /**将码号查询页面的数据导出为一张excel表格**/
    void exportExcel(NumberEvt evt,HttpServletResponse res);

4.service接口实现层代码

/** 将码号查询页面的数据导出为一张excel表格方法的实现 **/
    public void exportExcel(NumberEvt evt, HttpServletResponse res) {
        // TODO Auto-generated method stub
        String[] headers = { "码号", "状态", "码号段名称", "所属单位", "所属设备", "码号特征", "定价级别", "码号价格", "备注" };
        List<NumberVo> list = jdbcTemplate.query(sql_query, new Object[] { evt.getCode() }, new RowMapper<NumberVo>() {
            public NumberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                // TODO Auto-generated method stub
                NumberVo numberVo = new NumberVo();
                numberVo.setPhone(rs.getString(1));
                numberVo.setStatus(rs.getString(2));
                numberVo.setCodePartName(rs.getString(3));
                numberVo.setUnit(rs.getString(4));
                numberVo.setFacility(rs.getString(5));
                numberVo.setCodeFeature(rs.getString(6));
                numberVo.setPricingLevel(rs.getString(7));
                numberVo.setCodePrice(rs.getShort(8));
                numberVo.setRemark(rs.getString(9));
                return numberVo;
            }
        });
        try {
            ExcelUtils.export(list, headers, res);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

5.ExcelUtils工具类代码

package com.ffcs.oss.production.number.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ffcs.oss.production.number.model.NumberVo;

/**
 * 将页面上的数据导出到一张excel表格的工具类
 * 
 * @author may
 *
 */
public class ExcelUtils {
    public static void export(List<NumberVo> list, String[] headers, HttpServletResponse res) throws IOException {
        // 创建excel
        @SuppressWarnings("resource")
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建sheet
        HSSFSheet sheet = wb.createSheet("码号查询数据");

        // 创建表头行
        HSSFRow rowTitle = sheet.createRow(0);

        // 创建表头行样式
        HSSFCellStyle styleTitle = wb.createCellStyle();
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
        HSSFFont fontTitle = wb.createFont();
        // 宋体加粗
        fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontTitle.setFontName("宋体");
        fontTitle.setFontHeight((short) 200);
        styleTitle.setFont(fontTitle);

        for (int i = 0; i < headers.length; i++) {
            // 在表头行上创建1列
            HSSFCell cellTitle = rowTitle.createCell(i);
            // 列标题及样式
            cellTitle.setCellValue(headers[i]);
            cellTitle.setCellStyle(styleTitle);
        }
        // 设置文本行的每一列的样式
        HSSFCellStyle styleCenter = wb.createCellStyle();
        styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中

        for (int i = 0; i < list.size(); i++) {
            NumberVo item = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);

            HSSFCell cell = row.createCell(0);
            cell.setCellValue(item.getPhone());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(1);
            cell.setCellValue(item.getStatus());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(2);
            cell.setCellValue(item.getCodePartName());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(3);
            cell.setCellValue(item.getUnit());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(4);
            cell.setCellValue(item.getFacility());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(5);
            cell.setCellValue(item.getCodeFeature());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(6);
            cell.setCellValue(item.getPricingLevel());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(7);
            cell.setCellValue(item.getCodePrice());
            cell.setCellStyle(styleCenter);

            cell = row.createCell(8);
            cell.setCellValue(item.getRemark());
            cell.setCellStyle(styleCenter);
        }
        // 不弹出保存框方式
        /*
         * FileOutputStream fout = new FileOutputStream("e:/numberQuery1.xls");
         * wb.write(fout); fout.close(); wb.close();
         * System.out.println("导出完成！");
         */

        // 弹出保存框方式
        String fileName = "码号查询表";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        res.reset();
        res.setContentType("application/vnd.ms-excel;charset=utf-8");
        res.setHeader("Content-Disposition",
                "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = res.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }
}

6.添加一些测试数据后，结果截图如下所示：