An excel report is generated in PHP using the PHPExcel library, the encoding of all database tables is UTF-8, but the database itself is still windows-1251 ...

<?php require_once('PHPExcel.php'); require_once('PHPExcel/Writer/Excel5.php'); require_once('config.php'); $dbc = mysqli_connect($_HOST, $_USERNAME, $_PASSWORD, $_NAME_DB ); $query = "SELECT * FROM rpk_02_pto WHERE id_pto = 1 "; $data = mysqli_query($dbc, $query); $row = mysqli_fetch_array($data) ; // public $_id_pto = 0; $_ABC = 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'); $fields = array( 'first_name', //2 'name', //3 'patronymic', //4 'date_birth', //5 'sex', //6 'id_code_jobs', //7 'ped_work', 'id_academic_code', //8 'id_code_qual_cat', //9 'id_qualif_rank', //10 'id_code_object', //11 'id_code_object_dop', //12 'teaching_experience', //13 'officio_experience', //14 'higher_pedagogical_education', //15 'higher_education_more', //16 'sec_pedagogical_education', //17 'sec_education_more', //18 'overall_average', //19 'year_probation', //20 'id_code_educ_inst', //21 'year_last_training', //22 'id_specialty_code', //23 'retraining_year', //24 'retraining_enrolled_current_year', //25 'code_educ_specialty_group', //26 'year_training_profile_specialty', //27 'id_note'); //29 $xls = new PHPExcel(); // Устанавливаем индекс активного листа $xls->setActiveSheetIndex(0); // Получаем активный лист $sheet = $xls->getActiveSheet(); // Подписываем лист $sheet->setTitle('РПК02'); for($i=0;$i!=28;$i++) { $sheet->getColumnDimension($_ABC[$i])->setWidth(10); } $sheet->getColumnDimension('B')->setWidth(15); $sheet->getColumnDimension('C')->setWidth(15); $sheet->getColumnDimension('D')->setWidth(15); $sheet->getColumnDimension('Z')->setWidth(20); $sheet->mergeCells('A1:A3'); $sheet->mergeCells('B1:D1'); $sheet->mergeCells('B2:B3'); ... $sheet->setCellValue('A5', $row['name']); //не выводится так как кириллица(вообще пустая ячейка) $sheet->setCellValue('D5', $row['sex']); // тут и $sheet->setCellValue('B5', $row['id_code_jobs']); // тут выводятся как и положены цифры $sheet->setCellValue('A1', 'Код учреждения образования'); $sheet->setCellValue('B1', 'Сотрудники'); $sheet->setCellValue('B2', 'Фамилия'); $sheet->setCellValue('C2', 'Имя'); $sheet->setCellValue('D2', 'Отчество'); ... $sheet->setCellValue('E5', '8с'); $sheet->setCellValue('F5', '1ц'); $sheet->setCellValue('G5', '3ц'); $sheet->setCellValue('H5', '1ц'); ... $sheet->getStyle('A1')->getAlignment()->setTextRotation(90); $sheet->getStyle('B2')->getAlignment()->setTextRotation(90); $sheet->getStyle('C2')->getAlignment()->setTextRotation(90); ... // устанавливаем авто подбор высоты $sheet->getRowDimension(1)->setRowHeight(-1); $sheet->getRowDimension(2)->setRowHeight(-1); $sheet->getRowDimension(3)->setRowHeight(-1); // и авто перенос текста $sheet->getStyle('A1:AC1')->getAlignment()->setWrapText(true); $sheet->getStyle('A2:AC2')->getAlignment()->setWrapText(true); $sheet->getStyle('A3:AC3')->getAlignment()->setWrapText(true); $sheet->getRowDimension(1)->setRowHeight(100); $sheet->getRowDimension(2)->setRowHeight(30); $sheet->getRowDimension(3)->setRowHeight(90); for($i = 0; $i < 29; ++$i) { $sheet->setCellValue($_ABC[$i] . '4', $i+1); } header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" ); header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" ); header ( "Cache-Control: no-cache, must-revalidate" ); header ( "Pragma: no-cache" ); header ( "Content-type: application/vnd.ms-excel" ); header ( "Content-Disposition: attachment; filename=report.xls" ); $objWriter = new PHPExcel_Writer_Excel5($xls); $objWriter->save('php://output');

I put in front of the query mysql_set_charset("UTF-8"); - did not help, what can you do to display it? : 3

  • In general, the entire database was converted to utf8_general_ci encoding (and so all the tables were in this encoding, the weather will not play according to the idea ..), in general, this also did not help - Roman Ukhnalev

1 answer 1

iconv values ​​in the desired encoding (in phpEXcell need utf8).

 iconv('windows-1251', 'utf-8', $str2);
  • $ str2 = $ row ['name']; iconv ('windows-1251', 'utf-8', $ str2); $ sheet-> setCellValue ('A5', $ str2); // did not help ... - Roman Ukhnalev
  • And here >> $ sheet-> setCellValue ('A1', 'Educational Establishment Code'); is Cyrillic displayed? - hardworm
  • Yes, the report header is formed wonderfully, all Russian characters that are directly from the code are output, but only numbers from the database ... - Roman Ukhnalev
  • So not everything is done with the encoding. mysqli_set_charset ("UTF-8", $ dbc) after connecting. Determine the encoding of the output from the database php.net/manual/ru/function.mb-detect-encoding.php - hardworm
  • iconv - heff helped me in due time