How to convert *.xlsb to array or *.csv using php

问题:

I am trying to convert *.xlsb file into php array or *.csv file (or at least *.xls). I tried to use PHPExcel, but looks like it can not recognize what's inside this file.

I noticed, that you can rename *.xlsb file into *.zip file and then unzip it using command line unzip *.zip. And after this you will get next folders with sheet1.bin file:

Looks like this file should contain Excel cell values, but I still can not parse it using PHPExcel. Can someone help me is it even possible to parse *.xlsb files and get information from it? Or maybe it is possible to parse this sheet1.bin file?

回答1:

PHPExcel does not support XLSB files. XLSB file format is a zip archive, same as XLSX, but the majority of files inside the archive are binary files. The binary files are not easy to parse.

An Excel library for PHP that supports XLSB files is EasyXLS. You can download the library from here.

Convert XLSB to PHP array

//Code for reading xlsb file
$workbook = new COM("EasyXLS.ExcelDocument");
$workbook->easy_LoadXLSBFile("file.xlsb");

//Code for building the php array
$xlsTable = $workbook->easy_getSheetAt(0)->easy_getExcelTable();
for ($row=0; $row<$xlsTable->RowCount(); $row++)
{
   for ($column=0; $column<$xlsTable->ColumnCount(); $column++)
   {
       $value = $xlsTable->easy_getCell($row, $column)->getValue();
       //transfer $value into your array
   }
}

or

//Code for reading xlsb file    
$workbook = new COM("EasyXLS.ExcelDocument");
$rows = $workbook->easy_ReadXLSBActiveSheet_AsList("file.xlsb");

//Code for building the php array
for ($rowIndex=0; $rowIndex<$rows->size(); $rowIndex++)
    {
        $row = $rows->elementAt($rowIndex);
        for ($cellIndex=0; $cellIndex<$row->size(); $cellIndex++)
        {
            $value = $row->elementAt($cellIndex);
            //transfer $value into your array
        }
    }

Convert XLSB to CSV

//Code for reading xlsb file
$workbook = new COM("EasyXLS.ExcelDocument");
$workbook->easy_LoadXLSBFile("file.xlsb");

//Code for converting to CSV
$workbook->easy_WriteCSVFile("file.csv", $workbook->easy_getSheetAt(0)->getSheetName());

Convert XLSB to XLS

//Code for reading xlsb file
$workbook = new COM("EasyXLS.ExcelDocument");
$workbook->easy_LoadXLSBFile("file.xlsb");

//Code for converting to XLS
$workbook->easy_WriteXLSFile("file.xls");