java导出Excel内容

第一步，编写ExcelUtil类

package com.hc.oa.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.prefs.BackingStoreException;

public class ExcelUtil {

    /**
     * @param sheetName     工作表名，文件名，头部信息
     * @param rowNameList   列名
     * @param list          需要写入的数据
     * @param response      返回
     */
    public static void excelPort(String sheetName, List<String> rowNameList, List<Map<String, String>> list, HttpServletResponse response,HttpServletRequest request) {
        try {
            if (list.size() == 0) {
                throw new BackingStoreException("数据为空");
            }
            // 声明一个工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建sheet页
            XSSFSheet sheet = wb.createSheet(sheetName);
            sheet.setDefaultColumnWidth(19);

            // 全局加线样式
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

            // 记录标题信息
            TreeMap<String, Integer> headMap = new TreeMap<>();

            // 标题写入
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < rowNameList.size(); i++) {
                row.setHeight((short) 450);
                XSSFCell cell = row.createCell(i);
                String headName = rowNameList.get(i);
                cell.setCellValue(headName); //写入列名
                headMap.put(headName, i);
                cell.setCellStyle(cellStyle);
            }

            // 写入内容数据
            int ind = 1;
            for (Map<String, String> map : list) {
                XSSFRow r = sheet.createRow(ind++);
                for (Map.Entry<String, Integer> m : headMap.entrySet()) {
                    String name = m.getKey(); // 列名
                    String value = map.get(name); // value 不一定存在
                    XSSFCell cell2 = r.createCell(m.getValue());
                    if (value != null) {
                        cell2.setCellValue(value);
                    }
                    cell2.setCellStyle(cellStyle);
                }
            }

            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
//            response.setContentType("application/msexcel");



            String userAgent = request.getHeader("USER-AGENT");
            if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Edge")) { // IE浏览器
                sheetName = URLEncoder.encode(sheetName, "UTF-8");
            } else if (StringUtils.contains(userAgent, "Firefox")
            || StringUtils.contains(userAgent, "Chrome")
            || StringUtils.contains(userAgent, "Safari")) { // google,火狐浏览器
                sheetName = new String(sheetName.getBytes(), "ISO-8859-1");
            } else {
                sheetName = URLEncoder.encode(sheetName, "UTF-8"); // 其他浏览器
            }
            response.setHeader("Content-Disposition", "attachment; filename=" + sheetName);
            response.setContentType("application/vnd.ms-excel");
            wb.write(output);
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

第二步，编写java代码：

@PostMapping("/BingtuDateExcel")
@ApiOperation("进入百度推数据导出Excel接口")
public void BingtuScreenData(@RequestBody  BaiduProjectPromotion b, HttpServletResponse response, HttpServletRequest request) throws FileNotFoundException, ParseException {
    logger.info("进入百度推数据导出Excel接口");
    // 列名，表格第一行
    List<String> rowNameList = Arrays.asList("开始时间","结束时间", "项目名称","消耗", "消耗占比", "点击数","点击数占比","点击率","点击率占比","展现","展现占比");

    //获取当前日期
    Date date = new Date();
    Calendar calendar = Calendar.getInstance();
    //设置当前时间
    calendar.setTime(date);
    //格式转换
    SimpleDateFormat f = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss");
    //前一天
    calendar.add(Calendar.DATE,-30);
    String today =  f.format(date);
    String yesterday = f.format(calendar.getTime());

    List<BaiduProjectPromotion> bingTu = baiduService.selectdefaultData(b,today,yesterday);

    /** 数据库数据整合 */
    // 列名 数据
    List<Map<String, String>> list = new ArrayList<>();
    // SimpleDateFormat时间格式
    SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss");
    // 循环填表
    for (BaiduProjectPromotion item:bingTu) {
        Map<String, String> map = new HashMap<>();
        // 日期
        if (StringUtils.isNotBlank(b.getStartDate())&&StringUtils.isNotBlank(b.getEndDate())){
            map.put("开始时间",b.getStartDate()+"");
            map.put("结束时间",b.getStartDate()+"");
        }else{
            map.put("开始时间",yesterday+"");
            map.put("结束时间",today+"");
        }
        map.put("项目名称", item.getBusinessPointName() + "");
        DecimalFormat df = new DecimalFormat("0.00");

        map.put("消耗",  df.format(item.getCost()));

        BigDecimal decimal = new BigDecimal("100");
        BigDecimal CostZB = item.getCostZB().multiply(decimal);
        map.put("消耗占比", df.format(CostZB));

        map.put("点击数", df.format( item.getClick()));
        BigDecimal ClickZB = item.getClickZB().multiply(decimal);

        map.put("点击数占比", df.format(ClickZB));

        map.put("点击率", df.format(item.getCtr()));

        BigDecimal CtrZB = item.getCtrZB().multiply(decimal);

        map.put("点击率占比", df.format(CtrZB));
        map.put("展现", df.format(item.getImpression()));
        BigDecimal ImpressionZB = item.getImpressionZB().multiply(decimal);

        map.put("展现占比", df.format(ImpressionZB));

        list.add(map);
    }
    // 将需要写入Excel的数据传入
    ExcelUtil.excelPort("百度柄图推广数据表", rowNameList, list, response,request);
}

第三步：Excel下载接口，启动！！！