用 PHP 读取 Excel 文件

Question

提问by Dinah

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

我正在尝试读取 Excel 文件 (Office 2003)。有一个 Excel 文件需要上传并解析其内容。

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.

通过谷歌，我只能找到这些相关（和不足的主题）的答案：生成 Excel 文件、读取 Excel XML 文件、读取 Excel CSV 文件或不完整的废弃项目。我拥有 Office 2003，所以如果我需要那里的任何文件，它们都可用。它安装在我的盒子上，但没有也不能安装在我的共享主机上。

Edit:so far all answers point to PHP-ExcelReaderand/or this additional articleabout how to use it.

编辑：到目前为止，所有答案都指向PHP-ExcelReader和/或这篇关于如何使用它的附加文章。

Answer 1

采纳答案by Luis Melgratti

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

我使用PHP-ExcelReader读取 xls 文件，并且效果很好。

Answer 2

回答by Ionu? G. Stan

You have 2 choices as far as I know:

据我所知，您有 2 个选择：

Spreadsheet_Excel_Reader, which knows the Office 2003 binary format
PHPExcel, which knows both Office 2003 as well as Excel 2007 (XML). (Follow the link, and you'll see they upgraded this library to PHPSpreadSheet)

Spreadsheet_Excel_Reader，它知道 Office 2003 二进制格式
PHPExcel，它知道 Office 2003 和 Excel 2007 (XML)。（按照链接，您会看到他们将此库升级为PHPSpreadSheet）

PHPExcel uses Spreadsheet_Excel_Reader for the Office 2003 format.

PHPExcel 将 Spreadsheet_Excel_Reader 用于 Office 2003 格式。

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.

更新：我曾经不得不阅读一些 Excel 文件，但我使用 Office 2003 XML 格式来阅读它们，并告诉使用该应用程序的人只保存和上传那种类型的 Excel 文件。

Answer 3

回答by Jimbo

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 fgetcsvto parse it.

这取决于您希望如何使用 excel 文件中的数据。如果要将其导入mysql，只需将其另存为CSV 格式的文件，然后使用fgetcsv 对其进行解析。

Answer 4

回答by Sergey Shuchkin

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

阅读 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

阅读 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

CSV php 阅读器
https://github.com/shuchkin/simplecsv

Answer 5

回答by Deenadhayalan Manoharan

Try this...

尝试这个...

I have used following code to read "xls and xlsx"

我使用以下代码来读取“xls 和 xlsx”

    <?php
    include 'excel_reader.php';       // include the class
    $excel = new PhpExcelReader;      // creates object instance of the class
    $excel->read('excel_file.xls');   // reads and stores the excel file data

    // Test to see the excel data stored in $sheets property
    echo '<pre>';
    var_export($excel->sheets);

    echo '</pre>';

    or 

 echo '<pre>';
    print_r($excel->sheets);

    echo '</pre>';

Reference:http://coursesweb.net/php-mysql/read-excel-file-data-php_pc

参考：http: //coursesweb.net/php-mysql/read-excel-file-data-php_pc

Answer 6

回答by Vicky

// 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

Answer 7

回答by Inventor Bala

I'm using below excel file url: https://github.com/inventorbala/Sample-Excel-files/blob/master/sample-excel-files.xlsx

我正在使用以下 excel 文件网址：https: //github.com/inventorbala/Sample-Excel-files/blob/master/sample-excel-files.xlsx

Output:

输出：

Answer 8

回答by Inventor Bala

I have used following code to read "xls and xlsx" :

我使用以下代码读取 "xls 和 xlsx" ：

include 'PHPExcel/IOFactory.php';

包括“PHPExcel/IOFactory.php”；

$location='sample-excel-files.xlsx';

    $objPHPExcel = PHPExcel_IOFactory::load($location);
    $sheet = $objPHPExcel->getSheet(0);
    $total_rows = $sheet->getHighestRow();
    $total_columns = $sheet->getHighestColumn();
    $set_excel_query_all=array();
    for($row =2; $row <= $total_rows; $row++) {
        $singlerow = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);
        $single_row=$singlerow[0];
        $set_excel_query['store_id']=$single_row[0];
        $set_excel_query['employee_uid']=$single_row[1];
        $set_excel_query['opus_id']=$single_row[2];
        $set_excel_query['item_description']=$single_row[3];
        if($single_row[4])
        {
            $set_excel_query['opus_transaction_date']= date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($single_row[4]));
        }
        $set_excel_query['opus_transaction_num']=$single_row[5];
        $set_excel_query['opus_invoice_num']=$single_row[6];
        $set_excel_query['customer_name']=$single_row[7];
        $set_excel_query['mobile_num']=$single_row[8];
        $set_excel_query['opus_amount']=$single_row[9];
        $set_excel_query['rq4_amount']=$single_row[10];
        $set_excel_query['difference']=$single_row[11];
        $set_excel_query['ocomment']=$single_row[12];
        $set_excel_query['mark_delete']=$single_row[13];
        if($single_row[14])
        {
            $set_excel_query['upload_date']= date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($single_row[14]));
        }
        $set_excel_query_all[]=$set_excel_query;
    }

   print_r($set_excel_query_all);

用 PHP 读取 Excel 文件

提问by Dinah

采纳答案by Luis Melgratti

回答by Ionu? G. Stan

回答by Jimbo

回答by Sergey Shuchkin

回答by Deenadhayalan Manoharan

回答by Vicky

回答by Inventor Bala

回答by Inventor Bala

相关推荐

最近更新

标签

用 PHP 读取 Excel 文件

提问by Dinah

采纳答案by Luis Melgratti

回答by Ionu? G. Stan

回答by Jimbo

回答by Sergey Shuchkin

回答by Deenadhayalan Manoharan

回答by Vicky

回答by Inventor Bala

回答by Inventor Bala

相关推荐

php TCPDF 错误：无法打开图像文件：/var/www/html/tcpdf/cache/

使用 PHP 中的 LDAP 对用户进行身份验证

php 安装wamp后无法加载phpmyadmin

php wamp 无法加载 mysqli 扩展

相关推荐

最近更新

标签