Я пытаюсь прочитать файл Excel (Office 2003). Существует файл Excel, который необходимо загрузить, и его содержимое анализируется.
Через Google я могу найти ответы на эти связанные (и недостаточные темы): генерировать файлы Excel, читать файлы Excel XML, читать файлы Excel CSV или неполные заброшенные проекты. Я владею Office 2003, поэтому, если мне нужны какие-то файлы, они доступны. Он установлен на моем ящике, но не установлен и не может быть установлен на моем общем хосте.
Изменить: пока все ответы указывают на PHP-ExcelReader и / или эту дополнительную статью о том, как ее использовать.
Я использую PHP-ExcelReader для чтения файлов xls и отлично работает.
Насколько мне известно, у вас есть 2 варианта:
PHPExcel использует Spreadsheet_Excel_Reader для формата Office 2003.
Обновление: мне когда-то приходилось читать некоторые файлы Excel, но я использовал формат Office 2003 XML, чтобы прочитать их и сказал людям, которые использовали приложение, чтобы сохранить и загрузить только этот тип файла Excel.
Это зависит от того, как вы хотите использовать данные в файле excel. Если вы хотите импортировать его в mysql, вы можете просто сохранить его как файл в формате CSV, а затем использовать fgetcsv для его анализа.
Попробуй это…
Я использовал следующий код для чтения «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>'; Ссылка: http://coursesweb.net/php-mysql/read-excel-file-data-php_pc
Существует отличная статья, объясняющая, как читать / писать файлы excel через php-код. Они рекомендовали использовать PHP-обработчик MS-Excel Stream Handler, который является одной из самых популярных библиотек для этого:
// 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с// 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с// 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с// 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