Encoding Issue while reading Excel file through PHP COM

Question

I am reading an excel spreadsheet with PHP COM utility, everything is working fine except there are some cells in Excel file having different language data. When I read this data through PHP Com it displays like ???????

$ExlApp = new COM ( "Excel.Application" ); $workbook = $ExlApp->Workbooks->Open ( 'f:\dev\htdocs\excel\testfile.xlsx' ); $worksheet = $workbook->worksheets ( 1 ); $done = false; $row_index = 1; while ( $done == false ) { $english = $worksheet->cells ( $row_index, 1 )->value; $dari = $worksheet->cells ( $row_index, 2 )->value; if ($english != '') { $row_index ++; echo "<div style='float:left;width:420px'>".$english."</div><div>".$dari."</div>"; } else { $done = true; } } $workbook->close ();

I have checked page encoding and its set to UTF-8. When I open original excel file it shows correct text but when I read it from PHP COM the encoding is lost. Does anyone have solution to this problem.

EDIT

How I can ensure that the value given by excel $worksheet->cells ( $row_index,2)->value is in correct encoding OR is there any property in Excel which I can set through PHP COM so it return data in UTF-8?

I have checked the encoding of value returned by Excel cell through mb_detect_encoding function in PHP and it gives ASCII where as it must give UTF-16 or UTF-8. It appears that excel does not give value in correct encoding.

Here is the Excel file I am reading with this script: http://asimishaq.com/myfiles/testfile.xlsx

Note that the solution is required using PHP COM-INTEROP only.

that's not a php problem, per-se. you just need to ensure that whatever medium PHP is outputting into has been told what character set the data is in. e.g. if you're dumping out to a web-browser, you need an appropriate Content-type header/meta tag. — Marc B, CommentedDec 24, 2013 at 19:01
@marc-b How I can ensure that the value returned from $worksheet->cells ( $row_index, 1)->value is in correct encoding? — asim-ishaq, CommentedDec 24, 2013 at 19:06
either use mb_convert() and its cousins to convert the utf data coming out of the spreadsheet into whatever charset the destination is expecting, or TELL the destination that "hey, I'm sending you utf-8" — Marc B, CommentedDec 24, 2013 at 19:15
@marc-b I have checked the encoding of value return from Excel using mb_detect_encoding and it is ASCII. It must be UTF-8 or 16 it means the sender ms Excel have some issue in encoding? — asim-ishaq, CommentedDec 25, 2013 at 19:02
I would try to set the codepage used by COM in its constructor: see php.net/manual/en/class.com.php (try CP_UTF8) — user180100, CommentedDec 27, 2013 at 6:24

asim-ishaq · Accepted Answer · 2013-12-27 06:54:36Z

As pointed out by @rc we need to specify codepage property in COM constructor to obtain data in correct encoding.

$ExlApp = new COM ( "Excel.Application", NULL, CP_UTF8 );

By changing the above line in script the data is displayed correctly.

Collectives™ on Stack Overflow

Encoding Issue while reading Excel file through PHP COM

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related