springmvc导出excel并弹出下载框

最新推荐文章于 2022-08-11 09:44:53 发布

转载最新推荐文章于 2022-08-11 09:44:53 发布 · 90 阅读

文章标签：

#java

本文介绍了一种根据用户选择的列将Grid列表数据导出为Excel文件的方法，并提供了从前端触发导出请求到后端生成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) {

    //自己实现
    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;
  }

}

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