of with utf8 gbk encoding conversion based on php export to Excel or CSV
- 2020-06-19 09:57:01
- OfStack
php imported into excel because the utf8 encoding in xp system does not support all utf8 encoding transcoding 1 solved perfectly
The utf-8 coding case
Php code
Php code
The gbk coding case
Php code
Php code
Download to excel when you visit the website
If you want to differentiate the cells
Use the table form for web pages
= = = = = = = = = = = = = = = = = = = = = = other way = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
1. Simple Excel
2. Simple CSV
A closed function can also be made:
Closed function 1:
Closed function 2:
3. Use the excel class
Class 2
-- Function description
Read the Excel file
function Read_Excel_File($ExcelFile,$Result)
$ExcelFile Excel file name
$Result returns the result
The function returns 0 normally, otherwise an error message is returned
Returns an array of values
The value of $result[sheet name][row][column] is the corresponding value of Excel Cell
Create the Excel file
function Create_Excel_File($ExcelFile,$Data)
$ExcelFile Excel file name
$Data Excel table data
Write the function at the beginning of the PHP script
Case 1:
Example 2:
The utf-8 coding case
Php code
<?php
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=11.xls ");
header("Content-Transfer-Encoding: binary ");
?>
Php code
<?
$filename="php Imported into the excel-utf-8 coding ";
$filename=iconv("utf-8", "gb2312", $filename);
echo $filename;
?>
The gbk coding case
Php code
<?php
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=11.xls ");
header("Content-Transfer-Encoding: binary ");
?>
Php code
0.<?
0.$filename="php Imported into the excel-utf-8 coding ";
0.echo $filename;
0.?>
Download to excel when you visit the website
If you want to differentiate the cells
Use the table form for web pages
= = = = = = = = = = = = = = = = = = = = = = other way = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
1. Simple Excel
0.<?php
0.header("Content-type:application/vnd.ms-excel");
0.header("Content-Disposition:filename=php2excel.xls");
0.
0.echo "A1/t B1/t C1/n";
0.echo "A2/t B2/t C2/n";
0.echo "A3/t B3/t C3/n";
0.echo "A4/t B4/t C4/n";
0.?>
2. Simple CSV
<?php
$action =$_GET['action'];
if ($action=='make'){
$fp = fopen("demo_csv.csv","a"); // Open the csv The file is created if it does not exist
$title = array("First_Name","Last_Name","Contact_Email","Telephone"); // The first 1 Rows of data
$data_1 = array("42343","423432","4234","4234");
$data_2 = array("4234","Last_Name","Contact_Email","Telephone");
$title = implode(",",$title); // with ' Split into strings
$data_1 = implode(",",$data_1); // with ' Split into strings
$data_2 = implode(",",$data_2); // with ' Split into strings
$data_str =$title."/r/n".$data_1."/r/n".$data_2."/r/n"; // Add a newline character
fwrite($fp,$data_str); // Write data
fclose($fp); // Close the file handle
echo " Generate success ";
}
echo "<br>";
echo "<a href='?action=make'> generate csv file </a>";
?>
A closed function can also be made:
Closed function 1:
function exportToCsv($csv_data, $filename = 'export.csv') {
$csv_terminated = "/n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "//";
// Gets the data from the database
$schema_insert = '';
$out = '';
// Format the data
foreach ($csv_data as $row)
{
$schema_insert = '';
$fields_cnt = count($row);
//printr($row);
$tmp_str = '';
foreach($row as $v)
{
$tmp_str .= $csv_enclosed.str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $v).$csv_enclosed.$csv_separator;
} // end for
$tmp_str = substr($tmp_str, 0, -1);
$schema_insert .= $tmp_str;
$out .= $schema_insert;
$out .= $csv_terminated;
} // end while
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-type: text/x-csv");
header("Content-Disposition:filename=$filename");
echo $out;
}
/*
$csv_data = array(array('Name', 'Address'));
array_push($csv_data, array($row['name'],$row['address']));
...
exportToCsv($csv_data,'new_file.csv');
*/
Closed function 2:
<?
/**
* Simple class to properly output CSV data to clients. PHP 5 has a built
* in method to do the same for writing to files (fputcsv()), but many times
* going right to the client is beneficial.
*
* @author Jon Gales
*/
class CSV_Writer {
public $data = array();
public $deliminator;
/**
* Loads data and optionally a deliminator. Data is assumed to be an array
* of associative arrays.
*
* @param array $data
* @param string $deliminator
*/
function __construct($data, $deliminator = ",")
{
if (!is_array($data))
{
throw new Exception('CSV_Writer only accepts data as arrays');
}
$this->data = $data;
$this->deliminator = $deliminator;
}
private function wrap_with_quotes($data)
{
$data = preg_replace('/"(.+)"/', '""$1""', $data);
return sprintf('"%s"', $data);
}
/**
* Echos the escaped CSV file with chosen delimeter
*
* @return void
*/
public function output()
{
foreach ($this->data as $row)
{
$quoted_data = array_map(array('CSV_Writer', 'wrap_with_quotes'), $row);
echo sprintf("%s/n", implode($this->deliminator, $quoted_data));
}
}
/**
* Sets proper Content-Type header and attachment for the CSV outpu
*
* @param string $name
* @return void
*/
public function headers($name)
{
header('Content-Type: application/csv');
header("Content-disposition: attachment; filename={$name}.csv");
}
}
/*
//$data = array(array("one","two","three"), array(4,5,6));
$data[] = array("one","two","three");
$data[] = array(4,5,6);
$csv = new CSV_Writer($data);
$csv->headers('test');
$csv->output();
*/
3. Use the excel class
<?php
require_once 'Spreadsheet/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
/* generate CSV
$filename = date('YmdHis').'.csv';
$workbook->send($filename); // send Excel File name for download
*/
// generate Excel
$filename = date('YmdHis').'.xls';
$workbook->send($filename); // send Excel File name for download
$workbook->setVersion(8);
$workbook->setBIFF8InputEncoding('UTF-8');
$worksheet =& $workbook->addWorksheet("Sheet-1");
$data[]= array('id','username','company','email','mob','daytime','intent');
$data[] = array(1,' The old beam ','** The studio ','ofstack.com','1363137966*',time(),'y');
$total_row = count($data);
$total_col = count($data[0]);
for ($row = 0; $row < $total_row; $row ++) {
for ($col = 0; $col < $total_col; $col ++) {
$worksheet->writeString($row, $col, $data[$row][$col]); // in sheet-1 Write data in
}
}
/*
$worksheet =& $workbook->addWorksheet("Sheet-2");
$data[]= array('id','username','company','email','mob','daytime','intent');
$data[] = array(1,' The old beam ','** The studio ','ofstack.com','1363137966*',time(),'y');
$total_row = count($data);
$total_col = count($data[0]);
for ($row = 0; $row < $total_row; $row ++) {
for ($col = 0; $col < $total_col; $col ++) {
$worksheet->writeString($row, $col, $data[$row][$col]); // in sheet-2 Write data in
}
}
*/
$workbook->close(); // The download is complete
?>
Class 2
-- Function description
Read the Excel file
function Read_Excel_File($ExcelFile,$Result)
$ExcelFile Excel file name
$Result returns the result
The function returns 0 normally, otherwise an error message is returned
Returns an array of values
The value of $result[sheet name][row][column] is the corresponding value of Excel Cell
Create the Excel file
function Create_Excel_File($ExcelFile,$Data)
$ExcelFile Excel file name
$Data Excel table data
Write the function at the beginning of the PHP script
Case 1:
<?
require "excel_class.php";
Read_Excel_File("Book1.xls",$return);
for ($i=0;$i<count($return[Sheet1]);$i++)
{
for ($j=0;$j<count($return[Sheet1][$i]);$j++)
{
echo $return[Sheet1][$i][$j]."|";
}
echo "<br>";
}
?>
Example 2:
<?
$filename="php Imported into the excel-utf-8 coding ";
$filename=iconv("utf-8", "gb2312", $filename);
echo $filename;
?>
0