java导出Excell报表

最新推荐文章于 2024-07-03 14:13:18 发布

有一個人

最新推荐文章于 2024-07-03 14:13:18 发布

阅读量595

点赞数

分类专栏： poi

本文链接：https://blog.csdn.net/qq_40074764/article/details/79439986

版权

poi 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

根据条件查询出结果,然后导出Excell报表,代码如下(控制层框架vert.x,持久层框架:beetlsql):

/**
 * PC后台用户管理导出报表
 * @param context
 * @param userExtend
 */
public void exportUserExcell(RoutingContext context, UserExtend userExtend) throws IOException {
    // 设置导出文件名
    String fileName = "用户信息";
    // 根据条件查询用户信息
    String registEndTime = userExtend.getRegistEndTime();
    if (StrUtil.isNotBlank(registEndTime)){
        userExtend.setRegistEndTime(registEndTime+" 23:59:59");
    }
    String upEndTime = userExtend.getUpEndTime();
    if (StrUtil.isNotBlank(upEndTime)){
        userExtend.setUpEndTime(upEndTime+" 23:59:59");
    }
    List<UserExtend> list = sqlManager.select("sysUser.queryManager", UserExtend.class, userExtend);
    for (UserExtend extend : list) {
        // 处理所在地
        String contactProvince = extend.getContactProvince();
        String contactCity = extend.getContactCity();
        if(StrUtil.isNotBlank(contactProvince)&&StrUtil.isNotBlank(contactCity)){
            List<String> toString = AddressUtil.addressCodeToString(contactProvince, contactCity);
            extend.setProvinceName(toString.get(0));
            extend.setCityName(toString.get(1));
            // 设置所在省市
            extend.setRealAddress(toString.get(0)+toString.get(1));
        }
        // 处理推荐人信息
        String recommendId = extend.getRecommendId();
        if (StrUtil.isNotBlank(recommendId)){
            // 根据推荐人信息查询推荐人
            SysUser user = new SysUser();
            user.setId(recommendId);
            List<UserExtend> select = sqlManager.select("sysUser.queryUser", UserExtend.class, user);
            UserExtend single = new UserExtend();
            if (null !=select && select.size()>0){
                single = select.get(0);
            }
            extend.setRecommendName(single.getName());
            extend.setRecommendMobile(single.getMobile());
            extend.setRecommendRoleName(single.getRoleName());
            extend.setRecommendNickName(single.getNickName());
        }
    }
    List<Map<String,Object>> listMap = createUserExcelRecord(list);
    // 定义key
    String[] keys = {"nickName","mobile","name","sex","realAddress","createTime","roleName","limitTime","recommendNickName","recommendRoleName","recommendMobile","upTime"};
    // 定义列名
    String[] columnNames={"昵称","手机","真实姓名","性别","所在地","注册时间","用户等级","会员有效期","推荐人昵称","推荐人等级","推荐人手机号","升级时间"};
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    ExcelUtil.createWorkBook(fileName, listMap, keys, columnNames).write(os);
    byte[] content = os.toByteArray();
    Buffer buffer = Buffer.buffer();
    buffer.appendBytes(content);
    // 设置response参数，可以打开下载页面
    context.response().putHeader("content-type", "application/octet-stream;charset=UTF-8");
    context.response().putHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
    context.response().putHeader("Pargam", "no-cache");
    context.response().putHeader("Cache-Control", "no-cache");
    context.response().end(buffer);
}

private List<Map<String,Object>> createUserExcelRecord(List<UserExtend> datalist) {
    List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
    UserExtend bean;
    for (int i = 0; i < datalist.size(); i++) {
        bean = datalist.get(i);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("nickName", bean.getNickName());
        map.put("mobile", bean.getMobile());
        map.put("name",bean.getName());
        String sex = bean.getSex();
        if(sex.equals("0")){
            map.put("sex","女");
        }else if (sex.equals("1")){
            map.put("sex","男");
        }
        map.put("realAddress",bean.getRealAddress());
        map.put("roleName",bean.getRoleName());
        map.put("recommendNickName",bean.getRecommendNickName());
        map.put("recommendRoleName",bean.getRecommendRoleName());
        map.put("recommendMobile", bean.getRecommendMobile());
        // 注册时间
        Date createTime = bean.getCreateTime();
        String format = DateUtil.format(createTime, "yyyy/MM/dd HH:mm");
        map.put("createTime", format);
        // 升级时间
        Date upTime = bean.getUpTime();
        String up = DateUtil.format(upTime, "yyyy/MM/dd HH:mm");
        if(StrUtil.isBlank(up)){
            map.put("upTime","未升级");
        }else {
            map.put("upTime",up);
        }
        // 会员有效期
        Date limitTime = bean.getLimitTime();
        String limit = DateUtil.format(limitTime, "yyyy/MM/dd HH:mm");
        if (StrUtil.isBlank(limit)){
            map.put("limitTime","无");
        }else {
            map.put("limitTime",limit);
        }
        listMap.add(map);
    }
    return listMap;
}

导出报表的工具类:

package org.silk.Util;

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

import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
	
	/**
	 * 导出Excel文档工具类
	 * @author xc
	 * @date 2018.01.06
	 */
	public static Workbook createWorkBook(String filename, List<Map<String,Object>> listMap, String[] keys, String[] columnNames){
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("sheet1");
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，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 (int i = 0; i < listMap.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow((short) (i+1));
            // 在row行上创建一个方格
            for(short j=0;j<columnNames.length;j++){
                Cell cell = row1.createCell(j);
                // cell.setCellValue(listMap.get(i).get(keys[j]) == null?"":listMap.get(i).get(keys[j]).toString());
                Object obj = listMap.get(i).get(keys[j];
                if (obj == null) {
                    cell.setCellValue("");
                }else{
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    } else if (obj instanceof Long) {
                        cell.setCellValue((Long) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    }else {
                     cell.setCellValue((String) obj);
                    }
                }

                cell.setCellStyle(cs2);
            }
        }
        return wb;
	}
}