java将数据库中的数据导入到excel表中

最新推荐文章于 2024-07-24 09:54:02 发布

最爱陌生人

最新推荐文章于 2024-07-24 09:54:02 发布

阅读量700

点赞数

分类专栏： web开发

本文链接：https://blog.csdn.net/u014369382/article/details/22320973

版权

web开发专栏收录该内容

37 篇文章 0 订阅

订阅专栏

//格式化导出的数据
        reportList = formatExportData (reportList,params.type)
        def fileName = new String (params.label.getBytes("utf-8"), "ISO_8859_1")+"_" + new Date().format("yyyy-MM-dd_HH-mm-ss")+".xls"
        // 生成一个HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        // 设置显示的字体以及相关的样式
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 8);
        font.setFontName("Courier   New");
        font.setItalic(false);
        font.setStrikeout(false);
        // 设置标题栏单元格的样式
        HSSFCellStyle cellHeadStyle = wb.createCellStyle();
        cellHeadStyle.setAlignment (HSSFCellStyle.ALIGN_LEFT);
        cellHeadStyle.setBorderBottom (HSSFCellStyle.BORDER_DOUBLE);
        cellHeadStyle.setBorderLeft((short) 1);
        cellHeadStyle.setBorderRight((short) 1);
        cellHeadStyle.setBorderTop (HSSFCellStyle.BORDER_DOUBLE);
        cellHeadStyle.setFillForegroundColor (HSSFColor.LIGHT_ORANGE.index);
        cellHeadStyle.setFillPattern (HSSFCellStyle.SOLID_FOREGROUND);
        // 设置数据行的字体以及以及相关的对齐方式
        HSSFCellStyle cellDataStyle = wb.createCellStyle();
        cellDataStyle.setAlignment (HSSFCellStyle.ALIGN_LEFT);
        cellDataStyle.setFont(font);
        // 新建一个HSSFSheet对象

HSSFSheet sheet = wb.createSheet (params.label);

        if (params.keywords) {
            def keywords = params.keywords.split(",")
            //统一设置列宽
            for(int i=0; i<keywords.size();i++){
                sheet.setColumnWidth((short)i,(short) 5000)
            }

//先画查询条件
HSSFRow row = sheet.createRow((short)0);

            //订单ID
            HSSFCell searchConditionCell = row.createCell((short)0)
            searchConditionCell.setCellValue("查询条件："+params.searchCondition)

            int i = 1
            row = sheet.createRow((short)i)
            def j = 0
            keywords.each {
                HSSFCell cellHead = row.createCell ((short)(j));
                cellHead.setCellStyle(cellHeadStyle);
                cellHead.setCellValue (nameMap."$it".toString());
                ++j
            }
            i = 2

            SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
            reportList.each { record ->
                row = sheet.createRow(i)
                j = 0
                keywords.each {
                    HSSFCell cellHead = row.createCell((short)(j));
                    cellHead.setCellStyle (cellDataStyle);
                    if (it.equals("createTime")) {
                        if(record."$it" != null && (record."$it" instanceof Date)){
                            cellHead.setCellValue (formater.format(record."$it"));
                        }else{
                            cellHead.setCellValue (record."$it");
                        }
                    }else if(it.equals("position") || it.equals("duration")){
                        cellHead.setCellValue (record."$it"?:"-");
                    }else if(it.equals("pm") || it.equals("cpm")){
                        cellHead.setCellValue (record."$it"? 1000 * record."$it" : "");
                    } else {
                        cellHead.setCellValue (record."$it");
                    }
                    ++j
                }
                ++i
            }
        }
        // 利用输出流将数据集写入到硬盘的文件中，操作结束后，关闭输出流
        ExcelUtil.exportToExcel(response, fileName, wb)