用 PHP 读取 Excel 文件

小开

You have 2 choices as far as I know:

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)

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.

小开

最佳答案

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

小开

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.

小开

Try this...

I have used following code to read "xls and 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

小开

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

小开

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
)


)

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

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

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

if ( $csv = SimpleCSV::import('book.csv') ) {
print_r( $csv );
}

小开

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

    include '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);

小开

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

Output: