查询数据导出至excel文件

最新推荐文章于 2024-05-14 10:25:18 发布

壹叁壹肆

最新推荐文章于 2024-05-14 10:25:18 发布

阅读量283

点赞数

CC 4.0 BY-SA版权

分类专栏： java基础文章标签：导出至excel

本文链接：https://blog.youkuaiyun.com/qq_40524941/article/details/92837711

java基础专栏收录该内容

19 篇文章

订阅专栏

博客介绍了直接用代码将数据导出到Excel表的方法，给出了相关API链接，还提到从前端获取参数集合carRecList以及前端传参样式，并展示了结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

直接上代码导出数据到excel表

API:http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/FillPatternType.html

   /**   
     * @description 数据导出至Excel
     * @date 2019-06-16 16:35:22
     * @param sheetName sheet名
     * @param map 数据集
     * @param headers 标题数组
     * @param exportExcelName 导出文件名
     * */
    public static void export2Exc(String sheetName, List<HashMap<String, Object>> map, String[] headers,
                          String exportExcelName, HttpServletResponse aResponse) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(20);
        // 生成表格中非标题栏的样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.WHITE.index);//背景色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成表格中非标题栏的字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 设置表格标题栏的样式
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置标题栏字体
        XSSFFont titleFont = workbook.createFont();
        titleFont.setColor(HSSFColor.WHITE.index);
        titleFont.setFontHeightInPoints((short) 12);
        titleFont.setBold(true);
        // 把字体应用到当前的样式
        titleStyle.setFont(titleFont);
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        //填数据
        int index = 0;
        Iterator<HashMap<String, Object>> it = map.iterator();
        while (it.hasNext()){
            index++;
            row = sheet.createRow(index);
            Map<String, Object> data = it.next();
            int i = 1;
            for(String key : data.keySet()){
                XSSFCell cell = row.createCell(0); 
                cell.setCellValue(index);
                cell.setCellStyle(style);

                cell = row.createCell(i);//数据添加
                cell.setCellStyle(style);
                Object s = data.get(key);
                String ss = "";
                if (ComnUtil.isEmpty(s)){
                    ss = "";
                }else {
                    ss = s + "";
                }
                XSSFRichTextString text = new XSSFRichTextString(ss);
                cell.setCellValue(text);
                i++;
            }
        }
        //写文件/响应
        OutputStream out = null;
        try {
//            setResponseHeader(aResponse,exportExcelName);
//            OutputStream tmpOutputStream = aResponse.getOutputStream();
//            workbook.write(tmpOutputStream);
//            tmpOutputStream.flush();
//            tmpOutputStream.close();

            String tmpPath = "F:\\" + exportExcelName + ".xlsx";
            out = new FileOutputStream(tmpPath);
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            if(workbook != null){
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(out != null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void setResponseHeader(HttpServletResponse aResponse, String aFileName){
        try {
            try {
                aFileName = new String(aFileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            aResponse.setContentType("application/octet-stream;charset=ISO8859-1");
            aResponse.setHeader("Content-Disposition", "attachment;filename=" + aFileName);
            aResponse.addHeader("Pargam", "no-cache");
            aResponse.addHeader("Cache-Control", "no-cache");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

普通的从前端获得参数集合 carRecList

@Override
    public void export(Dto reqDto, HttpServletResponse response) {
        List<HashMap<String, Object>> carRecList = (List<HashMap<String, Object>>) reqDto.get("carRecList");
        if (ComnUtil.isEmpty(carRecList)){//校验空值
            throw new ComnException(ComnStatusCode.ERROR_PARAM, "carRecMaps.has.been.disabled");
        }
        String [] headList = {"序号","车牌号","所属人","联系方式","所属企业","进入时间","闸机号","车牌照片","驶离时间","闸机号","车牌照片","逗留时长"};
        ExportUtil.export2Exc("carRecord",carRecList,headList,"carRecord",response);

    }

前端传参样式:

结果展示: