-2

I need some help with PHPEXCEL library, everything works great, I'm successfully extracting my SQL query to excel5 file, I need to give this file to transport company in order to auto collect informations about packages, unfotunately the generated excel file has some ascii characters between each letter of the cell text, and when the excel file is imported you need to manually delete these charaters. If I open the excel file, everything is fine I see: COMPANY NAME, If I open the excel file with notepad++, I see the cell values this way: C(NUL)O(NUL)M(NUL)P(NUL)A(NUL)N(NUL)Y N(NUL)A(NUL)M(NUL)E If I open again the file with excel and save, then reopen with notepad++ I see COMPANY NAME. So I do not understan why every time I create an excel file using PHPEXCEL my every letter of all words are filled with (nul) every letter. So how do I prevent the generated excel file to include (nul) between every word????

Also if you open the original excel file generated from PHPExcel samples are also filled with (nul) and if you open and save it, the (nul) is gone. Any help would be appreciated, thanks. what is the (nul) ??? 0x00??? char(0)???

ok, here is the example:

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
    die('Disponibile solo su browser');

require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setCreator("Solidus")
                            ->setLastModifiedBy("Solidus")
                            ->setTitle("Import web")
                            ->setSubject("Import File")
                            ->setDescription("n.a")
                            ->setKeywords("n.a")
                            ->setCategory("n.a");
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueExplicit("A1", "COMPANY")
                ->setCellValue('A2', 'SAMSUNG');
$objPHPExcel->getActiveSheet()->setTitle('DDT');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="TEST.xls"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Cache-Control: private',false);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
ob_end_clean();
$objWriter->save('php://output');

As you can see from this little example, this scripts creates a file excel5 with 2 cells, A1 = COMPANY, A2 = SAMSUNG

when I send this file to the transport company, they import the file into their system, but as you can see from the picture, there is an weird character between each letter.

so I noticed every time I open the generated Excel5 with notepad++ file I get: S(nul)A(nul)M(nul)S(nul)U(nul)N(nul)G

If I save the save with excel and then open it again with notepad++ I get: SAMSUNG and this file is ok for the transport company

so my question is, how should I avoid the file generated to contain thi '(nul) charachter between each letter???? some help?

weird characters

SAMSUNG

Joshua
  • 40,822
  • 8
  • 72
  • 132
Solidus
  • 11
  • 3
  • provide a minimal, complete, and verifiable example http://stackoverflow.com/help/mcve – birdspider Feb 15 '17 at 18:07
  • BIFF-format Excel files (xls) use a dual-byte encoding; so that's what I'd expect to see if I opened it in notepad++... it's what you see when it's opened in MS Excel that matters.... however, it does expect all data inserted into cells to be UTF-8 – Mark Baker Feb 15 '17 at 23:41
  • How is the transport company reading the Excel file? – Mark Baker Feb 16 '17 at 10:46
  • I need to load the file into their website (php/excel reader?), I think they need to add some class to clean the fields before loading into input fields, I asked them to this, but they do not want to touch their code. Thanks for your interest in helping me Mark. – Solidus Feb 16 '17 at 11:09

1 Answers1

0

I found the soluion by myself, I explain just in case anyone has also this problem:

there is not way to change the way the excelfile is encoded by PHPEXCEL so I figured out the problem was reading the file, I did some simulations and reproduce the problem, every time a read the file and put the result into inputs a get weird characters:

C�O�M�P�A�N�Y�

If I set the output enconding enconding as follows:

$excel->setOutputEncoding('UTF-8');

the file loads fine, so the problem was not creating the excel file, but reading the excel file.

If I print the variable with ECHO I get: "COMPANY", if I put the variable on input as value I get: "C�O�M�P�A�N�Y�"

setting the output solves the problem, but I would like to know why the difference when I put the variable on input as value, thanks

Solidus
  • 11
  • 3