java实现导出数据为excel表格

导出excel表格

1.工具类:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class CsvUtil {
    private static final Logger logger = LoggerFactory.getLogger(CsvUtil.class);
    /**
     * CSV文件列分隔符
     */
    private static final String CSV_COLUMN_SEPARATOR = ",";

    /**
     * CSV文件列分隔符
     */
    private static final String CSV_RN = "\r\n";

    /**
     * @param dataList 集合数据
     * @param colNames 表头部数据
     * @param mapKey   查找的对应数据
     * @param1response 返回结果
     */
    public static boolean doExport(List<Map<String, Object>> dataList, String colNames[], String mapKey[], OutputStream os) {
        try {
            StringBuffer buf = new StringBuffer();
            // 完成数据csv文件的封装 // 输出列头
            for (int i = 0; i < colNames.length; i++) {
                buf.append(colNames[i]).append(CSV_COLUMN_SEPARATOR);
            }
            buf.append(CSV_RN);
            if (null != dataList) {
                // 输出数据
                for (int i = 0; i < dataList.size(); i++) {
                    for (int j = 0; j < mapKey.length; j++) {
                        buf.append(dataList.get(i).get(mapKey[j])).append(CSV_COLUMN_SEPARATOR);
                    }
                    buf.append(CSV_RN);
                }
            }
            // 写出响应
            os.write(buf.toString().getBytes("GBK"));
            os.flush();
            return true;
        } catch (Exception e) {
            logger.error("doExport错误...", e);
        }
        return false;
    }

    /**
     * @throws UnsupportedEncodingException * * setHeader
     */
    public static void responseSetProperties(String fileName, HttpServletResponse response) throws UnsupportedEncodingException {
        // 设置文件后缀
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String fn = fileName + sdf.format(new Date()).toString() + ".csv";
        // 读取字符编码
        String utf = "utf-8";
        // 设置响应
        response.setContentType("application/ms-txt");
        response.setCharacterEncoding(utf);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
    }
}

2.调用代码:

public void excelTalent(HttpServletResponse response,String talentName, String talentPhone, String cardType, String cardNum, String sex, String talentLev){

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //写入实体数据 实际应用中这些数据从数据库得到，
        List<TalentWebModel> talentWebModelList = talentService.getExcelTalentList(talentName, talentPhone, cardType, cardNum, sex, talentLev);
        List<Map<String, Object>> dataList = new ArrayList<>();
        String fileName = "人才信息";
        String sTitles[] = {"人才主键", "人才姓名", "人才头像", "人才手机号","证件类型","证件号", "人才认定级别", "性别", "单位", "行政职务", "创建时间", "修改时间"};
        String mapKeys[] = {"talentId", "talentName", "talentPhoto", "talentPhone","cardType","cardNum", "talentLev", "sex", "company", "duties", "createTime", "modifyTime"};
        Map<String, Object> map;
        for (TalentWebModel talentWebModel : talentWebModelList) {
            map = new HashMap<>();
            map.put("talentId", talentWebModel.getTalentId());
            map.put("talentName", talentWebModel.getTalentName());
            map.put("talentPhoto", talentWebModel.getTalentPhoto());
            map.put("talentPhone", ((char)(9))+talentWebModel.getTalentPhone());
            map.put("cardType", talentWebModel.getCardType());
            map.put("cardNum",((char)(9))+talentWebModel.getCardNum());
            map.put("talentLev", talentWebModel.getTalentLev());
            map.put("sex", talentWebModel.getSex());
            map.put("company", talentWebModel.getCompany());
            map.put("duties", talentWebModel.getDuties());
            map.put("createTime", ((char)(9))+sdf.format(talentWebModel.getCreateTime()));
            map.put("modifyTime", ((char)(9))+sdf.format(talentWebModel.getModifyTime()));
            dataList.add(map);
        }
        //输出流
        try (final OutputStream os = response.getOutputStream()) {
            CsvUtil.responseSetProperties(fileName, response);
            CsvUtil.doExport(dataList, sTitles, mapKeys, os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

注意:

编码的配置项,在util中可以配置;