欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java 将查询的数据导出成excel文件

程序员文章站 2024-03-20 21:27:40
...

controller 层代码

 /**
     * 导出excel
     * @param globalId
     * @param createTime
     */
    @GetMapping("/Export")
    public void findSignDataExport(String globalId, String createTime, HttpServletRequest request, HttpServletResponse response){

        applyService.findSignDataExport(globalId, createTime,request,response);

    }

 

 serviceImpl 层代码  

 /**
     *  根据 globalId、创建日期,查询报名信息并导出excel
     * @param globalId
     * @param createTime
     */
    @Override
    public void findSignDataExport(String globalId, String createTime, HttpServletRequest request, HttpServletResponse response) {
        List<Map<String,String>> applyData = new ArrayList<>();
        /**
         * 先查询出所有报名表名,根据表名id 获取所有表中字段
         */
        List<Signinfos> signinfoses = signinfosMapper.selectTableName(globalId);
        for (int i= 0; i < signinfoses.size(); i ++){
            List<String> fieldName = signparamMapper.findFieldName(signinfoses.get(i).getId());
            /**
             *  创建字符串缓冲池,将集合中的数据拼接成字符串到数据库中查询
             */
            StringBuilder fields = new StringBuilder();
            for (int j = 0; j < fieldName.size(); j++){
                fields.append(fieldName.get(j));
                if(j != fieldName.size()-1){
                    fields.append(",");
                }
            }
            // 获取报名完整信息
            List<Map<String, String>> applyData1 = signMapper.findApplyData(fields.toString(), signinfoses.get(i).getTableName());
            /**
             * 将数据用新的map接收
             */
            for (int j=0; j < applyData1.size(); j++){
                Set<Map.Entry<String, String>> entries = applyData1.get(j).entrySet();
                Iterator<Map.Entry<String, String>> iterator = entries.iterator();
                Map<String,String> map = new HashMap<>(16);
                while(iterator.hasNext()){
                    Map.Entry<String, String> next = iterator.next();
                    switch (next.getKey()){
                        case "Id":
                            map.put("0",next.getValue());
                            break;
                        case "CreateTime":
                            map.put("1",next.getValue());
                            break;
                        case "Status":
                            map.put("2",String.valueOf(next.getValue()));
                            break;
                        default:
                            map.put(next.getKey(),next.getValue());
                    }
                }
                applyData.add(map);
            }

        }

        List<Map<String, String>> list = new ArrayList<>();
        List<String> titleName = new ArrayList<>();
        for (int i = 0; i < applyData.size(); i++){
            int index = 0;
            if(i == 0){
                Map<String, String> stringStringMap = applyData.get(i);
                Set<Map.Entry<String, String>> entries = stringStringMap.entrySet();
                Iterator<Map.Entry<String, String>> iterator = entries.iterator();
                while (iterator.hasNext()){
                    Map.Entry<String, String> next = iterator.next();
                    switch (next.getKey()){
                        case "0":
                            titleName.add("序号");
                            break;
                        case "1":
                            titleName.add("开始日期");
                            break;
                        case "2":
                            titleName.add("状态");
                            break;
                        default:
                            titleName.add(next.getKey());
                    }
                }
            }
            Map<String, String> stringStringMap = applyData.get(i);
            Map<String,String> newMap = new HashMap<>(16);
            Set<Map.Entry<String, String>> entries = stringStringMap.entrySet();
            Iterator<Map.Entry<String, String>> iterator = entries.iterator();
            while (iterator.hasNext()){
                Map.Entry<String, String> next = iterator.next();
                newMap.put(String.valueOf(index),next.getValue());
                index++;
            }
            list.add(newMap);
        }

        List<Title> paramName = signparamMapper.getParamName(globalId);
        for (int i = 0; i < titleName.size(); i++ ){
            for (int j = 0; j < paramName.size(); j++){
                if(titleName.get(i).equals(paramName.get(j).getKey())){
                    titleName.set(i,titleName.get(i).replace(paramName.get(j).getKey(), paramName.get(j).getName()));
                }


            }
        }

        // excel标题
        String[] title = new String[titleName.size()];
        // 导出测评的结果
        titleName.toArray(title);
//        title = new String[] { "测评人姓名", "员工编号", "联系方式", "所属部门", "岗位类别", "测评问卷总数", "待填写测评数量" };

        // 测评名称、测评时间
        String evaluationName = "";
        String evaluationTime = "";
        String[][] content = new String[list.size()][title.length];
        for (int i = 0; i < list.size(); i++) {
            content[i] = new String[title.length];
            Map<String, String> stringMap = list.get(i);
            Set<Map.Entry<String, String>> set = stringMap.entrySet();
            Iterator<Map.Entry<String, String>> iterator = set.iterator();
            while(iterator.hasNext()){
                Map.Entry<String, String> next = iterator.next();
                content[i][Integer.valueOf(next.getKey())] = String.valueOf(next.getValue()) ;
            }



        }
        // excel文件名
        String fileName = "报名列表" + System.currentTimeMillis() + ".xls";
        // sheet名
        String sheetName = evaluationName + "_" + evaluationTime.split(" ")[0];

        // 创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);

        // 响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

这个是抽取出来的公共方法 

// 发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

接下来是工具类

package tide.jstv.operationdata.utils;

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

public class ExcelUtil {

    /**
     * 导出Excel
     * 
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {

        // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步,创建单元格,并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        // 声明列对象
        HSSFCell cell = null;

        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }

        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
}

到这里就已经完成了