Reading an Excel file in PHP

2019-01-01 12:41发布

I'm trying to read an Excel file (Office 2003). There is an Excel file that needs to be uploaded and its contents parsed.

Via Google, I can only find answers to these related (and insufficient topics): generating Excel files, reading Excel XML files, reading Excel CSV files, or incomplete abandoned projects. I own Office 2003 so if I need any files from there, they are available. It's installed on my box but isn't and can't be installed on my shared host.

Edit: so far all answers point to PHP-ExcelReader and/or this additional article about how to use it.

7条回答
ら面具成の殇う
2楼-- · 2019-01-01 13:26

I use PHP-ExcelReader to read xls files, and works great.

查看更多
临风纵饮
3楼-- · 2019-01-01 13:28

You have 2 choice as far as I know:

  1. Spreadsheet_Excel_Reader, which knows the Office 2003 binary format
  2. PHPExcel, which knows both Office 2003 as well as Excel 2007 (XML).

PHPExcel uses Spreadsheet_Excel_Reader for the Office 2003 format.

Update: I once had to read some Excel files but I used the Office 2003 XML format in order to read them and told the people that were using the application to save and upload only that type of Excel file.

查看更多
春风洒进眼中
4楼-- · 2019-01-01 13:38

Read XLSX (Excel 97-2003)
https://github.com/shuchkin/simplexls

if ( $xls = SimpleXLS::parse('book.xls') ) {
    print_r( $xls->rows() );
} else {
    echo SimpleXLS::parseError();
}

Read XLSX (Excel 2003+)
https://github.com/shuchkin/simplexlsx

if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) {
    print_r( $xlsx->rows() );
} else {
    echo SimpleXLSX::parseError();
}

Output

Array (
    [0] => Array
        (
            [0] => ISBN
            [1] => title
            [2] => author
            [3] => publisher
            [4] => ctry
        )
    [1] => Array
        (
            [0] => 618260307
            [1] => The Hobbit
            [2] => J. R. R. Tolkien
            [3] => Houghton Mifflin
            [4] => USA
       )

)

CSV php reader
https://github.com/shuchkin/simplecsv

查看更多
残风、尘缘若梦
5楼-- · 2019-01-01 13:40

There is a great article to explain how to read/write excel files through php code, They have been recommend to use MS-Excel Stream Handler PHP class, which is one of the top class library for that :)

查看更多
孤独总比滥情好
6楼-- · 2019-01-01 13:41
// Here is the simple code using COM object in PHP
class Excel_ReadWrite{

    private $XLSHandle;
    private $WrkBksHandle;
    private $xlBook;

    function __construct() {
        $this->XLSHandle = new COM("excel.application") or die("ERROR: Unable to instantaniate COM!\r\n"); 
    }

    function __destruct(){
        //if already existing file is opened
        if($this->WrkBksHandle != null)
        {   
            $this->WrkBksHandle->Close(True);
            unset($this->WrkBksHandle);
            $this->XLSHandle->Workbooks->Close();
        }
        //if created new xls file
        if($this->xlBook != null)
        {
            $this->xlBook->Close(True);
            unset($this->xlBook);
        }
        //Quit Excel Application
        $this->XLSHandle->Quit();
        unset($this->XLSHandle);
    }

    public function OpenFile($FilePath)
    {
        $this->WrkBksHandle = $this->XLSHandle->Workbooks->Open($FilePath);
    }

    public function ReadData($RowNo, $ClmNo)
    {
       $Value = $this->XLSHandle->ActiveSheet->Cells($RowNo, $ClmNo)->Value;
       return $Value;
    }  

    public function SaveOpenedFile()
    {
        $this->WrkBksHandle->Save(); 
    }  

    /***********************************************************************************
    * Function Name:- WriteToXlsFile() will write data based on row and column numbers
    * @Param:- $CellData- cell data
    * @Param:- $RowNumber- xlsx file row number
    * @Param:- $ColumnNumber- xlsx file column numbers
   ************************************************************************************/
   function WriteToXlsFile($CellData, $RowNumber, $ColumnNumber)
   {
       try{
               $this->XLSHandle->ActiveSheet->Cells($RowNumber,$ColumnNumber)->Value = $CellData;
           }
       catch(Exception $e){
               throw new Exception("Error:- Unable to write data to xlsx sheet");
           }
   }


   /****************************************************************************************
    * Function Name:- CreateXlsFileWithClmName() will initialize xls file with column Names
    * @Param:- $XlsColumnNames- Array of columns data
    * @Param:- $XlsColumnWidth- Array of columns width
   *******************************************************************************************/
   function CreateXlsFileWithClmNameAndWidth($WorkSheetName = "Raman", $XlsColumnNames = null, $XlsColumnWidth = null)
   {
       //Hide MS Excel application window
       $this->XLSHandle->Visible = 0;
       //Create new document
       $this->xlBook = $this->XLSHandle->Workbooks->Add();

       //Create Sheet 1
       $this->xlBook->Worksheets(1)->Name = $WorkSheetName;
       $this->xlBook->Worksheets(1)->Select;

       if($XlsColumnWidth != null)
       {
           //$XlsColumnWidth = array("A1"=>15,"B1"=>20);
           foreach($XlsColumnWidth as $Clm=>$Width)
           {
               //Set Columns Width
               $this->XLSHandle->ActiveSheet->Range($Clm.":".$Clm)->ColumnWidth = $Width;
           }    
       }
       if($XlsColumnNames != null)
       {
           //$XlsColumnNames = array("FirstColumnName"=>1, "SecondColumnName"=>2);
           foreach($XlsColumnNames as $ClmName=>$ClmNumber)
           {
               // Cells(Row,Column)
               $this->XLSHandle->ActiveSheet->Cells(1,$ClmNumber)->Value = $ClmName;
               $this->XLSHandle->ActiveSheet->Cells(1,$ClmNumber)->Font->Bold = True;
               $this->XLSHandle->ActiveSheet->Cells(1,$ClmNumber)->Interior->ColorIndex = "15";
           }
       }
   }
   //56 is for xls 8
    public function SaveCreatedFile($FileName, $FileFormat = 56)
    {
        $this->xlBook->SaveAs($FileName, $FileFormat);
    }

    public function MakeFileVisible()
    {
       //Hide MS Excel application window`enter code here`
       $this->XLSHandle->Visible = 1;
    }
}//end of EXCEL class
查看更多
不流泪的眼
7楼-- · 2019-01-01 13:44

It depends on how you want to use the data in the excel file. If you want to import it into mysql, you could simply save it as a CSV formatted file and then use fgetcsv to parse it.

查看更多
登录 后发表回答