Witam, mam problem z kodowaniem przy eksporcie przez php tabeli z mysql do pliku csv lub xls.
PHP Version 5.2.6-1+lenny9
Wersja klienta MySQL: 5.0.51a
PHPMYADMIN: 3.3.9.2
tak wygląda kod do eksportu który znalazłem i dopasowałem do siebie:
<?php
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=wpisy.xls ");
$connect = mysql_connect("localhost","uzytkownik","haslo");
$base = mysql_select_db("baza");
// Get data records from table.
$result=mysql_query("select * from tabela order by id asc");
// Functions for export to excel.
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
xlsBOF();
/*
Make a top line on your excel sheet at line 1 (starting at 0).
The first number is the row number and the second number is the column, both are start at '0'
*/
xlsWriteLabel(0,0,"ID");
xlsWriteLabel(0,1,"2kolumna");
xlsWriteLabel(0,2,"3kolumna");
xlsWriteLabel(0,3,"4kolumna");
xlsWriteLabel(0,4,"5kolumna");
xlsWriteLabel(0,5,"6kolumna");
xlsWriteLabel(0,6,"7kolumna");
xlsWriteLabel(0,7,"8kolumna");
$xlsRow = 1;
// Put data records from mysql by while loop.
while($row=mysql_fetch_array($result)){
xlsWriteNumber($xlsRow,0,$row['id']);
xlsWriteLabel($xlsRow,1,$row['2kolumna']);
xlsWriteLabel($xlsRow,2,$row['3kolumna']);
xlsWriteLabel($xlsRow,3,$row['4kolumna']);
xlsWriteNumber($xlsRow,4,$row['5kolumna']);
xlsWriteNumber($xlsRow,5,$row['6kolumna']);
xlsWriteNumber($xlsRow,6,$row['7kolumna']);
xlsWriteLabel($xlsRow,7,$row['8kolumna']);
$xlsRow++;
}
xlsEOF();
exit();
?>
DANE z bazy danych:
-- phpMyAdmin SQL Dump
-- version 3.3.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Czas wygenerowania: 31 Sie 2012, 14:33
-- Wersja serwera: 5.0.51
-- Wersja PHP: 5.2.6-1+lenny9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Baza danych: `baza`
--
-- --------------------------------------------------------
--
-- Struktura tabeli dla `tabela`
--
CREATE TABLE IF NOT EXISTS `tabela` (
`id` int(11) NOT NULL auto_increment,
`2kolumna` varchar(100) character set utf8 collate utf8_polish_ci NOT NULL,
`3kolumna` varchar(100) character set utf8 collate utf8_polish_ci NOT NULL,
`4kolumna` varchar(100) character set utf8 collate utf8_polish_ci NOT NULL,
`5kolumna` int(2) NOT NULL,
`6kolumna` int(2) NOT NULL,
`7kolumna` int(4) NOT NULL,
`8kolumna` varchar(20) character set utf8 collate utf8_polish_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
--
-- Zrzut danych tabeli `tabela`
--
INSERT INTO `rezerwacja` (`id`, `2kolumna`, `3kolumna`, `4kolumna`, `5kolumna`, `6kolumna`, `7kolumna`, `8kolumna`) VALUES
(5, 'abc', 'ĄĘĆŹLŁłó', 'ĄĘĆŹLŁłó', 15, 56, 1561, '12-08-29/12/664'),
(6, 'abc', '1', 'ĄĘĆŹLŁłó', 15, 11, 1561, '12-08-29/12/664'),
(7, 'abc', 'ĄĘĆŹLŁłó', 'ĄĘĆŹLŁłó', 15, 56, 1561, '12-08-29/12/412'),
(8, 'abc', 'ĄĘĆŹLŁłó', 'ĄĘĆŹLŁłó', 22, 22, 2222, '12-08-29/12/412');
a tak wygląda plik xls po eksporcie:
Proszę o pomoc, próbowałem różnych ustawień i niestety wszystko się krzaczy, nie mam pojęcia jak sobie z tym poradzić :(.