springmvc导出excel并弹出下载框

最近做grid列表相关数据导出到excel功能，根据自己选择的列导出成excel 并且下载到本地。废话不说 直接上关键代码：

需要引入相关的包：

compile 'org.apache.poi:poi-ooxml:3.9'
compile 'org.apache.poi:poi:3.9'
compile 'org.apache.poi:poi-scratchpad:3.9'

这是我项目中gradle的配置。

1.js 代码

window.location.href="export/exportExcel?ids=" + this.selectedId;

//  此处的selectedId 就是单选或者多选多列的id集合

2. controller

import com.fndsoft.bcis.model.WithdrawDeposit;

import com.fndsoft.bcis.service.SettlementService;

import com.fndsoft.bcis.utils.ExcelUtil;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.ResponseBody;

 

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletResponse;

import java.io.*;

import java.text.SimpleDateFormat;

import java.util.*;

 

/**

 * Created by bai on 2016/7/26.

 */

@Controller

@RequestMapping(value = "/export")

public class ExportExcelController {

  @Autowired

  private SettlementService settlementService;

 

 

  /**

   * 根据多选的结算申请导出excel

   *

   * @return

   */

  @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)

  @ResponseBody

  public Map<String, Object> exportExcel(@RequestParam Integer[] ids, HttpServletResponse response) throws IOException {

    String fileName = new Date().getTime() + ".xls";

    //填充projects数据

    List<WithdrawDeposit> projects = createData(ids);

    List<Map<String, Object>> list = createExcelRecord(projects);

    String columnNames[] = {"申请号", "提现金额", "申请时间", "支付类型", "账号", "状态"};//列名

    String keys[] = {"applicationNo", "amount", "opTime", "payType", "accountNo", "status"};//map中的key

    ByteArrayOutputStream os = new ByteArrayOutputStream();

    try {

      ExcelUtil.createWorkBook(list, keys, columnNames).write(os);

    } catch (IOException e) {

      e.printStackTrace();

    }

    byte[] content = os.toByteArray();

    InputStream is = new ByteArrayInputStream(content);

    // 设置response参数，可以打开下载页面

    response.reset();

    response.setContentType("application/vnd.ms-excel;charset=utf-8");

    response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes(), "iso-8859-1"));

    ServletOutputStream out = response.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 (final IOException e) {

      throw e;

    } finally {

      if (bis != null)

        bis.close();

      if (bos != null)

        bos.close();

    }

    return null;

  }

 

  private List<WithdrawDeposit> createData(Integer[] ids) {<br><br>    //自己实现

    return null;

  }

 

  private List<Map<String, Object>> createExcelRecord(List<WithdrawDeposit> projects) {

    List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();

    Map<String, Object> map = new HashMap<String, Object>();

    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

    map.put("sheetName", "sheet1");

    listmap.add(map);

    WithdrawDeposit project;

    for (int j = 0; j < projects.size(); j++) {

      project = projects.get(j);

      Map<String, Object> mapValue = new HashMap<>();

      mapValue.put("applicationNo", project.getApplicationNo());

      mapValue.put("amount", project.getAmount());

      mapValue.put("opTime", format.format(project.getOpTime()));

      mapValue.put("payType", project.getPayType());

      mapValue.put("accountNo", project.getAccountNo());

      mapValue.put("status", project.getStatus());

      listmap.add(mapValue);

    }

    return listmap;

  }

}

3.excel导出帮助类

import java.util.List;

import java.util.Map;

 

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

 

/**

 * 导出Excel文档工具类

 *

 * @author bai

 * @date 2016-7-25

 */

public class ExcelUtil {

 

  /**

   * 创建excel文档，

   *

   * @param list        数据

   * @param keys        list中map的key数组集合

   * @param columnNames excel的列名

   */

  public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {

    // 创建excel工作簿

    Workbook wb = new HSSFWorkbook();

    // 创建第一个sheet（页），并命名

    Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());

    // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。

    for (int i = 0; i < keys.length; i++) {

      sheet.setColumnWidth((short) i, (short) (35.7 * 150));

    }

 

    // 创建第一行

    Row row = sheet.createRow((short) 0);

 

    // 创建两种单元格格式

    CellStyle cs = wb.createCellStyle();

    CellStyle cs2 = wb.createCellStyle();

 

    // 创建两种字体

    Font f = wb.createFont();

    Font f2 = wb.createFont();

 

    // 创建第一种字体样式（用于列名）

    f.setFontHeightInPoints((short) 10);

    f.setColor(IndexedColors.BLACK.getIndex());

    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

 

    // 创建第二种字体样式（用于值）

    f2.setFontHeightInPoints((short) 10);

    f2.setColor(IndexedColors.BLACK.getIndex());

 

    // 设置第一种单元格的样式（用于列名）

    cs.setFont(f);

    cs.setBorderLeft(CellStyle.BORDER_THIN);

    cs.setBorderRight(CellStyle.BORDER_THIN);

    cs.setBorderTop(CellStyle.BORDER_THIN);

    cs.setBorderBottom(CellStyle.BORDER_THIN);

    cs.setAlignment(CellStyle.ALIGN_CENTER);

 

    // 设置第二种单元格的样式（用于值）

    cs2.setFont(f2);

    cs2.setBorderLeft(CellStyle.BORDER_THIN);

    cs2.setBorderRight(CellStyle.BORDER_THIN);

    cs2.setBorderTop(CellStyle.BORDER_THIN);

    cs2.setBorderBottom(CellStyle.BORDER_THIN);

    cs2.setAlignment(CellStyle.ALIGN_CENTER);

    //设置列名

    for (int i = 0; i < columnNames.length; i++) {

      Cell cell = row.createCell(i);

      cell.setCellValue(columnNames[i]);

      cell.setCellStyle(cs);

    }

    //设置每行每列的值

    for (short i = 1; i < list.size(); i++) {

      // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的

      // 创建一行，在页sheet上

      Row row1 = sheet.createRow(i);

      // 在row行上创建一个方格

      for (short j = 0; j < keys.length; j++) {

        Cell cell = row1.createCell(j);

        cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());

        cell.setCellStyle(cs2);

      }

    }

    return wb;

  }

 

}

执行之后会在你浏览器的下边看到下载的文件直接双击打开就行。