cmf5 excel导出

    //导出Excel
    public function export($value='')
    {
        $list = Db::name('user')
            ->where('user_type', 2)
            ->field('user_login,user_password')
            ->order("create_time DESC")
            ->select();

        header('Content-Type:text/html;charset=utf-8');
        
        $xlsName  = "学员列表";
        $xlsCell = [
            ['user_login','账号名称'],
            ['user_password','密码'],
        ];

        downloadExcel($xlsName,$xlsCell,$list);
    } 
function downloadExcel($expTitle,$expCellName,$expTableData){
    //$xlsTitle = iconv('gb2312', 'utf-8', $expTitle);//文件名称
    $fileName = date('Y-m-d H:i:s',time());//or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
    $dataNum = count($expTableData);
    vendor("PHPExcel.PHPExcel1");
    $objPHPExcel = new \PHPExcel();
    $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
	
	
    for($i=0;$i<$cellNum;$i++){
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]); 
    } 
	//$objPHPExcel->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);
    for($i=0;$i<$dataNum;$i++){
        for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
        }             
    }  
    
    ob_end_clean();
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$fileName.'.xls"');
    header('Content-Disposition: attachment;filename="' . date('Y-m-d H-i-s') . '.xls"');

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
    $objWriter->save('php://output'); 
    exit;   
}

 

<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>万得数据库浏览器</title> <style> * { box-sizing: border-box; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; } body { margin: 0; padding: 20px; background-color: #f5f7fa; color: #333; } .container { max-width: 1200px; margin: 0 auto; background: white; border-radius: 10px; box-shadow: 0 0 20px rgba(0,0,0,0.1); padding: 25px; } h1 { color: #2c3e50; text-align: center; margin-bottom: 30px; } .search-box { display: flex; margin-bottom: 20px; position: relative; } #search-input { flex: 1; padding: 12px 20px; font-size: 16px; border: 2px solid #3498db; border-radius: 50px; outline: none; transition: all 0.3s; } #search-input:focus { border-color: #2980b9; box-shadow: 0 0 10px rgba(52, 152, 219, 0.3); } #search-button { position: absolute; right: 5px; top: 5px; background: #3498db; color: white; border: none; padding: 8px 20px; border-radius: 50px; cursor: pointer; font-weight: bold; transition: background 0.3s; } #search-button:hover { background: #2980b9; } .results-container { display: none; margin-top: 30px; } .table-card { background: white; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.05); margin-bottom: 20px; overflow: hidden; border: 1px solid #e0e6ed; } .table-header { background: #3498db; color: white; padding: 12px 20px; display: flex; justify-content: space-between; align-items: center; } .table-name { font-weight: bold; font-size: 18px; } .export-btn { background: #27ae60; color: white; border: none; padding: 6px 15px; border-radius: 4px; cursor: pointer; font-size: 14px; transition: background 0.3s; } .export-btn:hover { background: #219653; } .columns-table { width: 100%; border-collapse: collapse; } .columns-table th { background: #f8f9fa; text-align: left; padding: 12px 15px; border-bottom: 2px solid #e0e6ed; } .columns-table td { padding: 10px 15px; border-bottom: 1px solid #e0e6ed; } .columns-table tr:nth-child(even) { background-color: #f8f9fa; } .no-results { text-align: center; padding: 30px; color: #7f8c8d; font-style: italic; } .loading { display: none; text-align: center; padding: 20px; } .loading-spinner { border: 4px solid #f3f3f3; border-top: 4px solid #3498db; border-radius: 50%; width: 30px; height: 30px; animation: spin 1s linear infinite; margin: 0 auto; } @keyframes spin { 0% { transform: rotate(0deg); } 100% { transform: rotate(360deg); } } .error-message { color: #e74c3c; text-align: center; padding: 20px; display: none; } .export-dictionary-btn { background: linear-gradient(to right, #8e44ad, #9b59b6); color: white; border: none; padding: 12px 30px; border-radius: 50px; cursor: pointer; font-size: 16px; font-weight: bold; box-shadow: 0 4px 10px rgba(0,0,0,0.1); transition: all 0.3s ease; display: inline-block; margin: 20px auto; } .export-dictionary-btn:hover { transform: translateY(-2px); box-shadow: 0 6px 15px rgba(0,0,0,0.2); background: linear-gradient(to right, #9b59b6, #8e44ad); } </style> </head> <body> <div class="container"> <h1>万得数据库浏览器</h1> <div class="search-box"> <input type="text" id="search-input" placeholder="输入表名、字段名或注释进行搜索..."> <button id="search-button">搜索</button> </div> <div style="text-align: center;"> <button id="export-dictionary" class="export-dictionary-btn"> <i class="fas fa-book"></i> 导出数据库字典 </button> </div> <div class="loading"> <div class="loading-spinner"></div> <p>正在搜索,请稍候...</p> </div> <div class="error-message" id="error-message"></div> <div class="results-container" id="results-container"> <!-- 搜索结果将在这里动态显示 --> </div> </div> <script> // 在<script>标签内添加 document.getElementById('export-dictionary').addEventListener('click', function() { // 显示加载提示 const loadingElement = document.querySelector('.loading'); loadingElement.style.display = 'block'; loadingElement.querySelector('p').textContent = '正在生成数据库字典,请稍候...'; // 发起导出请求 window.location.href = '/qwind/export/dictionary'; // 2秒后隐藏加载提示(实际导出时间可能更长) setTimeout(() => { loadingElement.style.display = 'none'; }, 2000); }); document.addEventListener('DOMContentLoaded', function() { const searchInput = document.getElementById('search-input'); const searchButton = document.getElementById('search-button'); const resultsContainer = document.getElementById('results-container'); const loadingElement = document.querySelector('.loading'); const errorMessage = document.getElementById('error-message'); // 搜索按钮点击事件 searchButton.addEventListener('click', performSearch); // 回车键搜索 searchInput.addEventListener('keyup', function(event) { if (event.key === 'Enter') { performSearch(); } }); function performSearch() { const searchTerm = searchInput.value.trim(); if (!searchTerm) { resultsContainer.style.display = 'none'; errorMessage.style.display = 'block'; errorMessage.textContent = '请输入搜索内容'; return; } // 显示加载动画 loadingElement.style.display = 'block'; resultsContainer.style.display = 'none'; errorMessage.style.display = 'none'; // 发送AJAX请求 fetch(`/qwind/search?q=${encodeURIComponent(searchTerm)}`) .then(response => { if (!response.ok) { throw new Error('搜索失败'); } return response.json(); }) .then(data => { loadingElement.style.display = 'none'; if ('error' in data) { errorMessage.style.display = 'block'; errorMessage.textContent = `错误: ${data.error}`; return; } // 处理搜索结果 displayResults(data); }) .catch(error => { loadingElement.style.display = 'none'; errorMessage.style.display = 'block'; errorMessage.textContent = `请求失败: ${error.message}`; }); } function displayResults(data) { resultsContainer.innerHTML = ''; if (Object.keys(data).length === 0) { resultsContainer.innerHTML = ` <div class="no-results"> <p>没有找到匹配的表或字段</p> </div> `; resultsContainer.style.display = 'block'; return; } for (const [tableName, columns] of Object.entries(data)) { const tableCard = document.createElement('div'); tableCard.className = 'table-card'; tableCard.innerHTML = ` <div class="table-header"> <div class="table-name">${tableName}</div> <button class="export-btn" data-table="${tableName}">导出数据</button> </div> <table class="columns-table"> <thead> <tr> <th width="40%">字段名</th> <th width="60%">注释/字段名</th> </tr> </thead> <tbody> ${columns.map(col => ` <tr> <td>${col.name}</td> <td>${col.comment}</td> </tr> `).join('')} </tbody> </table> `; resultsContainer.appendChild(tableCard); } // 添加导出按钮事件 document.querySelectorAll('.export-btn').forEach(btn => { btn.addEventListener('click', function() { const tableName = this.getAttribute('data-table'); window.location.href = `/qwind/export/${tableName}`; }); }); resultsContainer.style.display = 'block'; } }); </script> </body> </html> from flask import Flask, render_template, request, send_file, jsonify import cx_Oracle import csv import io import os from flask import send_file from openpyxl import Workbook import io import pandas as pd app = Flask(__name__) # Oracle数据库配置 ORACLE_USER = os.getenv('ORACLE_USER', 'az_wasp') ORACLE_PASSWORD = os.getenv('ORACLE_PASSWORD', 'cmf+2011') ORACLE_DSN = os.getenv('ORACLE_DSN', '192.168.125.66/PDB_CMWADEV') def get_db_connection(): return cx_Oracle.connect(ORACLE_USER, ORACLE_PASSWORD, ORACLE_DSN) @app.route('/qwind') def index(): """显示搜索页面""" return render_template('index.html') @app.route('/qwind/search') def search_tables(): """根据查询条件搜索表和字段""" search_term = request.args.get('q', '').strip().upper() try: with get_db_connection() as conn: cursor = conn.cursor() # 查询匹配的表和字段 query = """ SELECT t.table_name, c.column_name, NVL(com.comments, c.column_name) AS column_comment FROM all_tables@wd_dblink_20230908 t JOIN all_tab_columns@wd_dblink_20230908 c ON t.table_name = c.table_name LEFT JOIN all_col_comments@wd_dblink_20230908 com ON c.table_name = com.table_name AND c.column_name = com.column_name WHERE (t.owner = 'WIND_FILESYNC' and c.owner = 'WIND_FILESYNC' and com.owner = 'WIND_FILESYNC') and t.table_name LIKE :1 OR c.column_name LIKE :1 OR com.comments LIKE :1 ORDER BY t.table_name, c.column_id """ # 添加通配符进行模糊匹配 search_pattern = f'%{search_term}%' cursor.execute(query, [search_pattern]) # 组织结果数据 results = {} for row in cursor: table_name, column_name, column_comment = row if table_name not in results: results[table_name] = [] results[table_name].append({ 'name': column_name, 'comment': column_comment }) return jsonify(results) except Exception as e: return jsonify({'error': str(e)}), 500 @app.route('/qwind/export/<table_name>') def export_table(table_name): """导出表数据为CSV""" try: with get_db_connection() as conn: cursor = conn.cursor() # 获取字段注释作为列名 col_query = """ SELECT col.column_name, NVL(com.comments, col.column_name) AS column_comment FROM all_tab_columns@wd_dblink_20230908 col LEFT JOIN all_col_comments@wd_dblink_20230908 com ON col.table_name = com.table_name AND col.column_name = com.column_name WHERE col.table_name = :1 and (col.owner = 'WIND_FILESYNC' and com.owner = 'WIND_FILESYNC') ORDER BY col.column_id """ cursor.execute(col_query, [table_name.upper()]) columns = cursor.fetchall() headers = [col[1] for col in columns] col_names = [col[0] for col in columns] # 构建数据查询(限制10万行) data_query = f""" SELECT {', '.join(col_names)} FROM wind_filesync.{table_name}@wd_dblink_20230908 WHERE ROWNUM <= 100000 """ print(data_query) cursor.execute(data_query) # 创建CSV内存文件 output = io.StringIO() writer = csv.writer(output) writer.writerow(headers) # 写入表头(字段注释) writer.writerows(cursor) # 写入数据 # 返回CSV文件 output.seek(0) return send_file( io.BytesIO(output.getvalue().encode()), mimetype='text/csv', as_attachment=True, attachment_filename=f'{table_name}_data.csv' ) except Exception as e: return f"Export failed: {str(e)}", 500 # 添加新路由 @app.route('/qwind/export/dictionary') def export_dictionary(): """导出数据库表和字段字典为Excel文件""" try: with get_db_connection() as conn: cursor = conn.cursor() # 执行提供的SQL查询 sql = """ SELECT t.table_name AS 表名, t2.comments AS 表中文名, t.column_name AS 字段名, t.comments AS 字段中文名, t4.data_type AS 字段类型, t4.data_length AS 字段长度, t3.num_rows AS "行数量(近似值)" FROM ALL_COL_COMMENTS@WD_DBLINK_20230908 t LEFT JOIN (SELECT * FROM ALL_TAB_COMMENTS@WD_DBLINK_20230908 WHERE owner = 'WIND_FILESYNC') t2 ON t.table_name = t2.table_name LEFT JOIN (SELECT * FROM ALL_TABLES@WD_DBLINK_20230908 t WHERE t.owner = 'WIND_FILESYNC') t3 ON t.table_name = t3.table_name LEFT JOIN (SELECT * FROM all_tab_columns@WD_DBLINK_20230908 WHERE owner = 'WIND_FILESYNC') t4 ON t.table_name = t4.table_name AND t.column_name = t4.column_name WHERE t.owner = 'WIND_FILESYNC' AND T.TABLE_NAME NOT LIKE '%TB_OBJECT%' AND T.TABLE_NAME NOT LIKE '%WINDHASH%' AND T.TABLE_NAME NOT LIKE '%TEST%' AND T.TABLE_NAME NOT LIKE '%==%' """ cursor.execute(sql) # 创建Excel工作簿 wb = Workbook() ws = wb.active ws.title = "数据库字典" # 添加表头 headers = [desc[0] for desc in cursor.description] for col_num, header in enumerate(headers, 1): ws.cell(row=1, column=col_num, value=header) # 添加数据 for row_num, row_data in enumerate(cursor, 2): for col_num, cell_value in enumerate(row_data, 1): ws.cell(row=row_num, column=col_num, value=cell_value) # 调整列宽 for col in ws.columns: max_length = 0 column = col[0].column_letter for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = (max_length + 2) * 1.2 ws.column_dimensions[column].width = adjusted_width # 保存到内存 virtual_workbook = io.BytesIO() wb.save(virtual_workbook) virtual_workbook.seek(0) return send_file( virtual_workbook, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', as_attachment=True, attachment_filename='数据库字典.xlsx' ) except Exception as e: return f"导出字典失败: {str(e)}", 500 if __name__ == '__main__': #app.config['APPLICATION_ROOT'] = '/qwind' app.run(host='0.0.0.0', port=9093, debug=True) 我的前后端代码都放到了后端服务器,需要通过前端服务器的nginx进行访问,帮我配置nginx
06-27
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值