JAVA编程133——Excel导出表格数据并下载到客户端

一、需求：Excel导出表格数据到客户端

二、功能实现

1、页面发送一个下载导出请求

$("#button-export").click(
	function() {
		window.location.href = "../../area_export";
	});                   

2、controller层

package com.mollen.controller;

import com.mollen.dao.AreaDao;
import com.mollen.domain.Area;
import com.mollen.utils.DownLoadUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;

/**
 * @ClassName: AreaController
 * @Auther: Mollen
 * @CreateTime: 2019-01-23  09:27:40
 * @Description:
 */
@Controller
public class AreaController {
    @Autowired
    private AreaDao areaDao;
    
    /**
     * 数据导出
     * @return
     */
    @RequestMapping("/area_export")
    public String export(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //导出模板
        HSSFWorkbook book = template();

        //获得浏览器代理信息
        String finalFileName = DownLoadUtils.getFileName(request.getHeader("USER-AGENT"), "区域信息.xls");

        //设置HTTP响应头
        response.reset();		//重置 响应头
        response.setContentType("application/vnd.ms-excel");//告知浏览器下载文件，而不是直接打开，浏览器默认为打开
        response.addHeader("Content-Disposition", "attachment;filename="+finalFileName+"");//下载文件的名称

        //将数据写入输出流
        OutputStream os = response.getOutputStream();
        book.write(os);
        os.close();

        return "ok";

    }

    /**
     * excel导出模板
     * @return
     */
    public HSSFWorkbook template(){

        HSSFWorkbook template = new HSSFWorkbook();             //创建excel对象
        HSSFSheet sheet = template.createSheet();               //创建页

        int count = 0;
        HSSFRow row = sheet.createRow(count);                   //创建行
        row.createCell(0).setCellValue("id(c_id)");
        row.createCell(1).setCellValue("城市名称(c_city)");
        row.createCell(2).setCellValue("城市编码(c_citycode)");
        row.createCell(3).setCellValue("区域名称(c_district)");
        row.createCell(4).setCellValue("邮编(c_postcode)");
        row.createCell(5).setCellValue("省名称(c_province)");
        row.createCell(6).setCellValue("简编(c_shortcode)");
        count++;

        List<Area> areas = areaDao.findAll();

        for (Area area : areas) {
            row = sheet.createRow(count);
            row.createCell(0).setCellValue(area.getId());
            row.createCell(1).setCellValue(area.getCity());
            row.createCell(2).setCellValue(area.getCitycode());
            row.createCell(3).setCellValue(area.getDistrict());
            row.createCell(4).setCellValue(area.getPostcode());
            row.createCell(5).setCellValue(area.getProvince());
            row.createCell(6).setCellValue(area.getShortcode());
            count++;
        }
        return template;
    }
}

PS:严谨点Exception需要处理

三、DownLoadUtils工具

package com.mollen.utils;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Base64;

public class DownLoadUtils {

    public static String getFileName(String agent, String filename) throws UnsupportedEncodingException {
        if (agent.contains("MSIE")) {
            // IE浏览器
            filename = URLEncoder.encode(filename, "utf-8");
            filename = filename.replace("+", " ");
        } else if (agent.contains("Firefox")) {
            // 火狐浏览器
            Base64.Encoder base64Encoder = Base64.getEncoder();
            filename = "=?utf-8?B?" + new String(base64Encoder.encode(filename.getBytes("utf-8")),"UTF-8") + "?=";
        } else {
            // 其它浏览器
            filename = URLEncoder.encode(filename, "utf-8");
        }
        return filename;
    }
}