Экспорт данных MYSQL в Excel / CSV через php

Я думаю, это то, что вы ищете

Вы можете создать свой собственный файл, указав этот адрес: http://www.programmingfacts.com/export-mysql-data-into-excelcsv-via-php/

Я не могу добавить рабочий код здесь sth is wrong = /

но сделайте все \\ t до \ t и все \\ n до \ n

Чтобы создать файл .CSV с синтаксисом, подходящим для EXCEL, вы можете использовать базовый SQL:

SELECT * FROM mytable INTO OUTFILE '/mytable.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; 

См. Руководство здесь .

Я так много искал для этого в google, но единственный простой код и IS WORKING – это тот, который приведен ниже. Создайте файл downloadxls.php (или назовите его whatever.php), скопируйте / вставьте код ниже, заполните имя хоста, имя пользователя, пароль, базу данных и таблицы в начале кода, и там у вас очень хороший рабочий код , Затем загрузите файл php в свой браузер, и файл xls будет загружен. Я пробовал, и я использую его.

Всего наилучшего 🙂

  <?php // Author: Linmic, email: linmicya@gmail.com $host = ""; // your db host (ip/dn) $user = ""; // your db's privileged user account $password = ""; // and it's password $db_name = ""; // db name $tbl_name = ""; // table name of the selected db $link = mysql_connect ($host, $user, $password) or die('Could not connect: ' . mysql_error()); mysql_select_db($db_name) or die('Could not select database'); $select = "SELECT * FROM `".$tbl_name."`"; mysql_query('SET NAMES utf8;'); $export = mysql_query($select); //$fields = mysql_num_rows($export); // thanks to Eric $fields = mysql_num_fields($export); // by KAOSFORGE $col_title=""; $data=""; for ($i = 0; $i < $fields; $i++) { $col_title .= '<Cell ss:StyleID="2"><Data ss:Type="String">'.mysql_field_name($export, $i).'</Data></Cell>'; } $col_title = '<Row>'.$col_title.'</Row>'; while($row = mysql_fetch_row($export)) { $line = ''; foreach($row as $value) { if ((!isset($value)) OR ($value == "")) { $value = '<Cell ss:StyleID="1"><Data ss:Type="String"></Data></Cell>\t'; } else { $value = str_replace('"', '', $value); $value = '<Cell ss:StyleID="1"><Data ss:Type="String">' . $value . '</Data></Cell>\t'; } $line .= $value; } $data .= trim("<Row>".$line."</Row>")."\n"; } $data = str_replace("\r","",$data); header("Content-Type: application/vnd.ms-excel;"); header("Content-Disposition: attachment; filename=export.xls"); header("Pragma: no-cache"); header("Expires: 0"); $xls_header = '<?xml version="1.0" encoding="utf-8"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author></Author> <LastAuthor></LastAuthor> <Company></Company> </DocumentProperties> <Styles> <Style ss:ID="1"> <Alignment ss:Horizontal="Left"/> </Style> <Style ss:ID="2"> <Alignment ss:Horizontal="Left"/> <Font ss:Bold="1"/> </Style> </Styles> <Worksheet ss:Name="Export"> <Table>'; $xls_footer = '</Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Selected/> <FreezePanes/> <FrozenNoSplit/> <SplitHorizontal>1</SplitHorizontal> <TopRowBottomPane>1</TopRowBottomPane> </WorksheetOptions> </Worksheet> </Workbook>'; print $xls_header.$col_title.$data.$xls_footer; exit; ?> } else {  <?php // Author: Linmic, email: linmicya@gmail.com $host = ""; // your db host (ip/dn) $user = ""; // your db's privileged user account $password = ""; // and it's password $db_name = ""; // db name $tbl_name = ""; // table name of the selected db $link = mysql_connect ($host, $user, $password) or die('Could not connect: ' . mysql_error()); mysql_select_db($db_name) or die('Could not select database'); $select = "SELECT * FROM `".$tbl_name."`"; mysql_query('SET NAMES utf8;'); $export = mysql_query($select); //$fields = mysql_num_rows($export); // thanks to Eric $fields = mysql_num_fields($export); // by KAOSFORGE $col_title=""; $data=""; for ($i = 0; $i < $fields; $i++) { $col_title .= '<Cell ss:StyleID="2"><Data ss:Type="String">'.mysql_field_name($export, $i).'</Data></Cell>'; } $col_title = '<Row>'.$col_title.'</Row>'; while($row = mysql_fetch_row($export)) { $line = ''; foreach($row as $value) { if ((!isset($value)) OR ($value == "")) { $value = '<Cell ss:StyleID="1"><Data ss:Type="String"></Data></Cell>\t'; } else { $value = str_replace('"', '', $value); $value = '<Cell ss:StyleID="1"><Data ss:Type="String">' . $value . '</Data></Cell>\t'; } $line .= $value; } $data .= trim("<Row>".$line."</Row>")."\n"; } $data = str_replace("\r","",$data); header("Content-Type: application/vnd.ms-excel;"); header("Content-Disposition: attachment; filename=export.xls"); header("Pragma: no-cache"); header("Expires: 0"); $xls_header = '<?xml version="1.0" encoding="utf-8"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author></Author> <LastAuthor></LastAuthor> <Company></Company> </DocumentProperties> <Styles> <Style ss:ID="1"> <Alignment ss:Horizontal="Left"/> </Style> <Style ss:ID="2"> <Alignment ss:Horizontal="Left"/> <Font ss:Bold="1"/> </Style> </Styles> <Worksheet ss:Name="Export"> <Table>'; $xls_footer = '</Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Selected/> <FreezePanes/> <FrozenNoSplit/> <SplitHorizontal>1</SplitHorizontal> <TopRowBottomPane>1</TopRowBottomPane> </WorksheetOptions> </Worksheet> </Workbook>'; print $xls_header.$col_title.$data.$xls_footer; exit; ?> 

Если вы хотите использовать PHP, рассмотрите функцию fputcsv . Но вы можете экспортировать из MySQL в текстовый формат без PHP. См. Эту страницу при использовании mysqldump.

Кнопка «Добавить экспорт»:

 <button type="button" class="btn btn-warning btn-sm" onClick="tableToExcel('testTable', 'W3C Example Table')" title="Export to Excel">Export</button></div><br/> 

Добавить идентификатор в таблицу:

 <table class="table table-bordered table-hover " border="1" id="testTable" > 

Добавить скрипт ниже тела:

 <script type="text/javascript"> var tableToExcel = (function() { var uri = 'data:application/vnd.ms-excel;base64,' , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>' , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) } , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) } return function(table, name) { if (!table.nodeType) table = document.getElementById(table) var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML} window.location.href = uri + base64(format(template, ctx)) } })()</script> 

Попробуйте функцию fputcsv. Кроме того, Workbench MySql CE (5.2) является хорошим инструментом для экспорта данных и получения изображения базы данных в виде диаграммы.

 <?php //EDIT YOUR MySQL Connection Info: $DB_Server = "localhost"; //your MySQL Server $DB_Username = "root"; //your MySQL User Name $DB_Password = ""; //your MySQL Password $DB_DBName = "school"; //your MySQL Database Name $DB_TBLName = "s_question"; //your MySQL Table Name if(isset($_POST['exp_stdque'])) { $exstdid = $_POST['expstd']; //$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser //as parameters in a query string, so that this code may be easily reused for //any MySQL table or any MySQL database on your server //DEFINE SQL QUERY: //edit this to suit your needs $sql = "Select * from $DB_TBLName WHERE std_id = $exstdid"; //Optional: print out title to top of Excel or Word file with Timestamp //for when file was generated: //set $Use_Titel = 1 to generate title, 0 not to use title $Use_Title = 1; //define date for title: EDIT this to create the time-format you need $now_date = DATE('mdY H:i'); //define title for .doc or .xls file: EDIT this if you want $title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date"; /* Leave the connection info below as it is: just edit the above. (Editing of code past this point recommended only for advanced users.) */ //create MySQL connection $Connect = @MYSQL_CONNECT($DB_Server, $DB_Username, $DB_Password) or DIE("Couldn't connect to MySQL:<br>" . MYSQL_ERROR() . "<br>" . MYSQL_ERRNO()); //select database $Db = @MYSQL_SELECT_DB($DB_DBName, $Connect) or DIE("Couldn't select database:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO()); //execute query $result = @MYSQL_QUERY($sql,$Connect) or DIE("Couldn't execute query:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO()); //if this parameter is included ($w=1), file returned will be in word format ('.doc') //if parameter is not included, file returned will be in excel format ('.xls') IF (ISSET($w) && ($w==1)) { $file_type = "msword"; $file_ending = "doc"; }ELSE { $file_type = "vnd.ms-excel"; $file_ending = "xls"; } //header info for browser: determines file type ('.doc' or '.xls') HEADER("Content-Type: application/$file_type"); HEADER("Content-Disposition: attachment; filename=database_dump.$file_ending"); HEADER("Pragma: no-cache"); HEADER("Expires: 0"); /* Start of Formatting for Word or Excel */ IF (ISSET($w) && ($w==1)) //check for $w again { /* FORMATTING FOR WORD DOCUMENTS ('.doc') */ //create title with timestamp: IF ($Use_Title == 1) { ECHO("$title\n\n"); } //define separator (defines columns in excel & tabs in word) $sep = "\n"; //new line character WHILE($row = MYSQL_FETCH_ROW($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; FOR($j=0; $j<mysql_num_fields($result);$j++) { //define field names $field_name = MYSQL_FIELD_NAME($result,$j); //will show name of fields $schema_insert .= "$field_name:\t"; IF(!ISSET($row[$j])) { $schema_insert .= "NULL".$sep; } ELSEIF ($row[$j] != "") { $schema_insert .= "$row[$j]".$sep; } ELSE { $schema_insert .= "".$sep; } } $schema_insert = STR_REPLACE($sep."$", "", $schema_insert); $schema_insert .= "\t"; PRINT(TRIM($schema_insert)); //end of each mysql row //creates line to separate data from each MySQL table row PRINT "\n----------------------------------------------------\n"; } }ELSE{ /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */ //create title with timestamp: IF ($Use_Title == 1) { ECHO("$title\n"); } //define separator (defines columns in excel & tabs in word) $sep = "\t"; //tabbed character //start of printing column names as names of MySQL fields FOR ($i = 0; $i < MYSQL_NUM_FIELDS($result); $i++) { ECHO MYSQL_FIELD_NAME($result,$i) . "\t"; } PRINT("\n"); //end of printing column names //start while loop to get data WHILE($row = MYSQL_FETCH_ROW($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; FOR($j=0; $j<mysql_num_fields($result);$j++) { IF(!ISSET($row[$j])) $schema_insert .= "NULL".$sep; ELSEIF ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; ELSE $schema_insert .= "".$sep; } $schema_insert = STR_REPLACE($sep."$", "", $schema_insert); //following fix suggested by Josue (thanks, Josue!) //this corrects output in excel when table fields contain \n or \r //these two characters are now replaced with a space $schema_insert = PREG_REPLACE("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; PRINT(TRIM($schema_insert)); PRINT "\n"; } } } ?>