PHP excel export garbled characters

PHP excel export garbled characters

/*** Function used to generate an excel file * author: walker * @ param $ data to generate excel data (in the form of a two-dimensional array) * @ param null $ savefile: generate the excel file name (if not specified, it is the current timestamp) * @ param null $ title: generate the excel header (in the one-dimensional array form) * @ param string $ sheetname: the sheet name generated in excel (sheet1 by default) */function exportExcel ($ data, $ savefile = null, $ title = null, $ sheetname = 'sheet1') {// if no file name is specified, the current timestamp if (is_null ($ savefile) {$ savefile = time ();} // if an excel header is specified, the form is appended to the body content before the if (is_array ($ title) {array_unshift ($ data, $ title );} import ('org. util. PHPExcel '); import ('org. util. PHPExcel. IOFactory '); import ('phpexcel. util. PHPExcel. reader. excel5'); $ objPHPExcel = new \ PHPExcel (); // Excel content foreach ($ data as $ k => $ v) {$ obj = $ objPHPExcel-> setActiveSheetIndex (0); $ row = $ k + 1; // row $ nn = 0; foreach ($ v as $ vv) {$ col = chr (65 + $ nn); // Column $ vv = iconv ("UTF-8", "GB2312 // IGNORE", $ vv ); $ obj-> setCellValue ($ col. $ row, $ vv); // column, row, Value $ nn ++ ;}}$ objPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname ); $ objPHPExcel-> setActiveSheetIndex (0); header ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "'.20.savefile.'.xls"'); header ("Content-Type: application/vnd. ms-excel; charset = UTF-8 "); header ('cache-Control: max-age = 0'); $ objWriter = \ PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output'); exit;}/***** export Excel */function expUser () {// export Excel $ xlstitle = array ('id', 'item code', 'Template number', 'item name', 'Total stock', 'Current stock ', 'vendor '); $ xlssql = M ('erp _ inventory'); $ xlsData = $ xlssql-> getField ('Id, p_code, mid, pname, banlance, cur_banlance, factory '); // show_bug ($ xlsData); die; $ this-> exportExcel ($ xlsData, time (), $ xlstitle );}

The above is my code. I use the thinkphp framework. The second figure shows the exported results. Where can I solve the problem?

Reply to discussion (solution)

Comment out 29 rows $ vv = iconv ("UTF-8", "GB2312 // IGNORE", $ vv );

Comment out 29 rows $ vv = iconv ("UTF-8", "GB2312 // IGNORE", $ vv );

There were no 29 lines at the beginning. I added them later. the effect of adding or exporting them is the same !!

Since thinkphp is used, it should be UTF-8.
The default character set of PHPExcel is also UTF-8.
So why?
$ Vv = iconv ("UTF-8", "GB2312 // IGNORE", $ vv );
Convert UTF-8 to gb2312?

Paste 52 lines and some code printed by $ xlsData is a two-dimensional array. There should be no problem here. Hope you can help me a little

$ Vv = iconv ("UTF-8", "GB2312 // IGNORE", $ vv );
$ Vv = iconv ("UTF-8", "UTF-8 // IGNORE", $ vv );
$ Vv = iconv ("GB2312", "UTF-8 // IGNORE", $ vv );
The above three methods have been tried. The results are the same !!

Since thinkphp is used, it should be UTF-8.
The default character set of PHPExcel is also UTF-8.
So why?
$ Vv = iconv ("UTF-8", "GB2312 // IGNORE", $ vv );
Convert UTF-8 to gb2312?

Why not? Didn't I make it clear? It seems that you cannot accept heuristic answers

Delete all the statements using iconv transcoding!
Do you understand this?
Data written to excel in PHPExcel must be UTF-8 encoded.
Should that be understood?

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

Why not? Didn't I make it clear? It seems that you cannot accept heuristic answers

Delete all the statements using iconv transcoding!
Do you understand this?
Data written to excel in PHPExcel must be UTF-8 encoded.
Should that be understood?

I understand. it's still useless to delete lines 29th and 38. Where can I delete them?

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

$ Vv =, what does it mean?

38 no need to delete

If not, paste it.

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

$ Vv =, what does it mean?

Clerical error, $ vv

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

$ Vv =, what does it mean?

Clerical error, $ vv

Cannot survive,

38 no need to delete

If not, paste it.

Is it because of WPS? WPS used by the company and office2007 used at home, but it is currently garbled. How to break

Method 1:
Setlocale (LC_ALL, 'zh _ cn ');

Method 2:
$ Str = mb_convert_encoding ($ str, "UTF-8", "GBK"); // The source code is known as GBK and converted to UTF-8

For more information, see blog. Although I export csv files, I think the truth should be the same.
Http://blog.csdn.net/phpfenghuo/article/details/17483261

Method 1:
Setlocale (LC_ALL, 'zh _ cn ');

Method 2:
$ Str = mb_convert_encoding ($ str, "UTF-8", "GBK"); // The source code is known as GBK and converted to UTF-8

For more information, see blog. Although I export csv files, I think the truth should be the same.
Http://blog.csdn.net/phpfenghuo/article/details/17483261

Method 1: What is LC_ALL?
Method 2: Error
Where are these two sentences? I put it in 28 rows, instead of $ vv,

It is not clear how Untitled Spreadsheet and Unknown Creator come from.
Why $ obj = $ objPHPExcel-> setActiveSheetIndex (0 );
Instead of $ obj = $ objPHPExcel-> getActiveSheet ();

I tested it separately just now. your initial code should be okay, of course, there is no iconv
The problem is how the Untitled Spreadsheet and Unknown Creator in the document are generated.

It is not clear how Untitled Spreadsheet and Unknown Creator come from.
Why $ obj = $ objPHPExcel-> setActiveSheetIndex (0 );
Instead of $ obj = $ objPHPExcel-> getActiveSheet ();

As you said, the Untitled Spreadsheet and Unknown Creator are gone.

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

$ Vv =, what does it mean?

Clerical error, $ vv

Cannot survive,

Extension = php_mbstring.dll has enabled this extension.

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

$ Vv =, what does it mean?

Clerical error, $ vv

Cannot survive,

Extension = php_mbstring.dll has enabled this extension.

Php extension = php_mbstring.dll is enabled. restart apache and no error is returned for export, but it is still garbled.

Do you still have an error message?
A4 Worksheet... Feuilles de calcul

Original
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname );
$ ObjPHPExcel-> setActiveSheetIndex (0 );
Nothing wrong!

It is not clear how Untitled Spreadsheet and Unknown Creator come from.
Why $ obj = $ objPHPExcel-> setActiveSheetIndex (0 );
Instead of $ obj = $ objPHPExcel-> getActiveSheet ();

As you said, the Untitled Spreadsheet and Unknown Creator are gone.

I just read it wrong. it should be 28 lines or garbled characters.

Do you still have an error message?
A4 Worksheet... Feuilles de calcul

Original
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname );
$ ObjPHPExcel-> setActiveSheetIndex (0 );
Nothing wrong!

Now that the code has just returned, can I change the encoding in the garbled excel settings?

Yes, I have switched back.

As I mentioned earlier, it is okay for me to test your code segment separately.
So the problem may be something else: the incoming data, the data read from the table, or even the phpexcel class you use.

Include 'Plug In/PHPExcel/Classes/PHPExcel. php '; // import ('org. util. PHPExcel. IOFactory '); // import ('phpexcel. util. PHPExcel. reader. excel5 '); $ data = array ('zhong', 'wen', 'C'), array ('A',' B ', 'C '), array ('A', 'B', 'C'),); $ sheetname = '000000'; $ savefile = time (); $ objPHPExcel = new PHPExcel (); // Excel content foreach ($ data as $ k => $ v) {$ obj = $ objPHPExcel-> setActiveSheetIndex (0); $ row = $ k + 1; // row $ nn = 0; foreach ($ v as $ vv) {$ col = chr (65 + $ nn); // Column $ vv = iconv ('gbk ', 'utf-8', $ vv); $ obj-> setCellValue ($ col. $ row, $ vv); // column, row, Value $ nn ++ ;}}$ objPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname ); $ objPHPExcel-> setActiveSheetIndex (0); header ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "'.20.savefile.'.xls"'); header ("Content-Type: application/vnd. ms-excel; charset = UTF-8 "); header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output ');

Yes, I have switched back.

As I mentioned earlier, it is okay for me to test your code segment separately.
So the problem may be something else: the incoming data, the data read from the table, or even the phpexcel class you use.

Include 'Plug In/PHPExcel/Classes/PHPExcel. php '; // import ('org. util. PHPExcel. IOFactory '); // import ('phpexcel. util. PHPExcel. reader. excel5 '); $ data = array ('zhong', 'wen', 'C'), array ('A',' B ', 'C '), array ('A', 'B', 'C'),); $ sheetname = '000000'; $ savefile = time (); $ objPHPExcel = new PHPExcel (); // Excel content foreach ($ data as $ k => $ v) {$ obj = $ objPHPExcel-> setActiveSheetIndex (0); $ row = $ k + 1; // row $ nn = 0; foreach ($ v as $ vv) {$ col = chr (65 + $ nn); // Column $ vv = iconv ('gbk ', 'utf-8', $ vv); $ obj-> setCellValue ($ col. $ row, $ vv); // column, row, Value $ nn ++ ;}}$ objPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname ); $ objPHPExcel-> setActiveSheetIndex (0); header ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "'.20.savefile.'.xls"'); header ("Content-Type: application/vnd. ms-excel; charset = UTF-8 "); header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output ');

No problem with the source data and the obtained data. The excel class is under the official website. this package is still used for Project excel import. it should be okay.

Switch to gbk, and iconv to Mars and traditional Chinese characters will have problems.
$ Vv = mb_convert_encoding ($ vv =, "GBK", "UTF-8 ");

$ Vv =, what does it mean?

Clerical error, $ vv

Cannot survive,

Extension = php_mbstring.dll has enabled this extension.

Php extension = php_mbstring.dll is enabled. restart apache and no error is returned for export, but it is still garbled.

Header ("Content-Type: application/vnd. ms-excel; charset = UTF-8"); comment out this sentence to see

Yes, I have switched back.

As I mentioned earlier, it is okay for me to test your code segment separately.
So the problem may be something else: the incoming data, the data read from the table, or even the phpexcel class you use.

Include 'Plug In/PHPExcel/Classes/PHPExcel. php '; // import ('org. util. PHPExcel. IOFactory '); // import ('phpexcel. util. PHPExcel. reader. excel5 '); $ data = array ('zhong', 'wen', 'C'), array ('A',' B ', 'C '), array ('A', 'B', 'C'),); $ sheetname = '000000'; $ savefile = time (); $ objPHPExcel = new PHPExcel (); // Excel content foreach ($ data as $ k => $ v) {$ obj = $ objPHPExcel-> setActiveSheetIndex (0); $ row = $ k + 1; // row $ nn = 0; foreach ($ v as $ vv) {$ col = chr (65 + $ nn); // Column $ vv = iconv ('gbk ', 'utf-8', $ vv); $ obj-> setCellValue ($ col. $ row, $ vv); // column, row, Value $ nn ++ ;}}$ objPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname ); $ objPHPExcel-> setActiveSheetIndex (0); header ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "'.20.savefile.'.xls"'); header ("Content-Type: application/vnd. ms-excel; charset = UTF-8 "); header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output ');

I got a set of test data. is it still garbled? Are you sure you want to test it?

Okay, I have exported it, and exportexcel has been rewritten. I still have a problem writing this method. thank you for your consideration.

The error message is displayed in lines 5th and 11.
You can remove the title, retain only the data body, and test the title.
Comment out this sentence in exportExcel.
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname );

The error message is displayed in lines 5th and 11.
You can remove the title, retain only the data body, and test the title.
Comment out this sentence in exportExcel.
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname );

Or not.

Function exportexcel ($ data = array (), $ title = array (), $ filename = 'report') {header ("Content-type: application/octet-stream "); header ("Accept-Ranges: bytes"); header ("Content-type: application/vnd. ms-excel "); header (" Content-Disposition: attachment; filename = ". $ filename. ". xls "); header (" Pragma: no-cache "); header (" Expires: 0 "); // export xls to start if (! Empty ($ title) {foreach ($ title as $ k => $ v) {$ title [$ k] = iconv ("UTF-8", "GB2312 ", $ v) ;}$ title = implode ("\ t", $ title); echo "$ title \ n" ;}if (! Empty ($ data) {foreach ($ data as $ key => $ val) {foreach ($ val as $ ck => $ cv) {$ data [$ key] [$ ck] = iconv ("UTF-8", "GB2312", $ cv );} $ data [$ key] = implode ("\ t", $ data [$ key]);} echo implode ("\ n", $ data );}}

I can use the above method to export the data. The problem is that each group of data is in the column of each row in the excel table, and the data is not exclusive by column. Can you change it for me?